<?php
/**
 * lighthouse-gather.php
 *
 * CLI gatherer for the WMD SED Lighthouse Monitor.
 * Scans domains in the "Lighthouse Domains" WMD category using lighthouse-scan.js
 * and writes results to custom columns on the domain table.
 *
 * CLI ONLY — exits immediately if run in a browser.
 *
 * Usage:
 *   php lighthouse-gather.php
 *   php lighthouse-gather.php --batch=2
 *   php lighthouse-gather.php --domain=example.com
 *   php lighthouse-gather.php --device=mobile
 *   php lighthouse-gather.php --force
 *   php lighthouse-gather.php --dry-run
 *   php lighthouse-gather.php --debug
 *   php lighthouse-gather.php --setup-columns
 */

function findLoader(): string {
    $dir = __DIR__;
    for ($i = 0; $i < 6; $i++) {
        $candidate = $dir . '/lib/php/loader.php';
        if (file_exists($candidate)) return $candidate;
        $dir = dirname($dir);
    }
    die("Could not locate loader.php — ensure this tool is installed within the WMD SED directory tree.\n");
}
require findLoader();

use CodePunch\Base\Util as UTIL;
use CodePunch\Base\Text as TEXT;
use CodePunch\DB\Audit  as AUDIT;

// =============================================================================
// CONSTANTS — adjust per installation
// =============================================================================

/** Subfolder name under UTIL::get_log_folder_path() where Node scripts live. */
const LIGHTHOUSE_NODE_FOLDER = 'websites';

/** Subfolder name under UTIL::get_log_folder_path() where JSON results are stored. */
const LIGHTHOUSE_OUTPUT_FOLDER = 'lh-data';

/** WMD category name. Only domains in this category are scanned. Case-sensitive. */
const LIGHTHOUSE_DEFAULT_CATEGORY = 'Lighthouse Test Domains';

/**
 * Device mode: 'mobile', 'desktop', or 'both'.
 * 'both' runs two scans per domain and stores separate scores for each mode.
 */
const LIGHTHOUSE_DEVICE = 'both';

/** Domains with any score in LIGHTHOUSE_FAIL_CATEGORIES below this are marked 'fail'. */
const LIGHTHOUSE_FAIL_THRESHOLD = 50;

/**
 * Categories that count toward pass/fail.
 */
const LIGHTHOUSE_FAIL_CATEGORIES = ['performance', 'accessibility', 'best-practices', 'seo'];

/** Skip domains scanned within this many days (0 = always rescan). */
const LIGHTHOUSE_SKIP_RECENT_DAYS = 30;

/** Puppeteer navigation timeout in milliseconds. Lighthouse scans are heavy — keep generous. */
const LIGHTHOUSE_TIMEOUT_MS = 60000;

/** Lock file name under UTIL::get_log_folder_path(). Prevents concurrent runs. */
const LIGHTHOUSE_LOCK_FILE = 'lighthouse-gather.lock';

/**
 * Custom column name holding the preferred protocol ('http' or 'https') per domain.
 * Leave empty to use automatic protocol resolution (https first, http fallback).
 */
const LIGHTHOUSE_PROTOCOL_COLUMN = '';

// Category name → column suffix mapping
const CATEGORY_COLUMN_MAP = [
    'performance'    => 'perf',
    'seo'            => 'seo',
    'accessibility'  => 'a11y',
    'best-practices' => 'bp',
];

// Custom column definitions: [name, type, label, length|null, gridview]
const LIGHTHOUSE_COLUMNS = [
    ['lh_perf_mobile',  'integer', 'LH Perf Mobile',            null, 1],
    ['lh_seo_mobile',   'integer', 'LH SEO Mobile',             null, 1],
    ['lh_a11y_mobile',  'integer', 'LH A11y Mobile',            null, 1],
    ['lh_bp_mobile',    'integer', 'LH Best Practices Mobile',  null, 1],
    ['lh_perf_desktop', 'integer', 'LH Perf Desktop',           null, 1],
    ['lh_seo_desktop',  'integer', 'LH SEO Desktop',            null, 1],
    ['lh_a11y_desktop', 'integer', 'LH A11y Desktop',           null, 1],
    ['lh_bp_desktop',   'integer', 'LH Best Practices Desktop', null, 1],
    ['lh_status',       'string',  'LH Status',                  20,  1],
    ['lh_scanned_at',   'datetime','LH Scanned At',             null, 1],
];

// =============================================================================
// OUTPUT HELPERS
// =============================================================================

function out_info(string $msg): void  { UTIL::print("[INFO]  $msg"); }
function out_warn(string $msg): void  { UTIL::print("[WARN]  $msg"); }
function out_error(string $msg): void { UTIL::print("[ERROR] $msg"); }
function out_ok(string $msg): void    { UTIL::print("[OK]    $msg"); }
function out_skip(string $msg): void  { UTIL::print("[SKIP]  $msg"); }
function out_divider(): void          { UTIL::print(str_repeat('=', 72)); }
function out_debug(string $msg, bool $debug): void {
    if ($debug) UTIL::print("[DEBUG] $msg");
}

// =============================================================================
// CLI ONLY GUARD
// =============================================================================

if (!UTIL::is_cli()) {
    UTIL::print("This tool can only be run from the command line.");
    exit;
}

// =============================================================================
// PARSE CLI ARGUMENTS
// =============================================================================

UTIL::parse_request_data_in_cli();
$rd = UTIL::get_unsafe_request_data_array();

$debug        = false;
$dryRun       = false;
$force        = false;
$setupColumns = false;

foreach (($GLOBALS['argv'] ?? []) as $arg) {
    $a = strtolower(ltrim($arg, '-'));
    if ($a === 'debug'         || strpos($a, 'debug=')         === 0) $debug        = true;
    if ($a === 'dry-run'       || strpos($a, 'dry-run=')       === 0) $dryRun       = true;
    if ($a === 'force'         || strpos($a, 'force=')         === 0) $force        = true;
    if ($a === 'setup-columns' || strpos($a, 'setup-columns=') === 0) $setupColumns = true;
}

$categoryFilter = isset($rd['category'])    ? trim($rd['category'])           : LIGHTHOUSE_DEFAULT_CATEGORY;
$singleDomain   = isset($rd['domain'])      ? trim($rd['domain'])             : '';
$batchSize      = isset($rd['batch'])       ? max(1, (int)$rd['batch'])       : 1;
$skipDays       = isset($rd['skip-recent']) ? max(0, (int)$rd['skip-recent']) : LIGHTHOUSE_SKIP_RECENT_DAYS;
$deviceOverride = isset($rd['device'])      ? strtolower(trim($rd['device'])) : null;

$deviceMode = $deviceOverride ?? LIGHTHOUSE_DEVICE;

// --domain implies --force for that one domain
if ($singleDomain !== '') {
    $force = true;
}

// Validate device mode
if (!in_array($deviceMode, ['mobile', 'desktop', 'both'], true)) {
    out_error("Invalid --device value '$deviceMode'. Use mobile, desktop, or both.");
    exit(1);
}

$deviceModes = match($deviceMode) {
    'both'    => ['mobile', 'desktop'],
    'mobile'  => ['mobile'],
    'desktop' => ['desktop'],
};

// =============================================================================
// MAIN
// =============================================================================

try {
    out_divider();
    out_info("Lighthouse Gather — WMD SED Native Tool");
    out_divider();

    // -------------------------------------------------------------------------
    // Lock file — prevent concurrent runs
    // -------------------------------------------------------------------------
    $lockFile = UTIL::get_log_folder_path() . LIGHTHOUSE_LOCK_FILE;

    if (file_exists($lockFile)) {
        $existingPid = trim((string)file_get_contents($lockFile));
        if ($existingPid && @posix_kill((int)$existingPid, 0)) {
            out_warn("Another instance is already running (PID $existingPid). Exiting.");
            out_warn("Lock file: $lockFile");
            exit(0);
        } else {
            out_info("Stale lock file found (PID $existingPid no longer running). Removing.");
            unlink($lockFile);
        }
    }

    if (!$dryRun) {
        file_put_contents($lockFile, (string)getmypid());
        out_debug("Lock acquired: $lockFile (PID " . getmypid() . ")", $debug);

        register_shutdown_function(function() use ($lockFile) {
            if (file_exists($lockFile)) {
                unlink($lockFile);
            }
        });
    }

    // -------------------------------------------------------------------------
    // Auth + DB
    // -------------------------------------------------------------------------
    $auth = new \CodePunch\Config\Auth(\CodePunch\DB\Database::STANDARD_INIT);
    if (!$auth) throw new Exception("Failed to initialise Auth — check database configuration.");

    $db = $auth->getDatabase();
    if (!$db) throw new Exception("Failed to obtain database handle.");

    $table = $db->getDomainTableName();
    out_debug("Domain table: $table", $debug);

    // -------------------------------------------------------------------------
    // Resolve paths via UTIL::get_log_folder_path()
    // -------------------------------------------------------------------------
    $logBase    = UTIL::get_log_folder_path();
    $nodeFolder = $logBase . LIGHTHOUSE_NODE_FOLDER . DIRECTORY_SEPARATOR;
    $outFolder  = $logBase . LIGHTHOUSE_OUTPUT_FOLDER . DIRECTORY_SEPARATOR;
    $scanScript = $nodeFolder . 'lighthouse-scan.js';

    out_info("Node folder   : $nodeFolder");
    out_info("Output folder : $outFolder");
    out_info("Scan script   : $scanScript");

    // -------------------------------------------------------------------------
    // --setup-columns mode
    // -------------------------------------------------------------------------
    if ($setupColumns) {
        out_divider();
        out_info("Setting up Lighthouse custom columns...");
        setupLighthouseColumns($db, $debug, $dryRun);
        out_divider();
        out_info("Column setup complete.");
        exit(0);
    }

    // -------------------------------------------------------------------------
    // Verify environment
    // -------------------------------------------------------------------------
    if (!is_dir($nodeFolder)) {
        throw new Exception("Node folder not found: $nodeFolder — run lighthouse-setup.sh first.");
    }
    if (!is_dir($nodeFolder . 'node_modules')) {
        throw new Exception("node_modules not found in $nodeFolder — run lighthouse-setup.sh first.");
    }
    if (!file_exists($scanScript)) {
        throw new Exception("lighthouse-scan.js not found at $scanScript — run lighthouse-setup.sh first.");
    }
    $lhModule = $nodeFolder . 'node_modules' . DIRECTORY_SEPARATOR . 'lighthouse';
    if (!is_dir($lhModule)) {
        throw new Exception("lighthouse npm package not found in {$nodeFolder}node_modules — run lighthouse-setup.sh first.");
    }
    out_debug("lighthouse package found.", $debug);

    // Create output folder if needed
    if (!is_dir($outFolder)) {
        if (!$dryRun) {
            if (!mkdir($outFolder, 0755, true)) {
                throw new Exception("Could not create output folder: $outFolder");
            }
        }
        out_info("Created output folder: $outFolder");
    }

    // -------------------------------------------------------------------------
    // Verify custom columns exist
    // -------------------------------------------------------------------------
    $dcolumns     = $db->getDetailsOfAllDomainColumns();
    $requiredCols = ['lh_status', 'lh_scanned_at'];
    $missingCols  = [];
    foreach ($requiredCols as $col) {
        if (!isset($dcolumns[$col])) $missingCols[] = $col;
    }
    if (!empty($missingCols)) {
        throw new Exception(
            "Missing custom columns: " . implode(', ', $missingCols) .
            " — run: php lighthouse-gather.php --setup-columns"
        );
    }
    out_debug("All required custom columns present.", $debug);

    // -------------------------------------------------------------------------
    // Resolve columns to fetch
    // -------------------------------------------------------------------------
    $extraCols    = LIGHTHOUSE_PROTOCOL_COLUMN !== '' ? ['lh_scanned_at', LIGHTHOUSE_PROTOCOL_COLUMN] : ['lh_scanned_at'];
    $fetchColumns = implode(',', array_merge(
        ['sid', 'domain', 'availability', 'registry_expiry', 'registrar_expiry'],
        $extraCols
    ));

    // -------------------------------------------------------------------------
    // Resolve candidate SIDs
    // -------------------------------------------------------------------------
    $candidateSids = [];

    if ($singleDomain !== '') {
        out_info("Single domain mode: $singleDomain");
        $sidRows = $db->getFromTable("sid", $table, "domain = ?", [$singleDomain], '', '', 1);
        if ($sidRows === false) throw new Exception("SID lookup failed for domain: $singleDomain");
        if (empty($sidRows)) throw new Exception("Domain not found in database: $singleDomain");
		$row = array_change_key_case($sidRows[0], CASE_LOWER);
		$candidateSids = [(int)$row['sid']];
        out_debug("Resolved SID for $singleDomain: " . $candidateSids[0], $debug);

    } elseif ($categoryFilter !== '') {
        out_info("Category filter: $categoryFilter");
        $cid = $db->findOneOf($db->getCategoryTableName(), "name", $categoryFilter, "cid");
        if (!$cid) {
            $isDefault = ($categoryFilter === LIGHTHOUSE_DEFAULT_CATEGORY);
            if ($isDefault) {
                out_warn("Default category \"" . LIGHTHOUSE_DEFAULT_CATEGORY . "\" not found.");
                out_warn("Create this category in WMD SED, assign domains to it, then re-run.");
                out_warn("To scan a different category, use: --category=\"Your Category Name\"");
            } else {
                out_error("Category not found: \"$categoryFilter\" — check the name and try again.");
            }
            exit(0);
        }
        out_debug("Resolved category cid: $cid", $debug);

        $connRows = $db->getFromTable(
            "did",
            $db->getCategoryConnectionTableName(),
            "cid = ?",
            [(int)$cid],
            '', '', 100000
        );
        if ($connRows === false) throw new Exception("Category connection table query failed for cid: $cid");
        if (empty($connRows)) {
            out_info("Category \"$categoryFilter\" has no domains. Nothing to scan.");
            exit(0);
        }
		$connRows = array_map(fn($row) => array_change_key_case($row, CASE_LOWER), $connRows);
        $candidateSids = array_map(fn($r) => (int)$r['did'], $connRows);
        out_info("Domains in category: " . count($candidateSids));
        out_debug("SIDs: " . implode(', ', array_slice($candidateSids, 0, 20))
            . (count($candidateSids) > 20 ? ' … (truncated)' : ''), $debug);

    } else {
        out_info("Category filter: none (all categories)");
    }

    // -------------------------------------------------------------------------
    // Build WHERE clause
    // Note: PARAM_INT_ARRAY cannot be safely mixed with scalar params in the
    // same query — build explicit ? placeholders per SID instead.
    // -------------------------------------------------------------------------
    $where  = [];
    $params = [];

    if (!empty($candidateSids)) {
        $placeholders = implode(',', array_fill(0, count($candidateSids), '?'));
        $where[]      = "sid IN ($placeholders)";
        foreach ($candidateSids as $sid) {
            $params[] = $sid;
        }
    }

    // Exclude clearly unregistered domains
    $where[]  = "NOT (availability = ? AND registry_expiry IS NULL AND registrar_expiry IS NULL)";
    $params[] = 'available';

    // Skip-recent filter applied in SQL — only NULL is treated as not-yet-scanned
    if (!$force && $skipDays > 0) {
        $cutoff   = date('Y-m-d H:i:s', strtotime("-{$skipDays} days"));
        $where[]  = "(lh_scanned_at IS NULL OR lh_scanned_at < ?)";
        $params[] = $cutoff;
        out_info("Skipping domains scanned within $skipDays days (cutoff: $cutoff)");
    } elseif ($force) {
        out_info("--force: scanning all matching domains regardless of scan age.");
    }

    $whereClause = implode(' AND ', $where);
    out_debug("WHERE: $whereClause", $debug);
    out_debug("Params (" . count($params) . "): " . implode(', ', array_slice($params, 0, 10))
        . (count($params) > 10 ? ' … (truncated)' : ''), $debug);

    // -------------------------------------------------------------------------
    // Fetch domains
    // -------------------------------------------------------------------------
    $rows = $db->getFromTable($fetchColumns, $table, $whereClause, $params, 'domain', 'asc', 50000);
    if ($rows === false) throw new Exception("Domain query failed.");
	$rows = array_map(fn($row) => array_change_key_case($row, CASE_LOWER), $rows);

    $total = count($rows);
    out_info("Domains to scan: $total");
    out_info("Device mode: $deviceMode | Batch size: $batchSize");
    out_divider();

    if ($total === 0) {
        out_info("No domains to scan. Done.");
        exit(0);
    }

    if ($dryRun) {
        out_warn("DRY RUN — no scans will be performed, no data will be written.");
        foreach ($rows as $row) {
            out_info("Would scan: " . $row['domain']);
        }
        out_divider();
        out_warn("DRY RUN complete. Remove --dry-run to perform actual scans.");
        exit(0);
    }

    // -------------------------------------------------------------------------
    // Scan — single or batched
    // -------------------------------------------------------------------------
    $scanned = 0;
    $errors  = 0;
    $batches = array_chunk($rows, $batchSize);

    foreach ($batches as $batch) {
        if ($batchSize === 1) {
            $row     = $batch[0];
            $results = runScansForDomain($row, $nodeFolder, $scanScript, $deviceModes, $debug);
            processDomainResults($results, $row, $db, $table, $outFolder, $deviceModes, $debug);
            $status = computeDomainStatus($results, $deviceModes);
            if ($status === 'error') $errors++; else $scanned++;
        } else {
            // Parallel batch via proc_open
            $processes = [];
            foreach ($batch as $row) {
                foreach ($deviceModes as $mode) {
                    $url  = buildUrl($row);
                    $cmd  = buildNodeCommand($nodeFolder, $scanScript, $url, $mode, false);
                    out_info("Launching: " . $row['domain'] . " ($mode)");
                    out_debug("CMD: $cmd", $debug);
                    $proc = proc_open($cmd, [1 => ['pipe', 'w'], 2 => ['pipe', 'w']], $pipes);
                    if ($proc) {
                        $processes[] = ['proc' => $proc, 'pipes' => $pipes, 'row' => $row, 'mode' => $mode];
                    } else {
                        out_error("Failed to launch process for: " . $row['domain'] . " ($mode)");
                        $errors++;
                    }
                }
            }

            // Collect results — group by domain
            $domainResults = [];
            foreach ($processes as $p) {
                $stdout = stream_get_contents($p['pipes'][1]);
                $stderr = stream_get_contents($p['pipes'][2]);
                fclose($p['pipes'][1]);
                fclose($p['pipes'][2]);
                proc_close($p['proc']);

                if ($debug && $stderr) {
                    foreach (explode("\n", trim($stderr)) as $line) {
                        if ($line) out_debug("stderr [{$p['row']['domain']} {$p['mode']}]: $line", $debug);
                    }
                }

                $result = parseScanOutput($stdout ?? '', $p['row']['domain'], $p['mode']);
                $domainResults[$p['row']['domain']][$p['mode']] = $result;
            }

            foreach ($batch as $row) {
                $results = $domainResults[$row['domain']] ?? [];
                processDomainResults($results, $row, $db, $table, $outFolder, $deviceModes, $debug);
                $status = computeDomainStatus($results, $deviceModes);
                if ($status === 'error') $errors++; else $scanned++;
            }
        }
    }

    // -------------------------------------------------------------------------
    // Summary
    // -------------------------------------------------------------------------
    out_divider();
    out_info("Scan complete.");
    out_info("Scanned : $scanned");
    out_info("Errors  : $errors");
    out_divider();

} catch (Exception $e) {
    $logger = new \CodePunch\Base\CPLogger();
    $logger->error($e->getMessage());
    out_error($e->getMessage() . ' — ' . $e->getFile() . ':' . $e->getLine());
    exit(1);
}

// =============================================================================
// FUNCTIONS
// =============================================================================

/**
 * Build the URL for a domain row, using the protocol column if configured.
 */
function buildUrl(array $row): string {
    $protocol = 'https://';
    if (LIGHTHOUSE_PROTOCOL_COLUMN !== '' && !empty($row[LIGHTHOUSE_PROTOCOL_COLUMN])) {
        $protocol = rtrim($row[LIGHTHOUSE_PROTOCOL_COLUMN], ':/') . '://';
    }
    return $protocol . $row['domain'];
}

/**
 * Build an OS-aware shell command to invoke lighthouse-scan.js.
 * Uses 'cd /d' on Windows. mergeStderr controls stderr handling.
 */
function buildNodeCommand(string $nodeFolder, string $scanScript, string $url, string $mode, bool $mergeStderr = true): string {
    $isWin = (PHP_OS_FAMILY === 'Windows');

    $cd = $isWin
        ? 'cd /d ' . escapeshellarg($nodeFolder)
        : 'cd '    . escapeshellarg($nodeFolder);

    $cmd = $cd
         . ' && node ' . escapeshellarg($scanScript)
         . ' --domain=' . escapeshellarg(parse_url($url, PHP_URL_HOST) ?: $url)
         . ' --device=' . escapeshellarg($mode)
         . ' --timeout=' . escapeshellarg((string)LIGHTHOUSE_TIMEOUT_MS);

    if ($mergeStderr) {
        $cmd .= $isWin ? ' 2>&1' : ' 2>/dev/null';
    }

    return $cmd;
}

/**
 * Run synchronous scans for all device modes for one domain.
 * Returns array keyed by mode: ['mobile' => result, 'desktop' => result]
 */
function runScansForDomain(array $row, string $nodeFolder, string $scanScript, array $deviceModes, bool $debug): array {
    $results = [];
    $url     = buildUrl($row);

    foreach ($deviceModes as $mode) {
        $cmd = buildNodeCommand($nodeFolder, $scanScript, $url, $mode, true);
        out_info("Scanning: {$row['domain']} ($mode)");
        out_debug("CMD: $cmd", $debug);
        $output     = shell_exec($cmd);
        $results[$mode] = parseScanOutput($output ?? '', $row['domain'], $mode);
    }

    return $results;
}

/**
 * Parse raw stdout from lighthouse-scan.js into a result array.
 */
function parseScanOutput(string $raw, string $domain, string $mode): array {
    $raw = trim($raw);

    if ($raw === '') {
        return errorResult($domain, $mode, 'Empty output from scanner — process may have crashed.');
    }

    // Strip any non-JSON lines that bled through before the JSON object
    $lines     = explode("\n", $raw);
    $jsonStart = false;
    $jsonLines = [];
    foreach ($lines as $line) {
        if (!$jsonStart && strlen(ltrim($line)) > 0 && ltrim($line)[0] === '{') {
            $jsonStart = true;
        }
        if ($jsonStart) $jsonLines[] = $line;
    }
    $jsonStr = implode("\n", $jsonLines);

    $data = json_decode($jsonStr, true);
    if (!is_array($data)) {
        return errorResult($domain, $mode, 'Could not parse JSON output: ' . substr($raw, 0, 200));
    }

    return $data;
}

/**
 * Return a standard error result array for one device mode.
 */
function errorResult(string $domain, string $mode, string $message): array {
    return [
        'status' => 'error',
        'device' => $mode,
        'url'    => $domain,
        'scores' => [
            'performance'    => null,
            'seo'            => null,
            'accessibility'  => null,
            'best-practices' => null,
            'pwa'            => null,
        ],
        'audits'     => [],
        'incomplete' => [],
        'fetchTime'  => null,
        'error'      => $message,
    ];
}

/**
 * Compute the overall lh_status for a domain from all mode results.
 * error = all scores are null across all modes
 * fail  = any score in LIGHTHOUSE_FAIL_CATEGORIES is non-null and below threshold
 * pass  = everything else
 */
function computeDomainStatus(array $results, array $deviceModes): string {
    $allNull = true;
    $anyFail = false;

    foreach ($deviceModes as $mode) {
        $scores = $results[$mode]['scores'] ?? [];
        foreach (LIGHTHOUSE_FAIL_CATEGORIES as $cat) {
            $v = $scores[$cat] ?? null;
            if ($v !== null) {
                $allNull = false;
                if ((int)$v < LIGHTHOUSE_FAIL_THRESHOLD) {
                    $anyFail = true;
                }
            }
        }
    }

    if ($allNull) return 'error';
    return $anyFail ? 'fail' : 'pass';
}

/**
 * Write scan results for all device modes to DB columns and JSON files.
 */
function processDomainResults(array $results, array $row, $db, string $table, string $outFolder, array $deviceModes, bool $debug): void {
    $domain     = $row['domain'];
    $sid        = $row['sid'];
    $updateData = [];

    foreach ($deviceModes as $mode) {
        $result = $results[$mode] ?? errorResult($domain, $mode, 'No result collected.');
        $scores = $result['scores'] ?? [];

        foreach (CATEGORY_COLUMN_MAP as $cat => $col) {
            $v = $scores[$cat] ?? null;
            $updateData["lh_{$col}_{$mode}"] = ($v !== null) ? (int)$v : null;
        }

        // Save JSON to disk
        $jsonFile = $outFolder . $domain . '-' . $mode . '.json';
        $fullData = array_merge($result, [
            'domain'  => $domain,
            'sid'     => $sid,
            'savedAt' => date('Y-m-d H:i:s'),
        ]);
        out_debug("Writing JSON: $jsonFile", $debug);
        if (file_put_contents($jsonFile, json_encode($fullData, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES)) === false) {
            out_error("Could not write JSON file: $jsonFile");
        }

        if ($result['error'] ?? '') {
            out_error("$domain ($mode): " . $result['error']);
        }
    }

    // Overall status and timestamp
    $status = computeDomainStatus($results, $deviceModes);
    $updateData['lh_status']     = $status;
    $updateData['lh_scanned_at'] = date('Y-m-d H:i:s');

    $label = match($status) {
        'pass'  => "[OK]    PASS",
        'fail'  => "[WARN]  FAIL",
        default => "[ERROR] ERROR",
    };

    // Score summary for output line
    $scoreParts = [];
    foreach ($deviceModes as $mode) {
        $scores = $results[$mode]['scores'] ?? [];
        $perf   = $scores['performance'] ?? '–';
        $seo    = $scores['seo'] ?? '–';
        $scoreParts[] = "$mode perf:$perf seo:$seo";
    }
    UTIL::print("$label $domain — " . implode(' | ', $scoreParts));

    out_debug("Writing to DB for sid=$sid", $debug);
    $r = $db->updateTable($table, $updateData, "sid=?", [$sid]);
    if ($r === false) {
        out_error("DB write failed for $domain (sid=$sid)");
    }
}

/**
 * Create all lh_* custom columns in WMD SED.
 * Safe to run multiple times — skips columns that already exist.
 */
function setupLighthouseColumns($db, bool $debug, bool $dryRun): void {
    $dcolumns    = $db->getDetailsOfAllDomainColumns();
    $dcTableName = $db->getDatacolumnsTableName();
    $domainTable = $db->getDomainTableName();

    foreach (LIGHTHOUSE_COLUMNS as [$fname, $type, $label, $length, $gridview]) {

        if (isset($dcolumns[$fname])) {
            out_skip("Column '$fname' already exists.");
            continue;
        }

        try {
            if ($db->connection->getDatabasePlatform()->getReservedKeywordsList()->isKeyword($fname)) {
                out_error("'$fname' is a reserved SQL keyword — cannot create.");
                continue;
            }
        } catch (\Throwable $e) {
            // Older Doctrine — skip keyword check
        }

        $existing = $db->getFromTable("id", $dcTableName, "name=? AND tablename=?",
            [$fname, \CodePunch\DB\DomainDB::DOMAIN_TABLE]);
        if ($existing !== false && isset($existing[0])) {
            out_skip("Column '$fname' already registered in datacolumns.");
            continue;
        }

        if ($dryRun) {
            out_info("DRY RUN — would create column: $fname ($type)");
            continue;
        }

        // Step 1: Add physical column
        $options = ['notnull' => false];
        if ($type === 'string' && $length !== null) {
            $options['length'] = $length;
        }

        out_debug("Creating DB column: $fname ($type)", $debug);
        $istatus = $db->insertColumnsAndKeys($domainTable, [['name' => $fname, 'type' => $type, 'options' => $options]], []);
        if (!$istatus) {
            out_error("Failed to create DB column '$fname'.");
            continue;
        }

        // Step 2: Register in datacolumns metadata table
        $datacolumn = [
            'name'      => $fname,
            'tablename' => \CodePunch\DB\DomainDB::DOMAIN_TABLE,
            'server'    => '*',
            'label'     => $label,
            'editable'  => 0,
            'gridview'  => $gridview,
            'width'     => 80,
            'fieldtype' => $type,
            'custom'    => 1,
        ];

        if ($db->insertIntoTable($dcTableName, $datacolumn) === false) {
            out_error("Column '$fname' added to DB but failed to register in datacolumns table.");
            continue;
        }

        AUDIT::add($db, \CodePunch\DB\Audit::ADD_CUSTOM_COLUMN, "Added $fname (Lighthouse Monitor)", "");
        out_ok("Created column: $fname ($type)");
    }
}