/**
 * Invoice adapter query helpers — P056 invoices-adapters.
 *
 * Covers:
 *  - getAdapterConfig      — load + decrypt credentials for a provider/tenant
 *  - upsertAdapterConfig   — encrypt + store/update credentials for a provider
 *  - removeAdapterConfig   — delete credentials for a provider/tenant
 *  - listAdapterConfigs    — list configured providers for a tenant (no secrets)
 *  - logSyncEvent          — record a sync attempt outcome
 *  - listSyncLogs          — paginated sync log retrieval
 *  - getPendingInvoices    — list invoices eligible for push (TAX_ISSUED, not yet synced)
 *
 * Credentials are stored encrypted in the existing adapter_credentials table
 * (packages/db/src/schema/communications.ts). The adapter_id discriminator
 * is prefixed: 'invoice:<provider>' to namespace from comms adapters.
 *
 * Encryption uses INTEGRATION_ENCRYPTION_KEY (env) via encryptCredential /
 * decryptCredential from @zync/utils.
 *
 * Route files MUST NOT import raw Drizzle tables — they call these helpers.
 */
import { and, eq, sql, desc } from 'drizzle-orm'
import type { Db } from '../client'
import { adapterCredentials } from '../schema/communications'
import { customers } from '../schema/customers'
import { IL_INVOICE_PROVIDERS, integrationSyncLogs, type ILInvoiceProvider } from '../schema/invoice-adapters'
import { invoices } from '../schema/invoices'
import { tenants } from '../schema/tenants'
import { encryptCredential, decryptCredential } from '@zync/utils'

// ── Constants ─────────────────────────────────────────────────────────────────

/** Namespace prefix for invoice adapters in adapter_credentials.adapter_id */
function adapterKey(provider: ILInvoiceProvider): string {
  return `invoice:${provider}`
}

// ── Public interfaces ─────────────────────────────────────────────────────────

export interface AdapterConfig {
  provider: ILInvoiceProvider
  // Plain-text secrets — only returned by getAdapterConfig (never stored)
  apiKey?: string
  apiSecret?: string
  companyId?: string
  // Non-secret metadata stored in adapter_credentials.metadata
  metadata: {
    accountEmail?: string
    companyName?: string
    lastTestedAt?: string
  }
}

export interface AdapterConfigSummary {
  provider: ILInvoiceProvider
  configured: boolean
  metadata: {
    accountEmail?: string
    companyName?: string
    lastTestedAt?: string
  }
}

export interface SyncLog {
  id: string
  tenantId: string
  provider: ILInvoiceProvider
  entityType: 'invoice' | 'credit_note' | 'payment' | 'customer'
  entityId: string
  externalId: string | null
  direction: 'push' | 'pull'
  status: 'success' | 'failure' | 'skipped'
  httpStatus: number | null
  errorPayload: Record<string, unknown> | null
  durationMs: number | null
  triggeredBy: string | null
  createdAt: string
}

export interface SyncLogsPage {
  items: SyncLog[]
  nextCursor: string | null
  total: number
}

export interface InvoiceAdapterSettings {
  adapter: ILInvoiceProvider | null
  autoSyncOnTaxIssue: boolean
  autoDraftFromRetainer: boolean
  autoSendRetainerInvoice: boolean
  taskStatusTrigger: {
    enabled: boolean
    statusId: string
    invoiceType: 'draft' | 'sent'
  } | null
}

export interface InvoicePushPayload {
  invoiceNumber: string | null
  proformaNumber: string | null
  customerId: string
  total: string
  currency: string
  taxIssueDate: string | null
  issueDate: string | null
  status: string
  customerName?: string
  customerEmail?: string
}

const DEFAULT_INVOICE_ADAPTER_SETTINGS: InvoiceAdapterSettings = {
  adapter: null,
  autoSyncOnTaxIssue: false,
  autoDraftFromRetainer: false,
  autoSendRetainerInvoice: false,
  taskStatusTrigger: null,
}

// ── Credential encryption helpers ─────────────────────────────────────────────

/**
 * Encrypt a JSON secrets payload and return the three AES-GCM components.
 * Uses INTEGRATION_ENCRYPTION_KEY.
 */
async function encryptAdapterSecrets(
  secrets: Record<string, string>,
  encryptionKey: string,
): Promise<{ ciphertext: string; iv: string; authTag: string }> {
  return encryptCredential(JSON.stringify(secrets), encryptionKey)
}

async function decryptAdapterSecrets(
  blob: { ciphertext: string; iv: string; authTag: string },
  encryptionKey: string,
): Promise<Record<string, string>> {
  const json = await decryptCredential(blob, encryptionKey)
  return JSON.parse(json) as Record<string, string>
}

// ── getAdapterConfig ──────────────────────────────────────────────────────────

/**
 * Load and decrypt credentials for a specific provider/tenant.
 * Returns null if not configured.
 */
export async function getAdapterConfig(
  db: Db,
  tenantId: string,
  provider: ILInvoiceProvider,
  encryptionKey: string,
): Promise<AdapterConfig | null> {
  const [row] = await db
    .select()
    .from(adapterCredentials)
    .where(
      and(
        eq(adapterCredentials.tenantId, tenantId),
        eq(adapterCredentials.adapterId, adapterKey(provider)),
      ),
    )
    .limit(1)

  if (!row) return null

  const secrets = await decryptAdapterSecrets(
    { ciphertext: row.ciphertext, iv: row.iv, authTag: row.authTag },
    encryptionKey,
  )

  return {
    provider,
    apiKey: secrets.apiKey,
    apiSecret: secrets.apiSecret,
    companyId: secrets.companyId,
    metadata: (row.metadata as AdapterConfig['metadata']) ?? {},
  }
}

// ── upsertAdapterConfig ───────────────────────────────────────────────────────

/**
 * Encrypt and store/update credentials for a provider.
 * Metadata (non-secret display fields) is stored unencrypted alongside.
 */
export async function upsertAdapterConfig(
  db: Db,
  tenantId: string,
  provider: ILInvoiceProvider,
  secrets: { apiKey?: string; apiSecret?: string; companyId?: string },
  metadata: AdapterConfig['metadata'],
  encryptionKey: string,
): Promise<void> {
  const encrypted = await encryptAdapterSecrets(
    Object.fromEntries(
      Object.entries(secrets).filter(([, v]) => v !== undefined),
    ) as Record<string, string>,
    encryptionKey,
  )

  await db
    .insert(adapterCredentials)
    .values({
      tenantId,
      adapterId: adapterKey(provider),
      ciphertext: encrypted.ciphertext,
      iv: encrypted.iv,
      authTag: encrypted.authTag,
      metadata: metadata as Record<string, unknown>,
    })
    .onConflictDoUpdate({
      target: [adapterCredentials.tenantId, adapterCredentials.adapterId],
      set: {
        ciphertext: encrypted.ciphertext,
        iv: encrypted.iv,
        authTag: encrypted.authTag,
        metadata: metadata as Record<string, unknown>,
        updatedAt: new Date(),
      },
    })
}

// ── removeAdapterConfig ───────────────────────────────────────────────────────

export async function removeAdapterConfig(
  db: Db,
  tenantId: string,
  provider: ILInvoiceProvider,
): Promise<void> {
  await db
    .delete(adapterCredentials)
    .where(
      and(
        eq(adapterCredentials.tenantId, tenantId),
        eq(adapterCredentials.adapterId, adapterKey(provider)),
      ),
    )
}

// ── listAdapterConfigs ────────────────────────────────────────────────────────

/**
 * List which IL invoice providers are configured for a tenant.
 * Does NOT return any secrets — only metadata + configured flag.
 */
export async function listAdapterConfigs(
  db: Db,
  tenantId: string,
): Promise<AdapterConfigSummary[]> {
  const rows = await db
    .select({
      adapterId: adapterCredentials.adapterId,
      metadata: adapterCredentials.metadata,
    })
    .from(adapterCredentials)
    .where(
      and(
        eq(adapterCredentials.tenantId, tenantId),
        sql`${adapterCredentials.adapterId} LIKE 'invoice:%'`,
      ),
    )

  const configuredMap = new Map(
    rows.map((r) => [r.adapterId.replace('invoice:', '') as ILInvoiceProvider, r.metadata]),
  )

  const IL_PROVIDERS: ILInvoiceProvider[] = ['morning', 'icount', 'rivhit', 'invoice4u', 'easycount']
  return IL_PROVIDERS.map((provider) => ({
    provider,
    configured: configuredMap.has(provider),
    metadata: (configuredMap.get(provider) as AdapterConfig['metadata']) ?? {},
  }))
}

export async function getInvoiceAdapterSettings(
  db: Db,
  tenantId: string,
): Promise<InvoiceAdapterSettings> {
  const [row] = await db
    .select({ settings: tenants.settings })
    .from(tenants)
    .where(eq(tenants.id, tenantId))
    .limit(1)

  const root = (row?.settings as Record<string, unknown> | null) ?? {}
  const raw = (root.invoiceAutomation ?? {}) as Record<string, unknown>
  const adapter = raw.adapter

  return {
    adapter: IL_INVOICE_PROVIDERS.includes(adapter as ILInvoiceProvider)
      ? (adapter as ILInvoiceProvider)
      : null,
    autoSyncOnTaxIssue:
      typeof raw.autoSyncOnTaxIssue === 'boolean'
        ? raw.autoSyncOnTaxIssue
        : DEFAULT_INVOICE_ADAPTER_SETTINGS.autoSyncOnTaxIssue,
    autoDraftFromRetainer:
      typeof raw.autoDraftFromRetainer === 'boolean'
        ? raw.autoDraftFromRetainer
        : DEFAULT_INVOICE_ADAPTER_SETTINGS.autoDraftFromRetainer,
    autoSendRetainerInvoice:
      typeof raw.autoSendRetainerInvoice === 'boolean'
        ? raw.autoSendRetainerInvoice
        : DEFAULT_INVOICE_ADAPTER_SETTINGS.autoSendRetainerInvoice,
    taskStatusTrigger:
      raw.taskStatusTrigger
      && typeof raw.taskStatusTrigger === 'object'
      && typeof (raw.taskStatusTrigger as { statusId?: unknown }).statusId === 'string'
        ? {
            enabled: (raw.taskStatusTrigger as { enabled?: boolean }).enabled === true,
            statusId: (raw.taskStatusTrigger as { statusId: string }).statusId,
            invoiceType:
              (raw.taskStatusTrigger as { invoiceType?: string }).invoiceType === 'sent'
                ? 'sent'
                : 'draft',
          }
        : null,
  }
}

export async function getInvoiceForAdapterPush(
  db: Db,
  tenantId: string,
  invoiceId: string,
): Promise<InvoicePushPayload | null> {
  const [row] = await db
    .select({
      invoiceNumber: invoices.invoiceNumber,
      proformaNumber: invoices.proformaNumber,
      customerId: invoices.customerId,
      total: invoices.total,
      currency: invoices.currency,
      taxIssueDate: invoices.taxIssueDate,
      issueDate: invoices.issueDate,
      status: invoices.status,
      customerName: customers.name,
      customerEmail: customers.email,
    })
    .from(invoices)
    .leftJoin(customers, eq(customers.id, invoices.customerId))
    .where(and(eq(invoices.id, invoiceId), eq(invoices.tenantId, tenantId)))
    .limit(1)

  if (!row || !row.customerId) return null

  return {
    invoiceNumber: row.invoiceNumber,
    proformaNumber: row.proformaNumber,
    customerId: row.customerId,
    total: row.total,
    currency: row.currency,
    taxIssueDate: row.taxIssueDate,
    issueDate: row.issueDate,
    status: row.status,
    customerName: row.customerName ?? undefined,
    customerEmail: row.customerEmail ?? undefined,
  }
}

export async function updateInvoiceAdapterSettings(
  db: Db,
  tenantId: string,
  _actorId: string,
  patch: Partial<InvoiceAdapterSettings>,
): Promise<InvoiceAdapterSettings> {
  const current = await getInvoiceAdapterSettings(db, tenantId)
  const next: InvoiceAdapterSettings = {
    adapter: patch.adapter === undefined ? current.adapter : patch.adapter,
    autoSyncOnTaxIssue: patch.autoSyncOnTaxIssue ?? current.autoSyncOnTaxIssue,
    autoDraftFromRetainer: patch.autoDraftFromRetainer ?? current.autoDraftFromRetainer,
    autoSendRetainerInvoice: patch.autoSendRetainerInvoice ?? current.autoSendRetainerInvoice,
    taskStatusTrigger:
      patch.taskStatusTrigger === undefined ? current.taskStatusTrigger : patch.taskStatusTrigger,
  }

  await db
    .update(tenants)
    .set({
      settings: sql`jsonb_set(
        COALESCE(settings, '{}'::jsonb),
        '{invoiceAutomation}',
        ${JSON.stringify(next)}::jsonb
      )`,
    })
    .where(eq(tenants.id, tenantId))

  return next
}

// ── logSyncEvent ──────────────────────────────────────────────────────────────

export async function logSyncEvent(
  db: Db,
  input: {
    tenantId: string
    provider: ILInvoiceProvider
    entityType: 'invoice' | 'credit_note' | 'payment' | 'customer'
    entityId: string
    externalId?: string
    direction: 'push' | 'pull'
    status: 'success' | 'failure' | 'skipped'
    httpStatus?: number
    errorPayload?: Record<string, unknown>
    durationMs?: number
    triggeredBy?: string
  },
): Promise<void> {
  await db.insert(integrationSyncLogs).values({
    tenantId: input.tenantId,
    provider: input.provider,
    entityType: input.entityType,
    entityId: input.entityId,
    externalId: input.externalId ?? null,
    direction: input.direction,
    status: input.status,
    httpStatus: input.httpStatus ?? null,
    errorPayload: input.errorPayload ?? null,
    durationMs: input.durationMs ?? null,
    triggeredBy: input.triggeredBy ?? null,
  })
}

// ── listSyncLogs ──────────────────────────────────────────────────────────────

export async function listSyncLogs(
  db: Db,
  tenantId: string,
  opts: {
    provider?: ILInvoiceProvider
    entityType?: 'invoice' | 'credit_note' | 'payment' | 'customer'
    entityId?: string
    status?: 'success' | 'failure' | 'skipped'
    cursor?: string
    limit?: number
  } = {},
): Promise<SyncLogsPage> {
  const limit = Math.min(opts.limit ?? 50, 100)

  const conditions = [eq(integrationSyncLogs.tenantId, tenantId)]
  if (opts.provider) conditions.push(eq(integrationSyncLogs.provider, opts.provider))
  if (opts.entityType) conditions.push(eq(integrationSyncLogs.entityType, opts.entityType))
  if (opts.entityId) conditions.push(eq(integrationSyncLogs.entityId, opts.entityId))
  if (opts.status) conditions.push(eq(integrationSyncLogs.status, opts.status))

  const [countRow] = await db
    .select({ count: sql<number>`count(*)::int` })
    .from(integrationSyncLogs)
    .where(and(...conditions))
  const total = countRow?.count ?? 0

  let cursorCondition = undefined
  if (opts.cursor) {
    try {
      const decoded = JSON.parse(Buffer.from(opts.cursor, 'base64url').toString('utf8')) as { created_at: string; id: string }
      cursorCondition = sql`(${integrationSyncLogs.createdAt} < ${decoded.created_at}::timestamptz OR
        (${integrationSyncLogs.createdAt} = ${decoded.created_at}::timestamptz AND ${integrationSyncLogs.id} < ${decoded.id}::uuid))`
    } catch { /* invalid cursor — ignore */ }
  }

  const allConditions = cursorCondition ? and(...conditions, cursorCondition) : and(...conditions)

  const rows = await db
    .select()
    .from(integrationSyncLogs)
    .where(allConditions)
    .orderBy(desc(integrationSyncLogs.createdAt), desc(integrationSyncLogs.id))
    .limit(limit + 1)

  const hasMore = rows.length > limit
  const items = rows.slice(0, limit).map((r) => ({
    id: r.id,
    tenantId: r.tenantId,
    provider: r.provider as ILInvoiceProvider,
    entityType: r.entityType as 'invoice' | 'credit_note' | 'payment' | 'customer',
    entityId: r.entityId,
    externalId: r.externalId,
    direction: r.direction as 'push' | 'pull',
    status: r.status as 'success' | 'failure' | 'skipped',
    httpStatus: r.httpStatus,
    errorPayload: r.errorPayload as Record<string, unknown> | null,
    durationMs: r.durationMs,
    triggeredBy: r.triggeredBy,
    createdAt: r.createdAt.toISOString(),
  }))

  const lastItem = items[items.length - 1]
  const nextCursor = hasMore && lastItem
    ? Buffer.from(JSON.stringify({ created_at: lastItem.createdAt, id: lastItem.id })).toString('base64url')
    : null

  return { items, nextCursor, total }
}

// ── getPendingInvoices ────────────────────────────────────────────────────────

/**
 * List invoices that are eligible for push to a provider:
 *  - Status TAX_ISSUED or PAID (tax invoice already issued)
 *  - No successful push sync log for this provider in the last 24h
 *    (avoids redundant re-pushes; cron uses this to find new/updated invoices)
 *
 * Returns lightweight shape sufficient for adapter push calls.
 */
export async function getPendingInvoices(
  db: Db,
  tenantId: string,
  provider: ILInvoiceProvider,
  limit = 50,
): Promise<Array<{
  id: string
  invoiceNumber: string | null
  proformaNumber: string | null
  customerId: string
  total: string
  currency: string
  taxIssueDate: string | null
  issueDate: string | null
  status: string
}>> {
  const rows = await db.execute(sql`
    SELECT
      i.id,
      i.invoice_number,
      i.proforma_number,
      i.customer_id,
      i.total,
      i.currency,
      i.tax_issue_date,
      i.issue_date,
      i.status
    FROM invoices i
    WHERE i.tenant_id = ${tenantId}::uuid
      AND i.status IN ('TAX_ISSUED', 'PAID')
      AND NOT EXISTS (
        SELECT 1 FROM integration_sync_logs isl
        WHERE isl.tenant_id = ${tenantId}::uuid
          AND isl.provider = ${provider}
          AND isl.entity_type = 'invoice'
          AND isl.entity_id = i.id
          AND isl.status = 'success'
          AND isl.created_at > now() - interval '24 hours'
      )
    ORDER BY i.created_at ASC
    LIMIT ${limit}
  `) as unknown as Array<{
    id: string
    invoice_number: string | null
    proforma_number: string | null
    customer_id: string
    total: string
    currency: string
    tax_issue_date: string | null
    issue_date: string | null
    status: string
  }>

  return rows.map((r) => ({
    id: r.id,
    invoiceNumber: r.invoice_number,
    proformaNumber: r.proforma_number,
    customerId: r.customer_id,
    total: r.total,
    currency: r.currency,
    taxIssueDate: r.tax_issue_date,
    issueDate: r.issue_date,
    status: r.status,
  }))
}