<?php
// Usage:
//   php __CRON/cron_ikev2_repair_item_spec_org_cli.php --help
//   php __CRON/cron_ikev2_repair_item_spec_org_cli.php --ai-only
//   php __CRON/cron_ikev2_repair_item_spec_org_cli.php --apply --ai-only

if (PHP_SAPI !== 'cli') {
    echo "CLI only\n";
    exit(1);
}

$projectRoot = realpath(__DIR__ . '/..');
if ($projectRoot === false) {
    $projectRoot = dirname(__DIR__);
}

@chdir($projectRoot);

if (!is_file($projectRoot . '/_common.php')) {
    echo json_encode(array(
        'ok' => false,
        'error' => 'missing_common_php',
        'path' => $projectRoot . '/_common.php'
    ), JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    echo "\n";
    exit(1);
}

require_once $projectRoot . '/_common.php';

if (!function_exists('sql_query') || !isset($g5['g5_shop_item_table'])) {
    echo json_encode(array(
        'ok' => false,
        'error' => 'db_context_unavailable'
    ), JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    echo "\n";
    exit(1);
}

$opts = getopt('', array('help', 'apply', 'ai-only', 'it_id::', 'mb_id::', 'limit::'));

if (isset($opts['help'])) {
    echo "IKEv2 item datetime(org/current) repair CLI\n\n";
    echo "Options:\n";
    echo "  --help          Show this help\n";
    echo "  --apply         Apply updates (default: dry-run)\n";
    echo "  --ai-only       Restrict to AI bulk test items only\n";
    echo "  --it_id=VALUE   Restrict to specific item id\n";
    echo "  --mb_id=VALUE   Restrict to specific buyer member id\n";
    echo "  --limit=N       Max rows to inspect per rule (default: 5000, max: 50000)\n";
    echo "\nRules:\n";
    echo "  1) Unsold-like items (buyer empty) with stale datetime values -> clear current/org datetime and buyer name\n";
    echo "  2) Sold items (buyer exists) with missing org datetime -> backfill org from current (from/to each independently)\n";
    exit(0);
}

$apply = isset($opts['apply']);
$aiOnly = isset($opts['ai-only']);
$itId = isset($opts['it_id']) ? trim((string)$opts['it_id']) : '';
$mbId = isset($opts['mb_id']) ? trim((string)$opts['mb_id']) : '';
$limit = isset($opts['limit']) ? (int)$opts['limit'] : 5000;
if ($limit < 1) {
    $limit = 1;
}
if ($limit > 50000) {
    $limit = 50000;
}

$itemTable = $g5['g5_shop_item_table'];

$baseWhere = array();
$baseWhere[] = "it_spec_sales_type = 'vpn-mall'";

if ($aiOnly) {
    $baseWhere[] = "(it_shop_memo LIKE '[AI_BULK_VPN_TEST]%' OR pt_tag LIKE '%AI_BULK_VPN_TEST%')";
}

if ($itId !== '') {
    $baseWhere[] = "it_id = '" . sql_escape_string($itId) . "'";
}

if ($mbId !== '') {
    $baseWhere[] = "it_spec_buyer_mb_id = '" . sql_escape_string($mbId) . "'";
}

$whereBaseSql = implode(' AND ', $baseWhere);

// Rule 1: buyer empty but datetime columns populated (stale carry-over)
$whereRule1 = "{$whereBaseSql}
    AND (it_spec_buyer_mb_id IS NULL OR TRIM(it_spec_buyer_mb_id) = '')
    AND (
        (it_spec_from_datetime IS NOT NULL AND TRIM(it_spec_from_datetime) <> '' AND it_spec_from_datetime <> '0000-00-00 00:00:00')
        OR (it_spec_to_datetime IS NOT NULL AND TRIM(it_spec_to_datetime) <> '' AND it_spec_to_datetime <> '0000-00-00 00:00:00')
        OR (it_spec_from_datetime_org IS NOT NULL AND TRIM(it_spec_from_datetime_org) <> '' AND it_spec_from_datetime_org <> '0000-00-00 00:00:00')
        OR (it_spec_to_datetime_org IS NOT NULL AND TRIM(it_spec_to_datetime_org) <> '' AND it_spec_to_datetime_org <> '0000-00-00 00:00:00')
    )";

$sqlRule1 = "SELECT it_id,
                    it_spec_buyer_mb_id,
                    it_spec_buyer_mb_name,
                    it_spec_from_datetime,
                    it_spec_to_datetime,
                    it_spec_from_datetime_org,
                    it_spec_to_datetime_org
               FROM {$itemTable}
              WHERE {$whereRule1}
              ORDER BY it_id ASC
              LIMIT {$limit}";
$rowsRule1 = array();
$resRule1 = sql_query($sqlRule1, false);
if ($resRule1) {
    while ($r = sql_fetch_array($resRule1)) {
        $rowsRule1[] = $r;
    }
}

// Rule 2: buyer exists and current from/to exist, but org missing -> backfill org
$whereRule2 = "{$whereBaseSql}
    AND (it_spec_buyer_mb_id IS NOT NULL AND TRIM(it_spec_buyer_mb_id) <> '')
    AND (it_spec_from_datetime IS NOT NULL AND TRIM(it_spec_from_datetime) <> '' AND it_spec_from_datetime <> '0000-00-00 00:00:00')
    AND (it_spec_to_datetime IS NOT NULL AND TRIM(it_spec_to_datetime) <> '' AND it_spec_to_datetime <> '0000-00-00 00:00:00')
    AND (
        it_spec_from_datetime_org IS NULL OR TRIM(it_spec_from_datetime_org) = '' OR it_spec_from_datetime_org = '0000-00-00 00:00:00'
        OR it_spec_to_datetime_org IS NULL OR TRIM(it_spec_to_datetime_org) = '' OR it_spec_to_datetime_org = '0000-00-00 00:00:00'
    )";

$sqlRule2 = "SELECT it_id,
                    it_spec_buyer_mb_id,
                    it_spec_from_datetime,
                    it_spec_to_datetime,
                    it_spec_from_datetime_org,
                    it_spec_to_datetime_org
               FROM {$itemTable}
              WHERE {$whereRule2}
              ORDER BY it_id ASC
              LIMIT {$limit}";
$rowsRule2 = array();
$resRule2 = sql_query($sqlRule2, false);
if ($resRule2) {
    while ($r = sql_fetch_array($resRule2)) {
        $rowsRule2[] = $r;
    }
}

$appliedRule1 = 0;
$appliedRule2 = 0;

if ($apply) {
    foreach ($rowsRule1 as $row) {
        $itemIdEsc = sql_escape_string((string)$row['it_id']);
        $sqlUpdate1 = "UPDATE {$itemTable}
                          SET it_spec_from_datetime = '',
                              it_spec_to_datetime = '',
                              it_spec_from_datetime_org = '',
                              it_spec_to_datetime_org = '',
                              it_spec_buyer_mb_name = ''
                        WHERE it_id = '{$itemIdEsc}'
                          AND (it_spec_buyer_mb_id IS NULL OR TRIM(it_spec_buyer_mb_id) = '')";
        sql_query($sqlUpdate1, false);
        $appliedRule1++;
    }

    foreach ($rowsRule2 as $row) {
        $itemIdEsc = sql_escape_string((string)$row['it_id']);
        $sqlUpdate2 = "UPDATE {$itemTable}
                          SET it_spec_from_datetime_org = CASE
                                  WHEN it_spec_from_datetime_org IS NULL OR TRIM(it_spec_from_datetime_org) = '' OR it_spec_from_datetime_org = '0000-00-00 00:00:00'
                                      THEN it_spec_from_datetime
                                  ELSE it_spec_from_datetime_org
                              END,
                              it_spec_to_datetime_org = CASE
                                  WHEN it_spec_to_datetime_org IS NULL OR TRIM(it_spec_to_datetime_org) = '' OR it_spec_to_datetime_org = '0000-00-00 00:00:00'
                                      THEN it_spec_to_datetime
                                  ELSE it_spec_to_datetime_org
                              END
                        WHERE it_id = '{$itemIdEsc}'
                          AND (it_spec_buyer_mb_id IS NOT NULL AND TRIM(it_spec_buyer_mb_id) <> '')";
        sql_query($sqlUpdate2, false);
        $appliedRule2++;
    }
}

$result = array(
    'ok' => true,
    'mode' => $apply ? 'apply' : 'dry-run',
    'scope' => array(
        'vpn_sales_type_only' => true,
        'ai_only' => $aiOnly,
        'it_id' => $itId,
        'mb_id' => $mbId,
        'limit' => $limit
    ),
    'rule1_unsold_stale_datetime' => array(
        'matched' => count($rowsRule1),
        'applied' => $appliedRule1,
        'sample_it_ids' => array_slice(array_values(array_map(function($r) { return (string)$r['it_id']; }, $rowsRule1)), 0, 30)
    ),
    'rule2_sold_missing_org' => array(
        'matched' => count($rowsRule2),
        'applied' => $appliedRule2,
        'sample_it_ids' => array_slice(array_values(array_map(function($r) { return (string)$r['it_id']; }, $rowsRule2)), 0, 30)
    )
);

echo json_encode($result, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_PRETTY_PRINT);
echo "\n";

exit(0);
