import { pgTable, text, boolean, integer, jsonb, uuid, date, index, uniqueIndex } from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';
import { id, tenantId, createdAt, updatedAt, ts, qty, cbm, kg, money, rate, pct } from './_common';
import {
  billingCycleEnum, storageMethodEnum, contractStatusEnum, tariffGroupEnum, chargeUnitEnum, chargeTriggerEnum,
  chargeStatusEnum, invoiceStatusEnum, docSourceEnum,
} from './enums';
import { warehouses, storers } from './master-data';

/** Commercial agreement between the tenant (3PL) and a storer. */
export const billingContracts = pgTable(
  'billing_contracts',
  {
    id: id(),
    tenantId: tenantId(),
    storerId: uuid('storer_id').notNull().references(() => storers.id),
    contractNumber: text('contract_number').notNull(),
    name: text('name').notNull(),
    currency: text('currency').notNull().default('KWD'),
    status: contractStatusEnum('status').notNull().default('DRAFT'),
    billingCycle: billingCycleEnum('billing_cycle').notNull().default('MONTHLY'),
    storageMethod: storageMethodEnum('storage_method').notNull().default('WEEKLY_CBM_OPENING_PLUS_IN'),
    storageCap: jsonb('storage_cap').$type<Record<string, unknown>>(),
    minimumMonthly: money('minimum_monthly'),
    fixedMonthlyFee: money('fixed_monthly_fee'),
    paymentTermsDays: integer('payment_terms_days').notNull().default(30),
    weekStartsOn: integer('week_starts_on').notNull().default(1),
    validFrom: date('valid_from').notNull(),
    validTo: date('valid_to'),
    taxRatePct: pct('tax_rate_pct').notNull().default(sql`0`),
    notes: text('notes'),
    createdById: uuid('created_by_id'),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex('billing_contracts_number_uq').on(t.tenantId, t.contractNumber), index('billing_contracts_storer_status_idx').on(t.tenantId, t.storerId, t.status)],
);

export const tariffs = pgTable(
  'tariffs',
  {
    id: id(),
    tenantId: tenantId(),
    contractId: uuid('contract_id').notNull().references(() => billingContracts.id, { onDelete: 'cascade' }),
    code: text('code').notNull(),
    group: tariffGroupEnum('group').notNull(),
    description: text('description').notNull(),
    chargeUnit: chargeUnitEnum('charge_unit').notNull(),
    rate: rate('rate').notNull(),
    minimumCharge: money('minimum_charge'),
    tiers: jsonb('tiers').$type<{ upTo: number | null; rate: number }[]>(),
    trigger: chargeTriggerEnum('trigger').notNull().default('MANUAL'),
    conditions: jsonb('conditions').notNull().default({}).$type<Record<string, unknown>>(),
    taxable: boolean('taxable').notNull().default(true),
    validFrom: date('valid_from'),
    validTo: date('valid_to'),
    active: boolean('active').notNull().default(true),
    sortOrder: integer('sort_order').notNull().default(100),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex('tariffs_contract_code_uq').on(t.contractId, t.code), index('tariffs_trigger_idx').on(t.tenantId, t.trigger, t.active)],
);

/** Every billable event captured at source; invoices aggregate these. */
export const chargeEvents = pgTable(
  'charge_events',
  {
    id: id(),
    tenantId: tenantId(),
    warehouseId: uuid('warehouse_id').notNull().references(() => warehouses.id),
    storerId: uuid('storer_id').notNull().references(() => storers.id),
    contractId: uuid('contract_id').notNull(),
    tariffId: uuid('tariff_id').notNull().references(() => tariffs.id),
    refType: text('ref_type'),
    refId: uuid('ref_id'),
    refNumber: text('ref_number'),
    description: text('description').notNull(),
    quantity: qty('quantity').notNull(),
    unit: chargeUnitEnum('unit').notNull(),
    rate: rate('rate').notNull(),
    amount: money('amount').notNull(),
    currency: text('currency').notNull(),
    chargeDate: date('charge_date').notNull(),
    periodKey: text('period_key').notNull(),
    status: chargeStatusEnum('status').notNull().default('PENDING'),
    source: docSourceEnum('source').notNull().default('SYSTEM'),
    invoiceLineId: uuid('invoice_line_id'),
    detail: jsonb('detail').notNull().default({}).$type<Record<string, unknown>>(),
    idempotencyKey: text('idempotency_key'),
    waivedReason: text('waived_reason'),
    createdById: uuid('created_by_id'),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex('charge_events_idem_uq').on(t.tenantId, t.idempotencyKey), index('charge_events_storer_period_idx').on(t.tenantId, t.storerId, t.periodKey, t.status)],
);

/** Daily occupancy per storer per warehouse (job runs at day end). */
export const storageSnapshots = pgTable(
  'storage_snapshots',
  {
    id: id(),
    tenantId: tenantId(),
    warehouseId: uuid('warehouse_id').notNull().references(() => warehouses.id),
    storerId: uuid('storer_id').notNull().references(() => storers.id),
    snapshotDate: date('snapshot_date').notNull(),
    totalQty: qty('total_qty').notNull().default(sql`0`),
    totalCbm: cbm('total_cbm').notNull().default(sql`0`),
    totalWeightKg: kg('total_weight_kg').notNull().default(sql`0`),
    palletPositions: integer('pallet_positions').notNull().default(0),
    inboundCbm: cbm('inbound_cbm').notNull().default(sql`0`),
    outboundCbm: cbm('outbound_cbm').notNull().default(sql`0`),
    byTemperatureClass: jsonb('by_temperature_class').notNull().default({}).$type<Record<string, { cbm: string; pallets: number; qty: string }>>(),
    createdAt: createdAt(),
  },
  (t) => [uniqueIndex('storage_snapshots_uq').on(t.tenantId, t.warehouseId, t.storerId, t.snapshotDate), index('storage_snapshots_storer_date_idx').on(t.tenantId, t.storerId, t.snapshotDate)],
);

export const invoices = pgTable(
  'invoices',
  {
    id: id(),
    tenantId: tenantId(),
    storerId: uuid('storer_id').notNull().references(() => storers.id),
    contractId: uuid('contract_id').notNull().references(() => billingContracts.id),
    invoiceNumber: text('invoice_number').notNull(),
    periodStart: date('period_start').notNull(),
    periodEnd: date('period_end').notNull(),
    status: invoiceStatusEnum('status').notNull().default('DRAFT'),
    currency: text('currency').notNull(),
    subtotal: money('subtotal').notNull().default(sql`0`),
    taxTotal: money('tax_total').notNull().default(sql`0`),
    total: money('total').notNull().default(sql`0`),
    paidAmount: money('paid_amount').notNull().default(sql`0`),
    issuedAt: ts('issued_at'),
    dueAt: date('due_at'),
    sentAt: ts('sent_at'),
    paidAt: ts('paid_at'),
    documentId: uuid('document_id'),
    notes: text('notes'),
    createdById: uuid('created_by_id'),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex('invoices_tenant_number_uq').on(t.tenantId, t.invoiceNumber), index('invoices_storer_status_idx').on(t.tenantId, t.storerId, t.status)],
);

export const invoiceLines = pgTable(
  'invoice_lines',
  {
    id: id(),
    tenantId: tenantId(),
    invoiceId: uuid('invoice_id').notNull().references(() => invoices.id, { onDelete: 'cascade' }),
    tariffId: uuid('tariff_id'),
    group: tariffGroupEnum('group').notNull(),
    description: text('description').notNull(),
    quantity: qty('quantity').notNull(),
    unit: chargeUnitEnum('unit').notNull(),
    rate: rate('rate').notNull(),
    amount: money('amount').notNull(),
    taxRatePct: pct('tax_rate_pct').notNull().default(sql`0`),
    taxAmount: money('tax_amount').notNull().default(sql`0`),
    sortOrder: integer('sort_order').notNull().default(0),
    detail: jsonb('detail').notNull().default({}).$type<Record<string, unknown>>(),
    chargeEventIds: uuid('charge_event_ids').array().notNull().default([]),
  },
  (t) => [index('invoice_lines_invoice_idx').on(t.tenantId, t.invoiceId)],
);

export const creditNotes = pgTable(
  'credit_notes',
  {
    id: id(),
    tenantId: tenantId(),
    invoiceId: uuid('invoice_id').notNull().references(() => invoices.id),
    creditNumber: text('credit_number').notNull(),
    amount: money('amount').notNull(),
    reason: text('reason').notNull(),
    status: text('status').notNull().default('ISSUED'),
    issuedAt: ts('issued_at').notNull().defaultNow(),
    createdById: uuid('created_by_id'),
  },
  (t) => [uniqueIndex('credit_notes_number_uq').on(t.tenantId, t.creditNumber)],
);