/**
 * enquiry_replies — PLANNING.md §6b, verbatim DDL.
 * A thread, not a column: "reply" becomes "reply again" the first week it ships.
 * delivery_status / delivery_error record the outcome of the notification email.
 */
import type { Knex } from 'knex';

export async function up(knex: Knex): Promise<void> {
  await knex.schema.raw(`
    CREATE TABLE enquiry_replies (
      id              CHAR(26) NOT NULL PRIMARY KEY,
      enquiry_id      CHAR(26) NOT NULL,
      admin_user_id   CHAR(26) NOT NULL,
      body            TEXT     NOT NULL,
      delivery_status ENUM('pending','sent','failed') NOT NULL DEFAULT 'pending',
      delivery_error  VARCHAR(255) NULL,
      sent_at         DATETIME NULL,
      created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      KEY idx_reply_enquiry (enquiry_id, created_at),
      CONSTRAINT fk_reply_enquiry FOREIGN KEY (enquiry_id) REFERENCES enquiries(id) ON DELETE CASCADE,
      CONSTRAINT fk_reply_author  FOREIGN KEY (admin_user_id) REFERENCES admin_users(id)
    ) ENGINE=InnoDB
  `);
}

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