import type { FastifyInstance } from 'fastify';
import { slugifyTableName, type ImportResult } from '@wellformed/shared';
import { badRequest } from '../errors.js';
import { invalidateMeta } from '../db/driver.js';
import { loadMeta } from '../db/meta.js';
import { parseImport, MAX_IMPORT_ROWS, type ImportSource } from '../engine/import.js';
import { assertRoomForRows, assertRoomForTable, isOverRowLimit } from '../billing/limits.js';
import { addColumn, availableTableName, createTable, defaultViewName } from '../engine/schema.js';
import { insertRow } from '../engine/mutate.js';
import { recordAudit } from '../engine/audit.js';
import { importSchema, previewImportSchema, IMPORT_BODY_LIMIT } from './schemas.js';

/**
 * The gap between "the workspace exists" and "the workspace is doing my work".
 *
 * One way across it: bring the spreadsheet you already have. The demo is not
 * offered here — it is a property of workspace creation, chosen in the picker,
 * because a demo can only ever be the first thing in a workspace.
 */
export async function onboardingRoutes(app: FastifyInstance): Promise<void> {
  /**
   * Read a spreadsheet without writing anything.
   *
   * The preview exists because every import decision — the sheet, the
   * delimiter, the column names, the inferred types — is one the person can
   * correct only if they are shown it first. Importing straight from a file
   * picker is how you end up with a table of sixty text columns called `c_1`.
   */
  app.post('/import/preview', { bodyLimit: IMPORT_BODY_LIMIT }, async (request) => {
    request.ws.require('editor');
    const body = previewImportSchema.parse(request.body);
    const parsed = parseImport(sourceOf(body), {
      headerRow: body.headerRow,
      groupRow: body.groupRow,
      footerRows: body.footerRows,
      sheet: body.sheet,
      columns: body.columns,
      groups: body.groups,
      tableColumns: body.table ? columnsOf(request.ws.db, body.table) : undefined,
    });

    return {
      columns: parsed.columns,
      format: parsed.format,
      delimiter: parsed.delimiter,
      sheet: parsed.sheet,
      sheets: parsed.sheets,
      rowCount: parsed.rows.length,
      skipped: parsed.skipped,
      ignored: parsed.ignored,
      headerRow: parsed.headerRow,
      groupRow: parsed.groupRow,
      groupCandidate: parsed.groupCandidate,
      groups: parsed.groups,
      footerRows: parsed.footerRows,
      footerCandidate: parsed.footerCandidate,
      footerText: parsed.footerText,
      firstRows: parsed.firstRows,
      // Enough to see whether the columns line up, not enough to be a payload.
      sample: parsed.rows.slice(0, 5),
    };
  });

  app.post('/import', { bodyLimit: IMPORT_BODY_LIMIT }, async (request, reply): Promise<ImportResult> => {
    const body = importSchema.parse(request.body);
    const { db } = request.ws;

    // Creating a table is a schema change; adding rows to one is not.
    const actor = body.table
      ? request.ws.require('editor')
      : request.ws.require('admin');

    // The same rows the preview showed, so what is written is what was seen.
    const parsed = parseImport(sourceOf(body), {
      headerRow: body.headerRow,
      groupRow: body.groupRow,
      footerRows: body.footerRows,
      sheet: body.sheet,
      columns: body.columns,
      groups: body.groups,
      tableColumns: body.table ? columnsOf(db, body.table) : undefined,
    });
    if (parsed.rows.length === 0) throw badRequest('That file has a header but no rows.');

    // Only an override can make a value unreadable — an inferred type fits
    // every value by construction — and the preview already showed the count.
    // Refusing beats writing blanks: a silently emptied cell is data loss.
    const stuck = parsed.columns.find((c) => c.unreadable > 0);
    if (stuck) {
      throw badRequest(
        `${stuck.unreadable} value(s) in "${stuck.label}" will not read as ${stuck.type} — first: "${stuck.problem}". Import it as text and tighten it afterwards, or fix the file.`,
      );
    }

    // Asked before anything is written, so an import that is refused leaves no
    // half-filled table behind. A workspace with room takes the whole file
    // however far past the limit it lands — see `billing/limits.ts`.
    const plan = request.ws.plan();
    assertRoomForRows(plan, db, loadMeta(db));
    if (!body.table) assertRoomForTable(plan, loadMeta(db));

    const who = { id: actor.id, name: actor.name };
    let tableName: string;

    if (body.table) {
      const table = loadMeta(db).tables[body.table];
      if (!table) throw badRequest(`There is no table called "${body.table}" here.`);
      tableName = table.name;

      // Import into an existing table only matches headers to columns that
      // already exist. Silently creating columns from a stray header is how a
      // table grows a `column_7` nobody asked for.
      const known = new Set(table.columns.map((c) => c.name));
      const unknown = parsed.columns.filter((c) => !known.has(c.name));
      if (unknown.length > 0) {
        throw badRequest(
          `"${table.label}" has no column called ${unknown.map((c) => `"${c.label}"`).join(', ')}. Rename the header, or import into a new table.`,
        );
      }
    } else {
      // People name the table in their own words; the identifier is derived
      // and stepped past collisions. An explicit name is the API's affair.
      const givenLabel = body.label?.trim();
      if (!body.name && !givenLabel) throw badRequest('Name the table this file should become.');
      tableName = body.name ?? availableTableName(loadMeta(db), slugifyTableName(givenLabel!));

      // The first column becomes the row label, and a label is text — the
      // dialog says so and disables the choice, so this only answers the API.
      if (body.columns?.some((o) => o.index === 0 && o.type && o.type !== 'text')) {
        throw badRequest('The first column labels the rows, so it stays text.');
      }

      const display = parsed.columns[0]!;
      const label = givenLabel || tableName;
      createTable(
        db,
        {
          name: tableName,
          label,
          display: display.name,
          displayLabel: display.label,
          defaultViewName: defaultViewName(label, request.ws.workspace.locale),
        },
        who,
      );
      // `createTable` already made the first column, as the row label.
      for (const column of parsed.columns.slice(1)) {
        addColumn(db, tableName, { name: column.name, label: column.label, type: column.type }, who);
      }
      invalidateMeta(db);
    }

    let imported = 0;
    const meta = loadMeta(db);
    for (const row of parsed.rows) {
      insertRow(db, meta, tableName, row, who);
      imported++;
    }

    recordAudit(db, who, {
      kind: 'schema',
      table: tableName,
      nw: `imported ${imported} row(s) from ${describeSource(parsed)}`,
    });

    reply.code(201);
    return {
      table: tableName,
      imported,
      skipped: parsed.skipped,
      ignored: parsed.ignored,
      columns: parsed.columns,
      limit: MAX_IMPORT_ROWS,
      // Asked after the write, because this import was allowed to be the one
      // that crossed the line.
      overLimit: isOverRowLimit(plan, db, loadMeta(db)),
    };
  });
}

/**
 * The target table's columns, so the parser can match headers to them and
 * read the file the way the table will store it — into a date column as
 * dates, into a text column as the text that was written.
 */
function columnsOf(db: Parameters<typeof loadMeta>[0], tableName: string) {
  const table = loadMeta(db).tables[tableName];
  if (!table) throw badRequest(`There is no table called "${tableName}" here.`);
  return table.columns.map((c) => ({ name: c.name, label: c.label, type: c.type }));
}

/** What the request brought: a file's bytes, or text that was pasted in. */
function sourceOf(body: { csv?: string; file?: { name: string; data: string } }): ImportSource {
  if (!body.file) return body.csv!;

  const bytes = Buffer.from(body.file.data, 'base64');
  // `Buffer.from` never fails on base64; it discards what it cannot read. An
  // empty result means the payload was not base64 at all.
  if (bytes.length === 0) throw badRequest('That upload could not be read.');
  return { bytes, filename: body.file.name };
}

/** The file an import came out of, for the audit trail. */
function describeSource(parsed: { format: string; delimiter: string | null; sheet: string | null }): string {
  if (parsed.format === 'xlsx') return `sheet "${parsed.sheet}" of an Excel workbook`;
  if (parsed.format === 'ods') return `sheet "${parsed.sheet}" of an OpenDocument spreadsheet`;
  return `a ${parsed.delimiter === '\t' ? 'tab-separated' : 'delimited'} file`;
}