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

export class FulfillmentMigrateError extends Error {
  override readonly name = 'FulfillmentMigrateError'
  readonly code = 'FULFILLMENT_MIGRATE' as const

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

export function isFulfillmentMigrateError(e: unknown): e is FulfillmentMigrateError {
  return (
    typeof e === 'object' &&
    e !== null &&
    (e as { name?: unknown }).name === 'FulfillmentMigrateError' &&
    (e as { code?: unknown }).code === 'FULFILLMENT_MIGRATE'
  )
}

const MIGRATION_STATEMENTS = [
  sql`
    CREATE TABLE IF NOT EXISTS access_grant (
      id uuid PRIMARY KEY,
      order_id uuid NOT NULL,
      item_id text NOT NULL,
      owner_key text NOT NULL,
      blob_key text NOT NULL,
      created_at timestamptz NOT NULL DEFAULT NOW()
    )
  `,
  sql`
    CREATE UNIQUE INDEX IF NOT EXISTS uq_access_grant_owner
    ON access_grant (order_id, item_id, owner_key)
  `,
  sql`
    CREATE TABLE IF NOT EXISTS voucher (
      id uuid PRIMARY KEY,
      order_id uuid NOT NULL,
      line_id text NOT NULL,
      unit_index integer NOT NULL,
      vendor_id text,
      state text NOT NULL DEFAULT 'UNREDEEMED',
      expires_at timestamptz,
      redeemed_at timestamptz,
      created_at timestamptz NOT NULL DEFAULT NOW(),
      CONSTRAINT voucher_state_chk CHECK (state IN ('UNREDEEMED', 'REDEEMED', 'EXPIRED', 'CANCELLED')),
      CONSTRAINT voucher_unit_index_chk CHECK (unit_index >= 0)
    )
  `,
  sql`
    CREATE UNIQUE INDEX IF NOT EXISTS uq_voucher_unit
    ON voucher (order_id, line_id, unit_index)
  `,
  sql`
    CREATE TABLE IF NOT EXISTS shipment (
      id uuid PRIMARY KEY,
      order_id uuid NOT NULL,
      line_ids jsonb NOT NULL,
      address jsonb NOT NULL,
      status text NOT NULL DEFAULT 'pending',
      carrier_kind text,
      label_id text,
      tracking_number text,
      created_at timestamptz NOT NULL DEFAULT NOW(),
      updated_at timestamptz NOT NULL DEFAULT NOW(),
      CONSTRAINT shipment_status_chk CHECK (status IN ('pending', 'labeled', 'in_transit', 'delivered', 'failed'))
    )
  `,
  sql`
    CREATE TABLE IF NOT EXISTS carrier_webhook_event (
      event_id text PRIMARY KEY,
      shipment_id uuid,
      received_at timestamptz NOT NULL DEFAULT NOW()
    )
  `,
]

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