wellformed

import type { FastifyInstance } from 'fastify';
import Database from 'better-sqlite3';
import { z } from 'zod';
import { PLANS, type SqlToken } from '@wellformed/shared';
import { badRequest } from '../errors.js';
import { countRows } from '../db/meta.js';
import { ensureSqlViews, syncSqlViews, type BuiltView } from '../engine/sql-views.js';
import { highlight, inlineParams } from '../engine/sql-display.js';
import { AI_GUIDE } from '../ai/guide.js';

/**
 * The interface an AI assistant works through.
 *
 * Two routes, and the split between them is the whole design. Reads are SQL,
 * because the data really is SQL — ordinary tables, real foreign keys, and a
 * catalogue in `_wellformed_*` that describes itself. An assistant that can SELECT
 * needs nothing explained to it.
 *
 * Writes are not SQL, and deliberately. A raw `UPDATE` would route around the
 * things that make a change correct here: the catalogue that has to know about
 * a new column for it to exist as far as the product is concerned, the
 * workflow rules, the plan limits, the guards that refuse to drop a column a
 * formula depends on, and the audit trail. So writing goes through the same
 * REST endpoints the browser uses, with the same checks in the same order, and
 * the key is only a different way to authenticate to them.
 *
 * The manifest below is what tells an assistant all of that in one call.
 */

const MAX_ROWS = 1000;
const DEFAULT_ROWS = 200;
/** Roughly a megabyte of JSON. A cap on the answer, not on the question. */
const MAX_RESULT_BYTES = 1_000_000;

const sqlBodySchema = z.object({
  sql: z.string().min(1).max(20_000),
  /**
   * Bound, never interpolated — the same rule the formula compiler follows.
   * An assistant building a query from something a person typed has somewhere
   * safe to put it.
   */
  params: z.array(z.union([z.string(), z.number(), z.boolean(), z.null()])).max(100).optional(),
  limit: z.number().int().min(1).max(MAX_ROWS).optional(),
});

export interface SqlResponse {
  columns: string[];
  rows: Record<string, unknown>[];
  rowCount: number;
  /** True when the answer was cut off at the row or byte cap. */
  truncated: boolean;
  /** The statement as run, with parameters inlined — for display, as in the SQL bar. */
  sql: string;
  tokens: SqlToken[];
}

export async function aiRoutes(app: FastifyInstance): Promise<void> {
  /**
   * Run one read-only statement against the workspace.
   *
   * Read-only is enforced by opening a second connection to the same file with
   * SQLite's own readonly flag, not by inspecting the statement. A blacklist of
   * dangerous keywords is a guess about a parser; this is the parser refusing.
   *
   * The connection is opened per request and closed with it. That is what makes
   * `ATTACH` inert: better-sqlite3 refuses to prepare more than one statement,
   * so an attach cannot be followed by a read in the same call, and there is no
   * next call on the same connection to inherit it.
   */
  app.post('/ai/sql', async (request): Promise<SqlResponse> => {
    request.ws.require('viewer');
    const body = sqlBodySchema.parse(request.body);
    const params = body.params ?? [];
    const limit = body.limit ?? DEFAULT_ROWS;

    // Formula columns only exist inside the compiled views, so they have to be
    // current before the statement that may read them runs. Uses the writable
    // handle; the read below gets its own.
    ensureSqlViews(request.ws.db);

    const file = app.registry.fileFor(request.ws.workspace);
    const reader = new Database(file, { readonly: true });
    reader.pragma('busy_timeout = 5000');
    reader.pragma('cache_size = -20000');

    try {
      let statement;
      try {
        statement = reader.prepare(body.sql);
      } catch (err) {
        throw badRequest(sqliteMessage(err));
      }

      if (!statement.reader) {
        throw badRequest(
          'That statement returns no rows. This endpoint is for reading; to change anything, use the write endpoints in the manifest at GET /ai/manifest.',
        );
      }

      statement.raw(false);
      const rows: Record<string, unknown>[] = [];
      let bytes = 0;
      let truncated = false;

      try {
        for (const row of statement.iterate(...params) as Iterable<Record<string, unknown>>) {
          if (rows.length >= limit) {
            truncated = true;
            break;
          }
          const shaped = shapeValues(row);
          bytes += JSON.stringify(shaped).length;
          if (bytes > MAX_RESULT_BYTES) {
            truncated = true;
            break;
          }
          rows.push(shaped);
        }
      } catch (err) {
        throw badRequest(sqliteMessage(err));
      }

      const columns = statement.columns().map((c) => c.name);
      const displaySql = inlineParams(body.sql, params);

      return {
        columns,
        rows,
        rowCount: rows.length,
        truncated,
        sql: displaySql,
        tokens: highlight(displaySql),
      };
    } finally {
      reader.close();
    }
  });

  /**
   * Everything an assistant needs to be useful here, in one call.
   *
   * Deliberately more than a schema dump: the compiled view for each table, the
   * saved views and workflow rules that are already someone's decisions, the
   * write endpoints with their shapes, and a written guide. An assistant that
   * reads this should not need the person to explain the product.
   */
  app.get('/ai/manifest', async (request) => {
    request.ws.require('viewer');
    const db = request.ws.db;
    const meta = request.ws.meta();
    const views = ensureSqlViews(db) ?? syncSqlViews(db);
    const byTable = new Map<string, BuiltView>(views.map((v) => [v.table, v]));

    const plan = PLANS[request.ws.plan()];

    return {
      workspace: {
        slug: request.ws.workspace.slug,
        name: request.ws.workspace.name,
      },
      /**
       * What this caller may actually do, already reduced by the key's ceiling.
       * An assistant should read this rather than trying a write to find out.
       */
      you: {
        role: request.ws.role,
        agent: request.agent ? { label: request.agent.label, ceiling: request.agent.ceiling } : null,
        canWriteRows: request.ws.role !== 'viewer',
        canChangeSchema: request.ws.role === 'admin',
      },
      /**
       * Named because going over a limit stops the workspace growing. An
       * assistant bulk-inserting should know the ceiling before it starts,
       * not discover it on row 1,001.
       */
      plan: { id: plan.id, rowLimit: plan.rowLimit, tableLimit: plan.tableLimit },
      tables: meta.tableOrder.map((name) => {
        const table = meta.tables[name]!;
        const built = byTable.get(name);
        return {
          name: table.name,
          label: table.label,
          display: table.display,
          rowCount: countRows(db, name),
          sqlView: built?.skipped ? null : (built?.view ?? null),
          sqlViewUnavailable: built?.skipped ?? null,
          columnErrors: built?.columnErrors ?? {},
          columns: table.columns.map((c) => ({
            name: c.name,
            /** What people call it. Speak in labels; write with `name`. */
            label: c.label,
            type: c.type,
            required: c.required ?? false,
            options: c.options ?? null,
            /** The table a link points at; its id is what the column stores. */
            target: c.target ?? null,
            /** The formula source, for `formula` and `rollup` columns. */
            expr: c.expr ?? null,
            format: c.format ?? null,
          })),
        };
      }),
      savedViews: meta.views,
      rules: meta.rules,
      endpoints: writeEndpoints(request.ws.workspace.slug),
      guide: AI_GUIDE,
    };
  });
}

/**
 * The write surface, described where an assistant will look for it.
 *
 * Listed rather than generated from the route table on purpose: this is the
 * subset an assistant should use, in the order it should reach for them, and
 * that is an editorial judgement rather than an inventory.
 */
function writeEndpoints(slug: string): { method: string; path: string; body?: string; does: string }[] {
  const base = `/api/w/${slug}`;
  return [
    {
      method: 'POST',
      path: `${base}/tables/:table/rows`,
      body: '{ "values": { "column_name": value, ... } }',
      does: 'Insert one row. Workflow rules fire; omitted columns stay blank.',
    },
    {
      method: 'PATCH',
      path: `${base}/tables/:table/rows/:id`,
      body: '{ "values": { "column_name": value, ... } }',
      does: 'Update the named columns of one row. Others are left alone.',
    },
    {
      method: 'DELETE',
      path: `${base}/tables/:table/rows/:id`,
      does: 'Delete one row.',
    },
    {
      method: 'POST',
      path: `${base}/tables`,
      body: '{ "label": "Human name", "displayLabel"?: "Name" }',
      does: 'Create a table. The identifier is derived from the label. Needs the admin role.',
    },
    {
      method: 'POST',
      path: `${base}/tables/:table/columns`,
      body: '{ "label": "Human name", "type": "text|number|currency|date|checkbox|select|link|formula|rollup", ... }',
      does: 'Add a column. The identifier is derived from the label. Runs real DDL. Needs the admin role.',
    },
    {
      method: 'PATCH',
      path: `${base}/tables/:table/columns/:column`,
      body: '{ "label"?: "New name", "format"?: ..., "expr"?: ... }',
      does: "Change a column's label, display format or formula. The identifier stays. Constraints go through the two below.",
    },
    {
      method: 'POST',
      path: `${base}/tables/:table/columns/:column/tighten/plan`,
      body: '{ "type"?: "text|number|currency|date|checkbox|select|link", "options"?: [...], "target"?: "table", "required"?: bool, "unique"?: bool, "reading"?: { "decimal": "." | ",", "dayFirst": bool } }',
      does: 'A dry run. Reads every row and reports which ones would not fit, by row and by value, plus the SQL it would run. Changes nothing.',
    },
    {
      method: 'POST',
      path: `${base}/tables/:table/columns/:column/tighten`,
      body: 'The same body, plus "onProblem": "stop" | "clear".',
      does: 'Apply the constraint. Refused, with counts, while any row does not fit. Needs the admin role.',
    },
    {
      method: 'POST',
      path: `${base}/tables/:table/normalize/plan`,
      body: '{}',
      does: 'A dry run over the short-valued text and select columns: finds spellings that read as the same answer once case is ignored, and proposes one to survive, with row counts. Whitespace differences are folded in silently, never reported. A column holding any value over 40 chars is prose and is left alone entirely. Changes nothing.',
    },
    {
      method: 'POST',
      path: `${base}/tables/:table/columns/:column/normalize`,
      body: '{ "merges": [{ "to": "C", "from": ["c"] }] }',
      does: "Rewrite every cell spelling a `from` to its `to` (matched with whitespace straightened), and straighten the column's stray whitespace automatically. A select column's option list follows. One audit line per merge, one for the whitespace.",
    },
    {
      method: 'DELETE',
      path: `${base}/tables/:table/columns/:column`,
      does: 'Drop a column. Refused when a formula depends on it, naming the dependents. Needs the admin role.',
    },
    {
      method: 'GET',
      path: `${base}/audit`,
      does: 'The change history, including everything you have done. `via` names the agent key a change came through.',
    },
  ];
}

/** SQLite's own message, which is more use to an assistant than anything we could write. */
function sqliteMessage(err: unknown): string {
  const message = err instanceof Error ? err.message : String(err);
  if (/SQLITE_READONLY/i.test(message)) {
    return 'This connection is read-only. To change data or schema, use the write endpoints in the manifest at GET /ai/manifest.';
  }
  return message;
}

/** better-sqlite3 hands back Buffers and BigInts; neither survives JSON. */
function shapeValues(row: Record<string, unknown>): Record<string, unknown> {
  const out: Record<string, unknown> = {};
  for (const [key, value] of Object.entries(row)) {
    if (typeof value === 'bigint') out[key] = Number(value);
    else if (value instanceof Buffer) out[key] = value.toString('base64');
    else out[key] = value;
  }
  return out;
}