import { sql } from 'drizzle-orm';
import type { Querier } from '@platform-modules/db';
import { authUsersStatusMigrationSql } from '@platform-modules/auth/engine-custom';
import { pushSchema as pushCatalogSchema } from '@platform-modules/commerce-catalog';
import { pushSchema as pushCartSchema } from '@platform-modules/commerce-cart/store-db';
import { pushSchema as pushCheckoutSchema } from '@platform-modules/commerce-checkout';
import { pushSchema as pushFulfillmentSchema } from '@platform-modules/commerce-fulfillment';
import { pushSchema as pushInventorySchema } from '@platform-modules/commerce-inventory';
import { pushSchema as pushOrdersSchema } from '@platform-modules/commerce-orders';
import { pushSchema as pushPromotionsSchema } from '@platform-modules/commerce-promotions';
import { pushReviewsSchema } from '@platform-modules/commerce-reviews';
import { CREATE_CHARGE_INTENTS_TABLE_SQL, CREATE_WEBHOOK_EVENTS_TABLE_SQL } from './checkout-store.js';
import type { DbResult } from './db.js';

async function executeSplitDdl(db: Querier, ddl: string): Promise<void> {
  for (const stmt of ddl.split(';').map((s) => s.trim()).filter(Boolean)) {
    await db.execute(sql.raw(stmt));
  }
}

async function executeStatements(db: Querier, statements: Parameters<Querier['execute']>[0][]): Promise<void> {
  for (const statement of statements) {
    await db.execute(statement);
  }
}

/** Idempotent auth_users / user_sessions / service_tokens DDL (engine-custom shape). */
async function applyAuthSchema(db: Querier): Promise<void> {
  await executeStatements(db, [
    sql`
      CREATE TABLE IF NOT EXISTS auth_users (
        id text PRIMARY KEY,
        email text NOT NULL UNIQUE,
        password_hash text,
        session_version integer NOT NULL DEFAULT 0,
        roles text NOT NULL DEFAULT '["user"]',
        status text NOT NULL DEFAULT 'active',
        password_changed_at timestamptz DEFAULT NULL,
        created_at timestamptz NOT NULL
      )
    `,
    sql`
      CREATE TABLE IF NOT EXISTS user_sessions (
        id text PRIMARY KEY,
        user_id text NOT NULL REFERENCES auth_users(id),
        refresh_token_hash text NOT NULL,
        previous_refresh_token_hash text,
        last_refreshed_at timestamptz,
        expires_at timestamptz NOT NULL,
        status text NOT NULL DEFAULT 'active'
      )
    `,
    sql`
      CREATE TABLE IF NOT EXISTS service_tokens (
        id text PRIMARY KEY,
        user_id text NOT NULL REFERENCES auth_users(id),
        token_hash text NOT NULL UNIQUE,
        label text NOT NULL,
        expires_at timestamptz,
        status text NOT NULL DEFAULT 'active',
        created_at timestamptz NOT NULL,
        last_used_at timestamptz
      )
    `,
  ]);
  await executeSplitDdl(db, authUsersStatusMigrationSql());
  await db.execute(
    sql`ALTER TABLE auth_users ADD COLUMN IF NOT EXISTS password_changed_at timestamptz DEFAULT NULL`,
  );
}

/** Idempotent ledger_entries + wallet_balances DDL. */
async function applyLedgerSchema(db: Querier): Promise<void> {
  await executeStatements(db, [
    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()
      )
    `,
    sql`CREATE UNIQUE INDEX IF NOT EXISTS ledger_entries_idempotency_key_uq ON ledger_entries (idempotency_key)`,
    sql`ALTER TABLE ledger_entries ADD COLUMN IF NOT EXISTS currency text`,
    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()
      )
    `,
  ]);
}

/** Idempotent jobs + outbox DDL (jobs module has no pushSchema export). */
async function applyJobsSchema(db: Querier): Promise<void> {
  await executeStatements(db, [
    sql`
      CREATE TABLE IF NOT EXISTS jobs (
        id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
        type text NOT NULL,
        payload jsonb NOT NULL,
        status text NOT NULL DEFAULT 'pending',
        scheduled_for timestamptz NOT NULL DEFAULT NOW(),
        attempts integer NOT NULL DEFAULT 0,
        max_attempts integer NOT NULL DEFAULT 5,
        last_error text,
        created_at timestamptz NOT NULL DEFAULT NOW(),
        processed_at timestamptz,
        failed_at timestamptz
      )
    `,
    sql`
      CREATE TABLE IF NOT EXISTS outbox (
        id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
        aggregate_type text NOT NULL,
        aggregate_id text NOT NULL,
        event_type text NOT NULL,
        payload jsonb NOT NULL,
        processed_at timestamptz,
        failed_at timestamptz,
        retry_count integer NOT NULL DEFAULT 0,
        last_error text,
        created_at timestamptz NOT NULL DEFAULT NOW()
      )
    `,
  ]);
}

export function isInstallAssetPath(pathname: string): boolean {
  return (
    pathname.startsWith('/_astro/') ||
    pathname === '/favicon.ico' ||
    pathname.startsWith('/favicon')
  );
}

/** Idempotent mod_storefront_settings DDL (claim target). Run before claimInstallOnce. */
export async function applySettingsSchema(db: Querier): Promise<void> {
  await executeStatements(db, [
    sql`
      CREATE TABLE IF NOT EXISTS mod_storefront_settings (
        id TEXT PRIMARY KEY,
        value TEXT NOT NULL,
        claimed_at TIMESTAMPTZ
      )
    `,
    sql`ALTER TABLE mod_storefront_settings ADD COLUMN IF NOT EXISTS claimed_at TIMESTAMPTZ`,
  ]);
}

/**
 * Apply storefront schema in FK-safe dependency order. Idempotent (IF NOT EXISTS / pushSchema).
 * billing · tax · uploads · search · mail own no DB tables — skipped by design.
 */
export async function applyStorefrontSchema(db: DbResult['db']): Promise<void> {
  await applySettingsSchema(db as unknown as Querier);
  await db.execute(sql.raw(CREATE_CHARGE_INTENTS_TABLE_SQL));
  await db.execute(sql.raw(CREATE_WEBHOOK_EVENTS_TABLE_SQL));

  // billing — no module-owned tables (uses ledger at charge time)
  // tax — rates-table only, no DB schema
  await applyLedgerSchema(db as unknown as Querier);
  // uploads — blob storage seam, no DB schema
  // search — provider registry, no DB schema
  await applyAuthSchema(db as unknown as Querier);
  // mail — adapter-only, no DB schema
  await applyJobsSchema(db as unknown as Querier);

  await pushCatalogSchema(db);
  await pushCartSchema(db);
  await pushInventorySchema(db);
  await pushOrdersSchema(db);
  await pushFulfillmentSchema(db);
  await pushCheckoutSchema(db);
  await pushPromotionsSchema(db);
  await pushReviewsSchema(db);

  await executeStatements(db as unknown as Querier, [
    sql`
      CREATE TABLE IF NOT EXISTS mod_storefront_password_resets (
        token_hash TEXT PRIMARY KEY,
        user_id    TEXT NOT NULL,
        expires_at TIMESTAMPTZ NOT NULL,
        used_at    TIMESTAMPTZ
      )
    `,
    sql`CREATE INDEX IF NOT EXISTS idx_pwr_expires ON mod_storefront_password_resets(expires_at)`,
  ]);
}