import { sql } from 'drizzle-orm'
import {
  boolean,
  check,
  index,
  numeric,
  pgTable,
  primaryKey,
  text,
  timestamp,
  unique,
  uuid,
} from 'drizzle-orm/pg-core'

export const stockLocation = pgTable(
  'stock_location',
  {
    id: uuid('id').primaryKey(),
    tenantId: uuid('tenant_id').notNull(),
    name: text('name').notNull(),
    code: text('code'),
    isDefault: boolean('is_default').notNull().default(false),
    isActive: boolean('is_active').notNull().default(true),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (table) => [
    unique('uq_stock_location_tenant_code').on(table.tenantId, table.code),
  ],
)

export const stockItem = pgTable(
  'stock_item',
  {
    id: uuid('id').primaryKey(),
    tenantId: uuid('tenant_id').notNull(),
    sku: text('sku'),
    name: text('name').notNull(),
    uom: text('uom').notNull().default('unit'),
    method: text('method').notNull().default('weighted_average'),
    isActive: boolean('is_active').notNull().default(true),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (table) => [
    unique('uq_stock_item_tenant_sku').on(table.tenantId, table.sku),
    check('ck_stock_item_method', sql`${table.method} IN ('fifo', 'weighted_average')`),
  ],
)

export const stockMovement = pgTable(
  'stock_movement',
  {
    id: uuid('id').primaryKey(),
    tenantId: uuid('tenant_id').notNull(),
    itemId: uuid('item_id').notNull(),
    locationId: uuid('location_id').notNull(),
    kind: text('kind').notNull(),
    qtyDelta: numeric('qty_delta', { precision: 18, scale: 4 }).notNull(),
    unitCost: numeric('unit_cost', { precision: 18, scale: 6 }),
    cogsAmount: numeric('cogs_amount', { precision: 18, scale: 4 }),
    pendingCost: boolean('pending_cost').notNull().default(false),
    holderRef: text('holder_ref'),
    transferGroup: uuid('transfer_group'),
    occurredAt: timestamp('occurred_at', { withTimezone: true }).notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (table) => [
    check(
      'ck_stock_movement_kind',
      sql`${table.kind} IN ('receipt', 'issue', 'adjust', 'transfer_out', 'transfer_in', 'count_variance')`,
    ),
    index('ix_stock_movement_tenant_item_location_occurred_at').on(
      table.tenantId,
      table.itemId,
      table.locationId,
      table.occurredAt,
    ),
    index('ix_stock_movement_tenant_holder_ref').on(table.tenantId, table.holderRef),
    unique('uq_stock_movement_tenant_holder_ref_kind').on(
      table.tenantId,
      table.holderRef,
      table.kind,
    ),
  ],
)

export const stockCostLayer = pgTable(
  'stock_cost_layer',
  {
    id: uuid('id').primaryKey(),
    tenantId: uuid('tenant_id').notNull(),
    itemId: uuid('item_id').notNull(),
    locationId: uuid('location_id').notNull(),
    receiptMovementId: uuid('receipt_movement_id').notNull(),
    unitCost: numeric('unit_cost', { precision: 18, scale: 6 }).notNull(),
    qtyRemaining: numeric('qty_remaining', { precision: 18, scale: 4 }).notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull(),
  },
  (table) => [
    index('ix_stock_cost_layer_tenant_item_location_created_at').on(
      table.tenantId,
      table.itemId,
      table.locationId,
      table.createdAt,
    ),
  ],
)

export const stockPosition = pgTable(
  'stock_position',
  {
    tenantId: uuid('tenant_id').notNull(),
    itemId: uuid('item_id').notNull(),
    locationId: uuid('location_id').notNull(),
    qtyOnHand: numeric('qty_on_hand', { precision: 18, scale: 4 }).notNull(),
    avgUnitCost: numeric('avg_unit_cost', { precision: 18, scale: 6 }).notNull(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull(),
  },
  (table) => [
    primaryKey({
      name: 'stock_position_pkey',
      columns: [table.tenantId, table.itemId, table.locationId],
    }),
  ],
)

export const inventorySchema = {
  stockLocation,
  stockItem,
  stockMovement,
  stockCostLayer,
  stockPosition,
}

export type InventorySchema = typeof inventorySchema