<?php
include_once(__DIR__ . '/../common.php');

if (isset($_REQUEST['sort']))  {
    $sort = trim($_REQUEST['sort']);
    $sort = preg_replace("/[\<\>\'\"\\\'\\\"\%\=\(\)\s]/", "", $sort);
    $sort = preg_replace("/(--|#|\/\*|\*\/)/", "", $sort);
} else {
    $sort = '';
}

if (isset($_REQUEST['sortodr']))  {
    $sortodr = preg_match("/^(asc|desc)$/i", $sortodr) ? $sortodr : '';
} else {
    $sortodr = '';
}

if (!defined('G5_USE_SHOP') || !G5_USE_SHOP)
    die('<p> 아닙니다. 판매자에게 문의하여 이용해 주십시오.</p>');

define('_SHOP_', true);

if (!function_exists('vpnlink_native_purchase_sql_escape')) {
    function vpnlink_native_purchase_sql_escape($value)
    {
        if (function_exists('sql_real_escape_string')) {
            return sql_real_escape_string((string)$value);
        }
        if (function_exists('sql_escape_string')) {
            return sql_escape_string((string)$value);
        }
        return addslashes((string)$value);
    }
}

if (!function_exists('vpnlink_native_purchase_table_columns')) {
    function vpnlink_native_purchase_table_columns($table)
    {
        static $cache = array();
        $table = trim((string)$table);
        if ($table === '') {
            return array();
        }
        if (isset($cache[$table])) {
            return $cache[$table];
        }

        $cols = array();
        $res = sql_query("SHOW COLUMNS FROM `" . str_replace('`', '', $table) . "`", false);
        if ($res) {
            while ($row = sql_fetch_array($res)) {
                if (!empty($row['Field'])) {
                    $cols[(string)$row['Field']] = true;
                }
            }
        }

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

if (!function_exists('vpnlink_native_purchase_table_exists')) {
    function vpnlink_native_purchase_table_exists($table)
    {
        $table = trim((string)$table);
        if ($table === '') {
            return false;
        }
        $safe = str_replace("'", "''", $table);
        $row = sql_fetch("SHOW TABLES LIKE '{$safe}'", false);
        return is_array($row) && count($row) > 0;
    }
}

if (!function_exists('vpnlink_native_purchase_set_last_error')) {
    function vpnlink_native_purchase_set_last_error($message)
    {
        $GLOBALS['vpnlink_native_purchase_last_error'] = trim((string)$message);
    }
}

if (!function_exists('vpnlink_native_purchase_log')) {
    function vpnlink_native_purchase_log($message, $context)
    {
        $basePath = defined('G5_PATH') ? G5_PATH : (__DIR__ . '/..');
        $logPath = $basePath . '/mylib/data/native_vpn_purchase.log';
        $line = '[' . date('Y-m-d H:i:s') . '] ' . trim((string)$message);
        if (is_array($context) && count($context) > 0) {
            $line .= ' | ' . json_encode($context, JSON_UNESCAPED_UNICODE);
        }
        $line .= "\n";
        @file_put_contents($logPath, $line, FILE_APPEND | LOCK_EX);
    }
}

if (!function_exists('vpnlink_native_purchase_get_last_error')) {
    function vpnlink_native_purchase_get_last_error()
    {
        if (!empty($GLOBALS['vpnlink_native_purchase_last_error'])) {
            return (string)$GLOBALS['vpnlink_native_purchase_last_error'];
        }
        if (function_exists('sql_error')) {
            return (string)sql_error();
        }
        return '';
    }
}

if (!function_exists('vpnlink_native_purchase_add_set')) {
    function vpnlink_native_purchase_add_set(&$set, $cols, $name, $value, $isRaw)
    {
        if (!isset($cols[$name])) {
            return;
        }
        if ($isRaw) {
            $set[] = "`{$name}`={$value}";
            return;
        }
        $set[] = "`{$name}`='" . vpnlink_native_purchase_sql_escape($value) . "'";
    }
}

if (!function_exists('vpnlink_native_purchase_parse_payload')) {
    function vpnlink_native_purchase_parse_payload($order)
    {
        $raw = isset($order['payload_json']) ? trim((string)$order['payload_json']) : '';
        if ($raw === '') {
            return array();
        }
        $decoded = json_decode($raw, true);
        return is_array($decoded) ? $decoded : array();
    }
}

if (!function_exists('vpnlink_native_purchase_create_sales_tables')) {
    function vpnlink_native_purchase_create_sales_tables()
    {
        $sql1 = "CREATE TABLE IF NOT EXISTS vpn_native_buy_sales (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            order_id VARCHAR(40) NOT NULL,
            shop_od_id VARCHAR(40) NOT NULL,
            mb_id VARCHAR(100) NOT NULL,
            it_id VARCHAR(64) NOT NULL,
            it_name VARCHAR(255) NOT NULL,
            order_hours INT NOT NULL DEFAULT 1,
            line_price INT NOT NULL DEFAULT 0,
            created_at DATETIME NOT NULL,
            PRIMARY KEY (id),
            KEY idx_order_id (order_id),
            KEY idx_shop_od_id (shop_od_id),
            KEY idx_mb_id (mb_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

        $sql2 = "CREATE TABLE IF NOT EXISTS vpn_native_buy_order_map (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            order_id VARCHAR(40) NOT NULL,
            shop_od_id VARCHAR(40) NOT NULL,
            mb_id VARCHAR(100) NOT NULL,
            amount INT NOT NULL DEFAULT 0,
            created_at DATETIME NOT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY uq_order_id (order_id),
            KEY idx_shop_od_id (shop_od_id),
            KEY idx_mb_id (mb_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

        return sql_query($sql1, false) && sql_query($sql2, false);
    }
}

if (!function_exists('vpnlink_native_purchase_make_shop_order_id')) {
    function vpnlink_native_purchase_make_shop_order_id($orderId)
    {
        // g5_shop_cart.od_id가 숫자형인 운영 DB를 고려해 숫자만 사용한다.
        $odId = date('YmdHis') . str_pad((string)mt_rand(1, 99), 2, '0', STR_PAD_LEFT);
        $exists = sql_fetch("SELECT od_id FROM {$GLOBALS['g5']['g5_shop_order_table']} WHERE od_id='" . vpnlink_native_purchase_sql_escape($odId) . "' LIMIT 1", false);
        if (!empty($exists['od_id'])) {
            $odId = date('YmdHis') . str_pad((string)mt_rand(100, 999), 3, '0', STR_PAD_LEFT);
        }
        return $odId;
    }
}

if (!function_exists('vpnlink_native_purchase_insert_order')) {
    function vpnlink_native_purchase_insert_order($odId, $mbId, $member, $amount, $cartCount, $clientOrderId)
    {
        global $g5, $default;

        $table = $g5['g5_shop_order_table'];
        $cols = vpnlink_native_purchase_table_columns($table);
        if (count($cols) < 1) {
            return false;
        }

        $name = isset($member['mb_name']) ? trim((string)$member['mb_name']) : '';
        if ($name === '') {
            $name = $mbId;
        }
        $email = isset($member['mb_email']) ? trim((string)$member['mb_email']) : '';
        $hp = isset($member['mb_hp']) ? trim((string)$member['mb_hp']) : '';
        $tel = isset($member['mb_tel']) ? trim((string)$member['mb_tel']) : '';
        $mbPw = isset($member['mb_password']) ? (string)$member['mb_password'] : '';

        $set = array();
        vpnlink_native_purchase_add_set($set, $cols, 'od_id', $odId, false);
        vpnlink_native_purchase_add_set($set, $cols, 'mb_id', $mbId, false);
        vpnlink_native_purchase_add_set($set, $cols, 'od_pwd', $mbPw, false);
        vpnlink_native_purchase_add_set($set, $cols, 'od_name', $name, false);
        vpnlink_native_purchase_add_set($set, $cols, 'od_email', $email, false);
        vpnlink_native_purchase_add_set($set, $cols, 'od_tel', $tel, false);
        vpnlink_native_purchase_add_set($set, $cols, 'od_hp', $hp, false);
        vpnlink_native_purchase_add_set($set, $cols, 'od_b_name', $name, false);
        vpnlink_native_purchase_add_set($set, $cols, 'od_b_tel', $tel, false);
        vpnlink_native_purchase_add_set($set, $cols, 'od_b_hp', $hp, false);
        vpnlink_native_purchase_add_set($set, $cols, 'od_deposit_name', $name, false);
        vpnlink_native_purchase_add_set($set, $cols, 'od_memo', 'native_vpn_purchase order_id=' . $clientOrderId, false);
        vpnlink_native_purchase_add_set($set, $cols, 'od_shop_memo', 'native_vpn_purchase', false);
        vpnlink_native_purchase_add_set($set, $cols, 'od_cart_count', (int)$cartCount, true);
        vpnlink_native_purchase_add_set($set, $cols, 'od_cart_price', (int)$amount, true);
        vpnlink_native_purchase_add_set($set, $cols, 'od_send_cost', 0, true);
        vpnlink_native_purchase_add_set($set, $cols, 'od_send_cost2', 0, true);
        vpnlink_native_purchase_add_set($set, $cols, 'od_send_coupon', 0, true);
        vpnlink_native_purchase_add_set($set, $cols, 'od_coupon', 0, true);
        vpnlink_native_purchase_add_set($set, $cols, 'od_cart_coupon', 0, true);
        vpnlink_native_purchase_add_set($set, $cols, 'od_receipt_price', 0, true);
        vpnlink_native_purchase_add_set($set, $cols, 'od_receipt_point', (int)$amount, true);
        vpnlink_native_purchase_add_set($set, $cols, 'od_bank_account', '포인트', false);
        vpnlink_native_purchase_add_set($set, $cols, 'od_pg', 'point', false);
        vpnlink_native_purchase_add_set($set, $cols, 'od_tno', '', false);
        vpnlink_native_purchase_add_set($set, $cols, 'od_app_no', '', false);
        vpnlink_native_purchase_add_set($set, $cols, 'od_escrow', 0, true);
        vpnlink_native_purchase_add_set($set, $cols, 'od_tax_flag', isset($default['de_tax_flag_use']) ? (string)$default['de_tax_flag_use'] : '0', false);
        vpnlink_native_purchase_add_set($set, $cols, 'od_tax_mny', 0, true);
        vpnlink_native_purchase_add_set($set, $cols, 'od_vat_mny', 0, true);
        vpnlink_native_purchase_add_set($set, $cols, 'od_free_mny', (int)$amount, true);
        vpnlink_native_purchase_add_set($set, $cols, 'od_status', '입금', false);
        vpnlink_native_purchase_add_set($set, $cols, 'od_settle_case', '포인트', false);
        vpnlink_native_purchase_add_set($set, $cols, 'od_misu', 0, true);
        vpnlink_native_purchase_add_set($set, $cols, 'od_ip', isset($_SERVER['REMOTE_ADDR']) ? (string)$_SERVER['REMOTE_ADDR'] : '', false);
        vpnlink_native_purchase_add_set($set, $cols, 'od_time', 'NOW()', true);
        vpnlink_native_purchase_add_set($set, $cols, 'od_receipt_time', 'NOW()', true);
        vpnlink_native_purchase_add_set($set, $cols, 'od_test', isset($default['de_card_test']) ? (string)$default['de_card_test'] : '0', false);

        if (count($set) < 1) {
            return false;
        }

        $sql = "INSERT INTO {$table} SET " . implode(', ', $set);
        $ok = sql_query($sql, false) ? true : false;
        if (!$ok) {
            vpnlink_native_purchase_set_last_error('order_insert_failed: ' . vpnlink_native_purchase_get_last_error());
        }
        return $ok;
    }
}

if (!function_exists('vpnlink_native_purchase_insert_cart_line')) {
    function vpnlink_native_purchase_insert_cart_line($odId, $mbId, $item, $hours, $linePrice, $baseStartAt = '')
    {
        global $g5;

        $table = $g5['g5_shop_cart_table'];
        $cols = vpnlink_native_purchase_table_columns($table);
        if (count($cols) < 1) {
            return false;
        }

        $hours = max(1, (int)$hours);
        $linePrice = max(0, (int)$linePrice);
        $ctQtyDay = 0;
        $ctQtyHour = $hours;

        $unitHourPrice = 0;
        if ($hours > 0 && $linePrice > 0) {
            $unitHourPrice = (int)max(1, floor($linePrice / $hours));
        }
        if ($unitHourPrice < 1 && isset($item['it_is_hour_sale_price']) && (int)$item['it_is_hour_sale_price'] > 0) {
            $unitHourPrice = (int)$item['it_is_hour_sale_price'];
        }
        if ($unitHourPrice < 1 && isset($item['it_price']) && (int)$item['it_price'] > 0) {
            $unitHourPrice = (int)$item['it_price'];
        }

        $basePrice = max(0, $unitHourPrice * $hours);
        $installPrice = max(0, $linePrice - $basePrice);

        $baseTs = false;
        $baseStartAt = trim((string)$baseStartAt);
        if ($baseStartAt !== '') {
            $baseTs = strtotime($baseStartAt);
        }
        if ($baseTs === false || $baseTs <= 0) {
            $baseTs = time();
        }

        $now = date('Y-m-d H:i:s', $baseTs);
        $endTs = $baseTs + ($hours * 3600);
        $endAt = date('Y-m-d H:i:s', $endTs);
        $remoteIp = isset($_SERVER['REMOTE_ADDR']) ? (string)$_SERVER['REMOTE_ADDR'] : '';
        $history = "\n완료|automation|{$now}|";
        $ptSale = max(0, (int)$linePrice);
        $ptCommissionRate = 20;
        $ptIncentiveRate = 10;
        $ptCommission = (int)floor($ptSale * ($ptCommissionRate / 100.0));
        $ptIncentive = (int)floor($ptSale * ($ptIncentiveRate / 100.0));
        $ptNet = max(0, $ptSale - $ptCommission - $ptIncentive);

        $set = array();
        vpnlink_native_purchase_add_set($set, $cols, 'od_id', $odId, false);
        vpnlink_native_purchase_add_set($set, $cols, 'mb_id', $mbId, false);
        vpnlink_native_purchase_add_set($set, $cols, 'it_id', isset($item['it_id']) ? (string)$item['it_id'] : '', false);
        vpnlink_native_purchase_add_set($set, $cols, 'it_name', isset($item['it_name']) ? (string)$item['it_name'] : '', false);
        vpnlink_native_purchase_add_set($set, $cols, 'it_sc_type', isset($item['it_sc_type']) ? (int)$item['it_sc_type'] : 1, true);
        vpnlink_native_purchase_add_set($set, $cols, 'it_sc_method', isset($item['it_sc_method']) ? (int)$item['it_sc_method'] : 0, true);
        vpnlink_native_purchase_add_set($set, $cols, 'it_sc_price', isset($item['it_sc_price']) ? (int)$item['it_sc_price'] : 0, true);
        vpnlink_native_purchase_add_set($set, $cols, 'it_sc_minimum', isset($item['it_sc_minimum']) ? (int)$item['it_sc_minimum'] : 0, true);
        vpnlink_native_purchase_add_set($set, $cols, 'it_sc_qty', isset($item['it_sc_qty']) ? (int)$item['it_sc_qty'] : 0, true);
        vpnlink_native_purchase_add_set($set, $cols, 'ct_status', '완료', false);
        vpnlink_native_purchase_add_set($set, $cols, 'ct_history', $history, false);
        vpnlink_native_purchase_add_set($set, $cols, 'ct_select', 1, true);
        vpnlink_native_purchase_add_set($set, $cols, 'ct_qty', max(0, $ctQtyDay), true);
        vpnlink_native_purchase_add_set($set, $cols, 'ct_qty_for_extension_of_period', 0, true);
        vpnlink_native_purchase_add_set($set, $cols, 'ct_qty_hour', max(0, $ctQtyHour), true);
        vpnlink_native_purchase_add_set($set, $cols, 'ct_qty_hour_for_extension_of_period', 0, true);
        vpnlink_native_purchase_add_set($set, $cols, 'ct_price', max(0, $unitHourPrice), true);
        vpnlink_native_purchase_add_set($set, $cols, 'ct_price_for_extension_of_period', 0, true);
        vpnlink_native_purchase_add_set($set, $cols, 'ct_point', 0, true);
        vpnlink_native_purchase_add_set($set, $cols, 'ct_point_use', 1, true);
        vpnlink_native_purchase_add_set($set, $cols, 'ct_stock_use', 1, true);
        vpnlink_native_purchase_add_set($set, $cols, 'ct_option', isset($item['it_name']) ? (string)$item['it_name'] : 'VPN', false);
        vpnlink_native_purchase_add_set($set, $cols, 'io_id', '', false);
        vpnlink_native_purchase_add_set($set, $cols, 'io_type', 0, true);
        vpnlink_native_purchase_add_set($set, $cols, 'io_price', 0, true);
        vpnlink_native_purchase_add_set($set, $cols, 'ct_time', 'NOW()', true);
        vpnlink_native_purchase_add_set($set, $cols, 'ct_ip', $remoteIp, false);
        vpnlink_native_purchase_add_set($set, $cols, 'ct_send_cost', 2, true);
        vpnlink_native_purchase_add_set($set, $cols, 'ct_direct', 1, true);
        vpnlink_native_purchase_add_set($set, $cols, 'ct_select_time', 'NOW()', true);
        vpnlink_native_purchase_add_set($set, $cols, 'ct_install_price', $installPrice, true);
        vpnlink_native_purchase_add_set($set, $cols, 'pt_sale', $ptSale, true);
        vpnlink_native_purchase_add_set($set, $cols, 'pt_commission', $ptCommission, true);
        vpnlink_native_purchase_add_set($set, $cols, 'pt_point', 0, true);
        vpnlink_native_purchase_add_set($set, $cols, 'pt_incentive', $ptIncentive, true);
        vpnlink_native_purchase_add_set($set, $cols, 'pt_net', $ptNet, true);
        vpnlink_native_purchase_add_set($set, $cols, 'pt_commission_rate', $ptCommissionRate, true);
        vpnlink_native_purchase_add_set($set, $cols, 'pt_incentive_rate', $ptIncentiveRate, true);
        vpnlink_native_purchase_add_set($set, $cols, 'pt_it', isset($item['pt_it']) ? (int)$item['pt_it'] : 2, true);
        $partnerId = isset($item['pt_id']) ? trim((string)$item['pt_id']) : '';
        if ($partnerId === '' && isset($item['it_spec_buyer_mb_id'])) {
            $partnerId = trim((string)$item['it_spec_buyer_mb_id']);
        }
        if ($partnerId === '') {
            vpnlink_native_purchase_log('missing_pt_id', array(
                'od_id' => $odId,
                'mb_id' => $mbId,
                'it_id' => isset($item['it_id']) ? (string)$item['it_id'] : '',
            ));
        }
        vpnlink_native_purchase_add_set($set, $cols, 'pt_id', $partnerId, false);
        vpnlink_native_purchase_add_set($set, $cols, 'pt_send', '', false);
        vpnlink_native_purchase_add_set($set, $cols, 'pt_send_num', '', false);
        vpnlink_native_purchase_add_set($set, $cols, 'pt_datetime', $now, false);
        vpnlink_native_purchase_add_set($set, $cols, 'extension_of_period_date', $now, false);
        vpnlink_native_purchase_add_set($set, $cols, 'extension_of_period_to_datetime', $endAt, false);
        vpnlink_native_purchase_add_set($set, $cols, 'extension_of_period_days', 0, true);
        vpnlink_native_purchase_add_set($set, $cols, 'extension_of_period_hours', $hours, true);
        vpnlink_native_purchase_add_set($set, $cols, 'extension_of_period_price', max(0, (int)$linePrice), true);

        if (count($set) < 1) {
            return false;
        }

        $sql = "INSERT INTO {$table} SET " . implode(', ', $set);
        $ok = sql_query($sql, false) ? true : false;
        if (!$ok) {
            vpnlink_native_purchase_set_last_error('cart_insert_failed: ' . vpnlink_native_purchase_get_last_error());
            return false;
        }

        $row = sql_fetch("SELECT ct_id FROM {$table}
                          WHERE od_id='" . vpnlink_native_purchase_sql_escape($odId) . "'
                            AND mb_id='" . vpnlink_native_purchase_sql_escape($mbId) . "'
                            AND it_id='" . vpnlink_native_purchase_sql_escape(isset($item['it_id']) ? (string)$item['it_id'] : '') . "'
                          ORDER BY ct_id DESC
                          LIMIT 1", false);
        if (is_array($row) && isset($row['ct_id'])) {
            return (int)$row['ct_id'];
        }

        return true;
    }
}

if (!function_exists('vpnlink_native_purchase_refresh_order_amounts')) {
    function vpnlink_native_purchase_refresh_order_amounts($odId)
    {
        global $g5;

        $safeOd = vpnlink_native_purchase_sql_escape($odId);
        $sum = sql_fetch("SELECT
                            COUNT(*) AS cnt,
                            COALESCE(SUM(CASE
                                WHEN extension_of_period_price IS NOT NULL AND extension_of_period_price > 0 THEN extension_of_period_price
                                ELSE ((IFNULL(ct_price,0) * IFNULL(ct_qty_hour,0)) + IFNULL(ct_install_price,0))
                            END), 0) AS total_price
                          FROM {$g5['g5_shop_cart_table']}
                          WHERE od_id='{$safeOd}'", false);

        $cartCount = isset($sum['cnt']) ? (int)$sum['cnt'] : 0;
        $totalPrice = isset($sum['total_price']) ? (int)$sum['total_price'] : 0;

        sql_query("UPDATE {$g5['g5_shop_order_table']}
                   SET od_cart_count='{$cartCount}',
                       od_cart_price='{$totalPrice}',
                       od_receipt_point='{$totalPrice}',
                       od_misu='0',
                       od_status='입금',
                       od_settle_case='포인트',
                       od_receipt_time=NOW()
                   WHERE od_id='{$safeOd}'
                   LIMIT 1", false);
    }
}

if (!function_exists('vpnlink_native_purchase_lookup_profile_expiry_for_item')) {
    function vpnlink_native_purchase_lookup_profile_expiry_for_item($mbId, $itId, $forUpdate)
    {
        global $g5;

        $mbId = trim((string)$mbId);
        $itId = trim((string)$itId);
        if ($mbId === '' || $itId === '' || !isset($g5['g5_shop_item_table'])) {
            return '';
        }

        $safeIt = vpnlink_native_purchase_sql_escape($itId);
        $item = sql_fetch("SELECT it_name FROM {$g5['g5_shop_item_table']} WHERE it_id='{$safeIt}' LIMIT 1", false);
        $itName = isset($item['it_name']) ? trim((string)$item['it_name']) : '';
        if ($itName === '') {
            return '';
        }

        $safeMb = vpnlink_native_purchase_sql_escape($mbId);
        $safeName = vpnlink_native_purchase_sql_escape($itName);

        $bestTs = 0;

        // 1) 장바구니 히스토리 기준 만료 (주문 확정 시 즉시 반영되는 값)
        if (isset($g5['g5_shop_cart_table'])) {
            $safeCart = $g5['g5_shop_cart_table'];
            $sqlCart = "SELECT extension_of_period_to_datetime AS ex
                          FROM {$safeCart}
                         WHERE mb_id='{$safeMb}'
                           AND it_id='{$safeIt}'
                           AND ct_status IN ('입금','완료')
                           AND extension_of_period_to_datetime IS NOT NULL
                           AND extension_of_period_to_datetime <> ''
                         ORDER BY extension_of_period_to_datetime DESC, ct_id DESC
                         LIMIT 1";
            if ($forUpdate) {
                $sqlCart .= ' FOR UPDATE';
            }
            $rowCart = sql_fetch($sqlCart, false);
            $cartEx = isset($rowCart['ex']) ? trim((string)$rowCart['ex']) : '';
            $cartTs = ($cartEx !== '') ? strtotime($cartEx) : false;
            if ($cartTs !== false && $cartTs > $bestTs) {
                $bestTs = (int)$cartTs;
            }
        }

        // 2) vpnid_list 기준 만료 (프로비저닝 결과)
        $sqlVpn = "SELECT MAX(vpn_id_expire) AS ex
                     FROM vpnid_list
                    WHERE (mb_id='{$safeMb}' OR remark='{$safeMb}' OR member_id='{$safeMb}')
                      AND it_name='{$safeName}'";
        if ($forUpdate) {
            $sqlVpn .= ' FOR UPDATE';
        }
        $rowVpn = sql_fetch($sqlVpn, false);
        $vpnEx = isset($rowVpn['ex']) ? trim((string)$rowVpn['ex']) : '';
        $vpnTs = ($vpnEx !== '') ? strtotime($vpnEx) : false;
        if ($vpnTs !== false && $vpnTs > $bestTs) {
            $bestTs = (int)$vpnTs;
        }

        return ($bestTs > 0) ? date('Y-m-d H:i:s', $bestTs) : '';
    }
}

if (!function_exists('vpnlink_native_purchase_sync_postpaid_tables')) {
    function vpnlink_native_purchase_sync_postpaid_tables($odId, $mbId, $member, $orderId = '', $isRenewal = false)
    {
        global $g5;

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

        if ($extTable === '') {
            return array('ok' => false, 'error' => 'extension table is not configured');
        }

        $cartCols = vpnlink_native_purchase_table_columns($cartTable);
        $orderCols = vpnlink_native_purchase_table_columns($orderTable);
        $itemCols = vpnlink_native_purchase_table_columns($itemTable);
        $extCols = vpnlink_native_purchase_table_columns($extTable);

        if (count($cartCols) < 1 || count($orderCols) < 1 || count($itemCols) < 1 || count($extCols) < 1) {
            return array('ok' => false, 'error' => 'required table metadata missing');
        }

        $safeOd = vpnlink_native_purchase_sql_escape($odId);
        $safeMb = vpnlink_native_purchase_sql_escape($mbId);

        $resCart = sql_query("SELECT *
                                FROM {$cartTable}
                               WHERE od_id='{$safeOd}'
                                 AND mb_id='{$safeMb}'
                                 AND ct_select='1'
                                 AND ct_status IN ('입금','완료')
                               ORDER BY ct_id ASC", false);
        if (!$resCart) {
            return array('ok' => false, 'error' => 'cart select failed: ' . vpnlink_native_purchase_get_last_error());
        }

        $buyerName = '';
        if (is_array($member) && !empty($member['mb_name'])) {
            $buyerName = trim((string)$member['mb_name']);
        }
        if ($buyerName === '') {
            $rowMb = sql_fetch("SELECT mb_name FROM {$g5['member_table']} WHERE mb_id='{$safeMb}' LIMIT 1", false);
            $buyerName = isset($rowMb['mb_name']) ? trim((string)$rowMb['mb_name']) : '';
        }

        $orderRow = sql_fetch("SELECT * FROM {$orderTable} WHERE od_id='{$safeOd}' AND mb_id='{$safeMb}' LIMIT 1", false);
        $odModHistory = isset($orderRow['od_mod_history']) ? trim((string)$orderRow['od_mod_history']) : '';

        $syncCount = 0;
        $warnings = array();
        while ($row = sql_fetch_array($resCart)) {
            $itId = isset($row['it_id']) ? trim((string)$row['it_id']) : '';
            if ($itId === '') {
                continue;
            }

            $ctQty = isset($row['ct_qty']) ? (int)$row['ct_qty'] : 0;
            $ctQtyHour = isset($row['ct_qty_hour']) ? (int)$row['ct_qty_hour'] : 0;
            $hoursTotal = function_exists('apms_contract_hours_from_day_hour')
                ? (int)apms_contract_hours_from_day_hour($ctQty, $ctQtyHour)
                : (int)(($ctQty * 24) + $ctQtyHour);
            if ($hoursTotal < 0) {
                $hoursTotal = 0;
            }

            $baseOptPrice = (float)(isset($row['ct_price']) ? $row['ct_price'] : 0) + (float)(isset($row['io_price']) ? $row['io_price'] : 0);
            $baseInstallPrice = (float)(isset($row['ct_install_price']) ? $row['ct_install_price'] : 0);
            $baseCt1Price = 0;
            if (isset($cartCols['ct_1']) && isset($row['ct_1'])) {
                $baseCt1Price = (float)$row['ct_1'];
            }
            $extensionPrice = (int)round(($baseOptPrice * $hoursTotal) + $baseInstallPrice + $baseCt1Price, 0);

            // [FIX 2026-05-08]
            // cart insert 단계에서 계산한 누적 만료 시각(extension_of_period_*)을 우선 사용한다.
            // 여기서 now 기반으로 다시 계산하면 누적 규칙(max(current, now)+ext)을 덮어쓰게 된다.
            $rowFrom = isset($row['extension_of_period_date']) ? trim((string)$row['extension_of_period_date']) : '';
            $rowTo = isset($row['extension_of_period_to_datetime']) ? trim((string)$row['extension_of_period_to_datetime']) : '';

            $fromDatetime = $rowFrom;
            if ($fromDatetime === '' || $fromDatetime === '0000-00-00 00:00:00' || strtotime($fromDatetime) === false) {
                $fromDatetime = defined('G5_TIME_YMDHIS') ? G5_TIME_YMDHIS : date('Y-m-d H:i:s');
            }

            $toDatetime = $rowTo;
            if ($toDatetime === '' || $toDatetime === '0000-00-00 00:00:00' || strtotime($toDatetime) === false) {
                $toDatetime = date('Y-m-d H:i:s', strtotime($fromDatetime . ' +' . $ctQty . ' days +' . $ctQtyHour . ' hours'));
            }

            // 1) item 상태 반영 (웹 구매완료 로직과 동일 목적)
            $itemSet = array();
            vpnlink_native_purchase_add_set($itemSet, $itemCols, 'it_soldout', 1, true);
            vpnlink_native_purchase_add_set($itemSet, $itemCols, 'it_spec_from_datetime', $fromDatetime, false);
            if (isset($itemCols['it_spec_from_datetime_org'])) {
                $itemSet[] = "`it_spec_from_datetime_org` = CASE
                    WHEN `it_spec_from_datetime_org` IS NULL OR TRIM(`it_spec_from_datetime_org`) = '' OR `it_spec_from_datetime_org` = '0000-00-00 00:00:00'
                        THEN '" . vpnlink_native_purchase_sql_escape($fromDatetime) . "'
                    ELSE `it_spec_from_datetime_org`
                END";
            }
            vpnlink_native_purchase_add_set($itemSet, $itemCols, 'it_spec_to_datetime', $toDatetime, false);
            if (isset($itemCols['it_spec_to_datetime_org'])) {
                $itemSet[] = "`it_spec_to_datetime_org` = CASE
                    WHEN `it_spec_to_datetime_org` IS NULL OR TRIM(`it_spec_to_datetime_org`) = '' OR `it_spec_to_datetime_org` = '0000-00-00 00:00:00'
                        THEN '" . vpnlink_native_purchase_sql_escape($toDatetime) . "'
                    ELSE `it_spec_to_datetime_org`
                END";
            }
            vpnlink_native_purchase_add_set($itemSet, $itemCols, 'it_spec_buyer_mb_id', $mbId, false);
            vpnlink_native_purchase_add_set($itemSet, $itemCols, 'it_spec_buyer_mb_name', $buyerName, false);

            if (count($itemSet) > 0) {
                $sqlItem = "UPDATE {$itemTable} SET " . implode(', ', $itemSet) . "
                             WHERE it_id='" . vpnlink_native_purchase_sql_escape($itId) . "'";
                if (!sql_query($sqlItem, false)) {
                    return array('ok' => false, 'error' => 'item update failed: ' . vpnlink_native_purchase_get_last_error());
                }
            }

            // 2) cart 계약연장 메타 갱신
            $cartSet = array();
            vpnlink_native_purchase_add_set($cartSet, $cartCols, 'extension_of_period_date', $fromDatetime, false);
            vpnlink_native_purchase_add_set($cartSet, $cartCols, 'extension_of_period_to_datetime', $toDatetime, false);
            vpnlink_native_purchase_add_set($cartSet, $cartCols, 'extension_of_period_days', $ctQty, true);
            vpnlink_native_purchase_add_set($cartSet, $cartCols, 'extension_of_period_hours', $ctQtyHour, true);
            vpnlink_native_purchase_add_set($cartSet, $cartCols, 'extension_of_period_price', $extensionPrice, true);
            if (count($cartSet) > 0) {
                $sqlCartMeta = "UPDATE {$cartTable}
                                   SET " . implode(', ', $cartSet) . "
                                 WHERE od_id='{$safeOd}'
                                   AND it_id='" . vpnlink_native_purchase_sql_escape($itId) . "'
                                   AND mb_id='{$safeMb}'";
                if (!sql_query($sqlCartMeta, false)) {
                    return array('ok' => false, 'error' => 'cart meta update failed: ' . vpnlink_native_purchase_get_last_error());
                }
            }

            // 3) extension 테이블에 신규 구매 라인 동기화(없으면 insert, 있으면 update)
            $copyCols = array();
            foreach ($cartCols as $c => $_unused) {
                if (isset($extCols[$c])) {
                    $copyCols[] = $c;
                }
            }
            if (count($copyCols) < 1) {
                return array('ok' => false, 'error' => 'extension sync columns empty');
            }

            $safeIt = vpnlink_native_purchase_sql_escape($itId);
            $exists = sql_fetch("SELECT COUNT(*) AS cnt
                                  FROM {$extTable}
                                 WHERE od_id='{$safeOd}'
                                   AND it_id='{$safeIt}'
                                   AND mb_id='{$safeMb}'", false);
            $existsCnt = isset($exists['cnt']) ? (int)$exists['cnt'] : 0;

            if ($existsCnt > 0) {
                $setParts = array();
                foreach ($copyCols as $col) {
                    $setParts[] = "`{$col}`='" . vpnlink_native_purchase_sql_escape(isset($row[$col]) ? $row[$col] : '') . "'";
                }
                $sqlExt = "UPDATE {$extTable}
                              SET " . implode(', ', $setParts) . "
                            WHERE od_id='{$safeOd}'
                              AND it_id='{$safeIt}'
                              AND mb_id='{$safeMb}'";
                if (!sql_query($sqlExt, false)) {
                    $warnings[] = 'extension_update_failed:' . (string)$safeIt;
                    vpnlink_native_purchase_log('extension_update_warning', array(
                        'order_id' => (string)$orderId,
                        'od_id' => $odId,
                        'mb_id' => $mbId,
                        'it_id' => $itId,
                        'error' => vpnlink_native_purchase_get_last_error(),
                    ));
                }
            } else {
                // [FIX 2026-05-08] 다건 주문 시 extension 테이블 PK(pt_datetime) 충돌 회피.
                // 동일 초에 여러 row가 INSERT 되면 duplicate key 가 날 수 있어 pt_datetime 을
                // 1초씩 오프셋하며 재시도한다.
                $insertCols = array();
                foreach ($copyCols as $col) {
                    $insertCols[] = "`{$col}`";
                }
                $hasPtDatetime = in_array('pt_datetime', $copyCols, true);
                $basePtDatetime = '';
                if ($hasPtDatetime) {
                    $basePtDatetime = isset($row['pt_datetime']) ? trim((string)$row['pt_datetime']) : '';
                    if ($basePtDatetime === '' || $basePtDatetime === '0000-00-00 00:00:00') {
                        $basePtDatetime = date('Y-m-d H:i:s');
                    }
                }

                $insertOk = false;
                $lastErr = '';
                $maxRetry = $hasPtDatetime ? 5 : 1;
                for ($retry = 0; $retry < $maxRetry; $retry++) {
                    $insertVals = array();
                    foreach ($copyCols as $col) {
                        $val = isset($row[$col]) ? $row[$col] : '';
                        if ($hasPtDatetime && $col === 'pt_datetime') {
                            $baseTs = strtotime($basePtDatetime);
                            if ($baseTs === false || $baseTs <= 0) {
                                $baseTs = time();
                            }
                            // syncCount/ct_id 를 섞어 같은 초 충돌 확률을 낮추고, retry 마다 +1s
                            $ctIdOffset = isset($row['ct_id']) ? ((int)$row['ct_id'] % 3) : 0;
                            $val = date('Y-m-d H:i:s', $baseTs + $ctIdOffset + $retry);
                        }
                        $insertVals[] = "'" . vpnlink_native_purchase_sql_escape($val) . "'";
                    }

                    $sqlExt = "INSERT INTO {$extTable} (" . implode(', ', $insertCols) . ")
                               VALUES (" . implode(', ', $insertVals) . ")";
                    if (sql_query($sqlExt, false)) {
                        $insertOk = true;
                        break;
                    }
                    $lastErr = vpnlink_native_purchase_get_last_error();
                }

                if (!$insertOk) {
                    $warnings[] = 'extension_insert_failed:' . (string)$safeIt;
                    vpnlink_native_purchase_log('extension_insert_warning', array(
                        'order_id' => (string)$orderId,
                        'od_id' => $odId,
                        'mb_id' => $mbId,
                        'it_id' => $itId,
                        'error' => (string)$lastErr,
                    ));
                }
            }

            // 4) 주문 변경 히스토리 누적
            $buyerNameLine = '';
            if (is_array($member) && !empty($member['mb_name'])) {
                $buyerNameLine = (string)$member['mb_name'];
            }
            $historyLabel = $isRenewal ? '계약연장' : '신규계약';
            $odModHistory .= "\n" . (defined('G5_TIME_YMDHIS') ? G5_TIME_YMDHIS : date('Y-m-d H:i:s'))
                . " " . $historyLabel . " (" . (int)$ctQty . "일" . (int)$ctQtyHour . "시간) 상품명 RPC-" . $itId
                . " 결제 " . number_format($extensionPrice) . " Point"
                . " (이용자: " . $mbId . " / " . $buyerNameLine . ") ";

            $syncCount++;
        }

        if ($syncCount > 0 && isset($orderCols['od_mod_history'])) {
            $sqlHistory = "UPDATE {$orderTable}
                              SET od_mod_history='" . vpnlink_native_purchase_sql_escape(trim($odModHistory)) . "'
                            WHERE od_id='{$safeOd}'
                              AND mb_id='{$safeMb}'
                            LIMIT 1";
            if (!sql_query($sqlHistory, false)) {
                return array('ok' => false, 'error' => 'order history update failed: ' . vpnlink_native_purchase_get_last_error());
            }
        }

        return array('ok' => true, 'synced_rows' => $syncCount, 'warnings' => $warnings);
    }
}

if (!function_exists('vpnlink_native_purchase_record_sales')) {
    function vpnlink_native_purchase_record_sales($orderId, $shopOdId, $mbId, $itId, $itName, $hours, $linePrice)
    {
        if (!vpnlink_native_purchase_table_exists('vpn_native_buy_sales')) {
            return true;
        }

        $safeOrder = vpnlink_native_purchase_sql_escape($orderId);
        $safeShopOd = vpnlink_native_purchase_sql_escape($shopOdId);
        $safeMb = vpnlink_native_purchase_sql_escape($mbId);
        $safeItId = vpnlink_native_purchase_sql_escape($itId);
        $safeItName = vpnlink_native_purchase_sql_escape($itName);
        $hours = max(1, (int)$hours);
        $linePrice = max(0, (int)$linePrice);

        $ok = sql_query("INSERT INTO vpn_native_buy_sales
                            (order_id, shop_od_id, mb_id, it_id, it_name, order_hours, line_price, created_at)
                          VALUES
                            ('{$safeOrder}', '{$safeShopOd}', '{$safeMb}', '{$safeItId}', '{$safeItName}', {$hours}, {$linePrice}, NOW())", false) ? true : false;
        if (!$ok) {
            vpnlink_native_purchase_set_last_error('sales_insert_failed: ' . vpnlink_native_purchase_get_last_error());
        }
        return $ok;
    }
}

if (!function_exists('vpnlink_native_purchase_compensate_order')) {
    function vpnlink_native_purchase_compensate_order($orderId, $shopOdId, $mbId, $reason = '')
    {
        global $g5;

        $orderId = trim((string)$orderId);
        $shopOdId = trim((string)$shopOdId);
        $mbId = trim((string)$mbId);
        $reason = trim((string)$reason);

        if ($shopOdId === '' || $mbId === '') {
            return array('ok' => false, 'message' => 'missing_compensation_key');
        }

        $safeOrder = vpnlink_native_purchase_sql_escape($orderId);
        $safeOd = vpnlink_native_purchase_sql_escape($shopOdId);
        $safeMb = vpnlink_native_purchase_sql_escape($mbId);

        $summary = array(
            'ok' => true,
            'order_id' => $orderId,
            'shop_od_id' => $shopOdId,
            'mb_id' => $mbId,
            'reason' => $reason,
            'deleted_order_rows' => 0,
            'deleted_cart_rows' => 0,
            'deleted_ext_rows' => 0,
            'deleted_point_rows' => 0,
            'restored_points' => 0,
            'deleted_map_rows' => 0,
            'deleted_sales_rows' => 0,
            'reset_item_rows' => 0,
            'deleted_vpnid_rows' => 0,
        );

        vpnlink_native_purchase_log('compensate_start', array(
            'order_id' => $orderId,
            'shop_od_id' => $shopOdId,
            'mb_id' => $mbId,
            'reason' => $reason,
        ));

        // 0) 영향을 받은 상품 식별(삭제 전 수집)
        $itIds = array();
        if (isset($g5['g5_shop_cart_table'])) {
            $rsCart = sql_query("SELECT DISTINCT it_id FROM {$g5['g5_shop_cart_table']} WHERE od_id='{$safeOd}' AND mb_id='{$safeMb}'", false);
            if ($rsCart) {
                while ($rr = sql_fetch_array($rsCart)) {
                    $itId = isset($rr['it_id']) ? trim((string)$rr['it_id']) : '';
                    if ($itId !== '') {
                        $itIds[] = $itId;
                    }
                }
            }
        }

        // 1) 포인트 원복: 해당 주문 차감 row 삭제 + 회원 잔액 복구
        if (isset($g5['point_table']) && isset($g5['member_table'])) {
            $pointTable = $g5['point_table'];
            // 주문번호 LIKE 패턴으로 조회 (구형·신형 포맷 모두 처리)
            $safeOdId = vpnlink_native_purchase_sql_escape($shopOdId);
            $rowPt = sql_fetch("SELECT COALESCE(SUM(po_point),0) AS sum_point, COUNT(*) AS cnt
                                  FROM {$pointTable}
                                 WHERE mb_id='{$safeMb}'
                                   AND po_content LIKE '% 주문번호 {$safeOdId} %'
                                   AND po_point < 0", false);
            $sumPoint = isset($rowPt['sum_point']) ? (int)$rowPt['sum_point'] : 0;
            $cntPoint = isset($rowPt['cnt']) ? (int)$rowPt['cnt'] : 0;

            if ($cntPoint > 0 && $sumPoint < 0) {
                sql_query("DELETE FROM {$pointTable}
                           WHERE mb_id='{$safeMb}'
                             AND po_content LIKE '% 주문번호 {$safeOdId} %'
                             AND po_point < 0", false);
                $summary['deleted_point_rows'] = $cntPoint;

                $restore = abs($sumPoint);
                if ($restore > 0) {
                    sql_query("UPDATE {$g5['member_table']}
                                  SET mb_point = mb_point + {$restore}
                                WHERE mb_id='{$safeMb}'
                                LIMIT 1", false);
                    $summary['restored_points'] = $restore;
                }
            }
        }

        // 2) 연관 보조 테이블 정리
        if (vpnlink_native_purchase_table_exists('vpn_native_buy_order_map')) {
            sql_query("DELETE FROM vpn_native_buy_order_map
                       WHERE order_id='{$safeOrder}' OR shop_od_id='{$safeOd}'", false);
            $rowMap = sql_fetch("SELECT ROW_COUNT() AS c", false);
            $summary['deleted_map_rows'] = isset($rowMap['c']) ? max(0, (int)$rowMap['c']) : 0;
        }
        if (vpnlink_native_purchase_table_exists('vpn_native_buy_sales')) {
            sql_query("DELETE FROM vpn_native_buy_sales
                       WHERE order_id='{$safeOrder}' OR shop_od_id='{$safeOd}'", false);
            $rowSales = sql_fetch("SELECT ROW_COUNT() AS c", false);
            $summary['deleted_sales_rows'] = isset($rowSales['c']) ? max(0, (int)$rowSales['c']) : 0;
        }

        // 3) extension/cart/order 제거
        if (isset($g5['g5_shop_cart_for_extension_of_period_table']) && $g5['g5_shop_cart_for_extension_of_period_table']) {
            $extTable = $g5['g5_shop_cart_for_extension_of_period_table'];
            sql_query("DELETE FROM {$extTable} WHERE od_id='{$safeOd}' AND mb_id='{$safeMb}'", false);
            $rowExt = sql_fetch("SELECT ROW_COUNT() AS c", false);
            $summary['deleted_ext_rows'] = isset($rowExt['c']) ? max(0, (int)$rowExt['c']) : 0;
        }

        if (isset($g5['g5_shop_cart_table'])) {
            sql_query("DELETE FROM {$g5['g5_shop_cart_table']} WHERE od_id='{$safeOd}' AND mb_id='{$safeMb}'", false);
            $rowCartDel = sql_fetch("SELECT ROW_COUNT() AS c", false);
            $summary['deleted_cart_rows'] = isset($rowCartDel['c']) ? max(0, (int)$rowCartDel['c']) : 0;
        }

        if (isset($g5['g5_shop_order_table'])) {
            sql_query("DELETE FROM {$g5['g5_shop_order_table']} WHERE od_id='{$safeOd}' AND mb_id='{$safeMb}' LIMIT 1", false);
            $rowOrdDel = sql_fetch("SELECT ROW_COUNT() AS c", false);
            $summary['deleted_order_rows'] = isset($rowOrdDel['c']) ? max(0, (int)$rowOrdDel['c']) : 0;
        }

        // 3-1) vpnid_list(프로비저닝 결과) 정리: od_id 컬럼이 있을 때만 주문 단위 삭제
        if (vpnlink_native_purchase_table_exists('vpnid_list')) {
            $vpnCols = vpnlink_native_purchase_table_columns('vpnid_list');
            if (isset($vpnCols['od_id'])) {
                sql_query("DELETE FROM vpnid_list WHERE od_id='{$safeOd}' AND (mb_id='{$safeMb}' OR remark='{$safeMb}' OR member_id='{$safeMb}')", false);
                $rowVpnDel = sql_fetch("SELECT ROW_COUNT() AS c", false);
                $summary['deleted_vpnid_rows'] = isset($rowVpnDel['c']) ? max(0, (int)$rowVpnDel['c']) : 0;
            }
        }

        // 4) 상품 soldout/구매자 정보 원복 (해당 주문으로 인한 점유 해제)
        if (isset($g5['g5_shop_item_table']) && is_array($itIds) && count($itIds) > 0) {
            $itemCols = vpnlink_native_purchase_table_columns($g5['g5_shop_item_table']);
            foreach ($itIds as $itIdRaw) {
                $itIdRaw = trim((string)$itIdRaw);
                if ($itIdRaw === '') {
                    continue;
                }
                $safeIt = vpnlink_native_purchase_sql_escape($itIdRaw);

                // 아직 다른 완료/입금 주문이 남아 있으면 점유 유지
                $left = sql_fetch("SELECT COUNT(*) AS cnt
                                    FROM {$g5['g5_shop_cart_table']}
                                   WHERE it_id='{$safeIt}'
                                     AND ct_status IN ('입금','완료')", false);
                $leftCnt = isset($left['cnt']) ? (int)$left['cnt'] : 0;
                if ($leftCnt > 0) {
                    continue;
                }

                $set = array();
                vpnlink_native_purchase_add_set($set, $itemCols, 'it_soldout', 0, true);
                vpnlink_native_purchase_add_set($set, $itemCols, 'it_spec_buyer_mb_id', '', false);
                vpnlink_native_purchase_add_set($set, $itemCols, 'it_spec_buyer_mb_name', '', false);
                vpnlink_native_purchase_add_set($set, $itemCols, 'it_spec_from_datetime', '', false);
                vpnlink_native_purchase_add_set($set, $itemCols, 'it_spec_to_datetime', '', false);
                if (count($set) > 0) {
                    sql_query("UPDATE {$g5['g5_shop_item_table']} SET " . implode(', ', $set) . " WHERE it_id='{$safeIt}' LIMIT 1", false);
                    $summary['reset_item_rows']++;
                }
            }
        }

        vpnlink_native_purchase_log('compensate_done', $summary);
        return $summary;
    }
}

if (!function_exists('vpnlink_after_native_vpn_purchase')) {
    function vpnlink_after_native_vpn_purchase($order, $member)
    {
        global $g5;

        vpnlink_native_purchase_set_last_error('');

        $orderId = isset($order['order_id']) ? trim((string)$order['order_id']) : '';
        $mbId = isset($member['mb_id']) ? trim((string)$member['mb_id']) : '';
        if ($orderId === '' || $mbId === '') {
            return array('success' => false, 'message' => '주문/회원 식별값이 없습니다.');
        }

        vpnlink_native_purchase_log('hook_start', array(
            'order_id' => $orderId,
            'mb_id' => $mbId,
            'native_point_price' => isset($order['point_price']) ? (int)$order['point_price'] : 0,
        ));

        if (!isset($g5['g5_shop_item_table']) || !isset($g5['g5_shop_order_table']) || !isset($g5['g5_shop_cart_table'])) {
            return array('success' => false, 'message' => '쇼핑몰 테이블 구성이 없습니다.');
        }

        if (!function_exists('vpnlink_ikev2_queue_order') || !function_exists('vpnlink_vpnid_sync_upsert_by_order')) {
            $libPath = (defined('G5_PATH') ? G5_PATH . '/mylib/ikev2_provision.lib.php' : (__DIR__ . '/ikev2_provision.lib.php'));
            if (is_file($libPath)) {
                require_once $libPath;
            }
        }

        $hasOrderMapTable = vpnlink_native_purchase_table_exists('vpn_native_buy_order_map');
        $hasSalesTable = vpnlink_native_purchase_table_exists('vpn_native_buy_sales');

        // NOTE:
        // - 승인(approve) 흐름은 외부에서 트랜잭션으로 감싸져 있다.
        // - 이 함수 안에서 CREATE TABLE을 실행하면 MySQL implicit commit이 발생해 부분 반영 리스크가 생긴다.
        // - 따라서 테이블이 없으면 핵심 주문 흐름은 계속 진행하고, 선택 로그/매핑만 건너뛴다.
        if (!$hasOrderMapTable || !$hasSalesTable) {
            vpnlink_native_purchase_log('optional_tables_missing', array(
                'order_map_exists' => $hasOrderMapTable,
                'sales_exists' => $hasSalesTable,
            ));
        }

        if ($hasOrderMapTable) {
            $mapped = sql_fetch("SELECT shop_od_id, amount FROM vpn_native_buy_order_map
                                  WHERE order_id='" . vpnlink_native_purchase_sql_escape($orderId) . "'
                                  LIMIT 1", false);
            if (!empty($mapped['shop_od_id'])) {
                $safeMappedOd = vpnlink_native_purchase_sql_escape((string)$mapped['shop_od_id']);
                $orderExists = sql_fetch("SELECT od_id FROM {$g5['g5_shop_order_table']} WHERE od_id='{$safeMappedOd}' LIMIT 1", false);
                if (empty($orderExists['od_id'])) {
                    vpnlink_native_purchase_log('mapped_order_missing_rebuild', array(
                        'order_id' => $orderId,
                        'mapped_shop_od_id' => (string)$mapped['shop_od_id'],
                    ));
                    sql_query("DELETE FROM vpn_native_buy_order_map WHERE order_id='" . vpnlink_native_purchase_sql_escape($orderId) . "'", false);
                } else {
                return array(
                    'success' => true,
                    'message' => '기존 주문 재사용',
                    'shop_od_id' => (string)$mapped['shop_od_id'],
                    'amount' => isset($mapped['amount']) ? (int)$mapped['amount'] : 0,
                    'idempotent' => true,
                );
                }
            }
        }

        $payload = vpnlink_native_purchase_parse_payload($order);
        $productIds = isset($payload['selected_product_ids']) && is_array($payload['selected_product_ids'])
            ? $payload['selected_product_ids']
            : array();
        $hoursList = isset($payload['selected_order_hours']) && is_array($payload['selected_order_hours'])
            ? $payload['selected_order_hours']
            : array();
        $priceList = isset($payload['selected_item_prices']) && is_array($payload['selected_item_prices'])
            ? $payload['selected_item_prices']
            : array();

        $isRenewal = false;
        if (isset($payload['is_renewal'])) {
            $renewalRaw = strtolower(trim((string)$payload['is_renewal']));
            $isRenewal = in_array($renewalRaw, array('1', 'true', 'yes', 'y', 'on'), true);
        }

        $targetProfileOldExpiry = trim((string)($payload['target_profile_old_expiry'] ?? ''));
        $renewalBaseAt = '';
        if ($isRenewal) {
            $baseTs = time();
            $oldTs = strtotime($targetProfileOldExpiry);
            if ($oldTs !== false && $oldTs > $baseTs) {
                $baseTs = $oldTs;
            }
            $renewalBaseAt = date('Y-m-d H:i:s', $baseTs);
        }

        if (count($productIds) < 1 && !empty($order['it_id'])) {
            $productIds[] = (string)$order['it_id'];
        }
        if (count($productIds) < 1) {
            return array('success' => false, 'message' => '주문 상품이 없습니다.');
        }

        $itemCols = vpnlink_native_purchase_table_columns($g5['g5_shop_item_table']);
        $selectParts = array('it_id', 'it_name', 'it_price');
        $selectParts[] = isset($itemCols['it_is_hour_sale_price']) ? 'it_is_hour_sale_price' : '0 AS it_is_hour_sale_price';
        $selectParts[] = isset($itemCols['it_sc_type']) ? 'it_sc_type' : '1 AS it_sc_type';
        $selectParts[] = isset($itemCols['it_sc_method']) ? 'it_sc_method' : '0 AS it_sc_method';
        $selectParts[] = isset($itemCols['it_sc_price']) ? 'it_sc_price' : '0 AS it_sc_price';
        $selectParts[] = isset($itemCols['it_sc_minimum']) ? 'it_sc_minimum' : '0 AS it_sc_minimum';
        $selectParts[] = isset($itemCols['it_sc_qty']) ? 'it_sc_qty' : '0 AS it_sc_qty';
        $selectParts[] = isset($itemCols['pt_it']) ? 'pt_it' : '2 AS pt_it';
        $selectParts[] = isset($itemCols['pt_id']) ? 'pt_id' : "'' AS pt_id";
        $selectParts[] = isset($itemCols['it_spec_buyer_mb_id']) ? 'it_spec_buyer_mb_id' : "'' AS it_spec_buyer_mb_id";

        $items = array();
        $totalAmount = 0;
        foreach ($productIds as $idx => $rawId) {
            $itId = preg_replace('/[^A-Za-z0-9_\-]/', '', trim((string)$rawId));
            if ($itId === '') {
                return array('success' => false, 'message' => '주문 스냅샷 상품 정보가 올바르지 않습니다.');
            }
            $row = sql_fetch("SELECT " . implode(', ', $selectParts) . "
                               FROM {$g5['g5_shop_item_table']}
                              WHERE it_id='" . vpnlink_native_purchase_sql_escape($itId) . "'
                              LIMIT 1", false);
            if (!is_array($row) || empty($row['it_id'])) {
                return array('success' => false, 'message' => '주문 스냅샷 상품 정보를 확인할 수 없습니다.');
            }

            if (!isset($hoursList[$idx])) {
                return array('success' => false, 'message' => '주문 스냅샷 기간 정보가 올바르지 않습니다.');
            }
            $hours = (int)$hoursList[$idx];
            if ($hours < 1) {
                return array('success' => false, 'message' => '주문 스냅샷 기간 정보가 올바르지 않습니다.');
            }

            if (!isset($priceList[$idx])) {
                return array('success' => false, 'message' => '주문 스냅샷 금액 정보가 올바르지 않습니다.');
            }
            $linePrice = (int)$priceList[$idx];
            if ($linePrice <= 0) {
                return array('success' => false, 'message' => '주문 스냅샷 금액 정보가 올바르지 않습니다.');
            }

            $items[] = array(
                'it_id' => (string)$row['it_id'],
                'it_name' => isset($row['it_name']) ? (string)$row['it_name'] : '',
                'it_price' => isset($row['it_price']) ? (int)$row['it_price'] : 0,
                'it_is_hour_sale_price' => isset($row['it_is_hour_sale_price']) ? (int)$row['it_is_hour_sale_price'] : 0,
                'it_sc_type' => isset($row['it_sc_type']) ? (int)$row['it_sc_type'] : 1,
                'it_sc_method' => isset($row['it_sc_method']) ? (int)$row['it_sc_method'] : 0,
                'it_sc_price' => isset($row['it_sc_price']) ? (int)$row['it_sc_price'] : 0,
                'it_sc_minimum' => isset($row['it_sc_minimum']) ? (int)$row['it_sc_minimum'] : 0,
                'it_sc_qty' => isset($row['it_sc_qty']) ? (int)$row['it_sc_qty'] : 0,
                'pt_it' => isset($row['pt_it']) ? (int)$row['pt_it'] : 2,
                'pt_id' => isset($row['pt_id']) ? (string)$row['pt_id'] : '',
                'it_spec_buyer_mb_id' => isset($row['it_spec_buyer_mb_id']) ? (string)$row['it_spec_buyer_mb_id'] : '',
                'hours' => $hours,
                'line_price' => $linePrice,
            );
            $totalAmount += $linePrice;
        }

        if (count($items) < 1 || $totalAmount <= 0 || count($items) !== count($productIds)) {
            return array('success' => false, 'message' => '상품 조회 또는 금액 계산 실패');
        }

        $nativePrice = isset($order['point_price']) ? (int)$order['point_price'] : 0;
        if ($nativePrice > 0 && $nativePrice !== $totalAmount) {
            vpnlink_native_purchase_log('price_mismatch', array(
                'order_id' => $orderId,
                'native_point_price' => $nativePrice,
                'calculated_total' => $totalAmount,
                'line_count' => count($items),
            ));
            return array(
                'success' => false,
                'message' => '주문 금액 불일치: native=' . $nativePrice . ', calculated=' . $totalAmount,
            );
        }

        if (!function_exists('insert_point')) {
            return array('success' => false, 'message' => '포인트 원장 함수(insert_point)가 없습니다.');
        }

        // ── 포인트 잔액 사전 확인 (주문 생성 전 빠른 실패) ──
        $_safeMbPre  = vpnlink_native_purchase_sql_escape($mbId);
        $_balancePre = sql_fetch("SELECT mb_point FROM {$g5['member_table']} WHERE mb_id='{$_safeMbPre}' LIMIT 1", false);
        $_pointsPre  = isset($_balancePre['mb_point']) ? (int)$_balancePre['mb_point'] : 0;
        if ($_pointsPre < $totalAmount) {
            return array(
                'success'    => false,
                'message'    => '포인트 잔액이 부족합니다. (보유: ' . number_format($_pointsPre) . 'P / 결제 필요: ' . number_format($totalAmount) . 'P)',
                'error_code' => 'INSUFFICIENT_BALANCE',
            );
        }

        $shopOdId = vpnlink_native_purchase_make_shop_order_id($orderId);
        if (!vpnlink_native_purchase_insert_order($shopOdId, $mbId, $member, $totalAmount, count($items), $orderId)) {
            return array('success' => false, 'message' => '주문서 생성 실패: ' . vpnlink_native_purchase_get_last_error());
        }

        $cartCtIds = array();
        foreach ($items as $line) {
            // [FIX 2026-05-08]
            // 승인 확정 시 기간 누적 규칙 고정:
            //   new_expiry = max(current_expiry, now) + extension_hours
            // 분할 승인/연속 승인에서도 누적되도록 라인별 base 시각을 재조회(락 포함)한다.
            $lineBaseTs = time();

            if ($isRenewal && $renewalBaseAt !== '') {
                $renewalTs = strtotime($renewalBaseAt);
                if ($renewalTs !== false && $renewalTs > $lineBaseTs) {
                    $lineBaseTs = $renewalTs;
                }
            }

            $profileExpire = vpnlink_native_purchase_lookup_profile_expiry_for_item(
                $mbId,
                isset($line['it_id']) ? (string)$line['it_id'] : '',
                true
            );
            if ($profileExpire !== '') {
                $profileTs = strtotime($profileExpire);
                if ($profileTs !== false && $profileTs > $lineBaseTs) {
                    $lineBaseTs = $profileTs;
                }
            }

            $lineBaseAt = date('Y-m-d H:i:s', $lineBaseTs);
            vpnlink_native_purchase_log('line_base_decision', array(
                'order_id' => $orderId,
                'mb_id' => $mbId,
                'it_id' => isset($line['it_id']) ? (string)$line['it_id'] : '',
                'hours' => isset($line['hours']) ? (int)$line['hours'] : 0,
                'is_renewal' => $isRenewal ? 1 : 0,
                'renewal_base_at' => (string)$renewalBaseAt,
                'profile_expire' => (string)$profileExpire,
                'line_base_at' => (string)$lineBaseAt,
            ));
            $ctInserted = vpnlink_native_purchase_insert_cart_line($shopOdId, $mbId, $line, $line['hours'], $line['line_price'], $lineBaseAt);
            if ($ctInserted === false) {
                $comp = vpnlink_native_purchase_compensate_order($orderId, $shopOdId, $mbId, 'cart_insert_failed');
                return array(
                    'success' => false,
                    'message' => '장바구니 라인 생성 실패: ' . vpnlink_native_purchase_get_last_error(),
                    'shop_od_id' => $shopOdId,
                    'compensated' => !empty($comp['ok']),
                    'compensation' => $comp,
                );
            }
            if (is_int($ctInserted) && $ctInserted > 0) {
                $cartCtIds[] = $ctInserted;
            }
            if ($hasSalesTable) {
                vpnlink_native_purchase_record_sales($orderId, $shopOdId, $mbId, $line['it_id'], $line['it_name'], $line['hours'], $line['line_price']);
            }
        }

        vpnlink_native_purchase_refresh_order_amounts($shopOdId);

        // 포인트 메시지: 신규계약/계약연장 주문번호 ... 상품명 ... (X일Y시간) 결제
        $_firstItem  = !empty($items) ? reset($items) : array();
        $_itName     = !empty($_firstItem['it_name']) ? (string)$_firstItem['it_name'] : '';
        $_firstHours = !empty($_firstItem['hours'])   ? (int)$_firstItem['hours']      : 0;
        $_extraCount = count($items) - 1;
        $_prodStr    = $_itName . ($_extraCount > 0 ? ' 외 ' . $_extraCount . '건' : '');
        $_days       = intdiv($_firstHours, 24);
        $_hrs        = $_firstHours % 24;
        $_period     = $_days . '일' . $_hrs . '시간';
        $_mbNick     = isset($member['mb_nick']) ? trim((string)$member['mb_nick']) : '';
        $_orderLabel = $isRenewal ? '계약연장' : '신규계약';
        $pointMessage = $_orderLabel . ' 주문번호 ' . $shopOdId
            . ' ' . $_prodStr
            . ' (' . $_period . ') 결제'
            . ' (이용자: ' . $mbId . ' / ' . $_mbNick . ')';
        // 포인트 차감: 상위 트랜잭션(strongswan_vpnid_buy.php)에서 이미
        //   SELECT mb_point FOR UPDATE 로 잠금 후 잔액 확인을 마쳤으므로 직접 차감한다.
        //   (내부에서 START TRANSACTION을 호출하면 외부 트랜잭션이 묵시적으로 커밋되어 락이 풀림)
        insert_point($mbId, (-1) * $totalAmount, $pointMessage);

        if (function_exists('apms_order')) {
            $mbRecommend = isset($member['mb_recommend']) ? (string)$member['mb_recommend'] : '';
            @apms_order($shopOdId, '입금', $mbRecommend);
        }
        if (function_exists('apms_coupon_update')) {
            @apms_coupon_update($mbId);
        }

        $postSync = vpnlink_native_purchase_sync_postpaid_tables($shopOdId, $mbId, $member, $orderId, $isRenewal);
        if (empty($postSync['ok'])) {
            vpnlink_native_purchase_log('post_sync_warning', array(
                'order_id' => $orderId,
                'shop_od_id' => $shopOdId,
                'mb_id' => $mbId,
                'error' => isset($postSync['error']) ? (string)$postSync['error'] : 'unknown',
            ));
            $postSync = array(
                'ok' => false,
                'error' => isset($postSync['error']) ? (string)$postSync['error'] : 'unknown',
                'warnings' => isset($postSync['warnings']) && is_array($postSync['warnings']) ? $postSync['warnings'] : array(),
            );
        }

        $memberAfter = sql_fetch("SELECT mb_point FROM {$g5['member_table']} WHERE mb_id='" . vpnlink_native_purchase_sql_escape($mbId) . "' LIMIT 1", false);
        $pointAfter = isset($memberAfter['mb_point']) ? (int)$memberAfter['mb_point'] : 0;

                if ($hasOrderMapTable) {
                        sql_query("INSERT INTO vpn_native_buy_order_map
                                                (order_id, shop_od_id, mb_id, amount, created_at)
                                                VALUES
                                                ('" . vpnlink_native_purchase_sql_escape($orderId) . "',
                                                 '" . vpnlink_native_purchase_sql_escape($shopOdId) . "',
                                                 '" . vpnlink_native_purchase_sql_escape($mbId) . "',
                                                 " . (int)$totalAmount . ",
                                                 NOW())
                                             ON DUPLICATE KEY UPDATE
                                                    shop_od_id = VALUES(shop_od_id),
                                                    mb_id = VALUES(mb_id),
                                                    amount = VALUES(amount)", false);
                }

        $sync = array('queued' => false, 'error' => '');
        if (function_exists('vpnlink_ikev2_queue_order')) {
            $q = vpnlink_ikev2_queue_order($shopOdId, 'native_vpn_buy');
            if (!empty($q['ok'])) {
                $sync['queued'] = true;
            } else {
                $sync['error'] = isset($q['error']) ? (string)$q['error'] : 'queue_failed';
            }
        }
        if (function_exists('vpnlink_vpnid_sync_upsert_by_order')) {
            $s = vpnlink_vpnid_sync_upsert_by_order($shopOdId, '', 'native_vpn_buy_paid');
            if (empty($s['ok']) && $sync['error'] === '') {
                $sync['error'] = isset($s['error']) ? (string)$s['error'] : 'vpnid_sync_failed';
            }
        }

        vpnlink_native_purchase_log('hook_success', array(
            'order_id' => $orderId,
            'shop_od_id' => $shopOdId,
            'amount' => $totalAmount,
            'line_count' => count($items),
            'cart_ct_ids' => $cartCtIds,
            'sync' => $sync,
        ));

        return array(
            'success' => true,
            'message' => '정식 주문/결제 및 프로필 동기화 요청 완료',
            'shop_od_id' => $shopOdId,
            'amount' => $totalAmount,
            'point_after' => $pointAfter,
            'sync' => $sync,
            'line_count' => count($items),
            'cart_ct_ids' => $cartCtIds,
            'post_sync' => $postSync,
        );
    }
}
?>