import mysql, { type Pool, type RowDataPacket } from "mysql2/promise";
import type { AdminState, DownloadAccess, LeadEvent } from "./mpc-types";

let pool: Pool | null = null;
let schemaReady: Promise<void> | null = null;

type ColumnRow = RowDataPacket & { COLUMN_NAME?: string; column_name?: string };

function dbEnv(name: "HOST" | "PORT" | "NAME" | "USER" | "PASSWORD" | "SSL" | "CONNECTION_LIMIT") {
  const stable = process.env[`DB_${name}`];
  const legacyMap: Record<string, string | undefined> = {
    HOST: process.env.MPC_MYSQL_HOST,
    PORT: process.env.MPC_MYSQL_PORT,
    NAME: process.env.MPC_MYSQL_DATABASE,
    USER: process.env.MPC_MYSQL_USER,
    PASSWORD: process.env.MPC_MYSQL_PASSWORD,
    SSL: process.env.MPC_MYSQL_SSL,
    CONNECTION_LIMIT: process.env.MPC_MYSQL_CONNECTION_LIMIT,
  };
  return stable || legacyMap[name] || "";
}

function assertDatabaseIsolation() {
  const appEnv = String(process.env.APP_ENV || "production").toLowerCase();
  const database = dbEnv("NAME");
  if (appEnv !== "production" && appEnv !== "staging") throw new Error("APP_ENV is required");
  const explicitDriver = String(process.env.DB_DRIVER || process.env.MPC_DB_DRIVER || "").toLowerCase();
  const hasAllMysqlValues = (["HOST", "NAME", "USER", "PASSWORD"] as const).every((key) => Boolean(dbEnv(key)));
  const hasAnyMysqlValue = (["HOST", "NAME", "USER", "PASSWORD"] as const).some((key) => Boolean(dbEnv(key)));
  const driver = explicitDriver || (hasAllMysqlValues ? "mysql" : "json");
  if (hasAnyMysqlValue && !hasAllMysqlValues && !explicitDriver) throw new Error("Partial MySQL configuration detected");
  if (driver !== "mysql" && driver !== "json") throw new Error("DB_DRIVER must be mysql or json");
  if (driver !== "mysql") return;
  for (const key of ["HOST", "NAME", "USER", "PASSWORD"] as const) {
    if (!dbEnv(key)) throw new Error(`Missing required MySQL setting: DB_${key}`);
  }
  if (appEnv === "staging" && /(^|[-_])(prod|production)($|[-_])/i.test(database)) throw new Error("Safety stop: staging cannot connect to a production-labelled database");
  if (appEnv === "production" && /(^|[-_])(staging|stage|test|testing|dev|development|sandbox)($|[-_])/i.test(database)) throw new Error("Safety stop: production cannot connect to a non-production-labelled database");
}


async function mysqlColumnExists(tableName: string, columnName: string) {
  const [rows] = await getPool().execute<ColumnRow[]>(
    `SELECT COLUMN_NAME
       FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = ?
        AND TABLE_NAME = ?
        AND COLUMN_NAME = ?
      LIMIT 1`,
    [dbEnv("NAME"), tableName, columnName],
  );
  return rows.length > 0;
}

async function addMysqlColumnIfMissing(tableName: string, columnName: string, columnSql: string) {
  if (await mysqlColumnExists(tableName, columnName)) return;
  await getPool().execute(`ALTER TABLE ${tableName} ADD COLUMN ${columnSql}`);
}

async function repairMysqlSchemaDrift() {
  // Older cPanel imports can leave mpc_admin_state present but missing the JSON state column.
  // CREATE TABLE IF NOT EXISTS will not repair an existing incomplete table, so perform
  // an idempotent column check before any SELECT state queries run.
  await addMysqlColumnIfMissing("mpc_admin_state", "state", "state LONGTEXT NULL AFTER id");
  await addMysqlColumnIfMissing("mpc_admin_state", "version", "version INT NOT NULL DEFAULT 1 AFTER state");
  await addMysqlColumnIfMissing("mpc_admin_state", "updated_at", "updated_at DATETIME NULL AFTER version");
  await getPool().execute(`UPDATE mpc_admin_state SET state = '{}' WHERE state IS NULL`);
  await getPool().execute(`UPDATE mpc_admin_state SET updated_at = NOW() WHERE updated_at IS NULL`);
}

function env(name: string) {
  const value = process.env[name];
  if (!value && process.env.MPC_DB_DRIVER === "mysql") {
    throw new Error(`Missing required MySQL setting: ${name}`);
  }
  return value || "";
}

export function isMysqlEnabled() {
  const explicit = String(process.env.MPC_DB_DRIVER || process.env.DB_DRIVER || "").toLowerCase();
  if (explicit) return explicit === "mysql";
  return (["HOST", "NAME", "USER", "PASSWORD"] as const).every((key) => Boolean(dbEnv(key)));
}

function getPool() {
  if (pool) return pool;
  assertDatabaseIsolation();
  const host = dbEnv("HOST");
  const user = dbEnv("USER");
  const password = dbEnv("PASSWORD");
  const database = dbEnv("NAME");
  const port = Number(dbEnv("PORT") || 3306);
  const sslEnabled = (dbEnv("SSL") || "false").toLowerCase() === "true";

  pool = mysql.createPool({
    host,
    user,
    password,
    database,
    port,
    waitForConnections: true,
    connectionLimit: Number(dbEnv("CONNECTION_LIMIT") || 5),
    queueLimit: 0,
    charset: "utf8mb4",
    ssl: sslEnabled ? { rejectUnauthorized: false } : undefined,
  });
  return pool;
}

export async function ensureMysqlSchema() {
  if (!isMysqlEnabled()) return;
  if (schemaReady) return schemaReady;
  schemaReady = (async () => {
    const db = getPool();
    await db.execute(`
      CREATE TABLE IF NOT EXISTS mpc_admin_state (
        id VARCHAR(64) NOT NULL PRIMARY KEY,
        state LONGTEXT NOT NULL,
        version INT NOT NULL DEFAULT 1,
        updated_at DATETIME NOT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);
    await repairMysqlSchemaDrift();
    await db.execute(`
      CREATE TABLE IF NOT EXISTS mpc_lead_events (
        id VARCHAR(96) NOT NULL PRIMARY KEY,
        received_at DATETIME NOT NULL,
        category VARCHAR(120) NULL,
        button_type VARCHAR(80) NULL,
        lead_code VARCHAR(120) NULL,
        page VARCHAR(255) NULL,
        contact_name VARCHAR(255) NULL,
        contact_value VARCHAR(255) NULL,
        recommended_product VARCHAR(255) NULL,
        event_payload LONGTEXT NOT NULL,
        user_agent TEXT NULL,
        created_at DATETIME NOT NULL,
        INDEX idx_received_at (received_at),
        INDEX idx_category (category),
        INDEX idx_lead_code (lead_code),
        INDEX idx_recommended_product (recommended_product)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);
    await db.execute(`
      CREATE TABLE IF NOT EXISTS mpc_download_access (
        id VARCHAR(96) NOT NULL PRIMARY KEY,
        access_token VARCHAR(160) NOT NULL UNIQUE,
        customer_name VARCHAR(255) NOT NULL,
        customer_email VARCHAR(255) NOT NULL,
        customer_whatsapp VARCHAR(80) NOT NULL,
        products LONGTEXT NOT NULL,
        status VARCHAR(32) NOT NULL,
        payment_reference VARCHAR(255) NULL,
        internal_notes TEXT NULL,
        created_at DATETIME NOT NULL,
        updated_at DATETIME NOT NULL,
        expires_at DATETIME NULL,
        INDEX idx_access_token (access_token),
        INDEX idx_status (status),
        INDEX idx_customer_email (customer_email)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);
    await db.execute(`
      CREATE TABLE IF NOT EXISTS mpc_download_access_audit (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        access_token VARCHAR(160) NOT NULL,
        download_access_id VARCHAR(96) NULL,
        product_id VARCHAR(160) NULL,
        event_type VARCHAR(80) NOT NULL,
        ip_address VARCHAR(80) NULL,
        user_agent TEXT NULL,
        details LONGTEXT NULL,
        created_at DATETIME NOT NULL,
        INDEX idx_access_token (access_token),
        INDEX idx_product_id (product_id),
        INDEX idx_event_type (event_type),
        INDEX idx_created_at (created_at)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);
  })();
  return schemaReady;
}

function parseJson<T>(value: unknown, fallback: T): T {
  if (value == null) return fallback;
  if (typeof value === "object") return value as T;
  if (typeof value !== "string") return fallback;
  try {
    return JSON.parse(value) as T;
  } catch {
    return fallback;
  }
}

function toMysqlDate(value?: string | null) {
  if (!value) return null;
  const date = new Date(value);
  if (Number.isNaN(date.getTime())) return null;
  return date.toISOString().slice(0, 19).replace("T", " ");
}

function fromMysqlDate(value: unknown) {
  if (!value) return undefined;
  if (value instanceof Date) return value.toISOString();
  const text = String(value);
  if (!text) return undefined;
  return text.includes("T") ? text : new Date(`${text.replace(" ", "T")}Z`).toISOString();
}

type StateRow = RowDataPacket & { state: string; version?: number; updated_at?: Date | string };
type LeadRow = RowDataPacket & { event_payload: string };
type DownloadRow = RowDataPacket & {
  id: string;
  access_token: string;
  customer_name: string;
  customer_email: string;
  customer_whatsapp: string;
  products: string;
  status: DownloadAccess["status"];
  payment_reference?: string | null;
  internal_notes?: string | null;
  created_at: Date | string;
  updated_at: Date | string;
  expires_at?: Date | string | null;
};

async function readMysqlLeadEvents() {
  await ensureMysqlSchema();
  const [rows] = await getPool().execute<LeadRow[]>(
    "SELECT event_payload FROM mpc_lead_events ORDER BY received_at DESC LIMIT 5000",
  );
  return rows.map((row) => parseJson<LeadEvent>(row.event_payload, {} as LeadEvent)).filter((row) => row.id);
}

async function readMysqlDownloadAccess() {
  await ensureMysqlSchema();
  const [rows] = await getPool().execute<DownloadRow[]>(
    `SELECT id, access_token, customer_name, customer_email, customer_whatsapp, products, status,
            payment_reference, internal_notes, created_at, updated_at, expires_at
       FROM mpc_download_access
      ORDER BY updated_at DESC`,
  );
  return rows.map((row) => ({
    id: row.id,
    accessToken: row.access_token,
    customerName: row.customer_name,
    customerEmail: row.customer_email,
    customerWhatsapp: row.customer_whatsapp,
    products: parseJson<DownloadAccess["products"]>(row.products, []),
    status: row.status,
    paymentReference: row.payment_reference || undefined,
    internalNotes: row.internal_notes || undefined,
    createdAt: fromMysqlDate(row.created_at) || new Date().toISOString(),
    updatedAt: fromMysqlDate(row.updated_at) || new Date().toISOString(),
    expiresAt: fromMysqlDate(row.expires_at),
  } satisfies DownloadAccess));
}

export async function readMysqlState() {
  if (!isMysqlEnabled()) return null;
  await ensureMysqlSchema();
  const [rows] = await getPool().execute<StateRow[]>("SELECT state FROM mpc_admin_state WHERE id = ? LIMIT 1", ["main"]);
  const parsed = rows[0]?.state ? parseJson<Partial<AdminState>>(rows[0].state, {}) : {};
  const [leadEvents, downloadAccess] = await Promise.all([readMysqlLeadEvents(), readMysqlDownloadAccess()]);
  return {
    ...parsed,
    leadEvents: leadEvents.length ? leadEvents : parsed.leadEvents,
    downloadAccess: downloadAccess.length ? downloadAccess : parsed.downloadAccess,
  } as Partial<AdminState>;
}

async function syncLeadEvents(events: LeadEvent[]) {
  await ensureMysqlSchema();
  const db = getPool();
  const cleanEvents = events.slice(0, 5000);
  if (cleanEvents.length === 0) {
    return;
  }
  for (const event of cleanEvents) {
    await db.execute(
      `INSERT INTO mpc_lead_events
        (id, received_at, category, button_type, lead_code, page, contact_name, contact_value, recommended_product, event_payload, user_agent, created_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
       ON DUPLICATE KEY UPDATE
        received_at = VALUES(received_at), category = VALUES(category), button_type = VALUES(button_type),
        lead_code = VALUES(lead_code), page = VALUES(page), contact_name = VALUES(contact_name),
        contact_value = VALUES(contact_value), recommended_product = VALUES(recommended_product),
        event_payload = VALUES(event_payload), user_agent = VALUES(user_agent)`,
      [
        event.id,
        toMysqlDate(event.receivedAt) || toMysqlDate(new Date().toISOString()),
        event.category || null,
        event.buttonType || null,
        event.leadCode || null,
        event.page || null,
        event.contactName || null,
        event.contactValue || null,
        event.recommendedProduct || null,
        JSON.stringify(event),
        event.userAgent || null,
        toMysqlDate(new Date().toISOString()),
      ],
    );
  }
  // Do not hard-delete leads during a full admin-state save. A stale admin tab must never remove newer operational rows.
}

async function syncDownloadAccess(items: DownloadAccess[]) {
  await ensureMysqlSchema();
  const db = getPool();
  if (items.length === 0) {
    return;
  }
  for (const item of items) {
    await db.execute(
      `INSERT INTO mpc_download_access
        (id, access_token, customer_name, customer_email, customer_whatsapp, products, status, payment_reference, internal_notes, created_at, updated_at, expires_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
       ON DUPLICATE KEY UPDATE
        access_token = VALUES(access_token), customer_name = VALUES(customer_name), customer_email = VALUES(customer_email),
        customer_whatsapp = VALUES(customer_whatsapp), products = VALUES(products), status = VALUES(status),
        payment_reference = VALUES(payment_reference), internal_notes = VALUES(internal_notes),
        updated_at = VALUES(updated_at), expires_at = VALUES(expires_at)`,
      [
        item.id,
        item.accessToken,
        item.customerName,
        item.customerEmail,
        item.customerWhatsapp,
        JSON.stringify(item.products || []),
        item.status,
        item.paymentReference || null,
        item.internalNotes || null,
        toMysqlDate(item.createdAt) || toMysqlDate(new Date().toISOString()),
        toMysqlDate(item.updatedAt) || toMysqlDate(new Date().toISOString()),
        toMysqlDate(item.expiresAt),
      ],
    );
  }
  // Do not hard-delete customer access links during a full admin-state save. Pause links instead of removing rows.
}

export async function writeMysqlState(state: AdminState) {
  if (!isMysqlEnabled()) return null;
  await ensureMysqlSchema();
  const clean: AdminState = { ...state, updatedAt: new Date().toISOString(), version: state.version || 1 };
  await getPool().execute(
    `INSERT INTO mpc_admin_state (id, state, version, updated_at)
     VALUES (?, ?, ?, ?)
     ON DUPLICATE KEY UPDATE state = VALUES(state), version = VALUES(version), updated_at = VALUES(updated_at)`,
    ["main", JSON.stringify(clean), clean.version, toMysqlDate(clean.updatedAt)],
  );
  await Promise.all([
    syncLeadEvents(clean.leadEvents || []),
    syncDownloadAccess(clean.downloadAccess || []),
  ]);
  return clean;
}

export async function recordMysqlDownloadAudit(params: {
  accessToken: string;
  downloadAccessId?: string | null;
  productId?: string | null;
  eventType: string;
  ipAddress?: string | null;
  userAgent?: string | null;
  details?: Record<string, unknown> | null;
}) {
  if (!isMysqlEnabled()) return;
  await ensureMysqlSchema();
  await getPool().execute(
    `INSERT INTO mpc_download_access_audit
      (access_token, download_access_id, product_id, event_type, ip_address, user_agent, details, created_at)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
    [
      params.accessToken,
      params.downloadAccessId || null,
      params.productId || null,
      params.eventType,
      params.ipAddress || null,
      params.userAgent || null,
      params.details ? JSON.stringify(params.details) : null,
      toMysqlDate(new Date().toISOString()),
    ],
  );
}