<?php
if (!defined('_GNUBOARD_')) exit;

if (!function_exists('vpnlink_ikev2_log')) {
    function vpnlink_ikev2_log($message)
    {
        $line = "\n[" . date('Y-m-d H:i:s') . "] " . (string)$message;
        if (function_exists('write_log')) {
            write_log(G5_PATH . '/data/log/ikev2_provision.log', $line);
        } else {
            error_log('[ikev2_provision] ' . trim((string)$message));
        }
    }
}

if (!function_exists('vpnlink_ikev2_secret')) {
    function vpnlink_ikev2_secret()
    {
        $secretFromConfig = '';
        $secretFromEnv = '';

        if (isset($GLOBALS['config']) && isset($GLOBALS['config']['cf_10'])) {
            $secretFromConfig = trim((string)$GLOBALS['config']['cf_10']);
        }

        if (isset($_SERVER['IKEV2_PROVISION_SHARED_SECRET'])) {
            $secretFromEnv = trim((string)$_SERVER['IKEV2_PROVISION_SHARED_SECRET']);
        }

        if ($secretFromEnv === '' && isset($_ENV['IKEV2_PROVISION_SHARED_SECRET'])) {
            $secretFromEnv = trim((string)$_ENV['IKEV2_PROVISION_SHARED_SECRET']);
        }

        if ($secretFromEnv === '' && function_exists('getenv')) {
            $envSecret = getenv('IKEV2_PROVISION_SHARED_SECRET');
            if (is_string($envSecret)) {
                $secretFromEnv = trim($envSecret);
            }
        }

        if ($secretFromEnv === '') {
            $secretPhpFile = G5_PATH . '/mylib/ikev2_secret.php';
            if (is_file($secretPhpFile) && is_readable($secretPhpFile)) {
                $loaded = @include $secretPhpFile;
                if (is_array($loaded) && isset($loaded['IKEV2_PROVISION_SHARED_SECRET'])) {
                    $secretFromEnv = trim((string)$loaded['IKEV2_PROVISION_SHARED_SECRET']);
                } elseif (is_string($loaded)) {
                    $secretFromEnv = trim($loaded);
                }
            }
        }

        // 보안 요구사항(32자 이상)을 충족하는 값을 우선 사용
        if (vpnlink_ikev2_has_valid_secret($secretFromEnv)) {
            return $secretFromEnv;
        }

        if (vpnlink_ikev2_has_valid_secret($secretFromConfig)) {
            return $secretFromConfig;
        }

        // 모두 유효하지 않으면 진단을 위해 우선순위 값 반환
        if ($secretFromEnv !== '') {
            return $secretFromEnv;
        }

        return $secretFromConfig;
    }
}

if (!function_exists('vpnlink_ikev2_has_valid_secret')) {
    function vpnlink_ikev2_has_valid_secret($secret)
    {
        if (!is_string($secret)) {
            return false;
        }

        $secret = trim($secret);
        if ($secret === '') {
            return false;
        }

        if (strlen($secret) < 32) {
            return false;
        }

        if (stripos($secret, 'CHANGE_THIS') !== false) {
            return false;
        }

        return true;
    }
}

if (!function_exists('vpnlink_ikev2_enc_local')) {
    function vpnlink_ikev2_enc_local($plain, $secret)
    {
        $key = hash('sha256', $secret, true);
        $iv = random_bytes(16);
        $cipher = openssl_encrypt((string)$plain, 'AES-256-CBC', $key, OPENSSL_RAW_DATA, $iv);
        if ($cipher === false) {
            return '';
        }

        return base64_encode($iv) . ':' . base64_encode($cipher);
    }
}

if (!function_exists('vpnlink_ikev2_dec_local')) {
    function vpnlink_ikev2_dec_local($cipherText, $secret)
    {
        $parts = explode(':', (string)$cipherText, 2);
        if (count($parts) !== 2) {
            return '';
        }

        $iv = base64_decode($parts[0], true);
        $cipherRaw = base64_decode($parts[1], true);
        if ($iv === false || $cipherRaw === false || strlen($iv) !== 16) {
            return '';
        }

        $key = hash('sha256', $secret, true);
        $plain = openssl_decrypt($cipherRaw, 'AES-256-CBC', $key, OPENSSL_RAW_DATA, $iv);
        if ($plain === false) {
            return '';
        }

        return (string)$plain;
    }
}

if (!function_exists('vpnlink_ikev2_sql_escape')) {
    function vpnlink_ikev2_sql_escape($value)
    {
        return sql_escape_string((string)$value);
    }
}

if (!defined('IKEV2_MAX_AUTO_RETRY')) {
    define('IKEV2_MAX_AUTO_RETRY', 2880); // 약 48시간(1분마다 재시도 기준) 이후 manual_required 전환
}

if (!function_exists('vpnlink_ikev2_calc_retry_delay')) {
    function vpnlink_ikev2_calc_retry_delay($attemptCount)
    {
        $n = max(1, (int)$attemptCount);
        if ($n <= 1)   return 5;     // 1회: 5초  (순간 불가 대응)
        if ($n <= 2)   return 10;    // 2회: 10초
        if ($n <= 3)   return 30;    // 3회: 30초
        if ($n <= 10)  return 60;    // 4~10회: 1분
        if ($n <= 60)  return 300;   // 11~60회: 5분
        if ($n <= 360) return 1800;  // 61~360회: 30분
        return 3600;                  // 361회+: 1시간
    }
}

if (!function_exists('vpnlink_ikev2_build_endpoint')) {
    function vpnlink_ikev2_build_endpoint($serverIp)
    {
        $serverIp = trim((string)$serverIp);
        if ($serverIp === '') {
            return '';
        }

        $hasScheme = (bool)preg_match('#^https?://#i', $serverIp);
        if ($hasScheme) {
            $base = rtrim($serverIp, '/');
        } else {
            // IP 주소(v4/v6)는 HTTP, 도메인명은 HTTPS
            $host = preg_replace('#:\d+$#', '', $serverIp); // 포트 제거 후 판별
            $isIp = (filter_var($host, FILTER_VALIDATE_IP) !== false);
            $defaultScheme = $isIp ? 'http' : 'https';
            $base = $defaultScheme . '://' . rtrim($serverIp, '/');
        }

        if (preg_match('#/(mylib|mylib4ikev2svr)/ikev2_ipsec\.secrets_update\.php$#i', $base)) {
            return $base;
        }

        return $base . '/mylib4ikev2svr/ikev2_ipsec.secrets_update.php';
    }
}

if (!function_exists('vpnlink_ikev2_create_tables')) {
    function vpnlink_ikev2_create_tables()
    {
        $sql1 = "CREATE TABLE IF NOT EXISTS `app_ikev2_provision_queue` (
            `queue_id` bigint(20) NOT NULL AUTO_INCREMENT,
            `od_id` varchar(64) NOT NULL,
            `it_id` varchar(64) NOT NULL,
            `ct_id` bigint(20) DEFAULT NULL,
            `mb_id` varchar(64) NOT NULL,
            `protocol` varchar(64) NOT NULL,
            `server_ip` varchar(255) NOT NULL,
            `endpoint_url` varchar(255) NOT NULL,
            `vpn_user_id` varchar(255) NOT NULL,
            `vpn_password_enc` text NOT NULL,
            `status` varchar(20) NOT NULL DEFAULT 'queued',
            `final_status` varchar(20) NOT NULL DEFAULT 'queued',
            `attempt_count` int(11) NOT NULL DEFAULT 0,
            `fail_count` int(11) NOT NULL DEFAULT 0,
            `last_attempt_at` datetime DEFAULT NULL,
            `last_retry_at` datetime DEFAULT NULL,
            `next_retry_at` datetime DEFAULT NULL,
            `last_http_code` int(11) DEFAULT NULL,
            `last_result` varchar(50) DEFAULT NULL,
            `is_retry_enabled` tinyint(4) NOT NULL DEFAULT 1,
            `last_error` text,
            `last_response` mediumtext,
            `created_at` datetime NOT NULL,
            `updated_at` datetime NOT NULL,
            PRIMARY KEY (`queue_id`),
            UNIQUE KEY `uq_ikev2_order_item_user` (`od_id`,`it_id`,`protocol`,`server_ip`,`vpn_user_id`),
            KEY `idx_ikev2_retry` (`status`,`next_retry_at`),
            KEY `idx_ikev2_retry_enabled` (`is_retry_enabled`,`next_retry_at`),
            KEY `idx_ikev2_it_id` (`it_id`),
            KEY `idx_ikev2_od_id` (`od_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

        $sql2 = "CREATE TABLE IF NOT EXISTS `app_ikev2_provision_history` (
            `history_id` bigint(20) NOT NULL AUTO_INCREMENT,
            `queue_id` bigint(20) NOT NULL,
            `od_id` varchar(64) NOT NULL,
            `it_id` varchar(64) NOT NULL,
            `event_type` varchar(20) NOT NULL,
            `attempt_no` int(11) DEFAULT NULL,
            `event_status` varchar(50) DEFAULT NULL,
            `http_code` int(11) DEFAULT NULL,
            `error_message` text,
            `response_body` mediumtext,
            `next_retry_at` datetime DEFAULT NULL,
            `created_at` datetime NOT NULL,
            PRIMARY KEY (`history_id`),
            KEY `idx_ikev2_hist_item` (`it_id`,`created_at`),
            KEY `idx_ikev2_hist_order` (`od_id`,`created_at`),
            KEY `idx_ikev2_hist_queue` (`queue_id`,`created_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

        sql_query($sql1, false);
        sql_query($sql2, false);

        $sql3 = "CREATE TABLE IF NOT EXISTS `app_ikev2_id_conflict_history` (
            `conflict_id` bigint(20) NOT NULL AUTO_INCREMENT,
            `queue_id` bigint(20) NOT NULL,
            `od_id` varchar(64) NOT NULL,
            `it_id` varchar(64) NOT NULL,
            `ct_id` bigint(20) DEFAULT NULL,
            `server_ip` varchar(255) NOT NULL,
            `protocol` varchar(64) NOT NULL,
            `original_user_id` varchar(255) NOT NULL,
            `resolved_user_id` varchar(255) NOT NULL,
            `reason` varchar(120) NOT NULL DEFAULT 'scope_conflict',
            `trigger_source` varchar(64) NOT NULL DEFAULT '',
            `created_at` datetime NOT NULL,
            PRIMARY KEY (`conflict_id`),
            KEY `idx_conflict_order` (`od_id`,`created_at`),
            KEY `idx_conflict_queue` (`queue_id`,`created_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
        sql_query($sql3, false);

        $queueDesc = sql_query(" DESCRIBE `app_ikev2_provision_queue` ", false);
        $queueCols = array();
        if ($queueDesc) {
            while ($qCol = sql_fetch_array($queueDesc)) {
                $queueCols[] = $qCol['Field'];
            }
        }

        if (!in_array('is_retry_enabled', $queueCols, true)) {
            sql_query(" ALTER TABLE `app_ikev2_provision_queue` ADD `is_retry_enabled` tinyint(4) NOT NULL DEFAULT 1 AFTER `last_result` ", false);
            sql_query(" ALTER TABLE `app_ikev2_provision_queue` ADD KEY `idx_ikev2_retry_enabled` (`is_retry_enabled`, `next_retry_at`) ", false);
        }

        if (!in_array('expire_override', $queueCols, true)) {
            sql_query(" ALTER TABLE `app_ikev2_provision_queue` ADD `expire_override` varchar(20) DEFAULT NULL AFTER `is_retry_enabled` ", false);
        }

        if (!in_array('manual_retry_count', $queueCols, true)) {
            sql_query(" ALTER TABLE `app_ikev2_provision_queue` ADD `manual_retry_count` int(11) NOT NULL DEFAULT 0 AFTER `expire_override` ", false);
        }

        if (!in_array('point_refunded_at', $queueCols, true)) {
            sql_query(" ALTER TABLE `app_ikev2_provision_queue` ADD `point_refunded_at` datetime DEFAULT NULL AFTER `manual_retry_count` ", false);
            sql_query(" ALTER TABLE `app_ikev2_provision_queue` ADD `point_refunded_amount` int(11) NOT NULL DEFAULT 0 AFTER `point_refunded_at` ", false);
        }
    }
}

if (!function_exists('vpnlink_ikev2_validate_user_id')) {
    function vpnlink_ikev2_validate_user_id($userId)
    {
        $userId = trim((string)$userId);
        if ($userId === '') {
            return false;
        }
        return (bool)preg_match('/^[A-Za-z0-9._@\-]{3,64}$/', $userId);
    }
}

if (!function_exists('vpnlink_ikev2_build_suffix_candidate')) {
    function vpnlink_ikev2_build_suffix_candidate($baseUserId, $seq)
    {
        $baseUserId = trim((string)$baseUserId);
        $seq = (int)$seq;
        if ($baseUserId === '' || $seq <= 0) {
            return '';
        }

        $suffix = '_' . $seq;
        $maxLen = 64;
        $allowedBaseLen = $maxLen - strlen($suffix);
        if ($allowedBaseLen < 3) {
            return '';
        }

        if (strlen($baseUserId) > $allowedBaseLen) {
            $baseUserId = substr($baseUserId, 0, $allowedBaseLen);
        }

        return $baseUserId . $suffix;
    }
}

if (!function_exists('vpnlink_ikev2_collect_server_user_id_set')) {
    function vpnlink_ikev2_collect_server_user_id_set($serverIp)
    {
        $serverIp = trim((string)$serverIp);
        $set = array();
        if ($serverIp === '') {
            return $set;
        }

        $ret = vpnlink_ikev2_fetch_server_users($serverIp, 20000);
        if (!empty($ret['ok']) && !empty($ret['users']) && is_array($ret['users'])) {
            foreach ($ret['users'] as $u) {
                if (!is_array($u)) {
                    continue;
                }
                $uid = trim((string)($u['user_id'] ?? ''));
                if ($uid !== '') {
                    $set[strtolower($uid)] = true;
                }
            }
        }

        $rows = sql_query("SELECT vpn_user_id
                             FROM `app_ikev2_provision_queue`
                            WHERE server_ip = '" . vpnlink_ikev2_sql_escape($serverIp) . "'", false);
        if ($rows) {
            for ($i = 0; $row = sql_fetch_array($rows); $i++) {
                $uid = trim((string)($row['vpn_user_id'] ?? ''));
                if ($uid !== '') {
                    $set[strtolower($uid)] = true;
                }
            }
        }

        return $set;
    }
}

if (!function_exists('vpnlink_ikev2_update_item_remote_user_id')) {
    function vpnlink_ikev2_update_item_remote_user_id($itId, $serverIp, $protocol, $oldUserId, $newUserId)
    {
        global $g5;

        $itId = trim((string)$itId);
        $serverIp = trim((string)$serverIp);
        $protocol = trim((string)$protocol);
        $oldUserId = trim((string)$oldUserId);
        $newUserId = trim((string)$newUserId);

        if ($itId === '' || $serverIp === '' || $protocol === '' || $oldUserId === '' || $newUserId === '') {
            return false;
        }
        if (!isset($g5['g5_shop_item_table'])) {
            return false;
        }

        $item = sql_fetch("SELECT it_spec_remote_sw
                             FROM `{$g5['g5_shop_item_table']}`
                            WHERE it_id = '" . vpnlink_ikev2_sql_escape($itId) . "'
                            LIMIT 1", false);
        if (!is_array($item) || !isset($item['it_spec_remote_sw'])) {
            return false;
        }

        $json = (string)$item['it_spec_remote_sw'];
        if ($json === '') {
            return false;
        }

        $rows = json_decode($json, true);
        if (!is_array($rows)) {
            return false;
        }

        $changed = false;
        foreach ($rows as $idx => $row) {
            if (!is_array($row)) {
                continue;
            }

            $i = is_numeric($idx) ? (int)$idx : 0;
            $prog = '';
            if (isset($row['Program__' . $i])) {
                $prog = trim((string)$row['Program__' . $i]);
            }
            if ($prog === '') {
                $prog = vpnlink_ikev2_find_value($row, '/^Program__\\d+$/');
            }
            if ($prog === '' || strcasecmp($prog, $protocol) !== 0) {
                continue;
            }

            $rip = '';
            if (isset($row['RemoteIP__' . $i])) {
                $rip = trim((string)$row['RemoteIP__' . $i]);
            }
            if ($rip === '') {
                $rip = vpnlink_ikev2_find_value($row, '/^RemoteIP__\\d+$/');
            }
            if ($rip === '' || $rip !== $serverIp) {
                continue;
            }

            $idKey = $prog . '_ID__' . $i;
            if (!isset($row[$idKey])) {
                foreach ($row as $k => $v) {
                    if (preg_match('/_ID__\\d+$/', (string)$k)) {
                        $idKey = (string)$k;
                        break;
                    }
                }
            }

            $curId = isset($row[$idKey]) ? trim((string)$row[$idKey]) : '';
            if ($curId !== $oldUserId) {
                continue;
            }

            $rows[$idx][$idKey] = $newUserId;
            $changed = true;
        }

        if (!$changed) {
            return false;
        }

        $newJson = json_encode($rows, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        if (!is_string($newJson) || $newJson === '') {
            return false;
        }

        sql_query("UPDATE `{$g5['g5_shop_item_table']}`
                      SET it_spec_remote_sw = '" . vpnlink_ikev2_sql_escape($newJson) . "'
                    WHERE it_id = '" . vpnlink_ikev2_sql_escape($itId) . "'", false);

        return true;
    }
}

if (!function_exists('vpnlink_ikev2_append_order_memo')) {
    function vpnlink_ikev2_append_order_memo($odId, $memoLine)
    {
        global $g5;

        $odId = trim((string)$odId);
        $memoLine = trim((string)$memoLine);
        if ($odId === '' || $memoLine === '' || !isset($g5['g5_shop_order_table'])) {
            return;
        }

        $order = sql_fetch("SELECT od_memo
                              FROM `{$g5['g5_shop_order_table']}`
                             WHERE od_id = '" . vpnlink_ikev2_sql_escape($odId) . "'
                             LIMIT 1", false);
        $oldMemo = is_array($order) ? trim((string)($order['od_memo'] ?? '')) : '';
        if (strpos($oldMemo, $memoLine) !== false) {
            return;
        }

        $newMemo = $oldMemo;
        if ($newMemo !== '') {
            $newMemo .= "\n";
        }
        $newMemo .= '[' . date('Y-m-d H:i:s') . '] ' . $memoLine;

        sql_query("UPDATE `{$g5['g5_shop_order_table']}`
                      SET od_memo = '" . vpnlink_ikev2_sql_escape($newMemo) . "'
                    WHERE od_id = '" . vpnlink_ikev2_sql_escape($odId) . "'", false);
    }
}

if (!function_exists('vpnlink_ikev2_insert_id_conflict_history')) {
    function vpnlink_ikev2_insert_id_conflict_history($queueRow, $oldUserId, $newUserId, $triggerSource, $reason)
    {
        vpnlink_ikev2_create_tables();

        $sql = "INSERT INTO `app_ikev2_id_conflict_history`
                   SET queue_id = '" . (int)($queueRow['queue_id'] ?? 0) . "',
                       od_id = '" . vpnlink_ikev2_sql_escape((string)($queueRow['od_id'] ?? '')) . "',
                       it_id = '" . vpnlink_ikev2_sql_escape((string)($queueRow['it_id'] ?? '')) . "',
                       ct_id = '" . (int)($queueRow['ct_id'] ?? 0) . "',
                       server_ip = '" . vpnlink_ikev2_sql_escape((string)($queueRow['server_ip'] ?? '')) . "',
                       protocol = '" . vpnlink_ikev2_sql_escape((string)($queueRow['protocol'] ?? '')) . "',
                       original_user_id = '" . vpnlink_ikev2_sql_escape($oldUserId) . "',
                       resolved_user_id = '" . vpnlink_ikev2_sql_escape($newUserId) . "',
                       reason = '" . vpnlink_ikev2_sql_escape((string)$reason) . "',
                       trigger_source = '" . vpnlink_ikev2_sql_escape((string)$triggerSource) . "',
                       created_at = NOW()";
        sql_query($sql, false);
    }
}

if (!function_exists('vpnlink_ikev2_try_auto_suffix_on_scope_conflict')) {
    function vpnlink_ikev2_try_auto_suffix_on_scope_conflict($queueId, $triggerSource)
    {
        $queueId = (int)$queueId;
        if ($queueId <= 0) {
            return array('ok' => false, 'error' => 'invalid_queue_id');
        }

        $q = sql_fetch("SELECT * FROM `app_ikev2_provision_queue` WHERE queue_id = '" . $queueId . "' LIMIT 1", false);
        if (!is_array($q) || !isset($q['queue_id'])) {
            return array('ok' => false, 'error' => 'queue_not_found');
        }

        $oldUserId = trim((string)($q['vpn_user_id'] ?? ''));
        $serverIp = trim((string)($q['server_ip'] ?? ''));
        if (!vpnlink_ikev2_validate_user_id($oldUserId) || $serverIp === '') {
            return array('ok' => false, 'error' => 'invalid_queue_user_or_server');
        }

        $used = vpnlink_ikev2_collect_server_user_id_set($serverIp);
        $used[strtolower($oldUserId)] = true;

        $newUserId = '';
        for ($i = 2; $i <= 9999; $i++) {
            $cand = vpnlink_ikev2_build_suffix_candidate($oldUserId, $i);
            if ($cand === '' || !vpnlink_ikev2_validate_user_id($cand)) {
                continue;
            }
            if (!isset($used[strtolower($cand)])) {
                $newUserId = $cand;
                break;
            }
        }

        if ($newUserId === '') {
            return array('ok' => false, 'error' => 'suffix_candidate_exhausted');
        }

        $dup = sql_fetch("SELECT queue_id
                            FROM `app_ikev2_provision_queue`
                           WHERE od_id = '" . vpnlink_ikev2_sql_escape((string)$q['od_id']) . "'
                             AND it_id = '" . vpnlink_ikev2_sql_escape((string)$q['it_id']) . "'
                             AND protocol = '" . vpnlink_ikev2_sql_escape((string)$q['protocol']) . "'
                             AND server_ip = '" . vpnlink_ikev2_sql_escape((string)$q['server_ip']) . "'
                             AND vpn_user_id = '" . vpnlink_ikev2_sql_escape($newUserId) . "'
                           LIMIT 1", false);
        if (is_array($dup) && isset($dup['queue_id']) && (int)$dup['queue_id'] > 0) {
            return array('ok' => false, 'error' => 'queue_duplicate_after_suffix');
        }

        sql_query("UPDATE `app_ikev2_provision_queue`
                      SET vpn_user_id = '" . vpnlink_ikev2_sql_escape($newUserId) . "',
                          status = 'queued',
                          final_status = 'queued',
                          next_retry_at = NOW(),
                          last_result = 'queued',
                          last_error = NULL,
                          updated_at = NOW()
                    WHERE queue_id = '" . $queueId . "'", false);

        vpnlink_ikev2_update_item_remote_user_id(
            (string)($q['it_id'] ?? ''),
            (string)($q['server_ip'] ?? ''),
            (string)($q['protocol'] ?? ''),
            $oldUserId,
            $newUserId
        );

        $memoLine = 'VPN ID 자동변경: ' . $oldUserId . ' -> ' . $newUserId . ' (사유: 서버 수동등록 ID 충돌)';
        vpnlink_ikev2_append_order_memo((string)($q['od_id'] ?? ''), $memoLine);

        vpnlink_ikev2_insert_id_conflict_history($q, $oldUserId, $newUserId, (string)$triggerSource, 'scope_conflict');

        vpnlink_ikev2_insert_history(array(
            'queue_id' => $queueId,
            'od_id' => (string)($q['od_id'] ?? ''),
            'it_id' => (string)($q['it_id'] ?? ''),
            'event_type' => 'control',
            'attempt_no' => null,
            'event_status' => 'vpn_id_auto_suffix',
            'http_code' => null,
            'error_message' => 'scope_conflict_resolved:' . $oldUserId . '->' . $newUserId,
            'response_body' => null,
            'next_retry_at' => date('Y-m-d H:i:s')
        ));

        return array('ok' => true, 'old_user_id' => $oldUserId, 'new_user_id' => $newUserId);
    }
}

if (!function_exists('vpnlink_vpnid_sync_create_tables')) {
    function vpnlink_vpnid_sync_create_tables()
    {
        $sql = "CREATE TABLE IF NOT EXISTS `app_ikev2_vpnid_sync_queue` (
            `sync_id` bigint(20) NOT NULL AUTO_INCREMENT,
            `od_id` varchar(64) NOT NULL,
            `it_id` varchar(64) NOT NULL DEFAULT '',
            `trigger_source` varchar(64) NOT NULL DEFAULT '',
            `status` varchar(20) NOT NULL DEFAULT 'queued',
            `final_status` varchar(20) NOT NULL DEFAULT 'queued',
            `attempt_count` int(11) NOT NULL DEFAULT 0,
            `fail_count` int(11) NOT NULL DEFAULT 0,
            `last_attempt_at` datetime DEFAULT NULL,
            `next_retry_at` datetime DEFAULT NULL,
            `last_result` varchar(50) DEFAULT NULL,
            `last_error` text,
            `payload_json` mediumtext,
            `created_at` datetime NOT NULL,
            `updated_at` datetime NOT NULL,
            PRIMARY KEY (`sync_id`),
            UNIQUE KEY `uq_vpnid_sync_order_item` (`od_id`,`it_id`),
            KEY `idx_vpnid_sync_retry` (`status`,`next_retry_at`),
            KEY `idx_vpnid_sync_order` (`od_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

        sql_query($sql, false);
    }
}

if (!function_exists('vpnlink_vpnid_sync_get_table_columns')) {
    function vpnlink_vpnid_sync_get_table_columns($tableName)
    {
        static $cache = array();

        $tableName = trim((string)$tableName);
        if ($tableName === '') {
                        $retJson = (isset($ret['json']) && is_array($ret['json'])) ? $ret['json'] : array();
                                        $autoEnabled = isset($retJson['auto_enabled']) ? ((int)$retJson['auto_enabled'] === 1) : false;
                                        $sellerEnabledBeforeSync = isset($retJson['seller_enabled_before_sync']) ? ((int)$retJson['seller_enabled_before_sync'] === 1) : false;
                                        $sellerAccountStateBefore = isset($retJson['seller_account_state_before']) ? trim((string)$retJson['seller_account_state_before']) : '';
                        $sellerEnabledAfterSync = isset($retJson['seller_enabled_after_sync']) ? ((int)$retJson['seller_enabled_after_sync'] === 1) : false;
                        $sellerAccountState = isset($retJson['seller_account_state']) ? trim((string)$retJson['seller_account_state']) : '';

                return array(
                    'ok' => true,
                    'message' => isset($ret['message']) ? (string)$ret['message'] : 'synced',
                    'endpoint_url' => $endpointUrl,
                            'auto_enabled' => $autoEnabled,
                            'seller_enabled_before_sync' => $sellerEnabledBeforeSync,
                            'seller_account_state_before' => $sellerAccountStateBefore,
                            'seller_enabled_after_sync' => $sellerEnabledAfterSync,
                            'seller_account_state' => $sellerAccountState
                );
        }

        $cols = array();
        $res = sql_query(" DESCRIBE `" . vpnlink_ikev2_sql_escape($tableName) . "` ", false);
        if ($res) {
            while ($row = sql_fetch_array($res)) {
                if (!isset($row['Field'])) {
                    continue;
                }
                $field = trim((string)$row['Field']);
                if ($field === '') {
                    continue;
                }
                $cols[$field] = true;
            }
        }

        $cache[$tableName] = $cols;
        return $cols;
    }
}

if (!function_exists('vpnlink_vpnid_sync_build_common_assignments')) {
    function vpnlink_vpnid_sync_build_common_assignments($cols, $row, $forUpdate)
    {
        $assign = array();

        $ownerMbId = trim((string)($row['owner_mb_id'] ?? ''));
        $vpnUserId = trim((string)($row['vpn_user_id'] ?? ''));
        $mbId = trim((string)($row['mb_id'] ?? ''));
        if ($mbId === '') {
            $mbId = ($ownerMbId !== '') ? $ownerMbId : $vpnUserId;
        }

        $memberId = trim((string)($row['vpn_user_id'] ?? ''));
        if ($memberId === '') {
            $memberId = $vpnUserId;
        }

        $memberPwd = trim((string)($row['vpn_user_pwd'] ?? ''));
        $expire = trim((string)($row['vpn_id_expire'] ?? ''));
        $serverName = trim((string)($row['server_name'] ?? ''));
        $itemName = trim((string)($row['it_name'] ?? ''));
        $countryName = trim((string)($row['country_name'] ?? '-'));
        $countryShort = strtoupper(substr(trim((string)($row['country_name_short'] ?? 'KOR')), 0, 3));
        $memberMemo = trim((string)($row['vpn_memo'] ?? ''));
        $processType = trim((string)($row['process_type'] ?? 'shop_auto_sync'));
        $adminId = trim((string)($row['admin_id'] ?? 'shop_auto'));
        $adminPwd = trim((string)($row['admin_pwd'] ?? ''));
        $adminEmail = trim((string)($row['admin_email'] ?? ''));
        $isAdmin = trim((string)($row['is_admin'] ?? '0'));
        $remark = trim((string)($row['remark'] ?? ''));
        if ($remark === '') {
            $remark = $ownerMbId;
        }
        $ip = trim((string)($row['ip'] ?? ''));
        $device = trim((string)($row['device'] ?? ''));
        $enableVal = (int)($row['enable'] ?? 1);
        if (!in_array($enableVal, array(0, 1, 2), true)) {
            $enableVal = 1;
        }

        if (isset($cols['country_name'])) {
            $assign[] = "`country_name` = '" . vpnlink_ikev2_sql_escape($countryName) . "'";
        }
        if (isset($cols['country_name_short'])) {
            $assign[] = "`country_name_short` = '" . vpnlink_ikev2_sql_escape($countryShort) . "'";
        }
        if (isset($cols['ip']) && $ip !== '') {
            $assign[] = "`ip` = '" . vpnlink_ikev2_sql_escape($ip) . "'";
        }
        if (isset($cols['device']) && $device !== '') {
            $assign[] = "`device` = '" . vpnlink_ikev2_sql_escape($device) . "'";
        }
        if (isset($cols['mb_id']) && $mbId !== '') {
            $assign[] = "`mb_id` = '" . vpnlink_ikev2_sql_escape($mbId) . "'";
        }
        if (isset($cols['server_name']) && $serverName !== '') {
            $assign[] = "`server_name` = '" . vpnlink_ikev2_sql_escape($serverName) . "'";
        }
        if (isset($cols['it_name']) && $itemName !== '') {
            $assign[] = "`it_name` = '" . vpnlink_ikev2_sql_escape($itemName) . "'";
        }
        if (isset($cols['vpn_user_id']) && $memberId !== '') {
            $assign[] = "`vpn_user_id` = '" . vpnlink_ikev2_sql_escape($memberId) . "'";
        }
        if (isset($cols['vpn_user_pwd']) && $memberPwd !== '') {
            $assign[] = "`vpn_user_pwd` = '" . vpnlink_ikev2_sql_escape($memberPwd) . "'";
        }
        if (isset($cols['vpn_id_expire']) && $expire !== '') {
            $assign[] = "`vpn_id_expire` = '" . vpnlink_ikev2_sql_escape($expire) . "'";
        }
        if (isset($cols['vpn_memo'])) {
            $assign[] = "`vpn_memo` = '" . vpnlink_ikev2_sql_escape($memberMemo) . "'";
        }
        if (isset($cols['process_type'])) {
            $assign[] = "`process_type` = '" . vpnlink_ikev2_sql_escape($processType) . "'";
        }
        if (isset($cols['admin_id'])) {
            $assign[] = "`admin_id` = '" . vpnlink_ikev2_sql_escape($adminId) . "'";
        }
        if (isset($cols['admin_pwd'])) {
            $assign[] = "`admin_pwd` = '" . vpnlink_ikev2_sql_escape($adminPwd) . "'";
        }
        if (isset($cols['admin_email'])) {
            $assign[] = "`admin_email` = '" . vpnlink_ikev2_sql_escape($adminEmail) . "'";
        }
        if (isset($cols['is_admin'])) {
            $assign[] = "`is_admin` = '" . vpnlink_ikev2_sql_escape($isAdmin) . "'";
        }
        if (isset($cols['remark']) && $remark !== '') {
            $assign[] = "`remark` = '" . vpnlink_ikev2_sql_escape($remark) . "'";
        }
        if (isset($cols['enable'])) {
            $assign[] = "`enable` = '" . $enableVal . "'";
        }

        if (!$forUpdate && isset($cols['datetime'])) {
            $assign[] = "`datetime` = NOW()";
        }
        if (isset($cols['update_datetime'])) {
            $assign[] = "`update_datetime` = NOW()";
        }

        return $assign;
    }
}

if (!function_exists('vpnlink_vpnid_sync_upsert_generic_table')) {
    function vpnlink_vpnid_sync_upsert_generic_table($tableName, $row)
    {
        $tableName = trim((string)$tableName);
        if ($tableName === '') {
            return;
        }

        $cols = vpnlink_vpnid_sync_get_table_columns($tableName);
        if (count($cols) < 1) {
            return;
        }

        $serverName = trim((string)($row['server_name'] ?? ''));
        $memberId = trim((string)($row['vpn_user_id'] ?? ''));
        $mbId = trim((string)($row['mb_id'] ?? ''));
        if ($serverName === '' || $memberId === '') {
            return;
        }

        $where = "`server_name` = '" . vpnlink_ikev2_sql_escape($serverName) . "'"
               . " AND `vpn_user_id` = '" . vpnlink_ikev2_sql_escape($memberId) . "'";

        $existing = sql_fetch("SELECT `no` FROM `{$tableName}` WHERE {$where} ORDER BY `no` DESC LIMIT 1", false);

        $insertSet = vpnlink_vpnid_sync_build_common_assignments($cols, $row, false);
        $updateSet = vpnlink_vpnid_sync_build_common_assignments($cols, $row, true);

        if (is_array($existing) && isset($existing['no']) && (int)$existing['no'] > 0) {
            if (count($updateSet) > 0) {
                sql_query("UPDATE `{$tableName}` SET " . implode(', ', $updateSet) . " WHERE `no` = '" . (int)$existing['no'] . "'", false);
            }
            return;
        }

        if (count($insertSet) > 0) {
            sql_query("INSERT INTO `{$tableName}` SET " . implode(', ', $insertSet), false);
        }
    }
}

if (!function_exists('vpnlink_vpnid_sync_insert_log_table')) {
    function vpnlink_vpnid_sync_insert_log_table($tableName, $row)
    {
        $tableName = trim((string)$tableName);
        if ($tableName === '') {
            return;
        }

        $cols = vpnlink_vpnid_sync_get_table_columns($tableName);
        if (count($cols) < 1) {
            return;
        }

        $insertSet = vpnlink_vpnid_sync_build_common_assignments($cols, $row, false);
        if (count($insertSet) < 1) {
            return;
        }

        sql_query("INSERT INTO `{$tableName}` SET " . implode(', ', $insertSet), false);
    }
}

if (!function_exists('vpnlink_vpnid_sync_pick_plan_name')) {
    function vpnlink_vpnid_sync_pick_plan_name($ctOption, $ioId, $itemName, $itId)
    {
        $plan = trim((string)$ctOption);
        if ($plan === '') {
            $plan = trim((string)$ioId);
        }

        if ($plan === '') {
            return '';
        }

        $norm = function ($text) {
            $text = trim((string)$text);
            if ($text === '') {
                return '';
            }
            $text = preg_replace('/\s+/u', '', $text);
            return strtolower($text);
        };

        $planToken = $norm($plan);
        if ($planToken === '') {
            return '';
        }

        $itemNameToken = $norm($itemName);
        $itemIdToken = $norm($itId);

        if (($itemNameToken !== '' && $planToken === $itemNameToken)
            || ($itemIdToken !== '' && $planToken === $itemIdToken)) {
            return '';
        }

        return $plan;
    }
}

if (!function_exists('vpnlink_vpnid_sync_pick_country_name')) {
    function vpnlink_vpnid_sync_pick_country_name($itemRow, $serverIp)
    {
        $serverIp = trim((string)$serverIp);
        if ($serverIp === '') {
            return '-';
        }

        $remoteJson = isset($itemRow['it_spec_remote_sw']) ? (string)$itemRow['it_spec_remote_sw'] : '';
        if ($remoteJson === '') {
            return '-';
        }

        $rows = json_decode($remoteJson, true);
        if (!is_array($rows)) {
            return '-';
        }

        foreach ($rows as $idx => $row) {
            if (!is_array($row)) {
                continue;
            }

            $i = is_numeric($idx) ? (int)$idx : 0;
            $remoteIp = '';
            if (isset($row['RemoteIP__' . $i])) {
                $remoteIp = trim((string)$row['RemoteIP__' . $i]);
            }
            if ($remoteIp === '') {
                $remoteIp = vpnlink_ikev2_find_value($row, '/^RemoteIP__\\d+$/');
            }

            if ($remoteIp !== $serverIp) {
                continue;
            }

            $country = '';
            if (isset($row['RemoteIP_CountryName__' . $i])) {
                $country = trim((string)$row['RemoteIP_CountryName__' . $i]);
            }
            if ($country === '') {
                $country = vpnlink_ikev2_find_value($row, '/^RemoteIP_CountryName__\\d+$/');
            }

            if ($country === '') {
                return '-';
            }

            $countryUpper = strtoupper($country);
            if ($countryUpper === 'KOREA, REPUBLIC OF'
                || $countryUpper === 'REPUBLIC OF KOREA'
                || $countryUpper === 'SOUTH KOREA'
                || $countryUpper === 'KOREA') {
                return 'KOREA';
            }

            return $country;
        }

        return '-';
    }
}

if (!function_exists('vpnlink_vpnid_sync_upsert_row')) {
    function vpnlink_vpnid_sync_upsert_row($row)
    {
        $cols = vpnlink_vpnid_sync_get_table_columns('vpnid_list');
        if (count($cols) < 1) {
            return array(false, 'vpnid_list_table_missing');
        }

        $ownerMbId = trim((string)($row['owner_mb_id'] ?? ''));
        $vpnUserId = trim((string)($row['vpn_user_id'] ?? ''));
        $mbId = trim((string)($row['mb_id'] ?? ''));
        if ($mbId === '') {
            $mbId = $ownerMbId;
        }
        $memberId = ($vpnUserId !== '') ? $vpnUserId : trim((string)($row['vpn_user_id'] ?? ''));
        $memberPwd = trim((string)($row['vpn_user_pwd'] ?? ''));
        $expire = trim((string)($row['vpn_id_expire'] ?? ''));
        $serverName = trim((string)($row['server_name'] ?? ''));
        $itemName = trim((string)($row['it_name'] ?? ''));
        $countryName = trim((string)($row['country_name'] ?? '-'));
        $planName = trim((string)($row['plan_name'] ?? ''));
        $odId = trim((string)($row['od_id'] ?? ''));
        $memo = trim((string)($row['memo'] ?? ''));
        $ip = trim((string)($row['ip'] ?? ''));
        $device = trim((string)($row['device'] ?? ''));
        $memberMemo = trim((string)($row['vpn_memo'] ?? ''));
        $processType = trim((string)($row['process_type'] ?? 'shop_auto_sync'));
        $adminId = trim((string)($row['admin_id'] ?? 'shop_auto'));
        $adminPwd = trim((string)($row['admin_pwd'] ?? ''));
        $adminEmail = trim((string)($row['admin_email'] ?? ''));
        $isAdmin = trim((string)($row['is_admin'] ?? '0'));
        $remark = trim((string)($row['remark'] ?? ''));
        if ($remark === '') {
            $remark = $ownerMbId;
        }
        $enableVal = (int)($row['enable'] ?? 1);
        if (!in_array($enableVal, array(0, 1, 2), true)) {
            $enableVal = 1;
        }

        if ($memberId === '' || $serverName === '') {
            return array(false, 'empty_member_or_server');
        }

        $updateSet = array();
        $insertSet = array();

        if (isset($cols['vpn_user_id'])) {
            $insertSet[] = "`vpn_user_id` = '" . vpnlink_ikev2_sql_escape($memberId) . "'";
        }
        if (isset($cols['vpn_user_pwd']) && $memberPwd !== '') {
            $insertSet[] = "`vpn_user_pwd` = '" . vpnlink_ikev2_sql_escape($memberPwd) . "'";
            $updateSet[] = "`vpn_user_pwd` = '" . vpnlink_ikev2_sql_escape($memberPwd) . "'";
        }
        if (isset($cols['server_name'])) {
            $insertSet[] = "`server_name` = '" . vpnlink_ikev2_sql_escape($serverName) . "'";
            $updateSet[] = "`server_name` = '" . vpnlink_ikev2_sql_escape($serverName) . "'";
        }
        if (isset($cols['it_name']) && $itemName !== '') {
            $insertSet[] = "`it_name` = '" . vpnlink_ikev2_sql_escape($itemName) . "'";
            $updateSet[] = "`it_name` = '" . vpnlink_ikev2_sql_escape($itemName) . "'";
        }
        if (isset($cols['country_name'])) {
            $insertSet[] = "`country_name` = '" . vpnlink_ikev2_sql_escape($countryName) . "'";
            $updateSet[] = "`country_name` = '" . vpnlink_ikev2_sql_escape($countryName) . "'";
        }
        if (isset($cols['country_name_short'])) {
            $countryShort = strtoupper(substr(trim((string)($row['country_name_short'] ?? 'KOR')), 0, 3));
            $insertSet[] = "`country_name_short` = '" . vpnlink_ikev2_sql_escape($countryShort) . "'";
            $updateSet[] = "`country_name_short` = '" . vpnlink_ikev2_sql_escape($countryShort) . "'";
        }
        if (isset($cols['ip']) && $ip !== '') {
            $insertSet[] = "`ip` = '" . vpnlink_ikev2_sql_escape($ip) . "'";
            $updateSet[] = "`ip` = '" . vpnlink_ikev2_sql_escape($ip) . "'";
        }
        if (isset($cols['device']) && $device !== '') {
            $insertSet[] = "`device` = '" . vpnlink_ikev2_sql_escape($device) . "'";
            $updateSet[] = "`device` = '" . vpnlink_ikev2_sql_escape($device) . "'";
        }
        if (isset($cols['mb_id']) && $mbId !== '') {
            $insertSet[] = "`mb_id` = '" . vpnlink_ikev2_sql_escape($mbId) . "'";
            $updateSet[] = "`mb_id` = '" . vpnlink_ikev2_sql_escape($mbId) . "'";
        }
        if (isset($cols['remark']) && $ownerMbId !== '') {
            $insertSet[] = "`remark` = '" . vpnlink_ikev2_sql_escape($ownerMbId) . "'";
            $updateSet[] = "`remark` = '" . vpnlink_ikev2_sql_escape($ownerMbId) . "'";
        }
        if (isset($cols['vpn_memo'])) {
            $insertSet[] = "`vpn_memo` = '" . vpnlink_ikev2_sql_escape($memberMemo) . "'";
            $updateSet[] = "`vpn_memo` = '" . vpnlink_ikev2_sql_escape($memberMemo) . "'";
        }
        if (isset($cols['process_type'])) {
            $insertSet[] = "`process_type` = '" . vpnlink_ikev2_sql_escape($processType) . "'";
            $updateSet[] = "`process_type` = '" . vpnlink_ikev2_sql_escape($processType) . "'";
        }
        if (isset($cols['admin_id'])) {
            $insertSet[] = "`admin_id` = '" . vpnlink_ikev2_sql_escape($adminId) . "'";
            $updateSet[] = "`admin_id` = '" . vpnlink_ikev2_sql_escape($adminId) . "'";
        }
        if (isset($cols['admin_pwd'])) {
            $insertSet[] = "`admin_pwd` = '" . vpnlink_ikev2_sql_escape($adminPwd) . "'";
            $updateSet[] = "`admin_pwd` = '" . vpnlink_ikev2_sql_escape($adminPwd) . "'";
        }
        if (isset($cols['admin_email'])) {
            $insertSet[] = "`admin_email` = '" . vpnlink_ikev2_sql_escape($adminEmail) . "'";
            $updateSet[] = "`admin_email` = '" . vpnlink_ikev2_sql_escape($adminEmail) . "'";
        }
        if (isset($cols['is_admin'])) {
            $insertSet[] = "`is_admin` = '" . vpnlink_ikev2_sql_escape($isAdmin) . "'";
            $updateSet[] = "`is_admin` = '" . vpnlink_ikev2_sql_escape($isAdmin) . "'";
        }
        if (isset($cols['enable'])) {
            $insertSet[] = "`enable` = '" . $enableVal . "'";
            $updateSet[] = "`enable` = '" . $enableVal . "'";
        }
        if (isset($cols['od_id']) && $odId !== '') {
            $insertSet[] = "`od_id` = '" . vpnlink_ikev2_sql_escape($odId) . "'";
            $updateSet[] = "`od_id` = '" . vpnlink_ikev2_sql_escape($odId) . "'";
        }
        if (isset($cols['plan_name']) && $planName !== '') {
            $insertSet[] = "`plan_name` = '" . vpnlink_ikev2_sql_escape($planName) . "'";
            $updateSet[] = "`plan_name` = '" . vpnlink_ikev2_sql_escape($planName) . "'";
        }
        if (isset($cols['memo']) && $memo !== '') {
            $insertSet[] = "`memo` = '" . vpnlink_ikev2_sql_escape($memo) . "'";
            $updateSet[] = "`memo` = '" . vpnlink_ikev2_sql_escape($memo) . "'";
        }
        if (isset($cols['datetime'])) {
            $insertSet[] = "`datetime` = NOW()";
        }
        if (isset($cols['update_datetime'])) {
            $insertSet[] = "`update_datetime` = NOW()";
            $updateSet[] = "`update_datetime` = NOW()";
        }

        if (count($insertSet) < 1) {
            return array(false, 'no_assignable_columns');
        }

        // Strict identity match first to avoid clobbering other VPN accounts
        // that share the same owner mb_id/remark on the same server.
        $existingWhere = "`server_name` = '" . vpnlink_ikev2_sql_escape($serverName) . "'"
                   . " AND `vpn_user_id` = '" . vpnlink_ikev2_sql_escape($memberId) . "'";

        $existing = sql_fetch("SELECT `no`, `vpn_id_expire`, `enable` FROM `vpnid_list`
                                WHERE {$existingWhere}
                                ORDER BY `no` DESC
                                LIMIT 1", false);

        // [FIX 2026-05-08]
        // 누적 연장 보장: 활성 동기화(enable=1)에서는 기존 만료일이 더 늦다면 절대 줄이지 않는다.
        // (지연 재시도/이전 주문 재동기화가 최신 만료일을 덮어쓰는 문제 방지)
        $effectiveExpire = $expire;
        if ($effectiveExpire === '' && is_array($existing)) {
            $effectiveExpire = trim((string)($existing['vpn_id_expire'] ?? ''));
        }
        if ($enableVal === 1 && $effectiveExpire !== '' && is_array($existing)) {
            $incomingTs = strtotime($effectiveExpire);
            $existingExpire = trim((string)($existing['vpn_id_expire'] ?? ''));
            $existingTs = ($existingExpire !== '') ? strtotime($existingExpire) : false;
            if ($incomingTs !== false && $incomingTs > 0 && $existingTs !== false && $existingTs > $incomingTs) {
                $effectiveExpire = date('Y-m-d H:i:s', $existingTs);
            }
        }

        if (isset($cols['vpn_id_expire']) && $effectiveExpire !== '') {
            $insertSet[] = "`vpn_id_expire` = '" . vpnlink_ikev2_sql_escape($effectiveExpire) . "'";
            $updateSet[] = "`vpn_id_expire` = '" . vpnlink_ikev2_sql_escape($effectiveExpire) . "'";
        }

        if (is_array($existing) && isset($existing['no']) && (int)$existing['no'] > 0) {
            if (count($updateSet) > 0) {
                $sql = "UPDATE `vpnid_list` SET " . implode(', ', $updateSet) . "
                         WHERE `no` = '" . (int)$existing['no'] . "'";
                sql_query($sql, false);
            }

            $mirrorRow = array_merge($row, array(
                'member_id' => $memberId,
                'mb_id' => $mbId,
                'remark' => $remark,
                'ip' => $ip,
                'device' => $device,
                'vpn_memo' => $memberMemo,
                'process_type' => $processType,
                'admin_id' => $adminId,
                'admin_pwd' => $adminPwd,
                'admin_email' => $adminEmail,
                'is_admin' => $isAdmin,
                'country_name' => $countryName,
                'server_name' => $serverName,
                'it_name' => $itemName,
                'vpn_user_pwd' => $memberPwd,
                'vpn_id_expire' => $effectiveExpire,
                'enable' => $enableVal
            ));
            vpnlink_vpnid_sync_insert_log_table('vpnid_list_log', $mirrorRow);
            return array(true, 'updated');
        }

        $sql = "INSERT INTO `vpnid_list` SET " . implode(', ', $insertSet);
        sql_query($sql, false);

        $mirrorRow = array_merge($row, array(
            'member_id' => $memberId,
            'mb_id' => $mbId,
            'remark' => $remark,
            'ip' => $ip,
            'device' => $device,
            'vpn_memo' => $memberMemo,
            'process_type' => $processType,
            'admin_id' => $adminId,
            'admin_pwd' => $adminPwd,
            'admin_email' => $adminEmail,
            'is_admin' => $isAdmin,
            'country_name' => $countryName,
            'server_name' => $serverName,
            'it_name' => $itemName,
            'vpn_user_pwd' => $memberPwd,
            'vpn_id_expire' => $effectiveExpire,
            'enable' => $enableVal
        ));
        vpnlink_vpnid_sync_insert_log_table('vpnid_list_log', $mirrorRow);

        return array(true, 'inserted');
    }
}

if (!function_exists('vpnlink_vpnid_sync_apply_order')) {
    function vpnlink_vpnid_sync_apply_order($odId, $itId, $triggerSource)
    {
        global $g5;

        $odId = trim((string)$odId);
        $itId = trim((string)$itId);
        $triggerSource = trim((string)$triggerSource);

        if ($odId === '') {
            return array('ok' => false, 'error' => 'empty_od_id');
        }

        if (!isset($g5['g5_shop_cart_table']) || !isset($g5['g5_shop_item_table']) || !isset($g5['g5_shop_order_table'])) {
            return array('ok' => false, 'error' => 'missing_shop_tables');
        }

        $where = "c.od_id = '" . vpnlink_ikev2_sql_escape($odId) . "'";
        if ($itId !== '') {
            $where .= " AND c.it_id = '" . vpnlink_ikev2_sql_escape($itId) . "'";
        }

        $sql = "SELECT c.ct_id, c.od_id, c.it_id, c.mb_id, c.ct_status, c.ct_option, c.io_id,
                       c.real_refund_datetime,
                       i.it_spec_sales_type, i.it_spec_remote_sw, i.ca_id, i.ca_id2, i.ca_id3,
                       i.it_spec_to_datetime, i.it_name, i.it_spec_buyer_mb_id,
                       o.mb_id AS od_mb_id
                  FROM `{$g5['g5_shop_cart_table']}` c
             LEFT JOIN `{$g5['g5_shop_item_table']}` i ON c.it_id = i.it_id
             LEFT JOIN `{$g5['g5_shop_order_table']}` o ON c.od_id = o.od_id
                 WHERE {$where}
              ORDER BY c.ct_id DESC";

        $res = sql_query($sql, false);
        if (!$res) {
            return array('ok' => false, 'error' => 'query_failed');
        }

        $synced = 0;
        $scanned = 0;
        $failReason = '';

        for ($i = 0; $row = sql_fetch_array($res); $i++) {
            if (!is_array($row)) {
                continue;
            }

            $scanned++;
            $targets = vpnlink_ikev2_extract_targets_from_item($row);
            if (count($targets) < 1) {
                continue;
            }

            $memberId = trim((string)($row['od_mb_id'] ?? ''));
            if ($memberId === '') {
                $memberId = trim((string)($row['mb_id'] ?? ''));
            }
            if ($memberId === '') {
                $memberId = trim((string)($row['it_spec_buyer_mb_id'] ?? ''));
            }

            if ($memberId === '') {
                $failReason = 'empty_member_id';
                continue;
            }

            $expire = vpnlink_ikev2_resolve_order_expire($odId, (string)$row['it_id'], (int)$row['ct_id']);
            if ($expire === '') {
                $expire = vpnlink_ikev2_normalize_datetime_text(isset($row['it_spec_to_datetime']) ? $row['it_spec_to_datetime'] : '');
            }

            $planName = vpnlink_vpnid_sync_pick_plan_name(
                isset($row['ct_option']) ? $row['ct_option'] : '',
                isset($row['io_id']) ? $row['io_id'] : '',
                isset($row['it_name']) ? $row['it_name'] : '',
                isset($row['it_id']) ? $row['it_id'] : ''
            );

            $memo = vpnlink_ikev2_build_order_memo($odId, (string)$row['it_id'], (int)$row['ct_id'], $memberId);
            if ($memo === '') {
                $memo = 'vpnid sync source=' . ($triggerSource !== '' ? $triggerSource : 'unknown');
            }

            $ctStatus = trim((string)($row['ct_status'] ?? ''));
            $refundDatetime = vpnlink_ikev2_normalize_datetime_text(isset($row['real_refund_datetime']) ? $row['real_refund_datetime'] : '');
            $refundTs = ($refundDatetime !== '') ? strtotime($refundDatetime) : false;
            $isRefundTerminated = ($refundTs !== false && $refundTs > 0);

            // 환불/해지 완료는 enable=2로 고정해 앱 목록에는 남기고(수동 삭제 전), 연결은 차단한다.
            if ($isRefundTerminated) {
                $enableVal = 2;
                $expireTsCurrent = ($expire !== '') ? strtotime($expire) : false;
                if ($expireTsCurrent === false || $expireTsCurrent > $refundTs) {
                    $expire = $refundDatetime;
                }
            } else {
                $enableVal = (in_array($ctStatus, array('취소', '반품'), true)) ? 0 : 1;
                if ($enableVal === 1 && $expire !== '') {
                    $expireTs = strtotime($expire);
                    if ($expireTs !== false && $expireTs > 0 && $expireTs <= time()) {
                        $enableVal = 0;
                    }
                }
            }
            $clientIp = '';
            if (function_exists('get_real_client_ip')) {
                $clientIp = trim((string)get_real_client_ip());
            }
            if ($clientIp === '' && isset($_SERVER['REMOTE_ADDR'])) {
                $clientIp = trim((string)$_SERVER['REMOTE_ADDR']);
            }
            $clientDevice = isset($_SERVER['HTTP_USER_AGENT']) ? trim((string)$_SERVER['HTTP_USER_AGENT']) : '';
            if ($clientDevice === '') {
                $clientDevice = 'CLI';
            }
            $memberMemo = trim((string)($row['ct_option'] ?? ''));
            if ($memberMemo === '') {
                $memberMemo = $memberId;
            }
            $processType = 'shop_auto_sync';
            $adminId = 'shop_auto';
            $adminPwd = '';
            $adminEmail = '';
            $isAdmin = '0';

            foreach ($targets as $t) {
                $serverName = trim((string)$t['server_ip']);
                $memberPwd = trim((string)$t['vpn_user_pw']);
                if ($serverName === '') {
                    continue;
                }

                $countryName = vpnlink_vpnid_sync_pick_country_name($row, $serverName);

                $upsert = vpnlink_vpnid_sync_upsert_row(array(
                    'owner_mb_id' => $memberId,
                    'mb_id' => $memberId,
                    'vpn_user_id' => isset($t['vpn_user_id']) ? (string)$t['vpn_user_id'] : '',
                    'vpn_user_pwd' => $memberPwd,
                    'vpn_id_expire' => $expire,
                    'server_name' => $serverName,
                    'it_name' => isset($row['it_name']) ? (string)$row['it_name'] : '',
                    'country_name' => $countryName,
                    'ip' => $clientIp,
                    'device' => $clientDevice,
                    'vpn_memo' => $memberMemo,
                    'process_type' => $processType,
                    'admin_id' => $adminId,
                    'admin_pwd' => $adminPwd,
                    'admin_email' => $adminEmail,
                    'is_admin' => $isAdmin,
                    'plan_name' => $planName,
                    'od_id' => $odId,
                    'memo' => $memo,
                    'remark' => $memberId,
                    'enable' => $enableVal
                ));

                if (empty($upsert[0])) {
                    $failReason = isset($upsert[1]) ? (string)$upsert[1] : 'vpnid_upsert_failed';
                    continue;
                }

                $synced++;
            }
        }

        if ($synced > 0) {
            return array('ok' => true, 'status' => 'success', 'scanned' => $scanned, 'synced' => $synced);
        }

        if ($failReason !== '') {
            return array('ok' => false, 'error' => $failReason, 'scanned' => $scanned, 'synced' => 0);
        }

        return array('ok' => true, 'status' => 'noop', 'scanned' => $scanned, 'synced' => 0);
    }
}

if (!function_exists('vpnlink_vpnid_sync_attempt_queue')) {
    function vpnlink_vpnid_sync_attempt_queue($syncId)
    {
        $syncId = (int)$syncId;
        if ($syncId <= 0) {
            return array('ok' => false, 'error' => 'invalid_sync_id');
        }

        $q = sql_fetch("SELECT * FROM `app_ikev2_vpnid_sync_queue` WHERE sync_id = '" . $syncId . "' LIMIT 1", false);
        if (!is_array($q) || !isset($q['sync_id'])) {
            return array('ok' => false, 'error' => 'sync_queue_not_found');
        }

        $ret = vpnlink_vpnid_sync_apply_order(
            isset($q['od_id']) ? (string)$q['od_id'] : '',
            isset($q['it_id']) ? (string)$q['it_id'] : '',
            isset($q['trigger_source']) ? (string)$q['trigger_source'] : ''
        );

        $attemptNo = ((int)$q['attempt_count']) + 1;

        if (!empty($ret['ok'])) {
            $resultStatus = (isset($ret['status']) && (string)$ret['status'] === 'noop') ? 'noop' : 'success';
            $sql = "UPDATE `app_ikev2_vpnid_sync_queue`
                       SET attempt_count = attempt_count + 1,
                           last_attempt_at = NOW(),
                           next_retry_at = NULL,
                           status = 'success',
                           final_status = 'success',
                           last_result = '" . vpnlink_ikev2_sql_escape($resultStatus) . "',
                           last_error = NULL,
                           updated_at = NOW()
                     WHERE sync_id = '" . $syncId . "'";
            sql_query($sql, false);

            return array(
                'ok' => true,
                'status' => $resultStatus,
                'attempt_no' => $attemptNo,
                'synced' => isset($ret['synced']) ? (int)$ret['synced'] : 0,
                'scanned' => isset($ret['scanned']) ? (int)$ret['scanned'] : 0
            );
        }

        $error = isset($ret['error']) ? (string)$ret['error'] : 'sync_failed';
        $nextRetryAt = date('Y-m-d H:i:s', time() + 60);

        $sql = "UPDATE `app_ikev2_vpnid_sync_queue`
                   SET attempt_count = attempt_count + 1,
                       fail_count = fail_count + 1,
                       last_attempt_at = NOW(),
                       next_retry_at = '" . vpnlink_ikev2_sql_escape($nextRetryAt) . "',
                       status = 'retrying',
                       final_status = 'retrying',
                       last_result = 'retrying',
                       last_error = '" . vpnlink_ikev2_sql_escape($error) . "',
                       updated_at = NOW()
                 WHERE sync_id = '" . $syncId . "'";
        sql_query($sql, false);

        return array('ok' => false, 'error' => $error, 'attempt_no' => $attemptNo, 'next_retry_at' => $nextRetryAt);
    }
}

if (!function_exists('vpnlink_vpnid_sync_upsert_by_order')) {
    function vpnlink_vpnid_sync_upsert_by_order($odId, $itId, $triggerSource, $payload = array())
    {
        $odId = trim((string)$odId);
        $itId = trim((string)$itId);
        $triggerSource = trim((string)$triggerSource);

        if ($odId === '') {
            return array('ok' => false, 'error' => 'empty_od_id');
        }

        vpnlink_vpnid_sync_create_tables();

        $payloadJson = '';
        if (is_array($payload) && count($payload) > 0) {
            $payloadJson = json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
            if (!is_string($payloadJson)) {
                $payloadJson = '';
            }
        }

        $sql = "INSERT INTO `app_ikev2_vpnid_sync_queue`
                   SET od_id = '" . vpnlink_ikev2_sql_escape($odId) . "',
                       it_id = '" . vpnlink_ikev2_sql_escape($itId) . "',
                       trigger_source = '" . vpnlink_ikev2_sql_escape($triggerSource) . "',
                       status = 'queued',
                       final_status = 'queued',
                       next_retry_at = NOW(),
                       last_result = 'queued',
                       last_error = NULL,
                       payload_json = " . (($payloadJson === '') ? 'NULL' : "'" . vpnlink_ikev2_sql_escape(substr($payloadJson, 0, 60000)) . "'") . ",
                       created_at = NOW(),
                       updated_at = NOW()
                 ON DUPLICATE KEY UPDATE
                       trigger_source = VALUES(trigger_source),
                       status = 'queued',
                       final_status = 'queued',
                       next_retry_at = NOW(),
                       last_result = 'queued',
                       last_error = NULL,
                       payload_json = VALUES(payload_json),
                       updated_at = NOW()";
        sql_query($sql, false);

        $row = sql_fetch("SELECT sync_id FROM `app_ikev2_vpnid_sync_queue`
                           WHERE od_id = '" . vpnlink_ikev2_sql_escape($odId) . "'
                             AND it_id = '" . vpnlink_ikev2_sql_escape($itId) . "'
                           LIMIT 1", false);
        if (!is_array($row) || !isset($row['sync_id'])) {
            return array('ok' => false, 'error' => 'sync_queue_row_not_found');
        }

        $syncId = (int)$row['sync_id'];
        if ($syncId <= 0) {
            return array('ok' => false, 'error' => 'invalid_sync_id');
        }

        $attempt = vpnlink_vpnid_sync_attempt_queue($syncId);
        if (empty($attempt['ok'])) {
            return array(
                'ok' => false,
                'sync_id' => $syncId,
                'error' => isset($attempt['error']) ? $attempt['error'] : 'sync_attempt_failed',
                'queued' => true
            );
        }

        return array(
            'ok' => true,
            'sync_id' => $syncId,
            'status' => isset($attempt['status']) ? $attempt['status'] : 'success',
            'synced' => isset($attempt['synced']) ? (int)$attempt['synced'] : 0,
            'scanned' => isset($attempt['scanned']) ? (int)$attempt['scanned'] : 0
        );
    }
}

if (!function_exists('vpnlink_vpnid_sync_retry_due_jobs')) {
    function vpnlink_vpnid_sync_retry_due_jobs($limit)
    {
        $limit = (int)$limit;
        if ($limit <= 0) {
            $limit = 20;
        }

        vpnlink_vpnid_sync_create_tables();

        $sql = "SELECT sync_id
                  FROM `app_ikev2_vpnid_sync_queue`
                 WHERE final_status <> 'success'
                   AND (next_retry_at IS NULL OR next_retry_at <= NOW())
                 ORDER BY COALESCE(next_retry_at, created_at) ASC, sync_id ASC
                 LIMIT " . $limit;

        $res = sql_query($sql, false);

        $total = 0;
        $success = 0;
        $fail = 0;

        for ($i = 0; $row = sql_fetch_array($res); $i++) {
            if (!is_array($row) || !isset($row['sync_id'])) {
                continue;
            }
            $total++;
            $ret = vpnlink_vpnid_sync_attempt_queue((int)$row['sync_id']);
            if (!empty($ret['ok'])) {
                $success++;
            } else {
                $fail++;
            }
        }

        return array(
            'processed' => $total,
            'success' => $success,
            'fail' => $fail
        );
    }
}

if (!function_exists('vpnlink_ikev2_find_value')) {
    function vpnlink_ikev2_find_value($row, $pattern)
    {
        foreach ((array)$row as $k => $v) {
            if (preg_match($pattern, (string)$k)) {
                $vv = trim((string)$v);
                if ($vv !== '') {
                    return $vv;
                }
            }
        }

        return '';
    }
}

if (!function_exists('vpnlink_ikev2_is_target_program')) {
    function vpnlink_ikev2_is_target_program($program)
    {
        $program = trim((string)$program);
        if ($program === '') {
            return false;
        }

        // 허용 예시: IKEv2-Client, IKEv2 VPN Client, IKEv2-VPN-Client
        $normalized = strtolower(preg_replace('/[^a-z0-9]+/i', '', $program));
        if ($normalized === '') {
            return false;
        }

        return (strpos($normalized, 'ikev2') !== false && strpos($normalized, 'client') !== false);
    }
}

if (!function_exists('vpnlink_ikev2_item_is_vpn_or_proxy')) {
    function vpnlink_ikev2_item_is_vpn_or_proxy($itemRow)
    {
        $salesType = strtolower(trim((string)$itemRow['it_spec_sales_type']));
        if ($salesType === 'vpn-mall' || $salesType === 'proxy-mall') {
            return true;
        }

        $catList = array(
            isset($itemRow['ca_id']) ? (string)$itemRow['ca_id'] : '',
            isset($itemRow['ca_id2']) ? (string)$itemRow['ca_id2'] : '',
            isset($itemRow['ca_id3']) ? (string)$itemRow['ca_id3'] : ''
        );

        foreach ($catList as $ca) {
            $ca = trim($ca);
            if ($ca === '') {
                continue;
            }

            if (strpos($ca, '30') === 0 || strpos($ca, '40') === 0) {
                return true;
            }
        }

        return false;
    }
}

if (!function_exists('vpnlink_ikev2_extract_targets_from_item')) {
    function vpnlink_ikev2_extract_targets_from_item($itemRow)
    {
        $targets = array();

        if (!vpnlink_ikev2_item_is_vpn_or_proxy($itemRow)) {
            return $targets;
        }

        $remoteJson = isset($itemRow['it_spec_remote_sw']) ? (string)$itemRow['it_spec_remote_sw'] : '';
        if ($remoteJson === '') {
            return $targets;
        }

        $remoteRows = json_decode($remoteJson, true);
        if (!is_array($remoteRows)) {
            return $targets;
        }

        foreach ($remoteRows as $idx => $row) {
            if (!is_array($row)) {
                continue;
            }

            $i = is_numeric($idx) ? (int)$idx : 0;
            $program = '';
            if (isset($row['Program__' . $i])) {
                $program = trim((string)$row['Program__' . $i]);
            }
            if ($program === '') {
                $program = vpnlink_ikev2_find_value($row, '/^Program__\\d+$/');
            }
            // RPC(원격제어 일반/경매)는 상품 필터에서 제외하며,
            // VPN/PROXY 계열은 프로그램명이 IKEv2가 아니어도 계정정보가 있으면 대상에 포함
            if ($program === '') {
                continue;
            }

            $serverIp = '';
            if (isset($row['RemoteIP__' . $i])) {
                $serverIp = trim((string)$row['RemoteIP__' . $i]);
            }
            if ($serverIp === '') {
                $serverIp = vpnlink_ikev2_find_value($row, '/^RemoteIP__\\d+$/');
            }

            $userId = '';
            $pwd = '';
            if (isset($row[$program . '_ID__' . $i])) {
                $userId = trim((string)$row[$program . '_ID__' . $i]);
            }
            if (isset($row[$program . '_PWD__' . $i])) {
                $pwd = trim((string)$row[$program . '_PWD__' . $i]);
            }
            if ($userId === '') {
                $userId = vpnlink_ikev2_find_value($row, '/_ID__\\d+$/');
            }
            if ($pwd === '') {
                $pwd = vpnlink_ikev2_find_value($row, '/_PWD__\\d+$/');
            }

            if ($serverIp === '' || $userId === '' || $pwd === '') {
                continue;
            }

            $targets[] = array(
                'protocol' => $program,
                'server_ip' => $serverIp,
                'endpoint_url' => vpnlink_ikev2_build_endpoint($serverIp),
                'vpn_user_id' => $userId,
                'vpn_user_pw' => $pwd
            );
        }

        return $targets;
    }
}

if (!function_exists('vpnlink_ikev2_insert_history')) {
    function vpnlink_ikev2_insert_history($row)
    {
        $sql = "INSERT INTO `app_ikev2_provision_history`
                   SET queue_id = '" . (int)$row['queue_id'] . "',
                       od_id = '" . vpnlink_ikev2_sql_escape($row['od_id']) . "',
                       it_id = '" . vpnlink_ikev2_sql_escape($row['it_id']) . "',
                       event_type = '" . vpnlink_ikev2_sql_escape($row['event_type']) . "',
                       attempt_no = " . (($row['attempt_no'] === null) ? 'NULL' : (int)$row['attempt_no']) . ",
                       event_status = " . (($row['event_status'] === null) ? 'NULL' : "'" . vpnlink_ikev2_sql_escape($row['event_status']) . "'") . ",
                       http_code = " . (($row['http_code'] === null) ? 'NULL' : (int)$row['http_code']) . ",
                       error_message = " . (($row['error_message'] === null) ? 'NULL' : "'" . vpnlink_ikev2_sql_escape($row['error_message']) . "'") . ",
                       response_body = " . (($row['response_body'] === null) ? 'NULL' : "'" . vpnlink_ikev2_sql_escape($row['response_body']) . "'") . ",
                       next_retry_at = " . (($row['next_retry_at'] === null) ? 'NULL' : "'" . vpnlink_ikev2_sql_escape($row['next_retry_at']) . "'") . ",
                       created_at = '" . G5_TIME_YMDHIS . "'";

        sql_query($sql, false);
    }
}

if (!function_exists('vpnlink_ikev2_insert_control_history_by_item')) {
    function vpnlink_ikev2_insert_control_history_by_item($itId, $eventStatus, $actorMbId, $actorName)
    {
        $itId = trim((string)$itId);
        $eventStatus = trim((string)$eventStatus);
        $actorMbId = trim((string)$actorMbId);
        $actorName = trim((string)$actorName);

        if ($itId === '' || $eventStatus === '') {
            return;
        }

        $rows = sql_query("SELECT queue_id, od_id, it_id, final_status
                             FROM `app_ikev2_provision_queue`
                            WHERE it_id = '" . vpnlink_ikev2_sql_escape($itId) . "'
                            ORDER BY queue_id ASC", false);

        if (!$rows) {
            return;
        }

        $actorText = $actorMbId;
        if ($actorName !== '' && $actorMbId !== '') {
            $actorText = $actorName . ' (' . $actorMbId . ')';
        } else if ($actorName !== '') {
            $actorText = $actorName;
        }
        if ($actorText === '') {
            $actorText = 'system';
        }

        for ($i = 0; $q = sql_fetch_array($rows); $i++) {
            if (!$q || !isset($q['queue_id'])) {
                continue;
            }

            $queueId = (int)$q['queue_id'];
            if ($queueId <= 0) {
                continue;
            }

            $msg = '자동재시도 제어: ' . $eventStatus
                . ' / 처리자: ' . $actorText
                . ' / 당시상태: ' . trim((string)$q['final_status']);

            vpnlink_ikev2_insert_history(array(
                'queue_id' => $queueId,
                'od_id' => isset($q['od_id']) ? $q['od_id'] : '',
                'it_id' => isset($q['it_id']) ? $q['it_id'] : $itId,
                'event_type' => 'control',
                'attempt_no' => null,
                'event_status' => $eventStatus,
                'http_code' => null,
                'error_message' => $msg,
                'response_body' => null,
                'next_retry_at' => null
            ));
        }
    }
}

if (!function_exists('vpnlink_ikev2_http_post')) {
    function vpnlink_ikev2_http_post($url, $postData)
    {
        $ret = array(
            'http_code' => 0,
            'body' => '',
            'transport_error' => '',
            'requested_url' => (string)$url,
            'effective_url' => '',
            'redirect_url' => '',
            'response_headers' => array()
        );

        if (function_exists('curl_init')) {
            $ch = curl_init();
            curl_setopt($ch, CURLOPT_URL, $url);
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
            curl_setopt($ch, CURLOPT_POST, true);
            curl_setopt($ch, CURLOPT_POSTFIELDS, http_build_query($postData));
            curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 10);
            curl_setopt($ch, CURLOPT_TIMEOUT, 20);
            curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
            curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
            curl_setopt($ch, CURLOPT_FOLLOWLOCATION, false);
            curl_setopt($ch, CURLOPT_HEADER, true);
            $body = curl_exec($ch);

            if ($body === false) {
                $ret['transport_error'] = (string)curl_error($ch);
            } else {
                $headerSize = (int)curl_getinfo($ch, CURLINFO_HEADER_SIZE);
                $rawHeader = (string)substr((string)$body, 0, $headerSize);
                $ret['body'] = (string)substr((string)$body, $headerSize);

                if ($rawHeader !== '') {
                    $lines = preg_split('/\r\n|\n|\r/', $rawHeader);
                    foreach ((array)$lines as $line) {
                        $line = trim((string)$line);
                        if ($line === '' || strpos($line, ':') === false) {
                            continue;
                        }

                        list($hk, $hv) = explode(':', $line, 2);
                        $hk = strtolower(trim((string)$hk));
                        $hv = trim((string)$hv);
                        if ($hk === '') {
                            continue;
                        }

                        $ret['response_headers'][$hk] = $hv;
                        if ($hk === 'location' && $ret['redirect_url'] === '') {
                            $ret['redirect_url'] = $hv;
                        }
                    }
                }
            }

            $ret['http_code'] = (int)curl_getinfo($ch, CURLINFO_HTTP_CODE);
            $ret['effective_url'] = (string)curl_getinfo($ch, CURLINFO_EFFECTIVE_URL);

            if ($ret['redirect_url'] === '' && defined('CURLINFO_REDIRECT_URL')) {
                $redir = (string)curl_getinfo($ch, CURLINFO_REDIRECT_URL);
                if ($redir !== '') {
                    $ret['redirect_url'] = $redir;
                }
            }
            curl_close($ch);

            return $ret;
        }

        $context = stream_context_create(array(
            'http' => array(
                'method' => 'POST',
                'header' => "Content-Type: application/x-www-form-urlencoded\\r\\n",
                'content' => http_build_query($postData),
                'timeout' => 20,
                'ignore_errors' => true
            )
        ));

        $body = @file_get_contents($url, false, $context);
        if ($body === false) {
            $ret['transport_error'] = 'file_get_contents_failed';
            return $ret;
        }

        $ret['body'] = (string)$body;
        if (isset($http_response_header) && is_array($http_response_header)) {
            foreach ($http_response_header as $line) {
                if (preg_match('#^HTTP/\\S+\\s+(\\d{3})#', $line, $m)) {
                    $ret['http_code'] = (int)$m[1];
                    continue;
                }
                if (stripos((string)$line, 'Location:') === 0 && $ret['redirect_url'] === '') {
                    $ret['redirect_url'] = trim((string)substr((string)$line, strlen('Location:')));
                }
            }

            $ret['response_headers'] = $http_response_header;
        }

        $ret['effective_url'] = (string)$url;

        return $ret;
    }
}

if (!function_exists('vpnlink_ikev2_verify_response')) {
    function vpnlink_ikev2_verify_response($rawBody, $requestId, $secret)
    {
        $body = trim((string)$rawBody);
        if ($body === '') {
            return array(false, 'empty_response', '');
        }

        $json = json_decode($body, true);
        if (!is_array($json)) {
            return array(false, 'invalid_json_response', '');
        }

        $status = isset($json['status']) ? trim((string)$json['status']) : '';
        $echoValue = isset($json['echo']) ? trim((string)$json['echo']) : '';
        $respRequestId = isset($json['request_id']) ? trim((string)$json['request_id']) : '';
        $respTs = isset($json['ts']) ? (int)$json['ts'] : 0;
        $message = isset($json['message']) ? trim((string)$json['message']) : '';
        $signature = isset($json['signature']) ? trim((string)$json['signature']) : '';

        // 진단 우선: 원격이 shared secret 오류를 명시한 경우 즉시 원인 반환
        if ($status === 'fail' && $message === 'invalid_shared_secret') {
            return array(false, 'remote_invalid_shared_secret', $status);
        }

        if ($respRequestId !== $requestId) {
            $suffix = ($message !== '') ? (':' . $message) : '';
            return array(false, 'request_id_mismatch' . $suffix, $status);
        }

        if ($respTs <= 0 || abs(time() - $respTs) > 300) {
            return array(false, 'response_ts_invalid', $status);
        }

        $base = $status . '|' . $echoValue . '|' . $respRequestId . '|' . $respTs . '|' . $message;
        $calc = hash_hmac('sha256', $base, $secret);
        if (!hash_equals($calc, $signature)) {
            return array(false, 'response_signature_invalid', $status);
        }

        if ($status === 'success' && $echoValue === 'success') {
            return array(true, '', $status);
        }

        return array(false, 'remote_status_' . $status, $status);
    }
}

if (!function_exists('vpnlink_ikev2_send_secure_payload')) {
    function vpnlink_ikev2_send_secure_payload($endpointUrl, $payload)
    {
        $endpointUrl = trim((string)$endpointUrl);
        if ($endpointUrl === '') {
            return array('ok' => false, 'error' => 'empty_endpoint_url');
        }

        $secret = vpnlink_ikev2_secret();
        if (!vpnlink_ikev2_has_valid_secret($secret)) {
            return array('ok' => false, 'error' => 'shared_secret_invalid');
        }

        if (!is_array($payload)) {
            $payload = array();
        }

        $requestId = isset($payload['request_id']) ? trim((string)$payload['request_id']) : '';
        if ($requestId === '') {
            $requestId = uniqid('ikev2_secure_', true);
            $payload['request_id'] = $requestId;
        }

        if (!isset($payload['ts']) || !is_numeric($payload['ts'])) {
            $payload['ts'] = time();
        }
        if (!isset($payload['nonce']) || trim((string)$payload['nonce']) === '') {
            $payload['nonce'] = bin2hex(random_bytes(16));
        }

        $plain = json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        if (!is_string($plain) || $plain === '') {
            return array('ok' => false, 'error' => 'payload_encode_failed');
        }

        $key = hash('sha256', $secret, true);
        $iv = random_bytes(16);
        $cipher = openssl_encrypt($plain, 'AES-256-CBC', $key, OPENSSL_RAW_DATA, $iv);
        if ($cipher === false) {
            return array('ok' => false, 'error' => 'encrypt_failed');
        }

        $packet = base64_encode($iv) . ':' . base64_encode($cipher);
        $sig = hash_hmac('sha256', $packet, $secret);

        $http = vpnlink_ikev2_http_post($endpointUrl, array(
            'v' => '1',
            'packet' => $packet,
            'sig' => $sig
        ));

        $httpCode = isset($http['http_code']) ? (int)$http['http_code'] : 0;
        $transportError = isset($http['transport_error']) ? trim((string)$http['transport_error']) : '';
        $body = isset($http['body']) ? (string)$http['body'] : '';

        if ($transportError !== '') {
            return array('ok' => false, 'error' => 'transport_error:' . $transportError, 'http_code' => $httpCode, 'raw_body' => $body);
        }

        if ($httpCode < 200 || $httpCode >= 300) {
            return array('ok' => false, 'error' => 'http_status_' . $httpCode, 'http_code' => $httpCode, 'raw_body' => $body);
        }

        $verify = vpnlink_ikev2_verify_response($body, $requestId, $secret);
        if (empty($verify[0])) {
            return array(
                'ok' => false,
                'error' => isset($verify[1]) ? (string)$verify[1] : 'verify_failed',
                'remote_status' => isset($verify[2]) ? (string)$verify[2] : '',
                'http_code' => $httpCode,
                'raw_body' => $body
            );
        }

        $json = json_decode($body, true);
        if (!is_array($json)) {
            return array('ok' => false, 'error' => 'invalid_json_response', 'http_code' => $httpCode, 'raw_body' => $body);
        }

        return array(
            'ok' => true,
            'http_code' => $httpCode,
            'json' => $json,
            'raw_body' => $body,
            'request_id' => $requestId,
            'message' => isset($json['message']) ? (string)$json['message'] : ''
        );
    }
}

if (!function_exists('vpnlink_ikev2_fetch_server_users')) {
    function vpnlink_ikev2_fetch_server_users($serverName, $limit)
    {
        $serverName = trim((string)$serverName);
        $limit = (int)$limit;
        if ($limit <= 0) {
            $limit = 5000;
        }

        if ($serverName === '') {
            return array('ok' => false, 'error' => 'empty_server_name');
        }

        $endpointUrl = vpnlink_ikev2_build_endpoint($serverName);
        if ($endpointUrl === '') {
            return array('ok' => false, 'error' => 'invalid_endpoint_url');
        }

        $payload = array(
            'operation' => 'list_users',
            'source' => 'admin_compare',
            'vpn_user_id' => '__list__',
            'limit' => $limit
        );

        $ret = vpnlink_ikev2_send_secure_payload($endpointUrl, $payload);
        if (empty($ret['ok'])) {
            return $ret;
        }

        $json = isset($ret['json']) && is_array($ret['json']) ? $ret['json'] : array();
        $data = isset($json['data']) && is_array($json['data']) ? $json['data'] : array();
        $users = isset($data['users']) && is_array($data['users']) ? $data['users'] : array();

        $normalizedUsers = array();
        foreach ($users as $row) {
            if (!is_array($row)) {
                continue;
            }

            $userId = trim((string)($row['user_id'] ?? ''));
            if ($userId === '') {
                continue;
            }

            $normalizedUsers[] = array(
                'user_id' => $userId,
                'expire' => trim((string)($row['expire'] ?? '')),
                'memo' => trim((string)($row['memo'] ?? '')),
                'admin' => trim((string)($row['admin'] ?? '')),
                'shop_managed' => !empty($row['shop_managed']) ? 1 : 0,
                'order_od_id' => trim((string)($row['order_od_id'] ?? '')),
                'order_it_id' => trim((string)($row['order_it_id'] ?? '')),
                'password_sha256' => trim((string)($row['password_sha256'] ?? ''))
            );
        }

        return array(
            'ok' => true,
            'users' => $normalizedUsers,
            'count' => count($normalizedUsers),
            'endpoint_url' => $endpointUrl,
            'message' => isset($ret['message']) ? (string)$ret['message'] : ''
        );
    }
}

if (!function_exists('vpnlink_ikev2_force_sync_vpnid_member')) {
    function vpnlink_ikev2_force_sync_vpnid_member($serverName, $memberId)
    {
        $serverName = trim((string)$serverName);
        $memberId = trim((string)$memberId);

        if ($serverName === '' || $memberId === '') {
            return array('ok' => false, 'error' => 'empty_server_or_member');
        }

        $row = sql_fetch("SELECT *
                           FROM `vpnid_list`
                          WHERE `server_name` = '" . vpnlink_ikev2_sql_escape($serverName) . "'
                            AND `vpn_user_id` = '" . vpnlink_ikev2_sql_escape($memberId) . "'
                          ORDER BY `no` DESC
                          LIMIT 1", false);

        if (!is_array($row) || !isset($row['vpn_user_id'])) {
            return array('ok' => false, 'error' => 'vpnid_row_not_found');
        }

        $vpnUserId = trim((string)($row['vpn_user_id'] ?? ''));
        $vpnUserPw = trim((string)($row['vpn_user_pwd'] ?? ''));
        $expire = trim((string)($row['vpn_id_expire'] ?? ''));
        $odId = trim((string)($row['od_id'] ?? ''));
        $memo = trim((string)($row['memo'] ?? ''));

        if ($vpnUserId === '' || $vpnUserPw === '') {
            return array('ok' => false, 'error' => 'empty_user_or_password');
        }

        $endpointUrl = vpnlink_ikev2_build_endpoint($serverName);
        if ($endpointUrl === '') {
            return array('ok' => false, 'error' => 'invalid_endpoint_url');
        }

        $endpointCandidates = array();
        $endpointCandidates[] = $endpointUrl;

        // server_name에 스킴이 없는 경우 HTTPS 실패 시 HTTP 폴백 시도(신규 경로 우선, 레거시 경로 호환)
        if (!preg_match('#^https?://#i', $serverName)) {
            $fallbackCandidates = array(
                'http://' . rtrim($serverName, '/') . '/mylib4ikev2svr/ikev2_ipsec.secrets_update.php',
                'http://' . rtrim($serverName, '/') . '/mylib/ikev2_ipsec.secrets_update.php'
            );
            foreach ($fallbackCandidates as $httpEndpoint) {
                if (!in_array($httpEndpoint, $endpointCandidates, true)) {
                    $endpointCandidates[] = $httpEndpoint;
                }
            }
        }

        $payload = array(
            'operation' => 'upsert',
            'source' => 'auto_shop_order',
            'trigger_source' => 'admin_compare_force_sync',
            'admin' => 'shop_auto',
            'mb_id' => $vpnUserId,
            'od_id' => $odId,
            'vpn_user_id' => $vpnUserId,
            'vpn_user_pw' => $vpnUserPw,
            'expire' => $expire,
            'memo' => $memo
        );

        $attemptedEndpoints = array();
        $lastRet = array('ok' => false, 'error' => 'sync_failed');

        foreach ($endpointCandidates as $idx => $candidate) {
            $attemptedEndpoints[] = $candidate;
            $ret = vpnlink_ikev2_send_secure_payload($candidate, $payload);
            if (!empty($ret['ok'])) {
                if ($idx > 0) {
                    vpnlink_ikev2_log('force_sync fallback success member=' . $vpnUserId . ', endpoint=' . $candidate);
                }
                $retJson = (isset($ret['json']) && is_array($ret['json'])) ? $ret['json'] : array();
                return array(
                    'ok' => true,
                    'message' => isset($ret['message']) ? (string)$ret['message'] : 'synced',
                    'endpoint_url' => $candidate,
                    'final_endpoint' => $candidate,
                    'fallback_used' => ($idx > 0),
                    'attempted_endpoints' => $attemptedEndpoints,
                    'auto_enabled' => isset($retJson['auto_enabled']) ? ((int)$retJson['auto_enabled'] === 1) : false,
                    'seller_enabled_before_sync' => isset($retJson['seller_enabled_before_sync']) ? ((int)$retJson['seller_enabled_before_sync'] === 1) : false,
                    'seller_account_state_before' => isset($retJson['seller_account_state_before']) ? trim((string)$retJson['seller_account_state_before']) : '',
                    'seller_enabled_after_sync' => isset($retJson['seller_enabled_after_sync']) ? ((int)$retJson['seller_enabled_after_sync'] === 1) : false,
                    'seller_account_state' => isset($retJson['seller_account_state']) ? trim((string)$retJson['seller_account_state']) : '',
                    'activation_transition' => isset($retJson['activation_transition']) ? trim((string)$retJson['activation_transition']) : 'unknown'
                );
            }

            $lastRet = $ret;
            $errorCode = isset($ret['error']) ? (string)$ret['error'] : '';
            $isTransportFailure = (strpos($errorCode, 'transport_error:') === 0 || $errorCode === 'http_status_0');
            if (!$isTransportFailure) {
                break;
            }
        }

        if (!is_array($lastRet)) {
            $lastRet = array('ok' => false, 'error' => 'sync_failed');
        }
        $lastRet['attempted_endpoints'] = $attemptedEndpoints;
        $lastRet['final_endpoint'] = (count($attemptedEndpoints) > 0) ? $attemptedEndpoints[count($attemptedEndpoints) - 1] : '';
        $lastRet['fallback_used'] = (count($attemptedEndpoints) > 1);
        return $lastRet;
    }
}

if (!function_exists('vpnlink_ikev2_normalize_datetime_text')) {
    function vpnlink_ikev2_normalize_datetime_text($raw)
    {
        $text = trim((string)$raw);
        if ($text === '' || $text === '-' || $text === '0') {
            return '';
        }

        $text = str_replace('T', ' ', $text);
        $text = preg_replace('/\s*KST\(대한민국표준시\)\s*$/u', '', $text);
        $text = preg_replace('/\s*KST\s*$/u', '', $text);
        $text = preg_replace('/\s*UTC[^\s]*$/iu', '', $text);
        $text = trim($text);

        if ($text === '') {
            return '';
        }

        if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $text)) {
            $text .= ' 00:00:00';
        } else if (preg_match('/^\d{4}-\d{2}-\d{2}\s+\d{2}:\d{2}$/', $text)) {
            $text .= ':00';
        }

        $ts = strtotime($text);
        if ($ts === false) {
            return '';
        }

        return date('Y-m-d H:i:s', $ts);
    }
}

if (!function_exists('vpnlink_ikev2_resolve_order_expire')) {
    function vpnlink_ikev2_resolve_order_expire($odId, $itId, $ctId)
    {
        global $g5;

        $odId = trim((string)$odId);
        $itId = trim((string)$itId);
        $ctId = (int)$ctId;

        if ($odId === '' || $itId === '') {
            return '';
        }

        if (!isset($g5['g5_shop_cart_table']) || !isset($g5['g5_shop_item_table'])) {
            return '';
        }

        $orderTable = isset($g5['g5_shop_order_table']) ? $g5['g5_shop_order_table'] : '';

        $where = "c.od_id = '" . vpnlink_ikev2_sql_escape($odId) . "' AND c.it_id = '" . vpnlink_ikev2_sql_escape($itId) . "'";
        if ($ctId > 0) {
            $where .= " AND c.ct_id = '" . (int)$ctId . "'";
        }

        $joinOrder = '';
        $selectOrderTime = "'' AS od_time";
        if ($orderTable !== '') {
            $joinOrder = " LEFT JOIN {$orderTable} o ON c.od_id = o.od_id ";
            $selectOrderTime = "o.od_time AS od_time";
        }

        $sql = "SELECT c.extension_of_period_to_datetime,
                       c.extension_of_period_date,
                       c.ct_qty,
                       c.ct_qty_hour,
                       c.ct_time,
                       {$selectOrderTime},
                       b.it_spec_to_datetime
                  FROM {$g5['g5_shop_cart_table']} c
             LEFT JOIN {$g5['g5_shop_item_table']} b ON c.it_id = b.it_id
                  {$joinOrder}
                 WHERE {$where}
              ORDER BY c.ct_id DESC
                 LIMIT 1";
        $row = sql_fetch($sql, false);
        if (!is_array($row)) {
            return '';
        }

        $baseTimeText = vpnlink_ikev2_normalize_datetime_text(isset($row['od_time']) ? $row['od_time'] : '');
        if ($baseTimeText === '') {
            $baseTimeText = vpnlink_ikev2_normalize_datetime_text(isset($row['ct_time']) ? $row['ct_time'] : '');
        }
        $baseTs = $baseTimeText !== '' ? strtotime($baseTimeText) : false;

        $durationSeconds = 0;
        $qDay = isset($row['ct_qty']) ? (int)$row['ct_qty'] : 0;
        $qHour = isset($row['ct_qty_hour']) ? (int)$row['ct_qty_hour'] : 0;
        if ($qDay > 0) {
            $durationSeconds += ($qDay * 86400);
        }
        if ($qHour > 0) {
            $durationSeconds += ($qHour * 3600);
        }

        $candidates = array(
            isset($row['extension_of_period_to_datetime']) ? $row['extension_of_period_to_datetime'] : '',
            isset($row['extension_of_period_date']) ? $row['extension_of_period_date'] : '',
            isset($row['it_spec_to_datetime']) ? $row['it_spec_to_datetime'] : ''
        );

        foreach ($candidates as $candidate) {
            $normalized = vpnlink_ikev2_normalize_datetime_text($candidate);
            if ($normalized !== '') {
                $candidateTs = strtotime($normalized);
                if ($baseTs !== false && $candidateTs !== false && $candidateTs <= $baseTs) {
                    continue;
                }
                return $normalized;
            }
        }

        if ($baseTs !== false && $durationSeconds > 0) {
            return date('Y-m-d H:i:s', $baseTs + $durationSeconds);
        }

        return '';
    }
}


if (!function_exists('vpnlink_ikev2_resolve_order_duration_sec')) {
    function vpnlink_ikev2_resolve_order_duration_sec($odId, $itId, $ctId)
    {
        global $g5;
        $odId = trim((string)$odId);
        $itId = trim((string)$itId);
        $ctId = (int)$ctId;
        if ($odId === '' || $itId === '' || !isset($g5['g5_shop_cart_table'])) {
            return 0;
        }
        $where = "od_id = '" . vpnlink_ikev2_sql_escape($odId) . "'"
               . " AND it_id = '" . vpnlink_ikev2_sql_escape($itId) . "'";
        if ($ctId > 0) { $where .= " AND ct_id = " . $ctId; }
        $row = sql_fetch(
            "SELECT ct_qty, ct_qty_hour, extension_of_period_date, extension_of_period_to_datetime"
            . " FROM `{$g5['g5_shop_cart_table']}` WHERE {$where} ORDER BY ct_id DESC LIMIT 1",
            false
        );
        if (!is_array($row)) { return 0; }
        $parseLine = function($raw) {
            return array_values(array_filter(
                array_map('trim', explode("\n", (string)$raw)),
                function($v) { return $v !== ''; }
            ));
        };
        $fromLines = $parseLine($row['extension_of_period_date']);
        $toLines   = $parseLine($row['extension_of_period_to_datetime']);
        if (!empty($fromLines) && !empty($toLines)) {
            $fromTs = strtotime(end($fromLines));
            $toTs   = strtotime(end($toLines));
            if ($fromTs > 0 && $toTs > $fromTs) { return $toTs - $fromTs; }
        }
        return (int)$row['ct_qty'] * 86400 + (int)$row['ct_qty_hour'] * 3600;
    }
}

if (!function_exists('vpnlink_ikev2_update_cart_expire')) {
    function vpnlink_ikev2_update_cart_expire($odId, $itId, $ctId, $newExpire, $newFrom)
    {
        global $g5;
        $odId      = trim((string)$odId);
        $itId      = trim((string)$itId);
        $ctId      = (int)$ctId;
        $newExpire = trim((string)$newExpire);
        $newFrom   = trim((string)$newFrom);
        if ($odId === '' || $itId === '' || $newExpire === '' || !isset($g5['g5_shop_cart_table'])) {
            return false;
        }
        $where = "od_id = '" . vpnlink_ikev2_sql_escape($odId) . "'"
               . " AND it_id = '" . vpnlink_ikev2_sql_escape($itId) . "'";
        if ($ctId > 0) { $where .= " AND ct_id = " . $ctId; }
        $row = sql_fetch(
            "SELECT extension_of_period_date, extension_of_period_to_datetime"
            . " FROM `{$g5['g5_shop_cart_table']}` WHERE {$where} ORDER BY ct_id DESC LIMIT 1",
            false
        );
        if (!is_array($row)) { return false; }
        $rebuild = function($raw, $newLast) {
            $lines = array_values(array_filter(
                array_map('trim', explode("\n", (string)$raw)),
                function($v) { return $v !== ''; }
            ));
            if (empty($lines)) { $lines = array($newLast); }
            else { $lines[count($lines) - 1] = $newLast; }
            return implode("\r\n", $lines);
        };
        $newToField   = $rebuild($row['extension_of_period_to_datetime'], $newExpire);
        $newFromField = $rebuild($row['extension_of_period_date'], $newFrom);
        sql_query(
            "UPDATE `{$g5['g5_shop_cart_table']}`"
            . " SET extension_of_period_to_datetime = '" . vpnlink_ikev2_sql_escape($newToField) . "',"
            . "     extension_of_period_date = '"        . vpnlink_ikev2_sql_escape($newFromField) . "'"
            . " WHERE {$where}",
            false
        );
        vpnlink_ikev2_log("cart_expire_updated od_id={$odId} it_id={$itId} new_expire={$newExpire} new_from={$newFrom}");
        return true;
    }
}

if (!function_exists('vpnlink_ikev2_build_order_memo')) {
    function vpnlink_ikev2_build_order_context($odId, $itId, $ctId, $mbId)
    {
        global $g5;

        $odId = trim((string)$odId);
        $itId = trim((string)$itId);
        $ctId = (int)$ctId;
        $mbId = trim((string)$mbId);

        $ctx = array(
            'member_id' => '',
            'member_name' => '',
            'od_id' => $odId,
            'it_id' => $itId,
            'item_name' => '',
            'plan_name' => '',
            'order_period' => '',
            'expire' => '',
            'amount_total' => 0,
            'amount_cash' => 0,
            'amount_point' => 0,
            'currency' => 'KRW'
        );

        if ($odId === '' || !isset($g5['g5_shop_cart_table']) || !isset($g5['g5_shop_item_table'])) {
            return $ctx;
        }

        $orderTable = isset($g5['g5_shop_order_table']) ? $g5['g5_shop_order_table'] : '';
        $cartTable = $g5['g5_shop_cart_table'];
        $itemTable = $g5['g5_shop_item_table'];
        $memberTable = isset($g5['member_table']) ? $g5['member_table'] : '';

        $order = array();
        if ($orderTable !== '') {
            $order = sql_fetch("SELECT od_id, mb_id, od_name, od_time, od_receipt_point, od_receipt_price, od_cart_price, od_send_cost, od_send_cost2
                                  FROM {$orderTable}
                                 WHERE od_id = '" . vpnlink_ikev2_sql_escape($odId) . "'
                                 LIMIT 1", false);
            if (!is_array($order)) {
                $order = array();
            }
        }

        $memberId = trim((string)($order['mb_id'] ?? ''));
        if ($memberId === '') {
            $memberId = $mbId;
        }
        $ctx['member_id'] = $memberId;

        $memberName = trim((string)($order['od_name'] ?? ''));
        if ($memberTable !== '' && $memberId !== '') {
            $member = sql_fetch("SELECT mb_name FROM {$memberTable} WHERE mb_id = '" . vpnlink_ikev2_sql_escape($memberId) . "' LIMIT 1", false);
            if (is_array($member) && !empty($member['mb_name'])) {
                $memberName = trim((string)$member['mb_name']);
            }
        }
        $ctx['member_name'] = $memberName;

        $where = "od_id = '" . vpnlink_ikev2_sql_escape($odId) . "'";
        if ($itId !== '') {
            $where .= " AND it_id = '" . vpnlink_ikev2_sql_escape($itId) . "'";
        }
        if ($ctId > 0) {
            $where .= " AND ct_id = '" . (int)$ctId . "'";
        }

          $cart = sql_fetch("SELECT it_id, ct_option, io_id, ct_qty, ct_qty_hour, ct_price, ct_time,
                                  extension_of_period_to_datetime, extension_of_period_date
                             FROM {$cartTable}
                            WHERE {$where}
                         ORDER BY ct_id DESC
                            LIMIT 1", false);

        $resolvedItId = $itId;
        if (is_array($cart) && $resolvedItId === '' && !empty($cart['it_id'])) {
            $resolvedItId = trim((string)$cart['it_id']);
        }
        $ctx['it_id'] = $resolvedItId;

        if ($resolvedItId !== '') {
            $item = sql_fetch("SELECT it_name FROM {$itemTable} WHERE it_id = '" . vpnlink_ikev2_sql_escape($resolvedItId) . "' LIMIT 1", false);
            if (is_array($item) && !empty($item['it_name'])) {
                $ctx['item_name'] = trim((string)$item['it_name']);
            }
        }

        $qDay = 0;
        $qHour = 0;
        if (is_array($cart)) {
            $plan = trim((string)($cart['ct_option'] ?? ''));
            if ($plan === '') {
                $plan = trim((string)($cart['io_id'] ?? ''));
            }

            $qDay = isset($cart['ct_qty']) ? (int)$cart['ct_qty'] : 0;
            $qHour = isset($cart['ct_qty_hour']) ? (int)$cart['ct_qty_hour'] : 0;

            if ($qDay > 0 || $qHour > 0) {
                $ctx['order_period'] = $qDay . '일' . $qHour . '시간';
            }

            if ($plan !== '') {
                $normalizeToken = function ($text) {
                    $text = trim((string)$text);
                    if ($text === '') {
                        return '';
                    }
                    $text = preg_replace('/\s+/u', '', $text);
                    return strtolower($text);
                };

                $planToken = $normalizeToken($plan);
                $itemNameToken = $normalizeToken($ctx['item_name']);
                $itemIdToken = $normalizeToken($resolvedItId);

                if (!(($itemNameToken !== '' && $planToken === $itemNameToken)
                    || ($itemIdToken !== '' && $planToken === $itemIdToken))) {
                    $ctx['plan_name'] = $plan;
                }
            }

            if ($ctx['plan_name'] === '') {
                $planWhere = "od_id = '" . vpnlink_ikev2_sql_escape($odId) . "'";
                if ($resolvedItId !== '') {
                    $planWhere .= " AND it_id = '" . vpnlink_ikev2_sql_escape($resolvedItId) . "'";
                }

                $planRow = sql_fetch("SELECT ct_option, io_id
                                       FROM {$cartTable}
                                      WHERE {$planWhere}
                                   ORDER BY ((ct_option LIKE '%이용권%') OR (io_id LIKE '%이용권%')) DESC, ct_id DESC
                                      LIMIT 1", false);
                if (is_array($planRow)) {
                    $candidate = trim((string)($planRow['ct_option'] ?? ''));
                    if ($candidate === '') {
                        $candidate = trim((string)($planRow['io_id'] ?? ''));
                    }
                    if ($candidate !== '') {
                        $normalizeToken = function ($text) {
                            $text = trim((string)$text);
                            if ($text === '') {
                                return '';
                            }
                            $text = preg_replace('/\s+/u', '', $text);
                            return strtolower($text);
                        };

                        $candidateToken = $normalizeToken($candidate);
                        $itemNameToken = $normalizeToken($ctx['item_name']);
                        $itemIdToken = $normalizeToken($resolvedItId);

                        if (!(($itemNameToken !== '' && $candidateToken === $itemNameToken)
                            || ($itemIdToken !== '' && $candidateToken === $itemIdToken))) {
                            $ctx['plan_name'] = $candidate;
                        }
                    }
                }
            }

            $unitPrice = isset($cart['ct_price']) ? (float)$cart['ct_price'] : 0.0;
            if ($unitPrice > 0) {
                $lineQty = $qDay > 0 ? $qDay : 1;
                $ctx['amount_total'] = $unitPrice * $lineQty;
            }

            $expireCandidates = array(
                isset($cart['extension_of_period_to_datetime']) ? $cart['extension_of_period_to_datetime'] : '',
                isset($cart['extension_of_period_date']) ? $cart['extension_of_period_date'] : ''
            );
            foreach ($expireCandidates as $candidateExpire) {
                $normExpire = vpnlink_ikev2_normalize_datetime_text($candidateExpire);
                if ($normExpire !== '') {
                    $baseTimeText = vpnlink_ikev2_normalize_datetime_text(isset($order['od_time']) ? $order['od_time'] : '');
                    if ($baseTimeText === '') {
                        $baseTimeText = vpnlink_ikev2_normalize_datetime_text(isset($cart['ct_time']) ? $cart['ct_time'] : '');
                    }

                    $baseTs = $baseTimeText !== '' ? strtotime($baseTimeText) : false;
                    $expTs = strtotime($normExpire);
                    if ($baseTs !== false && $expTs !== false && $expTs <= $baseTs) {
                        continue;
                    }

                    $ctx['expire'] = $normExpire;
                    break;
                }
            }

            if ($ctx['expire'] === '' && ($qDay > 0 || $qHour > 0)) {
                $baseTimeText = vpnlink_ikev2_normalize_datetime_text(isset($order['od_time']) ? $order['od_time'] : '');
                if ($baseTimeText === '') {
                    $baseTimeText = vpnlink_ikev2_normalize_datetime_text(isset($cart['ct_time']) ? $cart['ct_time'] : '');
                }

                $baseTs = $baseTimeText !== '' ? strtotime($baseTimeText) : false;
                if ($baseTs !== false) {
                    $ctx['expire'] = date('Y-m-d H:i:s', $baseTs + ($qDay * 86400) + ($qHour * 3600));
                }
            }
        }

        $amountPoint = isset($order['od_receipt_point']) ? (float)$order['od_receipt_point'] : 0;
        $amountCash = isset($order['od_receipt_price']) ? (float)$order['od_receipt_price'] : 0;
        $amountCart = isset($order['od_cart_price']) ? (float)$order['od_cart_price'] : 0;
        $sendCost = isset($order['od_send_cost']) ? (float)$order['od_send_cost'] : 0;
        $sendCost2 = isset($order['od_send_cost2']) ? (float)$order['od_send_cost2'] : 0;

        $amountTotal = 0;
        if ($amountCash > 0 || $amountPoint > 0) {
            $amountTotal = $amountCash + $amountPoint;
        } elseif ($amountCart > 0 || $sendCost > 0 || $sendCost2 > 0) {
            $amountTotal = $amountCart + $sendCost + $sendCost2;
        } elseif ($ctx['amount_total'] > 0) {
            $amountTotal = $ctx['amount_total'];
        }

        if ($amountCash <= 0 && $amountTotal > 0 && $amountPoint <= 0) {
            $amountCash = $amountTotal;
        }

        $ctx['amount_total'] = (float)$amountTotal;
        $ctx['amount_cash'] = (float)$amountCash;
        $ctx['amount_point'] = (float)$amountPoint;

        if ($ctx['expire'] === '') {
            $ctx['expire'] = vpnlink_ikev2_resolve_order_expire($odId, $resolvedItId, $ctId);
        }

        return $ctx;
    }
}

if (!function_exists('vpnlink_ikev2_build_order_memo')) {
    function vpnlink_ikev2_build_order_memo($odId, $itId, $ctId, $mbId)
    {
        $ctx = vpnlink_ikev2_build_order_context($odId, $itId, $ctId, $mbId);
        if (!is_array($ctx) || trim((string)($ctx['od_id'] ?? '')) === '') {
            return '';
        }
        $parts = array();
        $memberName = trim((string)($ctx['member_name'] ?? ''));
        $memberId = trim((string)($ctx['member_id'] ?? ''));
        if ($memberName !== '' && $memberId !== '') {
            $parts[] = '회원:' . $memberName . '(' . $memberId . ')';
        } elseif ($memberId !== '') {
            $parts[] = '회원ID:' . $memberId;
        } elseif ($memberName !== '') {
            $parts[] = '회원:' . $memberName;
        }

        $parts[] = '주문번호:' . trim((string)($ctx['od_id'] ?? ''));

        $itemName = trim((string)($ctx['item_name'] ?? ''));
        $resolvedItId = trim((string)($ctx['it_id'] ?? ''));
        if ($itemName !== '') {
            $parts[] = '상품명:' . $itemName;
        } elseif ($resolvedItId !== '') {
            $parts[] = '상품코드:' . $resolvedItId;
        }

        $planText = trim((string)($ctx['plan_name'] ?? ''));
        if ($planText !== '') {
            $parts[] = '요금제:' . $planText;
        }

        $orderPeriod = trim((string)($ctx['order_period'] ?? ''));
        if ($orderPeriod !== '') {
            $parts[] = '주문기간:' . $orderPeriod;
        }

        $expireText = trim((string)($ctx['expire'] ?? ''));
        if ($expireText !== '') {
            $parts[] = '만료일시:' . $expireText;
        }

        $totalAmount = isset($ctx['amount_total']) ? (float)$ctx['amount_total'] : 0;
        $cashAmount = isset($ctx['amount_cash']) ? (float)$ctx['amount_cash'] : 0;
        $paidPoint = isset($ctx['amount_point']) ? (float)$ctx['amount_point'] : 0;
        if ($totalAmount > 0 || $cashAmount > 0 || $paidPoint > 0) {
            if ($totalAmount > 0) {
                $parts[] = '결제총액:' . rtrim(rtrim(number_format($totalAmount, 2, '.', ''), '0'), '.') . '원';
            }
            $parts[] = '실결제:' . rtrim(rtrim(number_format($cashAmount, 2, '.', ''), '0'), '.') . '원';
            $parts[] = '포인트결제:' . rtrim(rtrim(number_format($paidPoint, 2, '.', ''), '0'), '.') . 'P';
        }

        return implode(' Œ ', $parts);
    }
}

if (!function_exists('vpnlink_ikev2_attempt_queue')) {
    function vpnlink_ikev2_attempt_queue($queueId, $triggerSource)
    {
        static $scopeConflictRetryGuard = array();

        $queueId = (int)$queueId;
        if ($queueId <= 0) {
            return array('ok' => false, 'error' => 'invalid_queue_id');
        }

        $secret = vpnlink_ikev2_secret();
        if (!vpnlink_ikev2_has_valid_secret($secret)) {
            vpnlink_ikev2_log('shared secret missing/invalid (>=32 chars required); queue_id=' . $queueId);
            return array('ok' => false, 'error' => 'shared_secret_invalid');
        }

        $q = sql_fetch("SELECT * FROM `app_ikev2_provision_queue` WHERE queue_id = '" . $queueId . "' LIMIT 1");
        if (!$q || !isset($q['queue_id'])) {
            return array('ok' => false, 'error' => 'queue_not_found');
        }

        if ((string)$q['final_status'] === 'success') {
            return array('ok' => true, 'status' => 'already_success');
        }

        $pw = vpnlink_ikev2_dec_local($q['vpn_password_enc'], $secret);
        if ($pw === '') {
            $err = 'decrypt_password_failed';
            $attemptNo = ((int)$q['attempt_count']) + 1;
            $nextRetryAt = date('Y-m-d H:i:s', time() + 60);

            $sql = "UPDATE `app_ikev2_provision_queue`
                       SET attempt_count = attempt_count + 1,
                           fail_count = fail_count + 1,
                           last_attempt_at = NOW(),
                           last_retry_at = NOW(),
                           next_retry_at = '" . vpnlink_ikev2_sql_escape($nextRetryAt) . "',
                           status = 'retrying',
                           final_status = 'retrying',
                           last_result = 'retrying',
                           last_error = '" . vpnlink_ikev2_sql_escape($err) . "',
                           updated_at = NOW()
                     WHERE queue_id = '" . $queueId . "'";
            sql_query($sql, false);

            vpnlink_ikev2_insert_history(array(
                'queue_id' => $queueId,
                'od_id' => $q['od_id'],
                'it_id' => $q['it_id'],
                'event_type' => 'attempt',
                'attempt_no' => $attemptNo,
                'event_status' => 'retrying',
                'http_code' => null,
                'error_message' => $err,
                'response_body' => null,
                'next_retry_at' => $nextRetryAt
            ));

            return array('ok' => false, 'error' => $err);
        }

        $requestId = uniqid('ikev2_', true);
        $odId = isset($q['od_id']) ? trim((string)$q['od_id']) : '';
        $itId = isset($q['it_id']) ? trim((string)$q['it_id']) : '';
        $ctId = isset($q['ct_id']) ? (int)$q['ct_id'] : 0;
        $hasOrderContext = ($odId !== '' || $itId !== '');
        $isManualRetry = (strpos((string)$triggerSource, 'admin_manual_retry') !== false);
        $payloadSource = $isManualRetry ? 'admin_manual_retry' : ($hasOrderContext ? 'auto_shop_order' : trim((string)$triggerSource));
        if ($payloadSource === '') {
            $payloadSource = 'manual_api';
        }

        $resolvedExpire = vpnlink_ikev2_resolve_order_expire($odId, $itId, $ctId);

        // 프로비저닝 성공 시점 기준 만료일 재산정
        // [주문일시 + 주문기간] → [프로비저닝성공일시 + 주문기간]
        $provisionTime      = time();
        $provisionDurationSec = ($hasOrderContext && empty($q['expire_override']))
            ? vpnlink_ikev2_resolve_order_duration_sec($odId, $itId, $ctId)
            : 0;

        if (!empty($q['expire_override'])) {
            // 수동 재시도 시 관리자가 지정한 만료일 우선 적용
            $resolvedExpire = trim((string)$q['expire_override']);
        } elseif ($hasOrderContext && $provisionDurationSec > 0) {
            // 자동 프로비저닝: 현재 시각 기준 만료일 계산
            $resolvedExpire = date('Y-m-d H:i:s', $provisionTime + $provisionDurationSec);
        }

        $resolvedMemo = '';
        if ($hasOrderContext) {
            $resolvedMemo = vpnlink_ikev2_build_order_memo($odId, $itId, $ctId, isset($q['mb_id']) ? (string)$q['mb_id'] : '');
            $orderCtx = vpnlink_ikev2_build_order_context($odId, $itId, $ctId, isset($q['mb_id']) ? (string)$q['mb_id'] : '');
            if ($resolvedMemo === '') {
                $memoParts = array('쇼핑몰 자동등록');
                if ($odId !== '') {
                    $memoParts[] = '주문번호:' . $odId;
                }
                if ($itId !== '') {
                    $memoParts[] = '상품코드:' . $itId;
                }
                $resolvedMemo = implode(' Œ ', $memoParts);
            }
        }

        $payload = array(
            'request_id' => $requestId,
            'ts' => time(),
            'nonce' => bin2hex(random_bytes(16)),
            'source' => $payloadSource,
            'trigger_source' => (string)$triggerSource,
            'od_id' => (string)$odId,
            'it_id' => (string)$itId,
            'ct_id' => (int)$q['ct_id'],
            'mb_id' => (string)$q['mb_id'],
            'protocol' => (string)$q['protocol'],
            'vpn_user_id' => (string)$q['vpn_user_id'],
            'vpn_user_pw' => (string)$pw,
            'expire' => $resolvedExpire,
            'memo' => $resolvedMemo,
            'order_meta' => ($hasOrderContext ? $orderCtx : array()),
            'order_meta_ver' => 1,
            'auto_order_only' => ($hasOrderContext ? 1 : 0),
            'admin' => ($hasOrderContext ? 'shop_auto' : 'manual_api')
        );

        $plain = json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        $key = hash('sha256', $secret, true);
        $iv = random_bytes(16);
        $cipher = openssl_encrypt($plain, 'AES-256-CBC', $key, OPENSSL_RAW_DATA, $iv);

        if ($cipher === false) {
            return array('ok' => false, 'error' => 'encrypt_failed');
        }

        $packet = base64_encode($iv) . ':' . base64_encode($cipher);
        $sig = hash_hmac('sha256', $packet, $secret);

        $endpointUrl = trim((string)$q['endpoint_url']);
        if ($endpointUrl === '') {
            $endpointUrl = vpnlink_ikev2_build_endpoint($q['server_ip']);
        }

        $endpointCandidates = array();
        if ($endpointUrl !== '') {
            $endpointCandidates[] = $endpointUrl;

            if (strpos($endpointUrl, '/mylib/ikev2_ipsec.secrets_update.php') !== false) {
                $migrated = str_replace('/mylib/ikev2_ipsec.secrets_update.php', '/mylib4ikev2svr/ikev2_ipsec.secrets_update.php', $endpointUrl);
                if (!in_array($migrated, $endpointCandidates, true)) {
                    array_unshift($endpointCandidates, $migrated);
                }
            }
        }

        $serverIpRaw = trim((string)$q['server_ip']);
        $serverHasScheme = (bool)preg_match('#^https?://#i', $serverIpRaw);
        if (!$serverHasScheme && $serverIpRaw !== '') {
            $fallbackCandidates = array(
                'http://' . rtrim($serverIpRaw, '/') . '/mylib4ikev2svr/ikev2_ipsec.secrets_update.php',
                'http://' . rtrim($serverIpRaw, '/') . '/mylib/ikev2_ipsec.secrets_update.php'
            );
            foreach ($fallbackCandidates as $fallbackEndpointUrl) {
                if (!in_array($fallbackEndpointUrl, $endpointCandidates, true)) {
                    $endpointCandidates[] = $fallbackEndpointUrl;
                }
            }
        }

        $http = array('http_code' => 0, 'transport_error' => 'no_endpoint_candidate', 'body' => '');
        foreach ($endpointCandidates as $candidateUrl) {
            $http = vpnlink_ikev2_http_post($candidateUrl, array(
                'v' => '1',
                'packet' => $packet,
                'sig' => $sig
            ));

            if (((int)$http['http_code'] >= 200 && (int)$http['http_code'] < 300)
                || trim((string)$http['transport_error']) === '') {
                break;
            }
        }

        $httpCode = isset($http['http_code']) ? (int)$http['http_code'] : 0;
        $body = isset($http['body']) ? (string)$http['body'] : '';
        $transportError = isset($http['transport_error']) ? trim((string)$http['transport_error']) : '';

        $attemptNo = ((int)$q['attempt_count']) + 1;
        $nextRetryAt = null;
        $error = '';
        $resultStatus = 'retrying';
        $verifyStatus = '';
        $ok = false;

        if ($transportError !== '') {
            $error = 'transport_error:' . $transportError;
        } elseif ($httpCode < 200 || $httpCode >= 300) {
            $error = 'http_status_' . $httpCode;
            if ($httpCode >= 300 && $httpCode < 400) {
                $fromUrl = trim((string)$http['effective_url']);
                if ($fromUrl === '') {
                    $fromUrl = trim((string)$http['requested_url']);
                }

                $toUrl = trim((string)$http['redirect_url']);
                if ($toUrl === '' && !empty($http['response_headers']) && is_array($http['response_headers'])) {
                    foreach ($http['response_headers'] as $hk => $hv) {
                        if (strtolower(trim((string)$hk)) === 'location') {
                            $toUrl = trim((string)$hv);
                            if ($toUrl !== '') {
                                break;
                            }
                        }
                    }
                }

                if ($fromUrl !== '' || $toUrl !== '') {
                    $error .= '|from=' . rawurlencode($fromUrl) . '&to=' . rawurlencode($toUrl);
                }
            }
        } else {
            $verify = vpnlink_ikev2_verify_response($body, $requestId, $secret);
            $ok = (bool)$verify[0];
            $error = (string)$verify[1];
            $verifyStatus = (string)$verify[2];
            if ($ok) {
                $resultStatus = 'success';
            }
        }

        if ($ok) {
            $sql = "UPDATE `app_ikev2_provision_queue`
                       SET attempt_count = attempt_count + 1,
                           last_attempt_at = NOW(),
                           next_retry_at = NULL,
                           status = 'success',
                           final_status = 'success',
                           last_http_code = '" . $httpCode . "',
                           last_result = 'success',
                           last_error = NULL,
                           last_response = '" . vpnlink_ikev2_sql_escape(substr($body, 0, 60000)) . "',
                           updated_at = NOW()
                     WHERE queue_id = '" . $queueId . "'";
            sql_query($sql, false);

            vpnlink_ikev2_insert_history(array(
                'queue_id' => $queueId,
                'od_id' => $q['od_id'],
                'it_id' => $q['it_id'],
                'event_type' => 'attempt',
                'attempt_no' => $attemptNo,
                'event_status' => 'success',
                'http_code' => $httpCode,
                'error_message' => null,
                'response_body' => substr($body, 0, 60000),
                'next_retry_at' => null
            ));

            // 쇼핑몰 DB 만료일 근본 데이터 재기록 (프로비저닝 성공 시각 기준)
            if ($hasOrderContext && $resolvedExpire !== '') {
                $provisionTimeStr = date('Y-m-d H:i:s', $provisionTime);
                // 1) 장바구니 extension_of_period_to_datetime / extension_of_period_date 마지막 줄 교체
                vpnlink_ikev2_update_cart_expire($odId, $itId, $ctId, $resolvedExpire, $provisionTimeStr);
                // 2) 상품 테이블 it_spec_to_datetime 업데이트 (vpnlink_ikev2_resolve_order_expire의 주 소스)
                if ($itId !== '' && isset($g5['g5_shop_item_table'])) {
                    sql_query(
                        "UPDATE `{$g5['g5_shop_item_table']}`"
                        . " SET it_spec_to_datetime = '" . vpnlink_ikev2_sql_escape($resolvedExpire) . "'"
                        . " WHERE it_id = '" . vpnlink_ikev2_sql_escape($itId) . "'",
                        false
                    );
                    vpnlink_ikev2_log("it_spec_to_datetime_updated it_id={$itId} expire={$resolvedExpire}");
                }
                // 3) vpnid_list.vpn_id_expire 재조정 (프로비저닝 성공 시각 기준)
                // extend_order_approve 큐가 성공했을 때 승인 시각이 아닌 실제 서버 반영 시각으로 만료일을 확정한다.
                if (!empty($q['vpn_user_id'])) {
                    $vpnListUserEsc = vpnlink_ikev2_sql_escape(trim((string)$q['vpn_user_id']));
                    $resolvedExpireEsc = vpnlink_ikev2_sql_escape($resolvedExpire);
                    $odIdEscVpn = vpnlink_ikev2_sql_escape($odId);
                    $whereVpnList = "vpn_user_id = '{$vpnListUserEsc}'";
                    if ($odIdEscVpn !== '') {
                        $whereVpnList .= " AND od_id = '{$odIdEscVpn}'";
                    }
                    sql_query(
                        "UPDATE vpnid_list"
                        . " SET vpn_id_expire = '{$resolvedExpireEsc}', update_datetime = NOW()"
                        . " WHERE {$whereVpnList} LIMIT 1",
                        false
                    );
                    vpnlink_ikev2_log("vpnid_list_expire_updated vpn_user_id={$q['vpn_user_id']} expire={$resolvedExpire}");
                }
            }

            return array('ok' => true, 'status' => 'success', 'expire' => $resolvedExpire);
        }

        if ($error === '') {
            $error = ($verifyStatus !== '') ? ('remote_status_' . $verifyStatus) : 'unknown_error';
        }

        // 자동주문 ID가 서버 수동등록 사용자와 충돌하면, suffix를 붙인 새 ID를 즉시 확정 후 1회 재시도한다.
        if ($error === 'auto_order_user_scope_violation') {
            $retryCount = isset($scopeConflictRetryGuard[$queueId]) ? (int)$scopeConflictRetryGuard[$queueId] : 0;
            if ($retryCount < 1) {
                $scopeConflictRetryGuard[$queueId] = $retryCount + 1;
                $resolveRet = vpnlink_ikev2_try_auto_suffix_on_scope_conflict($queueId, (string)$triggerSource);
                if (!empty($resolveRet['ok'])) {
                    return vpnlink_ikev2_attempt_queue($queueId, (string)$triggerSource . '_suffix_retry');
                }
            }
        }

        $retryDelay = vpnlink_ikev2_calc_retry_delay($attemptNo);
        $nextRetryAt = date('Y-m-d H:i:s', time() + $retryDelay);

        // 최대 자동 재시도 횟수 초과 시 manual_required로 전환
        $newStatus = 'retrying';
        $newFinalStatus = 'retrying';
        $isRetryEnabled = 1;
        $nextRetryAtSql = "'" . vpnlink_ikev2_sql_escape($nextRetryAt) . "'";

        if ($attemptNo >= IKEV2_MAX_AUTO_RETRY) {
            $newStatus = 'manual_required';
            $newFinalStatus = 'manual_required';
            $isRetryEnabled = 0;
            $nextRetryAt = null;
            $nextRetryAtSql = 'NULL';
        }

        $sql = "UPDATE `app_ikev2_provision_queue`
                   SET attempt_count = attempt_count + 1,
                       fail_count = fail_count + 1,
                       last_attempt_at = NOW(),
                       last_retry_at = NOW(),
                       next_retry_at = " . $nextRetryAtSql . ",
                       status = '" . vpnlink_ikev2_sql_escape($newStatus) . "',
                       final_status = '" . vpnlink_ikev2_sql_escape($newFinalStatus) . "',
                       is_retry_enabled = " . (int)$isRetryEnabled . ",
                       last_http_code = " . (($httpCode > 0) ? (int)$httpCode : 'NULL') . ",
                       last_result = '" . vpnlink_ikev2_sql_escape($newStatus) . "',
                       last_error = '" . vpnlink_ikev2_sql_escape($error) . "',
                       last_response = " . (($body === '') ? 'NULL' : "'" . vpnlink_ikev2_sql_escape(substr($body, 0, 60000)) . "'") . ",
                       updated_at = NOW()
                 WHERE queue_id = '" . $queueId . "'";
        sql_query($sql, false);

        vpnlink_ikev2_insert_history(array(
            'queue_id' => $queueId,
            'od_id' => $q['od_id'],
            'it_id' => $q['it_id'],
            'event_type' => 'attempt',
            'attempt_no' => $attemptNo,
            'event_status' => $newStatus,
            'http_code' => ($httpCode > 0) ? $httpCode : null,
            'error_message' => $error,
            'response_body' => ($body === '') ? null : substr($body, 0, 60000),
            'next_retry_at' => $nextRetryAt
        ));

        // 최대 재시도 초과 → 포인트 자동 환불
        if ($newStatus === 'manual_required'
            && !empty($q['od_id'])
            && empty($q['point_refunded_at'])
            && function_exists('insert_point')
            && !empty($g5['g5_shop_order_table'])) {
            $odEsc = vpnlink_ikev2_sql_escape($q['od_id']);
            $orderRefund = sql_fetch(
                "SELECT mb_id, od_receipt_price, od_receipt_point
                   FROM `{$g5['g5_shop_order_table']}`
                  WHERE od_id = '{$odEsc}'", false);
            if ($orderRefund && !empty($orderRefund['mb_id'])) {
                $refundAmt = (int)$orderRefund['od_receipt_price']
                           + (int)$orderRefund['od_receipt_point'];
                if ($refundAmt > 0) {
                    $refundMb = $orderRefund['mb_id'];
                    insert_point(
                        $refundMb,
                        $refundAmt,
                        "프로비저닝 실패 자동환불 주문번호 {$q['od_id']} 상품 {$q['it_id']}",
                        '@ikev2ProvRefund|' . time(),
                        "{$refundMb}|{$refundMb}",
                        "{$q['od_id']}|{$q['it_id']}"
                    );
                    $nowStr = date('Y-m-d H:i:s');
                    sql_query("UPDATE `app_ikev2_provision_queue`
                        SET point_refunded_at = '{$nowStr}',
                            point_refunded_amount = {$refundAmt}
                        WHERE queue_id = " . (int)$queueId, false);
                    vpnlink_ikev2_log("point_refund_issued od_id={$q['od_id']}"
                        . " mb_id={$refundMb} amount={$refundAmt}");
                }
            }
        }

        return array('ok' => false, 'error' => $error, 'status' => $newStatus);
    }
}

if (!function_exists('vpnlink_ikev2_queue_order')) {
    function vpnlink_ikev2_queue_order($odId, $triggerSource)
    {
        global $g5;

        $odId = trim((string)$odId);
        if ($odId === '') {
            return array('ok' => false, 'error' => 'empty_od_id');
        }

        $secret = vpnlink_ikev2_secret();
        if (!vpnlink_ikev2_has_valid_secret($secret)) {
            vpnlink_ikev2_log('queue skipped: shared secret missing/invalid (>=32 chars required). od_id=' . $odId);
            return array('ok' => false, 'error' => 'shared_secret_invalid');
        }

        vpnlink_ikev2_create_tables();

        $order = sql_fetch("SELECT od_id, mb_id FROM `{$g5['g5_shop_order_table']}` WHERE od_id = '" . vpnlink_ikev2_sql_escape($odId) . "' LIMIT 1");
        if (!$order || !isset($order['od_id'])) {
            return array('ok' => false, 'error' => 'order_not_found');
        }

        $sql = "SELECT c.ct_id, c.it_id, c.mb_id, i.it_spec_sales_type, i.it_spec_remote_sw, i.ca_id, i.ca_id2, i.ca_id3
                  FROM `{$g5['g5_shop_cart_table']}` c
                  LEFT JOIN `{$g5['g5_shop_item_table']}` i ON c.it_id = i.it_id
                 WHERE c.od_id = '" . vpnlink_ikev2_sql_escape($odId) . "'";
        $res = sql_query($sql, false);

        $queueIds = array();
        $queuedCount = 0;

        for ($i = 0; $row = sql_fetch_array($res); $i++) {
            $targets = vpnlink_ikev2_extract_targets_from_item($row);
            if (count($targets) < 1) {
                continue;
            }

            foreach ($targets as $t) {
                if (trim((string)$t['endpoint_url']) === '') {
                    continue;
                }

                $encPw = vpnlink_ikev2_enc_local($t['vpn_user_pw'], $secret);
                if ($encPw === '') {
                    continue;
                }

                $mbId = trim((string)$order['mb_id']);
                if ($mbId === '') {
                    $mbId = trim((string)$row['mb_id']);
                }

                $sqlInsert = "INSERT INTO `app_ikev2_provision_queue`
                                SET od_id = '" . vpnlink_ikev2_sql_escape($odId) . "',
                                    it_id = '" . vpnlink_ikev2_sql_escape($row['it_id']) . "',
                                    ct_id = '" . (int)$row['ct_id'] . "',
                                    mb_id = '" . vpnlink_ikev2_sql_escape($mbId) . "',
                                    protocol = '" . vpnlink_ikev2_sql_escape($t['protocol']) . "',
                                    server_ip = '" . vpnlink_ikev2_sql_escape($t['server_ip']) . "',
                                    endpoint_url = '" . vpnlink_ikev2_sql_escape($t['endpoint_url']) . "',
                                    vpn_user_id = '" . vpnlink_ikev2_sql_escape($t['vpn_user_id']) . "',
                                    vpn_password_enc = '" . vpnlink_ikev2_sql_escape($encPw) . "',
                                    status = 'queued',
                                    final_status = 'queued',
                                    next_retry_at = NOW(),
                                    created_at = NOW(),
                                    updated_at = NOW()
                              ON DUPLICATE KEY UPDATE
                                    ct_id = VALUES(ct_id),
                                    mb_id = VALUES(mb_id),
                                    endpoint_url = VALUES(endpoint_url),
                                    vpn_password_enc = VALUES(vpn_password_enc),
                                status = 'queued',
                                final_status = 'queued',
                                    next_retry_at = NOW(),
                                last_result = 'queued',
                                last_error = NULL,
                                    updated_at = NOW()";
                sql_query($sqlInsert, false);

                $queueRow = sql_fetch("SELECT queue_id
                                         FROM `app_ikev2_provision_queue`
                                        WHERE od_id = '" . vpnlink_ikev2_sql_escape($odId) . "'
                                          AND it_id = '" . vpnlink_ikev2_sql_escape($row['it_id']) . "'
                                          AND protocol = '" . vpnlink_ikev2_sql_escape($t['protocol']) . "'
                                          AND server_ip = '" . vpnlink_ikev2_sql_escape($t['server_ip']) . "'
                                          AND vpn_user_id = '" . vpnlink_ikev2_sql_escape($t['vpn_user_id']) . "'
                                        LIMIT 1");

                if (!$queueRow || !isset($queueRow['queue_id'])) {
                    continue;
                }

                $queueId = (int)$queueRow['queue_id'];
                if ($queueId <= 0) {
                    continue;
                }

                $queueIds[] = $queueId;
                $queuedCount++;

                vpnlink_ikev2_insert_history(array(
                    'queue_id' => $queueId,
                    'od_id' => $odId,
                    'it_id' => $row['it_id'],
                    'event_type' => 'queued',
                    'attempt_no' => null,
                    'event_status' => 'queued',
                    'http_code' => null,
                    'error_message' => null,
                    'response_body' => null,
                    'next_retry_at' => date('Y-m-d H:i:s')
                ));
            }
        }

        $attemptSuccess = 0;
        $attemptFail = 0;
        foreach ($queueIds as $qid) {
            $attemptRet = vpnlink_ikev2_attempt_queue($qid, $triggerSource);
            if (!empty($attemptRet['ok'])) {
                $attemptSuccess++;
                continue;
            }
            // 즉시 재시도 1 (1초 후): 순간 네트워크 불가 대응
            sleep(1);
            $retry1 = vpnlink_ikev2_attempt_queue($qid, $triggerSource . '_q1');
            if (!empty($retry1['ok'])) {
                $attemptSuccess++;
                continue;
            }
            // 즉시 재시도 2 (2초 후)
            sleep(2);
            $retry2 = vpnlink_ikev2_attempt_queue($qid, $triggerSource . '_q2');
            if (!empty($retry2['ok'])) {
                $attemptSuccess++;
            } else {
                $attemptFail++;
            }
        }

        return array(
            'ok' => true,
            'queued_count' => $queuedCount,
            'attempt_success' => $attemptSuccess,
            'attempt_fail' => $attemptFail
        );
    }
}

if (!function_exists('vpnlink_ikev2_retry_due_jobs')) {
    function vpnlink_ikev2_retry_due_jobs($limit)
    {
        $limit = (int)$limit;
        if ($limit <= 0) {
            $limit = 20;
        }

        vpnlink_ikev2_create_tables();

        $sql = "SELECT queue_id
                  FROM `app_ikev2_provision_queue`
                 WHERE final_status <> 'success'
                                     AND is_retry_enabled = 1
                   AND (next_retry_at IS NULL OR next_retry_at <= NOW())
                 ORDER BY COALESCE(next_retry_at, created_at) ASC, queue_id ASC
                 LIMIT " . $limit;

        $res = sql_query($sql, false);

        $total = 0;
        $success = 0;
        $fail = 0;

        for ($i = 0; $row = sql_fetch_array($res); $i++) {
            $total++;
            $ret = vpnlink_ikev2_attempt_queue((int)$row['queue_id'], 'cron_retry');
            if (!empty($ret['ok'])) {
                $success++;
            } else {
                $fail++;
            }
        }

        return array(
            'processed' => $total,
            'success' => $success,
            'fail' => $fail
        );
    }
}

if (!function_exists('vpnlink_ikev2_get_manual_required_queues')) {
    function vpnlink_ikev2_get_manual_required_queues($limit = 200)
    {
        vpnlink_ikev2_create_tables();

        $sql = "SELECT queue_id, od_id, it_id, mb_id, server_ip, endpoint_url,
                       vpn_user_id, status, final_status, attempt_count, fail_count,
                       manual_retry_count, last_error, expire_override, created_at, updated_at
                  FROM `app_ikev2_provision_queue`
                 WHERE final_status = 'manual_required'
                 ORDER BY created_at ASC
                 LIMIT " . (int)$limit;

        $res = sql_query($sql, false);
        $rows = array();
        while ($row = sql_fetch_array($res)) {
            $rows[] = $row;
        }
        return $rows;
    }
}

if (!function_exists('vpnlink_ikev2_manual_retry_queue')) {
    function vpnlink_ikev2_manual_retry_queue($queueId, $newServerIp, $triggerSource)
    {
        vpnlink_ikev2_create_tables();

        $queueId = (int)$queueId;
        if ($queueId <= 0) {
            return array('ok' => false, 'error' => 'invalid_queue_id');
        }

        $q = sql_fetch("SELECT * FROM `app_ikev2_provision_queue` WHERE queue_id = '" . $queueId . "' LIMIT 1", false);
        if (!is_array($q) || !isset($q['queue_id'])) {
            return array('ok' => false, 'error' => 'queue_not_found');
        }

        $allowedStatuses = array('manual_required', 'retrying', 'queued');
        if (!in_array((string)$q['final_status'], $allowedStatuses, true)) {
            return array('ok' => false, 'error' => 'invalid_status:' . $q['final_status']);
        }

        $newServerIp = trim((string)$newServerIp);
        $updatedServerIp = ($newServerIp !== '') ? $newServerIp : trim((string)($q['server_ip'] ?? ''));
        $newEndpointUrl = vpnlink_ikev2_build_endpoint($updatedServerIp);

        // 원래 주문 기간을 현재 시각 기준으로 재산정
        // (extension_of_period_date ~ extension_of_period_to_datetime 의 마지막 기간 사용)
        $expireOverride = '';
        $odId = trim((string)($q['od_id'] ?? ''));
        $itId = trim((string)($q['it_id'] ?? ''));
        $ctId = (int)($q['ct_id'] ?? 0);
        $originalExpire = vpnlink_ikev2_resolve_order_expire($odId, $itId, $ctId);

        $durationSec = ($odId !== '' && $itId !== '')
            ? vpnlink_ikev2_resolve_order_duration_sec($odId, $itId, $ctId)
            : 0;
        if ($durationSec <= 0 && $originalExpire !== '') {
            // fallback: 원래 만료일 - 큐 생성 시각
            $createdTs = strtotime(trim((string)($q['created_at'] ?? '')));
            if ($createdTs > 0) {
                $originalExpireTs = strtotime($originalExpire);
                if ($originalExpireTs > $createdTs) {
                    $durationSec = $originalExpireTs - $createdTs;
                }
            }
        }
        if ($durationSec > 0) {
            $expireOverride = date('Y-m-d H:i:s', time() + $durationSec);
        }

        // 큐 초기화 및 서버 IP 교정
        $sql = "UPDATE `app_ikev2_provision_queue`
                   SET server_ip = '" . vpnlink_ikev2_sql_escape($updatedServerIp) . "',
                       endpoint_url = '" . vpnlink_ikev2_sql_escape($newEndpointUrl) . "',
                       expire_override = " . ($expireOverride !== '' ? "'" . vpnlink_ikev2_sql_escape($expireOverride) . "'" : 'NULL') . ",
                       status = 'queued',
                       final_status = 'queued',
                       is_retry_enabled = 1,
                       attempt_count = 0,
                       fail_count = 0,
                       manual_retry_count = manual_retry_count + 1,
                       next_retry_at = NOW(),
                       last_error = NULL,
                       updated_at = NOW()
                 WHERE queue_id = '" . $queueId . "'";
        sql_query($sql, false);

        vpnlink_ikev2_insert_history(array(
            'queue_id' => $queueId,
            'od_id' => $odId,
            'it_id' => $itId,
            'event_type' => 'control',
            'attempt_no' => null,
            'event_status' => 'admin_manual_retry',
            'http_code' => null,
            'error_message' => 'server_ip:' . trim((string)($q['server_ip'] ?? '')) . '->' . $updatedServerIp
                               . ($expireOverride !== '' ? ' expire_override:' . $expireOverride : ''),
            'response_body' => null,
            'next_retry_at' => null
        ));

        $ret = vpnlink_ikev2_attempt_queue($queueId, 'admin_manual_retry');

        if (!empty($ret['ok'])) {
            sql_query("UPDATE `app_ikev2_provision_queue` SET expire_override = NULL WHERE queue_id = '" . $queueId . "'", false);

            $noteParts = array('[관리자 수동 재시도 성공 ' . date('Y-m-d H:i:s') . ']');
            if ($newServerIp !== '' && $newServerIp !== trim((string)($q['server_ip'] ?? ''))) {
                $noteParts[] = 'VPN서버변경:' . trim((string)($q['server_ip'] ?? '')) . '->' . $newServerIp;
            }
            if ($expireOverride !== '') {
                $noteParts[] = '만료일재산정:' . $expireOverride . ' (원래만료일:' . $originalExpire . ')';
            }
            vpnlink_ikev2_append_order_memo($odId, implode(' ', $noteParts));
        }

        return array_merge($ret, array(
            'queue_id'        => $queueId,
            'new_server_ip'   => $updatedServerIp,
            'expire_override' => $expireOverride,
            'original_expire' => $originalExpire
        ));
    }
}
