import { sql } from 'drizzle-orm';
import type { Dialect, Querier } from '@platform-modules/db';
import { authUsersStatusMigrationSql } from '@platform-modules/auth/engine-custom';
import {
  contentEntriesBaseMigrationSql,
  contentRevisionsMigrationSql,
  contentSearchMigrationSql,
  contentTaxonomyMigrationSql,
  contentVisibilityMigrationSql,
} from '@platform-modules/content';
import { commentsTableSql } from '@platform-modules/comments';
import { i18nContentMigrationSql } from '@platform-modules/i18n-content/migrate';
import { fieldsMigrationSql } from '@platform-modules/fields';
import { formsTableSql } from '@platform-modules/forms/store';
import { menusMigrationSql } from '@platform-modules/menus';
import { notificationsTableSql } from '@platform-modules/notifications/inbox';
import { auditTableSql } from './audit.js';
import { getD1SchemaStatements } from './d1-schema.js';
import { mediaTableSql } from './media.js';
import { setSetting, SETTINGS_KEYS, type SettingsSchema } from './settings.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));
  }
}

/**
 * Apply D1/SQLite schema. Idempotent (IF NOT EXISTS).
 * Called by /install wizard before claimInstall.
 * Every racer runs this — safe because DDL is idempotent.
 */
export async function applyD1Schema<S extends Record<string, unknown>>(db: Querier<S>): Promise<void> {
  const statements = getD1SchemaStatements();
  for (const stmt of statements) {
    if (stmt.trim()) {
      await db.execute(sql.raw(stmt));
    }
  }
}

/** Idempotent content additive DDL — visibility column/index + FTS generated column (split per-statement for neon-http). */
export async function applyContentSchema(db: Querier): Promise<void> {
  for (const ddl of [contentVisibilityMigrationSql(), contentSearchMigrationSql()]) {
    for (const stmt of ddl.split(';').map((s) => s.trim()).filter(Boolean)) {
      await db.execute(sql.raw(stmt));
    }
  }
}

/** Idempotent comments DDL — independent additive table (not in the U2/U4 content-ALTER window). */
export async function applyCommentsSchema(db: Querier): Promise<void> {
  const ddl = commentsTableSql();
  for (const stmt of ddl.split(';').map((s) => s.trim()).filter(Boolean)) {
    await db.execute(sql.raw(stmt));
  }
}

/** Idempotent forms submissions DDL — independent additive table. */
export async function applyFormsSchema(db: Querier): Promise<void> {
  const ddl = formsTableSql();
  for (const stmt of ddl.split(';').map((s) => s.trim()).filter(Boolean)) {
    await db.execute(sql.raw(stmt));
  }
}

/** Idempotent notifications inbox DDL — independent additive table. */
export async function applyNotificationsSchema(db: Querier): Promise<void> {
  await executeSplitDdl(db, notificationsTableSql());
}

/** Idempotent media_assets DDL — app-local table. */
export async function applyMediaSchema(db: Querier): Promise<void> {
  await executeSplitDdl(db, mediaTableSql());
}

/** Idempotent audit_log DDL — app-local table. */
export async function applyAuditSchema(db: Querier): Promise<void> {
  await executeSplitDdl(db, auditTableSql());
}

/**
 * Unified Postgres schema provisioner — composes existing *MigrationSql / apply*Schema fns in FK order.
 * Idempotent (IF NOT EXISTS / ADD COLUMN IF NOT EXISTS). Never creates auth_users (engine-owned).
 * auth-status ALTER runs last (requires seed-admin first).
 */
export async function applyFullSchema(db: Querier, dialect: Dialect): Promise<void> {
  if (dialect === 'sqlite') {
    await applyD1Schema(db);
    return;
  }

  // languages before translation_value (FK dep); both before content tables (no FK dep, clear ordering).
  await executeSplitDdl(db, i18nContentMigrationSql);
  await executeSplitDdl(db, contentEntriesBaseMigrationSql());
  await applyContentSchema(db);
  // Revisions before taxonomy: contentTaxonomyMigrationSql ALTERs content_revisions.term_ids.
  await executeSplitDdl(db, contentRevisionsMigrationSql());
  await executeSplitDdl(db, contentTaxonomyMigrationSql());
  await applyCommentsSchema(db);
  await applyFormsSchema(db);
  await applyNotificationsSchema(db);
  await executeSplitDdl(db, fieldsMigrationSql());
  await executeSplitDdl(db, menusMigrationSql());
  await applyMediaSchema(db);
  await applyAuditSchema(db);
  await db.execute(sql.raw(authUsersStatusMigrationSql()));
}

export const INSTALL_CLAIM_TTL_MS = 15 * 60 * 1000;
export const SITE_CONFIGURED_KEY = SETTINGS_KEYS.siteConfigured;

/** Site is live iff stored value is STRICT boolean true. */
export function isSiteConfigured(value: unknown): boolean {
  return value === true;
}

const INSTALL_PATHS = ['/install', '/api/install'] as const;

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

/** Returns the redirect target ('/install') or null (let request through). */
export function bootGateDecision(pathname: string, isConfigured: boolean): string | null {
  if (isConfigured) return null;
  if ((INSTALL_PATHS as readonly string[]).includes(pathname) || isAssetPath(pathname)) return null;
  return '/install';
}

function normalizeExecuteRows(result: unknown): unknown[] {
  if (Array.isArray(result)) return result;
  const rows = (result as { rows?: unknown[] } | null)?.rows;
  return rows ?? [];
}

/**
 * Atomic single-statement lease. Returns true iff THIS caller won the claim.
 * Cutoff computed in JS and BOUND — never `timestamp − integer` SQL.
 */
export async function claimInstall(
  db: Querier<SettingsSchema>,
  now: Date,
  dialect: Dialect,
): Promise<boolean> {
  const nowIso = now.toISOString();
  const cutoffIso = new Date(now.getTime() - INSTALL_CLAIM_TTL_MS).toISOString();
  const claiming = JSON.stringify({ state: 'claiming', at: nowIso });
  const stateExtract = dialect === 'sqlite'
    ? sql.raw(`json_extract(site_settings.value, '$.state')`)
    : sql.raw(`site_settings.value->>'state'`);
  const atExtract = dialect === 'sqlite'
    ? sql.raw(`json_extract(site_settings.value, '$.at')`)
    : sql.raw(`site_settings.value->>'at'`);
  const result = await db.execute(sql`
    INSERT INTO site_settings (key, value, updated_at)
    VALUES (${SITE_CONFIGURED_KEY}, ${claiming}, ${nowIso})
    ON CONFLICT (key) DO UPDATE
      SET value = excluded.value, updated_at = excluded.updated_at
      WHERE ${stateExtract} = 'claiming'
        AND ${atExtract} < ${cutoffIso}
    RETURNING key
  `);
  return normalizeExecuteRows(result).length === 1;
}

/** Idempotent finalize — flips the lease to the permanent live marker. */
export async function finalizeInstall(db: Querier<SettingsSchema>): Promise<void> {
  await setSetting(db, SITE_CONFIGURED_KEY, true);
}