/**
 * enquiries — PLANNING.md §6b, verbatim DDL.
 * Spam is stored, not dropped (a honeypot hit writes status='spam') so a missing
 * lead can be traced. ip_hash is sha256(ip + IP_HASH_SALT), never the raw IP (§9).
 * FULLTEXT ft_enq_search backs the admin `q` filter (§4b).
 */
import type { Knex } from 'knex';

export async function up(knex: Knex): Promise<void> {
  await knex.schema.raw(`
    CREATE TABLE enquiries (
      id            CHAR(26)     NOT NULL PRIMARY KEY,
      name          VARCHAR(100) NOT NULL,
      phone         VARCHAR(20)  NOT NULL,
      email         VARCHAR(254) NOT NULL,
      message       TEXT         NOT NULL,
      source        ENUM('contact','genesis','hive','home') NOT NULL DEFAULT 'contact',
      status        ENUM('new','read','replied','spam','archived') NOT NULL DEFAULT 'new',
      spam_score    TINYINT UNSIGNED NOT NULL DEFAULT 0,
      spam_reason   VARCHAR(64) NULL,
      ip_hash       CHAR(64)  NULL,
      user_agent    VARCHAR(255) NULL,
      referer       VARCHAR(255) NULL,
      notified_at   DATETIME NULL,
      created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      KEY idx_enq_status_created (status, created_at),
      KEY idx_enq_source (source),
      KEY idx_enq_email (email),
      FULLTEXT KEY ft_enq_search (name, email, message)
    ) ENGINE=InnoDB
  `);
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.raw('DROP TABLE IF EXISTS enquiries');
}