/**
 * MySQL connection pool + schema bootstrap. All persistence (tenants,
 * conversations, messages) lives here. Times are stored as BIGINT epoch-ms so
 * the client's Date.now()-based math works unchanged.
 */
import mysql from "mysql2/promise";

const DB = {
  host: process.env.DB_HOST || "127.0.0.1",
  port: Number(process.env.DB_PORT || 3306),
  user: process.env.DB_USER || "root",
  password: process.env.DB_PASSWORD || "",
  database: process.env.DB_NAME || "ai_chat",
};

export const pool = mysql.createPool({
  ...DB,
  waitForConnections: true,
  connectionLimit: 10,
  namedPlaceholders: true,
});

export async function query(sql, params) {
  const [rows] = await pool.query(sql, params);
  return rows;
}

async function addColumnIfMissing(table, column, definition) {
  const rows = await query(
    `SELECT COUNT(*) AS c FROM information_schema.columns
     WHERE table_schema = :db AND table_name = :t AND column_name = :col`,
    { db: DB.database, t: table, col: column }
  );
  if (!rows[0].c) await query(`ALTER TABLE \`${table}\` ADD COLUMN \`${column}\` ${definition}`);
}

/** Create the database (if missing) and all tables. Run once at startup. */
export async function setupDatabase() {
  // Ensure the database exists using a connection without a selected DB.
  const boot = await mysql.createConnection({
    host: DB.host, port: DB.port, user: DB.user, password: DB.password,
  });
  await boot.query(`CREATE DATABASE IF NOT EXISTS \`${DB.database}\``);
  await boot.end();

  await query(`
    CREATE TABLE IF NOT EXISTS tenants (
      id BIGINT AUTO_INCREMENT PRIMARY KEY,
      public_key VARCHAR(40) NOT NULL UNIQUE,
      name VARCHAR(190) NOT NULL,
      site_url VARCHAR(255),
      admin_email VARCHAR(190) NOT NULL UNIQUE,
      password_hash VARCHAR(120) NOT NULL,
      vector_store_id VARCHAR(80),
      status ENUM('active','paused') NOT NULL DEFAULT 'active',
      settings JSON,
      created_at BIGINT NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  `);
  await addColumnIfMissing("tenants", "status", "ENUM('active','paused') NOT NULL DEFAULT 'active'");

  await query(`
    CREATE TABLE IF NOT EXISTS conversations (
      id CHAR(36) PRIMARY KEY,
      tenant_id BIGINT NOT NULL,
      name VARCHAR(190),
      email VARCHAR(190),
      mode ENUM('ai','human') NOT NULL DEFAULT 'ai',
      status ENUM('open','closed') NOT NULL DEFAULT 'open',
      handoff_requested TINYINT(1) NOT NULL DEFAULT 0,
      previous_response_id VARCHAR(120),
      agent_unread INT NOT NULL DEFAULT 0,
      created_at BIGINT NOT NULL,
      updated_at BIGINT NOT NULL,
      INDEX idx_tenant_updated (tenant_id, updated_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  `);

  await query(`
    CREATE TABLE IF NOT EXISTS messages (
      seq BIGINT AUTO_INCREMENT PRIMARY KEY,
      id CHAR(36) NOT NULL,
      conversation_id CHAR(36) NOT NULL,
      tenant_id BIGINT NOT NULL,
      role ENUM('user','assistant','agent','system') NOT NULL,
      text MEDIUMTEXT,
      ts BIGINT NOT NULL,
      INDEX idx_conv_seq (conversation_id, seq),
      INDEX idx_tenant (tenant_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  `);
}
