<?php
// usage:
//   php __CRON/cron_vpnid_it_name_backfill_cli.php --dry-run
//   php __CRON/cron_vpnid_it_name_backfill_cli.php --apply

@chdir(__DIR__ . '/..');

include_once(__DIR__ . '/../_common.php');

$apply = false;
foreach ($argv as $arg) {
    if ($arg === '--apply') {
        $apply = true;
    }
    if ($arg === '--dry-run') {
        $apply = false;
    }
}

$targets = array(
    'vpnid_list',
    'vpnid_list_free',
    'vpnid_list_log',
    'vpnid_list_log_free',
    'vpnid_free'
);

function col_exists($tableName, $columnName)
{
    $sql = "SHOW COLUMNS FROM `" . sql_escape_string($tableName) . "` LIKE '" . sql_escape_string($columnName) . "'";
    $row = sql_fetch($sql, false);
    return is_array($row) && isset($row['Field']);
}

function resolve_item_name($mbId, $serverName, $vpnUserId)
{
    $safeMb = sql_escape_string((string)$mbId);
    $safeServer = sql_escape_string((string)$serverName);
    $safeVpnUser = sql_escape_string((string)$vpnUserId);

    if ($safeMb === '' || $safeServer === '' || $safeVpnUser === '') {
        return '';
    }

    $sql1 = "SELECT i.it_name
               FROM app_ikev2_provision_queue q
               JOIN g5_shop_item i ON i.it_id = q.it_id
              WHERE q.mb_id = '{$safeMb}'
                AND q.server_ip = '{$safeServer}'
                AND q.vpn_user_id = '{$safeVpnUser}'
                AND q.final_status = 'success'
                AND i.it_name IS NOT NULL
                AND i.it_name <> ''
              ORDER BY q.queue_id DESC
              LIMIT 1";
    $r1 = sql_fetch($sql1, false);
    if (is_array($r1) && !empty($r1['it_name'])) {
        return trim((string)$r1['it_name']);
    }

    // Fallback for legacy rows where vpn_user_id may have been inconsistent.
    $sql2 = "SELECT i.it_name
               FROM app_ikev2_provision_queue q
               JOIN g5_shop_item i ON i.it_id = q.it_id
              WHERE q.mb_id = '{$safeMb}'
                AND q.server_ip = '{$safeServer}'
                AND q.final_status = 'success'
                AND i.it_name IS NOT NULL
                AND i.it_name <> ''
              ORDER BY q.queue_id DESC
              LIMIT 1";
    $r2 = sql_fetch($sql2, false);
    if (is_array($r2) && !empty($r2['it_name'])) {
        return trim((string)$r2['it_name']);
    }

    return '';
}

$report = array(
    'ok' => true,
    'mode' => $apply ? 'apply' : 'dry-run',
    'ts' => date('Y-m-d H:i:s'),
    'tables' => array(),
    'errors' => array()
);

$totalScanned = 0;
$totalResolved = 0;
$totalUpdated = 0;
$totalUnresolved = 0;

foreach ($targets as $tableName) {
    if (!col_exists($tableName, 'no')
        || !col_exists($tableName, 'mb_id')
        || !col_exists($tableName, 'server_name')
        || !col_exists($tableName, 'vpn_user_id')
        || !col_exists($tableName, 'it_name')) {
        $report['tables'][] = array(
            'table' => $tableName,
            'scanned' => 0,
            'resolved' => 0,
            'updated' => 0,
            'unresolved' => 0,
            'skipped' => 'required_columns_missing'
        );
        continue;
    }

    $stats = array(
        'table' => $tableName,
        'scanned' => 0,
        'resolved' => 0,
        'updated' => 0,
        'unresolved' => 0
    );

    $sqlRows = "SELECT no, mb_id, server_name, vpn_user_id
                  FROM `" . sql_escape_string($tableName) . "`
                 WHERE (it_name IS NULL OR it_name = '')
                 ORDER BY no ASC";
    $resRows = sql_query($sqlRows, false);

    if ($resRows === false) {
        $report['ok'] = false;
        $report['errors'][] = array(
            'table' => $tableName,
            'error' => 'select_failed'
        );
        continue;
    }

    while ($row = sql_fetch_array($resRows)) {
        $stats['scanned']++;
        $totalScanned++;

        $resolvedName = resolve_item_name(
            isset($row['mb_id']) ? $row['mb_id'] : '',
            isset($row['server_name']) ? $row['server_name'] : '',
            isset($row['vpn_user_id']) ? $row['vpn_user_id'] : ''
        );

        if ($resolvedName === '') {
            $stats['unresolved']++;
            $totalUnresolved++;
            continue;
        }

        $stats['resolved']++;
        $totalResolved++;

        if ($apply) {
            $sqlUpdate = "UPDATE `" . sql_escape_string($tableName) . "`
                             SET it_name = '" . sql_escape_string($resolvedName) . "',
                                 update_datetime = NOW()
                           WHERE no = '" . (int)$row['no'] . "'
                             AND (it_name IS NULL OR it_name = '')";
            $resUpdate = sql_query($sqlUpdate, false);
            if ($resUpdate === false) {
                $report['ok'] = false;
                $report['errors'][] = array(
                    'table' => $tableName,
                    'no' => (int)$row['no'],
                    'error' => 'update_failed'
                );
                continue;
            }
            $stats['updated']++;
            $totalUpdated++;
        }
    }

    $report['tables'][] = $stats;
}

$report['summary'] = array(
    'scanned' => $totalScanned,
    'resolved' => $totalResolved,
    'updated' => $totalUpdated,
    'unresolved' => $totalUnresolved
);

$logPath = G5_PATH . '/data/cron_vpnid_it_name_backfill.log';
@file_put_contents($logPath, json_encode($report, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) . PHP_EOL, FILE_APPEND | LOCK_EX);

echo json_encode($report, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) . PHP_EOL;
exit($report['ok'] ? 0 : 1);
