import { pgTable, text, boolean, integer, jsonb, uuid, index, uniqueIndex, primaryKey, unique } from 'drizzle-orm/pg-core';
import { id, tenantId, createdAt, updatedAt, ts, qty, cbm, kg, cm, money } from './_common';
import {
  activeStatusEnum, zoneTypeEnum, temperatureClassEnum, locationTypeEnum, locationUsageEnum, locationStatusEnum,
  rotationRuleEnum, storageConditionEnum, packLevelEnum, putawayStrategyEnum, reasonCodeTypeEnum,
} from './enums';
import { tenants } from './platform';

export const warehouses = pgTable(
  'warehouses',
  {
    id: id(),
    tenantId: tenantId().references(() => tenants.id),
    code: text('code').notNull(),
    name: text('name').notNull(),
    address: jsonb('address').notNull().default({}).$type<Record<string, unknown>>(),
    timezone: text('timezone').notNull().default('Asia/Kuwait'),
    status: activeStatusEnum('status').notNull().default('ACTIVE'),
    settings: jsonb('settings').notNull().default({}).$type<Record<string, unknown>>(),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex('warehouses_tenant_code_uq').on(t.tenantId, t.code)],
);

export const zones = pgTable(
  'zones',
  {
    id: id(),
    tenantId: tenantId(),
    warehouseId: uuid('warehouse_id').notNull().references(() => warehouses.id),
    code: text('code').notNull(),
    name: text('name').notNull(),
    type: zoneTypeEnum('type').notNull().default('STORAGE'),
    temperatureClass: temperatureClassEnum('temperature_class').notNull().default('AMBIENT'),
    pickSequence: integer('pick_sequence').notNull().default(100),
    status: activeStatusEnum('status').notNull().default('ACTIVE'),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex('zones_wh_code_uq').on(t.warehouseId, t.code), index('zones_tenant_idx').on(t.tenantId)],
);

export const locations = pgTable(
  'locations',
  {
    id: id(),
    tenantId: tenantId(),
    warehouseId: uuid('warehouse_id').notNull().references(() => warehouses.id),
    zoneId: uuid('zone_id').notNull().references(() => zones.id),
    code: text('code').notNull(),
    barcode: text('barcode').notNull(),
    checkDigit: text('check_digit'),
    aisle: text('aisle'),
    bay: text('bay'),
    level: text('level'),
    position: text('position'),
    type: locationTypeEnum('type').notNull().default('PALLET_RACK'),
    usage: locationUsageEnum('usage').notNull().default('RESERVE'),
    status: locationStatusEnum('status').notNull().default('ACTIVE'),
    abcClass: text('abc_class'),
    lengthCm: cm('length_cm'),
    widthCm: cm('width_cm'),
    heightCm: cm('height_cm'),
    cubicCapacityM3: cbm('cubic_capacity_m3'),
    weightCapacityKg: kg('weight_capacity_kg'),
    maxLpns: integer('max_lpns'),
    stackLimit: integer('stack_limit'),
    allowMixedSku: boolean('allow_mixed_sku').notNull().default(true),
    allowMixedLots: boolean('allow_mixed_lots').notNull().default(true),
    pickSequence: integer('pick_sequence').notNull().default(0),
    putawaySequence: integer('putaway_sequence').notNull().default(0),
    x: cm('x'),
    y: cm('y'),
    attributes: jsonb('attributes').notNull().default({}).$type<Record<string, unknown>>(),
    lastCountedAt: ts('last_counted_at'),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [
    uniqueIndex('locations_wh_code_uq').on(t.warehouseId, t.code),
    uniqueIndex('locations_wh_barcode_uq').on(t.warehouseId, t.barcode),
    index('locations_tenant_wh_zone_idx').on(t.tenantId, t.warehouseId, t.zoneId),
    index('locations_wh_usage_status_idx').on(t.warehouseId, t.usage, t.status),
  ],
);

/** Storer = owner of the goods (3PL client). */
export const storers = pgTable(
  'storers',
  {
    id: id(),
    tenantId: tenantId().references(() => tenants.id),
    code: text('code').notNull(),
    name: text('name').notNull(),
    legalName: text('legal_name'),
    taxId: text('tax_id'),
    address: jsonb('address').notNull().default({}).$type<Record<string, unknown>>(),
    contacts: jsonb('contacts').notNull().default([]).$type<unknown[]>(),
    status: activeStatusEnum('status').notNull().default('ACTIVE'),
    settings: jsonb('settings').notNull().default({}).$type<Record<string, unknown>>(),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex('storers_tenant_code_uq').on(t.tenantId, t.code)],
);

export const storerWarehouses = pgTable(
  'storer_warehouses',
  {
    storerId: uuid('storer_id').notNull().references(() => storers.id, { onDelete: 'cascade' }),
    warehouseId: uuid('warehouse_id').notNull().references(() => warehouses.id, { onDelete: 'cascade' }),
  },
  (t) => [primaryKey({ columns: [t.storerId, t.warehouseId] })],
);

export const suppliers = pgTable(
  'suppliers',
  {
    id: id(),
    tenantId: tenantId(),
    storerId: uuid('storer_id').references(() => storers.id),
    code: text('code').notNull(),
    name: text('name').notNull(),
    address: jsonb('address').notNull().default({}).$type<Record<string, unknown>>(),
    contacts: jsonb('contacts').notNull().default([]).$type<unknown[]>(),
    status: activeStatusEnum('status').notNull().default('ACTIVE'),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [unique('suppliers_tenant_storer_code_uq').on(t.tenantId, t.storerId, t.code).nullsNotDistinct(), index('suppliers_tenant_idx').on(t.tenantId)],
);

/** Consignee / ship-to party of a storer. */
export const customers = pgTable(
  'customers',
  {
    id: id(),
    tenantId: tenantId(),
    storerId: uuid('storer_id').notNull().references(() => storers.id),
    code: text('code').notNull(),
    name: text('name').notNull(),
    address: jsonb('address').notNull().default({}).$type<Record<string, unknown>>(),
    contacts: jsonb('contacts').notNull().default([]).$type<unknown[]>(),
    status: activeStatusEnum('status').notNull().default('ACTIVE'),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex('customers_tenant_storer_code_uq').on(t.tenantId, t.storerId, t.code), index('customers_tenant_idx').on(t.tenantId)],
);

export const carriers = pgTable(
  'carriers',
  {
    id: id(),
    tenantId: tenantId(),
    code: text('code').notNull(),
    name: text('name').notNull(),
    serviceLevels: text('service_levels').array().notNull().default([]),
    contacts: jsonb('contacts').notNull().default([]).$type<unknown[]>(),
    status: activeStatusEnum('status').notNull().default('ACTIVE'),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex('carriers_tenant_code_uq').on(t.tenantId, t.code)],
);

export const skus = pgTable(
  'skus',
  {
    id: id(),
    tenantId: tenantId(),
    storerId: uuid('storer_id').notNull().references(() => storers.id),
    code: text('code').notNull(),
    description: text('description').notNull(),
    longDescription: text('long_description'),
    category: text('category'),
    brand: text('brand'),
    groupCode: text('group_code'),
    materialType: text('material_type'),
    storageCondition: storageConditionEnum('storage_condition').notNull().default('AMBIENT'),
    rotationRule: rotationRuleEnum('rotation_rule').notNull().default('FIFO'),
    lotControlled: boolean('lot_controlled').notNull().default(false),
    expiryControlled: boolean('expiry_controlled').notNull().default(false),
    serialControlled: boolean('serial_controlled').notNull().default(false),
    captureSerialOnReceipt: boolean('capture_serial_on_receipt').notNull().default(true),
    shelfLifeDays: integer('shelf_life_days'),
    minShelfLifeInboundDays: integer('min_shelf_life_inbound_days'),
    minShelfLifeOutboundDays: integer('min_shelf_life_outbound_days'),
    baseUom: text('base_uom').notNull().default('EA'),
    weightKg: kg('weight_kg'),
    cbm: cbm('cbm'),
    lengthCm: cm('length_cm'),
    widthCm: cm('width_cm'),
    heightCm: cm('height_cm'),
    hsCode: text('hs_code'),
    countryOfOrigin: text('country_of_origin'),
    hazmat: boolean('hazmat').notNull().default(false),
    hazmatClass: text('hazmat_class'),
    abcClass: text('abc_class'),
    countFrequencyDays: integer('count_frequency_days'),
    unitValue: money('unit_value'),
    status: activeStatusEnum('status').notNull().default('ACTIVE'),
    attributes: jsonb('attributes').notNull().default({}).$type<Record<string, unknown>>(),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [
    uniqueIndex('skus_tenant_storer_code_uq').on(t.tenantId, t.storerId, t.code),
    index('skus_tenant_storer_status_idx').on(t.tenantId, t.storerId, t.status),
  ],
);

export const skuPacks = pgTable(
  'sku_packs',
  {
    id: id(),
    tenantId: tenantId(),
    skuId: uuid('sku_id').notNull().references(() => skus.id, { onDelete: 'cascade' }),
    level: packLevelEnum('level').notNull(),
    uomCode: text('uom_code').notNull(),
    qtyInBase: qty('qty_in_base').notNull(),
    lengthCm: cm('length_cm'),
    widthCm: cm('width_cm'),
    heightCm: cm('height_cm'),
    weightKg: kg('weight_kg'),
    barcode: text('barcode'),
    palletTi: integer('pallet_ti'),
    palletHi: integer('pallet_hi'),
    isDefaultReceiving: boolean('is_default_receiving').notNull().default(false),
    isDefaultShipping: boolean('is_default_shipping').notNull().default(false),
  },
  (t) => [uniqueIndex('sku_packs_sku_level_uq').on(t.skuId, t.level), index('sku_packs_tenant_barcode_idx').on(t.tenantId, t.barcode)],
);

export const skuBarcodes = pgTable(
  'sku_barcodes',
  {
    id: id(),
    tenantId: tenantId(),
    skuId: uuid('sku_id').notNull().references(() => skus.id, { onDelete: 'cascade' }),
    barcode: text('barcode').notNull(),
    packLevel: packLevelEnum('pack_level').notNull().default('EACH'),
    isPrimary: boolean('is_primary').notNull().default(false),
  },
  (t) => [uniqueIndex('sku_barcodes_tenant_barcode_uq').on(t.tenantId, t.barcode), index('sku_barcodes_sku_idx').on(t.skuId)],
);

/** Forward pick-face definition with replenishment min/max. */
export const skuSlottings = pgTable(
  'sku_slottings',
  {
    id: id(),
    tenantId: tenantId(),
    skuId: uuid('sku_id').notNull().references(() => skus.id, { onDelete: 'cascade' }),
    warehouseId: uuid('warehouse_id').notNull().references(() => warehouses.id),
    pickLocationId: uuid('pick_location_id').notNull().references(() => locations.id),
    minQty: qty('min_qty').notNull(),
    maxQty: qty('max_qty').notNull(),
    replenishPackLevel: packLevelEnum('replenish_pack_level').notNull().default('CASE'),
    active: boolean('active').notNull().default(true),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex('sku_slottings_uq').on(t.skuId, t.warehouseId, t.pickLocationId), index('sku_slottings_tenant_wh_idx').on(t.tenantId, t.warehouseId)],
);

export const putawayRules = pgTable(
  'putaway_rules',
  {
    id: id(),
    tenantId: tenantId(),
    warehouseId: uuid('warehouse_id').notNull().references(() => warehouses.id),
    name: text('name').notNull(),
    priority: integer('priority').notNull().default(100),
    criteria: jsonb('criteria').notNull().default({}).$type<Record<string, unknown>>(),
    strategy: putawayStrategyEnum('strategy').notNull().default('EMPTY_LOCATION'),
    targetZoneIds: uuid('target_zone_ids').array().notNull().default([]),
    allowMixedSku: boolean('allow_mixed_sku').notNull().default(true),
    allowMixedLots: boolean('allow_mixed_lots').notNull().default(false),
    respectCapacity: boolean('respect_capacity').notNull().default(true),
    active: boolean('active').notNull().default(true),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [index('putaway_rules_lookup_idx').on(t.tenantId, t.warehouseId, t.active, t.priority)],
);

export const allocationRules = pgTable(
  'allocation_rules',
  {
    id: id(),
    tenantId: tenantId(),
    storerId: uuid('storer_id').references(() => storers.id),
    skuId: uuid('sku_id').references(() => skus.id),
    name: text('name').notNull(),
    priority: integer('priority').notNull().default(100),
    rotation: rotationRuleEnum('rotation').notNull().default('FIFO'),
    pickFaceFirst: boolean('pick_face_first').notNull().default(true),
    fullLpnFirst: boolean('full_lpn_first').notNull().default(true),
    allowPartialLpn: boolean('allow_partial_lpn').notNull().default(true),
    minShelfLifeDays: integer('min_shelf_life_days'),
    allowedStatuses: text('allowed_statuses').array().notNull().default(['AVAILABLE']),
    zoneIds: uuid('zone_ids').array().notNull().default([]),
    active: boolean('active').notNull().default(true),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [index('allocation_rules_lookup_idx').on(t.tenantId, t.active, t.priority)],
);

export const reasonCodes = pgTable(
  'reason_codes',
  {
    id: id(),
    tenantId: tenantId(),
    type: reasonCodeTypeEnum('type').notNull(),
    code: text('code').notNull(),
    description: text('description').notNull(),
    requiresApproval: boolean('requires_approval').notNull().default(false),
    requiresPhoto: boolean('requires_photo').notNull().default(false),
    active: boolean('active').notNull().default(true),
  },
  (t) => [uniqueIndex('reason_codes_uq').on(t.tenantId, t.type, t.code)],
);

/** Document numbering per tenant (and optionally per warehouse). */
export const documentSequences = pgTable(
  'document_sequences',
  {
    id: id(),
    tenantId: tenantId(),
    warehouseId: uuid('warehouse_id'),
    docType: text('doc_type').notNull(),
    prefix: text('prefix').notNull(),
    nextNumber: integer('next_number').notNull().default(1),
    padding: integer('padding').notNull().default(6),
  },
  (t) => [unique('document_sequences_uq').on(t.tenantId, t.warehouseId, t.docType).nullsNotDistinct()],
);