/**
 * nii-advances.ts — DB queries for NII advance payments + Bituach Leumi report data.
 *
 * Spec: 2026-06-01-bituach-leumi (spec 175, wave-15)
 */
import { and, eq, sql, desc } from 'drizzle-orm'
import type { Db } from '../client'
import { niiAdvancePayments } from '../schema/nii-advance-payments'
import { invoices } from '../schema/invoices'
import { expenses } from '../schema/expenses'
import { payoutBills } from '../schema/contractors'
import type { NiiAdvancePayment } from '@zync/types'

// ── NII report aggregation ────────────────────────────────────────────────────

export interface NiiReportAggregates {
  grossRevenueNetVat: string
  deductibleExpenses: string
  contractorPayouts: string
}

/**
 * Aggregate gross revenue, deductible expenses, and contractor payouts for a year.
 */
export async function getNiiReportAggregates(
  db: Db,
  tenantId: string,
  year: number,
): Promise<NiiReportAggregates> {
  const periodFrom = `${year}-01-01`
  const periodTo   = `${year}-12-31`

  // ── Gross revenue excl. VAT (issued/paid invoices, excl. credit notes) ──
  const [incomeRow] = await db
    .select({
      total: sql<string>`COALESCE(SUM(${invoices.total}::numeric - ${invoices.vatAmount}::numeric), 0)`,
    })
    .from(invoices)
    .where(
      and(
        eq(invoices.tenantId, tenantId),
        sql`${invoices.taxIssueDate} BETWEEN ${periodFrom} AND ${periodTo}`,
        sql`${invoices.status} NOT IN ('DRAFT', 'SENT', 'VOID')`,
        sql`${invoices.source} != 'credit_note'`,
      ),
    )

  // ── Deductible expenses excl. VAT (status=COMPLETED, vat_deductible=true) ──
  const [expenseRow] = await db
    .select({
      total: sql<string>`COALESCE(SUM((${expenses.amount}::numeric - COALESCE(${expenses.vatAmount}::numeric, 0)) * ${expenses.businessPercent}::numeric / 100), 0)`,
    })
    .from(expenses)
    .where(
      and(
        eq(expenses.tenantId, tenantId),
        sql`${expenses.expenseDate} BETWEEN ${periodFrom} AND ${periodTo}`,
        eq(expenses.status, 'COMPLETED'),
        eq(expenses.vatDeductible, true),
      ),
    )

  // ── Contractor payouts (payout_bills PAID within year) ──
  const [payoutRow] = await db
    .select({
      total: sql<string>`COALESCE(SUM(${payoutBills.netAmount}::numeric), 0)`,
    })
    .from(payoutBills)
    .where(
      and(
        eq(payoutBills.tenantId, tenantId),
        eq(payoutBills.status, 'PAID'),
        sql`date_part('year', ${payoutBills.paidAt}) = ${year}`,
      ),
    )

  return {
    grossRevenueNetVat: (parseFloat(incomeRow?.total ?? '0')).toFixed(2),
    deductibleExpenses: (parseFloat(expenseRow?.total ?? '0')).toFixed(2),
    contractorPayouts:  (parseFloat(payoutRow?.total ?? '0')).toFixed(2),
  }
}

// ── NII Advance Payments CRUD ─────────────────────────────────────────────────

function rowToDto(row: typeof niiAdvancePayments.$inferSelect): NiiAdvancePayment {
  return {
    id: row.id,
    tenant_id: row.tenantId,
    user_id: row.userId,
    year: row.year,
    month: row.month,
    amount: parseFloat(row.amount),
    paid_at: row.paidAt,
    notes: row.notes ?? null,
    created_at: row.createdAt.toISOString(),
  }
}

/**
 * List all NII advance payments for a tenant/user/year.
 */
export async function listNiiAdvances(
  db: Db,
  tenantId: string,
  userId: string,
  year: number,
): Promise<NiiAdvancePayment[]> {
  const rows = await db
    .select()
    .from(niiAdvancePayments)
    .where(
      and(
        eq(niiAdvancePayments.tenantId, tenantId),
        eq(niiAdvancePayments.userId, userId),
        eq(niiAdvancePayments.year, year),
      ),
    )
    .orderBy(niiAdvancePayments.month)
  return rows.map(rowToDto)
}

/**
 * Get a single NII advance payment by ID, verifying tenant ownership.
 */
export async function getNiiAdvance(
  db: Db,
  tenantId: string,
  id: string,
): Promise<NiiAdvancePayment | null> {
  const [row] = await db
    .select()
    .from(niiAdvancePayments)
    .where(
      and(
        eq(niiAdvancePayments.id, id),
        eq(niiAdvancePayments.tenantId, tenantId),
      ),
    )
  return row ? rowToDto(row) : null
}

/**
 * Create a new NII advance payment.
 */
export async function createNiiAdvance(
  db: Db,
  tenantId: string,
  userId: string,
  input: {
    year: number
    month: number
    amount: number
    paidAt: string
    notes?: string
  },
): Promise<NiiAdvancePayment> {
  const rows = await db
    .insert(niiAdvancePayments)
    .values({
      tenantId,
      userId,
      year: input.year,
      month: input.month,
      amount: String(input.amount),
      paidAt: input.paidAt,
      notes: input.notes ?? null,
    })
    .returning()
  const row = rows[0]
  if (!row) throw new Error('Insert failed: no row returned')
  return rowToDto(row)
}

/**
 * Update an existing NII advance payment (amount, paidAt, notes).
 * userId scoping prevents cross-user IDOR within a tenant.
 */
export async function updateNiiAdvance(
  db: Db,
  tenantId: string,
  userId: string,
  id: string,
  patch: {
    amount?: number
    paidAt?: string
    notes?: string
  },
): Promise<NiiAdvancePayment | null> {
  const updates: Partial<typeof niiAdvancePayments.$inferInsert> = {}
  if (patch.amount !== undefined) updates.amount = String(patch.amount)
  if (patch.paidAt !== undefined) updates.paidAt = patch.paidAt
  if (patch.notes !== undefined) updates.notes = patch.notes

  if (Object.keys(updates).length === 0) {
    return getNiiAdvance(db, tenantId, id)
  }

  const [row] = await db
    .update(niiAdvancePayments)
    .set(updates)
    .where(
      and(
        eq(niiAdvancePayments.id, id),
        eq(niiAdvancePayments.tenantId, tenantId),
        eq(niiAdvancePayments.userId, userId),
      ),
    )
    .returning()
  return row ? rowToDto(row) : null
}

/**
 * Delete an NII advance payment. Returns true if deleted, false if not found.
 * userId scoping prevents cross-user IDOR within a tenant.
 */
export async function deleteNiiAdvance(
  db: Db,
  tenantId: string,
  userId: string,
  id: string,
): Promise<boolean> {
  const result = await db
    .delete(niiAdvancePayments)
    .where(
      and(
        eq(niiAdvancePayments.id, id),
        eq(niiAdvancePayments.tenantId, tenantId),
        eq(niiAdvancePayments.userId, userId),
      ),
    )
    .returning({ id: niiAdvancePayments.id })
  return result.length > 0
}