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

export class PromoMigrateError extends Error {
  override readonly name = 'PromoMigrateError'
  readonly code = 'PROMO_MIGRATE' as const

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

export function isPromoMigrateError(e: unknown): e is PromoMigrateError {
  return (
    typeof e === 'object' &&
    e !== null &&
    (e as { name?: unknown }).name === 'PromoMigrateError' &&
    (e as { code?: unknown }).code === 'PROMO_MIGRATE'
  )
}

const MIGRATION_STATEMENTS = [
  sql`
    CREATE TABLE IF NOT EXISTS promo (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      code text NOT NULL,
      kind text NOT NULL,
      value_bps integer,
      value_amount bigint,
      currency text,
      max_discount_amount bigint,
      bogo_buy_qty integer,
      bogo_get_qty integer,
      scope jsonb NOT NULL,
      eligibility jsonb NOT NULL,
      funder text NOT NULL,
      max_uses integer,
      per_user_cap integer,
      uses integer NOT NULL DEFAULT 0,
      starts_at timestamptz,
      ends_at timestamptz,
      min_order_amount bigint,
      max_order_amount bigint,
      active boolean NOT NULL DEFAULT true,
      vendor_id text,
      created_at timestamptz NOT NULL DEFAULT NOW(),
      updated_at timestamptz NOT NULL DEFAULT NOW(),
      CONSTRAINT promo_value_bps_chk CHECK (
        value_bps IS NULL OR (value_bps > 0 AND value_bps <= 10000)
      ),
      CONSTRAINT promo_value_amount_chk CHECK (value_amount IS NULL OR value_amount > 0),
      CONSTRAINT promo_max_discount_amount_chk CHECK (
        max_discount_amount IS NULL OR max_discount_amount > 0
      ),
      CONSTRAINT promo_max_order_amount_chk CHECK (
        max_order_amount IS NULL OR max_order_amount > 0
      ),
      CONSTRAINT promo_max_uses_chk CHECK (max_uses IS NULL OR max_uses > 0),
      CONSTRAINT promo_per_user_cap_chk CHECK (per_user_cap IS NULL OR per_user_cap > 0)
    )
  `,
  sql`
    ALTER TABLE promo
    ADD COLUMN IF NOT EXISTS max_discount_amount bigint
  `,
  sql`
    ALTER TABLE promo
    ADD COLUMN IF NOT EXISTS max_order_amount bigint
  `,
  sql`
    DO $$
    BEGIN
      ALTER TABLE promo
      ADD CONSTRAINT promo_max_discount_amount_chk
      CHECK (max_discount_amount IS NULL OR max_discount_amount > 0);
    EXCEPTION WHEN duplicate_object THEN
      NULL;
    END
    $$
  `,
  sql`
    DO $$
    BEGIN
      ALTER TABLE promo
      ADD CONSTRAINT promo_max_order_amount_chk
      CHECK (max_order_amount IS NULL OR max_order_amount > 0);
    EXCEPTION WHEN duplicate_object THEN
      NULL;
    END
    $$
  `,
  sql`
    CREATE TABLE IF NOT EXISTS promo_user_usage (
      promo_id uuid NOT NULL,
      user_id text NOT NULL,
      uses integer NOT NULL DEFAULT 0,
      created_at timestamptz NOT NULL DEFAULT NOW(),
      updated_at timestamptz NOT NULL DEFAULT NOW(),
      PRIMARY KEY (promo_id, user_id)
    )
  `,
  sql`
    CREATE UNIQUE INDEX IF NOT EXISTS uq_promo_code_platform
    ON promo (code)
    WHERE vendor_id IS NULL
  `,
  sql`
    CREATE UNIQUE INDEX IF NOT EXISTS uq_promo_code_vendor
    ON promo (code, vendor_id)
    WHERE vendor_id IS NOT NULL
  `,
]

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