import { pgTable, text, 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, temperature } from './_common';
import {
  asnTypeEnum, asnStatusEnum, docSourceEnum, lineStatusEnum, packLevelEnum, dockAppointmentStatusEnum,
  itemConditionEnum, discrepancyTypeEnum, discrepancyStatusEnum,
} from './enums';
import { warehouses, storers, skus, locations, suppliers } from './master-data';

/** Advance Shipping Notice / expected receipt. */
export const asns = pgTable(
  'asns',
  {
    id: id(),
    tenantId: tenantId(),
    warehouseId: uuid('warehouse_id').notNull().references(() => warehouses.id),
    storerId: uuid('storer_id').notNull().references(() => storers.id),
    asnNumber: text('asn_number').notNull(),
    externalRef: text('external_ref'),
    type: asnTypeEnum('type').notNull().default('PURCHASE'),
    status: asnStatusEnum('status').notNull().default('EXPECTED'),
    source: docSourceEnum('source').notNull().default('MANUAL'),
    supplierId: uuid('supplier_id').references(() => suppliers.id),
    priority: integer('priority').notNull().default(5),
    expectedAt: ts('expected_at'),
    carrierName: text('carrier_name'),
    vehicleNo: text('vehicle_no'),
    containerNo: text('container_no'),
    containerSize: text('container_size'),
    sealNo: text('seal_no'),
    sealIntact: text('seal_intact'),
    driverName: text('driver_name'),
    driverPhone: text('driver_phone'),
    dockLocationId: uuid('dock_location_id').references(() => locations.id),
    arrivedAt: ts('arrived_at'),
    startedAt: ts('started_at'),
    receivedAt: ts('received_at'),
    closedAt: ts('closed_at'),
    cancelledAt: ts('cancelled_at'),
    temperatureOnArrival: temperature('temperature_on_arrival'),
    documents: jsonb('documents').notNull().default([]).$type<unknown[]>(),
    notes: text('notes'),
    attributes: jsonb('attributes').notNull().default({}).$type<Record<string, unknown>>(),
    totalExpectedQty: qty('total_expected_qty').notNull().default(sql`0`),
    totalReceivedQty: qty('total_received_qty').notNull().default(sql`0`),
    totalCbm: cbm('total_cbm'),
    totalWeightKg: kg('total_weight_kg'),
    totalPallets: integer('total_pallets'),
    createdById: uuid('created_by_id'),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [
    uniqueIndex('asns_tenant_number_uq').on(t.tenantId, t.asnNumber),
    index('asns_tenant_wh_status_idx').on(t.tenantId, t.warehouseId, t.status),
    index('asns_tenant_storer_ref_idx').on(t.tenantId, t.storerId, t.externalRef),
  ],
);

export const asnLines = pgTable(
  'asn_lines',
  {
    id: id(),
    tenantId: tenantId(),
    asnId: uuid('asn_id').notNull().references(() => asns.id, { onDelete: 'cascade' }),
    lineNo: integer('line_no').notNull(),
    skuId: uuid('sku_id').notNull().references(() => skus.id),
    expectedQty: qty('expected_qty').notNull(),
    packLevel: packLevelEnum('pack_level').notNull().default('EACH'),
    packQty: qty('pack_qty'),
    expectedLot: text('expected_lot'),
    expectedExpiry: date('expected_expiry'),
    receivedQty: qty('received_qty').notNull().default(sql`0`),
    damagedQty: qty('damaged_qty').notNull().default(sql`0`),
    rejectedQty: qty('rejected_qty').notNull().default(sql`0`),
    status: lineStatusEnum('status').notNull().default('OPEN'),
    externalLineRef: text('external_line_ref'),
    unitValue: money('unit_value'),
    notes: text('notes'),
    attributes: jsonb('attributes').notNull().default({}).$type<Record<string, unknown>>(),
  },
  (t) => [uniqueIndex('asn_lines_asn_lineno_uq').on(t.asnId, t.lineNo), index('asn_lines_tenant_sku_idx').on(t.tenantId, t.skuId)],
);

export const dockAppointments = pgTable(
  'dock_appointments',
  {
    id: id(),
    tenantId: tenantId(),
    warehouseId: uuid('warehouse_id').notNull().references(() => warehouses.id),
    dockLocationId: uuid('dock_location_id').references(() => locations.id),
    direction: text('direction').notNull(),
    refType: text('ref_type'),
    refId: uuid('ref_id'),
    startsAt: ts('starts_at').notNull(),
    endsAt: ts('ends_at').notNull(),
    status: dockAppointmentStatusEnum('status').notNull().default('BOOKED'),
    carrierName: text('carrier_name'),
    vehicleNo: text('vehicle_no'),
    driverName: text('driver_name'),
    notes: text('notes'),
    createdById: uuid('created_by_id'),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [index('dock_appointments_wh_start_idx').on(t.tenantId, t.warehouseId, t.startsAt)],
);

/** One physical receiving confirmation (an RF scan) against an ASN. */
export const receiptLines = pgTable(
  'receipt_lines',
  {
    id: id(),
    tenantId: tenantId(),
    asnId: uuid('asn_id').notNull().references(() => asns.id, { onDelete: 'cascade' }),
    asnLineId: uuid('asn_line_id').references(() => asnLines.id),
    skuId: uuid('sku_id').notNull().references(() => skus.id),
    lpnId: uuid('lpn_id'),
    locationId: uuid('location_id').notNull().references(() => locations.id),
    qty: qty('qty').notNull(),
    packLevel: packLevelEnum('pack_level').notNull().default('EACH'),
    packQty: qty('pack_qty'),
    lotNumber: text('lot_number').notNull().default(''),
    expiryDate: date('expiry_date'),
    mfgDate: date('mfg_date'),
    condition: itemConditionEnum('condition').notNull().default('GOOD'),
    reasonCodeId: uuid('reason_code_id'),
    serials: text('serials').array().notNull().default([]),
    movementId: uuid('movement_id'),
    receivedById: uuid('received_by_id').notNull(),
    receivedAt: ts('received_at').notNull().defaultNow(),
    deviceId: text('device_id'),
    notes: text('notes'),
    reversedAt: ts('reversed_at'),
    reversedById: uuid('reversed_by_id'),
    reversalMovementId: uuid('reversal_movement_id'),
  },
  (t) => [index('receipt_lines_tenant_asn_idx').on(t.tenantId, t.asnId), index('receipt_lines_lpn_idx').on(t.lpnId)],
);

export const receivingDiscrepancies = pgTable(
  'receiving_discrepancies',
  {
    id: id(),
    tenantId: tenantId(),
    asnId: uuid('asn_id').notNull().references(() => asns.id, { onDelete: 'cascade' }),
    asnLineId: uuid('asn_line_id').references(() => asnLines.id),
    skuId: uuid('sku_id').notNull().references(() => skus.id),
    type: discrepancyTypeEnum('type').notNull(),
    expectedQty: qty('expected_qty').notNull().default(sql`0`),
    actualQty: qty('actual_qty').notNull().default(sql`0`),
    varianceQty: qty('variance_qty').notNull().default(sql`0`),
    status: discrepancyStatusEnum('status').notNull().default('OPEN'),
    resolution: text('resolution'),
    reportedAt: ts('reported_at'),
    resolvedAt: ts('resolved_at'),
    resolvedById: uuid('resolved_by_id'),
    notes: text('notes'),
    createdAt: createdAt(),
  },
  (t) => [index('receiving_discrepancies_asn_status_idx').on(t.tenantId, t.asnId, t.status)],
);

/** Goods Receipt Note: formal closure of an ASN. Stock becomes allocatable. */
export const grns = pgTable(
  'grns',
  {
    id: id(),
    tenantId: tenantId(),
    asnId: uuid('asn_id').notNull().unique().references(() => asns.id),
    grnNumber: text('grn_number').notNull(),
    postedAt: ts('posted_at').notNull().defaultNow(),
    postedById: uuid('posted_by_id').notNull(),
    summary: jsonb('summary').notNull().default({}).$type<Record<string, unknown>>(),
    documentId: uuid('document_id'),
    rcrDocumentId: uuid('rcr_document_id'),
    rcrSentAt: ts('rcr_sent_at'),
  },
  (t) => [uniqueIndex('grns_tenant_number_uq').on(t.tenantId, t.grnNumber)],
);