Spaces:

Mbonea
/

WifiBiz

Runtime error

Migrate production app to v2 Omada workflow

9b8d6f0 5 months ago

19.8 kB

/**
 * 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_ROOT = process.env.DATA_ROOT || '/data/wifiplatform';
const DATA_DIR = process.env.DB_DATA_DIR || path.join(DATA_ROOT, 'mysql');
const DB_NAME = process.env.DB_NAME || 'wifiplatform';
const DB_DIR = path.join(DATA_DIR, DB_NAME);
const QUARANTINE_DIR = path.join(DATA_ROOT, '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',
  'client_payment_credentials',
];

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`,
  `ALTER TABLE devices ADD COLUMN omada_imported_portal_page_id VARCHAR(64) DEFAULT NULL AFTER omada_ssid_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`,

  // 012 - Omada v2 voucher identifiers; additive for backward compatibility
  `ALTER TABLE wifi_plans ADD COLUMN omada_voucher_group_id VARCHAR(64) DEFAULT NULL AFTER is_active`,
  `ALTER TABLE wifi_plans ADD COLUMN omada_voucher_group_synced_at TIMESTAMP NULL DEFAULT NULL AFTER omada_voucher_group_id`,
  `ALTER TABLE wifi_plans ADD COLUMN omada_voucher_group_error VARCHAR(255) DEFAULT NULL AFTER omada_voucher_group_synced_at`,
  `ALTER TABLE access_tokens ADD COLUMN omada_voucher_id VARCHAR(64) DEFAULT NULL AFTER code`,
  `ALTER TABLE access_tokens ADD COLUMN omada_voucher_group_id VARCHAR(64) DEFAULT NULL AFTER omada_voucher_id`,

  // 013 - Tenant-owned Snippe credentials and payment-source markers
  `CREATE TABLE IF NOT EXISTS client_payment_credentials (
    client_id             INT UNSIGNED NOT NULL,
    provider              VARCHAR(32) NOT NULL DEFAULT 'snippe',
    is_enabled            TINYINT(1) NOT NULL DEFAULT 0,
    fallback_to_platform  TINYINT(1) NOT NULL DEFAULT 1,
    api_key               TEXT DEFAULT NULL,
    webhook_secret        TEXT DEFAULT NULL,
    credential_status     VARCHAR(32) NOT NULL DEFAULT 'untested',
    last_test_message     VARCHAR(255) DEFAULT NULL,
    last_tested_at        TIMESTAMP NULL DEFAULT NULL,
    updated_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (client_id, provider),
    CONSTRAINT fk_client_payment_credentials_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4`,
  `ALTER TABLE payments ADD COLUMN provider_account_type ENUM('platform','tenant') NOT NULL DEFAULT 'platform' AFTER payment_channel`,
  `ALTER TABLE payments ADD COLUMN provider_account_client_id INT UNSIGNED DEFAULT NULL AFTER provider_account_type`,
  `DROP TRIGGER IF EXISTS trg_payment_completed`,
  `CREATE TRIGGER trg_payment_completed
BEFORE UPDATE ON payments
FOR EACH ROW
BEGIN
  DECLARE v_new_balance DECIMAL(12,2);
  IF OLD.status = 'pending' AND NEW.status = 'completed' THEN
    IF NEW.counts_toward_balance = 1 THEN
      SET NEW.snippe_fee        = ROUND(NEW.gross_amount * 0.005, 2);
      SET NEW.commission_amount = ROUND(NEW.gross_amount * NEW.commission_rate, 2);
      SET NEW.client_credit     = NEW.gross_amount - NEW.snippe_fee - NEW.commission_amount;
    ELSE
      SET NEW.snippe_fee        = 0.00;
      SET NEW.commission_amount = 0.00;
      SET NEW.client_credit     = 0.00;
    END IF;
    SET NEW.completed_at = CURRENT_TIMESTAMP;
    IF NEW.counts_toward_balance = 1 THEN
      UPDATE clients
      SET balance      = balance + NEW.client_credit,
          total_earned = total_earned + NEW.client_credit
      WHERE id = NEW.client_id;
    END IF;
    IF NEW.counts_toward_balance = 1 THEN
      SET v_new_balance = (SELECT balance FROM clients WHERE id = NEW.client_id);
      INSERT INTO balance_ledger
        (client_id, type, amount, balance_after, reference_type, reference_id, description)
      VALUES
        (NEW.client_id, 'wifi_sale', NEW.client_credit, v_new_balance, 'payment', NEW.id,
         CONCAT('WiFi sale: ', NEW.gross_amount, ' TZS (fee: ', NEW.snippe_fee, ', comm: ', NEW.commission_amount, ')'));
    END IF;
  END IF;
END`,

];

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 };

	/**
	* 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_ROOT = process.env.DATA_ROOT \|\| '/data/wifiplatform';
	const DATA_DIR = process.env.DB_DATA_DIR \|\| path.join(DATA_ROOT, 'mysql');
	const DB_NAME = process.env.DB_NAME \|\| 'wifiplatform';
	const DB_DIR = path.join(DATA_DIR, DB_NAME);
	const QUARANTINE_DIR = path.join(DATA_ROOT, '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',
	'client_payment_credentials',
	];

	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`,
	`ALTER TABLE devices ADD COLUMN omada_imported_portal_page_id VARCHAR(64) DEFAULT NULL AFTER omada_ssid_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`,

	// 012 - Omada v2 voucher identifiers; additive for backward compatibility
	`ALTER TABLE wifi_plans ADD COLUMN omada_voucher_group_id VARCHAR(64) DEFAULT NULL AFTER is_active`,
	`ALTER TABLE wifi_plans ADD COLUMN omada_voucher_group_synced_at TIMESTAMP NULL DEFAULT NULL AFTER omada_voucher_group_id`,
	`ALTER TABLE wifi_plans ADD COLUMN omada_voucher_group_error VARCHAR(255) DEFAULT NULL AFTER omada_voucher_group_synced_at`,
	`ALTER TABLE access_tokens ADD COLUMN omada_voucher_id VARCHAR(64) DEFAULT NULL AFTER code`,
	`ALTER TABLE access_tokens ADD COLUMN omada_voucher_group_id VARCHAR(64) DEFAULT NULL AFTER omada_voucher_id`,

	// 013 - Tenant-owned Snippe credentials and payment-source markers
	`CREATE TABLE IF NOT EXISTS client_payment_credentials (
	client_id INT UNSIGNED NOT NULL,
	provider VARCHAR(32) NOT NULL DEFAULT 'snippe',
	is_enabled TINYINT(1) NOT NULL DEFAULT 0,
	fallback_to_platform TINYINT(1) NOT NULL DEFAULT 1,
	api_key TEXT DEFAULT NULL,
	webhook_secret TEXT DEFAULT NULL,
	credential_status VARCHAR(32) NOT NULL DEFAULT 'untested',
	last_test_message VARCHAR(255) DEFAULT NULL,
	last_tested_at TIMESTAMP NULL DEFAULT NULL,
	updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (client_id, provider),
	CONSTRAINT fk_client_payment_credentials_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4`,
	`ALTER TABLE payments ADD COLUMN provider_account_type ENUM('platform','tenant') NOT NULL DEFAULT 'platform' AFTER payment_channel`,
	`ALTER TABLE payments ADD COLUMN provider_account_client_id INT UNSIGNED DEFAULT NULL AFTER provider_account_type`,
	`DROP TRIGGER IF EXISTS trg_payment_completed`,
	`CREATE TRIGGER trg_payment_completed
	BEFORE UPDATE ON payments
	FOR EACH ROW
	BEGIN
	DECLARE v_new_balance DECIMAL(12,2);

	IF OLD.status = 'pending' AND NEW.status = 'completed' THEN
	IF NEW.counts_toward_balance = 1 THEN
	SET NEW.snippe_fee = ROUND(NEW.gross_amount * 0.005, 2);
	SET NEW.commission_amount = ROUND(NEW.gross_amount * NEW.commission_rate, 2);
	SET NEW.client_credit = NEW.gross_amount - NEW.snippe_fee - NEW.commission_amount;
	ELSE
	SET NEW.snippe_fee = 0.00;
	SET NEW.commission_amount = 0.00;
	SET NEW.client_credit = 0.00;
	END IF;
	SET NEW.completed_at = CURRENT_TIMESTAMP;

	IF NEW.counts_toward_balance = 1 THEN
	UPDATE clients
	SET balance = balance + NEW.client_credit,
	total_earned = total_earned + NEW.client_credit
	WHERE id = NEW.client_id;
	END IF;

	IF NEW.counts_toward_balance = 1 THEN
	SET v_new_balance = (SELECT balance FROM clients WHERE id = NEW.client_id);

	INSERT INTO balance_ledger
	(client_id, type, amount, balance_after, reference_type, reference_id, description)
	VALUES
	(NEW.client_id, 'wifi_sale', NEW.client_credit, v_new_balance, 'payment', NEW.id,
	CONCAT('WiFi sale: ', NEW.gross_amount, ' TZS (fee: ', NEW.snippe_fee, ', comm: ', NEW.commission_amount, ')'));
	END IF;
	END IF;
	END`,

	];

	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 };