import {
  isValidDate,
  q,
  type CellValue,
  type ColumnMeta,
} from '@wellformed/shared';
import { badRequest } from '../errors.js';
import type { Db } from '../db/driver.js';

const MAX_TEXT_LENGTH = 10_000;

/**
 * Turn a value from the wire into something the column can actually store.
 *
 * The grid sends strings for almost everything, because that is what an input
 * element produces. This is where a string becomes a number, a blank becomes
 * NULL, and a value that cannot possibly be right becomes a 400 rather than a
 * silently corrupted row.
 */
export function coerceForColumn(db: Db, col: ColumnMeta, input: unknown): CellValue {
  if (col.type === 'formula' || col.type === 'rollup') {
    throw badRequest(`"${col.label}" is a computed column and cannot be written to.`);
  }

  const blank = input === null || input === undefined || input === '';

  if (blank) {
    if (col.required) throw badRequest(`"${col.label}" is required.`);
    return col.type === 'checkbox' ? 0 : null;
  }

  switch (col.type) {
    case 'number':
    case 'currency': {
      // Money is pasted back in the shape it was copied out: with its sign in
      // front. The dollar was the only one accepted for as long as the dollar
      // was the only one shown; the rest of the signs are here for the same
      // reason it was.
      const n =
        typeof input === 'number' ? input : Number(String(input).replace(/[\s,$€£¥₹]/g, ''));
      if (!Number.isFinite(n)) {
        throw badRequest(`"${String(input)}" is not a number, so it cannot go in "${col.label}".`);
      }
      return n;
    }

    case 'checkbox':
      if (typeof input === 'boolean') return input ? 1 : 0;
      return /^(1|true|yes|on|✓)$/i.test(String(input)) ? 1 : 0;

    case 'date': {
      const s = String(input).trim();
      if (!isValidDate(s)) {
        throw badRequest(`"${s}" is not a date. Use YYYY-MM-DD.`);
      }
      return s;
    }

    case 'select': {
      const s = String(input);
      const options = col.options ?? [];
      if (options.length && !options.includes(s)) {
        throw badRequest(`"${s}" is not one of the options for "${col.label}" (${options.join(', ')}).`);
      }
      return s;
    }

    case 'link': {
      const id = String(input);
      if (!col.target) throw badRequest(`Link column "${col.label}" has no target table.`);
      const hit = db.sqlite
        .prepare<[string], { id: string }>(`SELECT id FROM ${q(col.target)} WHERE id = ?`)
        .get(id);
      if (!hit) {
        throw badRequest(`"${col.label}" must point at an existing ${col.target} row; "${id}" does not exist.`);
      }
      return id;
    }

    case 'text':
    default: {
      const s = String(input);
      if (s.length > MAX_TEXT_LENGTH) {
        throw badRequest(`"${col.label}" is limited to ${MAX_TEXT_LENGTH.toLocaleString()} characters.`);
      }
      return s;
    }
  }
}

/** SQLite column affinity for a stored column type. */
export function sqlTypeFor(type: ColumnMeta['type']): string {
  switch (type) {
    case 'number':
    case 'currency':
      return 'REAL';
    case 'checkbox':
      return 'INTEGER NOT NULL DEFAULT 0';
    default:
      return 'TEXT';
  }
}

/** How a value reads in the audit trail. */
export function describeValue(value: CellValue, col: ColumnMeta, label?: string | null): string {
  if (col.type === 'link') return label ?? (value === null ? '∅' : String(value));
  if (col.type === 'checkbox') return value ? 'checked' : 'unchecked';
  if (value === null || value === undefined || value === '') return '∅';
  return String(value);
}