<?php
/**
 * strongswan_profile_refund_order.php
 * - 유료 VPN 프로필 환불 견적/요청/승인 처리
 * - PHP 7.4 호환
 */

include_once('./_common.php');

global $g5;

if (!isset($link) || !$link) {
    $link = $g5['connect_db'];
}

$mysqli = ($link instanceof mysqli) ? $link : null;

header('X-Content-Type-Options: nosniff');
header('Access-Control-Allow-Origin: *');
date_default_timezone_set('Asia/Seoul');

define('VPN_REFUND_ORDER_VERSION', '2026-04-17-r2');
header('X-VPNLink-Refund-Version: ' . VPN_REFUND_ORDER_VERSION);

function refund_log($message, $context = array())
{
    // debug logging disabled
}

function h($s)
{
    return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8');
}

function req($key, $default = '')
{
    if (isset($_POST[$key])) {
        return trim((string)$_POST[$key]);
    }
    if (isset($_GET[$key])) {
        return trim((string)$_GET[$key]);
    }
    return $default;
}

function sql_esc($value)
{
    $value = (string)$value;
    if (function_exists('sql_real_escape_string')) {
        return sql_real_escape_string($value);
    }
    return addslashes($value);
}

function refund_load_shop_sync_secret()
{
    $secret = '';

    if (isset($_SERVER['IKEV2_SHOP_SYNC_SHARED_SECRET'])) {
        $secret = trim((string)$_SERVER['IKEV2_SHOP_SYNC_SHARED_SECRET']);
    }
    if ($secret === '' && isset($_ENV['IKEV2_SHOP_SYNC_SHARED_SECRET'])) {
        $secret = trim((string)$_ENV['IKEV2_SHOP_SYNC_SHARED_SECRET']);
    }
    if ($secret === '' && function_exists('getenv')) {
        $envVal = getenv('IKEV2_SHOP_SYNC_SHARED_SECRET');
        if (is_string($envVal)) {
            $secret = trim($envVal);
        }
    }

    if ($secret === '') {
        $envPath = dirname(__DIR__) . '/.env';
        if (is_file($envPath) && is_readable($envPath)) {
            $lines = @file($envPath, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
            if (is_array($lines)) {
                foreach ($lines as $line) {
                    $line = trim((string)$line);
                    if ($line === '' || strpos($line, '#') === 0) {
                        continue;
                    }
                    $pos = strpos($line, '=');
                    if ($pos === false) {
                        continue;
                    }
                    $k = trim(substr($line, 0, $pos));
                    if ($k !== 'IKEV2_SHOP_SYNC_SHARED_SECRET') {
                        continue;
                    }
                    $v = trim(substr($line, $pos + 1));
                    if ((strlen($v) >= 2) && (($v[0] === '"' && substr($v, -1) === '"') || ($v[0] === '\'' && substr($v, -1) === '\''))) {
                        $v = substr($v, 1, -1);
                    }
                    $secret = trim((string)$v);
                    break;
                }
            }
        }
    }

    return $secret;
}

function refund_extract_gateway_base($rawGateway)
{
    $rawGateway = trim((string)$rawGateway);
    if ($rawGateway === '') {
        return '';
    }

    if (preg_match('#^https?://#i', $rawGateway)) {
        $parsed = @parse_url($rawGateway);
        if (!is_array($parsed) || empty($parsed['host'])) {
            return '';
        }
        $scheme = isset($parsed['scheme']) ? strtolower((string)$parsed['scheme']) : 'http';
        $host = trim((string)$parsed['host']);
        if ($host === '') {
            return '';
        }
        return $scheme . '://' . $host;
    }

    $hostPart = preg_split('#[/?]#', $rawGateway, 2);
    $host = trim((string)$hostPart[0]);
    if ($host === '') {
        return '';
    }
    return 'http://' . $host;
}

function refund_default_vpn_users_endpoint($profileGateway = '')
{
    $gateway = refund_extract_gateway_base($profileGateway);
    if ($gateway !== '') {
        return $gateway . '/IKEv2SellerOffice4U/vpn_users.php';
    }

    $host = isset($_SERVER['HTTP_HOST']) ? trim((string)$_SERVER['HTTP_HOST']) : '';
    if ($host === '') {
        return '';
    }
    $scheme = (!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off') ? 'https' : 'http';
    return $scheme . '://' . $host . '/IKEv2SellerOffice4U/vpn_users.php';
}

function refund_call_vpn_users_disable($vpnUserId, $mbId, $profileGateway = '', $terminationAt = '', $terminationLabel = '')
{
    $vpnUserId = trim((string)$vpnUserId);
    $mbId = trim((string)$mbId);
    if ($vpnUserId === '') {
        return array('attempted' => false, 'status' => 'skip_empty_user_id');
    }

    $endpoint = refund_default_vpn_users_endpoint($profileGateway);
    if ($endpoint === '') {
        return array('attempted' => false, 'status' => 'skip_no_endpoint');
    }

    $secret = refund_load_shop_sync_secret();
    if ($secret === '') {
        return array('attempted' => false, 'status' => 'skip_no_shared_secret', 'endpoint' => $endpoint);
    }

    $terminationAt = trim((string)$terminationAt);
    $terminationLabel = trim((string)$terminationLabel);

    $postFields = array(
        'action' => 'disable_user',
        'user_id' => $vpnUserId,
        'sync_origin' => 'shop_backend',
        'reason' => 'app_refund_approve',
        'mb_id' => $mbId,
    );
    if ($terminationAt !== '') {
        $postFields['termination_at'] = $terminationAt;
    }
    if ($terminationLabel !== '') {
        $postFields['termination_label'] = $terminationLabel;
    }
    $payload = http_build_query($postFields, '', '&');
    $signature = hash_hmac('sha256', $payload, $secret);
    $postFields['sync_signature'] = $signature;
    $requestPayload = http_build_query($postFields, '', '&');
    $headers = array(
        'Content-Type: application/x-www-form-urlencoded; charset=UTF-8',
        'Accept: application/json',
        'X-Requested-With: XMLHttpRequest',
        'X-VPN-Sync-Origin: shop_backend',
        'X-VPN-Sync-Signature: ' . $signature,
    );

    $sendRequest = function ($targetEndpoint) use ($headers, $requestPayload) {
        $raw = '';
        $httpCode = 0;
        $transportError = '';
        $location = '';

        if (function_exists('curl_init')) {
            $ch = curl_init($targetEndpoint);
            curl_setopt($ch, CURLOPT_POST, true);
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
            curl_setopt($ch, CURLOPT_HEADER, true);
            curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 4);
            curl_setopt($ch, CURLOPT_TIMEOUT, 8);
            curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);
            curl_setopt($ch, CURLOPT_POSTFIELDS, $requestPayload);

            $rawResp = curl_exec($ch);
            if ($rawResp === false) {
                $transportError = (string)curl_error($ch);
            } else {
                $headerSize = (int)curl_getinfo($ch, CURLINFO_HEADER_SIZE);
                $headerBlob = (string)substr((string)$rawResp, 0, $headerSize);
                $raw = (string)substr((string)$rawResp, $headerSize);

                if (preg_match('/\r?\nLocation:\s*([^\r\n]+)/i', $headerBlob, $mLoc)) {
                    $location = trim((string)$mLoc[1]);
                }
            }
            $httpCode = (int)curl_getinfo($ch, CURLINFO_HTTP_CODE);
            curl_close($ch);
        } else {
            $ctx = stream_context_create(array(
                'http' => array(
                    'method' => 'POST',
                    'header' => implode("\r\n", $headers),
                    'content' => $requestPayload,
                    'timeout' => 8,
                    'ignore_errors' => true,
                ),
            ));
            $rawResp = @file_get_contents($targetEndpoint, false, $ctx);
            if ($rawResp === false) {
                $transportError = 'file_get_contents_failed';
                $raw = '';
            } else {
                $raw = (string)$rawResp;
            }
            if (isset($http_response_header) && is_array($http_response_header)) {
                foreach ($http_response_header as $line) {
                    if ($httpCode === 0 && preg_match('/^HTTP\/\S+\s+(\d{3})/i', $line, $m)) {
                        $httpCode = (int)$m[1];
                    }
                    if ($location === '' && preg_match('/^Location:\s*(.+)$/i', $line, $mLoc2)) {
                        $location = trim((string)$mLoc2[1]);
                    }
                }
            }
        }

        return array(
            'endpoint' => $targetEndpoint,
            'http_code' => $httpCode,
            'transport_error' => $transportError,
            'raw' => $raw,
            'location' => $location,
        );
    };

    $attemptEndpoints = array($endpoint);

    $seen = array();
    $attemptTrace = array();
    $last = array(
        'endpoint' => $endpoint,
        'http_code' => 0,
        'transport_error' => '',
        'raw' => '',
        'location' => '',
    );
    $success = false;
    $decoded = null;
    $isJson = false;

    $maxAttempts = 3;
    while (!empty($attemptEndpoints) && count($attemptTrace) < $maxAttempts) {
        $targetEndpoint = array_shift($attemptEndpoints);
        if ($targetEndpoint === '' || isset($seen[$targetEndpoint])) {
            continue;
        }
        $seen[$targetEndpoint] = true;

        $last = $sendRequest($targetEndpoint);
        $attemptTrace[] = array(
            'endpoint' => $last['endpoint'],
            'http_code' => (int)$last['http_code'],
            'transport_error' => (string)$last['transport_error'],
            'location' => (string)$last['location'],
        );

        $decoded = json_decode((string)$last['raw'], true);
        $isJson = is_array($decoded);
        $remoteSuccess = false;
        if ($isJson) {
            $resultVal = strtolower(trim((string)(isset($decoded['result']) ? $decoded['result'] : '')));
            $statusVal = strtolower(trim((string)(isset($decoded['status']) ? $decoded['status'] : '')));
            $successVal = isset($decoded['success']) ? $decoded['success'] : null;
            $okVal = isset($decoded['ok']) ? $decoded['ok'] : null;
            $remoteSuccess = !empty($successVal)
                || !empty($okVal)
                || $resultVal === 'success'
                || $statusVal === 'success'
                || $statusVal === 'ok';
        }
        if ($last['transport_error'] === '' && $last['http_code'] >= 200 && $last['http_code'] < 300 && $remoteSuccess) {
            $success = true;
            break;
        }

        if ($last['http_code'] >= 300 && $last['http_code'] < 400) {
            $location = trim((string)$last['location']);
            if ($location !== '') {
                if (preg_match('#^https?://#i', $location)) {
                    $attemptEndpoints[] = $location;
                } else {
                    $base = @parse_url($targetEndpoint);
                    if (is_array($base) && !empty($base['scheme']) && !empty($base['host'])) {
                        $portPart = isset($base['port']) ? (':' . (int)$base['port']) : '';
                        $prefix = strtolower((string)$base['scheme']) . '://' . $base['host'] . $portPart;
                        if (substr($location, 0, 1) === '/') {
                            $attemptEndpoints[] = $prefix . $location;
                        } else {
                            $basePath = isset($base['path']) ? (string)$base['path'] : '/';
                            $dir = rtrim(str_replace('\\', '/', dirname($basePath)), '/');
                            if ($dir === '') {
                                $dir = '';
                            }
                            $attemptEndpoints[] = $prefix . $dir . '/' . $location;
                        }
                    }
                }
            }
        }
    }

    $status = $success ? 'ok' : 'unknown';
    if (!$success) {
        $err = strtolower((string)$last['transport_error']);
        $loc = strtolower((string)$last['location']);
        $remoteAuthRequired = false;
        if ($isJson && is_array($decoded)) {
            $remoteAuthRequired = !empty($decoded['auth_required'])
                || (isset($decoded['error']) && strpos((string)$decoded['error'], '로그인이 필요') !== false);
        }
        if ($last['http_code'] >= 300 && $last['http_code'] < 400 && $loc !== '' && strpos($loc, 'login.php') !== false) {
            $status = 'auth_redirect';
        } elseif ($remoteAuthRequired) {
            $status = 'auth_required';
        } elseif ($err !== '' && strpos($err, 'connection refused') !== false) {
            $status = 'transport_connect_refused';
        } elseif ($err !== '') {
            $status = 'transport_error';
        } elseif ($last['http_code'] >= 300 && $last['http_code'] < 400) {
            $status = 'redirect_unresolved';
        } elseif ($last['http_code'] >= 400) {
            $status = 'http_error';
        } elseif ($last['http_code'] >= 200 && $last['http_code'] < 300) {
            $status = 'response_not_success';
        }
    }

    return array(
        'attempted' => true,
        'endpoint' => $last['endpoint'],
        'http_code' => (int)$last['http_code'],
        'transport_error' => (string)$last['transport_error'],
        'location' => (string)$last['location'],
        'status' => $status,
        'attempt_trace' => $attemptTrace,
        'raw' => (strlen((string)$last['raw']) > 400) ? substr((string)$last['raw'], 0, 400) . '...[truncated]' : (string)$last['raw'],
        'json' => $isJson ? $decoded : null,
        'success' => $success,
    );
}

function refund_force_local_vpn_users_terminate($vpnUserId, $terminationAt = '', $reason = '')
{
    $vpnUserId = trim((string)$vpnUserId);
    $terminationAt = trim((string)$terminationAt);
    $reason = trim((string)$reason);

    if ($vpnUserId === '') {
        return array(
            'attempted' => false,
            'success' => false,
            'status' => 'skip_empty_user_id',
        );
    }

    if ($terminationAt === '') {
        $terminationAt = date('Y-m-d H:i:s');
    }
    if ($reason === '') {
        $reason = 'app_refund_approve';
    }

    $candidateFiles = array(
        dirname(__DIR__) . '/IKEv2SellerOffice4U/vpn_users.php',
        dirname(__DIR__) . '/IKEv2SellerOffice/vpn_users.php',
    );

    $attempted = false;
    $errors = array();
    $lastFailure = null;

    foreach ($candidateFiles as $vpnUsersFile) {
        if (!is_file($vpnUsersFile)) {
            continue;
        }

        $attempted = true;
        try {
            if (!defined('VPN_USERS_SKIP_ACTION_ROUTER')) {
                define('VPN_USERS_SKIP_ACTION_ROUTER', true);
            }
            if (!defined('VPN_SESSIONS_SKIP_ACTION_ROUTER')) {
                define('VPN_SESSIONS_SKIP_ACTION_ROUTER', true);
            }

            if (!class_exists('VPNUserManager', false)) {
                @include_once $vpnUsersFile;
            }

            if (!class_exists('VPNUserManager')) {
                $errors[] = array('source' => $vpnUsersFile, 'error' => 'VPNUserManager class not found after include');
                continue;
            }
            if (!is_callable(array('VPNUserManager', 'terminateUser'))) {
                $errors[] = array('source' => $vpnUsersFile, 'error' => 'VPNUserManager::terminateUser is not callable');
                continue;
            }

            $ret = VPNUserManager::terminateUser($vpnUserId, $terminationAt, $reason);
            $ok = is_array($ret) && !empty($ret['success']);

            if (!$ok) {
                $retError = is_array($ret) && isset($ret['error']) ? trim((string)$ret['error']) : '';
                $status = 'terminate_failed';
                if ($retError !== '' && strpos($retError, '사용자를 찾을 수 없습니다') !== false) {
                    // 이미 존재하지 않는 사용자 = 이미 비활성화 상태 → 성공으로 처리
                    return array(
                        'attempted' => true,
                        'success' => true,
                        'status' => 'ok_user_not_found_already_absent',
                        'source' => $vpnUsersFile,
                        'result' => $ret,
                    );
                }
                $lastFailure = array(
                    'attempted' => true,
                    'success' => false,
                    'status' => $status,
                    'source' => $vpnUsersFile,
                    'result' => $ret,
                );
                $errors[] = array(
                    'source' => $vpnUsersFile,
                    'error' => is_array($ret) && isset($ret['error']) ? (string)$ret['error'] : 'terminate_failed',
                );
                continue;
            }

            return array(
                'attempted' => true,
                'success' => $ok,
                'status' => $ok ? 'ok' : 'terminate_failed',
                'source' => $vpnUsersFile,
                'result' => $ret,
            );
        } catch (Throwable $e) {
            $errors[] = array('source' => $vpnUsersFile, 'error' => $e->getMessage());
        }
    }

    if (is_array($lastFailure)) {
        $lastFailure['errors'] = $errors;
        return $lastFailure;
    }

    return array(
        'attempted' => $attempted,
        'success' => false,
        'status' => $attempted ? 'all_local_attempts_failed' : 'skip_no_vpn_users_file',
        'errors' => $errors,
    );
}

function table_has_column($mysqli, $tableName, $columnName)
{
    static $tableColumnsCache = array();

    $tableName = trim((string)$tableName);
    $columnName = trim((string)$columnName);
    if ($tableName === '' || $columnName === '' || !$mysqli) {
        return false;
    }

    if (!isset($tableColumnsCache[$tableName])) {
        $tableColumnsCache[$tableName] = array();
        $sql = "SHOW COLUMNS FROM `" . str_replace('`', '', $tableName) . "`";
        $res = $mysqli->query($sql);
        if ($res) {
            while ($col = $res->fetch_assoc()) {
                if (isset($col['Field']) && $col['Field'] !== '') {
                    $tableColumnsCache[$tableName][$col['Field']] = true;
                }
            }
            $res->free();
        }
    }

    return isset($tableColumnsCache[$tableName][$columnName]);
}

function is_api_request()
{
    if (isset($_REQUEST['api']) && $_REQUEST['api'] === '1') {
        return true;
    }

    $response = isset($_REQUEST['response']) ? strtolower(trim((string)$_REQUEST['response'])) : '';
    if ($response === 'json') {
        return true;
    }

    $xhr = isset($_SERVER['HTTP_X_REQUESTED_WITH']) ? strtolower((string)$_SERVER['HTTP_X_REQUESTED_WITH']) : '';
    return $xhr === 'xmlhttprequest';
}

function out_json($arr)
{
    header('Content-Type: application/json; charset=UTF-8');
    echo json_encode($arr, JSON_UNESCAPED_UNICODE);
    exit;
}

function success_json($msg, $data = array())
{
    $res = array(
        'result' => 'success',
        'status' => 'success',
        'message' => $msg,
        'msg' => $msg,
    );
    if (!empty($data)) {
        $res = array_merge($res, $data);
    }
    out_json($res);
}

function fail_json($msg, $data = array())
{
    $res = array(
        'result' => 'failure',
        'status' => 'failure',
        'message' => $msg,
        'msg' => $msg,
    );
    if (!empty($data)) {
        $res = array_merge($res, $data);
    }
    out_json($res);
}

function map_approve_error_code($msg)
{
    $msg = (string)$msg;

    if (strpos($msg, '이미 환불 처리') !== false) {
        return 'ALREADY_REFUNDED';
    }
    if (strpos($msg, '환불 포인트 내역 기록 확인에 실패') !== false) {
        return 'POINT_HISTORY_VERIFY_FAILED';
    }
    if (strpos($msg, 'insert_point 함수를 찾을 수 없습니다') !== false) {
        return 'POINT_API_MISSING';
    }
    if (strpos($msg, '환불 대상') !== false) {
        return 'REFUND_TARGET_NOT_FOUND';
    }
    if (strpos($msg, '주문') !== false && strpos($msg, '찾을 수 없습니다') !== false) {
        return 'ORDER_NOT_FOUND';
    }

    return 'REFUND_APPROVE_FAILED';
}

function redirect_with_html_fallback($url)
{
    if (!headers_sent()) {
        header('Location: ' . $url);
    }
    echo '<!doctype html><html lang="ko"><head><meta charset="utf-8"><meta http-equiv="refresh" content="0;url=' . h($url) . '"></head>'
        . '<body><script>location.replace(' . json_encode($url) . ');</script></body></html>';
    exit;
}

function resolve_member_id($rawMemberId, $rawMemberEmail)
{
    global $g5;

    $mbId = $rawMemberId;
    if ($rawMemberEmail !== '') {
        $mbId = $rawMemberEmail;
    }

    if ($mbId === '' || preg_match('/^VpNgueSt/', $mbId)) {
        return $mbId;
    }

    $resolvedMember = array();
    if ($rawMemberEmail !== '' || filter_var($mbId, FILTER_VALIDATE_EMAIL)) {
        $safeIdentifier = function_exists('sql_real_escape_string')
            ? sql_real_escape_string($mbId)
            : addslashes($mbId);
        $resolvedMember = sql_fetch(" select * from {$g5['member_table']} where mb_email = '{$safeIdentifier}' ");
    }

    if (!isset($resolvedMember['mb_id']) || !$resolvedMember['mb_id']) {
        $resolvedMember = get_member($mbId);
    }

    if (isset($resolvedMember['mb_id']) && $resolvedMember['mb_id']) {
        return $resolvedMember['mb_id'];
    }

    return $mbId;
}

function parse_product_code_from_profile_name($profileName)
{
    if (preg_match('/(VPN-\d+)/i', (string)$profileName, $m)) {
        return strtoupper($m[1]);
    }
    return '';
}

function parse_vpn_number_from_it_name($itName)
{
    if ($itName !== '' && preg_match('/VPN-(\d+)/i', (string)$itName, $m)) {
        return (int)$m[1];
    }
    return 0;
}

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

function find_target_profile($mysqli, $mbId, $profileId, $profileName, $profileGateway, $profileUsername)
{
    $itName = parse_product_code_from_profile_name($profileName);
    if ($itName === '' && req('it_name') !== '') {
        $itName = req('it_name');
    }

    $profileIdInt = ctype_digit((string)$profileId) ? (int)$profileId : 0;
    $vpnNoFromName = parse_vpn_number_from_it_name($itName);

    $selectCols = array('no', 'mb_id', 'remark', 'it_name', 'vpn_user_id', 'vpn_user_pwd', 'server_name', 'vpn_id_expire', 'enable');
    if (table_has_column($mysqli, 'vpnid_list', 'od_id')) {
        $selectCols[] = 'od_id';
    }
    if (table_has_column($mysqli, 'vpnid_list', 'memo')) {
        $selectCols[] = 'memo';
    }
    $selectSql = 'SELECT ' . implode(', ', $selectCols) . ' FROM vpnid_list WHERE enable=1';

    $attempts = array();
    if ($profileIdInt > 0) {
        $attempts[] = array(
            'sql' => $selectSql . ' AND (mb_id=? OR remark=?) AND no=? LIMIT 1',
            'types' => 'ssi',
            'vals' => array($mbId, $mbId, $profileIdInt),
        );
    }
    if ($vpnNoFromName > 0) {
        $attempts[] = array(
            'sql' => $selectSql . ' AND (mb_id=? OR remark=?) AND no=? LIMIT 1',
            'types' => 'ssi',
            'vals' => array($mbId, $mbId, $vpnNoFromName),
        );
    }
    if ($itName !== '' && $profileUsername !== '') {
        $attempts[] = array(
            'sql' => $selectSql . ' AND (mb_id=? OR remark=?) AND it_name=? AND vpn_user_id=? LIMIT 1',
            'types' => 'ssss',
            'vals' => array($mbId, $mbId, $itName, $profileUsername),
        );
    }
    if ($profileUsername !== '' && $profileGateway !== '') {
        $attempts[] = array(
            'sql' => $selectSql . ' AND (mb_id=? OR remark=?) AND vpn_user_id=? AND server_name=? LIMIT 1',
            'types' => 'ssss',
            'vals' => array($mbId, $mbId, $profileUsername, $profileGateway),
        );
    }

    foreach ($attempts as $attempt) {
        $stmt = $mysqli->prepare($attempt['sql']);
        if (!$stmt) {
            continue;
        }
        $stmt->bind_param($attempt['types'], ...$attempt['vals']);
        if (!$stmt->execute()) {
            $stmt->close();
            continue;
        }
        $res = $stmt->get_result();
        $row = $res ? $res->fetch_assoc() : null;
        $stmt->close();
        if ($row) {
            return array('ok' => true, 'row' => $row);
        }
    }

    return array('ok' => false, 'msg' => '대상 프로필을 찾지 못했습니다.');
}

function find_refund_context($mbId, $profile, $preferredOdId = '', $preferredItId = 0, $forUpdate = false)
{
    global $g5;

    $mbIdEsc = sql_esc($mbId);
    $preferredOdId = trim((string)$preferredOdId);
    $preferredItId = (int)$preferredItId;

    $profileOdId = isset($profile['od_id']) ? trim((string)$profile['od_id']) : '';
    $profileItId = parse_vpn_number_from_it_name(isset($profile['it_name']) ? (string)$profile['it_name'] : '');

    $candidateOdId = $preferredOdId !== '' ? $preferredOdId : $profileOdId;
    $candidateItId = $preferredItId > 0 ? $preferredItId : $profileItId;
    $lockSuffix = $forUpdate ? ' FOR UPDATE' : '';

    $cart = null;
    if ($candidateOdId !== '' && $candidateItId > 0) {
        $odIdEsc = sql_esc($candidateOdId);
        $itIdEsc = sql_esc((string)$candidateItId);
        $cart = sql_fetch("SELECT * FROM {$g5['g5_shop_cart_table']} WHERE od_id='{$odIdEsc}' AND it_id='{$itIdEsc}' AND mb_id='{$mbIdEsc}' AND ct_status IN ('입금','완료') ORDER BY ct_id DESC LIMIT 1{$lockSuffix}");
    }

    if (!$cart && $candidateItId > 0) {
        $itIdEsc = sql_esc((string)$candidateItId);
        $cart = sql_fetch("SELECT * FROM {$g5['g5_shop_cart_table']} WHERE it_id='{$itIdEsc}' AND mb_id='{$mbIdEsc}' AND ct_status IN ('입금','완료') ORDER BY ct_id DESC LIMIT 1{$lockSuffix}");
    }

    if (!$cart) {
        return array('ok' => false, 'msg' => '환불 대상 주문을 찾지 못했습니다.');
    }

    $odId = isset($cart['od_id']) ? trim((string)$cart['od_id']) : '';
    $itId = isset($cart['it_id']) ? (int)$cart['it_id'] : 0;
    if ($odId === '' || $itId <= 0) {
        return array('ok' => false, 'msg' => '환불 대상 주문 식별값이 올바르지 않습니다.');
    }

    $odIdEsc = sql_esc($odId);
    $itIdEsc = sql_esc((string)$itId);

    $order = sql_fetch("SELECT od_id, od_mod_history, od_receipt_point, od_receipt_point_for_extension_of_period FROM {$g5['g5_shop_order_table']} WHERE od_id='{$odIdEsc}' AND mb_id='{$mbIdEsc}' LIMIT 1{$lockSuffix}");
    if (!$order || !isset($order['od_id']) || trim((string)$order['od_id']) === '') {
        return array('ok' => false, 'msg' => '환불 대상 주문서를 찾지 못했습니다.');
    }

    $item = sql_fetch("SELECT * FROM {$g5['g5_shop_item_table']} WHERE it_id='{$itIdEsc}' AND it_spec_buyer_mb_id='{$mbIdEsc}' LIMIT 1{$lockSuffix}");
    if (!$item) {
        return array('ok' => false, 'msg' => '환불 대상 계약정보를 찾지 못했습니다.');
    }


    return array(
        'ok' => true,
        'od_id' => $odId,
        'it_id' => $itId,
        'order' => $order,
        'cart' => $cart,
        'item' => $item,
    );
}

function calc_web_refund_amount($cartRow, $itemRow)
{
    $from = isset($itemRow['it_spec_from_datetime']) ? trim((string)$itemRow['it_spec_from_datetime']) : '';
    $to = isset($itemRow['it_spec_to_datetime']) ? trim((string)$itemRow['it_spec_to_datetime']) : '';
    if ($from === '' || $to === '') {
        return array('ok' => false, 'msg' => '계약 시작/종료일 정보가 없습니다.');
    }

    try {
        $currentTime = new DateTime(G5_TIME_YMDHIS);
        $startTime = new DateTime($from);
        $endTime = new DateTime($to);
    } catch (Exception $e) {
        return array('ok' => false, 'msg' => '계약일시 파싱에 실패했습니다.');
    }

    $sellPrice = max(0, ((float)$cartRow['ct_price'] * (float)$cartRow['ct_qty']) + (float)$cartRow['ct_price_for_extension_of_period']);
    $totalUsageTime = max(0, $endTime->getTimestamp() - $startTime->getTimestamp());
    $totalUsageMinutes = $totalUsageTime / 60;
    if ($totalUsageMinutes <= 0) {
        return array('ok' => false, 'msg' => '계약기간 정보가 올바르지 않습니다.');
    }

    $minuteRate = ($totalUsageMinutes > 0) ? ($sellPrice / $totalUsageMinutes) : 0;
    $elapsedTimeRaw = $currentTime->getTimestamp() - $startTime->getTimestamp();
    $elapsedTime = min(max(0, $elapsedTimeRaw), $totalUsageTime);
    $remainingTime = max(0, $totalUsageTime - $elapsedTime);
    $remainingMinutes = $remainingTime / 60;

    $refundAmount   = max(0, $remainingMinutes * $minuteRate); // 미사용요금 (float)
    $usedAmount     = max(0, $sellPrice - $refundAmount);       // 사용한요금 (float)
    $feeAmount      = max(0, $refundAmount * (defined('VPNLINK_CANCELLATION_FEE_RATE') ? VPNLINK_CANCELLATION_FEE_RATE : 0.30));  // 해지수수료
    $refundAmountAfterPenalty = max(0, $refundAmount - $feeAmount);
    $refundRounded  = (int)round($refundAmountAfterPenalty);

    return array(
        'ok' => true,
        'refund_point' => $refundRounded,
        'refund_raw' => $refundAmountAfterPenalty,
        'remaining_time_sec' => (int)$remainingTime,
        'detail' => array(
            'total_paid'           => (int)round($sellPrice),
            'contract_start'       => $from,
            'contract_end'         => $to,
            'total_time_sec'       => (int)$totalUsageTime,
            'elapsed_time_sec'     => (int)$elapsedTime,
            'remaining_time_sec'   => (int)$remainingTime,
            'used_amount'          => (int)round($usedAmount),
            'used_amount_pt'       => round($usedAmount, 2),
            'unused_amount_pt'     => round($refundAmount, 2),
            'refundable_gross'     => (int)round($refundAmount),
            'fee_rate_pct'         => (int)round((defined('VPNLINK_CANCELLATION_FEE_RATE') ? VPNLINK_CANCELLATION_FEE_RATE : 0.30) * 100),
            'fee_amount'           => (int)round($feeAmount),
            'fee_amount_pt'        => round($feeAmount, 2),
            'refund_amount'        => $refundRounded,
        ),
    );
}

function estimate_refund_quote($mbId, $profile, $preferredOdId = '', $preferredItId = 0, $forUpdate = false)
{
    $ctx = find_refund_context($mbId, $profile, $preferredOdId, $preferredItId, $forUpdate);
    if (empty($ctx['ok'])) {
        return $ctx;
    }

    // 총결제금액 = 신규주문총액 + 연장주문총액 (od_receipt_point + od_receipt_point_for_extension_of_period)
    // ct_qty=0 버그로 cart 계산이 0이 되는 경우 order의 실제 결제금액으로 대체한다.
    $cartForCalc = $ctx['cart'];
    // 신규계약 금액(od_receipt_point)은 개통비/설치비로 간주 — 환불 계산 기준에서 제외
    $setupFee  = max(0, (float)(isset($ctx['order']['od_receipt_point']) ? $ctx['order']['od_receipt_point'] : 0));
    $totalPaid = $setupFee
               + (float)(isset($ctx['order']['od_receipt_point_for_extension_of_period']) ? $ctx['order']['od_receipt_point_for_extension_of_period'] : 0);

    // 동일 it_id의 다른 경로(vpnid_buy 연장 포함) 주문 결제금액 추가 합산
    if ($totalPaid > 0 && (int)$ctx['it_id'] > 0) {
        global $g5;
        $itIdSumEsc = sql_esc((string)(int)$ctx['it_id']);
        $mbIdSumEsc = sql_esc($mbId);
        $sumRow = sql_fetch(
            "SELECT COALESCE(SUM(o.od_receipt_point), 0) AS sum_receipt,"
            . " COALESCE(SUM(o.od_receipt_point_for_extension_of_period), 0) AS sum_ext"
            . " FROM {$g5['g5_shop_order_table']} o"
            . " INNER JOIN {$g5['g5_shop_cart_table']} c ON c.od_id = o.od_id AND c.mb_id = o.mb_id"
            . " WHERE o.mb_id = '{$mbIdSumEsc}'"
            . "   AND c.it_id = '{$itIdSumEsc}'"
            . "   AND c.ct_status IN ('입금', '완료')", false);
        if (is_array($sumRow)) {
            $allOrdersTotal = (float)$sumRow['sum_receipt'] + (float)$sumRow['sum_ext'];
            if ($allOrdersTotal > $totalPaid) {
                $totalPaid = $allOrdersTotal;
            }
        }
    }

    // 개통비/설치비를 제외한 이용요금을 환불 계산 기준(refundBase)으로 사용
    $subscriptionFee = max(0, $totalPaid - $setupFee);
    $refundBase      = ($subscriptionFee > 0) ? $subscriptionFee : $totalPaid;
    $setupFeeDisplay = ($subscriptionFee > 0) ? $setupFee : 0;

    if ($refundBase > 0) {
        $cartForCalc['ct_price'] = $refundBase;
        $cartForCalc['ct_qty']   = 1;
        $cartForCalc['ct_price_for_extension_of_period'] = 0;
    } elseif ($totalPaid > 0) {
        $cartForCalc['ct_price'] = $totalPaid;
        $cartForCalc['ct_qty']   = 1;
        $cartForCalc['ct_price_for_extension_of_period'] = 0;
    }

    $calc = calc_web_refund_amount($cartForCalc, $ctx['item']);
    if (empty($calc['ok'])) {
        return $calc;
    }

    // 개통비/설치비, 전체 결제금액을 detail 에 병합
    if (isset($calc['detail'])) {
        $calc['detail']['setup_fee']        = (int)round($setupFeeDisplay);
        $calc['detail']['total_paid_all']   = (int)round($totalPaid);
        $calc['detail']['subscription_fee'] = (int)round($refundBase);
    }

    // 동일 it_id 주문 목록 (계산 내역 표시용)
    $ordersBreakdown = array();
    if ($totalPaid > 0 && (int)$ctx['it_id'] > 0) {
        global $g5;
        $itIdBdEsc = sql_esc((string)(int)$ctx['it_id']);
        $mbIdBdEsc = sql_esc($mbId);
        $bdRes = sql_query(
            "SELECT o.od_id, o.od_time, o.od_receipt_point,"
            . " o.od_receipt_point_for_extension_of_period, o.od_mod_history"
            . " FROM {$g5['g5_shop_order_table']} o"
            . " INNER JOIN {$g5['g5_shop_cart_table']} c ON c.od_id=o.od_id AND c.mb_id=o.mb_id"
            . " WHERE o.mb_id='{$mbIdBdEsc}'"
            . "   AND c.it_id='{$itIdBdEsc}'"
            . "   AND c.ct_status IN ('입금','완료')"
            . " ORDER BY o.od_time ASC", false);
        while ($bdRow = sql_fetch_array($bdRes, false)) {
            $newAmt = (int)$bdRow['od_receipt_point'];
            $extAmt = (int)$bdRow['od_receipt_point_for_extension_of_period'];
            $modHist = (string)($bdRow['od_mod_history'] ?? '');
            $newEndTime = '';

            // 신규계약: od_receipt_point 금액 + od_mod_history에서 기간 파싱
            if ($newAmt > 0) {
                $newPeriodLabel = '';
                if ($modHist && preg_match('/신규계약\s*\((\d+)일(\d+)시간\)/', $modHist, $mNP)) {
                    $nDays = (int)$mNP[1]; $nHours = (int)$mNP[2];
                    $newPeriodLabel = ($nDays > 0 ? $nDays . '일' : '') . ($nHours > 0 ? $nHours . '시간' : '');
                    if (!$newPeriodLabel) $newPeriodLabel = '0시간';
                    $ts = strtotime($bdRow['od_time']) + $nDays * 86400 + $nHours * 3600;
                    if ($ts > 0) $newEndTime = date('Y-m-d H:i:s', $ts);
                }
                $ordersBreakdown[] = array(
                    'od_id'        => (string)$bdRow['od_id'],
                    'od_time'      => (string)$bdRow['od_time'],
                    'end_time'     => $newEndTime,
                    'period_label' => $newPeriodLabel,
                    'amount'       => $newAmt,
                    'type'         => '신규계약',
                );
            }

            // 계약연장: od_receipt_point_for_extension_of_period 금액 + od_mod_history에서 시각·기간 파싱
            if ($extAmt > 0) {
                $extTime = (string)$bdRow['od_time'];
                $extEndTime = '';
                $extPeriodLabel = '';
                if ($modHist) {
                    if (preg_match('/(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})\s+계약연장/', $modHist, $mET)) {
                        $extTime = $mET[1];
                    }
                    if (preg_match('/계약연장\s*\((\d+)일(\d+)시간\)/', $modHist, $mEP)) {
                        $eDays = (int)$mEP[1]; $eHours = (int)$mEP[2];
                        $extPeriodLabel = ($eDays > 0 ? $eDays . '일' : '') . ($eHours > 0 ? $eHours . '시간' : '');
                        if (!$extPeriodLabel) $extPeriodLabel = '0시간';
                        // 연장 종료일: 갭이 있으면(연장 결제 > 신규 종료 + 5분) 결제 시각 기준, 없으면 신규 종료일 기준
                        $baseTs_new = $newEndTime ? strtotime($newEndTime) : 0;
                        $baseTs_ext = strtotime($extTime);
                        $hasGapExt = ($baseTs_new > 0 && $baseTs_ext > 0 && $baseTs_ext > $baseTs_new + 300);
                        $baseTs = $hasGapExt ? $baseTs_ext : ($baseTs_new > 0 ? $baseTs_new : $baseTs_ext);
                        $ts = $baseTs + $eDays * 86400 + $eHours * 3600;
                        if ($ts > 0) $extEndTime = date('Y-m-d H:i:s', $ts);
                    }
                }
                $ordersBreakdown[] = array(
                    'od_id'        => (string)$bdRow['od_id'],
                    'od_time'      => $extTime,
                    'end_time'     => $extEndTime,
                    'period_label' => $extPeriodLabel,
                    'amount'       => $extAmt,
                    'type'         => '계약연장',
                );
            }
        }
    }

    // ── 마지막 연속 블록 로직: 만료 후 재연장된 경우 환불 대상 블록만 계산 ──
    $nowTs = time();
    $lastGapIdx = -1;  // 마지막 갭 이후 첫 번째 항목 인덱스 (-1이면 갭 없음)
    $prevEndTs = null;
    $bdCount = count($ordersBreakdown);
    for ($i = 0; $i < $bdCount; $i++) {
        $entryOdTs = !empty($ordersBreakdown[$i]['od_time']) ? strtotime($ordersBreakdown[$i]['od_time']) : 0;
        // 현재 항목 시작이 이전 항목 종료 이후 5분 초과인 경우 갭으로 간주
        if ($prevEndTs !== null && $entryOdTs > 0 && $entryOdTs > $prevEndTs + 300) {
            $lastGapIdx = $i;
        }
        if (!empty($ordersBreakdown[$i]['end_time'])) {
            $prevEndTs = strtotime($ordersBreakdown[$i]['end_time']);
        }
    }

    // 각 항목에 환불 대상 여부 플래그 설정
    $refundableAmount = 0;
    $refundableStartTs = null;
    $refundableEndTs = null;
    for ($i = 0; $i < $bdCount; $i++) {
        $isRefundable = ($lastGapIdx < 0 || $i >= $lastGapIdx);
        $ordersBreakdown[$i]['refundable'] = $isRefundable;
        if ($isRefundable) {
            $refundableAmount += (int)$ordersBreakdown[$i]['amount'];
            if ($refundableStartTs === null) {
                $refundableStartTs = !empty($ordersBreakdown[$i]['od_time']) ? strtotime($ordersBreakdown[$i]['od_time']) : null;
            }
            if (!empty($ordersBreakdown[$i]['end_time'])) {
                $refundableEndTs = strtotime($ordersBreakdown[$i]['end_time']);
            }
        }
    }

    // it_spec_to_datetime이 권위 있는 계약 종료일 — breakdown의 계산된 end_time을 덮어씀
    // (갭 시나리오에서 연장 end_time = 원계약_end + 연장기간 으로 잘못 계산될 수 있음)
    $specToTs = !empty($ctx['item']['it_spec_to_datetime']) ? strtotime($ctx['item']['it_spec_to_datetime']) : 0;
    if ($specToTs > 0 && $lastGapIdx > 0) {
        $refundableEndTs = $specToTs;
    }

    // 갭이 있는 경우 환불 대상 블록 기준으로 calc 값 재계산
    if ($lastGapIdx > 0 && $refundableStartTs !== null && $refundableEndTs !== null && $refundableAmount > 0) {
        $refPeriodSec  = max(0, $refundableEndTs - $refundableStartTs);
        $refElapsedSec = max(0, min($nowTs - $refundableStartTs, $refPeriodSec));
        $refRemainSec  = max(0, $refPeriodSec - $refElapsedSec);
        $_feeRateBlk   = defined('VPNLINK_CANCELLATION_FEE_RATE') ? VPNLINK_CANCELLATION_FEE_RATE : 0.15;
        $ratePtPerSec  = $refPeriodSec > 0 ? $refundableAmount / $refPeriodSec : 0;
        $refUnused     = $ratePtPerSec * $refRemainSec;
        $refFee        = $refUnused * $_feeRateBlk;
        $refRefund     = max(0, $refUnused - $refFee);
        $calc['refund_point']       = (int)round($refRefund);
        $calc['remaining_time_sec'] = (int)$refRemainSec;
        if (isset($calc['detail'])) {
            $calc['detail']['contract_start']      = date('Y-m-d H:i:s', $refundableStartTs);
            $calc['detail']['total_time_sec']      = (int)$refPeriodSec;
            $calc['detail']['elapsed_time_sec']    = (int)$refElapsedSec;
            $calc['detail']['remaining_time_sec']  = (int)$refRemainSec;
            $calc['detail']['subscription_fee']    = (int)round($refundableAmount);
            $calc['detail']['total_paid_all']      = (int)round($refundableAmount);
            $calc['detail']['setup_fee']           = 0;
            $calc['detail']['unused_amount_pt']    = round($refUnused, 2);
            $calc['detail']['refundable_gross']    = (int)round($refUnused);
            $calc['detail']['fee_amount']          = (int)round($refFee);
            $calc['detail']['fee_amount_pt']       = round($refFee, 2);
            $calc['detail']['refund_amount']       = (int)round($refRefund);
            $calc['detail']['used_amount_pt']      = round($refundableAmount - $refUnused, 2);
            $calc['detail']['used_amount']         = (int)round($refundableAmount - $refUnused);
        }
    }

    // 항상 breakdown 메타 정보 추가
    if (isset($calc['detail'])) {
        $calc['detail']['last_gap_idx']       = $lastGapIdx;
        $calc['detail']['refundable_amount']  = (int)$refundableAmount;
        $calc['detail']['gap_sec']            = 0;  // 새 로직에서는 gap_sec 불필요
    }

    return array(
        'ok' => true,
        'od_id' => $ctx['od_id'],
        'it_id' => $ctx['it_id'],
        'order' => $ctx['order'],
        'cart' => $ctx['cart'],
        'item' => $ctx['item'],
        'refund_point' => (int)$calc['refund_point'],
        'remaining_time_sec' => (int)$calc['remaining_time_sec'],
        'orders_breakdown' => $ordersBreakdown,
        'detail' => isset($calc['detail']) ? $calc['detail'] : array(),
    );
}

function build_order_context_target($mbId, $odId = '', $itId = 0)
{
    $itId = (int)$itId;
    $seedProfile = array(
        'od_id' => (string)$odId,
        'it_name' => $itId > 0 ? ('VPN-' . $itId) : '',
    );
    $ctx = find_refund_context($mbId, $seedProfile, $odId, $itId, false);
    if (empty($ctx['ok'])) {
        return array('ok' => false, 'msg' => isset($ctx['msg']) ? $ctx['msg'] : '주문 기반 대상 조회 실패');
    }

    $itemName = isset($ctx['item']['it_name']) ? (string)$ctx['item']['it_name'] : ($itId > 0 ? ('VPN-' . $itId) : '');
    return array(
        'ok' => true,
        'row' => array(
            'no' => $itId,
            'it_name' => $itemName,
            'vpn_user_id' => '',
            'vpn_id_expire' => isset($ctx['item']['it_spec_to_datetime']) ? (string)$ctx['item']['it_spec_to_datetime'] : '',
            'od_id' => isset($ctx['od_id']) ? (string)$ctx['od_id'] : (string)$odId,
        ),
    );
}

function parse_refund_order_memo_params($memo)
{
    $memo = (string)$memo;
    $odId = '';
    $itId = 0;
    $profileGateway = '';
    $profileUsername = '';

    if (preg_match('/(?:^|;)od_id=([^;]+)/', $memo, $mOd)) {
        $odId = trim((string)$mOd[1]);
    }
    if (preg_match('/(?:^|;)it_id=([0-9]+)/', $memo, $mIt)) {
        $itId = (int)$mIt[1];
    }
    if (preg_match('/(?:^|;)profile_gateway=([^;]+)/', $memo, $mGw)) {
        $profileGateway = rawurldecode(trim((string)$mGw[1]));
    }
    if (preg_match('/(?:^|;)profile_username=([^;]+)/', $memo, $mUser)) {
        $profileUsername = rawurldecode(trim((string)$mUser[1]));
    }

    return array(
        'od_id' => $odId,
        'it_id' => $itId,
        'profile_gateway' => $profileGateway,
        'profile_username' => $profileUsername,
    );
}

function credit_refund_point($mysqli, $mbId, $point, $orderNo)
{
    global $g5;

    $point = (int)$point;
    if ($point <= 0 || $mbId === '') {
        return true;
    }

    // GNUBoard 공용 포인트 API가 있으면 우선 사용 (중복 적립 방지/이력 관리)
    if (function_exists('insert_point')) {
        insert_point(
            $mbId,
            $point,
            'VPN 환불 적립 주문번호 ' . $orderNo,
            '@refund',
            (string)$orderNo,
            '환불'
        );
        return true;
    }

    // fallback: 최소한 회원 포인트 잔액은 증가시킨다.
    $memberTable = isset($g5['member_table']) ? $g5['member_table'] : 'g5_member';
    $stmt = $mysqli->prepare("UPDATE {$memberTable} SET mb_point = mb_point + ? WHERE mb_id = ? LIMIT 1");
    if (!$stmt) {
        throw new Exception('회원 포인트 적립 prepare 실패: ' . $mysqli->error);
    }
    $stmt->bind_param('is', $point, $mbId);
    if (!$stmt->execute()) {
        $err = $stmt->error;
        $stmt->close();
        throw new Exception('회원 포인트 적립 execute 실패: ' . $err);
    }
    $stmt->close();
    return true;
}

function apply_refund_point_with_history($mbId, $point, $odId, $itId, $mbName)
{
    global $g5;

    $point = (int)$point;
    $mbId = trim((string)$mbId);
    $odId = trim((string)$odId);
    $itId = (int)$itId;
    $mbName = trim((string)$mbName);

    if ($point <= 0 || $mbId === '' || $odId === '' || $itId <= 0) {
        return array(
            'ok' => true,
            'skipped' => true,
            'point' => $point,
        );
    }

    $mbIdEsc = sql_esc($mbId);
    $poRelAction = $odId . '|' . $itId;
    $poRelActionEsc = sql_esc($poRelAction);

    $beforeMember = sql_fetch("SELECT mb_point FROM {$g5['member_table']} WHERE mb_id='{$mbIdEsc}' LIMIT 1");
    $beforePoint = isset($beforeMember['mb_point']) ? (int)$beforeMember['mb_point'] : 0;

    if (!function_exists('insert_point')) {
        return array(
            'ok' => false,
            'error' => 'insert_point 함수를 찾을 수 없습니다.',
        );
    }

    insert_point(
        $mbId,
        $point,
        "계약해지 주문번호 {$odId} VPN-{$itId} 환불 (이용자: {$mbId} / {$mbName})",
        '@shoprefund|' . time(),
        $mbId . '|' . $mbId,
        $poRelAction
    );

    $pointRow = sql_fetch("SELECT po_id, po_point, po_content, po_datetime FROM {$g5['point_table']} WHERE mb_id='{$mbIdEsc}' AND po_rel_action='{$poRelActionEsc}' AND po_rel_table LIKE '@shoprefund%' ORDER BY po_id DESC LIMIT 1");
    if (!$pointRow || !isset($pointRow['po_id']) || (int)$pointRow['po_point'] !== $point) {
        return array(
            'ok' => false,
            'error' => '환불 포인트 내역 기록 확인에 실패했습니다.',
            'expected_point' => $point,
            'actual_point' => isset($pointRow['po_point']) ? (int)$pointRow['po_point'] : null,
        );
    }

    $afterMember = sql_fetch("SELECT mb_point FROM {$g5['member_table']} WHERE mb_id='{$mbIdEsc}' LIMIT 1");
    $afterPoint = isset($afterMember['mb_point']) ? (int)$afterMember['mb_point'] : 0;

    return array(
        'ok' => true,
        'skipped' => false,
        'point' => $point,
        'before_point' => $beforePoint,
        'after_point' => $afterPoint,
        'point_log_id' => isset($pointRow['po_id']) ? (int)$pointRow['po_id'] : 0,
    );
}

function force_disable_vpnid_after_refund($mysqli, $mbId, $vpnNo, $odId, $itId, $refundDatetime)
{
    $mbId = trim((string)$mbId);
    $vpnNo = (int)$vpnNo;
    $odId = trim((string)$odId);
    $itId = (int)$itId;
    $refundDatetime = trim((string)$refundDatetime);

    if ($mbId === '' || $refundDatetime === '') {
        return array('ok' => false, 'msg' => 'vpnid 강제 비활성화 인자 부족');
    }

    $mbIdEsc = sql_esc($mbId);
    $refundEsc = sql_esc($refundDatetime);
    $whereParts = array();

    if ($vpnNo > 0) {
        $whereParts[] = "(no = '" . $vpnNo . "' AND (mb_id = '{$mbIdEsc}' OR remark = '{$mbIdEsc}'))";
    }

    if ($odId !== '' && table_has_column($mysqli, 'vpnid_list', 'od_id')) {
        $odEsc = sql_esc($odId);
        $whereParts[] = "(od_id = '{$odEsc}' AND (mb_id = '{$mbIdEsc}' OR remark = '{$mbIdEsc}'))";
    }

    if ($itId > 0) {
        $itNameEsc = sql_esc('VPN-' . $itId);
        $whereParts[] = "(it_name = '{$itNameEsc}' AND (mb_id = '{$mbIdEsc}' OR remark = '{$mbIdEsc}'))";
    }

    if (empty($whereParts)) {
        return array('ok' => false, 'msg' => 'vpnid 강제 비활성화 대상을 찾을 수 없습니다.');
    }

    $setParts = array(
        "enable = 0",
        "vpn_id_expire = '{$refundEsc}'"
    );
    if (table_has_column($mysqli, 'vpnid_list', 'update_datetime')) {
        $setParts[] = 'update_datetime = NOW()';
    }

    $sql = 'UPDATE vpnid_list SET ' . implode(', ', $setParts)
        . ' WHERE ' . implode(' OR ', $whereParts);
    $res = sql_query($sql, false);
    if ($res === false) {
        return array('ok' => false, 'msg' => 'vpnid_list 강제 비활성화 쿼리 실패');
    }

    $affected = isset($mysqli->affected_rows) ? (int)$mysqli->affected_rows : 0;
    return array('ok' => true, 'affected' => $affected);
}

function ensure_refund_order_table($mysqli)
{
    $sql = "CREATE TABLE IF NOT EXISTS vpn_refund_orders (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        order_no VARCHAR(40) NOT NULL,
        mb_id VARCHAR(100) NOT NULL,
        vpn_no INT NOT NULL,
        vpn_business_no INT NULL,
        it_name VARCHAR(100) NOT NULL,
        vpn_user_id VARCHAR(100) NOT NULL,
        refund_point INT NOT NULL DEFAULT 0,
        status VARCHAR(20) NOT NULL DEFAULT 'pending',
        memo VARCHAR(255) NULL,
        created_at DATETIME NOT NULL,
        refunded_at DATETIME NULL,
        approved_by VARCHAR(50) NULL,
        PRIMARY KEY (id),
        UNIQUE KEY uq_order_no (order_no),
        KEY idx_mb_status (mb_id, status),
        KEY idx_vpn_no (vpn_no)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

    return (bool)$mysqli->query($sql);
}

function make_order_no()
{
    return 'R' . date('YmdHis') . mt_rand(1000, 9999);
}

$action = req('action', '');

$postMbEmail = req('mb_email', '');
$postMbId = req('mb_id', '');
$postUserId = req('user_id', '');
$rawMemberId = $postMbId !== '' ? $postMbId : $postUserId;
$mbId = resolve_member_id($rawMemberId, $postMbEmail);

$profileId = req('profile_id', '');
$profileName = req('profile_name', '');
$profileGateway = req('profile_gateway', '');
$profileUsername = req('profile_username', '');
$orderOdId = req('od_id', '');
$orderItId = (int)req('it_id', 0);
$refundMemoReq = req('refund_memo', '');

if ($mbId === '') {
    if (is_api_request() || $action !== '') {
        fail_json('mb_id가 필요합니다.');
    }
    echo 'mb_id required';
    exit;
}

if (!$mysqli) {
    if (is_api_request() || $action !== '') {
        fail_json('DB 연결 미구성');
    }
    echo 'DB connection not configured';
    exit;
}

if (isset($_REQUEST['vcheck']) && (string)$_REQUEST['vcheck'] === '1') {
    success_json('version check', array('version' => VPN_REFUND_ORDER_VERSION));
}

$requiresTargetLookup = !in_array($action, array('pay', 'approve'), true);
$target = null;
if ($requiresTargetLookup) {
    $found = find_target_profile($mysqli, $mbId, $profileId, $profileName, $profileGateway, $profileUsername);
    if (!$found['ok']) {
        $fallback = build_order_context_target($mbId, $orderOdId, $orderItId);
        if (!empty($fallback['ok'])) {
            $found = $fallback;
        } else {
            if (is_api_request() || $action !== '') {
                fail_json(isset($found['msg']) ? $found['msg'] : '대상 조회 실패', array(
                    'fallback_msg' => isset($fallback['msg']) ? $fallback['msg'] : '',
                ));
            }
            echo '대상 프로필을 찾지 못했습니다.';
            exit;
        }
    }
    $target = $found['row'];
}

if ($action === 'quote') {
    $quote = estimate_refund_quote($mbId, $target, $orderOdId, $orderItId, false);
    if (empty($quote['ok'])) {
        fail_json(isset($quote['msg']) ? $quote['msg'] : '환불 견적 계산 실패');
    }

    $refundPoint = (int)$quote['refund_point'];
    $vpnNo = isset($target['no']) ? (int)$target['no'] : 0;
    $businessVpnNo = parse_vpn_number_from_it_name(isset($target['it_name']) ? $target['it_name'] : '');

    success_json('환불 견적 계산 완료', array(
        'action' => 'quote',
        'vpn_no' => $vpnNo,
        'internal_profile_no' => $vpnNo,
        'business_vpn_no' => $businessVpnNo,
        'it_name' => isset($target['it_name']) ? $target['it_name'] : '',
        'vpn_user_id' => isset($target['vpn_user_id']) ? $target['vpn_user_id'] : '',
        'current_expire' => isset($target['vpn_id_expire']) ? $target['vpn_id_expire'] : '',
        'refund_point' => $refundPoint,
        'od_id' => isset($quote['od_id']) ? $quote['od_id'] : '',
        'it_id' => isset($quote['it_id']) ? (int)$quote['it_id'] : 0,
        'breakdown' => isset($quote['detail']) ? $quote['detail'] : array(),
        'orders_breakdown' => isset($quote['orders_breakdown']) ? $quote['orders_breakdown'] : array(),
    ));
}

if ($action === 'create_order') {
    if (!ensure_refund_order_table($mysqli)) {
        fail_json('환불 주문 테이블 준비 실패: ' . $mysqli->error);
    }

    $quote = estimate_refund_quote($mbId, $target, $orderOdId, $orderItId, false);
    if (empty($quote['ok'])) {
        fail_json(isset($quote['msg']) ? $quote['msg'] : '환불 주문 생성 전 견적 계산 실패');
    }

    $refundPoint = (int)$quote['refund_point'];
    $orderNo = make_order_no();
    $vpnNo = isset($target['no']) ? (int)$target['no'] : 0;
    $itName = isset($target['it_name']) ? (string)$target['it_name'] : '';
    $vpnUserId = isset($target['vpn_user_id']) ? (string)$target['vpn_user_id'] : '';
    $vpnBusinessNo = parse_vpn_number_from_it_name($itName);

    $memo = 'refund_request_via_app;od_id=' . (isset($quote['od_id']) ? $quote['od_id'] : '')
        . ';it_id=' . (isset($quote['it_id']) ? (int)$quote['it_id'] : 0)
        . ';profile_gateway=' . rawurlencode(isset($target['server_name']) ? (string)$target['server_name'] : '')
        . ';profile_username=' . rawurlencode($vpnUserId);

    $sql = "INSERT INTO vpn_refund_orders (order_no, mb_id, vpn_no, vpn_business_no, it_name, vpn_user_id, refund_point, status, memo, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, 'pending', ?, NOW())";
    $stmt = $mysqli->prepare($sql);
    if (!$stmt) {
        fail_json('환불 주문 생성 prepare 실패: ' . $mysqli->error);
    }

    $stmt->bind_param('ssiissis', $orderNo, $mbId, $vpnNo, $vpnBusinessNo, $itName, $vpnUserId, $refundPoint, $memo);
    if (!$stmt->execute()) {
        $err = $stmt->error;
        $stmt->close();
        fail_json('환불 주문 생성 실패: ' . $err);
    }
    $stmt->close();

    $payUrl = $_SERVER['PHP_SELF'] . '?action=pay&order_no=' . urlencode($orderNo) . '&mb_id=' . urlencode($mbId);

    success_json('환불 주문이 생성되었습니다.', array(
        'action' => 'create_order',
        'order_no' => $orderNo,
        'refund_point' => $refundPoint,
        'pay_url' => $payUrl,
    ));
}

if ($action === 'approve') {
    $orderNo = req('order_no', '');
    if ($orderNo === '') {
        fail_json('order_no가 필요합니다.');
    }

    if (!ensure_refund_order_table($mysqli)) {
        fail_json('환불 주문 테이블 준비 실패: ' . $mysqli->error);
    }

    $apiMode = is_api_request();

    $mysqli->begin_transaction();
    try {
        $stmt = $mysqli->prepare('SELECT * FROM vpn_refund_orders WHERE order_no=? AND mb_id=? LIMIT 1 FOR UPDATE');
        if (!$stmt) {
            throw new Exception('환불 주문 조회 실패: ' . $mysqli->error);
        }
        $stmt->bind_param('ss', $orderNo, $mbId);
        if (!$stmt->execute()) {
            throw new Exception('환불 주문 조회 execute 실패: ' . $stmt->error);
        }
        $order = $stmt->get_result()->fetch_assoc();
        $stmt->close();

        if (!$order) {
            throw new Exception('환불 주문을 찾을 수 없습니다.');
        }
        if ((string)$order['status'] === 'refunded') {
            throw new Exception('이미 환불 처리된 주문입니다.');
        }

        $vpnNo = (int)$order['vpn_no'];
        $memoParams = parse_refund_order_memo_params(isset($order['memo']) ? $order['memo'] : '');

        $approveOdId = req('od_id', '');
        if ($approveOdId === '') {
            $approveOdId = isset($memoParams['od_id']) ? (string)$memoParams['od_id'] : '';
        }

        $approveItId = (int)req('it_id', 0);
        if ($approveItId <= 0 && isset($memoParams['it_id'])) {
            $approveItId = (int)$memoParams['it_id'];
        }

        $profile = null;
        if ($vpnNo > 0) {
            $approveSelectCols = array('no', 'mb_id', 'remark', 'it_name', 'vpn_user_id', 'server_name', 'vpn_id_expire', 'enable');
            if (table_has_column($mysqli, 'vpnid_list', 'od_id')) {
                $approveSelectCols[] = 'od_id';
            } else {
                $approveSelectCols[] = 'NULL AS od_id';
            }
            $approveProfileSql = 'SELECT ' . implode(', ', $approveSelectCols) . ' FROM vpnid_list WHERE no=? AND (mb_id=? OR remark=?) LIMIT 1 FOR UPDATE';
            $stmt = $mysqli->prepare($approveProfileSql);
            if (!$stmt) {
                throw new Exception('프로필 조회 실패: ' . $mysqli->error);
            }
            $stmt->bind_param('iss', $vpnNo, $mbId, $mbId);
            if (!$stmt->execute()) {
                throw new Exception('프로필 조회 execute 실패: ' . $stmt->error);
            }
            $profile = $stmt->get_result()->fetch_assoc();
            $stmt->close();
        }

        if (!$profile) {
            if ($approveItId <= 0) {
                $approveItId = parse_vpn_number_from_it_name(isset($order['it_name']) ? (string)$order['it_name'] : '');
            }
            $profile = array(
                'no' => $vpnNo > 0 ? $vpnNo : $approveItId,
                'it_name' => isset($order['it_name']) ? (string)$order['it_name'] : ($approveItId > 0 ? ('VPN-' . $approveItId) : ''),
                'vpn_user_id' => isset($order['vpn_user_id']) ? (string)$order['vpn_user_id'] : '',
                'server_name' => isset($memoParams['profile_gateway']) ? (string)$memoParams['profile_gateway'] : '',
                'vpn_id_expire' => '',
                'od_id' => $approveOdId,
            );
        }

        if ($approveOdId === '') {
            $approveOdId = isset($profile['od_id']) ? trim((string)$profile['od_id']) : '';
        }
        if ($approveItId <= 0) {
            $approveItId = parse_vpn_number_from_it_name(isset($profile['it_name']) ? (string)$profile['it_name'] : '');
        }

        $quote = estimate_refund_quote($mbId, $profile, $approveOdId, $approveItId, true);
        if (empty($quote['ok'])) {
            throw new Exception(isset($quote['msg']) ? $quote['msg'] : '환불 대상 산정 실패');
        }

        $odId = (string)$quote['od_id'];
        $itId = (int)$quote['it_id'];
        $refundAmountAfterPenalty = (int)$quote['refund_point'];
        $realRefundDatetime = G5_TIME_YMDHIS;
        $refundMemo = trim($refundMemoReq);
        if ($refundMemo === '') {
            $refundMemo = '앱 환불요청';
        }

        $odIdEsc = sql_esc($odId);
        $itIdEsc = sql_esc((string)$itId);
        $mbIdEsc = sql_esc($mbId);
        $refundMemoEsc = sql_esc($refundMemo);

        $poRelAction = $odId . '|' . $itId;
        $poRelActionEsc = sql_esc($poRelAction);
        $rowPointSum = sql_fetch("SELECT sum(po_point) as sum_po_point FROM {$g5['point_table']} WHERE mb_id = '{$mbIdEsc}' AND po_rel_table LIKE '@shoprefund%' AND po_rel_action = '{$poRelActionEsc}'");
        $oldPoint = isset($rowPointSum['sum_po_point']) ? (int)$rowPointSum['sum_po_point'] : 0;
        if ($oldPoint > 0 && function_exists('insert_point')) {
            insert_point($mbId, $oldPoint * (-1), "계약해지 주문번호 {$odId} VPN-{$itId} 환불 취소 (시스템 자동처리)", "@shoprefundcancel|" . time(), $mbId . '|' . $mbId, $odId . '|' . $itId);
        }

        sql_query("UPDATE {$g5['g5_shop_cart_table']} SET real_refund_datetime = '{$realRefundDatetime}', real_refund_amount = '{$refundAmountAfterPenalty}', real_refund_memo = '{$refundMemoEsc}' WHERE od_id = '{$odIdEsc}' AND it_id = '{$itIdEsc}' AND mb_id = '{$mbIdEsc}'");

        sql_query("UPDATE {$g5['g5_shop_item_table']} SET it_spec_to_datetime = '{$realRefundDatetime}' WHERE it_id = '{$itIdEsc}' AND it_spec_buyer_mb_id = '{$mbIdEsc}'");

        $rowTotalRealRefund = sql_fetch("SELECT sum(real_refund_amount) AS total_real_refund_amount FROM {$g5['g5_shop_cart_table']} WHERE od_id = '{$odIdEsc}' AND mb_id = '{$mbIdEsc}'");
        $sumRealRefundAmount = round(isset($rowTotalRealRefund['total_real_refund_amount']) ? (float)$rowTotalRealRefund['total_real_refund_amount'] : 0);

        $rowOdMod = sql_fetch("SELECT od_mod_history FROM {$g5['g5_shop_order_table']} WHERE od_id = '{$odIdEsc}' AND mb_id = '{$mbIdEsc}'");
        $existingOdModHistory = trim(isset($rowOdMod['od_mod_history']) ? (string)$rowOdMod['od_mod_history'] : '');
        $memberRow = get_member($mbId);
        $mbName = isset($memberRow['mb_name']) ? (string)$memberRow['mb_name'] : $mbId;
        $newOdModHistory = G5_TIME_YMDHIS . " 계약해지 VPN-{$itId} 환불 (이용자: {$mbId} / {$mbName}) " . display_point($refundAmountAfterPenalty);
        $updatedOdModHistory = trim($existingOdModHistory . "\n" . $newOdModHistory);
        $updatedOdModHistoryEsc = sql_esc($updatedOdModHistory);
        sql_query("UPDATE {$g5['g5_shop_order_table']} SET od_refund_price = '{$sumRealRefundAmount}', od_mod_history = '{$updatedOdModHistoryEsc}' WHERE od_id = '{$odIdEsc}' AND mb_id = '{$mbIdEsc}'");

        $pointApplyRet = apply_refund_point_with_history($mbId, $refundAmountAfterPenalty, $odId, $itId, $mbName);
        if (empty($pointApplyRet['ok'])) {
            throw new Exception(isset($pointApplyRet['error']) ? $pointApplyRet['error'] : '환불 포인트 반영에 실패했습니다.');
        }

        $forceDisableRet = force_disable_vpnid_after_refund($mysqli, $mbId, $vpnNo, $odId, $itId, $realRefundDatetime);
        if (empty($forceDisableRet['ok'])) {
            throw new Exception(isset($forceDisableRet['msg']) ? $forceDisableRet['msg'] : 'vpnid 즉시 비활성화 실패');
        }

        $syncUserId = isset($profile['vpn_user_id']) ? trim((string)$profile['vpn_user_id']) : '';
        if ($syncUserId === '') {
            $syncUserId = isset($order['vpn_user_id']) ? trim((string)$order['vpn_user_id']) : '';
        }
        if ($syncUserId === '') {
            $syncUserId = isset($memoParams['profile_username']) ? trim((string)$memoParams['profile_username']) : '';
        }
        if ($syncUserId === '') {
            throw new Exception('vpn_users 동기화 대상 user_id 식별에 실패했습니다.');
        }

        $syncGateway = isset($profile['server_name']) ? trim((string)$profile['server_name']) : '';
        if ($syncGateway === '') {
            $syncGateway = isset($memoParams['profile_gateway']) ? trim((string)$memoParams['profile_gateway']) : '';
        }

        $vpnUsersSyncRet = refund_call_vpn_users_disable(
            $syncUserId,
            $mbId,
            $syncGateway,
            $realRefundDatetime,
            '해지됨'
        );

        $vpnUsersLocalForceRet = array(
            'attempted' => false,
            'success' => false,
            'status' => 'not_needed',
        );
        $vpnUsersSyncForced = false;

        if (empty($vpnUsersSyncRet['success'])) {
            $vpnUsersSyncForced = true;
            $vpnUsersLocalForceRet = refund_force_local_vpn_users_terminate(
                $syncUserId,
                $realRefundDatetime,
                'app_refund_approve'
            );
            refund_log('vpn_users_disable_local_force_result', array(
                'order_no' => $orderNo,
                'mb_id' => $mbId,
                'sync_user_id' => $syncUserId,
                'local_force_result' => $vpnUsersLocalForceRet,
            ));

            if (empty($vpnUsersLocalForceRet['success'])) {
                // VPN sync 실패는 환불 자체를 차단하지 않는다.
                // force_disable_vpnid_after_refund()로 DB에서 이미 비활성화됐으므로 환불은 진행하고 sync 실패를 기록만 한다.
                $vpnUsersSyncDegraded = true;
                refund_log('vpn_users_disable_degraded_both_failed', array(
                    'order_no' => $orderNo,
                    'mb_id' => $mbId,
                    'sync_user_id' => $syncUserId,
                    'remote_status' => isset($vpnUsersSyncRet['status']) ? (string)$vpnUsersSyncRet['status'] : 'unknown',
                    'local_status' => isset($vpnUsersLocalForceRet['status']) ? (string)$vpnUsersLocalForceRet['status'] : 'unknown',
                ));
            }
        }

        refund_log('vpn_users_disable_result', array(
            'order_no' => $orderNo,
            'mb_id' => $mbId,
            'sync_user_id' => $syncUserId,
            'sync_gateway' => $syncGateway,
            'sync_result' => $vpnUsersSyncRet,
            'sync_forced' => $vpnUsersSyncForced,
            'sync_local_force_result' => $vpnUsersLocalForceRet,
        ));

        $vpnUsersSyncDegraded = false;

        if (function_exists('vpnlink_vpnid_sync_upsert_by_order')) {
            $vpnidSyncRet = vpnlink_vpnid_sync_upsert_by_order($odId, $itId, 'shop_order_refund');
            if (empty($vpnidSyncRet['ok']) && function_exists('vpnlink_ikev2_log')) {
                vpnlink_ikev2_log('strongswan_profile_refund_order vpnid sync failed: od_id='.$odId.' it_id='.$itId.' error='.(isset($vpnidSyncRet['error']) ? $vpnidSyncRet['error'] : 'unknown'));
            }
        }

        if (function_exists('vpnlink_ikev2_queue_order')) {
            $ikev2RequeueRet = vpnlink_ikev2_queue_order($odId, 'shop_order_refund_requeue');
            if (empty($ikev2RequeueRet['ok']) && function_exists('vpnlink_ikev2_log')) {
                vpnlink_ikev2_log('strongswan_profile_refund_order requeue failed: od_id='.$odId.' it_id='.$itId.' error='.(isset($ikev2RequeueRet['error']) ? $ikev2RequeueRet['error'] : 'unknown'));
            }
        }

        $stmt = $mysqli->prepare("UPDATE vpn_refund_orders SET status='refunded', refunded_at=NOW(), approved_by='app' WHERE order_no=? AND mb_id=? LIMIT 1");
        if (!$stmt) {
            throw new Exception('환불 주문 상태 갱신 prepare 실패: ' . $mysqli->error);
        }
        $stmt->bind_param('ss', $orderNo, $mbId);
        if (!$stmt->execute()) {
            throw new Exception('환불 주문 상태 갱신 execute 실패: ' . $stmt->error);
        }
        $stmt->close();

        $mysqli->commit();

        if ($apiMode) {
            success_json('환불이 완료되었습니다.', array(
                'action' => 'approve',
                'order_no' => $orderNo,
                'status' => 'refunded',
                'refund_point' => (int)$refundAmountAfterPenalty,
                'vpn_no' => $vpnNo,
                'vpn_users_sync_success' => empty($vpnUsersSyncRet['success']) ? 0 : 1,
                'vpn_users_sync_status' => isset($vpnUsersSyncRet['status']) ? (string)$vpnUsersSyncRet['status'] : '',
                'vpn_users_sync_degraded' => $vpnUsersSyncDegraded ? 1 : 0,
                'vpn_users_sync_forced' => $vpnUsersSyncForced ? 1 : 0,
                'vpn_users_sync_local_status' => isset($vpnUsersLocalForceRet['status']) ? (string)$vpnUsersLocalForceRet['status'] : '',
                'vpn_users_sync_local_source' => isset($vpnUsersLocalForceRet['source']) ? (string)$vpnUsersLocalForceRet['source'] : '',
                'point_skipped' => !empty($pointApplyRet['skipped']) ? 1 : 0,
                'member_point_before' => isset($pointApplyRet['before_point']) ? (int)$pointApplyRet['before_point'] : null,
                'member_point_after' => isset($pointApplyRet['after_point']) ? (int)$pointApplyRet['after_point'] : null,
                'point_log_id' => isset($pointApplyRet['point_log_id']) ? (int)$pointApplyRet['point_log_id'] : 0,
            ));
        }

        $redirectUrl = $_SERVER['PHP_SELF'] . '?action=pay&order_no=' . urlencode($orderNo) . '&mb_id=' . urlencode($mbId) . '&status=refunded';
        redirect_with_html_fallback($redirectUrl);
    } catch (Exception $e) {
        $mysqli->rollback();
        $msg = '환불 처리 실패: ' . $e->getMessage();
        refund_log('approve_failed', array('order_no' => $orderNo, 'mb_id' => $mbId, 'error' => $msg));

        if ($apiMode) {
            fail_json($msg, array(
                'action' => 'approve',
                'order_no' => $orderNo,
                'notify_app' => 1,
                'error_code' => map_approve_error_code($msg),
            ));
        }

        $redirectUrl = $_SERVER['PHP_SELF'] . '?action=pay&order_no=' . urlencode($orderNo) . '&mb_id=' . urlencode($mbId) . '&status=failure&error=' . urlencode($msg);
        redirect_with_html_fallback($redirectUrl);
    }
}

if ($action === 'pay') {
    if (!ensure_refund_order_table($mysqli)) {
        echo '환불 주문 테이블 준비 실패';
        exit;
    }

    $orderNo = req('order_no', '');
    $stmt = $mysqli->prepare('SELECT * FROM vpn_refund_orders WHERE order_no=? AND mb_id=? LIMIT 1');
    if (!$stmt) {
        echo '주문 조회 실패';
        exit;
    }
    $stmt->bind_param('ss', $orderNo, $mbId);
    $stmt->execute();
    $order = $stmt->get_result()->fetch_assoc();
    $stmt->close();

    if (!$order) {
        echo '주문 정보 없음';
        exit;
    }

    $uiStatus = strtolower((string)req('status', isset($order['status']) ? $order['status'] : 'pending'));
    if ($uiStatus === '') {
        $uiStatus = 'pending';
    }
    $statusLabel = '상태 미정';
    $statusClass = 'status-unknown';
    if ($uiStatus === 'pending') {
        $statusLabel = '환불 승인 대기';
        $statusClass = 'status-pending';
    } else if ($uiStatus === 'refunded') {
        $statusLabel = '환불 완료';
        $statusClass = 'status-paid';
    } else if ($uiStatus === 'failure' || $uiStatus === 'failed') {
        $statusLabel = '환불 실패';
        $statusClass = 'status-failed';
    }
    $isRefunded = ($uiStatus === 'refunded');
    $errorNotice = req('error', '');
    ?>
<!doctype html>
<html lang="ko">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width,initial-scale=1">
<title>환불 승인</title>
<style>
body{font-family:sans-serif;background:#f3f4f6;margin:0;padding:24px}
.box{max-width:520px;background:#fff;border:1px solid #ddd;padding:18px}
.btn{display:inline-block;border:0;background:#1f6feb;color:#fff;padding:10px 16px;cursor:pointer}
.status-badge{display:inline-block;margin:6px 0 12px 0;padding:6px 10px;border-radius:999px;font-size:13px;font-weight:700}
.status-pending{background:#fff3cd;color:#856404;border:1px solid #ffeeba}
.status-paid{background:#d4edda;color:#155724;border:1px solid #c3e6cb}
.status-failed{background:#f8d7da;color:#721c24;border:1px solid #f5c6cb}
.status-unknown{background:#e2e3e5;color:#383d41;border:1px solid #d6d8db}
.status-note{margin-top:10px;color:#2f2f33;font-size:14px}
</style>
</head>
<body>
<div class="box">
    <h3>환불 승인</h3>
    <div class="status-badge <?php echo h($statusClass); ?>"><?php echo h($statusLabel); ?></div>
    <p>주문번호: <?php echo h($order['order_no']); ?></p>
    <p>상품: <?php echo h($order['it_name']); ?> / VPN ID: <?php echo h($order['vpn_user_id']); ?></p>
    <p>환불 예상 포인트: <?php echo h($order['refund_point']); ?> Point</p>

    <?php if (!$isRefunded) { ?>
    <form method="post" action="<?php echo h($_SERVER['PHP_SELF']); ?>" id="refund_approve_form">
        <input type="hidden" name="action" value="approve">
        <input type="hidden" name="mb_id" value="<?php echo h($mbId); ?>">
        <input type="hidden" name="order_no" value="<?php echo h($order['order_no']); ?>">
        <button class="btn" type="submit" id="refund_approve_btn" style="display:none">환불 승인</button>
    </form>
    <div id="refund_auto_msg" style="margin-top:8px;color:#888;font-size:13px;">처리 중...</div>
    <script>
    (function(){
        var msgEl  = document.getElementById('refund_auto_msg');
        var doneUrl = '<?php echo h($_SERVER['PHP_SELF']
            . '?action=pay&order_no=' . urlencode($order['order_no'])
            . '&mb_id=' . urlencode($mbId)
            . '&status=refunded'); ?>';
        var fd = new FormData();
        fd.append('action',   'approve');
        fd.append('mb_id',    '<?php echo h($mbId); ?>');
        fd.append('order_no', '<?php echo h($order['order_no']); ?>');
        fetch('<?php echo h($_SERVER['PHP_SELF']); ?>', {method:'POST', body:fd, headers:{'X-Requested-With':'XMLHttpRequest'}})
            .then(function(r){ return r.json(); })
            .then(function(j){
                if (j && (j.result === 'success' || j.status === 'success')) {
                    window.location.replace(doneUrl);
                } else {
                    var errMsg = (j && j.message) ? j.message : '처리 실패. 페이지를 새로고침해 주세요.';
                    if (msgEl) { msgEl.style.color = '#e53e3e'; msgEl.textContent = errMsg; }
                }
            })
            .catch(function(){
                if (msgEl) { msgEl.style.color = '#e53e3e'; msgEl.textContent = '네트워크 오류. 페이지를 새로고침해 주세요.'; }
            });
    })();
    </script>
    <?php } else { ?>
    <div class="status-note">잠시 후 자동으로 이전 화면으로 돌아갑니다.</div>
    <?php } ?>

    <?php if ($uiStatus === 'failure' || $uiStatus === 'failed') { ?>
    <div class="status-note"><?php echo h($errorNotice !== '' ? $errorNotice : '환불 처리 중 오류가 발생했습니다. 다시 시도해 주세요.'); ?></div>
    <?php } ?>
</div>
<?php if ($isRefunded) { ?>
<script>
(function(){
    setTimeout(function(){
        try { window.close(); } catch (e) {}
        try { window.history.back(); } catch (e2) {}
    }, 1200);
})();
</script>
<?php } ?>
</body>
</html>
<?php
    exit;
}

?>
<!doctype html>
<html lang="ko">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width,initial-scale=1">
<title>환불 요청 · <?php echo h(isset($target['it_name']) ? $target['it_name'] : 'VPN'); ?></title>
<style>
body{font-family:sans-serif;background:#f3f4f6;margin:0;padding:18px}
.wrap{max-width:760px;margin:0 auto;background:#fff;border:1px solid #ddd}
.head{background:#1f2a44;color:#fff;padding:14px 16px;font-size:24px;font-weight:700}
.inner{padding:16px}
.row{margin:10px 0;font-size:18px;color:#222}
.btn{height:44px;border:0;background:#1f6feb;color:#fff;padding:0 16px;cursor:pointer;font-size:18px}
.msg{margin-top:10px;color:#1f6feb;font-size:15px}
</style>
</head>
<body>
<div id="vpnlink_refund_breakdown" style="display:none" data-breakdown="" data-ready="0"></div>
<div class="wrap">
    <div class="head">환불 요청 · <?php echo h(isset($target['it_name']) ? $target['it_name'] : ''); ?></div>
    <div class="inner">
        <div class="row">현재 종료일: <?php echo h(isset($target['vpn_id_expire']) ? $target['vpn_id_expire'] : ''); ?></div>
        <div class="row" id="refundEstimate">환불 예상 포인트: 계산 중...</div>
        <button class="btn" id="refundBtn" type="button">환불 요청하기</button>
        <div class="msg" id="msg"></div>
    </div>
</div>

<script>
(function(){
    var msgEl = document.getElementById('msg');
    var estimateEl = document.getElementById('refundEstimate');
    var refundBtn = document.getElementById('refundBtn');

    function postAction(actionName){
        var f = new FormData();
        f.append('action', actionName);
        f.append('api', '1');
        f.append('mb_id', '<?php echo h($mbId); ?>');
        f.append('profile_id', '<?php echo h(isset($target['no']) ? $target['no'] : ''); ?>');
        f.append('profile_name', <?php echo json_encode(isset($target['it_name']) ? $target['it_name'] : '', JSON_UNESCAPED_UNICODE); ?>);
        f.append('profile_gateway', <?php echo json_encode(isset($target['server_name']) ? $target['server_name'] : '', JSON_UNESCAPED_UNICODE); ?>);
        f.append('profile_username', <?php echo json_encode(isset($target['vpn_user_id']) ? $target['vpn_user_id'] : '', JSON_UNESCAPED_UNICODE); ?>);
        f.append('it_name', <?php echo json_encode(isset($target['it_name']) ? $target['it_name'] : '', JSON_UNESCAPED_UNICODE); ?>);

        return fetch('<?php echo h($_SERVER['PHP_SELF']); ?>', {method:'POST', body:f}).then(function(r){ return r.json(); });
    }

    function loadQuote(){
        postAction('quote').then(function(j){
            if (j.result !== 'success') {
                msgEl.textContent = j.msg || '견적 계산 실패';
                return;
            }
            estimateEl.textContent = '환불 예상 포인트: ' + String(j.refund_point || 0) + ' Point';
            msgEl.textContent = '';
            // 계산 내역을 hidden div에 저장 (앱이 JS로 읽어감)
            try {
                var bdDiv = document.getElementById('vpnlink_refund_breakdown');
                if (bdDiv) {
                    var bd = {
                        breakdown: j.breakdown || {},
                        orders_breakdown: j.orders_breakdown || []
                    };
                    bdDiv.setAttribute('data-breakdown', JSON.stringify(bd));
                    bdDiv.setAttribute('data-ready', '1');
                }
            } catch(e){}
        }).catch(function(){
            msgEl.textContent = '통신 오류';
        });
    }

    refundBtn.addEventListener('click', function(){
        refundBtn.disabled = true;
        postAction('create_order').then(function(j){
            if (j.result !== 'success') {
                msgEl.textContent = j.msg || '환불 주문 실패';
                refundBtn.disabled = false;
                return;
            }
            window.location.href = j.pay_url;
        }).catch(function(){
            msgEl.textContent = '주문 통신 오류';
            refundBtn.disabled = false;
        });
    });

    loadQuote();
})();
</script>
</body>
</html>


