import { isComputed, q, type ColumnMeta } from '@wellformed/shared';
import { Aliases, Compiler, Frame, type Scope } from './compile.js';
import { inlineParams } from './sql-display.js';
import { loadMeta, type MetaSnapshot } from '../db/meta.js';
import type { Db } from '../db/driver.js';

/**
 * Compiled SQL views, one per table.
 *
 * A formula column is an expression, not stored data, so a plain
 * `SELECT * FROM orders` shows `Qty` and a `Product` foreign key and no
 * `Total`. That is the right design for the storage and the wrong shape to
 * hand an assistant: it would have to re-derive every formula from the
 * catalogue, and rollups compile to correlated subqueries it would get wrong.
 *
 * So each table gets a view — `v_orders` — whose SELECT list is the one the
 * grid uses: stored columns, compiled formulas and rollups, and the display
 * label of every link beside its id. Reading through it, an assistant sees the
 * same numbers the person looking at the grid sees, and can filter, sort,
 * group and join on them without knowing the formula language at all.
 *
 * The views are derived, never authoritative. They are dropped and rebuilt
 * whenever the catalogue moves, which is why nothing is allowed to depend on
 * one — they exist to be read from outside, and the product itself never
 * queries them.
 */

/** Views live in the same namespace as tables, so they need a prefix of their own. */
export const VIEW_PREFIX = 'v_';

/** Suffix marking the display label of a link column, matching the query builder. */
const LABEL_SUFFIX = '::label';

export function viewNameFor(tableName: string): string {
  return VIEW_PREFIX + tableName;
}

export interface ViewColumn {
  name: string;
  /** The catalogue column this came from, or null for a generated `::label`. */
  source: string | null;
  type: ColumnMeta['type'] | 'text';
}

export interface BuiltView {
  table: string;
  view: string;
  columns: ViewColumn[];
  /** Why this table has no view, when it has none. */
  skipped?: string;
  /** Formula columns that no longer compile; they read NULL, as in the grid. */
  columnErrors: Record<string, string>;
}

/**
 * Build the `CREATE VIEW` body for one table.
 *
 * Returns null when the view cannot be made, which happens for exactly one
 * reason: a user table is already called `v_<something>` and would collide.
 */
function buildViewSelect(
  meta: MetaSnapshot,
  tableName: string,
): { sql: string; columns: ViewColumn[]; columnErrors: Record<string, string> } | null {
  const table = meta.tables[tableName];
  if (!table) return null;

  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 parts: string[] = [`${rootAlias}."id" AS "id"`];
  const params: unknown[] = [];
  const columns: ViewColumn[] = [{ name: 'id', source: null, type: 'text' }];
  const columnErrors: Record<string, string> = {};

  for (const col of table.columns) {
    if (isComputed(col.type)) {
      try {
        const f = compiler.compileSource(col.expr ?? '', scope);
        parts.push(`${f.sql} AS ${q(col.name)}`);
        params.push(...f.params);
      } catch (err) {
        // Same degradation as the grid: one broken formula reads NULL rather
        // than costing the whole table its view.
        columnErrors[col.name] = err instanceof Error ? err.message : String(err);
        parts.push(`NULL AS ${q(col.name)}`);
      }
      columns.push({ name: col.name, source: col.name, type: col.type });
      continue;
    }

    parts.push(`${rootAlias}.${q(col.name)} AS ${q(col.name)}`);
    columns.push({ name: col.name, source: col.name, type: col.type });

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

  const body = `SELECT ${parts.join(', ')} FROM ${q(table.name)} ${rootAlias}${frame.joinSql()}`;

  /**
   * A view definition cannot carry bound parameters, so the literals a formula
   * contributes are written into the text.
   *
   * This is the one place `inlineParams` is not display-only, and it is safe
   * for a reason that does not generalise: these values came from literals the
   * formula lexer produced out of the catalogue, not from anything a request
   * carried, and `literal()` escapes them the way SQLite does. Nothing a caller
   * sends ever reaches this function.
   */
  return { sql: params.length ? inlineParams(body, params) : body, columns, columnErrors };
}

/** Every view this module owns that currently exists in the database. */
function existingViews(db: Db): string[] {
  return db.sqlite
    .prepare<
      [string],
      { name: string }
    >(`SELECT name FROM sqlite_master WHERE type = 'view' AND name LIKE ? ESCAPE '\\'`)
    .all(`${VIEW_PREFIX.replace('_', '\\_')}%`)
    .map((r) => r.name);
}

/**
 * Drop every derived view, leaving them to be rebuilt on the next read.
 *
 * SQLite refuses to drop or retype a column that any other part of the schema
 * mentions, and a view that selects it mentions it. Since these views are
 * derived and nothing is allowed to depend on one, the cheapest way through a
 * column operation is to take them out of the way first. `sqlViewsVersion` is
 * wound back so the next reader builds them again from the new catalogue.
 */
export function dropSqlViews(db: Db): void {
  for (const name of existingViews(db)) {
    db.sqlite.exec(`DROP VIEW IF EXISTS ${q(name)}`);
  }
  db.sqlViewsVersion = -1;
}

/**
 * Bring the compiled views in line with the catalogue.
 *
 * Drop-then-create rather than diff: rebuilding every view costs a handful of
 * `CREATE VIEW` statements against metadata SQLite already has in memory, and
 * a diff would be a second model of what the catalogue means. Runs inside one
 * transaction, so a workspace is never seen half-rebuilt.
 */
export function syncSqlViews(db: Db): BuiltView[] {
  const meta = loadMeta(db);
  const built: BuiltView[] = [];

  const run = db.sqlite.transaction(() => {
    for (const name of existingViews(db)) {
      db.sqlite.exec(`DROP VIEW IF EXISTS ${q(name)}`);
    }

    for (const tableName of meta.tableOrder) {
      const view = viewNameFor(tableName);

      // A user table may legally be named `v_orders`. It owns the name; the
      // derived view is what gives way, and the manifest says so.
      if (meta.tables[view]) {
        built.push({
          table: tableName,
          view,
          columns: [],
          columnErrors: {},
          skipped: `A table is already named "${view}", so no view could be made for "${tableName}". Query the table directly; its formula columns will not be present.`,
        });
        continue;
      }

      const select = buildViewSelect(meta, tableName);
      if (!select) continue;

      db.sqlite.exec(`CREATE VIEW ${q(view)} AS ${select.sql}`);
      built.push({
        table: tableName,
        view,
        columns: select.columns,
        columnErrors: select.columnErrors,
      });
    }
  });

  run();
  db.sqlViewsVersion = db.metaVersion;
  return built;
}

/**
 * Rebuild the views only if the catalogue has moved since they were last built.
 *
 * Hung off `metaVersion` rather than off each schema operation, so a new way to
 * change the catalogue cannot forget to keep the views honest — it only has to
 * invalidate the metadata cache, which it already must.
 */
export function ensureSqlViews(db: Db): BuiltView[] | null {
  if (db.sqlViewsVersion === db.metaVersion) return null;
  return syncSqlViews(db);
}