/**
 * Contractors & Payouts schema — contractor-payouts (P049, wave 7).
 * Postgres / Neon via Hyperdrive.
 *
 * Tables:
 *   contractors                  — freelancer / sub-provider directory
 *   contractor_assignments       — contractor → project associations + rate overrides
 *   payout_bills                 — tenant-owed payout per contractor per period
 *   payout_bill_lines            — line-level detail for each payout bill
 *   withholding_tax_certificates — ITA certificate record per contractor (historical audit trail)
 *
 * Israeli Tax Law:
 *   Withholding (ניכוי מס במקור) §164 Income Tax Ordinance.
 *   payout_bills.withholding_rate is a snapshot of contractor.withholding_tax_rate
 *   at bill generation time; immutable after SENT.
 *   NULL contractor rate → caller resolves statutory default from tax_rates
 *   WHERE tax_type='withholding_default' AND country_code='IL'.
 *
 * DB conventions (from invoices.ts):
 *   UUID PK .defaultRandom()
 *   TIMESTAMPTZ via timestamp(col, { withTimezone: true })
 *   Money: NUMERIC
 *   Enums: text() + check(... IN (...)) — NEVER pgEnum
 *   Expression / GIN / partial indexes → manifest.raw_ddl only
 */
import {
  pgTable,
  uuid,
  text,
  numeric,
  boolean,
  timestamp,
  date,
  index,
  check,
} from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { users } from './users'
import { projects } from './projects'

// ── contractors ───────────────────────────────────────────────────────────────

export const contractors = pgTable(
  'contractors',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),

    name: text('name').notNull(),
    email: text('email'),
    phone: text('phone'),
    userId: uuid('user_id').references(() => users.id, { onDelete: 'set null' }),

    // ח.פ. / ע.מ. / ת.ז. — Israeli business or personal tax number
    taxId: text('tax_id'),

    // 'hourly' | 'fixed' | 'retainer'
    billingType: text('billing_type').notNull().default('hourly'),
    hourlyRate: numeric('hourly_rate', { precision: 10, scale: 2 }),
    currency: text('currency').notNull().default('ILS'),

    active: boolean('active').notNull().default(true),
    notes: text('notes'),

    // ── Withholding tax certificate (ניכוי מס במקור) ──────────────────────
    // NULL = not configured → caller uses statutory default (currently 30%)
    withholdingTaxRate: numeric('withholding_tax_rate', { precision: 5, scale: 4 }),
    withholdingCertificateNumber: text('withholding_certificate_number'),
    withholdingCertificateExpiry: date('withholding_certificate_expiry'),
    // R2 object key for uploaded certificate PDF (optional, for record-keeping)
    withholdingCertificateR2Key: text('withholding_certificate_r2_key'),

    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    tenantIdx: index('contractors_tenant_idx').on(t.tenantId),
    tenantActiveIdx: index('contractors_tenant_active_idx').on(t.tenantId, t.active),

    billingTypeCheck: check(
      'contractors_billing_type_check',
      sql`${t.billingType} IN ('hourly','fixed','retainer')`,
    ),
    withholdingRateCheck: check(
      'contractors_withholding_rate_check',
      sql`${t.withholdingTaxRate} IS NULL OR (${t.withholdingTaxRate} >= 0 AND ${t.withholdingTaxRate} <= 1)`,
    ),
  }),
)

export type ContractorRow = typeof contractors.$inferSelect
export type NewContractor = typeof contractors.$inferInsert

// ── contractor_assignments ────────────────────────────────────────────────────

export const contractorAssignments = pgTable(
  'contractor_assignments',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    contractorId: uuid('contractor_id')
      .notNull()
      .references(() => contractors.id, { onDelete: 'cascade' }),
    projectId: uuid('project_id')
      .notNull()
      .references(() => projects.id, { onDelete: 'cascade' }),

    role: text('role'),
    // NULL = use contractor.hourly_rate; non-null = project-specific override
    rateOverride: numeric('rate_override', { precision: 10, scale: 2 }),

    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    contractorProjectIdx: index('contractor_assignments_contractor_project_idx').on(
      t.contractorId,
      t.projectId,
    ),
    tenantContractorIdx: index('contractor_assignments_tenant_contractor_idx').on(
      t.tenantId,
      t.contractorId,
    ),
  }),
)

export type ContractorAssignmentRow = typeof contractorAssignments.$inferSelect
export type NewContractorAssignment = typeof contractorAssignments.$inferInsert

// ── payout_bills ──────────────────────────────────────────────────────────────

export const payoutBills = pgTable(
  'payout_bills',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    contractorId: uuid('contractor_id')
      .notNull()
      .references(() => contractors.id, { onDelete: 'restrict' }),

    periodStart: date('period_start').notNull(),
    periodEnd: date('period_end').notNull(),

    // 'DRAFT' | 'SENT' | 'APPROVED' | 'PAID' | 'VOID'
    status: text('status').notNull().default('DRAFT'),

    totalHours: numeric('total_hours', { precision: 8, scale: 2 }),
    amount: numeric('amount', { precision: 12, scale: 2 }).notNull(),
    currency: text('currency').notNull().default('ILS'),

    // ── Withholding (snapshot at generation time; immutable after SENT) ───
    // Snapshot of contractor.withholding_tax_rate (or resolved statutory default)
    withholdingRate: numeric('withholding_rate', { precision: 5, scale: 4 }).notNull().default('0'),
    withholdingAmount: numeric('withholding_amount', { precision: 12, scale: 2 }).notNull().default('0'),
    // net_amount = amount - withholding_amount (what contractor actually receives)
    netAmount: numeric('net_amount', { precision: 12, scale: 2 }),

    notes: text('notes'),

    // ── Payment recording ─────────────────────────────────────────────────
    paidAt: timestamp('paid_at', { withTimezone: true }),
    // 'bank_transfer' | 'check' | 'other'
    paymentMethod: text('payment_method'),
    paymentReference: text('payment_reference'),

    // ── Void tracking ─────────────────────────────────────────────────────
    voidedAt: timestamp('voided_at', { withTimezone: true }),
    voidedBy: uuid('voided_by').references(() => users.id, { onDelete: 'set null' }),
    voidReason: text('void_reason'),

    createdBy: uuid('created_by')
      .notNull()
      .references(() => users.id, { onDelete: 'restrict' }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    tenantContractorIdx: index('payout_bills_tenant_contractor_idx').on(
      t.tenantId,
      t.contractorId,
    ),
    tenantStatusIdx: index('payout_bills_tenant_status_idx').on(t.tenantId, t.status),
    periodIdx: index('payout_bills_period_idx').on(t.tenantId, t.periodStart, t.periodEnd),
    // financial-statements (wave-13): covering index for Cash Flow report queries
    reportIdx: index('idx_payout_bills_rpt').on(t.tenantId, t.status, t.paidAt),

    statusCheck: check(
      'payout_bills_status_check',
      sql`${t.status} IN ('DRAFT','SENT','APPROVED','PAID','VOID')`,
    ),
    paymentMethodCheck: check(
      'payout_bills_payment_method_check',
      sql`${t.paymentMethod} IS NULL OR ${t.paymentMethod} IN ('bank_transfer','check','other')`,
    ),
    voidConsistencyCheck: check(
      'payout_bills_void_consistency_check',
      sql`(${t.status} = 'VOID') = (${t.voidedAt} IS NOT NULL)`,
    ),
    paidConsistencyCheck: check(
      'payout_bills_paid_consistency_check',
      sql`(${t.status} = 'PAID') = (${t.paidAt} IS NOT NULL)`,
    ),
  }),
)

export type PayoutBillRow = typeof payoutBills.$inferSelect
export type NewPayoutBill = typeof payoutBills.$inferInsert

// ── payout_bill_lines ─────────────────────────────────────────────────────────

export const payoutBillLines = pgTable(
  'payout_bill_lines',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    billId: uuid('bill_id')
      .notNull()
      .references(() => payoutBills.id, { onDelete: 'cascade' }),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),

    // FK to time_entries — nullable for fixed/retainer lines or manually added lines
    timeEntryId: uuid('time_entry_id'),

    description: text('description').notNull(),
    hours: numeric('hours', { precision: 8, scale: 2 }),
    rate: numeric('rate', { precision: 10, scale: 2 }),
    lineTotal: numeric('line_total', { precision: 12, scale: 2 }).notNull(),

    // Optional project attribution per line (a bill may span multiple projects)
    projectId: uuid('project_id').references(() => projects.id, { onDelete: 'set null' }),

    position: text('position').notNull().default('0'), // fractional-index ordering
  },
  (t) => ({
    billIdx: index('payout_bill_lines_bill_idx').on(t.billId),
    timeEntryIdx: index('payout_bill_lines_time_entry_idx').on(t.timeEntryId),
  }),
)

export type PayoutBillLineRow = typeof payoutBillLines.$inferSelect
export type NewPayoutBillLine = typeof payoutBillLines.$inferInsert

// ── withholding_tax_certificates ──────────────────────────────────────────────
// Historical archive of uploaded ITA certificates for audit trail.
// The live/active values live as columns on contractors for easy query;
// this table stores the full certificate history.

export const withholdingTaxCertificates = pgTable(
  'withholding_tax_certificates',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    contractorId: uuid('contractor_id')
      .notNull()
      .references(() => contractors.id, { onDelete: 'cascade' }),

    certificateNumber: text('certificate_number').notNull(),
    // Decimal fraction: 0 = full exemption, 0.30 = 30% withholding
    taxYear: text('tax_year').notNull(),  // e.g. '2026'
    withholdingRate: numeric('withholding_rate', { precision: 5, scale: 4 }).notNull(),
    expiryDate: date('expiry_date').notNull(),

    // R2 object key for the uploaded PDF
    r2Key: text('r2_key'),

    uploadedBy: uuid('uploaded_by')
      .notNull()
      .references(() => users.id, { onDelete: 'restrict' }),
    uploadedAt: timestamp('uploaded_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    contractorIdx: index('withholding_tax_certs_contractor_idx').on(t.contractorId),
    tenantYearIdx: index('withholding_tax_certs_tenant_year_idx').on(t.tenantId, t.taxYear),
    withholdingRateCheck: check(
      'withholding_tax_certs_rate_check',
      sql`${t.withholdingRate} >= 0 AND ${t.withholdingRate} <= 1`,
    ),
  }),
)

export type WithholdingTaxCertificateRow = typeof withholdingTaxCertificates.$inferSelect
export type NewWithholdingTaxCertificate = typeof withholdingTaxCertificates.$inferInsert