<?php
/**
 * wcag-gather.php
 * CLI tool for gathering WCAG accessibility data for domains in WMD SED.
 *
 * Queries the domain table filtered by category and active status, runs
 * axe-scan.js via Puppeteer for each domain, writes summary data back to
 * custom WMD columns, and saves full violation JSON to disk.
 *
 * CLI ONLY — exits immediately if run in a browser.
 *
 * Usage:
 *   php wcag-gather.php
 *   php wcag-gather.php --category="My Category"
 *   php wcag-gather.php --domain=example.com
 *   php wcag-gather.php --batch=3
 *   php wcag-gather.php --skip-recent=14
 *   php wcag-gather.php --force
 *   php wcag-gather.php --dry-run
 *   php wcag-gather.php --debug
 *   php wcag-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 WCAG_NODE_FOLDER = 'websites';

/** Subfolder name under UTIL::get_log_folder_path() where JSON output is stored. */
const WCAG_OUTPUT_FOLDER = 'wcag-data';

/** Default WMD category name to filter domains. Empty string = all categories. */
const WCAG_DEFAULT_CATEGORY = 'WCAG Test Domains';

/**
 * Custom column name that holds the protocol (http/https) for each domain.
 * Set to empty string to use automatic https:// with http:// fallback.
 */
const WCAG_PROTOCOL_COLUMN = '';

/** Skip domains scanned within this many days (0 = always scan). */
const WCAG_SKIP_RECENT_DAYS = 7;

/** WCAG level to test against: 'A', 'AA', or 'AAA'. */
const WCAG_LEVEL = 'AA';

/** Lock file name under the log folder to prevent concurrent runs. */
const WCAG_LOCK_FILE = 'wcag-gather.lock';

/** Puppeteer navigation timeout in milliseconds. */
const WCAG_TIMEOUT_MS = 30000;

/** Custom column names written by this tool. */
const COL_STATUS       = 'wcag_status';
const COL_LEVEL        = 'wcag_level';
const COL_ISSUE_COUNTS = 'wcag_issue_counts';
const COL_SCANNED_AT   = 'wcag_scanned_at';

// =============================================================================
// 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']) : WCAG_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']) : WCAG_SKIP_RECENT_DAYS;

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

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

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

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

    if (file_exists($lockFile)) {
        $existingPid = trim(file_get_contents($lockFile));
        // Check if the PID is still running
        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);
        }
    }

    // Write our PID to the lock file
    file_put_contents($lockFile, (string)getmypid());
    out_debug("Lock acquired: $lockFile (PID " . getmypid() . ")", $debug);

    // Always release the lock on exit — covers normal exit, exceptions, and crashes
    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
    // -------------------------------------------------------------------------
    $logBase    = UTIL::get_log_folder_path();
    $nodeFolder = $logBase . WCAG_NODE_FOLDER . DIRECTORY_SEPARATOR;
    $outFolder  = $logBase . WCAG_OUTPUT_FOLDER . DIRECTORY_SEPARATOR;
    $axeScript  = $nodeFolder . 'axe-scan.js';

    out_info("Node folder   : $nodeFolder");
    out_info("Output folder : $outFolder");
    out_info("Axe script    : $axeScript");

    // Validate environment
    if (!is_dir($nodeFolder)) {
        throw new Exception("Node folder not found: $nodeFolder — run wcag-setup.sh first.");
    }
    if (!is_dir($nodeFolder . 'node_modules')) {
        throw new Exception("node_modules not found in $nodeFolder — run wcag-setup.sh first.");
    }
    if (!file_exists($axeScript)) {
        throw new Exception("axe-scan.js not found at $axeScript — copy it to the websites folder.");
    }

    $axeModule = $nodeFolder . 'node_modules' . DIRECTORY_SEPARATOR . '@axe-core' . DIRECTORY_SEPARATOR . 'puppeteer';
    if (!is_dir($axeModule)) {
        throw new Exception(
            "@axe-core/puppeteer not found in {$nodeFolder}node_modules" . DIRECTORY_SEPARATOR . " — " .
            "run wcag-setup.sh before using this tool."
        );
    }
    out_debug("@axe-core/puppeteer 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");
    }

    out_debug("node_modules present: yes", $debug);
    out_debug("axe-scan.js present: yes", $debug);

    // -------------------------------------------------------------------------
    // --setup-columns: create custom columns if they don't exist
    // -------------------------------------------------------------------------
    if ($setupColumns) {
        out_divider();
        out_info("Setting up custom WCAG columns...");
        setupWcagColumns($db, $debug, $dryRun);
        out_divider();
        out_info("Column setup complete.");
        exit;
    }

    // -------------------------------------------------------------------------
    // Verify custom columns exist
    // -------------------------------------------------------------------------
    $dcolumns = $db->getDetailsOfAllDomainColumns();
    $requiredCols = [COL_STATUS, COL_LEVEL, COL_ISSUE_COUNTS, COL_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 wcag-gather.php --setup-columns"
        );
    }
    out_debug("All required custom columns present.", $debug);

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

    // -------------------------------------------------------------------------
    // Resolve candidate SIDs
    // — single domain: look up its SID directly
    // — category filter: resolve via category → connection → domain SIDs
    // — no filter: all SIDs (empty = no SID restriction applied)
    // -------------------------------------------------------------------------
    $candidateSids = []; // empty means "no SID restriction"

    if ($singleDomain !== '') {
        // Single domain mode — fetch just that domain's SID
        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");
        }
        $connRows = array_map(fn($row) => array_change_key_case($row, CASE_LOWER), $sidRows);
		$candidateSids = array_map(fn($r) => (int)$r['did'], $connRows);
        out_debug("Resolved SID for $singleDomain: " . $candidateSids[0], $debug);

    } elseif ($categoryFilter !== '') {
        // Step 1 — resolve category name to cid
        out_info("Category filter: $categoryFilter");
        $cid = $db->findOneOf($db->getCategoryTableName(), "name", $categoryFilter, "cid");
        if (!$cid) {
            $isDefault = ($categoryFilter === WCAG_DEFAULT_CATEGORY);
            if ($isDefault) {
                out_warn("Default category \"" . WCAG_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);

        // Step 2 — get all dids (= sids) from the category connection table
        $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;
        }
		$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 domain table WHERE clause
    // -------------------------------------------------------------------------
    // Note: PARAM_INT_ARRAY cannot be safely mixed with scalar params in the
    // same query — Doctrine expands the IN(?) placeholders internally which
    // confuses positional scalar params. Instead we build explicit placeholders
    // for the SID list as plain scalars.
    $where  = [];
    $params = [];

    // Restrict to resolved SIDs if we have any — use explicit ? placeholders
    // per SID rather than PARAM_INT_ARRAY to avoid mixed-type param issues.
    if (!empty($candidateSids)) {
        $placeholders = implode(',', array_fill(0, count($candidateSids), '?'));
        $where[]      = "sid IN ($placeholders)";
        foreach ($candidateSids as $sid) {
            $params[] = $sid;
        }
    }

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

    // Skip recently scanned unless --force or --domain
    if (!$force && $skipDays > 0) {
        $cutoff   = date('Y-m-d H:i:s', strtotime("-{$skipDays} days"));
        $where[]  = "(" . COL_SCANNED_AT . " IS NULL OR " . COL_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);
    $debugParams = array_slice($params, 0, 10);
    out_debug("Params (" . count($params) . " total): " . implode(', ', $debugParams)
        . (count($params) > 10 ? ' … (truncated)' : ''), $debug);

    // -------------------------------------------------------------------------
    // Fetch domains
    // -------------------------------------------------------------------------
    out_debug("WHERE: " . $whereClause, $debug);

    $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("Batch size: $batchSize");
    out_divider();

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

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

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

    foreach ($batches as $batchIndex => $batch) {
        if ($batchSize === 1) {
            $row    = $batch[0];
            $result = runScan($row, $nodeFolder, $axeScript, $debug);
            processScanResult($result, $row, $db, $table, $outFolder, $debug);
            if ($result['status'] === 'error') $errors++; else $scanned++;
        } else {
            // Parallel batch via proc_open
            $processes = [];
            foreach ($batch as $row) {
                $url = buildUrl($row);
                $cmd = buildScanCommand($nodeFolder, $axeScript, $url);
                out_info("Launching: " . $row['domain']);
                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];
                } else {
                    out_error("Failed to launch process for: " . $row['domain']);
                    $errors++;
                }
            }

            // Wait for all processes in batch
            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: $line", $debug);
                    }
                }

                $result = parseOutput($stdout, $p['row']['domain']);
                processScanResult($result, $p['row'], $db, $table, $outFolder, $debug);
                if ($result['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 (WCAG_PROTOCOL_COLUMN !== '' && !empty($row[WCAG_PROTOCOL_COLUMN])) {
        $protocol = rtrim($row[WCAG_PROTOCOL_COLUMN], ':/') . '://';
    }
    return $protocol . $row['domain'];
}

/**
 * Build the shell command to invoke axe-scan.js (parallel mode, stderr separate).
 */
function buildScanCommand(string $nodeFolder, string $axeScript, string $url): string {
    return buildNodeCommand($nodeFolder, $axeScript, $url, false);
}

/**
 * Build an OS-aware shell command to invoke axe-scan.js.
 * On Windows uses 'cd /d' and suppresses stderr differently.
 */
function buildNodeCommand(string $nodeFolder, string $axeScript, string $url, bool $mergeStderr = true): string {
    $isWin = (PHP_OS_FAMILY === 'Windows');
    if ($isWin) {
        $cmd = 'cd /d ' . escapeshellarg($nodeFolder)
             . ' && node ' . escapeshellarg($axeScript)
             . ' ' . escapeshellarg($url)
             . ' --level=' . escapeshellarg(WCAG_LEVEL)
             . ' --timeout=' . escapeshellarg((string)WCAG_TIMEOUT_MS);
        if ($mergeStderr) $cmd .= ' 2>&1';
    } else {
        $cmd = 'cd ' . escapeshellarg($nodeFolder)
             . ' && node ' . escapeshellarg($axeScript)
             . ' ' . escapeshellarg($url)
             . ' --level=' . escapeshellarg(WCAG_LEVEL)
             . ' --timeout=' . escapeshellarg((string)WCAG_TIMEOUT_MS);
        if ($mergeStderr) $cmd .= ' 2>/dev/null';
    }
    return $cmd;
}

/**
 * Run a single synchronous scan and return the parsed result array.
 */
function runScan(array $row, string $nodeFolder, string $axeScript, bool $debug): array {
    $url = buildUrl($row);
    $cmd = buildNodeCommand($nodeFolder, $axeScript, $url, true);

    out_info("Scanning: $url");
    out_debug("CMD: $cmd", $debug);

    $output = shell_exec($cmd);

    return parseOutput($output ?? '', $row['domain']);
}

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

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

    // Strip any stderr lines that bled through (lines not starting with {)
    $lines = explode("\n", $raw);
    $jsonStart = false;
    $jsonLines = [];
    foreach ($lines as $line) {
        if (!$jsonStart && 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, 'Could not parse JSON output: ' . substr($raw, 0, 200));
    }

    return $data;
}

/**
 * Return a standard error result array.
 */
function errorResult(string $domain, string $message): array {
    return [
        'url'         => $domain,
        'finalUrl'    => null,
        'scannedAt'   => date('Y-m-d H:i:s'),
        'status'      => 'error',
        'wcagLevel'   => 'none',
        'issueCounts' => ['critical' => 0, 'serious' => 0, 'moderate' => 0, 'minor' => 0],
        'violations'  => [],
        'incomplete'  => [],
        'passes'      => 0,
        'error'       => $message,
    ];
}

/**
 * Write scan result to DB columns and JSON file.
 */
function processScanResult(array $result, array $row, $db, string $table, string $outFolder, bool $debug): void {
    $domain = $row['domain'];
    $sid    = $row['sid'];

    $status      = $result['status']      ?? 'error';
    $wcagLevel   = $result['wcagLevel']   ?? 'none';
    $issueCounts = $result['issueCounts'] ?? ['critical' => 0, 'serious' => 0, 'moderate' => 0, 'minor' => 0];
    $scannedAt   = isset($result['scannedAt'])
        ? date('Y-m-d H:i:s', strtotime($result['scannedAt']))
        : date('Y-m-d H:i:s');

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

    $counts = $issueCounts;
    $countStr = "critical:{$counts['critical']} serious:{$counts['serious']} moderate:{$counts['moderate']} minor:{$counts['minor']}";
    UTIL::print("$label $domain — level:$wcagLevel — $countStr");

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

    // Write summary to DB
    $updateData = [
        COL_STATUS       => $status,
        COL_LEVEL        => $wcagLevel,
        COL_ISSUE_COUNTS => json_encode($issueCounts),
        COL_SCANNED_AT   => $scannedAt,
    ];

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

    // Write full JSON to disk
    $jsonFile = $outFolder . $domain . '.json';
    $fullData = array_merge($result, [
        'domain'    => $domain,
        'sid'       => $sid,
        'savedAt'   => date('Y-m-d H:i:s'),
    ]);

    out_debug("Writing JSON to: $jsonFile", $debug);
    if (file_put_contents($jsonFile, json_encode($fullData, JSON_PRETTY_PRINT)) === false) {
        out_error("Could not write JSON file: $jsonFile");
    }
}

/**
 * Create the four WCAG custom columns in WMD SED.
 * Safe to call multiple times — skips columns that already exist.
 */
function setupWcagColumns($db, bool $debug, bool $dryRun): void {
    $columnsToCreate = [
        [
            'name'     => COL_STATUS,
            'label'    => 'WCAG Status',
            'type'     => 'string',
            'length'   => 20,
            'gridview' => 1,
        ],
        [
            'name'     => COL_LEVEL,
            'label'    => 'WCAG Level',
            'type'     => 'string',
            'length'   => 5,
            'gridview' => 1,
        ],
        [
            'name'     => COL_ISSUE_COUNTS,
            'label'    => 'WCAG Issue Counts',
            'type'     => 'text',
            'length'   => null,
            'gridview' => 0,
        ],
        [
            'name'     => COL_SCANNED_AT,
            'label'    => 'WCAG Scanned At',
            'type'     => 'datetime',
            'length'   => null,
            'gridview' => 1,
        ],
    ];

    $dcolumns    = $db->getDetailsOfAllDomainColumns();
    $dcTableName = $db->getDatacolumnsTableName();
    $domainTable = $db->getDomainTableName();

    foreach ($columnsToCreate as $col) {
        $fname = $col['name'];

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

        // Reserved keyword check
        if ($db->connection->getDatabasePlatform()->getReservedKeywordsList()->isKeyword($fname)) {
            out_error("'$fname' is a reserved SQL keyword — cannot create.");
            continue;
        }

        // Already in datacolumns table?
        $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 ({$col['type']})");
            continue;
        }

        // Build columninfo
        $columninfo = [
            'name'    => $fname,
            'type'    => $col['type'],
            'options' => ['notnull' => false],
        ];
        if ($col['type'] === 'string' && $col['length'] !== null) {
            $columninfo['options']['length'] = $col['length'];
        }

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

        // Register in datacolumns
        $datacolumn = [
            'name'      => $fname,
            'tablename' => \CodePunch\DB\DomainDB::DOMAIN_TABLE,
            'server'    => '*',
            'label'     => $col['label'],
            'editable'  => 1,
            'gridview'  => $col['gridview'],
            'width'     => 100,
            'fieldtype' => $col['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", "");
        out_ok("Created column: $fname ({$col['type']})");
    }
}