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, cm, money, pct } from './_common';
import {
  lpnTypeEnum, lpnStatusEnum, inventoryStatusEnum, movementTypeEnum, serialStatusEnum, holdScopeEnum,
  approvalStatusEnum, countTypeEnum, countStatusEnum, countLineStatusEnum,
} from './enums';
import { warehouses, storers, skus, locations } from './master-data';

/** License plate (pallet / tote id). */
export const lpns = pgTable(
  'lpns',
  {
    id: id(),
    tenantId: tenantId(),
    warehouseId: uuid('warehouse_id').notNull().references(() => warehouses.id),
    lpnNumber: text('lpn_number').notNull(),
    type: lpnTypeEnum('type').notNull().default('PALLET'),
    status: lpnStatusEnum('status').notNull().default('ACTIVE'),
    locationId: uuid('location_id').references(() => locations.id),
    parentLpnId: uuid('parent_lpn_id'),
    storerId: uuid('storer_id').references(() => storers.id),
    weightKg: kg('weight_kg'),
    heightCm: cm('height_cm'),
    sealed: boolean('sealed').notNull().default(false),
    attributes: jsonb('attributes').notNull().default({}).$type<Record<string, unknown>>(),
    createdAt: createdAt(),
    lastMovedAt: ts('last_moved_at'),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex('lpns_tenant_number_uq').on(t.tenantId, t.lpnNumber), index('lpns_tenant_wh_loc_idx').on(t.tenantId, t.warehouseId, t.locationId)],
);

/**
 * Materialised current stock. balanceKey is a deterministic string of the
 * identity fields (so nullable columns still get a unique constraint).
 */
export const inventoryBalances = pgTable(
  'inventory_balances',
  {
    id: id(),
    tenantId: tenantId(),
    warehouseId: uuid('warehouse_id').notNull().references(() => warehouses.id),
    storerId: uuid('storer_id').notNull().references(() => storers.id),
    skuId: uuid('sku_id').notNull().references(() => skus.id),
    locationId: uuid('location_id').notNull().references(() => locations.id),
    lpnId: uuid('lpn_id').references(() => lpns.id),
    lotNumber: text('lot_number').notNull().default(''),
    expiryDate: date('expiry_date'),
    mfgDate: date('mfg_date'),
    status: inventoryStatusEnum('status').notNull().default('AVAILABLE'),
    balanceKey: text('balance_key').notNull().unique(),
    qtyOnHand: qty('qty_on_hand').notNull().default(sql`0`),
    qtyAllocated: qty('qty_allocated').notNull().default(sql`0`),
    firstReceivedAt: ts('first_received_at').notNull().defaultNow(),
    lastMovedAt: ts('last_moved_at').notNull().defaultNow(),
    originAsnId: uuid('origin_asn_id'),
    unitCost: money('unit_cost'),
    attributes: jsonb('attributes').notNull().default({}).$type<Record<string, unknown>>(),
    version: integer('version').notNull().default(0),
    updatedAt: updatedAt(),
  },
  (t) => [
    index('inv_bal_lookup_idx').on(t.tenantId, t.warehouseId, t.storerId, t.skuId, t.status),
    index('inv_bal_location_idx').on(t.tenantId, t.locationId),
    index('inv_bal_lpn_idx').on(t.tenantId, t.lpnId),
    index('inv_bal_expiry_idx').on(t.tenantId, t.expiryDate),
  ],
);

/** Append-only ledger. Never updated, never deleted. */
export const inventoryMovements = pgTable(
  'inventory_movements',
  {
    id: id(),
    tenantId: tenantId(),
    warehouseId: uuid('warehouse_id').notNull(),
    storerId: uuid('storer_id').notNull(),
    skuId: uuid('sku_id').notNull(),
    type: movementTypeEnum('type').notNull(),
    qty: qty('qty').notNull(),
    fromLocationId: uuid('from_location_id'),
    toLocationId: uuid('to_location_id'),
    fromLpnId: uuid('from_lpn_id'),
    toLpnId: uuid('to_lpn_id'),
    lotNumber: text('lot_number').notNull().default(''),
    expiryDate: date('expiry_date'),
    fromStatus: inventoryStatusEnum('from_status'),
    toStatus: inventoryStatusEnum('to_status'),
    refType: text('ref_type'),
    refId: uuid('ref_id'),
    taskId: uuid('task_id'),
    reasonCodeId: uuid('reason_code_id'),
    serials: text('serials').array().notNull().default([]),
    cbm: cbm('cbm'),
    weightKg: kg('weight_kg'),
    idempotencyKey: text('idempotency_key'),
    performedById: uuid('performed_by_id'),
    performedAt: ts('performed_at').notNull().defaultNow(),
    deviceId: text('device_id'),
    notes: text('notes'),
  },
  (t) => [
    uniqueIndex('inv_mov_idem_uq').on(t.tenantId, t.idempotencyKey),
    index('inv_mov_sku_time_idx').on(t.tenantId, t.warehouseId, t.storerId, t.skuId, t.performedAt),
    index('inv_mov_ref_idx').on(t.tenantId, t.refType, t.refId),
    index('inv_mov_time_idx').on(t.tenantId, t.performedAt),
  ],
);

export const serialUnits = pgTable(
  'serial_units',
  {
    id: id(),
    tenantId: tenantId(),
    storerId: uuid('storer_id').notNull().references(() => storers.id),
    skuId: uuid('sku_id').notNull().references(() => skus.id),
    serialNumber: text('serial_number').notNull(),
    status: serialStatusEnum('status').notNull().default('IN_STOCK'),
    warehouseId: uuid('warehouse_id'),
    locationId: uuid('location_id'),
    lpnId: uuid('lpn_id'),
    lotNumber: text('lot_number').notNull().default(''),
    expiryDate: date('expiry_date'),
    receivedAsnId: uuid('received_asn_id'),
    orderId: uuid('order_id'),
    lastMovementId: uuid('last_movement_id'),
    attributes: jsonb('attributes').notNull().default({}).$type<Record<string, unknown>>(),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [
    uniqueIndex('serial_units_uq').on(t.tenantId, t.storerId, t.skuId, t.serialNumber),
    index('serial_units_status_wh_idx').on(t.tenantId, t.status, t.warehouseId),
    index('serial_units_serial_idx').on(t.tenantId, t.serialNumber),
  ],
);

export const inventoryHolds = pgTable(
  'inventory_holds',
  {
    id: id(),
    tenantId: tenantId(),
    warehouseId: uuid('warehouse_id').notNull(),
    scope: holdScopeEnum('scope').notNull(),
    targetId: text('target_id').notNull(),
    storerId: uuid('storer_id'),
    skuId: uuid('sku_id'),
    reasonCodeId: uuid('reason_code_id'),
    toStatus: inventoryStatusEnum('to_status').notNull().default('HOLD'),
    affectedBalanceIds: uuid('affected_balance_ids').array().notNull().default([]),
    notes: text('notes'),
    placedById: uuid('placed_by_id').notNull(),
    placedAt: ts('placed_at').notNull().defaultNow(),
    releasedById: uuid('released_by_id'),
    releasedAt: ts('released_at'),
  },
  (t) => [index('inventory_holds_target_idx').on(t.tenantId, t.warehouseId, t.scope, t.targetId)],
);

export const inventoryAdjustments = pgTable(
  'inventory_adjustments',
  {
    id: id(),
    tenantId: tenantId(),
    warehouseId: uuid('warehouse_id').notNull().references(() => warehouses.id),
    storerId: uuid('storer_id').notNull().references(() => storers.id),
    adjNumber: text('adj_number').notNull(),
    status: approvalStatusEnum('status').notNull().default('DRAFT'),
    reasonCodeId: uuid('reason_code_id').notNull(),
    source: text('source').notNull().default('MANUAL'),
    countId: uuid('count_id'),
    notes: text('notes'),
    requestedById: uuid('requested_by_id').notNull(),
    approvedById: uuid('approved_by_id'),
    approvedAt: ts('approved_at'),
    rejectedReason: text('rejected_reason'),
    postedAt: ts('posted_at'),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex('inventory_adjustments_number_uq').on(t.tenantId, t.adjNumber), index('inventory_adjustments_wh_status_idx').on(t.tenantId, t.warehouseId, t.status)],
);

export const inventoryAdjustmentLines = pgTable(
  'inventory_adjustment_lines',
  {
    id: id(),
    tenantId: tenantId(),
    adjustmentId: uuid('adjustment_id').notNull().references(() => inventoryAdjustments.id, { onDelete: 'cascade' }),
    skuId: uuid('sku_id').notNull().references(() => skus.id),
    locationId: uuid('location_id').notNull().references(() => locations.id),
    lpnId: uuid('lpn_id'),
    lotNumber: text('lot_number').notNull().default(''),
    expiryDate: date('expiry_date'),
    fromStatus: inventoryStatusEnum('from_status').notNull().default('AVAILABLE'),
    toStatus: inventoryStatusEnum('to_status'),
    qtyDelta: qty('qty_delta').notNull(),
    serials: text('serials').array().notNull().default([]),
    movementId: uuid('movement_id'),
    notes: text('notes'),
  },
  (t) => [index('inventory_adjustment_lines_adj_idx').on(t.tenantId, t.adjustmentId)],
);

export const cycleCounts = pgTable(
  'cycle_counts',
  {
    id: id(),
    tenantId: tenantId(),
    warehouseId: uuid('warehouse_id').notNull().references(() => warehouses.id),
    countNumber: text('count_number').notNull(),
    type: countTypeEnum('type').notNull().default('CYCLE'),
    status: countStatusEnum('status').notNull().default('PLANNED'),
    storerId: uuid('storer_id'),
    scope: jsonb('scope').notNull().default({}).$type<Record<string, unknown>>(),
    blind: boolean('blind').notNull().default(true),
    recountTolerancePct: pct('recount_tolerance_pct').notNull().default(sql`0`),
    scheduledFor: ts('scheduled_for'),
    releasedAt: ts('released_at'),
    completedAt: ts('completed_at'),
    createdById: uuid('created_by_id').notNull(),
    notes: text('notes'),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex('cycle_counts_number_uq').on(t.tenantId, t.countNumber), index('cycle_counts_wh_status_idx').on(t.tenantId, t.warehouseId, t.status)],
);

export const cycleCountLines = pgTable(
  'cycle_count_lines',
  {
    id: id(),
    tenantId: tenantId(),
    countId: uuid('count_id').notNull().references(() => cycleCounts.id, { onDelete: 'cascade' }),
    locationId: uuid('location_id').notNull().references(() => locations.id),
    skuId: uuid('sku_id'),
    storerId: uuid('storer_id'),
    lpnId: uuid('lpn_id'),
    lotNumber: text('lot_number').notNull().default(''),
    expiryDate: date('expiry_date'),
    inventoryStatus: inventoryStatusEnum('inventory_status').notNull().default('AVAILABLE'),
    systemQty: qty('system_qty').notNull().default(sql`0`),
    countedQty: qty('counted_qty'),
    recountQty: qty('recount_qty'),
    finalQty: qty('final_qty'),
    varianceQty: qty('variance_qty'),
    status: countLineStatusEnum('status').notNull().default('PENDING'),
    countedById: uuid('counted_by_id'),
    countedAt: ts('counted_at'),
    recountById: uuid('recount_by_id'),
    recountAt: ts('recount_at'),
    approvedById: uuid('approved_by_id'),
    adjustmentId: uuid('adjustment_id'),
    notes: text('notes'),
  },
  (t) => [index('cycle_count_lines_count_status_idx').on(t.tenantId, t.countId, t.status), index('cycle_count_lines_location_idx').on(t.tenantId, t.locationId)],
);