src/utils/migrate.js

/**
 * Safe schema migrations — run before server start.
 * Each ALTER TABLE is caught on errno 1060 (duplicate column) so
 * it is safe to run on every boot without wiping data.
 */
const fs = require('fs');
const path = require('path');
const db = require('../config/db');

const DATA_DIR = process.env.DB_DATA_DIR || '/data/wifiplatform/mysql';
const DB_NAME = process.env.DB_NAME || 'wifiplatform';
const DB_DIR = path.join(DATA_DIR, DB_NAME);
const QUARANTINE_DIR = path.join('/data/wifiplatform/quarantine', 'mysql-table-artifacts');
const REPAIRABLE_TABLES = [
  'access_tokens',
  'payments',
  'device_portal_settings',
  'device_portal_payment_options',
  'token_compensations',
  'token_deauth_events',
  'client_notification_preferences',
];

const migrations = [
  // 001 — SSID / WLAN fields on devices
  `ALTER TABLE devices ADD COLUMN ssid_name     VARCHAR(64) DEFAULT NULL AFTER device_password`,
  `ALTER TABLE devices ADD COLUMN ssid_password VARCHAR(64) DEFAULT NULL AFTER ssid_name`,
  `ALTER TABLE devices ADD COLUMN omada_wlan_id VARCHAR(64) DEFAULT NULL AFTER omada_portal_id`,
  `ALTER TABLE devices ADD COLUMN omada_ssid_id VARCHAR(64) DEFAULT NULL AFTER omada_wlan_id`,

  // 002 — Portal customisation settings per device
  `CREATE TABLE IF NOT EXISTS device_portal_settings (
    device_id     INT UNSIGNED NOT NULL PRIMARY KEY,
    display_name  VARCHAR(128) DEFAULT NULL,
    welcome_text  VARCHAR(255) DEFAULT NULL,
    logo_url      VARCHAR(512) DEFAULT NULL,
    support_phone VARCHAR(20)  DEFAULT NULL,
    primary_color VARCHAR(7)   DEFAULT NULL,
    bg_color      VARCHAR(7)   DEFAULT NULL,
    FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4`,

  // 003 — Ensure nullable columns on device_portal_settings (fix pre-migration tables)
  `ALTER TABLE device_portal_settings MODIFY COLUMN bg_color VARCHAR(7) DEFAULT NULL`,
  `ALTER TABLE device_portal_settings MODIFY COLUMN primary_color VARCHAR(7) DEFAULT NULL`,
  `ALTER TABLE device_portal_settings ADD COLUMN manual_sales_enabled TINYINT(1) NOT NULL DEFAULT 0 AFTER bg_color`,
  `ALTER TABLE device_portal_settings ADD COLUMN portal_payment_mode VARCHAR(24) NOT NULL DEFAULT 'both' AFTER manual_sales_enabled`,

  // 003b — Uploaded per-device portal payment options
  `CREATE TABLE IF NOT EXISTS device_portal_payment_options (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    device_id INT UNSIGNED NOT NULL,
    provider_name VARCHAR(100) NOT NULL,
    image_path VARCHAR(255) NOT NULL,
    display_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_portal_payment_device (device_id),
    CONSTRAINT fk_portal_payment_options_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4`,

  // 004 — Fix any invalid access_tokens.status values before modifying the ENUM
  `UPDATE access_tokens SET status = 'active' WHERE status NOT IN ('unused','active','expired','revoked')`,
  `ALTER TABLE access_tokens MODIFY COLUMN status ENUM('unused','active','expired','revoked') NOT NULL DEFAULT 'unused'`,

  // 005 — Fix any invalid payments.status values before modifying the ENUM
  `UPDATE payments SET status = 'pending' WHERE status NOT IN ('pending','completed','failed','refunded','voided')`,
  `ALTER TABLE payments MODIFY COLUMN status ENUM('pending','completed','failed','refunded','voided') NOT NULL DEFAULT 'pending'`,

  // 006 — Audit trail for tenant/admin customer compensation extensions
  `CREATE TABLE IF NOT EXISTS token_compensations (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    token_id          BIGINT UNSIGNED NOT NULL,
    payment_id        BIGINT UNSIGNED NOT NULL,
    client_id         INT UNSIGNED NOT NULL,
    device_id         INT UNSIGNED NOT NULL,
    actor_type        ENUM('tenant','admin') NOT NULL,
    actor_client_id   INT UNSIGNED DEFAULT NULL,
    granted_seconds   INT UNSIGNED NOT NULL,
    old_expires_at    TIMESTAMP NULL DEFAULT NULL,
    new_expires_at    TIMESTAMP NOT NULL,
    reason            VARCHAR(255) DEFAULT NULL,
    created_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_token_comp_token (token_id),
    KEY idx_token_comp_client (client_id),
    KEY idx_token_comp_payment (payment_id),
    CONSTRAINT fk_token_comp_token FOREIGN KEY (token_id) REFERENCES access_tokens(id) ON DELETE CASCADE,
    CONSTRAINT fk_token_comp_payment FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE CASCADE,
    CONSTRAINT fk_token_comp_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    CONSTRAINT fk_token_comp_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4`,

  // 007 — Tenant SMS notification preferences
  `CREATE TABLE IF NOT EXISTS client_notification_preferences (
    client_id          INT UNSIGNED NOT NULL PRIMARY KEY,
    device_renewal     TINYINT(1) NOT NULL DEFAULT 1,
    device_suspended   TINYINT(1) NOT NULL DEFAULT 1,
    device_paused      TINYINT(1) NOT NULL DEFAULT 1,
    billing_reminder   TINYINT(1) NOT NULL DEFAULT 1,
    payout_completed   TINYINT(1) NOT NULL DEFAULT 1,
    payout_failed      TINYINT(1) NOT NULL DEFAULT 1,
    trial_granted      TINYINT(1) NOT NULL DEFAULT 1,
    purchase_alerts    TINYINT(1) NOT NULL DEFAULT 1,
    system_alerts      TINYINT(1) NOT NULL DEFAULT 1,
    updated_at         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_client_notification_preferences_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4`,

  // 008 — Add in-app alert notification preferences to existing installs
  `ALTER TABLE client_notification_preferences ADD COLUMN purchase_alerts TINYINT(1) NOT NULL DEFAULT 1 AFTER trial_granted`,
  `ALTER TABLE client_notification_preferences ADD COLUMN system_alerts TINYINT(1) NOT NULL DEFAULT 1 AFTER purchase_alerts`,

  // 009 — Tenant direct-payment settings on clients
  `ALTER TABLE clients ADD COLUMN payment_display_name VARCHAR(100) DEFAULT NULL AFTER phone`,
  `ALTER TABLE clients ADD COLUMN payment_phone VARCHAR(20) DEFAULT NULL AFTER payment_display_name`,
  `ALTER TABLE clients ADD COLUMN payment_provider VARCHAR(32) DEFAULT NULL AFTER payment_phone`,
  `ALTER TABLE clients ADD COLUMN payment_instructions VARCHAR(255) DEFAULT NULL AFTER payment_provider`,
  `ALTER TABLE clients ADD COLUMN payment_qr_image_url VARCHAR(512) DEFAULT NULL AFTER payment_instructions`,
  `ALTER TABLE clients ADD COLUMN manual_sales_enabled TINYINT(1) NOT NULL DEFAULT 0 AFTER payment_qr_image_url`,
  `ALTER TABLE clients ADD COLUMN portal_payment_mode VARCHAR(24) NOT NULL DEFAULT 'both' AFTER manual_sales_enabled`,

  // 010 — Manual/direct payment tracking fields on payments
  `ALTER TABLE payments MODIFY COLUMN phone VARCHAR(20) NULL`,
  `ALTER TABLE payments MODIFY COLUMN phone_provider ENUM('mpesa','selcom','airtel','tigo','halopesa','bank','other') NOT NULL DEFAULT 'mpesa'`,
  `ALTER TABLE payments ADD COLUMN payment_channel ENUM('snippe','tenant_direct_manual') NOT NULL DEFAULT 'snippe' AFTER phone_provider`,
  `ALTER TABLE payments ADD COLUMN verification_source ENUM('webhook','tenant_confirmed') DEFAULT NULL AFTER payment_channel`,
  `ALTER TABLE payments ADD COLUMN counts_toward_balance TINYINT(1) NOT NULL DEFAULT 1 AFTER verification_source`,
  `ALTER TABLE payments ADD COLUMN confirmed_by_client_id INT UNSIGNED DEFAULT NULL AFTER counts_toward_balance`,
  `ALTER TABLE payments ADD COLUMN confirmed_at TIMESTAMP NULL DEFAULT NULL AFTER confirmed_by_client_id`,
  `ALTER TABLE payments ADD COLUMN notes VARCHAR(255) DEFAULT NULL AFTER confirmed_at`,

  // 011 - Track successful Omada deauthorization without altering access_tokens
  `CREATE TABLE IF NOT EXISTS token_deauth_events (
    token_id      BIGINT UNSIGNED NOT NULL PRIMARY KEY,
    client_id     INT UNSIGNED NOT NULL,
    device_id     INT UNSIGNED NOT NULL,
    omada_site_id VARCHAR(64) NOT NULL,
    locked_mac    VARCHAR(17) NOT NULL,
    status        ENUM('succeeded') NOT NULL DEFAULT 'succeeded',
    succeeded_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_token_deauth_mac (device_id, locked_mac),
    KEY idx_token_deauth_site (omada_site_id),
    CONSTRAINT fk_token_deauth_token FOREIGN KEY (token_id) REFERENCES access_tokens(id) ON DELETE CASCADE,
    CONSTRAINT fk_token_deauth_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    CONSTRAINT fk_token_deauth_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4`,

];

function summarizeSql(sql) {
  return sql.replace(/\s+/g, ' ').trim().slice(0, 160);
}

function extractTargetTable(sql) {
  return (
    sql.match(/ALTER TABLE\s+`?([a-zA-Z0-9_]+)`?/i)?.[1] ||
    sql.match(/CREATE TABLE(?: IF NOT EXISTS)?\s+`?([a-zA-Z0-9_]+)`?/i)?.[1] ||
    null
  );
}

function extractAddColumnName(sql) {
  return sql.match(/ADD COLUMN\s+`?([a-zA-Z0-9_]+)`?/i)?.[1] || null;
}

function extractModifyColumnName(sql) {
  return sql.match(/MODIFY COLUMN\s+`?([a-zA-Z0-9_]+)`?/i)?.[1] || null;
}

async function getColumnMetadata(table, column) {
  const rows = await db.query(
    `SELECT COLUMN_NAME, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_TYPE
     FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND COLUMN_NAME = ?
     LIMIT 1`,
    [DB_NAME, table, column]
  );
  return rows[0] || null;
}

async function getCreateTableSql(table) {
  const rows = await db.query(`SHOW CREATE TABLE \`${table}\``);
  return rows[0]?.['Create Table'] || null;
}

async function isMigrationAlreadySatisfied(sql, table) {
  const addColumn = extractAddColumnName(sql);
  if (addColumn) {
    const column = await getColumnMetadata(table, addColumn);
    return Boolean(column);
  }

  const modifyColumn = extractModifyColumnName(sql);
  if (modifyColumn) {
    const column = await getColumnMetadata(table, modifyColumn);
    if (!column) return false;

    if (/DEFAULT NULL/i.test(sql) && column.IS_NULLABLE === 'YES') {
      return true;
    }

    const enumMatch = sql.match(/MODIFY COLUMN\s+`?[a-zA-Z0-9_]+`?\s+ENUM\(([^)]+)\)\s+NOT NULL DEFAULT\s+'([^']+)'/i);
    if (enumMatch) {
      const expectedType = `enum(${enumMatch[1]})`.toLowerCase().replace(/\s+/g, '');
      const actualType = String(column.COLUMN_TYPE || '').toLowerCase().replace(/\s+/g, '');
      const expectedDefault = enumMatch[2];
      if (actualType === expectedType && String(column.COLUMN_DEFAULT || '') === expectedDefault) {
        return true;
      }

      const createSql = await getCreateTableSql(table);
      if (createSql) {
        const normalizedCreateSql = createSql.toLowerCase().replace(/\s+/g, ' ');
        const normalizedColumnFragment =
          `\`${modifyColumn}\` ${`enum(${enumMatch[1]})`.toLowerCase()} not null default '${expectedDefault.toLowerCase()}'`;
        if (normalizedCreateSql.includes(normalizedColumnFragment)) {
          return true;
        }
      }
    }

    const varcharNullMatch = sql.match(/MODIFY COLUMN\s+`?[a-zA-Z0-9_]+`?\s+VARCHAR\((\d+)\)\s+NULL/i);
    if (varcharNullMatch) {
      return column.IS_NULLABLE === 'YES';
    }
  }

  return false;
}

async function logMariaDiagnostics(table, sql, err) {
  console.error('[migrate] Diagnostic SQL:', summarizeSql(sql));
  console.error('[migrate] Diagnostic error meta:', JSON.stringify({
    errno: err?.errno ?? null,
    code: err?.code ?? null,
    sqlState: err?.sqlState ?? null,
    message: err?.message ?? null,
    dbDir: DB_DIR,
    dbDirExists: fs.existsSync(DB_DIR),
    targetTable: table,
  }));

  if (!table || !fs.existsSync(DB_DIR)) return;

  try {
    const matchingEntries = fs.readdirSync(DB_DIR)
      .filter(entry => entry === table || entry.startsWith(`${table}.`))
      .map(entry => {
        const fullPath = path.join(DB_DIR, entry);
        const stat = fs.statSync(fullPath);
        return {
          name: entry,
          type: stat.isDirectory() ? 'directory' : 'file',
          size: stat.size,
        };
      });

    console.error('[migrate] Matching filesystem entries:', JSON.stringify(matchingEntries));
  } catch (fsErr) {
    console.error('[migrate] Failed to inspect filesystem entries:', fsErr.message);
  }

  try {
    const escapedTable = String(table).replace(/'/g, "''");
    const tableRows = await db.query(`SHOW FULL TABLES LIKE '${escapedTable}'`);
    console.error('[migrate] SHOW FULL TABLES LIKE result:', JSON.stringify(tableRows));
  } catch (showErr) {
    console.error('[migrate] SHOW FULL TABLES LIKE failed:', showErr.message);
  }

  try {
    const createRows = await db.query(`SHOW CREATE TABLE \`${table}\``);
    console.error('[migrate] SHOW CREATE TABLE result:', JSON.stringify(createRows));
  } catch (createErr) {
    console.error('[migrate] SHOW CREATE TABLE failed:', createErr.message);
  }

  const addColumn = extractAddColumnName(sql);
  const modifyColumn = extractModifyColumnName(sql);
  const targetColumn = addColumn || modifyColumn;
  if (!targetColumn) return;

  try {
    const columnRows = await db.query(
      `SELECT COLUMN_NAME, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_TYPE
       FROM information_schema.COLUMNS
       WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND COLUMN_NAME = ?
       LIMIT 1`,
      [DB_NAME, table, targetColumn]
    );
    console.error('[migrate] information_schema column result:', JSON.stringify(columnRows));
  } catch (columnErr) {
    console.error('[migrate] information_schema column lookup failed:', columnErr.message);
  }
}

function repairBlockingMariaArtifacts() {
  if (!fs.existsSync(DB_DIR)) return false;

  const quarantineRoot = path.join(QUARANTINE_DIR, String(Date.now()));
  let repaired = false;

  for (const table of REPAIRABLE_TABLES) {
    const candidates = [path.join(DB_DIR, table)];

    for (const entry of fs.readdirSync(DB_DIR)) {
      if (entry === table || entry.startsWith(`${table}.`)) {
        candidates.push(path.join(DB_DIR, entry));
      }
    }

    for (const candidate of new Set(candidates)) {
      if (!fs.existsSync(candidate)) continue;

      let stat;
      try {
        stat = fs.statSync(candidate);
      } catch {
        continue;
      }

      if (!stat.isDirectory()) continue;

      fs.mkdirSync(quarantineRoot, { recursive: true });
      const destination = path.join(quarantineRoot, path.basename(candidate));
      fs.renameSync(candidate, destination);
      console.warn(`[migrate] Quarantined blocking MariaDB artifact: ${candidate}`);
      repaired = true;
    }
  }

  return repaired;
}

async function runMigrations() {
  console.log('[migrate] Running schema migrations...');
  for (const sql of migrations) {
    const targetTable = extractTargetTable(sql);
    try {
      await db.query(sql);
      // Extract column name from the SQL for a readable log line
      const col = sql.match(/(?:ADD|MODIFY) COLUMN (\w+)/i)?.[1] ?? sql.slice(0, 60);
      console.log(`[migrate] Applied: ${col}`);
    } catch (err) {
      if (targetTable && REPAIRABLE_TABLES.includes(targetTable)) {
        await logMariaDiagnostics(targetTable, sql, err);
      }
      if (err.errno === 1060 || err.errno === 1091) {
        // Column already exists — nothing to do
      } else if ((err.errno === 1005 || err.errno === 21) && targetTable && await isMigrationAlreadySatisfied(sql, targetTable)) {
        const col = extractAddColumnName(sql) || extractModifyColumnName(sql) || targetTable;
        console.warn(`[migrate] Skipping already-satisfied migration after MariaDB alter failure: ${col}`);
      } else if (err.errno === 21 && repairBlockingMariaArtifacts()) {
        console.warn(`[migrate] Retrying after artifact repair for table ${targetTable}`);
        await db.query(sql);
        const col = sql.match(/(?:ADD|MODIFY) COLUMN (\w+)/i)?.[1] ?? sql.slice(0, 60);
        console.log(`[migrate] Applied after repair: ${col}`);
      } else {
        console.error('[migrate] Migration failed:', err.message);
        throw err;
      }
    }
  }
  console.log('[migrate] Done.');
}

module.exports = { runMigrations };