/**
 * Persistence helpers — all async, talk to PostgreSQL.
 * Routes should call these alongside in-memory mutations so data survives restarts.
 */

import { pool } from './db';
import type { Layer, IndicatorConfig, FilterConfig, UserRecord, AuditEvent, NewAuditEvent } from './store';

export async function dbLoadAuditLog(limit = 1000): Promise<AuditEvent[]> {
  const { rows } = await pool.query(
    `SELECT id, action, subject, actor, timestamp, details, metadata
     FROM sc_audit_log ORDER BY id DESC LIMIT $1`,
    [limit],
  );
  return rows.map((row) => ({
    id: Number(row.id), action: row.action, subject: row.subject, actor: row.actor,
    timestamp: row.timestamp, details: row.details ?? undefined, metadata: row.metadata ?? {},
  }));
}

export async function dbInsertAuditEvent(event: NewAuditEvent): Promise<AuditEvent> {
  const { rows } = await pool.query(
    `INSERT INTO sc_audit_log (action, subject, actor, timestamp, details, metadata)
     VALUES ($1, $2, $3, $4, $5, $6)
     RETURNING id, action, subject, actor, timestamp, details, metadata`,
    [event.action, event.subject, event.actor, event.timestamp, event.details ?? null, JSON.stringify(event.metadata ?? {})],
  );
  const row = rows[0];
  return {
    id: Number(row.id), action: row.action, subject: row.subject, actor: row.actor,
    timestamp: row.timestamp, details: row.details ?? undefined, metadata: row.metadata ?? {},
  };
}

/** Create the connect-pg-simple sessions table if it doesn't exist.
 *  We do this manually because connect-pg-simple's createTableIfMissing reads
 *  a SQL asset file that is not bundled by esbuild. */
export async function dbEnsureSessionTable(): Promise<void> {
  await pool.query(`
    CREATE TABLE IF NOT EXISTS sc_sessions (
      sid  VARCHAR NOT NULL COLLATE "default",
      sess JSON    NOT NULL,
      expire TIMESTAMP(6) NOT NULL,
      CONSTRAINT session_pkey PRIMARY KEY (sid) NOT DEFERRABLE INITIALLY IMMEDIATE
    ) WITH (OIDS=FALSE);
    CREATE INDEX IF NOT EXISTS idx_session_expire ON sc_sessions (expire);
  `);
}

// ── Users ──────────────────────────────────────────────────────────────────────

export async function dbLoadUsers(): Promise<UserRecord[]> {
  const { rows } = await pool.query(`
    SELECT id, username, password, name, email, role, status, last_login
    FROM sc_users ORDER BY id
  `);
  return rows.map((r) => ({
    id: r.id,
    username: r.username,
    password: r.password,
    name: r.name,
    email: r.email,
    role: r.role as UserRecord['role'],
    status: r.status as UserRecord['status'],
    lastLogin: r.last_login,
  }));
}

export async function dbUpsertUser(u: UserRecord): Promise<void> {
  await pool.query(
    `INSERT INTO sc_users (id, username, password, name, email, role, status, last_login)
     VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
     ON CONFLICT (id) DO UPDATE SET
       username   = EXCLUDED.username,
       password   = EXCLUDED.password,
       name       = EXCLUDED.name,
       email      = EXCLUDED.email,
       role       = EXCLUDED.role,
       status     = EXCLUDED.status,
       last_login = EXCLUDED.last_login`,
    [u.id, u.username, u.password, u.name, u.email, u.role, u.status, u.lastLogin],
  );
}

export async function dbDeleteUser(id: number): Promise<void> {
  await pool.query('DELETE FROM sc_users WHERE id = $1', [id]);
}

// ── Layers ─────────────────────────────────────────────────────────────────────

export async function dbLoadLayers(): Promise<Layer[]> {
  const { rows } = await pool.query(`
    SELECT id, name, description, source_type, geometry_type, color,
           visibility, priority, columns, column_types, column_type_overrides,
           lat_column, lng_column,
           geometry_column, rows, feature_count, sync_status, updated_at,
           source_url, refresh_interval, last_refreshed_at,
           symbology, polygon_style, polygon_symbology, cluster_config, popup_config, row_filters,
           derived_columns
    FROM sc_layers ORDER BY priority
  `);
  return rows.map((r) => ({
    id: r.id,
    name: r.name,
    description: r.description,
    sourceType: r.source_type as Layer['sourceType'],
    geometryType: r.geometry_type as Layer['geometryType'],
    color: r.color,
    visibility: r.visibility,
    priority: r.priority,
    columns: r.columns,
    columnTypes: r.column_types,
    columnTypeOverrides: r.column_type_overrides ?? undefined,
    latColumn: r.lat_column ?? undefined,
    lngColumn: r.lng_column ?? undefined,
    geometryColumn: r.geometry_column ?? undefined,
    rows: r.rows,
    featureCount: r.feature_count,
    syncStatus: r.sync_status ?? undefined,
    updatedAt: r.updated_at,
    sourceUrl: r.source_url ?? undefined,
    refreshInterval: r.refresh_interval ?? 0,
    lastRefreshedAt: r.last_refreshed_at ?? undefined,
    symbology: r.symbology ?? undefined,
    polygonStyle: r.polygon_style ?? undefined,
    polygonSymbology: r.polygon_symbology ?? undefined,
    clusterConfig: r.cluster_config ?? undefined,
    popupConfig: r.popup_config ?? undefined,
    derivedColumns: Array.isArray(r.derived_columns) ? r.derived_columns : [],
    rowFilters: (() => {
      const raw = r.row_filters as Record<string, unknown> | null;
      if (!raw) return undefined;
      // New recursive format — pass through as-is
      if (raw.kind === 'group') return raw as unknown as Layer['rowFilters'];
      // Old flat format: { combinator, conditions[] } — migrate to recursive tree
      if (Array.isArray(raw.conditions) && (raw.conditions as unknown[]).length) {
        return {
          kind: 'group' as const,
          combinator: (raw.combinator as 'and' | 'or') ?? 'and',
          children: (raw.conditions as Record<string, unknown>[]).map(
            (c) => ({ ...c, kind: 'condition' as const }),
          ),
        } as unknown as Layer['rowFilters'];
      }
      return undefined;
    })(),
  }));
}

export async function dbUpsertLayer(l: Layer): Promise<void> {
  await pool.query(
    `INSERT INTO sc_layers
       (id, name, description, source_type, geometry_type, color, visibility, priority,
        columns, column_types, column_type_overrides, lat_column, lng_column, geometry_column,
        rows, feature_count, sync_status, updated_at,
        source_url, refresh_interval, last_refreshed_at, symbology, polygon_style, polygon_symbology, cluster_config, popup_config, row_filters,
        derived_columns)
     VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28)
     ON CONFLICT (id) DO UPDATE SET
       name                  = EXCLUDED.name,
       description           = EXCLUDED.description,
       source_type           = EXCLUDED.source_type,
       geometry_type         = EXCLUDED.geometry_type,
       color                 = EXCLUDED.color,
       visibility            = EXCLUDED.visibility,
       priority              = EXCLUDED.priority,
       columns               = EXCLUDED.columns,
       column_types          = EXCLUDED.column_types,
       column_type_overrides = EXCLUDED.column_type_overrides,
       lat_column            = EXCLUDED.lat_column,
       lng_column            = EXCLUDED.lng_column,
       geometry_column       = EXCLUDED.geometry_column,
       rows                  = EXCLUDED.rows,
       feature_count         = EXCLUDED.feature_count,
       sync_status           = EXCLUDED.sync_status,
       updated_at            = EXCLUDED.updated_at,
       source_url            = EXCLUDED.source_url,
       refresh_interval      = EXCLUDED.refresh_interval,
       last_refreshed_at     = EXCLUDED.last_refreshed_at,
       symbology             = EXCLUDED.symbology,
       polygon_style         = EXCLUDED.polygon_style,
       polygon_symbology     = EXCLUDED.polygon_symbology,
       cluster_config        = EXCLUDED.cluster_config,
       popup_config          = EXCLUDED.popup_config,
       row_filters           = EXCLUDED.row_filters,
       derived_columns       = EXCLUDED.derived_columns`,
    [
      l.id, l.name, l.description, l.sourceType, l.geometryType,
      l.color, l.visibility, l.priority,
      JSON.stringify(l.columns), JSON.stringify(l.columnTypes),
      l.columnTypeOverrides ? JSON.stringify(l.columnTypeOverrides) : null,
      l.latColumn ?? null, l.lngColumn ?? null, l.geometryColumn ?? null,
      JSON.stringify(l.rows), l.featureCount,
      l.syncStatus ?? null, l.updatedAt,
      l.sourceUrl ?? null, l.refreshInterval ?? 0, l.lastRefreshedAt ?? null,
      l.symbology ? JSON.stringify(l.symbology) : null,
      l.polygonStyle ? JSON.stringify(l.polygonStyle) : null,
      l.polygonSymbology ? JSON.stringify(l.polygonSymbology) : null,
      l.clusterConfig ? JSON.stringify(l.clusterConfig) : null,
      l.popupConfig ? JSON.stringify(l.popupConfig) : null,
      l.rowFilters ? JSON.stringify(l.rowFilters) : null,
      JSON.stringify(l.derivedColumns ?? []),
    ],
  );
}

export async function dbDeleteLayer(id: string): Promise<void> {
  await pool.query('DELETE FROM sc_layers WHERE id = $1', [id]);
}

export async function dbReorderLayers(ids: string[]): Promise<void> {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    for (let i = 0; i < ids.length; i++) {
      await client.query('UPDATE sc_layers SET priority = $1 WHERE id = $2', [i + 1, ids[i]]);
    }
    await client.query('COMMIT');
  } catch (e) { await client.query('ROLLBACK'); throw e; }
  finally { client.release(); }
}

export async function dbReorderIndicatorConfigs(ids: string[]): Promise<void> {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    for (let i = 0; i < ids.length; i++) {
      await client.query('UPDATE sc_indicator_configs SET "order" = $1 WHERE id = $2', [i + 1, ids[i]]);
    }
    await client.query('COMMIT');
  } catch (e) { await client.query('ROLLBACK'); throw e; }
  finally { client.release(); }
}

export async function dbReorderFilterConfigs(ids: string[]): Promise<void> {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    for (let i = 0; i < ids.length; i++) {
      await client.query('UPDATE sc_filter_configs SET "order" = $1 WHERE id = $2', [i + 1, ids[i]]);
    }
    await client.query('COMMIT');
  } catch (e) { await client.query('ROLLBACK'); throw e; }
  finally { client.release(); }
}

// ── Indicator configs ──────────────────────────────────────────────────────────

export async function dbLoadIndicatorConfigs(): Promise<IndicatorConfig[]> {
  const { rows } = await pool.query(`
    SELECT id, label, layer_id, col, aggregation, format, color, icon, "order", enabled
    FROM sc_indicator_configs ORDER BY "order"
  `);
  return rows.map((r) => ({
    id: r.id,
    label: r.label,
    layerId: r.layer_id,
    column: r.col,
    aggregation: r.aggregation as IndicatorConfig['aggregation'],
    format: r.format as IndicatorConfig['format'],
    color: r.color,
    icon: r.icon,
    order: r.order,
    enabled: r.enabled,
  }));
}

export async function dbUpsertIndicatorConfig(c: IndicatorConfig): Promise<void> {
  await pool.query(
    `INSERT INTO sc_indicator_configs (id, label, layer_id, col, aggregation, format, color, icon, "order", enabled)
     VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10)
     ON CONFLICT (id) DO UPDATE SET
       label       = EXCLUDED.label,
       layer_id    = EXCLUDED.layer_id,
       col         = EXCLUDED.col,
       aggregation = EXCLUDED.aggregation,
       format      = EXCLUDED.format,
       color       = EXCLUDED.color,
       icon        = EXCLUDED.icon,
       "order"     = EXCLUDED."order",
       enabled     = EXCLUDED.enabled`,
    [c.id, c.label, c.layerId, c.column, c.aggregation, c.format, c.color, c.icon, c.order, c.enabled],
  );
}

export async function dbDeleteIndicatorConfig(id: string): Promise<void> {
  await pool.query('DELETE FROM sc_indicator_configs WHERE id = $1', [id]);
}

// ── Filter configs ─────────────────────────────────────────────────────────────

export async function dbLoadFilterConfigs(): Promise<FilterConfig[]> {
  const { rows } = await pool.query(`
    SELECT id, label, layer_id, col, type, placeholder, "order", enabled, joins
    FROM sc_filter_configs ORDER BY "order"
  `);
  return rows.map((r) => ({
    id: r.id,
    label: r.label,
    layerId: r.layer_id,
    column: r.col,
    type: r.type as FilterConfig['type'],
    placeholder: r.placeholder,
    order: r.order,
    enabled: r.enabled,
    joins: r.joins ?? [],
  }));
}

export async function dbUpsertFilterConfig(c: FilterConfig): Promise<void> {
  await pool.query(
    `INSERT INTO sc_filter_configs (id, label, layer_id, col, type, placeholder, "order", enabled, joins)
     VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9)
     ON CONFLICT (id) DO UPDATE SET
       label       = EXCLUDED.label,
       layer_id    = EXCLUDED.layer_id,
       col         = EXCLUDED.col,
       type        = EXCLUDED.type,
       placeholder = EXCLUDED.placeholder,
       "order"     = EXCLUDED."order",
       enabled     = EXCLUDED.enabled,
       joins       = EXCLUDED.joins`,
    [c.id, c.label, c.layerId, c.column, c.type, c.placeholder, c.order, c.enabled,
     JSON.stringify(c.joins ?? [])],
  );
}

export async function dbDeleteFilterConfig(id: string): Promise<void> {
  await pool.query('DELETE FROM sc_filter_configs WHERE id = $1', [id]);
}