import {
  aggLabel,
  FormulaError,
  isAggAllowed,
  isComputed,
  isNumericColumn,
  isUnaryOp,
  q,
  type AggFn,
  type CellValue,
  type ColumnMeta,
  type Filter,
  type Row,
  type Sort,
  type SqlToken,
  type Summaries,
  type SummaryResponse,
  type ViewDef,
  type ViewMeta,
} from '@wellformed/shared';
import { badRequest } from '../errors.js';
import { findColumn, type MetaSnapshot, type TableSchema } from '../db/meta.js';
import { Aliases, Compiler, Frame, type Fragment, type Scope } from './compile.js';
import { addDef, displayTokens, inlineParams, type SqlDef } from './sql-display.js';
import type { Db } from '../db/driver.js';

export const MAX_PAGE_SIZE = 1000;
export const DEFAULT_PAGE_SIZE = 200;

/** Suffix marking the display label of a link column in the result set. */
const LABEL_SUFFIX = '::label';

export interface QueryRequest {
  table: string;
  filters: Filter[];
  sort: Sort | null;
  search?: string;
  limit: number;
  offset: number;
  /** Restrict to a single row — used to re-read a row after a write. */
  rowId?: string;
  /**
   * Columns to leave out of the SELECT entirely. The view's way of showing
   * less: the statement narrows, not just the rendering. Filters and sort may
   * still reference them — WHERE and ORDER BY never needed the SELECT list.
   */
  hidden?: string[];
}

export interface CompiledQuery {
  sql: string;
  params: unknown[];
  countSql: string;
  countParams: unknown[];
  displaySql: string;
  tokens: SqlToken[];
  /** Column name → why its expression could not be compiled. */
  columnErrors: Record<string, string>;
  selected: ColumnMeta[];
}

/**
 * Build the SELECT for a table view.
 *
 * Formula and rollup columns become expressions in the SELECT list, which is
 * why they can also appear in WHERE and ORDER BY — the grid, its filters and
 * its sort are one statement, not a pipeline of post-processing steps.
 *
 * A column whose expression no longer compiles degrades to NULL and is
 * reported in `columnErrors`, so one broken formula cannot blank the grid.
 */
export function buildQuery(meta: MetaSnapshot, req: QueryRequest): CompiledQuery {
  const table = meta.tables[req.table];
  if (!table) throw badRequest(`No table named "${req.table}".`);

  const aliases = new Aliases();
  const rootAlias = aliases.next('t');
  const frame = new Frame();
  const scope: Scope = { table: table.name, alias: rootAlias, frame };
  const compiler = new Compiler(meta, aliases);

  const columnErrors: Record<string, string> = {};
  const selectParts: string[] = [`${rootAlias}."id" AS "id"`];
  const selectParams: unknown[] = [];
  const selected: ColumnMeta[] = [];
  const hidden = new Set(req.hidden ?? []);
  // For the display layer: computed columns whose expression the statement
  // spells out, so the SQL bar can fold each back into its name.
  const defs: SqlDef[] = [];

  for (const col of table.columns) {
    if (hidden.has(col.name)) continue;
    selected.push(col);
    if (isComputed(col.type)) {
      try {
        const f = compiler.compileSource(col.expr ?? '', scope);
        selectParts.push(`${f.sql} AS ${q(col.name)}`);
        selectParams.push(...f.params);
        addDef(defs, col.name, inlineParams(f.sql, f.params));
      } catch (err) {
        columnErrors[col.name] = messageOf(err);
        selectParts.push(`NULL AS ${q(col.name)}`);
      }
      continue;
    }

    selectParts.push(`${rootAlias}.${q(col.name)} AS ${q(col.name)}`);

    if (col.type === 'link' && col.target) {
      const target = meta.tables[col.target];
      if (target) {
        const join = frame.link(rootAlias, col.name, col.target, aliases);
        selectParts.push(`${join.alias}.${q(target.display)} AS ${q(col.name + LABEL_SUFFIX)}`);
      }
    }
  }

  const where = buildWhere(table, compiler, scope, req, rootAlias, defs);
  const order = buildOrderBy(table, compiler, scope, req.sort, rootAlias, defs);

  const limit = clamp(req.limit, 1, MAX_PAGE_SIZE);
  const offset = Math.max(0, Math.floor(req.offset));

  // Joins must be rendered after every expression has had a chance to add one.
  const fromSql = ` FROM ${q(table.name)} ${rootAlias}${frame.joinSql()}`;

  const sql =
    `SELECT ${selectParts.join(', ')}${fromSql}${where.sql}${order.sql}` +
    ` LIMIT ${limit} OFFSET ${offset}`;
  const params = [...selectParams, ...where.params, ...order.params];

  const countSql = `SELECT COUNT(*) AS n${fromSql}${where.sql}`;

  const displaySql = inlineParams(sql, params);

  return {
    sql,
    params,
    countSql,
    countParams: [...where.params],
    displaySql,
    tokens: displayTokens(displaySql, defs),
    columnErrors,
    selected,
  };
}

function buildWhere(
  table: TableSchema,
  compiler: Compiler,
  scope: Scope,
  req: QueryRequest,
  rootAlias: string,
  defs: SqlDef[],
): Fragment {
  const clauses: string[] = [];
  const params: unknown[] = [];

  if (req.rowId !== undefined) {
    clauses.push(`${rootAlias}."id" = ?`);
    params.push(req.rowId);
  }

  for (const filter of req.filters) {
    const col = findColumn(table, filter.col);
    if (!col) throw badRequest(`Cannot filter on "${filter.col}": no such column in "${table.name}".`);

    let expr: Fragment;
    try {
      expr = compiler.resolvePath([col.name], scope);
    } catch (err) {
      throw badRequest(`Cannot filter on "${filter.col}": ${messageOf(err)}`);
    }
    // A hidden computed column can still be filtered on; its definition must
    // reach the display layer from here or its fold would have no legend.
    if (isComputed(col.type)) addDef(defs, col.name, inlineParams(expr.sql, expr.params));
    params.push(...expr.params);

    const blank = `COALESCE(CAST(${expr.sql} AS TEXT), '')`;

    if (isUnaryOp(filter.op)) {
      clauses.push(filter.op === 'is empty' ? `${blank} = ''` : `${blank} <> ''`);
      continue;
    }

    const raw = filter.val ?? '';

    switch (filter.op) {
      case '=':
        clauses.push(`${expr.sql} IS ?`);
        params.push(coerceFilterValue(col, raw));
        break;
      case '≠':
        clauses.push(`${expr.sql} IS NOT ?`);
        params.push(coerceFilterValue(col, raw));
        break;
      case '>':
      case '<':
        clauses.push(`${comparable(col, expr.sql)} ${filter.op} ?`);
        params.push(coerceComparand(col, raw));
        break;
      case '≥':
        clauses.push(`${comparable(col, expr.sql)} >= ?`);
        params.push(coerceComparand(col, raw));
        break;
      case '≤':
        clauses.push(`${comparable(col, expr.sql)} <= ?`);
        params.push(coerceComparand(col, raw));
        break;
      case 'contains':
        clauses.push(`${blank} LIKE ? ESCAPE '\\'`);
        params.push(`%${escapeLike(raw)}%`);
        break;
      default:
        throw badRequest(`Unsupported filter operator "${String(filter.op)}".`);
    }
  }

  const search = req.search?.trim();
  if (search) {
    // Hidden columns do not participate: a match you cannot see is a row that
    // appears for no visible reason.
    const hidden = new Set(req.hidden ?? []);
    const searchable = table.columns.filter(
      (c) => !hidden.has(c.name) && c.type !== 'checkbox' && !(isComputed(c.type) && !c.expr),
    );
    const ors: string[] = [];
    for (const col of searchable) {
      let expr: Fragment;
      try {
        expr = compiler.resolvePath([col.name], scope);
      } catch {
        continue; // A broken formula simply does not participate in search.
      }
      if (isComputed(col.type)) addDef(defs, col.name, inlineParams(expr.sql, expr.params));
      params.push(...expr.params);
      ors.push(`COALESCE(CAST(${expr.sql} AS TEXT), '') LIKE ? ESCAPE '\\'`);
      params.push(`%${escapeLike(search)}%`);
    }
    if (ors.length) clauses.push(`(${ors.join(' OR ')})`);
    else clauses.push('0');
  }

  return clauses.length ? { sql: ` WHERE ${clauses.join(' AND ')}`, params } : { sql: '', params: [] };
}

function buildOrderBy(
  table: TableSchema,
  compiler: Compiler,
  scope: Scope,
  sort: Sort | null,
  rootAlias: string,
  defs: SqlDef[],
): Fragment {
  if (!sort) return { sql: ` ORDER BY ${rootAlias}."id"`, params: [] };

  const col = findColumn(table, sort.col);
  if (!col) throw badRequest(`Cannot sort by "${sort.col}": no such column in "${table.name}".`);

  let expr: Fragment;
  try {
    expr = compiler.resolvePath([col.name], scope);
  } catch (err) {
    throw badRequest(`Cannot sort by "${sort.col}": ${messageOf(err)}`);
  }
  if (isComputed(col.type)) addDef(defs, col.name, inlineParams(expr.sql, expr.params));

  const dir = sort.dir === 'desc' ? 'DESC' : 'ASC';
  // Numeric columns must not sort as text, and blanks belong at the end
  // regardless of direction. The id tiebreak keeps pagination stable.
  const key = isNumericColumn(col) ? `CAST(${expr.sql} AS REAL)` : expr.sql;
  return {
    sql: ` ORDER BY ${key} ${dir} NULLS LAST, ${rootAlias}."id" ASC`,
    params: expr.params,
  };
}

function comparable(col: ColumnMeta, sql: string): string {
  return isNumericColumn(col) ? `CAST(${sql} AS REAL)` : sql;
}

function coerceFilterValue(col: ColumnMeta, raw: string): CellValue {
  if (raw === '') return null;
  if (col.type === 'checkbox') return /^(1|true|yes|✓)$/i.test(raw) ? 1 : 0;
  if (isNumericColumn(col)) {
    const n = Number(raw);
    return Number.isFinite(n) ? n : raw;
  }
  return raw;
}

function coerceComparand(col: ColumnMeta, raw: string): CellValue {
  if (isNumericColumn(col)) {
    const n = Number(raw);
    if (!Number.isFinite(n)) throw badRequest(`"${raw}" is not a number, so it cannot be compared with "${col.name}".`);
    return n;
  }
  return raw;
}

function escapeLike(value: string): string {
  return value.replace(/[\\%_]/g, (m) => `\\${m}`);
}

function clamp(n: number, min: number, max: number): number {
  if (!Number.isFinite(n)) return min;
  return Math.min(max, Math.max(min, Math.floor(n)));
}

function messageOf(err: unknown): string {
  if (err instanceof FormulaError) return err.message;
  return err instanceof Error ? err.message : String(err);
}

/* ---- the aggregate row --------------------------------------------------- */

export interface SummaryRequest {
  table: string;
  filters: Filter[];
  search?: string;
  /** Column name → the function to compute under it. */
  summaries: Summaries;
  /** The grid's hidden columns, so a search scopes to the same rows it shows. */
  hidden?: string[];
}

export interface CompiledSummary {
  sql: string;
  params: unknown[];
  displaySql: string;
  tokens: SqlToken[];
  /** Columns that made it into the SELECT, in the order they appear there. */
  names: string[];
  /** Column name → why its summary could not be compiled. */
  errors: Record<string, string>;
}

/** Alias of the row count. Column names must start with a letter, so it cannot collide. */
const TOTAL_ALIAS = '__total';

/**
 * Build the one statement behind the aggregate row.
 *
 * It is the grid's own query with the SELECT list replaced: same table, same
 * joins, same WHERE, no ORDER BY and no page. That is the whole point — a
 * footer computed from the rows the browser happens to hold would change as you
 * scrolled, so the aggregate is taken by the database over everything the view
 * matches, in a single round trip for every column at once.
 *
 * A column that has been dropped, or whose expression stopped compiling, is
 * reported in `errors` and left out rather than taking the statement down with
 * it — the same bargain `buildQuery` makes for a broken formula.
 */
export function buildSummary(meta: MetaSnapshot, req: SummaryRequest): CompiledSummary {
  const table = meta.tables[req.table];
  if (!table) throw badRequest(`No table named "${req.table}".`);

  const aliases = new Aliases();
  const rootAlias = aliases.next('t');
  const frame = new Frame();
  const scope: Scope = { table: table.name, alias: rootAlias, frame };
  const compiler = new Compiler(meta, aliases);

  const errors: Record<string, string> = {};
  const names: string[] = [];
  const selectParts: string[] = [`COUNT(*) AS ${q(TOTAL_ALIAS)}`];
  const selectParams: unknown[] = [];
  const defs: SqlDef[] = [];

  for (const [name, fn] of Object.entries(req.summaries)) {
    const col = findColumn(table, name);
    if (!col) {
      errors[name] = `"${name}" is no longer a column in "${table.name}".`;
      continue;
    }
    if (!isAggAllowed(col, fn)) {
      errors[name] = `${aggLabel(fn, col)} does not apply to a ${col.type} column.`;
      continue;
    }

    let expr: Fragment;
    try {
      expr = compiler.resolvePath([col.name], scope);
    } catch (err) {
      errors[name] = messageOf(err);
      continue;
    }
    if (isComputed(col.type)) addDef(defs, col.name, inlineParams(expr.sql, expr.params));

    selectParts.push(`${aggregateSql(col, fn, expr.sql)} AS ${q(name)}`);
    selectParams.push(...expr.params);
    names.push(name);
  }

  const where = buildWhere(
    table,
    compiler,
    scope,
    {
      table: req.table,
      filters: req.filters,
      sort: null,
      search: req.search,
      hidden: req.hidden,
      limit: 1,
      offset: 0,
    },
    rootAlias,
    defs,
  );

  // Joins must be rendered after every expression has had a chance to add one.
  const sql =
    `SELECT ${selectParts.join(', ')} FROM ${q(table.name)} ${rootAlias}${frame.joinSql()}` +
    where.sql;
  const params = [...selectParams, ...where.params];
  const displaySql = inlineParams(sql, params);

  return { sql, params, displaySql, tokens: displayTokens(displaySql, defs), names, errors };
}

/**
 * The SQL for one function.
 *
 * Counting functions coalesce to zero because "none of them" is a number a
 * spreadsheet states plainly; `AVG`, `MIN` and `MAX` stay null over no rows,
 * because there is no average of nothing to state.
 */
function aggregateSql(col: ColumnMeta, fn: AggFn, sql: string): string {
  const num = `CAST(${sql} AS REAL)`;
  const blank = `COALESCE(CAST(${sql} AS TEXT), '')`;
  const counted = (when: string) => `COALESCE(SUM(CASE WHEN ${when} THEN 1 ELSE 0 END), 0)`;

  switch (fn) {
    case 'sum':
      return `COALESCE(SUM(${num}), 0)`;
    case 'avg':
      return `AVG(${num})`;
    case 'min':
    case 'max':
      // Text dates sort correctly as text; numbers must not sort as text.
      return `${fn.toUpperCase()}(${isNumericColumn(col) ? num : sql})`;
    case 'filled':
      return counted(`${blank} <> ''`);
    case 'empty':
      return col.type === 'checkbox'
        ? counted(`COALESCE(${num}, 0) = 0`)
        : counted(`${blank} = ''`);
    case 'unique':
      return `COUNT(DISTINCT ${sql})`;
    case 'checked':
      return counted(`COALESCE(${num}, 0) <> 0`);
  }
}

export function runSummary(db: Db, meta: MetaSnapshot, req: SummaryRequest): SummaryResponse {
  const compiled = buildSummary(meta, req);
  const raw =
    db.sqlite
      .prepare<unknown[], Record<string, unknown>>(compiled.sql)
      .get(...compiled.params) ?? {};

  const values: Record<string, CellValue> = {};
  for (const name of compiled.names) values[name] = scalar(raw[name]);

  return {
    total: Number(raw[TOTAL_ALIAS] ?? 0),
    values,
    errors: compiled.errors,
    sql: compiled.displaySql,
    tokens: compiled.tokens,
  };
}

/** An aggregate is a bare value: no column type to normalise it against. */
function scalar(v: unknown): CellValue {
  if (v === null || v === undefined) return null;
  if (typeof v === 'bigint') return Number(v);
  if (typeof v === 'number' || typeof v === 'string' || typeof v === 'boolean') return v;
  return String(v);
}

/** Reshape a raw SQLite row into the `{ values, labels }` wire format. */
export function shapeRow(raw: Record<string, unknown>, columns: ColumnMeta[]): Row {
  const values: Record<string, CellValue> = {};
  const labels: Record<string, string | null> = {};

  for (const col of columns) {
    const v = raw[col.name];
    values[col.name] = normalize(v, col);
    if (col.type === 'link') {
      const label = raw[col.name + LABEL_SUFFIX];
      labels[col.name] = label === null || label === undefined ? null : String(label);
    }
  }

  return { id: String(raw.id), values, labels };
}

function normalize(v: unknown, col: ColumnMeta): CellValue {
  if (v === null || v === undefined) return null;
  if (col.type === 'checkbox') return v === 1 || v === true || v === '1';
  if (typeof v === 'bigint') return Number(v);
  if (v instanceof Buffer) return v.toString('utf8');
  if (typeof v === 'number' || typeof v === 'string' || typeof v === 'boolean') return v;
  return String(v);
}

export interface RunQueryResult {
  rows: Row[];
  total: number;
  sql: string;
  tokens: SqlToken[];
  columnErrors: Record<string, string>;
}

export function runQuery(db: Db, meta: MetaSnapshot, req: QueryRequest): RunQueryResult {
  const compiled = buildQuery(meta, req);
  const raw = db.sqlite
    .prepare<unknown[], Record<string, unknown>>(compiled.sql)
    .all(...compiled.params);
  const countRow = db.sqlite
    .prepare<unknown[], { n: number }>(compiled.countSql)
    .get(...compiled.countParams);

  return {
    rows: raw.map((r) => shapeRow(r, compiled.selected)),
    total: countRow?.n ?? 0,
    sql: compiled.displaySql,
    tokens: compiled.tokens,
    columnErrors: compiled.columnErrors,
  };
}

/**
 * How many rows a view shows right now.
 *
 * A view *is* its filters, so the only honest count is the one the database
 * gives for those filters — the same WHERE the grid runs, without the page.
 *
 * A view whose filters stopped compiling counts as `null` rather than throwing:
 * dropping a column rewrites the views that mention it, but a formula can break
 * under one, and one broken view must not take the whole catalogue down.
 */
export function countView(db: Db, meta: MetaSnapshot, view: ViewDef): number | null {
  try {
    const compiled = buildQuery(meta, {
      table: view.table,
      filters: view.filters,
      sort: null,
      limit: 1,
      offset: 0,
    });
    const row = db.sqlite
      .prepare<unknown[], { n: number }>(compiled.countSql)
      .get(...compiled.countParams);
    return row?.n ?? 0;
  } catch {
    return null;
  }
}

/** The wire shape for `GET /api/meta`: view definitions plus live row counts. */
export function viewsWithCounts(db: Db, meta: MetaSnapshot): ViewMeta[] {
  return meta.views.map((v) => ({ ...v, rowCount: countView(db, meta, v) }));
}

/** Read one row through the same pipeline the grid uses, labels and all. */
export function fetchRow(db: Db, meta: MetaSnapshot, table: string, id: string): Row | null {
  const result = runQuery(db, meta, {
    table,
    filters: [],
    sort: null,
    limit: 1,
    offset: 0,
    rowId: id,
  });
  return result.rows[0] ?? null;
}

/** The human label for a row, used in audit lines and link chips. */
export function rowLabel(row: Row | null, table: TableSchema): string {
  if (!row) return '';
  const raw = row.values[table.display];
  if (raw === null || raw === undefined || raw === '') return row.id;
  return String(raw);
}