/**
 * Expenses module schema — expenses + expense_corrections tables.
 * Postgres / Neon via Hyperdrive.
 *
 * - expenses:            receipt-based and per-diem expense entries, with Claude Vision OCR
 *                        and AI-driven Israeli tax-deductibility evaluation fields.
 * - expense_corrections: audit trail for manual field edits by staff.
 *
 * NOTE: tenant_settings base table (id + unique tenant_id FK + timestamps) is owned by
 * foundation-auth-rbac (packages/db/src/schema/tenants.ts). This module extends it via
 * ALTER TABLE in the migration; the Drizzle extended shape is in tenant-settings-expenses.ts
 * (a separate file so drizzle-kit sees only the new columns without re-creating the base).
 *
 * Indexes:
 *   idx_expenses_report     — tenant_id, status, expense_date (report queries / financial-statements)
 *   idx_expenses_cursor     — tenant_id, created_at DESC, id DESC (cursor-based pagination)
 *   idx_expenses_project    — project_id (FK join)
 *   idx_expense_corrections_expense — expense_id (lookup)
 * These are plain btree column indexes (no sql`` expression) → safe to declare via drizzle index().
 */
import {
  pgTable,
  uuid,
  text,
  boolean,
  jsonb,
  numeric,
  integer,
  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'
import { customers } from './customers'
import { invoices } from './invoices'
import type { AnyPgColumn } from 'drizzle-orm/pg-core'

// ── expenses ──────────────────────────────────────────────────────────────────

export const expenses = pgTable(
  'expenses',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    projectId: uuid('project_id').references(() => projects.id, { onDelete: 'set null' }),
    createdBy: uuid('created_by')
      .notNull()
      .references(() => users.id),

    // ── File ──────────────────────────────────────────────────────────────────
    r2Key: text('r2_key').notNull(),
    fileName: text('file_name').notNull(),
    fileType: text('file_type').notNull(),
    fileSizeBytes: integer('file_size_bytes').notNull(),

    // ── OCR raw capture (nullable until processed) ────────────────────────────
    vendorName: text('vendor_name'),
    vendorTaxId: text('vendor_tax_id'),
    invoiceNumber: text('invoice_number'),
    invoiceTotal: numeric('invoice_total', { precision: 12, scale: 2 }),
    vatAmount: numeric('vat_amount', { precision: 12, scale: 2 }),
    currency: text('currency').notNull().default('ILS'),
    allocationNumber: text('allocation_number'),
    rawOcrText: text('raw_ocr_text'),

    // ── Canonical accounting fields ───────────────────────────────────────────
    expenseDate: date('expense_date'),
    amount: numeric('amount', { precision: 12, scale: 2 }),
    vatDeductible: boolean('vat_deductible').notNull().default(true),

    // ── Processing status ─────────────────────────────────────────────────────
    status: text('status').notNull().default('PENDING'),
    ocrConfidence: numeric('ocr_confidence', { precision: 3, scale: 2 }),
    processingStartedAt: timestamp('processing_started_at', { withTimezone: true }),
    processedAt: timestamp('processed_at', { withTimezone: true }),
    processingError: text('processing_error'),

    // ── AI tax evaluation (nullable until evaluated) ───────────────────────────
    expenseCategory: text('expense_category'),
    deductionPct: integer('deduction_pct'),
    deductionConfidence: numeric('deduction_confidence', { precision: 3, scale: 2 }),
    deductionReasoningHe: text('deduction_reasoning_he'),
    deductionReasoningEn: text('deduction_reasoning_en'),
    evaluatedAt: timestamp('evaluated_at', { withTimezone: true }),

    // ── Per-diem (receipt-less daily allowance) ───────────────────────────────
    isPerDiem: boolean('is_per_diem').notNull().default(false),
    perDiemDays: numeric('per_diem_days', { precision: 5, scale: 2 }),
    perDiemRateIls: numeric('per_diem_rate_ils', { precision: 10, scale: 2 }),

    // ── Source tracking ───────────────────────────────────────────────────────
    source: text('source').notNull().default('upload'),
    sourceMetadata: jsonb('source_metadata').$type<Record<string, unknown>>(),

    notes: text('notes'),

    // ── Billing bridge (expense-to-invoice-line P052, time-to-invoice P064) ───
    customerId: uuid('customer_id').references(() => customers.id, { onDelete: 'set null' }),
    billable: boolean('billable').notNull().default(false),
    // invoiceId: lazy reference avoids circular import (expenses ↔ invoices)
    invoiceId: uuid('invoice_id').references((): AnyPgColumn => invoices.id, { onDelete: 'set null' }),
    billedAt: timestamp('billed_at', { withTimezone: true }),

    // ── Business/personal split (expense-personal-business-split spec) ────────
    businessPercent: integer('business_percent').notNull().default(100),

    // ── Approval workflow (expense-approval-workflow spec) ────────────────────
    // CHECK enforced in migration SQL (not via drizzle check() — partial index below)
    approvalStatus: text('approval_status').notNull().default('not_required'),
    approvedBy: uuid('approved_by').references(() => users.id),
    approvedAt: timestamp('approved_at', { withTimezone: true }),
    approvalNote: text('approval_note'),

    // ── Vendors/Suppliers (vendors-suppliers spec) ────────────────────────────
    vendorId: uuid('vendor_id'), // FK added after vendors table is created; no .references() here (avoids forward ref)

    // ── OCR correction UX (expense-ocr-correction-ux spec) ───────────────────
    correctionNote: text('correction_note'),
    voidedAt: timestamp('voided_at', { withTimezone: true }),
    voidedReason: text('voided_reason'),

    deletedAt: timestamp('deleted_at', { withTimezone: true }),

    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    // Indexes for report queries (financial-statements also uses idx_expenses_report)
    reportIdx: index('idx_expenses_report').on(t.tenantId, t.status, t.expenseDate),
    cursorIdx: index('idx_expenses_cursor').on(t.tenantId, t.createdAt, t.id),
    projectIdx: index('idx_expenses_project').on(t.projectId),

    // CHECK constraints
    businessPercentCheck: check(
      'expenses_business_percent_check',
      sql`${t.businessPercent} >= 0 AND ${t.businessPercent} <= 100`,
    ),
    fileTypeCheck: check(
      'expenses_file_type_check',
      sql`${t.fileType} IN ('pdf','jpg','png','heic')`,
    ),
    statusCheck: check(
      'expenses_status_check',
      sql`${t.status} IN ('PENDING','PROCESSING','COMPLETED','FAILED','NEEDS_REVIEW')`,
    ),
    categoryCheck: check(
      'expenses_category_check',
      sql`${t.expenseCategory} IS NULL OR ${t.expenseCategory} IN ('office','marketing','professional','vehicle','equipment','finance','welfare','exceptional','travel')`,
    ),
    deductionPctCheck: check(
      'expenses_deduction_pct_check',
      sql`${t.deductionPct} IS NULL OR (${t.deductionPct} >= 0 AND ${t.deductionPct} <= 100)`,
    ),
    sourceCheck: check(
      'expenses_source_check',
      sql`${t.source} IN ('upload','email','whatsapp','telegram')`,
    ),
  }),
)

export type ExpenseRow = typeof expenses.$inferSelect
export type NewExpense = typeof expenses.$inferInsert

// ── expense_corrections ───────────────────────────────────────────────────────

export const expenseCorrections = pgTable(
  'expense_corrections',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    expenseId: uuid('expense_id')
      .notNull()
      .references(() => expenses.id, { onDelete: 'cascade' }),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id),
    fieldName: text('field_name').notNull(),
    originalValue: text('original_value'),
    correctedValue: text('corrected_value').notNull(),
    // ── OCR correction UX: discriminate OCR-review vs manual post-approval corrections ─
    // CHECK enforced in migration SQL
    correctionSource: text('correction_source').notNull().default('ocr'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    expenseIdx: index('idx_expense_corrections_expense').on(t.expenseId),
  }),
)

export type ExpenseCorrectionRow = typeof expenseCorrections.$inferSelect
export type NewExpenseCorrection = typeof expenseCorrections.$inferInsert

// ── tenant_settings delta (tenant-portals wave 9) ─────────────────────────────
// Base tenant_settings pgTable lives in tenants.ts; expenses-module owns portal_max_session_hours.

export const tenantSettingsTenantPortals = pgTable(
  'tenant_settings',
  {
    portalMaxSessionHours: integer('portal_max_session_hours').notNull().default(24),
  },
  (t) => ({
    portalMaxSessionHoursCheck: check(
      'tenant_settings_portal_max_session_hours_check',
      sql`${t.portalMaxSessionHours} >= 4 AND ${t.portalMaxSessionHours} <= 72`,
    ),
  }),
)