import crypto from 'crypto';
import { pool } from './db';
import {
  dbLoadUsers, dbUpsertUser, dbDeleteUser,
  dbLoadLayers, dbUpsertLayer, dbDeleteLayer, dbReorderLayers,
  dbLoadIndicatorConfigs, dbUpsertIndicatorConfig, dbDeleteIndicatorConfig, dbReorderIndicatorConfigs,
  dbLoadFilterConfigs, dbUpsertFilterConfig, dbDeleteFilterConfig, dbReorderFilterConfigs,
  dbEnsureSessionTable, dbLoadAuditLog, dbInsertAuditEvent,
} from './persist';

export type LayerRow = Record<string, string | number | boolean | null>;

export type ColumnType = 'text' | 'number' | 'date';

export type DerivedColumnOperation =
  | 'copy'
  | 'add'
  | 'subtract'
  | 'multiply'
  | 'divide'
  | 'concat'
  | 'formula';

export type DerivedColumnConfig = {
  id: string;
  name: string;
  dataType: ColumnType;
  mode: 'default' | 'calculated';
  defaultValue?: string | number | null;
  defaultMode?: 'empty-only' | 'all';
  calculation?: {
    operation: DerivedColumnOperation;
    sourceColumns: string[];
    separator?: string;
    decimalPlaces?: number;
    formula?: string;
  };
};

export type SymbologyRule = {
  value: string;
  color: string;
  icon?: string;
  size?: number;  // marker radius px, default 7
  label?: string; // legend label
};

export type LayerSymbology = {
  column: string;
  rules: SymbologyRule[];
  defaultColor: string;
  defaultIcon?: string;
  defaultSize?: number;
  legendLabel?: string;
};

export type PolygonStyle = {
  fillColor: string;
  fillOpacity: number;   // 0–1
  strokeColor: string;
  strokeWeight: number;  // px
  legendLabel?: string;
};

export type PolygonSymbologyRule = {
  value: string;
  fillColor: string;
  label?: string;
};

export type PolygonSymbology = {
  column: string;
  rules: PolygonSymbologyRule[];
  defaultFillColor: string;
};

export type ClusterConfig = {
  enabled: boolean;
  radius: number;   // cluster grid cell size in px (default 60)
  color: string;    // cluster bubble fill color
  maxZoom: number;  // zoom above which clustering is disabled (default 14)
  icon?: string;
  minSize?: number;
  maxSize?: number;
  maxSizeCount?: number;
  legendLabel?: string;
  showInLegend?: boolean;
};

export type StoredFilterOp =
  | 'eq' | 'neq' | 'contains' | 'not_contains' | 'is_empty' | 'is_not_empty'
  | 'gt' | 'lt' | 'gte' | 'lte' | 'between'
  | 'date_before' | 'date_after' | 'date_last_n_months' | 'date_last_n_days';
export type StoredRowFilterCondition = {
  kind: 'condition';
  column: string;
  operator: StoredFilterOp;
  value: string;
  value2?: string;
};
export type StoredRowFilterGroup = {
  kind: 'group';
  combinator: 'and' | 'or';
  children: Array<StoredRowFilterCondition | StoredRowFilterGroup>;
};

export type PopupFieldConfig = {
  column: string;
  label: string;
  format: 'raw' | 'number' | 'percent' | 'currency';
  numericAgg: 'sum' | 'min' | 'max' | 'avg' | 'count';
  textAgg: 'distinct' | 'dominant';
};
export type PopupConfig = {
  fields: PopupFieldConfig[];
  showOnHover: boolean;
  showOnClick: boolean;
};

export type Layer = {
  id: string;
  name: string;
  description: string;
  sourceType: 'Excel' | 'CSV' | 'GeoJSON' | 'KML' | 'WMS' | 'ESRI' | 'URL' | 'ArcGIS';
  geometryType: 'Point' | 'LineString' | 'MultiLineString' | 'Polygon' | 'MultiPolygon';
  color: string;
  visibility: boolean;
  priority: number;
  columns: string[];
  columnTypes: Record<string, ColumnType>;
  /** Explicitly admin-set column types — preserved across URL-source auto-refreshes. */
  columnTypeOverrides?: Record<string, ColumnType>;
  latColumn?: string;
  lngColumn?: string;
  geometryColumn?: string;
  rows: LayerRow[];
  featureCount: number;
  syncStatus?: string;
  updatedAt: string;
  // URL-source fields
  sourceUrl?: string;
  refreshInterval?: number; // minutes; 0 = manual only
  lastRefreshedAt?: string;
  // Row filters (applied at query time so they can be edited without re-uploading)
  rowFilters?: StoredRowFilterGroup;
  /** Definitions for columns added by an administrator and reapplied after refreshes. */
  derivedColumns?: DerivedColumnConfig[];
  // Symbology, clustering, polygon style & popup
  symbology?: LayerSymbology;
  polygonStyle?: PolygonStyle;
  polygonSymbology?: PolygonSymbology;
  clusterConfig?: ClusterConfig;
  popupConfig?: PopupConfig;
};

export type IndicatorConfig = {
  id: string;
  label: string;
  layerId: string;
  column: string;
  aggregation: 'count' | 'sum' | 'countDistinct' | 'avg' | 'min' | 'max';
  format: 'number' | 'decimal' | 'percent';
  color: string;
  icon: string;
  order: number;
  enabled: boolean;
};

export type FilterConfig = {
  id: string;
  label: string;
  layerId: string;
  column: string;
  type: 'select' | 'multiselect' | 'multiselect-dropdown' | 'text' | 'number-range' | 'date-range' | 'boolean';
  placeholder: string;
  order: number;
  enabled: boolean;
  joins: { layerId: string; column: string }[];
};

export type UserRecord = {
  id: number;
  username: string;
  password: string;
  name: string;
  email: string;
  role: 'Admin' | 'Editor' | 'Viewer';
  status: 'Active' | 'Invited' | 'Disabled';
  lastLogin: string;
};

// ── Runtime state (in-memory cache, loaded from DB on startup) ─────────────────
export const users: UserRecord[] = [];
export const layers: Layer[] = [];
export const indicatorConfigs: IndicatorConfig[] = [];
export const filterConfigs: FilterConfig[] = [];

// ── Audit log (in-memory only, no persistence needed) ─────────────────────────
export type AuditMetadata = Record<string, string | number | boolean | null | string[] | number[]>;
export type AuditEvent = {
  id: number;
  action: string;
  subject: string;
  actor: string;
  timestamp: string;
  details?: string;
  metadata: AuditMetadata;
};
export type NewAuditEvent = Omit<AuditEvent, 'id'>;
export const auditLog: AuditEvent[] = [];

// ── Temporary upload store (parsed but not yet committed layers) ───────────────
export type PendingUpload = {
  columns: string[];
  columnTypes: Record<string, ColumnType>;
  rows: LayerRow[];
  latSuggestion?: string;
  lngSuggestion?: string;
  geometryColumnSuggestion?: string;
  geometryType: 'Point' | 'LineString' | 'MultiLineString' | 'Polygon' | 'MultiPolygon';
  expiresAt: number;
};
export const pendingUploads = new Map<string, PendingUpload>();

// Clean up stale pending uploads every 10 min
setInterval(() => {
  const now = Date.now();
  for (const [k, v] of pendingUploads) {
    if (v.expiresAt < now) pendingUploads.delete(k);
  }
}, 10 * 60 * 1000);

// ── Default seed data ──────────────────────────────────────────────────────────
const DEFAULT_USERS: UserRecord[] = [
  {
    id: 1,
    username: 'admin',
    password: 'admin',
    name: 'Admin',
    email: 'admin@coordination.org',
    role: 'Admin',
    status: 'Active',
    lastLogin: 'Not yet',
  },
  {
    id: 2,
    username: 'partner',
    password: 'partner',
    name: 'Partner User',
    email: 'partner@coordination.org',
    role: 'Viewer',
    status: 'Active',
    lastLogin: 'Not yet',
  },
];

// ── Schema bootstrap ───────────────────────────────────────────────────────────
async function ensureSchema(): Promise<void> {
  await pool.query(`
    CREATE TABLE IF NOT EXISTS sc_users (
      id SERIAL PRIMARY KEY,
      username TEXT UNIQUE NOT NULL,
      password TEXT NOT NULL,
      name TEXT NOT NULL,
      email TEXT NOT NULL,
      role TEXT NOT NULL DEFAULT 'Viewer',
      status TEXT NOT NULL DEFAULT 'Active',
      last_login TEXT NOT NULL DEFAULT 'Not yet'
    );

    CREATE TABLE IF NOT EXISTS sc_layers (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL,
      description TEXT NOT NULL DEFAULT '',
      source_type TEXT NOT NULL DEFAULT 'Excel',
      geometry_type TEXT NOT NULL DEFAULT 'Point',
      color TEXT NOT NULL DEFAULT '#2563eb',
      visibility BOOLEAN NOT NULL DEFAULT true,
      priority INTEGER NOT NULL DEFAULT 0,
      columns JSONB NOT NULL DEFAULT '[]',
      column_types JSONB NOT NULL DEFAULT '{}',
      lat_column TEXT,
      lng_column TEXT,
      rows JSONB NOT NULL DEFAULT '[]',
      feature_count INTEGER NOT NULL DEFAULT 0,
      sync_status TEXT,
      updated_at TEXT NOT NULL
    );

    ALTER TABLE sc_layers ADD COLUMN IF NOT EXISTS source_url TEXT;
    ALTER TABLE sc_layers ADD COLUMN IF NOT EXISTS refresh_interval INTEGER NOT NULL DEFAULT 0;
    ALTER TABLE sc_layers ADD COLUMN IF NOT EXISTS last_refreshed_at TEXT;
    ALTER TABLE sc_layers ADD COLUMN IF NOT EXISTS symbology JSONB;
    ALTER TABLE sc_layers ADD COLUMN IF NOT EXISTS cluster_config JSONB;
    ALTER TABLE sc_layers ADD COLUMN IF NOT EXISTS polygon_style JSONB;
    ALTER TABLE sc_layers ADD COLUMN IF NOT EXISTS polygon_symbology JSONB;
    ALTER TABLE sc_layers ADD COLUMN IF NOT EXISTS column_type_overrides JSONB;
    ALTER TABLE sc_layers ADD COLUMN IF NOT EXISTS geometry_column TEXT;
    ALTER TABLE sc_layers ADD COLUMN IF NOT EXISTS popup_config JSONB;
    ALTER TABLE sc_layers ADD COLUMN IF NOT EXISTS row_filters JSONB;
    ALTER TABLE sc_layers ADD COLUMN IF NOT EXISTS derived_columns JSONB NOT NULL DEFAULT '[]'::jsonb;
    UPDATE sc_layers SET derived_columns = '[]'::jsonb WHERE derived_columns IS NULL;
    ALTER TABLE sc_layers ALTER COLUMN derived_columns SET DEFAULT '[]'::jsonb;
    ALTER TABLE sc_layers ALTER COLUMN derived_columns SET NOT NULL;

    CREATE TABLE IF NOT EXISTS sc_indicator_configs (
      id TEXT PRIMARY KEY,
      label TEXT NOT NULL,
      layer_id TEXT NOT NULL,
      col TEXT NOT NULL,
      aggregation TEXT NOT NULL,
      format TEXT NOT NULL DEFAULT 'number',
      color TEXT NOT NULL DEFAULT '#2563eb',
      icon TEXT NOT NULL DEFAULT 'bar-chart',
      "order" INTEGER NOT NULL DEFAULT 0,
      enabled BOOLEAN NOT NULL DEFAULT true
    );

    CREATE TABLE IF NOT EXISTS sc_filter_configs (
      id TEXT PRIMARY KEY,
      label TEXT NOT NULL,
      layer_id TEXT NOT NULL,
      col TEXT NOT NULL,
      type TEXT NOT NULL,
      placeholder TEXT NOT NULL DEFAULT '',
      "order" INTEGER NOT NULL DEFAULT 0,
      enabled BOOLEAN NOT NULL DEFAULT true,
      joins JSONB NOT NULL DEFAULT '[]'
    );
    ALTER TABLE sc_filter_configs ADD COLUMN IF NOT EXISTS joins JSONB NOT NULL DEFAULT '[]';

    CREATE TABLE IF NOT EXISTS sc_audit_log (
      id BIGSERIAL PRIMARY KEY,
      action TEXT NOT NULL,
      subject TEXT NOT NULL,
      actor TEXT NOT NULL,
      timestamp TEXT NOT NULL,
      details TEXT,
      metadata JSONB NOT NULL DEFAULT '{}'::jsonb
    );
    CREATE INDEX IF NOT EXISTS idx_sc_audit_log_id_desc ON sc_audit_log (id DESC);

    CREATE TABLE IF NOT EXISTS sc_workbooks (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL,
      source_type TEXT NOT NULL DEFAULT 'Excel',
      source_url TEXT,
      file_name TEXT,
      refresh_config JSONB NOT NULL DEFAULT '{}'::jsonb,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS sc_dataset_sheets (
      id TEXT PRIMARY KEY,
      workbook_id TEXT NOT NULL REFERENCES sc_workbooks(id) ON DELETE CASCADE,
      name TEXT NOT NULL,
      sheet_index INTEGER NOT NULL DEFAULT 0,
      columns JSONB NOT NULL DEFAULT '[]'::jsonb,
      column_types JSONB NOT NULL DEFAULT '{}'::jsonb,
      raw_rows JSONB NOT NULL DEFAULT '[]'::jsonb,
      clean_rows JSONB NOT NULL DEFAULT '[]'::jsonb,
      row_count INTEGER NOT NULL DEFAULT 0,
      updated_at TEXT NOT NULL,
      UNIQUE(workbook_id, name)
    );

    CREATE TABLE IF NOT EXISTS sc_transform_steps (
      id TEXT PRIMARY KEY,
      sheet_id TEXT NOT NULL REFERENCES sc_dataset_sheets(id) ON DELETE CASCADE,
      step_order INTEGER NOT NULL,
      name TEXT NOT NULL,
      operation TEXT NOT NULL,
      config JSONB NOT NULL DEFAULT '{}'::jsonb,
      enabled BOOLEAN NOT NULL DEFAULT true,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );
    CREATE INDEX IF NOT EXISTS idx_sc_transform_steps_sheet_order
      ON sc_transform_steps(sheet_id, step_order);

    CREATE TABLE IF NOT EXISTS sc_dataset_relationships (
      id TEXT PRIMARY KEY,
      from_sheet_id TEXT NOT NULL REFERENCES sc_dataset_sheets(id) ON DELETE CASCADE,
      from_column TEXT NOT NULL,
      to_sheet_id TEXT NOT NULL REFERENCES sc_dataset_sheets(id) ON DELETE CASCADE,
      to_column TEXT NOT NULL,
      cardinality TEXT NOT NULL DEFAULT 'many-to-one',
      filter_direction TEXT NOT NULL DEFAULT 'both',
      enabled BOOLEAN NOT NULL DEFAULT true
    );

    CREATE TABLE IF NOT EXISTS sc_report_pages (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL,
      page_order INTEGER NOT NULL DEFAULT 0,
      enabled BOOLEAN NOT NULL DEFAULT true,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS sc_report_visuals (
      id TEXT PRIMARY KEY,
      page_id TEXT NOT NULL REFERENCES sc_report_pages(id) ON DELETE CASCADE,
      title TEXT NOT NULL,
      visual_type TEXT NOT NULL,
      layer_id TEXT NOT NULL,
      category_column TEXT,
      value_column TEXT,
      aggregation TEXT NOT NULL DEFAULT 'count',
      series_column TEXT,
      color TEXT NOT NULL DEFAULT '#294b55',
      settings JSONB NOT NULL DEFAULT '{}'::jsonb,
      layout JSONB NOT NULL DEFAULT '{"x":0,"y":0,"w":6,"h":4}'::jsonb,
      visual_order INTEGER NOT NULL DEFAULT 0,
      enabled BOOLEAN NOT NULL DEFAULT true,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );
    CREATE INDEX IF NOT EXISTS idx_sc_report_visuals_page_order
      ON sc_report_visuals(page_id, visual_order);

    INSERT INTO sc_report_pages (id, name, page_order, enabled, created_at, updated_at)
    VALUES
      ('executive-summary', 'Executive Summary', 1, true, NOW()::text, NOW()::text),
      ('gaza-5w', 'Gaza Strip 5W', 2, true, NOW()::text, NOW()::text),
      ('gaza-pipeline', 'Gaza Strip Pipeline', 3, true, NOW()::text, NOW()::text),
      ('gaza-stockpile', 'Gaza Strip Stockpile', 4, true, NOW()::text, NOW()::text)
    ON CONFLICT (id) DO NOTHING;
  `);
}

// ── Store initialisation (call once at server startup) ─────────────────────────
export async function initializeStore(): Promise<void> {
  // Ensure tables exist (idempotent — safe to run on every startup)
  await ensureSchema();
  await dbEnsureSessionTable();

  // Load users; if table is empty, seed defaults
  const dbUsers = await dbLoadUsers();
  if (dbUsers.length === 0) {
    for (const u of DEFAULT_USERS) {
      await dbUpsertUser(u);
    }
    users.push(...DEFAULT_USERS);
  } else {
    users.push(...dbUsers);
  }

  // Derive next user id from highest existing id
  const maxId = users.reduce((m, u) => Math.max(m, u.id), 0);
  _nextUserId = maxId;

  // Load layers, indicators, filters
  const dbLayers = await dbLoadLayers();
  layers.push(...dbLayers);

  const dbIndicators = await dbLoadIndicatorConfigs();
  indicatorConfigs.push(...dbIndicators);

  const dbFilters = await dbLoadFilterConfigs();
  filterConfigs.push(...dbFilters);

  auditLog.push(...await dbLoadAuditLog());
  await addAuditEvent(
    'System initialized',
    'Shelter Cluster Dashboard',
    'System',
    `Loaded ${layers.length} layers, ${indicatorConfigs.length} indicators, ${filterConfigs.length} slicers and ${users.length} users`,
    { layerCount: layers.length, indicatorCount: indicatorConfigs.length, slicerCount: filterConfigs.length, userCount: users.length },
  );
}

// ── ID generators ─────────────────────────────────────────────────────────────
let _nextUserId = 2;
export const getNextUserId = () => ++_nextUserId;
// Config IDs use UUIDs to avoid collisions across restarts
export const getNextConfigId = () => crypto.randomUUID().slice(0, 8);
export const newId = () => crypto.randomUUID().slice(0, 8);

// ── Audit log helpers ──────────────────────────────────────────────────────────
export async function addAuditEvent(
  action: string,
  subject: string,
  actor: string,
  details?: string,
  metadata: AuditMetadata = {},
): Promise<AuditEvent> {
  const event = await dbInsertAuditEvent({ action, subject, actor, timestamp: nowLabel(), details, metadata });
  auditLog.unshift(event);
  if (auditLog.length > 1000) auditLog.length = 1000;
  return event;
}

function nowLabel() {
  return new Date().toLocaleString('en-GB', {
    day: '2-digit', month: 'short', year: 'numeric',
    hour: '2-digit', minute: '2-digit', timeZone: 'UTC',
  }) + ' UTC';
}

// ── Async mutation helpers (write-through: update memory + DB) ─────────────────

// Users
export async function addUser(u: UserRecord): Promise<void> {
  users.push(u);
  await dbUpsertUser(u);
}

export async function updateUser(u: UserRecord): Promise<void> {
  await dbUpsertUser(u);
}

export async function removeUser(id: number): Promise<void> {
  const idx = users.findIndex((u) => u.id === id);
  if (idx !== -1) users.splice(idx, 1);
  await dbDeleteUser(id);
}

// Layers
export async function addLayer(l: Layer): Promise<void> {
  layers.push(l);
  await dbUpsertLayer(l);
}

export async function updateLayer(l: Layer): Promise<void> {
  await dbUpsertLayer(l);
}

export async function removeLayer(id: string): Promise<void> {
  const idx = layers.findIndex((l) => l.id === id);
  if (idx !== -1) layers.splice(idx, 1);
  await dbDeleteLayer(id);
}

export async function reorderLayers(ids: string[]): Promise<void> {
  ids.forEach((id, i) => {
    const l = layers.find((x) => x.id === id);
    if (l) l.priority = i + 1;
  });
  layers.sort((a, b) => a.priority - b.priority);
  await dbReorderLayers(ids);
}

// Indicator configs
export async function addIndicatorConfig(c: IndicatorConfig): Promise<void> {
  indicatorConfigs.push(c);
  await dbUpsertIndicatorConfig(c);
}

export async function updateIndicatorConfig(c: IndicatorConfig): Promise<void> {
  await dbUpsertIndicatorConfig(c);
}

export async function removeIndicatorConfig(id: string): Promise<void> {
  const idx = indicatorConfigs.findIndex((c) => c.id === id);
  if (idx !== -1) indicatorConfigs.splice(idx, 1);
  await dbDeleteIndicatorConfig(id);
}

export async function reorderIndicatorConfigs(ids: string[]): Promise<void> {
  ids.forEach((id, i) => {
    const c = indicatorConfigs.find((x) => x.id === id);
    if (c) c.order = i + 1;
  });
  await dbReorderIndicatorConfigs(ids);
}

// Filter configs
export async function addFilterConfig(c: FilterConfig): Promise<void> {
  filterConfigs.push(c);
  await dbUpsertFilterConfig(c);
}

export async function updateFilterConfig(c: FilterConfig): Promise<void> {
  await dbUpsertFilterConfig(c);
}

export async function removeFilterConfig(id: string): Promise<void> {
  const idx = filterConfigs.findIndex((c) => c.id === id);
  if (idx !== -1) filterConfigs.splice(idx, 1);
  await dbDeleteFilterConfig(id);
}

export async function reorderFilterConfigs(ids: string[]): Promise<void> {
  ids.forEach((id, i) => {
    const c = filterConfigs.find((x) => x.id === id);
    if (c) c.order = i + 1;
  });
  await dbReorderFilterConfigs(ids);
}