/**
 * payment-reconciliation query helpers — wave-12.
 *
 * All helpers are tenant-filtered.
 * Route files MUST NOT import raw Drizzle tables.
 */
import { and, eq, ilike, or, desc, asc, sql, type SQL } from 'drizzle-orm'
import type { Db } from '../client'
import { unmatchedPayments } from '../schema/unmatched-payments'
import { invoices } from '../schema/invoices'
import { customers } from '../schema/customers'
import { auditLog } from './_audit-forward'
import { recordInvoicePaymentTx } from './invoice-payments'
import type { UnmatchedPaymentObject } from '../schema/unmatched-payments'
import type { InvoicePaymentObject } from '@zync/types'

// ── Custom errors ─────────────────────────────────────────────────────────────

export class AlreadyMatchedError extends Error {
  constructor() {
    super('This payment has already been matched to an invoice.')
    this.name = 'AlreadyMatchedError'
  }
}

export class InvalidInvoiceStatusError extends Error {
  constructor(status: string) {
    super(`Invoice status ${status} is not eligible for payment matching. Must be SENT, APPROVED, TAX_ISSUED, or PARTIALLY_PAID.`)
    this.name = 'InvalidInvoiceStatusError'
  }
}

// ── Serializer ────────────────────────────────────────────────────────────────

function mapUnmatched(row: typeof unmatchedPayments.$inferSelect): UnmatchedPaymentObject {
  return {
    id: row.id,
    tenantId: row.tenantId,
    amount: row.amount,
    currency: row.currency as UnmatchedPaymentObject['currency'],
    paidAt: String(row.paidAt).slice(0, 10),
    paymentMethod: row.paymentMethod as UnmatchedPaymentObject['paymentMethod'],
    reference: row.reference ?? null,
    notes: row.notes ?? null,
    payerName: row.payerName ?? null,
    matchedToInvoiceId: row.matchedToInvoiceId ?? null,
    matchedAt: row.matchedAt ? String(row.matchedAt) : null,
    matchedBy: row.matchedBy ?? null,
    createdAt: String(row.createdAt),
  }
}

// ── Outstanding invoices ──────────────────────────────────────────────────────

export interface OutstandingInvoice {
  id: string
  invoiceNumber: string | null
  customerName: string
  customerId: string
  currency: string
  total: string
  amountPaid: string
  outstanding: string
  status: string
  dueDate: string | null
  issueDate: string | null
}

export interface OutstandingInvoicesPage {
  items: OutstandingInvoice[]
  total: number
  page: number
  perPage: number
  hasMore: boolean
}

export interface ListOutstandingOptions {
  search?: string
  sort?: 'oldest_due' | 'newest_due' | 'amount' | 'customer'
  page?: number
  perPage?: number
}

const OUTSTANDING_STATUSES = ['SENT', 'APPROVED', 'TAX_ISSUED', 'PARTIALLY_PAID'] as const

export async function listOutstandingInvoices(
  db: Db,
  tenantId: string,
  opts: ListOutstandingOptions = {},
): Promise<OutstandingInvoicesPage> {
  const { search, sort = 'oldest_due', page = 1, perPage = 25 } = opts
  const offset = (page - 1) * perPage

  // Build WHERE conditions
  const conditions: SQL[] = [
    eq(invoices.tenantId, tenantId),
    sql`${invoices.status} IN ('SENT','APPROVED','TAX_ISSUED','PARTIALLY_PAID')`,
  ]

  // Build order
  let orderBy: SQL
  switch (sort) {
    case 'newest_due':
      orderBy = desc(invoices.dueDate)
      break
    case 'amount':
      orderBy = desc(sql`(${invoices.total}::numeric - ${invoices.amountPaid}::numeric)`)
      break
    case 'customer':
      orderBy = asc(customers.name)
      break
    case 'oldest_due':
    default:
      orderBy = asc(invoices.dueDate)
      break
  }

  const baseConditions: SQL[] = [...conditions]
  if (search) {
    const searchFilter = or(
      ilike(invoices.invoiceNumber, `%${search}%`),
      ilike(customers.name, `%${search}%`),
    )
    if (searchFilter) baseConditions.push(searchFilter)
  }

  const [countResult, rows] = await Promise.all([
    db
      .select({ count: sql<number>`count(*)::int` })
      .from(invoices)
      .leftJoin(customers, eq(invoices.customerId, customers.id))
      .where(and(...baseConditions)),
    db
      .select({
        id: invoices.id,
        invoiceNumber: invoices.invoiceNumber,
        customerName: customers.name,
        customerId: invoices.customerId,
        currency: invoices.currency,
        total: invoices.total,
        amountPaid: invoices.amountPaid,
        status: invoices.status,
        dueDate: invoices.dueDate,
        issueDate: invoices.issueDate,
      })
      .from(invoices)
      .leftJoin(customers, eq(invoices.customerId, customers.id))
      .where(and(...baseConditions))
      .orderBy(orderBy)
      .limit(perPage)
      .offset(offset),
  ])

  const total = countResult[0]?.count ?? 0

  const items: OutstandingInvoice[] = rows.map((r) => {
    const totalNum = parseFloat(r.total)
    const paidNum = parseFloat(r.amountPaid)
    const outstanding = Math.max(0, totalNum - paidNum).toFixed(4)
    return {
      id: r.id,
      invoiceNumber: r.invoiceNumber ?? null,
      customerName: r.customerName ?? '',
      customerId: r.customerId ?? '',
      currency: r.currency ?? 'ILS',
      total: r.total,
      amountPaid: r.amountPaid,
      outstanding,
      status: r.status,
      dueDate: r.dueDate ? String(r.dueDate).slice(0, 10) : null,
      issueDate: r.issueDate ? String(r.issueDate).slice(0, 10) : null,
    }
  })

  return {
    items,
    total,
    page,
    perPage,
    hasMore: offset + items.length < total,
  }
}

// ── Unmatched payments CRUD ───────────────────────────────────────────────────

export interface CreateUnmatchedPaymentInput {
  amount: string
  currency?: string
  paidAt: string
  paymentMethod: string
  reference?: string | null
  payerName?: string | null
  notes?: string | null
}

export async function listUnmatchedPayments(
  db: Db,
  tenantId: string,
): Promise<UnmatchedPaymentObject[]> {
  const rows = await db
    .select()
    .from(unmatchedPayments)
    .where(eq(unmatchedPayments.tenantId, tenantId))
    .orderBy(desc(unmatchedPayments.paidAt), desc(unmatchedPayments.createdAt))

  return rows.map(mapUnmatched)
}

export async function createUnmatchedPayment(
  db: Db,
  tenantId: string,
  input: CreateUnmatchedPaymentInput,
): Promise<UnmatchedPaymentObject> {
  const [row] = await db
    .insert(unmatchedPayments)
    .values({
      tenantId,
      amount: input.amount,
      currency: input.currency ?? 'ILS',
      paidAt: input.paidAt,
      paymentMethod: input.paymentMethod,
      reference: input.reference ?? null,
      payerName: input.payerName ?? null,
      notes: input.notes ?? null,
    })
    .returning()

  return mapUnmatched(row!)
}

export async function deleteUnmatchedPayment(
  db: Db,
  tenantId: string,
  id: string,
): Promise<void> {
  const [row] = await db
    .select({ id: unmatchedPayments.id, matchedToInvoiceId: unmatchedPayments.matchedToInvoiceId })
    .from(unmatchedPayments)
    .where(and(eq(unmatchedPayments.tenantId, tenantId), eq(unmatchedPayments.id, id)))
    .limit(1)

  if (!row) throw new Error('not_found')
  if (row.matchedToInvoiceId) throw new AlreadyMatchedError()

  await db
    .delete(unmatchedPayments)
    .where(and(eq(unmatchedPayments.tenantId, tenantId), eq(unmatchedPayments.id, id)))
}

// ── Match transaction ─────────────────────────────────────────────────────────

export interface MatchResult {
  payment: InvoicePaymentObject
  unmatched: UnmatchedPaymentObject
}

/**
 * Match an unmatched payment to an invoice in a single transaction:
 * 1. Load & validate the unmatched payment (tenant-scoped, not already matched)
 * 2. Load & validate the target invoice (eligible status)
 * 3. Insert invoice_payments row (recomputes amount_paid + status via recordInvoicePaymentTx)
 * 4. Mark the unmatched payment as matched
 * 5. Write audit log entry
 */
export async function matchUnmatchedPayment(
  db: Db,
  tenantId: string,
  unmatchedId: string,
  invoiceId: string,
  userId: string,
): Promise<MatchResult> {
  return db.transaction(async (tx) => {
    // 1. Load unmatched payment
    const [unmatched] = await tx
      .select()
      .from(unmatchedPayments)
      .where(and(eq(unmatchedPayments.tenantId, tenantId), eq(unmatchedPayments.id, unmatchedId)))
      .limit(1)
      .for('update')

    if (!unmatched) throw new Error('not_found')
    if (unmatched.matchedToInvoiceId) throw new AlreadyMatchedError()

    // 2. Load invoice + validate status
    const [invoice] = await tx
      .select({ id: invoices.id, status: invoices.status, tenantId: invoices.tenantId })
      .from(invoices)
      .where(and(eq(invoices.tenantId, tenantId), eq(invoices.id, invoiceId)))
      .limit(1)
      .for('update')

    if (!invoice) throw new Error('invoice_not_found')

    const eligibleStatuses = ['SENT', 'APPROVED', 'TAX_ISSUED', 'PARTIALLY_PAID']
    if (!eligibleStatuses.includes(invoice.status)) {
      throw new InvalidInvoiceStatusError(invoice.status)
    }

    // 3. Record invoice payment (tx-aware, no nested transaction)
    const paymentInput = {
      amount: parseFloat(unmatched.amount),
      paidAt: String(unmatched.paidAt).slice(0, 10),
      source: 'bank_transfer' as const,
      reference: unmatched.reference ?? null,
      note: unmatched.notes ?? null,
    }

    const payment = await recordInvoicePaymentTx(
      tx,
      tenantId,
      invoiceId,
      userId,
      paymentInput,
      'invoice.payment_recorded',
    )

    // 4. Mark unmatched payment as matched
    const now = new Date()
    const [updatedUnmatched] = await tx
      .update(unmatchedPayments)
      .set({
        matchedToInvoiceId: invoiceId,
        matchedAt: now,
        matchedBy: userId,
      })
      .where(and(eq(unmatchedPayments.tenantId, tenantId), eq(unmatchedPayments.id, unmatchedId)))
      .returning()

    // 5. Audit log for the reconcile action
    await tx.insert(auditLog).values({
      tenantId,
      actorId: userId,
      actorType: 'user',
      entityType: 'invoice',
      entityId: invoiceId,
      action: 'reconcile.match',
      changes: {
        amount: [null, unmatched.amount],
        unmatchedPaymentId: [null, unmatchedId],
      },
    })

    return {
      payment,
      unmatched: mapUnmatched(updatedUnmatched!),
    }
  })
}

export { OUTSTANDING_STATUSES }
export type { UnmatchedPaymentObject }