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

const MIGRATION_STATEMENTS = [
  sql`
    CREATE TABLE IF NOT EXISTS "order" (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      buyer_user_id uuid,
      buyer_guest_email text,
      status text NOT NULL DEFAULT 'pending',
      currency text NOT NULL,
      price_mode text NOT NULL,
      subtotal bigint NOT NULL,
      tax bigint NOT NULL,
      discount bigint NOT NULL,
      total bigint NOT NULL,
      charge_ref text,
      failure_reason text,
      idempotency_key text NOT NULL,
      request_hash text NOT NULL,
      created_at timestamptz NOT NULL DEFAULT NOW(),
      updated_at timestamptz NOT NULL DEFAULT NOW(),
      CONSTRAINT order_buyer_chk CHECK (num_nonnulls(buyer_user_id, buyer_guest_email) = 1)
    )
  `,
  sql`
    CREATE TABLE IF NOT EXISTS order_line (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      order_id uuid NOT NULL,
      variant_id uuid NOT NULL,
      kind text NOT NULL,
      qty integer NOT NULL,
      unit_price bigint NOT NULL,
      line_total bigint NOT NULL,
      vendor_id text,
      created_at timestamptz NOT NULL DEFAULT NOW()
    )
  `,
  sql`
    CREATE TABLE IF NOT EXISTS vendor_split (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      order_id uuid NOT NULL,
      vendor_id text,
      amount bigint NOT NULL,
      funder text NOT NULL,
      created_at timestamptz NOT NULL DEFAULT NOW()
    )
  `,
  sql`
    CREATE TABLE IF NOT EXISTS order_step (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      order_id uuid NOT NULL,
      step_id text NOT NULL,
      result jsonb NOT NULL,
      created_at timestamptz NOT NULL DEFAULT NOW()
    )
  `,
  sql`
    CREATE TABLE IF NOT EXISTS refund_intent (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      order_id uuid NOT NULL,
      seq integer NOT NULL,
      amount bigint NOT NULL CONSTRAINT refund_intent_amount_chk CHECK (amount > 0),
      status text NOT NULL DEFAULT 'pending',
      refund_key text NOT NULL,
      provider_ref text,
      created_at timestamptz NOT NULL DEFAULT NOW(),
      updated_at timestamptz NOT NULL DEFAULT NOW()
    )
  `,
  sql`
    CREATE UNIQUE INDEX IF NOT EXISTS uq_order_step
    ON order_step (order_id, step_id)
  `,
  sql`
    CREATE UNIQUE INDEX IF NOT EXISTS uq_refund_intent_seq
    ON refund_intent (order_id, seq)
  `,
  sql`
    CREATE UNIQUE INDEX IF NOT EXISTS uq_order_idempotency_key
    ON "order" (idempotency_key)
  `,
  sql`
    CREATE INDEX IF NOT EXISTS ix_order_line_order
    ON order_line (order_id)
  `,
  sql`
    CREATE INDEX IF NOT EXISTS ix_refund_intent_order
    ON refund_intent (order_id)
    WHERE status IN ('pending', 'executed')
  `,
]

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