/**
 * PGLite test harness for fraud integration tests.
 *
 * Wave-1 seam decision: Option P2 — curated DDL (mandatory).
 *   pushSchema is NOT used because schema.ts has affiliate_admin_actions.admin_user_id .notNull()
 *   but runAutoSuspend inserts NULL (automated audit row, no human actor). pushSchema enforces
 *   NOT NULL → Wave 3f/3i would fail at INSERT. Curated DDL makes admin_user_id NULLABLE.
 *
 * PGLite limitation: does NOT support multiple commands in a single prepared statement.
 *   Each CREATE TABLE must be a separate db.execute() call.
 *
 * NOTE: deals DDL uses window_end (matches schema.ts). maturation.ts previously
 *   referenced d.expires_at (production bug — fixed). DDL and production SQL now agree.
 */

import { PGlite } from '@electric-sql/pglite'
import { drizzle } from 'drizzle-orm/pglite'
import { sql } from 'drizzle-orm'
import type { TransactionalDatabase } from '@platform-modules/db'
import { harnessSchema, type HarnessSchema } from './harness-schema.js'

export type TestDb = TransactionalDatabase<HarnessSchema>

let _client: PGlite | null = null
let _db: TestDb | null = null

/**
 * Boot PGLite + apply curated DDL schema.
 * Call in beforeAll. One instance per test FILE (PGLite is single-connection).
 */
export async function makeTestDb(): Promise<TestDb> {
  _client = new PGlite()
  _db = drizzle(_client, { schema: harnessSchema }) as unknown as TestDb

  // PGLite does not allow multiple statements per query — each CREATE TABLE is separate.
  // P2: curated DDL — covers only the ~14 fraud-relevant tables.
  // PII columns are plain text (no pgcrypto). FKs reference only tables in this set.
  // admin_user_id is intentionally NULLABLE (diverges from schema.ts .notNull()).
  // deals.expires_at is added (absent from schema.ts — maturation.ts SQL references it).

  await _db.execute(sql`
    CREATE TABLE IF NOT EXISTS users (
      id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      email TEXT UNIQUE,
      email_index TEXT UNIQUE,
      phone TEXT,
      phone_index TEXT UNIQUE,
      email_canonical_index TEXT,
      display_name TEXT,
      avatar_type TEXT NOT NULL DEFAULT 'ICON',
      avatar_value TEXT NOT NULL DEFAULT '',
      is_admin BOOLEAN NOT NULL DEFAULT false,
      account_state TEXT NOT NULL DEFAULT 'ACTIVE',
      email_verified_at TIMESTAMPTZ,
      session_version INTEGER NOT NULL DEFAULT 0,
      mh_version INTEGER NOT NULL DEFAULT 1,
      purchase_count INTEGER NOT NULL DEFAULT 0,
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
  `)

  await _db.execute(sql`
    CREATE TABLE IF NOT EXISTS referral_links (
      id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      owner_user_id UUID NOT NULL REFERENCES users(id),
      code TEXT NOT NULL UNIQUE,
      kind TEXT NOT NULL DEFAULT 'referral',
      commission_pct_override INTEGER,
      commission_window_days_override INTEGER,
      active BOOLEAN NOT NULL DEFAULT true,
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
  `)

  await _db.execute(sql`
    CREATE TABLE IF NOT EXISTS referrals (
      id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      referrer_user_id UUID NOT NULL REFERENCES users(id),
      referee_user_id UUID NOT NULL UNIQUE REFERENCES users(id),
      link_id UUID NOT NULL REFERENCES referral_links(id),
      kind TEXT NOT NULL DEFAULT 'referral',
      status TEXT NOT NULL DEFAULT 'pending',
      qualified_at TIMESTAMPTZ,
      clicked_at TIMESTAMPTZ,
      commission_pct INTEGER,
      commission_window_ends_at TIMESTAMPTZ,
      commission_orders_remaining INTEGER,
      referee_promo_code_id UUID,
      visitor_id_referrer TEXT,
      visitor_id_referee TEXT,
      ip_hash_referee TEXT,
      quarantined_at TIMESTAMPTZ,
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
  `)

  await _db.execute(sql`
    CREATE TABLE IF NOT EXISTS ledger_entries (
      id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      delta BIGINT NOT NULL,
      currency TEXT,
      reason TEXT NOT NULL,
      ref JSONB,
      idempotency_key TEXT NOT NULL,
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
  `)

  await _db.execute(sql`
    CREATE UNIQUE INDEX IF NOT EXISTS ledger_entries_idempotency_key_uq
      ON ledger_entries (idempotency_key)
  `)

  await _db.execute(sql`
    CREATE TABLE IF NOT EXISTS wallet_balances (
      owner_id TEXT PRIMARY KEY,
      balance BIGINT NOT NULL DEFAULT 0,
      updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
  `)

  await _db.execute(sql`
    CREATE TABLE IF NOT EXISTS ledger_entry_vesting (
      entry_id UUID PRIMARY KEY REFERENCES ledger_entries(id),
      mature_at TIMESTAMPTZ,
      swept_at TIMESTAMPTZ,
      withdrawable_at TIMESTAMPTZ
    )
  `)

  await _db.execute(sql`
    CREATE INDEX IF NOT EXISTS ledger_entry_vesting_mature_idx
      ON ledger_entry_vesting (mature_at)
      WHERE swept_at IS NULL
  `)

  await _db.execute(sql`
    CREATE TABLE IF NOT EXISTS wallet_vesting (
      owner_id TEXT PRIMARY KEY,
      pending_minor BIGINT NOT NULL DEFAULT 0,
      matured_minor BIGINT NOT NULL DEFAULT 0,
      withdrawable_minor BIGINT NOT NULL DEFAULT 0,
      lifetime_earned_minor BIGINT NOT NULL DEFAULT 0,
      updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      CONSTRAINT wallet_vesting_pending_minor_nonneg CHECK (pending_minor >= 0),
      CONSTRAINT wallet_vesting_matured_minor_nonneg CHECK (matured_minor >= 0),
      CONSTRAINT wallet_vesting_withdrawable_minor_nonneg CHECK (withdrawable_minor >= 0)
    )
  `)

  await _db.execute(sql`
    CREATE TABLE IF NOT EXISTS affiliate_entries (
      entry_id UUID PRIMARY KEY REFERENCES ledger_entries(id),
      owner_id TEXT NOT NULL,
      entry_type TEXT NOT NULL,
      source_type TEXT NOT NULL,
      source_id TEXT NOT NULL,
      referral_id UUID REFERENCES referrals(id),
      resolved_pct SMALLINT,
      memo TEXT,
      consumed_at TIMESTAMPTZ
    )
  `)

  await _db.execute(sql`
    CREATE UNIQUE INDEX IF NOT EXISTS affiliate_entries_idem_uq
      ON affiliate_entries (entry_type, source_type, source_id)
  `)

  await _db.execute(sql`
    CREATE INDEX IF NOT EXISTS affiliate_entries_source_idx
      ON affiliate_entries (source_type, source_id)
  `)

  await _db.execute(sql`
    CREATE INDEX IF NOT EXISTS affiliate_entries_unconsumed_idx
      ON affiliate_entries (source_type, source_id)
      WHERE consumed_at IS NULL
  `)

  await _db.execute(sql`
    CREATE TABLE IF NOT EXISTS affiliate_enrollments (
      id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      user_id UUID NOT NULL UNIQUE REFERENCES users(id),
      status TEXT NOT NULL DEFAULT 'active',
      commission_pct INTEGER,
      enrolled_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      suspended_at TIMESTAMPTZ,
      suspended_reason TEXT,
      notes TEXT,
      stripe_account_id TEXT UNIQUE,
      stripe_status TEXT NOT NULL DEFAULT 'none',
      stripe_payouts_enabled BOOLEAN NOT NULL DEFAULT false,
      stripe_updated_at TIMESTAMPTZ,
      tos_accepted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      tos_version TEXT NOT NULL DEFAULT 'v1-2026-05-28'
    )
  `)

  await _db.execute(sql`
    CREATE TABLE IF NOT EXISTS affiliate_admin_actions (
      id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      ts TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      admin_user_id UUID REFERENCES users(id),
      target_user_id UUID REFERENCES users(id),
      action TEXT NOT NULL,
      payload JSONB NOT NULL DEFAULT '{}',
      reason TEXT
    )
  `)
  // NOTE: admin_user_id is INTENTIONALLY NULLABLE above.
  // schema.ts has .notNull() but runAutoSuspend INSERTs admin_user_id = NULL for
  // automated audit rows (no human actor). Using pushSchema (P1 path) would enforce
  // NOT NULL → Wave 3f/3i INSERT failures. Curated DDL makes it nullable.

  await _db.execute(sql`
    CREATE TABLE IF NOT EXISTS affiliate_payouts (
      id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      user_id UUID NOT NULL REFERENCES users(id),
      enrollment_id UUID NOT NULL REFERENCES affiliate_enrollments(id),
      amount_agorot BIGINT NOT NULL,
      status TEXT NOT NULL,
      requested_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      approved_at TIMESTAMPTZ,
      approved_by_user_id UUID REFERENCES users(id),
      processing_at TIMESTAMPTZ,
      paid_at TIMESTAMPTZ,
      failed_at TIMESTAMPTZ,
      failure_reason TEXT,
      stripe_transfer_id TEXT UNIQUE,
      stripe_payout_id TEXT UNIQUE,
      idempotency_key TEXT NOT NULL UNIQUE,
      ledger_entry_id UUID UNIQUE REFERENCES affiliate_entries(entry_id)
    )
  `)

  await _db.execute(sql`
    CREATE UNIQUE INDEX IF NOT EXISTS affiliate_payouts_one_active_idx
      ON affiliate_payouts (user_id)
      WHERE status IN ('requested', 'approved', 'processing')
  `)

  await _db.execute(sql`
    CREATE TABLE IF NOT EXISTS fraud_events (
      id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      user_id UUID NOT NULL REFERENCES users(id),
      referral_id UUID REFERENCES referrals(id),
      decision_point TEXT NOT NULL,
      adapter TEXT NOT NULL,
      action TEXT NOT NULL,
      codes TEXT[] NOT NULL DEFAULT '{}',
      detail JSONB,
      resolved_by UUID REFERENCES users(id),
      resolved_at TIMESTAMPTZ,
      ts TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
  `)

  await _db.execute(sql`
    CREATE TABLE IF NOT EXISTS referral_settings (
      id INTEGER PRIMARY KEY,
      affiliate_pct INTEGER NOT NULL DEFAULT 30,
      affiliate_window_days INTEGER NOT NULL DEFAULT 90,
      affiliate_max_orders INTEGER NOT NULL DEFAULT 50,
      cookie_days INTEGER NOT NULL DEFAULT 14,
      reward_agorot INTEGER NOT NULL DEFAULT 2000,
      referee_discount_agorot INTEGER NOT NULL DEFAULT 2000,
      hold_days INTEGER NOT NULL DEFAULT 30,
      withdrawal_min_agorot INTEGER NOT NULL DEFAULT 30000,
      auto_approve_lifetime_agorot INTEGER NOT NULL DEFAULT 50000,
      brand_keyword_blocklist TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
      disallowed_referer_hosts TEXT[] NOT NULL DEFAULT '{}',
      tos_version TEXT NOT NULL DEFAULT 'v1-2026-05-28',
      tier1_pct SMALLINT NOT NULL DEFAULT 3,
      tier2_pct SMALLINT NOT NULL DEFAULT 4,
      tier3_pct SMALLINT NOT NULL DEFAULT 5,
      tier2_min_sales INTEGER NOT NULL DEFAULT 20,
      tier3_min_sales INTEGER NOT NULL DEFAULT 50,
      referral_pct SMALLINT NOT NULL DEFAULT 2,
      fraud_config JSONB NOT NULL DEFAULT '{}',
      dispute_window_days INTEGER NOT NULL DEFAULT 120,
      reserve_pct INTEGER NOT NULL DEFAULT 0,
      reserve_release_days INTEGER NOT NULL DEFAULT 120,
      updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      updated_by_user_id UUID REFERENCES users(id)
    )
  `)

  await _db.execute(sql`
    CREATE TABLE IF NOT EXISTS deals (
      id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      deal_type TEXT NOT NULL DEFAULT 'COUPON',
      window_end TIMESTAMPTZ,
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
  `)

  await _db.execute(sql`
    CREATE TABLE IF NOT EXISTS purchases (
      id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      user_id UUID REFERENCES users(id),
      deal_id UUID REFERENCES deals(id),
      payment_status TEXT NOT NULL DEFAULT 'PENDING',
      redeemed_at TIMESTAMPTZ,
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
  `)

  await _db.execute(sql`
    CREATE TABLE IF NOT EXISTS payment_methods (
      id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      user_id UUID NOT NULL REFERENCES users(id),
      card_fingerprint TEXT
    )
  `)

  await seedHarnessDefaults(_db)

  await _db.execute(sql`
    CREATE TABLE IF NOT EXISTS referral_link_stats_daily (
      link_id UUID NOT NULL REFERENCES referral_links(id),
      day DATE NOT NULL,
      clicks INTEGER NOT NULL DEFAULT 0,
      clicks_suspicious INTEGER NOT NULL DEFAULT 0,
      signups INTEGER NOT NULL DEFAULT 0,
      purchases INTEGER NOT NULL DEFAULT 0,
      commission_agorot BIGINT NOT NULL DEFAULT 0,
      reward_agorot BIGINT NOT NULL DEFAULT 0,
      PRIMARY KEY (link_id, day)
    )
  `)

  return _db
}

/**
 * Insert singleton config rows required by production code paths.
 * Mirrors migration 0071: INSERT INTO referral_settings (id) VALUES (1).
 * Column defaults come from the curated DDL above (aligned with schema.ts).
 */
async function seedHarnessDefaults(db: TestDb): Promise<void> {
  await db.execute(sql`
    INSERT INTO referral_settings (id)
    VALUES (1)
    ON CONFLICT (id) DO NOTHING
  `)
}

/**
 * Truncate all fraud-relevant tables in dependency-safe reverse order.
 * Call in beforeEach (or afterEach) to isolate test cases.
 *
 * PGLite does not support multi-statement queries, so we use CASCADE on
 * the root tables only (users, deals) which cascades to all dependents.
 */
export async function resetTables(db: TestDb): Promise<void> {
  // Truncate leaf tables first, then roots. CASCADE handles FK chains.
  await db.execute(sql`TRUNCATE TABLE referral_link_stats_daily CASCADE`)
  await db.execute(sql`TRUNCATE TABLE payment_methods CASCADE`)
  await db.execute(sql`TRUNCATE TABLE fraud_events CASCADE`)
  await db.execute(sql`TRUNCATE TABLE affiliate_payouts CASCADE`)
  await db.execute(sql`TRUNCATE TABLE affiliate_admin_actions CASCADE`)
  await db.execute(sql`TRUNCATE TABLE affiliate_enrollments CASCADE`)
  await db.execute(sql`TRUNCATE TABLE affiliate_entries CASCADE`)
  await db.execute(sql`TRUNCATE TABLE ledger_entry_vesting CASCADE`)
  await db.execute(sql`TRUNCATE TABLE wallet_vesting CASCADE`)
  await db.execute(sql`TRUNCATE TABLE ledger_entries CASCADE`)
  await db.execute(sql`TRUNCATE TABLE wallet_balances CASCADE`)
  await db.execute(sql`TRUNCATE TABLE purchases CASCADE`)
  await db.execute(sql`TRUNCATE TABLE referrals CASCADE`)
  await db.execute(sql`TRUNCATE TABLE referral_links CASCADE`)
  await db.execute(sql`TRUNCATE TABLE referral_settings CASCADE`)
  await db.execute(sql`TRUNCATE TABLE deals CASCADE`)
  await db.execute(sql`TRUNCATE TABLE users CASCADE`)
  await seedHarnessDefaults(db)
}

/**
 * Close the PGLite instance. Call in afterAll.
 */
export async function closeTestDb(): Promise<void> {
  if (_client) {
    await _client.close()
    _client = null
    _db = null
  }
}