/**
 * Contractor & Payout query helpers — contractor-payouts (P049, wave 7).
 *
 * All helpers are tenant-filtered; every statement carries a tenant_id WHERE.
 * Route files MUST NOT import raw Drizzle tables; they import from @zync/db/queries.
 *
 * Key invariants:
 *   - Withholding rate snapshot: at bill generation, contractor.withholding_tax_rate
 *     is resolved (NULL → statutory default from tax_rates WHERE
 *     tax_type='withholding_default' AND country_code='IL' AND effective_from <= today).
 *   - Rate per line: COALESCE(assignment.rate_override, contractor.hourly_rate).
 *   - Void guard: PAID bills cannot be voided (409).
 *   - Time entry unlocking: voiding a bill resets locked time entries to 'approved'.
 */
import { and, eq, desc, asc, sql, isNotNull, isNull, inArray, lte, or, count } from 'drizzle-orm'
import { z } from 'zod'
import type { Db, DbTx } from '../client'
import { ilikeSubstringPattern } from '../utils/escape-like'
import {
  contractors,
  contractorAssignments,
  payoutBills,
  payoutBillLines,
  withholdingTaxCertificates,
} from '../schema/contractors'
import { vendors } from '../schema/vendors'
import { expenses } from '../schema/expenses'
import { timeEntries } from '../schema/time'
import { taxRates } from '../schema/tax'
import { projects } from '../schema/projects'
import { tasks } from '../schema/tasks'
import { assertTenantOwnsOrThrow, assertTenantOwnsProject } from './tenant-guards'
import type {
  ContractorRow,
  NewContractor,
  ContractorAssignmentRow,
  PayoutBillRow,
  NewPayoutBill,
  PayoutBillLineRow,
  WithholdingTaxCertificateRow,
} from '../schema/contractors'
import { auditLog } from './_audit-forward'

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

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

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

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

const PAYOUT_LINE_TOTAL_TOLERANCE = 0.005

export type PayoutBillLineBounds = {
  billingType: 'hourly' | 'fixed' | 'retainer'
  agreedAmount: number | null
  hourlyLinesTotal: number
}

/**
 * Resolve payout bill line total server-side.
 * Hourly lines (hours + rate present) must match hours×rate; fixed-fee lines omit both.
 * Fixed-fee amounts are capped against contractor agreed rate (S9-i2-001).
 */
export function resolvePayoutBillLineTotal(
  line: {
    hours?: string | null
    rate?: string | null
    lineTotal: string
  },
  bounds?: PayoutBillLineBounds,
): string {
  const hasHours = line.hours != null && line.hours !== ''
  const hasRate = line.rate != null && line.rate !== ''

  if (hasHours !== hasRate) {
    throw new PayoutBillConflictError('hours and rate must both be provided or both omitted')
  }

  if (hasHours && hasRate) {
    const computed = Math.round(parseFloat(line.hours!) * parseFloat(line.rate!) * 100) / 100
    const client = parseFloat(line.lineTotal)
    if (Math.abs(computed - client) > PAYOUT_LINE_TOTAL_TOLERANCE) {
      throw new PayoutBillConflictError(
        `lineTotal ${line.lineTotal} does not match hours×rate (${computed.toFixed(2)})`,
      )
    }
    return computed.toFixed(2)
  }

  const client = parseFloat(line.lineTotal)
  if (bounds) {
    let maxFixedFee: number | null = null
    if (bounds.billingType === 'fixed' || bounds.billingType === 'retainer') {
      // Fixed/retainer contractors may have no hourlyRate — per-bill fixed fees are uncapped then.
      if (bounds.agreedAmount != null && bounds.agreedAmount > 0) {
        maxFixedFee = bounds.agreedAmount
      }
    } else if (bounds.hourlyLinesTotal > 0) {
      maxFixedFee = bounds.hourlyLinesTotal
    } else if (bounds.agreedAmount != null && bounds.agreedAmount > 0) {
      maxFixedFee = bounds.agreedAmount
    } else {
      throw new PayoutBillConflictError(
        'Fixed-fee lines require hourly lines or a contractor agreed rate on the bill',
      )
    }

    if (maxFixedFee != null && client > maxFixedFee + PAYOUT_LINE_TOTAL_TOLERANCE) {
      throw new PayoutBillConflictError(
        `lineTotal ${line.lineTotal} exceeds contractor agreed amount (${maxFixedFee.toFixed(2)})`,
      )
    }
  }

  return client.toFixed(2)
}

// ── Zod schemas (re-exported for routes) ──────────────────────────────────────

export const createContractorSchema = z.object({
  name: z.string().min(1).max(255),
  email: z.string().email().optional().nullable(),
  phone: z.string().max(50).optional().nullable(),
  taxId: z.string().max(50).optional().nullable(),
  billingType: z.enum(['hourly', 'fixed', 'retainer']).optional().default('hourly'),
  hourlyRate: z.string().regex(/^\d+(\.\d{1,2})?$/).optional().nullable(),
  currency: z.string().length(3).optional().default('ILS'),
  notes: z.string().max(5000).optional().nullable(),
  userId: z.string().uuid().optional().nullable(),
})

export const updateContractorSchema = z.object({
  name: z.string().min(1).max(255).optional(),
  email: z.string().email().optional().nullable(),
  phone: z.string().max(50).optional().nullable(),
  taxId: z.string().max(50).optional().nullable(),
  billingType: z.enum(['hourly', 'fixed', 'retainer']).optional(),
  hourlyRate: z.string().regex(/^\d+(\.\d{1,2})?$/).optional().nullable(),
  currency: z.string().length(3).optional(),
  notes: z.string().max(5000).optional().nullable(),
  userId: z.string().uuid().optional().nullable(),
  active: z.boolean().optional(),
  // Withholding certificate fields
  withholdingTaxRate: z.string().regex(/^(0(\.\d{1,4})?|1(\.0{1,4})?)$/).optional().nullable(),
  withholdingCertificateNumber: z.string().max(100).optional().nullable(),
  withholdingCertificateExpiry: z.string().regex(/^\d{4}-\d{2}-\d{2}$/).optional().nullable(),
  withholdingCertificateR2Key: z.string().max(500).optional().nullable(),
})

export const listContractorsSchema = z.object({
  cursor: z.string().optional(),
  limit: z.coerce.number().int().min(1).max(100).default(50),
  active: z.enum(['true', 'false', 'all']).optional().default('true'),
  projectId: z.string().uuid().optional(),
  search: z.string().max(200).optional(),
})

export const createAssignmentSchema = z.object({
  projectId: z.string().uuid(),
  role: z.string().max(200).optional().nullable(),
  rateOverride: z.string().regex(/^\d+(\.\d{1,2})?$/).optional().nullable(),
})

export const updateAssignmentSchema = z.object({
  role: z.string().max(200).optional().nullable(),
  rateOverride: z.string().regex(/^\d+(\.\d{1,2})?$/).optional().nullable(),
})

export const generateBillSchema = z.object({
  periodStart: z.string().regex(/^\d{4}-\d{2}-\d{2}$/),
  periodEnd: z.string().regex(/^\d{4}-\d{2}-\d{2}$/),
  notes: z.string().max(5000).optional().nullable(),
  // Optional: if true, include non-billable entries (unusual but supported)
  includeNonBillable: z.boolean().optional().default(false),
})

export const updateBillSchema = z.object({
  status: z.enum(['DRAFT', 'SENT', 'APPROVED', 'PAID']).optional(),
  notes: z.string().max(5000).optional().nullable(),
  paidAt: z.string().optional().nullable(),
  paymentMethod: z.enum(['bank_transfer', 'check', 'other']).optional().nullable(),
  paymentReference: z.string().max(500).optional().nullable(),
  lines: z
    .array(
      z.object({
        id: z.string().uuid().optional(), // existing line id (omit to add new)
        timeEntryId: z.string().uuid().optional().nullable(),
        description: z.string().min(1).max(2000),
        hours: z.string().regex(/^\d+(\.\d{1,2})?$/).optional().nullable(),
        rate: z.string().regex(/^\d+(\.\d{1,2})?$/).optional().nullable(),
        lineTotal: z.string().regex(/^\d+(\.\d{1,2})?$/),
        projectId: z.string().uuid().optional().nullable(),
        position: z.string().optional(),
      }),
    )
    .optional(),
})

export const voidBillSchema = z.object({
  reason: z.string().min(1).max(2000),
})

export const listPayoutsSchema = z.object({
  cursor: z.string().optional(),
  limit: z.coerce.number().int().min(1).max(100).default(50),
  status: z.enum(['DRAFT', 'SENT', 'APPROVED', 'PAID', 'VOID']).optional(),
  contractorId: z.string().uuid().optional(),
  periodFrom: z.string().regex(/^\d{4}-\d{2}-\d{2}$/).optional(),
  periodTo: z.string().regex(/^\d{4}-\d{2}-\d{2}$/).optional(),
})

export const listTimeForContractorSchema = z.object({
  period: z.string().regex(/^\d{4}-\d{2}$/).optional(), // YYYY-MM
  dateFrom: z.string().regex(/^\d{4}-\d{2}-\d{2}$/).optional(),
  dateTo: z.string().regex(/^\d{4}-\d{2}-\d{2}$/).optional(),
  projectId: z.string().uuid().optional(),
  cursor: z.string().optional(),
  limit: z.coerce.number().int().min(1).max(100).default(50),
})

export const withholdingReportSchema = z.object({
  year: z.coerce.number().int().min(2020).max(2100),
})

// ── Cursor helpers ─────────────────────────────────────────────────────────────

function encodeCursor(createdAt: Date, id: string): string {
  const payload = JSON.stringify({ createdAt: createdAt.toISOString(), id })
  return Buffer.from(payload).toString('base64url')
}

function decodeCursor(cursor: string): { createdAt: string; id: string } | null {
  try {
    const raw = Buffer.from(cursor, 'base64url').toString('utf8')
    const parsed = JSON.parse(raw) as { createdAt: string; id: string }
    if (typeof parsed.createdAt !== 'string' || typeof parsed.id !== 'string') return null
    return parsed
  } catch {
    return null
  }
}

// ── Statutory withholding default ─────────────────────────────────────────────

/**
 * Resolve the statutory withholding default rate for Israel.
 * Falls back to 0.30 (30%) if no row found.
 */
async function resolveStatutoryWithholdingRate(db: Db, billDate: Date): Promise<string> {
  const iso = billDate.toISOString().slice(0, 10)
  const rows = await db
    .select({ rate: taxRates.rate })
    .from(taxRates)
    .where(
      and(
        eq(taxRates.countryCode, 'IL'),
        eq(taxRates.taxType, 'withholding_default'),
        lte(taxRates.effectiveFrom, iso),
      ),
    )
    .orderBy(desc(taxRates.effectiveFrom))
    .limit(1)

  return rows[0]?.rate ?? '0.3000'
}

// ── Contractors CRUD ──────────────────────────────────────────────────────────

export async function listContractors(
  db: Db,
  tenantId: string,
  params: z.infer<typeof listContractorsSchema>,
) {
  const limit = params.limit
  const cursor = params.cursor ? decodeCursor(params.cursor) : null

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

  if (params.active !== 'all') {
    conditions.push(eq(contractors.active, params.active === 'true'))
  }

  if (params.search) {
    const pattern = ilikeSubstringPattern(params.search)
    conditions.push(
      sql`(${contractors.name} ILIKE ${pattern} OR ${contractors.email} ILIKE ${pattern} OR ${contractors.taxId} ILIKE ${pattern})`,
    )
  }

  // Cursor pagination: created_at DESC, id DESC
  if (cursor) {
    conditions.push(
      sql`(${contractors.createdAt}, ${contractors.id}) < (${cursor.createdAt}::timestamptz, ${cursor.id}::uuid)`,
    )
  }

  // projectId filter: inner join to contractor_assignments
  const monthHoursSql = sql<number>`
    COALESCE(
      (
        SELECT SUM(COALESCE(${timeEntries.durationSeconds}, 0)) / 3600.0
        FROM ${timeEntries}
        WHERE ${timeEntries.tenantId} = ${tenantId}
          AND ${timeEntries.contractorId} = ${contractors.id}
          AND DATE_TRUNC('month', ${timeEntries.startedAt} AT TIME ZONE 'UTC')
            = DATE_TRUNC('month', NOW() AT TIME ZONE 'UTC')
      ),
      0
    )
  `

  let rows: Array<ContractorRow & { activeProjects: number; monthHours: number }>
  if (params.projectId) {
    rows = await db
      .select({
        contractors,
        activeProjects: count(contractorAssignments.projectId),
        monthHours: monthHoursSql,
      })
      .from(contractors)
      .innerJoin(
        contractorAssignments,
        and(
          eq(contractorAssignments.tenantId, tenantId),
          eq(contractorAssignments.contractorId, contractors.id),
          eq(contractorAssignments.projectId, params.projectId),
        ),
      )
      .where(and(...conditions))
      .groupBy(contractors.id)
      .orderBy(desc(contractors.createdAt), desc(contractors.id))
      .limit(limit + 1)
      .then((r) => r.map((row) => ({ ...row.contractors, activeProjects: row.activeProjects, monthHours: row.monthHours })))
  } else {
    rows = await db
      .select({
        contractors,
        activeProjects: count(contractorAssignments.projectId),
        monthHours: monthHoursSql,
      })
      .from(contractors)
      .leftJoin(
        contractorAssignments,
        and(
          eq(contractorAssignments.tenantId, tenantId),
          eq(contractorAssignments.contractorId, contractors.id),
        ),
      )
      .where(and(...conditions))
      .groupBy(contractors.id)
      .orderBy(desc(contractors.createdAt), desc(contractors.id))
      .limit(limit + 1)
      .then((r) => r.map((row) => ({ ...row.contractors, activeProjects: row.activeProjects, monthHours: row.monthHours })))
  }

  const hasMore = rows.length > limit
  const items = hasMore ? rows.slice(0, limit) : rows

  return {
    items,
    nextCursor:
      hasMore && items.length > 0
        ? encodeCursor(items[items.length - 1]!.createdAt, items[items.length - 1]!.id)
        : null,
    hasMore,
  }
}

export async function getContractor(
  db: Db,
  tenantId: string,
  contractorId: string,
): Promise<ContractorRow> {
  const rows = await db
    .select()
    .from(contractors)
    .where(and(eq(contractors.tenantId, tenantId), eq(contractors.id, contractorId)))
    .limit(1)

  if (rows.length === 0) throw new ContractorNotFoundError(contractorId)
  return rows[0]!
}

export async function createContractor(
  db: Db,
  tenantId: string,
  input: z.infer<typeof createContractorSchema>,
): Promise<ContractorRow> {
  const rows = await db
    .insert(contractors)
    .values({
      tenantId,
      name: input.name,
      email: input.email ?? null,
      phone: input.phone ?? null,
      taxId: input.taxId ?? null,
      billingType: input.billingType ?? 'hourly',
      hourlyRate: input.hourlyRate ?? null,
      currency: input.currency ?? 'ILS',
      notes: input.notes ?? null,
    })
    .returning()

  return rows[0]!
}

export async function updateContractor(
  db: Db,
  tenantId: string,
  contractorId: string,
  input: z.infer<typeof updateContractorSchema>,
): Promise<ContractorRow> {
  // Verify exists + ownership
  await getContractor(db, tenantId, contractorId)

  const updates: Partial<NewContractor> = {
    updatedAt: new Date(),
  }

  if (input.name !== undefined) updates.name = input.name
  if (input.email !== undefined) updates.email = input.email
  if (input.phone !== undefined) updates.phone = input.phone
  if (input.taxId !== undefined) updates.taxId = input.taxId
  if (input.billingType !== undefined) updates.billingType = input.billingType
  if (input.hourlyRate !== undefined) updates.hourlyRate = input.hourlyRate
  if (input.currency !== undefined) updates.currency = input.currency
  if (input.notes !== undefined) updates.notes = input.notes
  if (input.userId !== undefined) updates.userId = input.userId
  if (input.active !== undefined) updates.active = input.active
  if (input.withholdingTaxRate !== undefined) updates.withholdingTaxRate = input.withholdingTaxRate
  if (input.withholdingCertificateNumber !== undefined)
    updates.withholdingCertificateNumber = input.withholdingCertificateNumber
  if (input.withholdingCertificateExpiry !== undefined)
    updates.withholdingCertificateExpiry = input.withholdingCertificateExpiry
  if (input.withholdingCertificateR2Key !== undefined)
    updates.withholdingCertificateR2Key = input.withholdingCertificateR2Key

  const rows = await db
    .update(contractors)
    .set(updates)
    .where(and(eq(contractors.tenantId, tenantId), eq(contractors.id, contractorId)))
    .returning()

  return rows[0]!
}

export async function deactivateContractor(
  db: Db,
  tenantId: string,
  contractorId: string,
): Promise<ContractorRow> {
  await getContractor(db, tenantId, contractorId)

  const rows = await db
    .update(contractors)
    .set({ active: false, updatedAt: new Date() })
    .where(and(eq(contractors.tenantId, tenantId), eq(contractors.id, contractorId)))
    .returning()

  return rows[0]!
}

// ── Contractor Assignments ────────────────────────────────────────────────────

export async function listAssignments(
  db: Db,
  tenantId: string,
  contractorId: string,
): Promise<Array<ContractorAssignmentRow & { projectName: string | null }>> {
  return db
    .select({
      id: contractorAssignments.id,
      tenantId: contractorAssignments.tenantId,
      contractorId: contractorAssignments.contractorId,
      projectId: contractorAssignments.projectId,
      role: contractorAssignments.role,
      rateOverride: contractorAssignments.rateOverride,
      createdAt: contractorAssignments.createdAt,
      projectName: projects.name,
    })
    .from(contractorAssignments)
    .leftJoin(projects, eq(projects.id, contractorAssignments.projectId))
    .where(
      and(
        eq(contractorAssignments.tenantId, tenantId),
        eq(contractorAssignments.contractorId, contractorId),
      ),
    )
    .orderBy(asc(contractorAssignments.createdAt))
}

export async function createAssignment(
  db: Db,
  tenantId: string,
  contractorId: string,
  input: z.infer<typeof createAssignmentSchema>,
): Promise<ContractorAssignmentRow> {
  // Verify contractor exists + belongs to tenant
  await getContractor(db, tenantId, contractorId)

  assertTenantOwnsOrThrow(
    'project_id',
    await assertTenantOwnsProject(db, tenantId, input.projectId),
  )

  const rows = await db
    .insert(contractorAssignments)
    .values({
      tenantId,
      contractorId,
      projectId: input.projectId,
      role: input.role ?? null,
      rateOverride: input.rateOverride ?? null,
    })
    .returning()

  return rows[0]!
}

export async function updateAssignment(
  db: Db,
  tenantId: string,
  contractorId: string,
  assignmentId: string,
  input: z.infer<typeof updateAssignmentSchema>,
): Promise<ContractorAssignmentRow> {
  const rows = await db
    .update(contractorAssignments)
    .set({
      role: input.role,
      rateOverride: input.rateOverride,
    })
    .where(
      and(
        eq(contractorAssignments.tenantId, tenantId),
        eq(contractorAssignments.contractorId, contractorId),
        eq(contractorAssignments.id, assignmentId),
      ),
    )
    .returning()

  if (rows.length === 0) throw new ContractorNotFoundError(assignmentId)
  return rows[0]!
}

export async function deleteAssignment(
  db: Db,
  tenantId: string,
  contractorId: string,
  assignmentId: string,
): Promise<void> {
  await db
    .delete(contractorAssignments)
    .where(
      and(
        eq(contractorAssignments.tenantId, tenantId),
        eq(contractorAssignments.contractorId, contractorId),
        eq(contractorAssignments.id, assignmentId),
      ),
    )
}

// ── Contractor Time Entries ───────────────────────────────────────────────────

export async function listContractorTimeEntries(
  db: Db,
  tenantId: string,
  contractorId: string,
  params: z.infer<typeof listTimeForContractorSchema>,
) {
  const conditions = [
    eq(timeEntries.tenantId, tenantId),
  ]

  const contractor = await getContractor(db, tenantId, contractorId)
  if (contractor.userId) {
    conditions.push(or(eq(timeEntries.contractorId, contractorId), eq(timeEntries.userId, contractor.userId))!)
  } else {
    conditions.push(eq(timeEntries.contractorId, contractorId))
  }

  // Support YYYY-MM period shorthand
  if (params.period) {
    const [year, month] = params.period.split('-')
    const from = `${year}-${month}-01`
    const lastDay = new Date(Number(year), Number(month), 0).getDate()
    const to = `${year}-${month}-${String(lastDay).padStart(2, '0')}`
    conditions.push(
      sql`DATE(${timeEntries.startedAt} AT TIME ZONE 'UTC') >= ${from}::date`,
      sql`DATE(${timeEntries.startedAt} AT TIME ZONE 'UTC') <= ${to}::date`,
    )
  } else {
    if (params.dateFrom) {
      conditions.push(
        sql`DATE(${timeEntries.startedAt} AT TIME ZONE 'UTC') >= ${params.dateFrom}::date`,
      )
    }
    if (params.dateTo) {
      conditions.push(
        sql`DATE(${timeEntries.startedAt} AT TIME ZONE 'UTC') <= ${params.dateTo}::date`,
      )
    }
  }

  if (params.projectId) {
    conditions.push(eq(timeEntries.projectId, params.projectId))
  }

  const limit = params.limit
  if (params.cursor) {
    const cur = decodeCursor(params.cursor)
    if (cur) {
      conditions.push(
        sql`(${timeEntries.startedAt}, ${timeEntries.id}) < (${cur.createdAt}::timestamptz, ${cur.id}::uuid)`,
      )
    }
  }

  const rows = await db
    .select({
      id: timeEntries.id,
      tenantId: timeEntries.tenantId,
      userId: timeEntries.userId,
      contractorId: timeEntries.contractorId,
      taskId: timeEntries.taskId,
      projectId: timeEntries.projectId,
      description: timeEntries.description,
      startedAt: timeEntries.startedAt,
      stoppedAt: timeEntries.stoppedAt,
      durationSeconds: timeEntries.durationSeconds,
      source: timeEntries.source,
      billable: timeEntries.billable,
      approvalStatus: timeEntries.approvalStatus,
      lockedAt: timeEntries.lockedAt,
      lockedBy: timeEntries.lockedBy,
      lockedReason: timeEntries.lockedReason,
      invoiceId: timeEntries.invoiceId,
      billedAt: timeEntries.billedAt,
      submittedAt: timeEntries.submittedAt,
      approvedAt: timeEntries.approvedAt,
      approvedBy: timeEntries.approvedBy,
      rejectedAt: timeEntries.rejectedAt,
      rejectionReason: timeEntries.rejectionReason,
      createdAt: timeEntries.createdAt,
      updatedAt: timeEntries.updatedAt,
      projectName: projects.name,
      taskTitle: tasks.title,
    })
    .from(timeEntries)
    .leftJoin(projects, eq(projects.id, timeEntries.projectId))
    .leftJoin(tasks, eq(tasks.id, timeEntries.taskId))
    .where(and(...conditions))
    .orderBy(desc(timeEntries.startedAt), desc(timeEntries.id))
    .limit(limit + 1)

  const hasMore = rows.length > limit
  const items = hasMore ? rows.slice(0, limit) : rows

  // Summary: total hours + estimated amount
  const totalSeconds = items.reduce((sum, e) => sum + (e.durationSeconds ?? 0), 0)
  const approvedSeconds = items.reduce(
    (sum, e) =>
      sum
      + ((e.approvalStatus === 'approved' || e.approvalStatus === 'auto_approved' || e.approvalStatus === 'locked')
        ? (e.durationSeconds ?? 0)
        : 0),
    0,
  )
  const pendingSeconds = items.reduce(
    (sum, e) => sum + (e.approvalStatus === 'pending' ? (e.durationSeconds ?? 0) : 0),
    0,
  )
  const totalHours = totalSeconds / 3600

  return {
    items,
    totalHours,
    approvedHours: approvedSeconds / 3600,
    pendingHours: pendingSeconds / 3600,
    nextCursor:
      hasMore && items.length > 0
        ? encodeCursor(items[items.length - 1]!.startedAt, items[items.length - 1]!.id)
        : null,
    hasMore,
  }
}

// ── Payout Bills ──────────────────────────────────────────────────────────────

export async function listPayoutBills(
  db: Db,
  tenantId: string,
  contractorId: string,
  params: { cursor?: string; limit?: number },
) {
  const limit = params.limit ?? 50
  const cursor = params.cursor ? decodeCursor(params.cursor) : null

  const conditions = [
    eq(payoutBills.tenantId, tenantId),
    eq(payoutBills.contractorId, contractorId),
  ]

  if (cursor) {
    conditions.push(
      sql`(${payoutBills.createdAt}, ${payoutBills.id}) < (${cursor.createdAt}::timestamptz, ${cursor.id}::uuid)`,
    )
  }

  const rows = await db
    .select()
    .from(payoutBills)
    .where(and(...conditions))
    .orderBy(desc(payoutBills.createdAt), desc(payoutBills.id))
    .limit(limit + 1)

  const hasMore = rows.length > limit
  const items = hasMore ? rows.slice(0, limit) : rows

  return {
    items,
    nextCursor:
      hasMore && items.length > 0
        ? encodeCursor(items[items.length - 1]!.createdAt, items[items.length - 1]!.id)
        : null,
    hasMore,
  }
}

export async function getPayoutBillWithLines(
  db: Db,
  tenantId: string,
  contractorId: string,
  billId: string,
) {
  const billRows = await db
    .select()
    .from(payoutBills)
    .where(
      and(
        eq(payoutBills.tenantId, tenantId),
        eq(payoutBills.contractorId, contractorId),
        eq(payoutBills.id, billId),
      ),
    )
    .limit(1)

  if (billRows.length === 0) throw new PayoutBillNotFoundError(billId)

  const lines = await db
    .select()
    .from(payoutBillLines)
    .where(and(eq(payoutBillLines.billId, billId), eq(payoutBillLines.tenantId, tenantId)))
    .orderBy(asc(payoutBillLines.position), asc(payoutBillLines.id))

  return { bill: billRows[0]!, lines }
}

/**
 * Generate a draft payout bill from approved time entries for a period.
 *
 * Rate resolution per line:
 *   COALESCE(contractor_assignments.rate_override, contractors.hourly_rate)
 *
 * Withholding:
 *   1. If contractor.withholding_tax_rate IS NOT NULL → use it
 *   2. Else → resolve statutory default from tax_rates (IL, withholding_default, today)
 *   3. Snapshot resolved rate into payout_bills.withholding_rate
 *   4. withholding_amount = ROUND(amount × rate, 2)
 *   5. net_amount = amount - withholding_amount
 *
 * Returns the created bill with lines and a flag indicating whether the statutory
 * default was applied (for UI warning display).
 */
export async function generatePayoutBillDraft(
  db: Db,
  tenantId: string,
  contractorId: string,
  createdBy: string,
  input: z.infer<typeof generateBillSchema>,
): Promise<{ bill: PayoutBillRow; lines: PayoutBillLineRow[]; usedStatutoryDefault: boolean }> {
  // Validate contractor
  const contractor = await getContractor(db, tenantId, contractorId)

  // Load approved time entries for the period
  const entryConditions = [
    eq(timeEntries.tenantId, tenantId),
    sql`DATE(${timeEntries.startedAt} AT TIME ZONE 'UTC') >= ${input.periodStart}::date`,
    sql`DATE(${timeEntries.startedAt} AT TIME ZONE 'UTC') <= ${input.periodEnd}::date`,
    // Only approved entries (not already locked to another bill)
    sql`${timeEntries.approvalStatus} IN ('approved', 'auto_approved')`,
    sql`${timeEntries.lockedAt} IS NULL`,
  ]

  if (contractor.userId) {
    entryConditions.push(or(eq(timeEntries.contractorId, contractorId), eq(timeEntries.userId, contractor.userId))!)
  } else {
    entryConditions.push(eq(timeEntries.contractorId, contractorId))
  }

  if (!input.includeNonBillable) {
    entryConditions.push(eq(timeEntries.billable, true))
  }

  const entries = await db
    .select()
    .from(timeEntries)
    .where(and(...entryConditions))
    .orderBy(asc(timeEntries.startedAt))

  // Load assignments to find rate overrides per project
  const assignments = await db
    .select()
    .from(contractorAssignments)
    .where(
      and(
        eq(contractorAssignments.tenantId, tenantId),
        eq(contractorAssignments.contractorId, contractorId),
      ),
    )

  const assignmentByProject = new Map<string, ContractorAssignmentRow>()
  for (const a of assignments) {
    assignmentByProject.set(a.projectId, a)
  }

  // Resolve withholding rate
  let resolvedWithholdingRate: string
  let usedStatutoryDefault = false

  if (contractor.withholdingTaxRate !== null && contractor.withholdingTaxRate !== undefined) {
    resolvedWithholdingRate = contractor.withholdingTaxRate
  } else {
    resolvedWithholdingRate = await resolveStatutoryWithholdingRate(db, new Date(input.periodEnd))
    usedStatutoryDefault = true
  }

  // Build lines
  type LineSpec = {
    timeEntryId: string | null
    description: string
    hours: string | null
    rate: string | null
    lineTotal: string
    projectId: string | null
    position: string
  }

  const lineSpecs: LineSpec[] = []
  let totalHours = 0
  let totalAmount = 0

  for (let i = 0; i < entries.length; i++) {
    const entry = entries[i]!
    const hours = (entry.durationSeconds ?? 0) / 3600
    totalHours += hours

    // Rate: project-override → contractor default → 0
    const assignment = assignmentByProject.get(entry.projectId)
    const rate = parseFloat(
      assignment?.rateOverride ?? contractor.hourlyRate ?? '0',
    )
    const lineTotal = Math.round(hours * rate * 100) / 100
    totalAmount += lineTotal

    lineSpecs.push({
      timeEntryId: entry.id,
      description: entry.description ?? `Time entry ${new Date(entry.startedAt).toISOString().slice(0, 10)}`,
      hours: hours.toFixed(2),
      rate: rate.toFixed(2),
      lineTotal: lineTotal.toFixed(2),
      projectId: entry.projectId,
      position: String(i),
    })
  }

  const amount = totalAmount.toFixed(2)
  const withholdingRate = resolvedWithholdingRate
  const withholdingAmount = (Math.round(totalAmount * parseFloat(withholdingRate) * 100) / 100).toFixed(2)
  const netAmount = (totalAmount - parseFloat(withholdingAmount)).toFixed(2)

  // Insert bill + lines in one transaction
  return db.transaction(async (tx: DbTx) => {
    const billRows = await tx
      .insert(payoutBills)
      .values({
        tenantId,
        contractorId,
        periodStart: input.periodStart,
        periodEnd: input.periodEnd,
        status: 'DRAFT',
        totalHours: totalHours.toFixed(2),
        amount,
        currency: contractor.currency,
        withholdingRate,
        withholdingAmount,
        netAmount,
        notes: input.notes ?? null,
        createdBy,
      })
      .returning()

    const bill = billRows[0]!

    const insertedLines: PayoutBillLineRow[] = []

    if (lineSpecs.length > 0) {
      const lineRows = await tx
        .insert(payoutBillLines)
        .values(
          lineSpecs.map((ls) => ({
            billId: bill.id,
            tenantId,
            timeEntryId: ls.timeEntryId,
            description: ls.description,
            hours: ls.hours,
            rate: ls.rate,
            lineTotal: ls.lineTotal,
            projectId: ls.projectId,
            position: ls.position,
          })),
        )
        .returning()
      insertedLines.push(...lineRows)
    }

    // Lock time entries to this bill
    if (entries.length > 0) {
      await tx
        .update(timeEntries)
        .set({
          approvalStatus: 'locked',
          lockedAt: new Date(),
          lockedReason: 'approved',
        })
        .where(
          and(
            eq(timeEntries.tenantId, tenantId),
            inArray(
              timeEntries.id,
              entries.map((e) => e.id),
            ),
          ),
        )
    }

    await tx.insert(auditLog).values({
      tenantId,
      actorId: createdBy,
      actorType: 'user',
      entityType: 'payout_bill',
      entityId: bill.id,
      action: 'payout_bill.created',
    })

    return { bill, lines: insertedLines, usedStatutoryDefault }
  })
}

export async function updatePayoutBill(
  db: Db,
  tenantId: string,
  contractorId: string,
  billId: string,
  input: z.infer<typeof updateBillSchema>,
  updatedBy: string,
): Promise<{ bill: PayoutBillRow; lines: PayoutBillLineRow[] }> {
  const { bill: existing } = await getPayoutBillWithLines(db, tenantId, contractorId, billId)

  // Guard: cannot edit a PAID or VOID bill's core fields (except notes)
  if (['PAID', 'VOID'].includes(existing.status)) {
    throw new PayoutBillConflictError(
      `Bill in ${existing.status} status cannot be modified`,
    )
  }

  // Guard: bill lines are immutable once sent (S9-i2-001 / contractor-payouts spec).
  if (input.lines !== undefined && ['SENT', 'APPROVED'].includes(existing.status)) {
    throw new PayoutBillConflictError('Bill lines cannot be modified after bill is sent')
  }

  const contractor = await getContractor(db, tenantId, contractorId)

  return db.transaction(async (tx: DbTx) => {
    // Update bill fields
    const billUpdates: Partial<NewPayoutBill> = { updatedAt: new Date() }

    if (input.status !== undefined) {
      // Validate transitions
      const allowed: Record<string, string[]> = {
        DRAFT: ['SENT'],
        SENT: ['APPROVED', 'DRAFT'],
        APPROVED: ['PAID', 'SENT'],
        PAID: [],
        VOID: [],
      }
      if (!allowed[existing.status]?.includes(input.status)) {
        throw new PayoutBillConflictError(
          `Cannot transition from ${existing.status} to ${input.status}`,
        )
      }
      billUpdates.status = input.status

      if (input.status === 'PAID') {
        billUpdates.paidAt = input.paidAt ? new Date(input.paidAt) : new Date()
        billUpdates.paymentMethod = input.paymentMethod ?? null
        billUpdates.paymentReference = input.paymentReference ?? null
      }
    }

    if (input.notes !== undefined) billUpdates.notes = input.notes

    const billRows = await tx
      .update(payoutBills)
      .set(billUpdates)
      .where(
        and(
          eq(payoutBills.tenantId, tenantId),
          eq(payoutBills.contractorId, contractorId),
          eq(payoutBills.id, billId),
        ),
      )
      .returning()

    const updatedBill = billRows[0]!

    // Replace lines if provided
    let updatedLines: PayoutBillLineRow[] = []

    if (input.lines !== undefined) {
      // Delete all existing lines
      await tx
        .delete(payoutBillLines)
        .where(and(eq(payoutBillLines.billId, billId), eq(payoutBillLines.tenantId, tenantId)))

      const hourlyLinesTotal = input.lines.reduce((sum, l) => {
        const hasHours = l.hours != null && l.hours !== ''
        const hasRate = l.rate != null && l.rate !== ''
        if (!hasHours || !hasRate) return sum
        return sum + parseFloat(l.hours!) * parseFloat(l.rate!)
      }, 0)

      const lineBounds: PayoutBillLineBounds = {
        billingType: contractor.billingType as PayoutBillLineBounds['billingType'],
        agreedAmount:
          contractor.hourlyRate != null ? parseFloat(contractor.hourlyRate) : null,
        hourlyLinesTotal: Math.round(hourlyLinesTotal * 100) / 100,
      }

      const resolvedLines =
        input.lines.length > 0
          ? input.lines.map((l) => ({
              ...l,
              lineTotal: resolvePayoutBillLineTotal(l, lineBounds),
            }))
          : []

      // Insert new lines (lineTotal recomputed server-side from hours×rate when applicable)
      if (resolvedLines.length > 0) {
        const newLineRows = await tx
          .insert(payoutBillLines)
          .values(
            resolvedLines.map((l, idx) => ({
              billId,
              tenantId,
              timeEntryId: l.timeEntryId ?? null,
              description: l.description,
              hours: l.hours ?? null,
              rate: l.rate ?? null,
              lineTotal: l.lineTotal,
              projectId: l.projectId ?? null,
              position: l.position ?? String(idx),
            })),
          )
          .returning()
        updatedLines = newLineRows
      }

      // Recompute bill amount from server-resolved line totals
      const newAmount = resolvedLines.reduce(
        (sum, l) => sum + parseFloat(l.lineTotal),
        0,
      )
      const newWithholdingAmount = Math.round(
        newAmount * parseFloat(updatedBill.withholdingRate) * 100,
      ) / 100
      const newNetAmount = newAmount - newWithholdingAmount
      const newHours = input.lines.reduce((sum, l) => sum + parseFloat(l.hours ?? '0'), 0)

      await tx
        .update(payoutBills)
        .set({
          amount: newAmount.toFixed(2),
          withholdingAmount: newWithholdingAmount.toFixed(2),
          netAmount: newNetAmount.toFixed(2),
          totalHours: newHours.toFixed(2),
          updatedAt: new Date(),
        })
        .where(
          and(
            eq(payoutBills.tenantId, tenantId),
            eq(payoutBills.id, billId),
          ),
        )
    } else {
      const lineRows = await tx
        .select()
        .from(payoutBillLines)
        .where(and(eq(payoutBillLines.billId, billId), eq(payoutBillLines.tenantId, tenantId)))
        .orderBy(asc(payoutBillLines.position), asc(payoutBillLines.id))
      updatedLines = lineRows
    }

    await tx.insert(auditLog).values({
      tenantId,
      actorId: updatedBy,
      actorType: 'user',
      entityType: 'payout_bill',
      entityId: billId,
      action: 'payout_bill.updated',
    })

    return { bill: updatedBill, lines: updatedLines }
  })
}

export async function voidPayoutBill(
  db: Db,
  tenantId: string,
  contractorId: string,
  billId: string,
  voidedBy: string,
  reason: string,
): Promise<PayoutBillRow> {
  const { bill: existing, lines } = await getPayoutBillWithLines(
    db,
    tenantId,
    contractorId,
    billId,
  )

  if (existing.status === 'PAID') {
    throw new PayoutBillConflictError(
      'Cannot void a PAID bill. Reverse the payment first.',
    )
  }
  if (existing.status === 'VOID') {
    throw new PayoutBillConflictError('Bill is already VOID.')
  }

  return db.transaction(async (tx: DbTx) => {
    const billRows = await tx
      .update(payoutBills)
      .set({
        status: 'VOID',
        voidedAt: new Date(),
        voidedBy,
        voidReason: reason,
        updatedAt: new Date(),
      })
      .where(
        and(
          eq(payoutBills.tenantId, tenantId),
          eq(payoutBills.contractorId, contractorId),
          eq(payoutBills.id, billId),
        ),
      )
      .returning()

    // Unlock time entries: reset 'locked' → 'approved' for entries tied to this bill
    const timeEntryIds = lines
      .map((l) => l.timeEntryId)
      .filter((id): id is string => id !== null)

    if (timeEntryIds.length > 0) {
      await tx
        .update(timeEntries)
        .set({
          approvalStatus: 'approved',
          lockedAt: null,
          lockedReason: null,
          updatedAt: new Date(),
        })
        .where(
          and(
            eq(timeEntries.tenantId, tenantId),
            eq(timeEntries.approvalStatus, 'locked'),
            inArray(timeEntries.id, timeEntryIds),
          ),
        )
    }

    await tx.insert(auditLog).values({
      tenantId,
      actorId: voidedBy,
      actorType: 'user',
      entityType: 'payout_bill',
      entityId: billId,
      action: 'payout_bill.voided',
    })

    return billRows[0]!
  })
}

// ── Payout Ledger ─────────────────────────────────────────────────────────────

export async function listAllPayouts(
  db: Db,
  tenantId: string,
  params: z.infer<typeof listPayoutsSchema>,
) {
  const limit = params.limit
  const cursor = params.cursor ? decodeCursor(params.cursor) : null

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

  if (params.status) conditions.push(eq(payoutBills.status, params.status))
  if (params.contractorId) conditions.push(eq(payoutBills.contractorId, params.contractorId))
  if (params.periodFrom) {
    conditions.push(
      sql`${payoutBills.periodStart} >= ${params.periodFrom}::date`,
    )
  }
  if (params.periodTo) {
    conditions.push(
      sql`${payoutBills.periodEnd} <= ${params.periodTo}::date`,
    )
  }

  if (cursor) {
    conditions.push(
      sql`(${payoutBills.createdAt}, ${payoutBills.id}) < (${cursor.createdAt}::timestamptz, ${cursor.id}::uuid)`,
    )
  }

  const rows = await db
    .select({
      bill: payoutBills,
      contractorName: contractors.name,
      contractorTaxId: contractors.taxId,
    })
    .from(payoutBills)
    .leftJoin(contractors, eq(payoutBills.contractorId, contractors.id))
    .where(and(...conditions))
    .orderBy(desc(payoutBills.createdAt), desc(payoutBills.id))
    .limit(limit + 1)

  const hasMore = rows.length > limit
  const items = hasMore ? rows.slice(0, limit) : rows

  // Total due (unpaid, non-void)
  const unpaidRows = await db
    .select({ total: sql<string>`COALESCE(SUM(${payoutBills.netAmount}::numeric), 0)` })
    .from(payoutBills)
    .where(
      and(
        eq(payoutBills.tenantId, tenantId),
        sql`${payoutBills.status} NOT IN ('PAID', 'VOID')`,
      ),
    )

  const totalDue = unpaidRows[0]?.total ?? '0'

  return {
    items,
    totalDue,
    nextCursor:
      hasMore && items.length > 0
        ? encodeCursor(items[items.length - 1]!.bill.createdAt, items[items.length - 1]!.bill.id)
        : null,
    hasMore,
  }
}

// ── Withholding Report ────────────────────────────────────────────────────────

/**
 * Annual withholding report (Form 856 / טופס 856).
 * Only includes PAID bills in the given year.
 */
export async function getWithholdingReport(
  db: Db,
  tenantId: string,
  year: number,
) {
  const yearStart = `${year}-01-01`
  const yearEnd = `${year}-12-31`

  const rows = await db
    .select({
      contractorId: payoutBills.contractorId,
      contractorName: contractors.name,
      contractorTaxId: contractors.taxId,
      contractorWithholdingCertNumber: contractors.withholdingCertificateNumber,
      contractorWithholdingCertExpiry: contractors.withholdingCertificateExpiry,
      grossPaid: sql<string>`SUM(${payoutBills.amount}::numeric)`,
      withheldAmount: sql<string>`SUM(${payoutBills.withholdingAmount}::numeric)`,
      // Use most recent bill's withholding_rate for display
      withholdingRate: sql<string>`MAX(${payoutBills.withholdingRate}::numeric)`,
    })
    .from(payoutBills)
    .leftJoin(contractors, eq(payoutBills.contractorId, contractors.id))
    .where(
      and(
        eq(payoutBills.tenantId, tenantId),
        eq(payoutBills.status, 'PAID'),
        sql`${payoutBills.paidAt} >= ${yearStart}::timestamptz`,
        sql`${payoutBills.paidAt} <= ${yearEnd}::timestamptz + INTERVAL '1 day' - INTERVAL '1 second'`,
      ),
    )
    .groupBy(
      payoutBills.contractorId,
      contractors.name,
      contractors.taxId,
      contractors.withholdingCertificateNumber,
      contractors.withholdingCertificateExpiry,
    )

  const totalGross = rows.reduce((sum, r) => sum + parseFloat(r.grossPaid ?? '0'), 0)
  const totalWithheld = rows.reduce((sum, r) => sum + parseFloat(r.withheldAmount ?? '0'), 0)

  return {
    year,
    total_gross: totalGross.toFixed(2),
    total_withheld: totalWithheld.toFixed(2),
    contractors: rows.map((r) => ({
      contractor_id: r.contractorId,
      name: r.contractorName,
      tax_id: r.contractorTaxId,
      gross_paid: r.grossPaid,
      withholding_rate: r.withholdingRate,
      withheld_amount: r.withheldAmount,
      certificate_number: r.contractorWithholdingCertNumber,
      certificate_expiry: r.contractorWithholdingCertExpiry,
    })),
  }
}

export type UnifiedWithholdingPayee = {
  payee_kind: 'contractor' | 'vendor'
  payee_id: string
  name: string | null
  tax_id: string | null
  gross_paid: string
  withholding_rate: string
  withheld_amount: string
  certificate_number: string | null
  certificate_expiry: string | null
}

export type UnifiedWithholdingReport = {
  year: number
  total_gross: string
  total_withheld: string
  payees: UnifiedWithholdingPayee[]
}

export async function getUnifiedWithholdingReport(
  db: Db,
  tenantId: string,
  year: number,
): Promise<UnifiedWithholdingReport> {
  const contractorReport = await getWithholdingReport(db, tenantId, year)

  const vendorRows = await db
    .select({
      vendorId: vendors.id,
      name: vendors.name,
      taxId: vendors.taxId,
      withholdingRate: vendors.withholdingRate,
      withholdingCertNumber: vendors.withholdingCertNumber,
      withholdingCertExpiry: vendors.withholdingCertExpiry,
      expenseId: expenses.id,
      expenseDate: expenses.expenseDate,
      amount: sql<string>`coalesce(${expenses.amount}::text, '0')`,
      businessPercent: expenses.businessPercent,
    })
    .from(vendors)
    .leftJoin(
      expenses,
      and(
        eq(expenses.vendorId, vendors.id),
        eq(expenses.tenantId, tenantId),
        isNull(expenses.deletedAt),
        sql`extract(year from ${expenses.expenseDate}) = ${year}`,
      ),
    )
    .where(eq(vendors.tenantId, tenantId))
    .orderBy(vendors.name, expenses.expenseDate)

  const vendorMap = new Map<string, UnifiedWithholdingPayee>()

  for (const row of vendorRows) {
    const existing = vendorMap.get(row.vendorId)
    if (!existing) {
      vendorMap.set(row.vendorId, {
        payee_kind: 'vendor',
        payee_id: row.vendorId,
        name: row.name,
        tax_id: row.taxId,
        gross_paid: '0.00',
        withholding_rate: row.withholdingRate ?? '0.0000',
        withheld_amount: '0.00',
        certificate_number: row.withholdingCertNumber,
        certificate_expiry: row.withholdingCertExpiry,
      })
    }

    if (!row.expenseId || !row.expenseDate) continue

    const expenseDate = new Date(`${row.expenseDate}T00:00:00.000Z`)
    const defaultRate = await resolveStatutoryWithholdingRate(db, expenseDate)
    const hasValidCert = row.withholdingCertExpiry !== null && row.withholdingCertExpiry >= row.expenseDate
    const resolvedRate =
      row.withholdingRate === null
        ? defaultRate
        : row.withholdingRate === '0.0000' || row.withholdingRate === '0'
          ? (hasValidCert ? '0.0000' : defaultRate)
          : row.withholdingRate

    const grossPaid =
      (parseFloat(row.amount) * ((row.businessPercent ?? 100) / 100))
    const withheldAmount = grossPaid * parseFloat(resolvedRate)

    const item = vendorMap.get(row.vendorId)!
    item.gross_paid = (parseFloat(item.gross_paid) + grossPaid).toFixed(2)
    item.withheld_amount = (parseFloat(item.withheld_amount) + withheldAmount).toFixed(2)
    item.withholding_rate = resolvedRate
  }

  const vendorPayees = Array.from(vendorMap.values()).filter((item) => {
    return item.withholding_rate !== '0.0000' || parseFloat(item.withheld_amount) > 0
  })

  const contractorPayees: UnifiedWithholdingPayee[] = contractorReport.contractors.map((row) => ({
    payee_kind: 'contractor',
    payee_id: row.contractor_id,
    name: row.name,
    tax_id: row.tax_id,
    gross_paid: row.gross_paid,
    withholding_rate: row.withholding_rate,
    withheld_amount: row.withheld_amount,
    certificate_number: row.certificate_number,
    certificate_expiry: row.certificate_expiry,
  }))

  const payees = [...contractorPayees, ...vendorPayees].sort((a, b) =>
    (a.name ?? a.payee_id).localeCompare(b.name ?? b.payee_id),
  )

  const totalGross = payees.reduce((sum, item) => sum + parseFloat(item.gross_paid), 0)
  const totalWithheld = payees.reduce((sum, item) => sum + parseFloat(item.withheld_amount), 0)

  return {
    year,
    total_gross: totalGross.toFixed(2),
    total_withheld: totalWithheld.toFixed(2),
    payees,
  }
}

/**
 * Find contractors with withholding certificates expiring within the next N days.
 * Used by the weekly cron job.
 */
export async function findExpiringWithholdingCertificates(
  db: Db,
  withinDays = 30,
): Promise<Array<ContractorRow & { tenantId: string }>> {
  const cutoff = new Date()
  cutoff.setDate(cutoff.getDate() + withinDays)
  const cutoffIso = cutoff.toISOString().slice(0, 10)

  return db
    .select()
    .from(contractors)
    .where(
      and(
        isNotNull(contractors.withholdingCertificateExpiry),
        sql`${contractors.withholdingCertificateExpiry}::date <= ${cutoffIso}::date`,
        eq(contractors.active, true),
      ),
    ) as unknown as Array<ContractorRow & { tenantId: string }>
}

// ── Withholding Tax Certificates (history) ────────────────────────────────────

export async function listWithholdingCertificates(
  db: Db,
  tenantId: string,
  contractorId: string,
): Promise<WithholdingTaxCertificateRow[]> {
  return db
    .select()
    .from(withholdingTaxCertificates)
    .where(
      and(
        eq(withholdingTaxCertificates.tenantId, tenantId),
        eq(withholdingTaxCertificates.contractorId, contractorId),
      ),
    )
    .orderBy(desc(withholdingTaxCertificates.uploadedAt))
}

export async function addWithholdingCertificate(
  db: Db,
  tenantId: string,
  contractorId: string,
  uploadedBy: string,
  input: {
    certificateNumber: string
    taxYear: string
    withholdingRate: string
    expiryDate: string
    r2Key?: string | null
  },
): Promise<WithholdingTaxCertificateRow> {
  // Verify contractor ownership
  await getContractor(db, tenantId, contractorId)

  const rows = await db
    .insert(withholdingTaxCertificates)
    .values({
      tenantId,
      contractorId,
      certificateNumber: input.certificateNumber,
      taxYear: input.taxYear,
      withholdingRate: input.withholdingRate,
      expiryDate: input.expiryDate,
      r2Key: input.r2Key ?? null,
      uploadedBy,
    })
    .returning()

  return rows[0]!
}