import { sql } from 'drizzle-orm'
import type { Querier } from '@platform-modules/db'
import type { InventorySchema } from './schema.js'

export class InventoryMigrateError extends Error {
  override readonly name = 'InventoryMigrateError'
  readonly code = 'INVENTORY_MIGRATE' as const

  constructor(readonly detail: string) {
    super(`inventory migrate: ${detail}`)
  }
}

export function isInventoryMigrateError(e: unknown): e is InventoryMigrateError {
  return (
    typeof e === 'object' &&
    e !== null &&
    (e as { name?: unknown }).name === 'InventoryMigrateError' &&
    (e as { code?: unknown }).code === 'INVENTORY_MIGRATE'
  )
}

const MIGRATION_STATEMENTS = [
  sql`
    CREATE TABLE IF NOT EXISTS stock_location (
      id uuid PRIMARY KEY,
      tenant_id uuid NOT NULL,
      name text NOT NULL,
      code text,
      is_default boolean NOT NULL DEFAULT false,
      is_active boolean NOT NULL DEFAULT true,
      created_at timestamptz NOT NULL DEFAULT now(),
      updated_at timestamptz NOT NULL DEFAULT now()
    )
  `,
  sql`
    CREATE TABLE IF NOT EXISTS stock_item (
      id uuid PRIMARY KEY,
      tenant_id uuid NOT NULL,
      sku text,
      name text NOT NULL,
      uom text NOT NULL DEFAULT 'unit',
      method text NOT NULL DEFAULT 'weighted_average',
      is_active boolean NOT NULL DEFAULT true,
      created_at timestamptz NOT NULL DEFAULT now(),
      updated_at timestamptz NOT NULL DEFAULT now(),
      CONSTRAINT ck_stock_item_method CHECK (method IN ('fifo', 'weighted_average'))
    )
  `,
  sql`
    CREATE TABLE IF NOT EXISTS stock_movement (
      id uuid PRIMARY KEY,
      tenant_id uuid NOT NULL,
      item_id uuid NOT NULL,
      location_id uuid NOT NULL,
      kind text NOT NULL,
      qty_delta numeric(18,4) NOT NULL,
      unit_cost numeric(18,6),
      cogs_amount numeric(18,4),
      pending_cost boolean NOT NULL DEFAULT false,
      holder_ref text,
      transfer_group uuid,
      occurred_at timestamptz NOT NULL,
      created_at timestamptz NOT NULL DEFAULT now(),
      CONSTRAINT ck_stock_movement_kind CHECK (
        kind IN ('receipt', 'issue', 'adjust', 'transfer_out', 'transfer_in', 'count_variance')
      )
    )
  `,
  sql`
    CREATE TABLE IF NOT EXISTS stock_cost_layer (
      id uuid PRIMARY KEY,
      tenant_id uuid NOT NULL,
      item_id uuid NOT NULL,
      location_id uuid NOT NULL,
      receipt_movement_id uuid NOT NULL,
      unit_cost numeric(18,6) NOT NULL,
      qty_remaining numeric(18,4) NOT NULL,
      created_at timestamptz NOT NULL
    )
  `,
  sql`
    CREATE TABLE IF NOT EXISTS stock_position (
      tenant_id uuid NOT NULL,
      item_id uuid NOT NULL,
      location_id uuid NOT NULL,
      qty_on_hand numeric(18,4) NOT NULL,
      avg_unit_cost numeric(18,6) NOT NULL,
      updated_at timestamptz NOT NULL,
      PRIMARY KEY (tenant_id, item_id, location_id)
    )
  `,
  sql`
    CREATE UNIQUE INDEX IF NOT EXISTS uq_stock_location_tenant_code
    ON stock_location (tenant_id, code)
  `,
  sql`
    CREATE UNIQUE INDEX IF NOT EXISTS uq_stock_location_default_per_tenant
    ON stock_location (tenant_id)
    WHERE is_default
  `,
  sql`
    CREATE UNIQUE INDEX IF NOT EXISTS uq_stock_item_tenant_sku
    ON stock_item (tenant_id, sku)
    WHERE sku IS NOT NULL
  `,
  sql`
    CREATE INDEX IF NOT EXISTS ix_stock_movement_tenant_item_location_occurred_at
    ON stock_movement (tenant_id, item_id, location_id, occurred_at)
  `,
  sql`
    CREATE INDEX IF NOT EXISTS ix_stock_movement_tenant_holder_ref
    ON stock_movement (tenant_id, holder_ref)
  `,
  sql`
    CREATE UNIQUE INDEX IF NOT EXISTS uq_stock_movement_tenant_holder_ref_kind
    ON stock_movement (tenant_id, holder_ref, kind)
  `,
  sql`
    CREATE INDEX IF NOT EXISTS ix_stock_cost_layer_tenant_item_location_created_at
    ON stock_cost_layer (tenant_id, item_id, location_id, created_at)
  `,
]

export async function pushSchema(db: Querier<InventorySchema>): Promise<void> {
  try {
    for (const statement of MIGRATION_STATEMENTS) {
      await db.execute(statement)
    }
  } catch (e) {
    throw new InventoryMigrateError(e instanceof Error ? e.message : String(e))
  }
}