/**
 * Bad-debt write-off query helpers — bad-debt-writeoff (wave-12).
 *
 * All helpers are tenant-scoped.
 */
import { and, desc, eq, gte, inArray, lt, sql } from 'drizzle-orm'
import { z } from 'zod'
import type { Db } from '../client'
import { invoices } from '../schema/invoices'
import { badDebtVatReclaims } from '../schema/bad-debt-vat-reclaims'
import { tenantSettings } from '../schema/tenants'
import { customers } from '../schema/customers'
import { invoicePayments } from '../schema/invoice-payments'
import { auditLog } from '../schema/audit-log'
import type { BadDebtVatReclaim, BadDebtReportData, BadDebtReportInvoice, BadDebtReason, VatReclaimStatus } from '@zync/types'

// ── Zod schemas ───────────────────────────────────────────────────────────────

export const writeOffInputSchema = z.object({
  reason: z.enum(['bankruptcy', 'collection_failed', 'other']),
  note: z.string().max(2000).optional(),
  log_vat_reclaim: z.boolean(),
  write_off_date: z.string().regex(/^\d{4}-\d{2}-\d{2}$/).optional(),
})
export type WriteOffInput = z.infer<typeof writeOffInputSchema>

export const recordRecoveryInputSchema = z.object({
  amount: z.number().positive(),
  paidAt: z.string().datetime({ offset: true }).or(z.string().regex(/^\d{4}-\d{2}-\d{2}$/)),
  source: z.enum(['manual', 'bank_transfer', 'gateway', 'other']).optional().default('manual'),
  reference: z.string().max(500).optional(),
  note: z.string().max(2000).optional(),
})
export type RecordRecoveryInput = z.infer<typeof recordRecoveryInputSchema>

export const reclaimPatchInputSchema = z.object({
  registered_letter_sent_at: z.string().regex(/^\d{4}-\d{2}-\d{2}$/).optional(),
  ita_submission_date: z.string().regex(/^\d{4}-\d{2}-\d{2}$/).optional(),
  ita_reference: z.string().max(200).optional(),
  status: z.enum(['pending', 'submitted', 'approved', 'rejected', 'reversed', 'cancelled']).optional(),
  notes: z.string().max(2000).optional(),
})
export type ReclaimPatchInput = z.infer<typeof reclaimPatchInputSchema>

// ── Serializers ───────────────────────────────────────────────────────────────

function mapReclaim(row: typeof badDebtVatReclaims.$inferSelect): BadDebtVatReclaim {
  return {
    id: row.id,
    tenantId: row.tenantId,
    invoiceId: row.invoiceId,
    vatAmount: parseFloat(row.vatAmount),
    status: row.status as VatReclaimStatus,
    registeredLetterSentAt: row.registeredLetterSentAt ?? null,
    itaSubmissionDate: row.itaSubmissionDate ?? null,
    itaReference: row.itaReference ?? null,
    resolvedAt: row.resolvedAt instanceof Date ? row.resolvedAt.toISOString() : (row.resolvedAt ?? null),
    reversedAt: row.reversedAt instanceof Date ? row.reversedAt.toISOString() : (row.reversedAt ?? null),
    notes: row.notes ?? null,
    createdAt: row.createdAt instanceof Date ? row.createdAt.toISOString() : String(row.createdAt),
  }
}

// ── Write-off ─────────────────────────────────────────────────────────────────

export interface WriteOffResult {
  invoiceId: string
  vatReclaimId?: string
}

/**
 * Perform the write-off in one transaction.
 * Returns the updated invoice id and optionally a new VAT reclaim id.
 */
export async function performWriteOff(
  db: Db,
  tenantId: string,
  invoiceId: string,
  userId: string,
  input: WriteOffInput,
): Promise<WriteOffResult> {
  return db.transaction(async (tx) => {
    // Load invoice + tenant_settings in one go
    const [invoice] = await tx
      .select({
        id: invoices.id,
        status: invoices.status,
        dueDate: invoices.dueDate,
        total: invoices.total,
        amountPaid: invoices.amountPaid,
        vatAmount: invoices.vatAmount,
        invoiceNumber: invoices.invoiceNumber,
        tenantId: invoices.tenantId,
      })
      .from(invoices)
      .where(and(eq(invoices.tenantId, tenantId), eq(invoices.id, invoiceId)))
      .limit(1)

    if (!invoice) {
      throw new WriteOffNotFoundError(invoiceId)
    }

    if (!['TAX_ISSUED', 'PARTIALLY_PAID'].includes(invoice.status)) {
      throw new WriteOffIneligibleError(
        `Invoice status '${invoice.status}' is not eligible for write-off. Must be TAX_ISSUED or PARTIALLY_PAID.`,
      )
    }

    // Threshold check
    const [settings] = await tx
      .select({ badDebtThresholdDays: tenantSettings.badDebtThresholdDays })
      .from(tenantSettings)
      .where(eq(tenantSettings.tenantId, tenantId))
      .limit(1)

    const thresholdDays = settings?.badDebtThresholdDays ?? 90
    const dueDate = invoice.dueDate ? new Date(invoice.dueDate) : null
    if (dueDate) {
      const daysOverdue = Math.floor((Date.now() - dueDate.getTime()) / 86_400_000)
      if (daysOverdue < thresholdDays) {
        throw new WriteOffThresholdError(
          `Invoice is ${daysOverdue} days overdue; minimum threshold is ${thresholdDays} days.`,
        )
      }
    }

    const total = parseFloat(invoice.total)
    const amountPaid = parseFloat(invoice.amountPaid)
    const vatAmount = parseFloat(invoice.vatAmount)

    const newStatus = input.log_vat_reclaim ? 'BAD_DEBT' : 'WRITTEN_OFF'
    const badDebtAt = input.write_off_date ? new Date(input.write_off_date) : new Date()

    // Update invoice status + bad_debt columns
    await tx
      .update(invoices)
      .set({
        status: newStatus,
        badDebtAt,
        badDebtReason: input.reason,
        badDebtNote: input.note ?? null,
        updatedAt: new Date(),
      })
      .where(and(eq(invoices.tenantId, tenantId), eq(invoices.id, invoiceId)))

    let vatReclaimId: string | undefined

    if (input.log_vat_reclaim) {
      // reclaimable VAT = vat_amount * (total - amount_paid) / total
      const reclaimableVat =
        total > 0 ? Math.round((vatAmount * (total - amountPaid)) / total * 100) / 100 : 0

      const [reclaim] = await tx
        .insert(badDebtVatReclaims)
        .values({
          tenantId,
          invoiceId,
          vatAmount: String(reclaimableVat),
          status: 'pending',
        })
        .returning({ id: badDebtVatReclaims.id })

      vatReclaimId = reclaim?.id
    }

    await tx.insert(auditLog).values({
      tenantId,
      actorId: userId,
      actorType: 'user',
      entityType: 'invoice',
      entityId: invoiceId,
      action: 'invoice.write_off',
      changes: {
        status: [invoice.status, newStatus],
        reason: [null, input.reason],
        log_vat_reclaim: [null, input.log_vat_reclaim],
        reclaimable_vat: [null, input.log_vat_reclaim
          ? Math.round((vatAmount * (total - amountPaid)) / total * 100) / 100
          : null],
      },
    })

    return { invoiceId, vatReclaimId }
  })
}

// ── Recovery ──────────────────────────────────────────────────────────────────

export interface RecoveryResult {
  invoiceId: string
  status: string
  reclaimStatus?: VatReclaimStatus
}

export async function performRecovery(
  db: Db,
  tenantId: string,
  invoiceId: string,
  userId: string,
  input: RecordRecoveryInput,
): Promise<RecoveryResult> {
  return db.transaction(async (tx) => {
    const [invoice] = await tx
      .select({
        id: invoices.id,
        status: invoices.status,
        total: invoices.total,
        amountPaid: invoices.amountPaid,
      })
      .from(invoices)
      .where(and(eq(invoices.tenantId, tenantId), eq(invoices.id, invoiceId)))
      .limit(1)

    if (!invoice) throw new WriteOffNotFoundError(invoiceId)

    if (!['BAD_DEBT', 'WRITTEN_OFF'].includes(invoice.status)) {
      throw new WriteOffIneligibleError(
        `Invoice status '${invoice.status}' is not eligible for recovery. Must be BAD_DEBT or WRITTEN_OFF.`,
      )
    }

    // Insert payment row (spec-80 pattern)
    const paidAt = new Date(input.paidAt)
    await tx.insert(invoicePayments).values({
      tenantId,
      invoiceId,
      amount: String(input.amount),
      currency: 'ILS',
      paidAt,
      source: input.source ?? 'manual',
      reference: input.reference ?? null,
      recordedBy: userId,
      note: input.note ?? null,
    })

    // Recompute amount_paid + status using spec-80 CTE pattern
    const [updated] = await tx.execute(
      sql`WITH new_amount AS (
            SELECT COALESCE(SUM(amount), 0) AS total_paid
            FROM invoice_payments
            WHERE invoice_id = ${invoiceId} AND tenant_id = ${tenantId}
          )
          UPDATE invoices
          SET
            amount_paid = (SELECT total_paid FROM new_amount),
            status = CASE
              WHEN (SELECT total_paid FROM new_amount) >= total THEN 'PAID'
              WHEN (SELECT total_paid FROM new_amount) > 0 THEN 'PARTIALLY_PAID'
              ELSE status
            END,
            paid_at = CASE
              WHEN (SELECT total_paid FROM new_amount) >= total THEN now()
              ELSE NULL
            END,
            updated_at = now()
          WHERE id = ${invoiceId} AND tenant_id = ${tenantId}
          RETURNING id, status`,
    ) as unknown as Array<{ id: string; status: string }>

    const newStatus = updated?.status ?? 'PARTIALLY_PAID'

    // Reclaim reversal logic
    const [reclaim] = await tx
      .select({ id: badDebtVatReclaims.id, status: badDebtVatReclaims.status })
      .from(badDebtVatReclaims)
      .where(and(eq(badDebtVatReclaims.tenantId, tenantId), eq(badDebtVatReclaims.invoiceId, invoiceId)))
      .orderBy(desc(badDebtVatReclaims.createdAt))
      .limit(1)

    let reclaimStatus: VatReclaimStatus | undefined

    if (reclaim) {
      if (reclaim.status === 'approved') {
        await tx
          .update(badDebtVatReclaims)
          .set({ status: 'reversed', reversedAt: new Date() })
          .where(and(eq(badDebtVatReclaims.tenantId, tenantId), eq(badDebtVatReclaims.id, reclaim.id)))
        reclaimStatus = 'reversed'
      } else if (['pending', 'submitted'].includes(reclaim.status)) {
        await tx
          .update(badDebtVatReclaims)
          .set({ status: 'cancelled' })
          .where(and(eq(badDebtVatReclaims.tenantId, tenantId), eq(badDebtVatReclaims.id, reclaim.id)))
        reclaimStatus = 'cancelled'
      } else {
        reclaimStatus = reclaim.status as VatReclaimStatus
      }
    }

    await tx.insert(auditLog).values({
      tenantId,
      actorId: userId,
      actorType: 'user',
      entityType: 'invoice',
      entityId: invoiceId,
      action: 'invoice.recovery',
      changes: {
        recovered_amount: [null, input.amount],
        new_status: [null, newStatus],
        reclaim_transition: [null, reclaimStatus ?? null],
      },
    })

    return { invoiceId, status: newStatus, reclaimStatus }
  })
}

// ── Reclaim list + patch ───────────────────────────────────────────────────────

export interface ReclaimWithContext extends BadDebtVatReclaim {
  invoiceNumber: string | null
  customerName: string | null
}

export async function listReclaims(
  db: Db,
  tenantId: string,
  opts: { statuses?: string[]; year?: number; limit?: number; offset?: number },
): Promise<{ items: ReclaimWithContext[]; total: number }> {
  const { statuses, year, limit = 20, offset = 0 } = opts

  const conditions = [eq(badDebtVatReclaims.tenantId, tenantId)]

  if (statuses && statuses.length > 0) {
    conditions.push(inArray(badDebtVatReclaims.status, statuses))
  }

  if (year) {
    const yearStart = new Date(`${year}-01-01`)
    const yearEnd = new Date(`${year + 1}-01-01`)
    conditions.push(
      gte(badDebtVatReclaims.createdAt, yearStart),
      lt(badDebtVatReclaims.createdAt, yearEnd),
    )
  }

  const rows = await db
    .select({
      reclaim: badDebtVatReclaims,
      invoiceNumber: invoices.invoiceNumber,
      customerName: customers.name,
    })
    .from(badDebtVatReclaims)
    .leftJoin(invoices, eq(badDebtVatReclaims.invoiceId, invoices.id))
    .leftJoin(customers, eq(invoices.customerId, customers.id))
    .where(and(...conditions))
    .orderBy(desc(badDebtVatReclaims.createdAt))
    .limit(limit)
    .offset(offset)

  const countRows = await db
    .select({ count: sql<number>`count(*)::int` })
    .from(badDebtVatReclaims)
    .where(and(...conditions))

  return {
    items: rows.map((r) => ({
      ...mapReclaim(r.reclaim),
      invoiceNumber: r.invoiceNumber ?? null,
      customerName: r.customerName ?? null,
    })),
    total: countRows[0]?.count ?? 0,
  }
}

export async function patchReclaim(
  db: Db,
  tenantId: string,
  reclaimId: string,
  userId: string,
  input: ReclaimPatchInput,
): Promise<BadDebtVatReclaim> {
  return db.transaction(async (tx) => {
    const [existing] = await tx
      .select()
      .from(badDebtVatReclaims)
      .where(and(eq(badDebtVatReclaims.tenantId, tenantId), eq(badDebtVatReclaims.id, reclaimId)))
      .limit(1)

    if (!existing) throw new WriteOffNotFoundError(reclaimId)

    const patch: Partial<typeof badDebtVatReclaims.$inferInsert> = {}

    if (input.registered_letter_sent_at !== undefined) {
      patch.registeredLetterSentAt = input.registered_letter_sent_at
    }
    if (input.ita_submission_date !== undefined) {
      patch.itaSubmissionDate = input.ita_submission_date
    }
    if (input.ita_reference !== undefined) {
      patch.itaReference = input.ita_reference
    }
    if (input.status !== undefined) {
      patch.status = input.status
      if (['approved', 'rejected'].includes(input.status)) {
        patch.resolvedAt = new Date()
      }
    }
    if (input.notes !== undefined) {
      patch.notes = input.notes
    }

    const [updated] = await tx
      .update(badDebtVatReclaims)
      .set(patch)
      .where(and(eq(badDebtVatReclaims.tenantId, tenantId), eq(badDebtVatReclaims.id, reclaimId)))
      .returning()

    await tx.insert(auditLog).values({
      tenantId,
      actorId: userId,
      actorType: 'user',
      entityType: 'bad_debt_vat_reclaim',
      entityId: reclaimId,
      action: 'reclaim.updated',
      changes: { patch: [null, input as unknown] } as Record<string, [unknown, unknown]>,
    })

    if (!updated) throw new WriteOffNotFoundError(reclaimId)
    return mapReclaim(updated)
  })
}

// ── Report ─────────────────────────────────────────────────────────────────────

export async function getBadDebtReport(
  db: Db,
  tenantId: string,
  year: number,
): Promise<BadDebtReportData> {
  const yearStart = new Date(`${year}-01-01`)
  const yearEnd = new Date(`${year + 1}-01-01`)

  const rows = await db
    .select({
      invoice: invoices,
      customerName: customers.name,
      reclaimStatus: badDebtVatReclaims.status,
      reclaimVatAmount: badDebtVatReclaims.vatAmount,
      registeredLetterSentAt: badDebtVatReclaims.registeredLetterSentAt,
    })
    .from(invoices)
    .leftJoin(customers, eq(invoices.customerId, customers.id))
    .leftJoin(
      badDebtVatReclaims,
      and(
        eq(badDebtVatReclaims.invoiceId, invoices.id),
        eq(badDebtVatReclaims.tenantId, tenantId),
      ),
    )
    .where(
      and(
        eq(invoices.tenantId, tenantId),
        inArray(invoices.status, ['BAD_DEBT', 'WRITTEN_OFF']),
        gte(invoices.badDebtAt!, yearStart),
        lt(invoices.badDebtAt!, yearEnd),
      ),
    )
    .orderBy(desc(invoices.badDebtAt))

  let totalWrittenOff = 0
  let totalVatReclaimed = 0
  const byReason: Record<BadDebtReason, number> = {
    bankruptcy: 0,
    collection_failed: 0,
    other: 0,
  }
  const invoiceList: BadDebtReportInvoice[] = []

  for (const row of rows) {
    const total = parseFloat(row.invoice.total)
    totalWrittenOff += total

    if (row.reclaimStatus === 'approved' && row.reclaimVatAmount) {
      totalVatReclaimed += parseFloat(row.reclaimVatAmount)
    }

    const reason = (row.invoice.badDebtReason ?? 'other') as BadDebtReason
    byReason[reason] = (byReason[reason] ?? 0) + 1

    invoiceList.push({
      invoice_number: row.invoice.invoiceNumber ?? '',
      customer_name: row.customerName ?? '',
      amount: total,
      vat_amount: parseFloat(row.invoice.vatAmount),
      bad_debt_at: row.invoice.badDebtAt instanceof Date
        ? row.invoice.badDebtAt.toISOString()
        : String(row.invoice.badDebtAt ?? ''),
      vat_reclaim_status: (row.reclaimStatus as VatReclaimStatus) ?? null,
      registered_letter_sent: row.registeredLetterSentAt !== null,
    })
  }

  return {
    year,
    total_written_off: Math.round(totalWrittenOff * 100) / 100,
    total_vat_reclaimed: Math.round(totalVatReclaimed * 100) / 100,
    by_reason: byReason,
    invoices: invoiceList,
  }
}

// ── Settings helpers ──────────────────────────────────────────────────────────

export async function getBadDebtSettings(db: Db, tenantId: string) {
  const [row] = await db
    .select({ badDebtThresholdDays: tenantSettings.badDebtThresholdDays })
    .from(tenantSettings)
    .where(eq(tenantSettings.tenantId, tenantId))
    .limit(1)
  return { bad_debt_threshold_days: row?.badDebtThresholdDays ?? 90 }
}

export async function updateBadDebtSettings(
  db: Db,
  tenantId: string,
  days: number,
) {
  await db
    .update(tenantSettings)
    .set({ badDebtThresholdDays: days })
    .where(eq(tenantSettings.tenantId, tenantId))
  return { bad_debt_threshold_days: days }
}

// ── Errors ─────────────────────────────────────────────────────────────────────

export class WriteOffNotFoundError extends Error {
  constructor(id: string) {
    super(`Resource not found: ${id}`)
    this.name = 'WriteOffNotFoundError'
  }
}

export class WriteOffIneligibleError extends Error {
  constructor(msg: string) {
    super(msg)
    this.name = 'WriteOffIneligibleError'
  }
}

export class WriteOffThresholdError extends Error {
  constructor(msg: string) {
    super(msg)
    this.name = 'WriteOffThresholdError'
  }
}