/**
 * Movement derivation — accountant-export (wave-15, spec 2026-06-01-accountant-export).
 *
 * Derives double-entry ledger movements from Zync source documents:
 *  - TAX_ISSUED/PAID/PARTIALLY_PAID invoices (A/R debit, revenue credit)
 *  - Credit notes (reverse the same accounts)
 *  - ISSUED receipts (bank/cash debit, A/R credit)
 *  - COMPLETED expenses (expense + VAT-input debit, A/P credit; partial deductibility)
 *  - PAID payout bills (subcontractor expense debit, bank + withholding credit)
 *
 * Chart-of-accounts mappings are loaded from coa_mappings (required).
 * If no mappings exist, hasAccountantLedger returns false and the caller
 * should skip B100/B110 emission in the uniform-format export.
 */
import { eq, and, gte, lte, inArray } from '@zync/db'
import { createDb, coaMappings } from '@zync/db/queries'
import { invoices, receipts, expenses, payoutBills } from '@zync/db'
import type { Env } from '@zync/types'
import type { Movement } from '@zync/types'

import type { Db } from '@zync/db/queries'

// ── Internal helpers ──────────────────────────────────────────────────────────

type AccountMap = Map<string, string>

async function loadAccountMap(db: Db, tenantId: string): Promise<AccountMap> {
  const rows = await db
    .select()
    .from(coaMappings)
    .where(eq(coaMappings.tenantId, tenantId))
  return new Map(rows.map((r) => [r.sourceKind, r.accountCode]))
}

function mapAccount(map: AccountMap, key: string, fallback: string): string {
  return map.get(key) ?? fallback
}

function toIls(val: string | null | undefined): number {
  return Math.abs(parseFloat(val ?? '0') || 0)
}

function round2(n: number): number {
  return Math.round(n * 100) / 100
}

// ── Public API ────────────────────────────────────────────────────────────────

/**
 * Returns true when the tenant has at least one coa_mapping row, indicating
 * the chart of accounts has been configured and movements can be derived.
 */
export async function hasAccountantLedger(env: Env, tenantId: string): Promise<boolean> {
  const db = createDb(env)
  const rows = await db
    .select({ sourceKind: coaMappings.sourceKind })
    .from(coaMappings)
    .where(eq(coaMappings.tenantId, tenantId))
    .limit(1)
  return rows.length > 0
}

/**
 * Derive double-entry movements for the given period from source documents.
 * Requires coa_mappings to exist for the tenant (call hasAccountantLedger first).
 *
 * @param env      Cloudflare Worker env bindings
 * @param tenantId Tenant UUID
 * @param from     Period start date, YYYY-MM-DD (inclusive)
 * @param to       Period end date, YYYY-MM-DD (inclusive)
 */
export async function deriveMovements(
  env: Env,
  tenantId: string,
  from: string,
  to: string,
): Promise<Movement[]> {
  const db = createDb(env)
  const accountMap = await loadAccountMap(db, tenantId)

  const [invoiceRows, receiptRows, expenseRows, payoutRows] = await Promise.all([
    // TAX_ISSUED, PAID, PARTIALLY_PAID invoices within period (by taxIssueDate)
    db
      .select()
      .from(invoices)
      .where(
        and(
          eq(invoices.tenantId, tenantId),
          inArray(invoices.status, ['TAX_ISSUED', 'PAID', 'PARTIALLY_PAID']),
          gte(invoices.taxIssueDate, from),
          lte(invoices.taxIssueDate, to),
        ),
      ),
    // ISSUED receipts within period (by issuedAt date portion)
    db
      .select()
      .from(receipts)
      .where(
        and(
          eq(receipts.tenantId, tenantId),
          eq(receipts.status, 'ISSUED'),
          gte(receipts.issuedAt, new Date(from + 'T00:00:00Z')),
          lte(receipts.issuedAt, new Date(to + 'T23:59:59Z')),
        ),
      ),
    // COMPLETED expenses within period (by expenseDate)
    db
      .select()
      .from(expenses)
      .where(
        and(
          eq(expenses.tenantId, tenantId),
          eq(expenses.status, 'COMPLETED'),
          gte(expenses.expenseDate, from),
          lte(expenses.expenseDate, to),
        ),
      ),
    // PAID payout bills within period (by paidAt date portion)
    db
      .select()
      .from(payoutBills)
      .where(
        and(
          eq(payoutBills.tenantId, tenantId),
          eq(payoutBills.status, 'PAID'),
          gte(payoutBills.paidAt, new Date(from + 'T00:00:00Z')),
          lte(payoutBills.paidAt, new Date(to + 'T23:59:59Z')),
        ),
      ),
  ])

  const movements: Movement[] = []

  // ── Invoices ─────────────────────────────────────────────────────────────────
  for (const inv of invoiceRows) {
    const date = inv.taxIssueDate ?? inv.issueDate ?? from
    const isCreditNote = inv.source === 'credit_note'
    const arAccount = mapAccount(accountMap, 'ar', '1100')
    const revenueAccount = mapAccount(accountMap, 'revenue', '4000')
    const vatPayableAccount = mapAccount(accountMap, 'vat_payable', '2200')

    const subtotal = toIls(inv.subtotal)
    const total = toIls(inv.total)
    const vatAmount = total - subtotal
    const ref = inv.invoiceNumber ?? inv.id.slice(0, 8)

    if (isCreditNote) {
      // Credit note reverses the original invoice movements
      if (subtotal > 0) {
        movements.push({
          date,
          debitAccount: revenueAccount,
          creditAccount: arAccount,
          amountIls: subtotal,
          reference: ref,
          description: `זיכוי ${ref}`,
          sourceKind: 'invoice',
          sourceId: inv.id,
        })
      }
      if (vatAmount > 0) {
        movements.push({
          date,
          debitAccount: vatPayableAccount,
          creditAccount: arAccount,
          amountIls: vatAmount,
          reference: ref,
          description: `מע"מ זיכוי ${ref}`,
          sourceKind: 'invoice',
          sourceId: inv.id,
        })
      }
    } else {
      // Regular invoice: Dr A/R, Cr Revenue; Dr A/R, Cr VAT Payable
      if (subtotal > 0) {
        movements.push({
          date,
          debitAccount: arAccount,
          creditAccount: revenueAccount,
          amountIls: subtotal,
          reference: ref,
          description: `חשבונית ${ref}`,
          sourceKind: 'invoice',
          sourceId: inv.id,
        })
      }
      if (vatAmount > 0) {
        movements.push({
          date,
          debitAccount: arAccount,
          creditAccount: vatPayableAccount,
          amountIls: vatAmount,
          reference: ref,
          description: `מע"מ חשבונית ${ref}`,
          sourceKind: 'invoice',
          sourceId: inv.id,
        })
      }
    }
  }

  // ── Receipts ──────────────────────────────────────────────────────────────────
  for (const rec of receiptRows) {
    const date = rec.issuedAt
      ? rec.issuedAt.toISOString().slice(0, 10)
      : from
    const amountIls = toIls(rec.amountIls ?? rec.amount)
    const bankAccount = mapAccount(accountMap, 'bank', '1000')
    const arAccount = mapAccount(accountMap, 'ar', '1100')

    if (amountIls > 0) {
      movements.push({
        date,
        debitAccount: bankAccount,
        creditAccount: arAccount,
        amountIls,
        reference: rec.id.slice(0, 8),
        description: `קבלה ${rec.id.slice(0, 8)}`,
        sourceKind: 'receipt',
        sourceId: rec.id,
      })
    }
  }

  // ── Expenses ──────────────────────────────────────────────────────────────────
  // expenses.amount is GROSS (VAT-inclusive); non-deductible VAT stays in expense.
  for (const exp of expenseRows) {
    const date = exp.expenseDate ?? from
    const splitFactor = (exp.businessPercent ?? 100) / 100
    const amount = round2(toIls(exp.amount) * splitFactor)
    const vatAmount = round2(toIls(exp.vatAmount) * splitFactor)
    const category = exp.expenseCategory ?? 'office'

    const expenseAccount = mapAccount(accountMap, category, '5000')
    const vatInputAccount = mapAccount(accountMap, 'vat_input', '1200')
    const apAccount = mapAccount(accountMap, 'ap', '2100')

    const grossPayable = amount
    const deductionPct = exp.deductionPct ?? 100
    const deductibleVat = exp.vatDeductible
      ? round2(vatAmount * deductionPct / 100)
      : 0
    const expenseDebit = round2(grossPayable - deductibleVat)

    if (expenseDebit > 0) {
      movements.push({
        date,
        debitAccount: expenseAccount,
        creditAccount: apAccount,
        amountIls: expenseDebit,
        reference: exp.id.slice(0, 8),
        description: `הוצאה ${category} ${exp.id.slice(0, 8)}`,
        sourceKind: 'expense',
        sourceId: exp.id,
      })
    }
    if (deductibleVat > 0) {
      movements.push({
        date,
        debitAccount: vatInputAccount,
        creditAccount: apAccount,
        amountIls: deductibleVat,
        reference: exp.id.slice(0, 8),
        description: `מע"מ תשומות ${exp.id.slice(0, 8)}`,
        sourceKind: 'expense',
        sourceId: exp.id,
      })
    }
  }

  // ── Payout bills ──────────────────────────────────────────────────────────────
  // PAID bills: cash already out — Dr subcontractor expense (gross), Cr bank + withholding.
  for (const bill of payoutRows) {
    const date = bill.paidAt
      ? bill.paidAt.toISOString().slice(0, 10)
      : from
    const grossAmount = toIls(bill.amount)
    const withholdingAmount = toIls(bill.withholdingAmount)
    const netAmount =
      bill.netAmount != null && bill.netAmount !== ''
        ? toIls(bill.netAmount)
        : grossAmount - withholdingAmount

    const expenseAccount = mapAccount(accountMap, 'professional', '5010')
    const bankAccount = mapAccount(accountMap, 'bank', '1000')
    const withholdingAccount = mapAccount(accountMap, 'withholding', '2300')

    if (netAmount > 0) {
      movements.push({
        date,
        debitAccount: expenseAccount,
        creditAccount: bankAccount,
        amountIls: netAmount,
        reference: bill.id.slice(0, 8),
        description: `קבלן ${bill.id.slice(0, 8)}`,
        sourceKind: 'payout_bill',
        sourceId: bill.id,
      })
    }
    if (withholdingAmount > 0) {
      movements.push({
        date,
        debitAccount: expenseAccount,
        creditAccount: withholdingAccount,
        amountIls: withholdingAmount,
        reference: bill.id.slice(0, 8),
        description: `ניכוי מס במקור ${bill.id.slice(0, 8)}`,
        sourceKind: 'payout_bill',
        sourceId: bill.id,
      })
    }
    // bill.amount (gross) === netAmount + withholdingAmount — debits/credits balance
  }

  return movements
}