import {
  isComputed,
  nextSequenceValue,
  nowISO,
  q,
  todayISO,
  type CellValue,
  type ColumnMeta,
  type Row,
  type Rule,
} from '@wellformed/shared';
import { badRequest, conflict, notFound } from '../errors.js';
import { transact, type Db } from '../db/driver.js';
import { getTable, incomingLinks, requireColumn, storedColumns, type MetaSnapshot } from '../db/meta.js';
import { recordAudit, SYSTEM_ACTOR, type Actor } from './audit.js';
import { fetchRow, rowLabel } from './query.js';
import { newId, isValidId } from './ids.js';
import { coerceForColumn, describeValue } from './values.js';

export interface WriteResult {
  row: Row;
  /** Labels of the workflow rules that fired, in order. */
  workflows: string[];
}

/**
 * Apply the workflow rules that match a set of pending changes.
 *
 * A rule fires when its column is being set to its trigger value. Rule-applied
 * changes do not themselves re-trigger rules; one pass keeps the outcome of an
 * edit predictable and prevents two rules from ping-ponging.
 */
function applyRules(
  db: Db,
  meta: MetaSnapshot,
  tableName: string,
  patch: Map<string, CellValue>,
  actor: Actor,
): string[] {
  const table = getTable(meta, tableName);
  const fired: string[] = [];

  const matching = meta.rules.filter(
    (r) => r.enabled && r.table === tableName && patch.has(r.column),
  );

  for (const rule of matching) {
    const value = patch.get(rule.column);
    if (String(value ?? '') !== rule.equals) continue;
    for (const action of rule.actions) {
      if (action.type !== 'set') continue;
      const col = table.columns.find((c) => c.name === action.column);
      if (!col || isComputed(col.type)) continue;
      patch.set(col.name, coerceForColumn(db, col, resolveActionValue(action.value, actor)));
    }
    fired.push(rule.label);
  }

  return fired;
}

function resolveActionValue(value: string, actor: Actor): string {
  switch (value) {
    case '@today':
      return todayISO();
    case '@now':
      return nowISO();
    case '@user':
      return actor.name;
    case '@empty':
      return '';
    default:
      return value;
  }
}

export function insertRow(
  db: Db,
  meta: MetaSnapshot,
  tableName: string,
  input: Record<string, unknown>,
  actor: Actor,
  explicitId?: string,
): WriteResult {
  const table = getTable(meta, tableName);

  if (explicitId !== undefined && !isValidId(explicitId)) {
    throw badRequest(`"${explicitId}" is not a valid row id.`);
  }
  const id = explicitId ?? newId(tableName.slice(0, 3));

  const patch = new Map<string, CellValue>();
  for (const [key, raw] of Object.entries(input)) {
    if (key === 'id') continue;
    const col = requireColumn(table, key);
    if (isComputed(col.type)) continue;
    // An empty cell in a self-numbering column is a request for the next
    // number, not a value — it falls through to the fill below, where a
    // required one would otherwise be refused as blank.
    if (col.sequence && (raw === null || raw === undefined || raw === '')) continue;
    patch.set(col.name, coerceForColumn(db, col, raw));
  }

  // Self-numbering columns fill themselves when the row arrives without them.
  // The counter is read off the rows already in the column, scoped to the
  // pattern's rendered date parts — `INV-{yyyy}-{n}` starts over each January
  // because nothing from last year matches it any more. A typed value stands;
  // the pattern only speaks for cells left empty.
  for (const col of storedColumns(table)) {
    if (col.type !== 'text' || !col.sequence || patch.has(col.name)) continue;
    const existing = db.sqlite
      .prepare<[], { v: string }>(
        `SELECT ${q(col.name)} AS v FROM ${q(tableName)} WHERE ${q(col.name)} IS NOT NULL`,
      )
      .all()
      .map((r) => String(r.v));
    patch.set(col.name, nextSequenceValue(col.sequence, existing, new Date()));
  }

  // Required columns must be present even when the client omitted them.
  for (const col of storedColumns(table)) {
    if (col.required && !patch.has(col.name)) {
      throw badRequest(`"${col.label}" is required.`);
    }
  }

  const workflows = applyRules(db, meta, tableName, patch, actor);

  return transact(db, () => {
    const existing = db.sqlite
      .prepare<[string], { id: string }>(`SELECT id FROM ${q(tableName)} WHERE id = ?`)
      .get(id);
    if (existing) throw conflict(`A row with id "${id}" already exists in "${tableName}".`);

    const names = ['id', ...patch.keys()];
    const values = [id, ...patch.values()];
    const placeholders = names.map(() => '?').join(', ');

    db.sqlite
      .prepare(`INSERT INTO ${q(tableName)} (${names.map(q).join(', ')}) VALUES (${placeholders})`)
      .run(...values);

    const row = fetchRow(db, meta, tableName, id);
    if (!row) throw notFound(`Row "${id}" vanished immediately after insert.`);
    const label = rowLabel(row, table);

    recordAudit(db, actor, {
      kind: 'insert',
      table: tableName,
      rowId: id,
      rowLabel: label,
      // The badge already says "insert" and the location line already names the
      // table, so echoing the statement here only repeated both.
      nw: 'created',
    });
    for (const w of workflows) {
      recordAudit(db, SYSTEM_ACTOR, {
        kind: 'workflow',
        table: tableName,
        rowId: id,
        rowLabel: label,
        nw: w,
      });
    }

    return { row, workflows };
  });
}

export function updateRow(
  db: Db,
  meta: MetaSnapshot,
  tableName: string,
  id: string,
  input: Record<string, unknown>,
  actor: Actor,
): WriteResult {
  const table = getTable(meta, tableName);

  const before = fetchRow(db, meta, tableName, id);
  if (!before) throw notFound(`No row "${id}" in "${tableName}".`);

  const patch = new Map<string, CellValue>();
  const touched: ColumnMeta[] = [];

  for (const [key, raw] of Object.entries(input)) {
    if (key === 'id') throw badRequest('Row ids cannot be changed.');
    const col = requireColumn(table, key);
    if (isComputed(col.type)) {
      throw badRequest(`"${col.label}" is a computed column and cannot be written to.`);
    }
    const next = coerceForColumn(db, col, raw);
    if (sameValue(before.values[col.name] ?? null, next)) continue;
    patch.set(col.name, next);
    touched.push(col);
  }

  if (patch.size === 0) return { row: before, workflows: [] };

  const workflows = applyRules(db, meta, tableName, patch, actor);

  return transact(db, () => {
    const assignments = [...patch.keys()].map((name) => `${q(name)} = ?`).join(', ');
    const info = db.sqlite
      .prepare(`UPDATE ${q(tableName)} SET ${assignments} WHERE id = ?`)
      .run(...patch.values(), id);
    if (info.changes === 0) throw notFound(`No row "${id}" in "${tableName}".`);

    const after = fetchRow(db, meta, tableName, id);
    if (!after) throw notFound(`Row "${id}" vanished during update.`);
    const label = rowLabel(after, table);

    for (const [name, next] of patch) {
      const col = table.columns.find((c) => c.name === name)!;
      recordAudit(db, actor, {
        kind: 'edit',
        table: tableName,
        rowId: id,
        rowLabel: label,
        column: name,
        old: describeValue(before.values[name] ?? null, col, before.labels[name]),
        nw: describeValue(next, col, after.labels[name]),
      });
    }

    for (const w of workflows) {
      recordAudit(db, SYSTEM_ACTOR, {
        kind: 'workflow',
        table: tableName,
        rowId: id,
        rowLabel: label,
        nw: w,
      });
    }

    return { row: after, workflows };
  });
}

export interface DeleteResult {
  /** Stored values of the deleted row, enough to recreate it for an undo. */
  restore: Record<string, CellValue>;
  label: string;
}

export function deleteRow(
  db: Db,
  meta: MetaSnapshot,
  tableName: string,
  id: string,
  actor: Actor,
): DeleteResult {
  const table = getTable(meta, tableName);
  const before = fetchRow(db, meta, tableName, id);
  if (!before) throw notFound(`No row "${id}" in "${tableName}".`);

  const label = rowLabel(before, table);
  const restore: Record<string, CellValue> = { id };
  for (const col of storedColumns(table)) {
    restore[col.name] = before.values[col.name] ?? null;
  }

  // Foreign keys are ON DELETE SET NULL, so rows elsewhere quietly lose their
  // link. Count them first and say so in the trail rather than let it pass.
  const orphaned = incomingLinks(meta, tableName).reduce((sum, ref) => {
    const row = db.sqlite
      .prepare<[string], { n: number }>(
        `SELECT COUNT(*) AS n FROM ${q(ref.table)} WHERE ${q(ref.column.name)} = ?`,
      )
      .get(id);
    return sum + (row?.n ?? 0);
  }, 0);

  return transact(db, () => {
    const info = db.sqlite.prepare(`DELETE FROM ${q(tableName)} WHERE id = ?`).run(id);
    if (info.changes === 0) throw notFound(`No row "${id}" in "${tableName}".`);

    recordAudit(db, actor, {
      kind: 'delete',
      table: tableName,
      rowId: id,
      rowLabel: label,
      old: label,
      nw:
        orphaned > 0
          ? `deleted — cleared ${orphaned} link${orphaned === 1 ? '' : 's'}`
          : 'deleted',
    });

    return { restore, label };
  });
}

function sameValue(a: CellValue, b: CellValue): boolean {
  if (a === b) return true;
  if (a === null || b === null) return false;
  if (typeof a === 'boolean' || typeof b === 'boolean') return Boolean(a) === Boolean(b);
  return String(a) === String(b);
}

/** Rules attached to a table, for the sidebar. */
export function rulesFor(meta: MetaSnapshot, tableName: string): Rule[] {
  return meta.rules.filter((r) => r.table === tableName);
}