import {
  isComputed,
  q,
  type ColumnFormat,
  type ColumnMeta,
  type ColumnType,
  type Filter,
  type Rule,
  type RuleAction,
  type Sort,
  type Summaries,
  type TableMeta,
  type ViewDef,
} from '@wellformed/shared';
import { notFound } from '../errors.js';
import type { Db } from './driver.js';

export type TableSchema = Omit<TableMeta, 'rowCount'>;

export interface MetaSnapshot {
  tables: Record<string, TableSchema>;
  tableOrder: string[];
  views: ViewDef[];
  rules: Rule[];
}

interface TableRow {
  name: string;
  label: string;
  display_column: string;
  group_name: string | null;
  position: number;
}

interface ColumnRow {
  table_name: string;
  name: string;
  label: string | null;
  type: string;
  position: number;
  options: string | null;
  target_table: string | null;
  expr: string | null;
  format: string | null;
  required: number;
  uniq: number;
  sequence: string | null;
}

interface ViewRow {
  id: string;
  table_name: string;
  name: string;
  filters: string;
  sort: string | null;
  summaries: string | null;
  hidden: string | null;
  position: number;
  system: number;
}

interface RuleRow {
  id: string;
  table_name: string;
  column_name: string;
  equals: string;
  label: string;
  actions: string;
  enabled: number;
}

function safeParse<T>(json: string | null, fallback: T): T {
  if (!json) return fallback;
  try {
    return JSON.parse(json) as T;
  } catch {
    return fallback;
  }
}

/** Read the whole metadata catalogue, memoised on the `Db` handle. */
export function loadMeta(db: Db): MetaSnapshot {
  if (db.metaCache) return db.metaCache;

  const tableRows = db.sqlite
    .prepare<[], TableRow>('SELECT name, label, display_column, group_name, position FROM _wellformed_table ORDER BY position, name')
    .all();

  const columnRows = db.sqlite
    .prepare<[], ColumnRow>(
      'SELECT table_name, name, label, type, position, options, target_table, expr, format, required, uniq, sequence FROM _wellformed_column ORDER BY table_name, position, name',
    )
    .all();

  const tables: Record<string, TableSchema> = {};
  const tableOrder: string[] = [];

  for (const t of tableRows) {
    tables[t.name] = {
      name: t.name,
      label: t.label,
      display: t.display_column,
      group: t.group_name,
      position: t.position,
      columns: [],
    };
    tableOrder.push(t.name);
  }

  for (const c of columnRows) {
    const table = tables[c.table_name];
    if (!table) continue;
    const col: ColumnMeta = {
      name: c.name,
      // The migration filled every label; the fallback only answers for a row
      // written by a version that predates it and not yet migrated past.
      label: c.label || c.name,
      type: c.type as ColumnType,
      position: c.position,
      format: (c.format as ColumnFormat | null) ?? null,
      required: c.required === 1,
      unique: c.uniq === 1,
    };
    if (c.options) col.options = safeParse<string[]>(c.options, []);
    if (c.target_table) col.target = c.target_table;
    if (c.expr) col.expr = c.expr;
    if (c.sequence) col.sequence = c.sequence;
    table.columns.push(col);
  }

  const views = db.sqlite
    .prepare<[], ViewRow>(
      'SELECT id, table_name, name, filters, sort, summaries, hidden, position, system FROM _wellformed_view ORDER BY table_name, position, name',
    )
    .all()
    .map<ViewDef>((v) => ({
      id: v.id,
      table: v.table_name,
      name: v.name,
      filters: safeParse<Filter[]>(v.filters, []),
      sort: safeParse<Sort | null>(v.sort, null),
      summaries: safeParse<Summaries>(v.summaries, {}),
      hidden: safeParse<string[]>(v.hidden, []),
      position: v.position,
      system: v.system === 1,
    }));

  const rules = db.sqlite
    .prepare<[], RuleRow>(
      'SELECT id, table_name, column_name, equals, label, actions, enabled FROM _wellformed_rule ORDER BY table_name, column_name',
    )
    .all()
    .map<Rule>((r) => ({
      id: r.id,
      table: r.table_name,
      column: r.column_name,
      equals: r.equals,
      label: r.label,
      actions: safeParse<RuleAction[]>(r.actions, []),
      enabled: r.enabled === 1,
    }));

  const snapshot: MetaSnapshot = { tables, tableOrder, views, rules };
  db.metaCache = snapshot;
  return snapshot;
}

export function getTable(meta: MetaSnapshot, name: string): TableSchema {
  const t = meta.tables[name];
  if (!t) throw notFound(`No table named "${name}".`);
  return t;
}

export function findColumn(table: TableSchema, name: string): ColumnMeta | undefined {
  return table.columns.find((c) => c.name === name);
}

export function requireColumn(table: TableSchema, name: string): ColumnMeta {
  const c = findColumn(table, name);
  if (!c) throw notFound(`Table "${table.name}" has no column "${name}".`);
  return c;
}

/** Columns backed by a physical SQL column, in declared order. */
export function storedColumns(table: TableSchema): ColumnMeta[] {
  return table.columns.filter((c) => !isComputed(c.type));
}

export function countRows(db: Db, tableName: string): number {
  const row = db.sqlite
    .prepare<[], { n: number }>(`SELECT COUNT(*) AS n FROM ${q(tableName)}`)
    .get();
  return row?.n ?? 0;
}

/** The wire shape for `GET /api/meta`: schema plus live row counts. */
export function tablesWithCounts(db: Db, meta: MetaSnapshot): TableMeta[] {
  return meta.tableOrder.map((name) => {
    const t = meta.tables[name]!;
    return { ...t, rowCount: countRows(db, name) };
  });
}

/** Link columns on other tables that point at `tableName`. */
export function incomingLinks(meta: MetaSnapshot, tableName: string): { table: string; column: ColumnMeta }[] {
  const out: { table: string; column: ColumnMeta }[] = [];
  for (const name of meta.tableOrder) {
    for (const col of meta.tables[name]!.columns) {
      if (col.type === 'link' && col.target === tableName) out.push({ table: name, column: col });
    }
  }
  return out;
}