import type { FastifyInstance } from 'fastify';
import { z } from 'zod';
import {
  aggLabel,
  isAggAllowed,
  slugifyTableName,
  type ColumnMeta,
  type MetaResponse,
  type Summaries,
} from '@wellformed/shared';
import { badRequest, conflict, notFound } from '../errors.js';
import { invalidateMeta, transact } from '../db/driver.js';
import { getTable, tablesWithCounts } from '../db/meta.js';
import { assertRoomForTable } from '../billing/limits.js';
import { recordAudit } from '../engine/audit.js';
import { validateExpression } from '../engine/compile.js';
import { newId } from '../engine/ids.js';
import { viewsWithCounts } from '../engine/query.js';
import {
  addColumn,
  availableTableName,
  createTable,
  defaultDisplayLabel,
  defaultViewName,
  dropColumn,
  dropTable,
  renameColumn,
  reorderColumns,
  reorderTables,
  setDisplayColumn,
  updateColumn,
} from '../engine/schema.js';
import { applyNormalize, planNormalize } from '../engine/normalize.js';
import { applyTighten, planTighten } from '../engine/tighten.js';
import { planWalk } from '../engine/walk.js';
import {
  addColumnSchema,
  columnNameSchema,
  createTableSchema,
  normalizeSchema,
  patchViewSchema,
  reorderColumnsSchema,
  reorderTablesSchema,
  ruleSchema,
  tableNameSchema,
  tightenSchema,
  updateColumnSchema,
  upsertViewSchema,
  validateExprSchema,
} from './schemas.js';

const tableParams = z.object({ table: tableNameSchema });
const columnParams = z.object({ table: tableNameSchema, column: columnNameSchema });
const idParams = z.object({ id: z.string().min(1).max(64) });

export async function catalogRoutes(app: FastifyInstance): Promise<void> {
  /** Everything the client needs to render the shell in one round trip. */
  app.get('/meta', async (request): Promise<MetaResponse> => {
    request.ws.require('viewer');
    const meta = request.ws.meta();
    return {
      tables: tablesWithCounts(request.ws.db, meta),
      views: viewsWithCounts(request.ws.db, meta),
      rules: meta.rules,
    };
  });

  app.post('/formula/validate', async (request) => {
    request.ws.require('viewer');
    const body = validateExprSchema.parse(request.body);
    const meta = request.ws.meta();
    getTable(meta, body.table);
    const error = validateExpression(meta, body.table, body.expr);
    return { ok: error === null, error };
  });

  // ---- tables -------------------------------------------------------------

  app.post('/tables', async (request, reply) => {
    request.ws.require('admin');
    const body = createTableSchema.parse(request.body);
    const meta = request.ws.meta();
    assertRoomForTable(request.ws.plan(), meta);
    const name = createTable(
      request.ws.db,
      {
        ...body,
        name: body.name ?? availableTableName(meta, slugifyTableName(body.label)),
        displayLabel: body.displayLabel ?? defaultDisplayLabel(request.ws.workspace.locale),
        defaultViewName: defaultViewName(body.label, request.ws.workspace.locale),
      },
      request.ws.actor(),
    );
    reply.code(201);
    return { table: request.ws.meta().tables[name] };
  });

  app.delete('/tables/:table', async (request) => {
    request.ws.require('admin');
    const { table } = tableParams.parse(request.params);
    dropTable(request.ws.db, table, request.ws.actor());
    return { ok: true };
  });

  // Editor like column reorder: it moves furniture, it changes no data.
  app.post('/tables/reorder', async (request) => {
    request.ws.require('editor');
    const { order } = reorderTablesSchema.parse(request.body);
    reorderTables(request.ws.db, order, request.ws.actor());
    return { ok: true };
  });

  app.patch('/tables/:table', async (request) => {
    request.ws.require('admin');
    const { table } = tableParams.parse(request.params);
    const body = z
      .object({
        label: z.string().min(1).max(60).optional(),
        display: columnNameSchema.optional(),
        /** The sidebar heading; null takes the table off its shelf. */
        group: z.string().trim().min(1).max(60).nullable().optional(),
      })
      .strict()
      .parse(request.body);

    const meta = request.ws.meta();
    const schema = getTable(meta, table);
    const actor = request.ws.actor();

    if (body.label && body.label !== schema.label) {
      transact(request.ws.db, () => {
        request.ws.db.sqlite.prepare('UPDATE _wellformed_table SET label = ? WHERE name = ?').run(body.label, table);
        recordAudit(request.ws.db, actor, {
          kind: 'schema',
          table,
          old: schema.label,
          nw: `RENAME TABLE LABEL → ${body.label}`,
        });
      });
      invalidateMeta(request.ws.db);
    }

    if (body.display && body.display !== schema.display) {
      setDisplayColumn(request.ws.db, table, body.display, actor);
    }

    if (body.group !== undefined && body.group !== schema.group) {
      transact(request.ws.db, () => {
        request.ws.db.sqlite
          .prepare('UPDATE _wellformed_table SET group_name = ? WHERE name = ?')
          .run(body.group, table);
        recordAudit(request.ws.db, actor, {
          kind: 'schema',
          table,
          old: schema.group ?? undefined,
          nw: body.group ? `GROUP → ${body.group}` : 'GROUP CLEARED',
        });
      });
      invalidateMeta(request.ws.db);
    }

    return { table: request.ws.meta().tables[table] };
  });

  // ---- columns ------------------------------------------------------------

  app.post('/tables/:table/columns', async (request, reply) => {
    request.ws.require('admin');
    const { table } = tableParams.parse(request.params);
    const body = addColumnSchema.parse(request.body);
    const column = addColumn(request.ws.db, table, body, request.ws.actor());
    reply.code(201);
    return { column };
  });

  app.patch('/tables/:table/columns/:column', async (request) => {
    request.ws.require('admin');
    const { table, column } = columnParams.parse(request.params);
    const body = updateColumnSchema.parse(request.body);
    const actor = request.ws.actor();

    const { rename, ...rest } = body;
    if (Object.keys(rest).length) updateColumn(request.ws.db, table, column, rest, actor);
    if (rename) renameColumn(request.ws.db, table, column, rename, actor);

    const meta = request.ws.meta();
    const updated = getTable(meta, table).columns.find((c) => c.name === (rename ?? column));
    if (!updated) throw notFound(`Column "${rename ?? column}" disappeared.`);
    return { column: updated };
  });

  /**
   * The dry run. Read-only, and the only way to find out what a constraint
   * would cost before it binds — so it answers for anyone who may edit, not
   * only for whoever is allowed to apply it.
   */
  app.post('/tables/:table/columns/:column/tighten/plan', async (request) => {
    request.ws.require('editor');
    const { table, column } = columnParams.parse(request.params);
    const body = tightenSchema.parse(request.body);
    return { plan: planTighten(request.ws.db, table, column, body) };
  });

  app.post('/tables/:table/columns/:column/tighten', async (request) => {
    request.ws.require('admin');
    const { table, column } = columnParams.parse(request.params);
    const body = tightenSchema.parse(request.body);
    const result = applyTighten(request.ws.db, table, column, body, request.ws.actor());
    return { column: result.column, plan: result.plan, cleared: result.cleared };
  });

  /**
   * The tighten walk: every stored column looked at once, each with what its
   * values say it really is — a detected type, spellings to merge, a select
   * in waiting. Read-only; each step is applied through normalize and
   * tighten, one column at a time.
   */
  app.post('/tables/:table/tighten/walk', async (request) => {
    request.ws.require('editor');
    const { table } = tableParams.parse(request.params);
    return { plan: planWalk(request.ws.db, table) };
  });

  /**
   * The scan behind the walk's merge step: every text and select column, read
   * for spellings that are the same answer written differently. Read-only,
   * like the tighten plan, and answering for anyone who may edit.
   */
  app.post('/tables/:table/normalize/plan', async (request) => {
    request.ws.require('editor');
    const { table } = tableParams.parse(request.params);
    return { plan: planNormalize(request.ws.db, table) };
  });

  /**
   * Editor, not admin: merging spellings is editing cell values, which an
   * editor may already do row by row. A select's option list follows the
   * values it describes — that is the merge finishing, not the list being
   * redesigned.
   */
  app.post('/tables/:table/columns/:column/normalize', async (request) => {
    request.ws.require('editor');
    const { table, column } = columnParams.parse(request.params);
    const body = normalizeSchema.parse(request.body);
    const result = applyNormalize(request.ws.db, table, column, body.merges, request.ws.actor());
    return { column: result.column, changed: result.changed };
  });

  app.post('/tables/:table/columns/reorder', async (request) => {
    request.ws.require('editor');
    const { table } = tableParams.parse(request.params);
    const { order } = reorderColumnsSchema.parse(request.body);
    reorderColumns(request.ws.db, table, order, request.ws.actor());
    return { table: request.ws.meta().tables[table] };
  });

  app.delete('/tables/:table/columns/:column', async (request) => {
    request.ws.require('admin');
    const { table, column } = columnParams.parse(request.params);
    dropColumn(request.ws.db, table, column, request.ws.actor());
    return { ok: true };
  });

  // ---- views --------------------------------------------------------------

  app.post('/tables/:table/views', async (request, reply) => {
    request.ws.require('editor');
    const { table } = tableParams.parse(request.params);
    const body = upsertViewSchema.parse(request.body);
    const meta = request.ws.meta();
    const schema = getTable(meta, table);

    validateViewColumns(schema.columns, body);
    if (meta.views.some((v) => v.table === table && v.name === body.name)) {
      throw conflict(`"${table}" already has a view called "${body.name}".`);
    }

    const id = newId('vw');
    const position =
      Math.max(-1, ...meta.views.filter((v) => v.table === table).map((v) => v.position)) + 1;

    transact(request.ws.db, () => {
      request.ws.db.sqlite
        .prepare(
          'INSERT INTO _wellformed_view (id, table_name, name, filters, sort, summaries, hidden, position, system) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 0)',
        )
        .run(
          id,
          table,
          body.name,
          JSON.stringify(body.filters),
          body.sort ? JSON.stringify(body.sort) : null,
          JSON.stringify(body.summaries),
          JSON.stringify(body.hidden),
          position,
        );
      recordAudit(request.ws.db, request.ws.actor(), {
        kind: 'view',
        table,
        nw: `CREATE VIEW ${body.name}`,
      });
    });
    invalidateMeta(request.ws.db);

    reply.code(201);
    return { view: request.ws.meta().views.find((v) => v.id === id) };
  });

  app.patch('/views/:id', async (request) => {
    request.ws.require('editor');
    const { id } = idParams.parse(request.params);
    const body = patchViewSchema.parse(request.body);

    const meta = request.ws.meta();
    const view = meta.views.find((v) => v.id === id);
    if (!view) throw notFound(`No view "${id}".`);

    const schema = getTable(meta, view.table);
    const next = {
      name: body.name ?? view.name,
      filters: body.filters ?? view.filters,
      sort: body.sort === undefined ? view.sort : body.sort,
      summaries: body.summaries ?? view.summaries,
      hidden: body.hidden ?? view.hidden,
    };
    validateViewColumns(schema.columns, next);

    transact(request.ws.db, () => {
      request.ws.db.sqlite
        .prepare('UPDATE _wellformed_view SET name = ?, filters = ?, sort = ?, summaries = ?, hidden = ? WHERE id = ?')
        .run(
          next.name,
          JSON.stringify(next.filters),
          next.sort ? JSON.stringify(next.sort) : null,
          JSON.stringify(next.summaries),
          JSON.stringify(next.hidden),
          id,
        );
      recordAudit(request.ws.db, request.ws.actor(), {
        kind: 'view',
        table: view.table,
        old: view.name,
        nw: `UPDATE VIEW ${next.name}`,
      });
    });
    invalidateMeta(request.ws.db);

    return { view: request.ws.meta().views.find((v) => v.id === id) };
  });

  app.delete('/views/:id', async (request) => {
    request.ws.require('editor');
    const { id } = idParams.parse(request.params);
    const view = request.ws.meta().views.find((v) => v.id === id);
    if (!view) throw notFound(`No view "${id}".`);
    if (view.system) throw conflict(`"${view.name}" is a built-in view and cannot be deleted.`);

    transact(request.ws.db, () => {
      request.ws.db.sqlite.prepare('DELETE FROM _wellformed_view WHERE id = ?').run(id);
      recordAudit(request.ws.db, request.ws.actor(), {
        kind: 'view',
        table: view.table,
        nw: `DROP VIEW ${view.name}`,
      });
    });
    invalidateMeta(request.ws.db);

    return { ok: true };
  });

  // ---- workflow rules -----------------------------------------------------

  app.post('/rules', async (request, reply) => {
    request.ws.require('admin');
    const body = ruleSchema.parse(request.body);
    const meta = request.ws.meta();
    const schema = getTable(meta, body.table);

    const trigger = schema.columns.find((c) => c.name === body.column);
    if (!trigger) throw notFound(`"${body.table}" has no column "${body.column}".`);
    for (const action of body.actions) {
      const target = schema.columns.find((c) => c.name === action.column);
      if (!target) throw notFound(`"${body.table}" has no column "${action.column}".`);
      if (target.type === 'formula' || target.type === 'rollup') {
        throw badRequest(`"${action.column}" is computed; a rule cannot set it.`);
      }
    }

    const id = newId('rl');
    transact(request.ws.db, () => {
      request.ws.db.sqlite
        .prepare(
          'INSERT INTO _wellformed_rule (id, table_name, column_name, equals, label, actions, enabled) VALUES (?, ?, ?, ?, ?, ?, ?)',
        )
        .run(id, body.table, body.column, body.equals, body.label, JSON.stringify(body.actions), body.enabled ? 1 : 0);
      recordAudit(request.ws.db, request.ws.actor(), {
        kind: 'schema',
        table: body.table,
        column: body.column,
        nw: `CREATE RULE ${body.label}`,
      });
    });
    invalidateMeta(request.ws.db);

    reply.code(201);
    return { rule: request.ws.meta().rules.find((r) => r.id === id) };
  });

  app.patch('/rules/:id', async (request) => {
    request.ws.require('admin');
    const { id } = idParams.parse(request.params);
    const body = z.object({ enabled: z.boolean() }).strict().parse(request.body);

    const rule = request.ws.meta().rules.find((r) => r.id === id);
    if (!rule) throw notFound(`No rule "${id}".`);

    transact(request.ws.db, () => {
      request.ws.db.sqlite.prepare('UPDATE _wellformed_rule SET enabled = ? WHERE id = ?').run(body.enabled ? 1 : 0, id);
      recordAudit(request.ws.db, request.ws.actor(), {
        kind: 'schema',
        table: rule.table,
        nw: `${body.enabled ? 'ENABLE' : 'DISABLE'} RULE ${rule.label}`,
      });
    });
    invalidateMeta(request.ws.db);

    return { rule: request.ws.meta().rules.find((r) => r.id === id) };
  });

  app.delete('/rules/:id', async (request) => {
    request.ws.require('admin');
    const { id } = idParams.parse(request.params);
    const rule = request.ws.meta().rules.find((r) => r.id === id);
    if (!rule) throw notFound(`No rule "${id}".`);

    transact(request.ws.db, () => {
      request.ws.db.sqlite.prepare('DELETE FROM _wellformed_rule WHERE id = ?').run(id);
      recordAudit(request.ws.db, request.ws.actor(), {
        kind: 'schema',
        table: rule.table,
        nw: `DROP RULE ${rule.label}`,
      });
    });
    invalidateMeta(request.ws.db);

    return { ok: true };
  });
}

function validateViewColumns(
  known: ColumnMeta[],
  view: {
    filters?: { col: string }[];
    sort?: { col: string } | null;
    summaries?: Summaries;
    hidden?: string[];
  },
): void {
  const byName = new Map(known.map((c) => [c.name, c]));
  for (const f of view.filters ?? []) {
    if (!byName.has(f.col)) throw badRequest(`This view filters on "${f.col}", which does not exist.`);
  }
  if (view.sort && !byName.has(view.sort.col)) {
    throw badRequest(`This view sorts by "${view.sort.col}", which does not exist.`);
  }
  for (const name of view.hidden ?? []) {
    if (!byName.has(name)) throw badRequest(`This view hides "${name}", which does not exist.`);
  }
  if (view.hidden && view.hidden.length >= known.length) {
    throw badRequest('A view must show at least one column.');
  }
  for (const [name, fn] of Object.entries(view.summaries ?? {})) {
    const col = byName.get(name);
    if (!col) throw badRequest(`This view summarises "${name}", which does not exist.`);
    // Refused here rather than stored and reported forever after: a saved view
    // should not be able to hold a summary the database would decline to take.
    if (!isAggAllowed(col, fn)) {
      throw badRequest(`${aggLabel(fn, col)} does not apply to the ${col.type} column "${name}".`);
    }
  }
}