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

export class CatalogMigrateError extends Error {
  override readonly name = 'CatalogMigrateError'
  readonly code = 'CATALOG_MIGRATE' as const

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

export function isCatalogMigrateError(e: unknown): e is CatalogMigrateError {
  return (
    typeof e === 'object' &&
    e !== null &&
    (e as { name?: unknown }).name === 'CatalogMigrateError' &&
    (e as { code?: unknown }).code === 'CATALOG_MIGRATE'
  )
}

const MIGRATION_STATEMENTS = [
  sql`
    CREATE TABLE IF NOT EXISTS product (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      kind text NOT NULL,
      vendor_id uuid,
      slug text NOT NULL,
      title text NOT NULL,
      description text,
      status text NOT NULL DEFAULT 'draft',
      media jsonb NOT NULL DEFAULT '[]'::jsonb,
      tags jsonb NOT NULL DEFAULT '[]'::jsonb,
      available_from timestamptz(3),
      available_until timestamptz(3),
      created_at timestamptz(3) NOT NULL DEFAULT NOW(),
      updated_at timestamptz(3) NOT NULL DEFAULT NOW()
    )
  `,
  sql`CREATE UNIQUE INDEX IF NOT EXISTS product_vendor_slug_uq ON product (vendor_id, slug)`,
  sql`CREATE INDEX IF NOT EXISTS product_vendor_idx ON product (vendor_id)`,
  sql`CREATE INDEX IF NOT EXISTS product_status_idx ON product (status)`,
  sql`
    CREATE TABLE IF NOT EXISTS variant (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      product_id uuid NOT NULL,
      sku text NOT NULL,
      attributes jsonb NOT NULL DEFAULT '{}'::jsonb,
      created_at timestamptz(3) NOT NULL DEFAULT NOW()
    )
  `,
  sql`CREATE UNIQUE INDEX IF NOT EXISTS variant_product_sku_uq ON variant (product_id, sku)`,
  sql`
    CREATE TABLE IF NOT EXISTS variant_price (
      variant_id uuid NOT NULL,
      currency text NOT NULL,
      amount bigint NOT NULL,
      price_mode text NOT NULL,
      PRIMARY KEY (variant_id, currency)
    )
  `,
  sql`CREATE UNIQUE INDEX IF NOT EXISTS variant_price_variant_currency_uq ON variant_price (variant_id, currency)`,
  sql`
    CREATE TABLE IF NOT EXISTS category (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      parent_id uuid,
      vendor_id uuid,
      name text NOT NULL,
      slug text NOT NULL
    )
  `,
  sql`CREATE UNIQUE INDEX IF NOT EXISTS category_vendor_slug_uq ON category (vendor_id, slug)`,
  sql`
    CREATE TABLE IF NOT EXISTS collection (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      vendor_id uuid,
      name text NOT NULL,
      slug text NOT NULL
    )
  `,
  sql`CREATE UNIQUE INDEX IF NOT EXISTS collection_vendor_slug_uq ON collection (vendor_id, slug)`,
  sql`
    CREATE TABLE IF NOT EXISTS product_category (
      product_id uuid NOT NULL,
      category_id uuid NOT NULL,
      PRIMARY KEY (product_id, category_id)
    )
  `,
  sql`
    CREATE TABLE IF NOT EXISTS product_collection (
      collection_id uuid NOT NULL,
      product_id uuid NOT NULL,
      position integer NOT NULL DEFAULT 0,
      PRIMARY KEY (collection_id, product_id)
    )
  `,
]

export async function pushSchema(db: Querier<CatalogSchema>): Promise<void> {
  try {
    for (const statement of MIGRATION_STATEMENTS) {
      await db.execute(statement)
    }
  } catch (e) {
    // carry the root cause (no info-disclosure floor here — migrate runs over the caller's own DB,
    // no trust boundary, no PII; swallowing the cause only blinds the operator). No ignored signals.
    throw new CatalogMigrateError(e instanceof Error ? e.message : String(e))
  }
}