/**
 * Accountant export schema — accountant-export (wave-15).
 *
 * Tables:
 *   coa_accounts           — chart of accounts (per tenant, editable)
 *   coa_mappings           — source_kind → account_code mapping (composite PK)
 *   accountant_export_jobs — async export job tracking (movement file, form 6111)
 *
 * Postgres / Neon via Hyperdrive.
 */
import {
  pgTable,
  uuid,
  text,
  timestamp,
  date,
  unique,
  index,
  check,
  primaryKey,
} from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { users } from './users'

// ── coa_accounts ──────────────────────────────────────────────────────────────

export const coaAccounts = pgTable(
  'coa_accounts',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    code: text('code').notNull(),
    name: text('name').notNull(),
    // 'asset'|'liability'|'equity'|'revenue'|'expense'
    type: text('type').notNull(),
    form6111Code: text('form6111_code'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    tenantIdx: index('coa_accounts_tenant_idx').on(t.tenantId),
    tenantCodeUniq: unique('coa_accounts_tenant_code_uniq').on(t.tenantId, t.code),
    typeCheck: check(
      'coa_accounts_type_check',
      sql`${t.type} IN ('asset','liability','equity','revenue','expense')`,
    ),
  }),
)

export type CoaAccountRow = typeof coaAccounts.$inferSelect
export type NewCoaAccount = typeof coaAccounts.$inferInsert

// ── coa_mappings ──────────────────────────────────────────────────────────────

// Composite-PK mapping: source_kind → account_code (no surrogate UUID, like role_permissions)
export const coaMappings = pgTable(
  'coa_mappings',
  {
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    // 'revenue'|'vat_payable'|'ar'|'bank'|'vat_input'|'ap'|'withholding'
    // or any expense category: 'office'|'marketing'|'professional'|'vehicle'|
    //                           'equipment'|'finance'|'welfare'|'exceptional'|'travel'
    sourceKind: text('source_kind').notNull(),
    accountCode: text('account_code').notNull(),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.tenantId, t.sourceKind] }),
  }),
)

export type CoaMappingRow = typeof coaMappings.$inferSelect
export type NewCoaMapping = typeof coaMappings.$inferInsert

// ── accountant_export_jobs ────────────────────────────────────────────────────

export const accountantExportJobs = pgTable(
  'accountant_export_jobs',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    // 'movement_file' | 'form6111'
    kind: text('kind').notNull(),
    periodFrom: date('period_from').notNull(),
    periodTo: date('period_to').notNull(),
    status: text('status').notNull().default('pending'),
    r2Key: text('r2_key'),
    downloadExpiresAt: timestamp('download_expires_at', { withTimezone: true }),
    generatedBy: uuid('generated_by').references(() => users.id, { onDelete: 'set null' }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    tenantIdx: index('accountant_export_jobs_tenant_idx').on(t.tenantId, t.createdAt),
    kindCheck: check(
      'accountant_export_jobs_kind_check',
      sql`${t.kind} IN ('movement_file','form6111')`,
    ),
    statusCheck: check(
      'accountant_export_jobs_status_check',
      sql`${t.status} IN ('pending','running','done','error')`,
    ),
  }),
)

export type AccountantExportJobRow = typeof accountantExportJobs.$inferSelect
export type NewAccountantExportJob = typeof accountantExportJobs.$inferInsert