import { sql } from 'drizzle-orm';
import type { Querier } from '@platform-modules/db';

export const SETTINGS_KEYS = {
  installed: 'installed',
  storeName: 'store_name',
  currency: 'currency',
  locale: 'locale',
  tagline: 'tagline',
  themeMode: 'theme_mode',
  priceMode: 'price_mode',
  stripePublishableKey: 'stripe_publishable_key',
  stripeTestMode: 'stripe_test_mode',
} as const;

export const CREATE_SETTINGS_TABLE_SQL = `
CREATE TABLE IF NOT EXISTS mod_storefront_settings (
  id TEXT PRIMARY KEY,
  value TEXT NOT NULL,
  claimed_at TIMESTAMPTZ
)`.trim();

export type InstallSettings = {
  storeName: string;
  currency: string;
  locale: string;
  tagline?: string;
  themeMode?: string;
  priceMode?: 'inclusive' | 'exclusive';
  stripePublishableKey?: string;
  stripeTestMode?: boolean;
};

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

export async function getSettingValue(db: Querier, key: string): Promise<string | null> {
  const result = await db.execute(
    sql`SELECT value FROM mod_storefront_settings WHERE id = ${key} LIMIT 1`,
  );
  const rows = normalizeExecuteRows(result);
  const row = rows[0] as { value?: unknown } | undefined;
  return typeof row?.value === 'string' ? row.value : null;
}

export async function setSettingValue(db: Querier, key: string, value: string): Promise<void> {
  await db.execute(sql`
    INSERT INTO mod_storefront_settings (id, value)
    VALUES (${key}, ${value})
    ON CONFLICT (id) DO UPDATE SET value = excluded.value
  `);
}

export async function createSettingsTable(db: Querier): Promise<void> {
  await db.execute(sql.raw(CREATE_SETTINGS_TABLE_SQL));
}

/** True iff the stored installed flag is strictly the string "true". */
export async function isInstalled(db: Querier): Promise<boolean> {
  try {
    const value = await getSettingValue(db, SETTINGS_KEYS.installed);
    return value === 'true';
  } catch {
    return false;
  }
}

export async function claimInstall(db: Querier, settings: InstallSettings): Promise<void> {
  // Write config FIRST, installed=true LAST — if anything fails before the flag is set,
  // the store remains in "not installed" state (retryable), never "installed-but-misconfigured".
  await setSettingValue(db, SETTINGS_KEYS.storeName, settings.storeName);
  await setSettingValue(db, SETTINGS_KEYS.currency, settings.currency);
  await setSettingValue(db, SETTINGS_KEYS.locale, settings.locale);

  if (settings.tagline?.trim()) {
    await setSettingValue(db, SETTINGS_KEYS.tagline, settings.tagline.trim());
  }
  if (settings.themeMode) {
    await setSettingValue(db, SETTINGS_KEYS.themeMode, settings.themeMode);
  }
  // Stripe secret key + webhook secret live in CF Workers secrets (env.STRIPE_SECRET_KEY /
  // env.STRIPE_WEBHOOK_SECRET), never in the database — set via `wrangler secret put`.
  if (settings.stripePublishableKey?.trim()) {
    await setSettingValue(db, SETTINGS_KEYS.stripePublishableKey, settings.stripePublishableKey.trim());
  }
  if (settings.stripeTestMode !== undefined) {
    await setSettingValue(db, SETTINGS_KEYS.stripeTestMode, settings.stripeTestMode ? 'true' : 'false');
  }
  if (settings.priceMode) {
    await setSettingValue(db, SETTINGS_KEYS.priceMode, settings.priceMode);
  }
  // installed=true written LAST — atomicity: if any prior write fails, installed stays false (retryable)
  await setSettingValue(db, SETTINGS_KEYS.installed, 'true');
}

export type StoreSettings = {
  storeName: string;
  currency: string;
  locale: string;
  themeMode: string;
  priceMode: 'inclusive' | 'exclusive';
  tagline: string | null;
  stripePublishableKey: string | null;
  stripeTestMode: boolean;
};

export async function getStoreSettings(db: Querier): Promise<StoreSettings> {
  const [
    storeName,
    currency,
    locale,
    themeMode,
    priceMode,
    tagline,
    stripePublishableKey,
    stripeTestMode,
  ] = await Promise.all([
    getSettingValue(db, SETTINGS_KEYS.storeName),
    getSettingValue(db, SETTINGS_KEYS.currency),
    getSettingValue(db, SETTINGS_KEYS.locale),
    getSettingValue(db, SETTINGS_KEYS.themeMode),
    getSettingValue(db, SETTINGS_KEYS.priceMode),
    getSettingValue(db, SETTINGS_KEYS.tagline),
    getSettingValue(db, SETTINGS_KEYS.stripePublishableKey),
    getSettingValue(db, SETTINGS_KEYS.stripeTestMode),
  ]);

  const resolvedPriceMode =
    priceMode === 'inclusive' || priceMode === 'exclusive' ? priceMode : 'exclusive';

  return {
    storeName: storeName ?? '',
    currency: currency ?? 'USD',
    locale: locale ?? 'en',
    themeMode: themeMode ?? 'system',
    priceMode: resolvedPriceMode,
    tagline,
    stripePublishableKey,
    stripeTestMode: stripeTestMode === 'true',
  };
}

export async function claimInstallOnce(db: Querier): Promise<boolean> {
  const result = await db.execute(
    sql`INSERT INTO mod_storefront_settings(id, value, claimed_at)
      VALUES(${SETTINGS_KEYS.installed}, 'pending', now())
      ON CONFLICT(id) DO UPDATE
        SET value = 'pending', claimed_at = now()
        WHERE mod_storefront_settings.value = 'pending'
          AND (mod_storefront_settings.claimed_at IS NULL
            OR mod_storefront_settings.claimed_at < now() - INTERVAL '5 minutes')
      RETURNING id`,
  );
  const rows = normalizeExecuteRows(result);
  return rows.length > 0;
}