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, temperature } from './_common';
import {
  orderTypeEnum, orderStatusEnum, docSourceEnum, lineStatusEnum, packLevelEnum, allocationStatusEnum,
  waveStatusEnum, waveStrategyEnum, shipmentStatusEnum, loadStatusEnum, returnStatusEnum, returnDispositionEnum,
} from './enums';
import { warehouses, storers, skus, locations, customers, carriers } from './master-data';
import { inventoryBalances } from './inventory';

export const waves = pgTable(
  'waves',
  {
    id: id(),
    tenantId: tenantId(),
    warehouseId: uuid('warehouse_id').notNull().references(() => warehouses.id),
    waveNumber: text('wave_number').notNull(),
    status: waveStatusEnum('status').notNull().default('PLANNED'),
    strategy: waveStrategyEnum('strategy').notNull().default('BY_ORDER'),
    criteria: jsonb('criteria').notNull().default({}).$type<Record<string, unknown>>(),
    orderCount: integer('order_count').notNull().default(0),
    lineCount: integer('line_count').notNull().default(0),
    taskCount: integer('task_count').notNull().default(0),
    shortLines: integer('short_lines').notNull().default(0),
    createdById: uuid('created_by_id').notNull(),
    allocatedAt: ts('allocated_at'),
    releasedAt: ts('released_at'),
    completedAt: ts('completed_at'),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex('waves_tenant_number_uq').on(t.tenantId, t.waveNumber), index('waves_wh_status_idx').on(t.tenantId, t.warehouseId, t.status)],
);

export const loads = pgTable(
  'loads',
  {
    id: id(),
    tenantId: tenantId(),
    warehouseId: uuid('warehouse_id').notNull().references(() => warehouses.id),
    loadNumber: text('load_number').notNull(),
    status: loadStatusEnum('status').notNull().default('PLANNED'),
    carrierId: uuid('carrier_id').references(() => carriers.id),
    carrierName: text('carrier_name'),
    vehicleNo: text('vehicle_no'),
    vehicleType: text('vehicle_type'),
    driverName: text('driver_name'),
    driverIdNo: text('driver_id_no'),
    driverPhone: text('driver_phone'),
    sealNo: text('seal_no'),
    dockLocationId: uuid('dock_location_id').references(() => locations.id),
    scheduledAt: ts('scheduled_at'),
    loadingStartedAt: ts('loading_started_at'),
    loadedAt: ts('loaded_at'),
    departedAt: ts('departed_at'),
    deliveredAt: ts('delivered_at'),
    gatePassNumber: text('gate_pass_number'),
    gatePassDocumentId: uuid('gate_pass_document_id'),
    temperatureAtDispatch: temperature('temperature_at_dispatch'),
    pod: jsonb('pod').$type<Record<string, unknown>>(),
    notes: text('notes'),
    createdById: uuid('created_by_id').notNull(),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex('loads_tenant_number_uq').on(t.tenantId, t.loadNumber), index('loads_wh_status_idx').on(t.tenantId, t.warehouseId, t.status)],
);

export const outboundOrders = pgTable(
  'outbound_orders',
  {
    id: id(),
    tenantId: tenantId(),
    warehouseId: uuid('warehouse_id').notNull().references(() => warehouses.id),
    storerId: uuid('storer_id').notNull().references(() => storers.id),
    orderNumber: text('order_number').notNull(),
    externalRef: text('external_ref'),
    type: orderTypeEnum('type').notNull().default('SALES'),
    status: orderStatusEnum('status').notNull().default('OPEN'),
    source: docSourceEnum('source').notNull().default('MANUAL'),
    priority: integer('priority').notNull().default(5),
    customerId: uuid('customer_id').references(() => customers.id),
    shipTo: jsonb('ship_to').notNull().default({}).$type<Record<string, unknown>>(),
    carrierId: uuid('carrier_id').references(() => carriers.id),
    carrierName: text('carrier_name'),
    serviceLevel: text('service_level'),
    requestedShipDate: date('requested_ship_date'),
    dueDate: ts('due_date'),
    waveId: uuid('wave_id').references(() => waves.id),
    loadId: uuid('load_id').references(() => loads.id),
    holdReason: text('hold_reason'),
    notes: text('notes'),
    attributes: jsonb('attributes').notNull().default({}).$type<Record<string, unknown>>(),
    totalOrderedQty: qty('total_ordered_qty').notNull().default(sql`0`),
    totalShippedQty: qty('total_shipped_qty').notNull().default(sql`0`),
    totalCbm: cbm('total_cbm'),
    totalWeightKg: kg('total_weight_kg'),
    allocatedAt: ts('allocated_at'),
    releasedAt: ts('released_at'),
    pickedAt: ts('picked_at'),
    packedAt: ts('packed_at'),
    shippedAt: ts('shipped_at'),
    cancelledAt: ts('cancelled_at'),
    createdById: uuid('created_by_id'),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [
    uniqueIndex('orders_tenant_number_uq').on(t.tenantId, t.orderNumber),
    index('orders_wh_status_prio_idx').on(t.tenantId, t.warehouseId, t.status, t.priority),
    index('orders_storer_ref_idx').on(t.tenantId, t.storerId, t.externalRef),
    index('orders_wave_idx').on(t.tenantId, t.waveId),
  ],
);

export const outboundOrderLines = pgTable(
  'outbound_order_lines',
  {
    id: id(),
    tenantId: tenantId(),
    orderId: uuid('order_id').notNull().references(() => outboundOrders.id, { onDelete: 'cascade' }),
    lineNo: integer('line_no').notNull(),
    skuId: uuid('sku_id').notNull().references(() => skus.id),
    orderedQty: qty('ordered_qty').notNull(),
    packLevel: packLevelEnum('pack_level').notNull().default('EACH'),
    packQty: qty('pack_qty'),
    allocatedQty: qty('allocated_qty').notNull().default(sql`0`),
    pickedQty: qty('picked_qty').notNull().default(sql`0`),
    packedQty: qty('packed_qty').notNull().default(sql`0`),
    shippedQty: qty('shipped_qty').notNull().default(sql`0`),
    cancelledQty: qty('cancelled_qty').notNull().default(sql`0`),
    requestedLot: text('requested_lot'),
    requestedSerials: text('requested_serials').array().notNull().default([]),
    minExpiryDate: date('min_expiry_date'),
    status: lineStatusEnum('status').notNull().default('OPEN'),
    externalLineRef: text('external_line_ref'),
    unitPrice: money('unit_price'),
    notes: text('notes'),
    attributes: jsonb('attributes').notNull().default({}).$type<Record<string, unknown>>(),
  },
  (t) => [uniqueIndex('order_lines_order_lineno_uq').on(t.orderId, t.lineNo), index('order_lines_sku_idx').on(t.tenantId, t.skuId)],
);

/** Reservation of a specific balance for an order line. */
export const allocations = pgTable(
  'allocations',
  {
    id: id(),
    tenantId: tenantId(),
    orderLineId: uuid('order_line_id').notNull().references(() => outboundOrderLines.id, { onDelete: 'cascade' }),
    orderId: uuid('order_id').notNull(),
    balanceId: uuid('balance_id').notNull().references(() => inventoryBalances.id),
    skuId: uuid('sku_id').notNull(),
    locationId: uuid('location_id').notNull(),
    lpnId: uuid('lpn_id'),
    lotNumber: text('lot_number').notNull().default(''),
    expiryDate: date('expiry_date'),
    qty: qty('qty').notNull(),
    pickedQty: qty('picked_qty').notNull().default(sql`0`),
    status: allocationStatusEnum('status').notNull().default('ALLOCATED'),
    pickTaskId: uuid('pick_task_id'),
    serials: text('serials').array().notNull().default([]),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [
    index('allocations_line_idx').on(t.tenantId, t.orderLineId),
    index('allocations_balance_status_idx').on(t.tenantId, t.balanceId, t.status),
    index('allocations_order_idx').on(t.tenantId, t.orderId),
  ],
);

export const shipments = pgTable(
  'shipments',
  {
    id: id(),
    tenantId: tenantId(),
    warehouseId: uuid('warehouse_id').notNull().references(() => warehouses.id),
    orderId: uuid('order_id').notNull().references(() => outboundOrders.id),
    shipmentNumber: text('shipment_number').notNull(),
    status: shipmentStatusEnum('status').notNull().default('PACKING'),
    packingLocationId: uuid('packing_location_id').references(() => locations.id),
    stagingLocationId: uuid('staging_location_id').references(() => locations.id),
    loadId: uuid('load_id').references(() => loads.id),
    carrierId: uuid('carrier_id'),
    trackingNo: text('tracking_no'),
    cartonCount: integer('carton_count').notNull().default(0),
    palletCount: integer('pallet_count').notNull().default(0),
    totalWeightKg: kg('total_weight_kg'),
    totalCbm: cbm('total_cbm'),
    dnNumber: text('dn_number'),
    dnDocumentId: uuid('dn_document_id'),
    packingListDocumentId: uuid('packing_list_document_id'),
    packedAt: ts('packed_at'),
    stagedAt: ts('staged_at'),
    loadedAt: ts('loaded_at'),
    shippedAt: ts('shipped_at'),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex('shipments_tenant_number_uq').on(t.tenantId, t.shipmentNumber), index('shipments_order_idx').on(t.tenantId, t.orderId), index('shipments_load_idx').on(t.tenantId, t.loadId)],
);

export const cartons = pgTable(
  'cartons',
  {
    id: id(),
    tenantId: tenantId(),
    shipmentId: uuid('shipment_id').notNull().references(() => shipments.id, { onDelete: 'cascade' }),
    cartonNumber: text('carton_number').notNull(),
    type: text('type').notNull().default('CARTON'),
    lpnId: uuid('lpn_id'),
    weightKg: kg('weight_kg'),
    lengthCm: cm('length_cm'),
    widthCm: cm('width_cm'),
    heightCm: cm('height_cm'),
    trackingNo: text('tracking_no'),
    sealed: boolean('sealed').notNull().default(false),
    createdAt: createdAt(),
  },
  (t) => [uniqueIndex('cartons_tenant_number_uq').on(t.tenantId, t.cartonNumber)],
);

export const cartonLines = pgTable(
  'carton_lines',
  {
    id: id(),
    tenantId: tenantId(),
    cartonId: uuid('carton_id').notNull().references(() => cartons.id, { onDelete: 'cascade' }),
    orderLineId: uuid('order_line_id').notNull(),
    skuId: uuid('sku_id').notNull(),
    qty: qty('qty').notNull(),
    lotNumber: text('lot_number').notNull().default(''),
    expiryDate: date('expiry_date'),
    serials: text('serials').array().notNull().default([]),
  },
  (t) => [index('carton_lines_order_line_idx').on(t.tenantId, t.orderLineId)],
);

export const returnOrders = pgTable(
  'return_orders',
  {
    id: id(),
    tenantId: tenantId(),
    warehouseId: uuid('warehouse_id').notNull().references(() => warehouses.id),
    storerId: uuid('storer_id').notNull().references(() => storers.id),
    rmaNumber: text('rma_number').notNull(),
    originalOrderId: uuid('original_order_id'),
    status: returnStatusEnum('status').notNull().default('REQUESTED'),
    reasonCodeId: uuid('reason_code_id'),
    asnId: uuid('asn_id'),
    customerRef: text('customer_ref'),
    notes: text('notes'),
    createdById: uuid('created_by_id').notNull(),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex('return_orders_number_uq').on(t.tenantId, t.rmaNumber), index('return_orders_wh_status_idx').on(t.tenantId, t.warehouseId, t.status)],
);

export const returnOrderLines = pgTable(
  'return_order_lines',
  {
    id: id(),
    tenantId: tenantId(),
    returnId: uuid('return_id').notNull().references(() => returnOrders.id, { onDelete: 'cascade' }),
    lineNo: integer('line_no').notNull(),
    skuId: uuid('sku_id').notNull().references(() => skus.id),
    expectedQty: qty('expected_qty').notNull(),
    receivedQty: qty('received_qty').notNull().default(sql`0`),
    disposition: returnDispositionEnum('disposition'),
    reasonCodeId: uuid('reason_code_id'),
    serials: text('serials').array().notNull().default([]),
    notes: text('notes'),
  },
  (t) => [uniqueIndex('return_order_lines_uq').on(t.returnId, t.lineNo)],
);