<?php
include_once('./_common.php');

global $g5;

$g5['title'] = "StrongSwan Profile Check";

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

// 헤더 설정 (CORS 허용 및 JSON 응답 타입 지정)
header('Access-Control-Allow-Origin: *');
header('Content-Type: application/json; charset=UTF-8');

// 결과 배열 초기화
$profiles = array();

if (!function_exists('vpnlink_profile_check_has_column')) {
    function vpnlink_profile_check_has_column($table, $column)
    {
        $table = trim((string)$table);
        $column = trim((string)$column);
        if ($table === '' || $column === '') {
            return false;
        }

        static $cache = array();
        if (!isset($cache[$table])) {
            $cache[$table] = array();
            $safeTable = str_replace('`', '', $table);
            $res = sql_query("SHOW COLUMNS FROM `{$safeTable}`", false);
            if ($res) {
                while ($r = sql_fetch_array($res)) {
                    if (!empty($r['Field'])) {
                        $cache[$table][$r['Field']] = true;
                    }
                }
            }
        }

        return isset($cache[$table][$column]);
    }
}

if (!function_exists('vpnlink_profile_pick_first')) {
    function vpnlink_profile_pick_first($row, $keys, $default = '')
    {
        if (!is_array($row) || !is_array($keys)) {
            return $default;
        }
        foreach ($keys as $k) {
            if (isset($row[$k]) && trim((string)$row[$k]) !== '') {
                return (string)$row[$k];
            }
        }
        return $default;
    }
}

// POST/REQUEST 파라미터 수신 (로그인 체크와 동일하게 mb_email 우선 처리)
$post_mb_email = isset($_REQUEST['mb_email']) ? trim($_REQUEST['mb_email']) : '';
$post_mb_id = isset($_REQUEST['mb_id']) ? trim($_REQUEST['mb_id']) : '';

if ($post_mb_email !== '') {
    $mb_id = $post_mb_email;
} else if ($post_mb_id !== '') {
    $mb_id = $post_mb_id;
} else {
    $mb_id = '';
}

$mb_password = isset($_REQUEST['mb_password']) ? $_REQUEST['mb_password'] : '';
$app_language_org = isset($_REQUEST['app_language']) ? trim($_REQUEST['app_language']) : '';
$app_language = isset($_REQUEST['app_language']) ? strtolower(trim($_REQUEST['app_language'])) : 'en';
$app_timezone = isset($_REQUEST['app_timezone']) ? trim($_REQUEST['app_timezone']) : 'Asia/Seoul';

// [로그 포인트 #1] 요청 시작 직후
error_log("[PROFILE_CHECK] mb_id={$mb_id}, lang={$app_language}, tz={$app_timezone}");

// 유료 회원 식별자 정규화: 이메일 또는 mb_id 입력을 실제 mb_id로 통일
$resolved_mb = array();
if ($mb_id !== '' && !preg_match('/^VpNgueSt/', $mb_id)) {
    // 이메일 입력(또는 mb_email 파라미터 제공)인 경우 mb_email 컬럼 우선 조회
    if ($post_mb_email !== '' || filter_var($mb_id, FILTER_VALIDATE_EMAIL)) {
        $safe_identifier = function_exists('sql_real_escape_string')
            ? sql_real_escape_string($mb_id)
            : addslashes($mb_id);
        $resolved_mb = sql_fetch(" select * from {$g5['member_table']} where mb_email = '{$safe_identifier}' ");
    }

    // 이메일 조회 실패 시 mb_id 컬럼으로 재조회
    if (!isset($resolved_mb['mb_id']) || !$resolved_mb['mb_id']) {
        $resolved_mb = get_member($mb_id);
    }

    // 프로필 생성/조회는 반드시 실제 mb_id 기준으로 처리
    if (isset($resolved_mb['mb_id']) && $resolved_mb['mb_id']) {
        $mb_id = $resolved_mb['mb_id'];
    }
}

if ($mb_id !== '') {
    $safe_mb_id = function_exists('sql_real_escape_string')
        ? sql_real_escape_string($mb_id)
        : addslashes($mb_id);

    $vpnTable = 'vpnid_list';
    $hasMbIdCol = vpnlink_profile_check_has_column($vpnTable, 'mb_id');
    $hasMemberIdCol = vpnlink_profile_check_has_column($vpnTable, 'member_id');
    $hasRemarkCol = vpnlink_profile_check_has_column($vpnTable, 'remark');
    $hasEnableCol = vpnlink_profile_check_has_column($vpnTable, 'enable');
    $hasExpireCol = vpnlink_profile_check_has_column($vpnTable, 'vpn_id_expire');
    $hasUpdateDatetimeCol = vpnlink_profile_check_has_column($vpnTable, 'update_datetime');
    $hasDatetimeCol = vpnlink_profile_check_has_column($vpnTable, 'datetime');
    $hasNoCol = vpnlink_profile_check_has_column($vpnTable, 'no');

    $identityParts = array();
    if ($hasMbIdCol) {
        $identityParts[] = "mb_id='{$safe_mb_id}'";
    }
    if ($hasMemberIdCol) {
        $identityParts[] = "member_id='{$safe_mb_id}'";
    }
    if ($hasRemarkCol) {
        $identityParts[] = "remark='{$safe_mb_id}'";
    }

    if (count($identityParts) < 1) {
        error_log('[PROFILE_CHECK][EMPTY] reason=no_identity_column mb_id/remark not found in vpnid_list');
        echo json_encode($profiles, JSON_UNESCAPED_UNICODE);
        exit;
    }

    $identityWhere = '(' . implode(' OR ', $identityParts) . ')';
    // enable=0 이지만 최근 30일 이내 해지된 프로필도 포함하여 앱이 정확한 만료일을 동기화하게 한다.
    $enableWhere = $hasEnableCol
        ? '(enable IN (1,2) OR (enable=0 AND vpn_id_expire >= DATE_SUB(NOW(), INTERVAL 30 DAY)))'
        : '1=1';

    $where_core = $identityWhere . ' AND ' . $enableWhere;
    $expiry_note = "no hard expiry filter";

    // [로그 포인트 #2] SQL 실행 직전
    error_log("[PROFILE_CHECK] where: identity={$identityWhere}, status={$enableWhere}, use_yn=n/a, deleted=n/a, visible=n/a, expiry={$expiry_note}");

    $orderParts = array();
    // 1순위: 만료되지 않은(유효한) 프로필 먼저
    if ($hasExpireCol) {
        $orderParts[] = "CASE WHEN vpn_id_expire = '0000-00-00 00:00:00' OR vpn_id_expire IS NULL OR vpn_id_expire > NOW() THEN 0 ELSE 1 END ASC";
    }
    // 2순위: 상품번호(VPN 번호) 높은 순
    $orderParts[] = "CAST(SUBSTRING_INDEX(it_name, '-', -1) AS UNSIGNED) DESC";
    // 3순위: no 높은 순
    if ($hasNoCol) {
        $orderParts[] = 'no DESC';
    }
    if (count($orderParts) < 1) {
        $orderParts[] = '1 DESC';
    }
    $orderBy = implode(', ', $orderParts);

    $sql = "SELECT *
              FROM {$vpnTable}
             WHERE {$where_core}
          ORDER BY {$orderBy}";
    $result = sql_query($sql, false);

    $rows = array();
    if ($result) {
        while ($row = sql_fetch_array($result)) {
            $rows[] = $row;
        }
    }

    $fallback_used = false;
    if (count($rows) < 1 && $hasEnableCol) {
        $fallbackWhere = $identityWhere . " AND (enable IN (1,2) OR enable IS NULL)";

        $recentParts = array();
        if ($hasExpireCol) {
            $recentParts[] = "vpn_id_expire >= DATE_SUB(NOW(), INTERVAL 1 DAY)";
            $recentParts[] = "vpn_id_expire='0000-00-00 00:00:00'";
            $recentParts[] = "vpn_id_expire IS NULL";
        }
        if ($hasUpdateDatetimeCol) {
            $recentParts[] = "update_datetime >= DATE_SUB(NOW(), INTERVAL 15 MINUTE)";
        }
        if ($hasDatetimeCol) {
            $recentParts[] = "datetime >= DATE_SUB(NOW(), INTERVAL 15 MINUTE)";
        }
        if (count($recentParts) > 0) {
            $fallbackWhere .= ' AND (' . implode(' OR ', $recentParts) . ')';
        }

        $fallbackSql = "SELECT *
                          FROM {$vpnTable}
                         WHERE {$fallbackWhere}
                      ORDER BY {$orderBy}";
        $fallbackResult = sql_query($fallbackSql, false);
        if ($fallbackResult) {
            while ($fr = sql_fetch_array($fallbackResult)) {
                $rows[] = $fr;
            }
        }

        if (count($rows) > 0) {
            $fallback_used = true;
            error_log('[PROFILE_CHECK] fallback_recent_rows_used=1 rows=' . count($rows));
        }
    }

    // [로그 포인트 #3] SQL 실행 직후
    error_log("[PROFILE_CHECK] rows=" . count($rows) . ", fallback_used=" . ($fallback_used ? '1' : '0'));

    if (count($rows) < 1) {
        $mb_exists = false;
        if (isset($resolved_mb['mb_id']) && $resolved_mb['mb_id'] !== '') {
            $mb_exists = true;
        } elseif ($mb_id !== '' && !preg_match('/^VpNgueSt/', $mb_id)) {
            $mb_check = get_member($mb_id);
            if (isset($mb_check['mb_id']) && $mb_check['mb_id'] !== '') {
                $mb_exists = true;
            }
        }

        $row_any = sql_fetch("SELECT COUNT(*) AS cnt FROM {$vpnTable} WHERE {$identityWhere}", false);
        $row_enable = sql_fetch("SELECT COUNT(*) AS cnt FROM {$vpnTable} WHERE {$identityWhere} AND {$enableWhere}", false);
        $row_expired = $hasExpireCol
            ? sql_fetch("SELECT COUNT(*) AS cnt FROM {$vpnTable} WHERE {$identityWhere} AND {$enableWhere} AND vpn_id_expire < NOW()", false)
            : array('cnt' => 0);
        $row_remark = $hasRemarkCol
            ? sql_fetch("SELECT COUNT(*) AS cnt FROM {$vpnTable} WHERE remark='{$safe_mb_id}'", false)
            : array('cnt' => 0);

        $cnt_any = isset($row_any['cnt']) ? (int)$row_any['cnt'] : 0;
        $cnt_enable = isset($row_enable['cnt']) ? (int)$row_enable['cnt'] : 0;
        $cnt_expired = isset($row_expired['cnt']) ? (int)$row_expired['cnt'] : 0;
        $cnt_remark = isset($row_remark['cnt']) ? (int)$row_remark['cnt'] : 0;

        $reason = 'unknown';
        if (!$mb_exists) {
            $reason = 'member_not_found';
        } else if ($cnt_any > 0 && $cnt_enable === 0) {
            $reason = 'filtered_by_enable';
        } else if ($cnt_enable > 0 && $cnt_expired >= $cnt_enable) {
            $reason = 'all_expired_or_inactive';
        } else if ($cnt_any > 0 && $cnt_remark > 0) {
            $reason = 'rows_mapped_via_remark';
        } else if ($cnt_any === 0) {
            $reason = 'no_profile_rows';
        }

        // [로그 포인트 #4] 빈 결과 원인 분기
        error_log("[PROFILE_CHECK][EMPTY] mb_exists=" . ($mb_exists ? '1' : '0') . ", cnt_any={$cnt_any}, cnt_enable={$cnt_enable}, cnt_expired={$cnt_expired}, cnt_remark={$cnt_remark}, reason={$reason}");
    }




    // ── 상품 DB 존재 여부 체크 ────────────────────────────────────────
    if (count($rows) > 0 && isset($g5['g5_shop_item_table'])) {
        $itNamesToCheck = array();
        foreach ($rows as $_r) {
            $_itN = trim(vpnlink_profile_pick_first($_r, array('it_name', 'member_memo', 'product_name')));
            if ($_itN !== '') {
                $itNamesToCheck[] = $_itN;
            }
        }
        $existingItNames = array();
        $itGradeMap = array();
        if (count($itNamesToCheck) > 0) {
            $_inParts = array();
            foreach ($itNamesToCheck as $_n) {
                $_inParts[] = "'" . (function_exists('sql_real_escape_string') ? sql_real_escape_string($_n) : addslashes($_n)) . "'";
            }
            $_existRes = sql_query("SELECT it_name, it_spec_grade FROM {$g5['g5_shop_item_table']} WHERE it_name IN (" . implode(',', $_inParts) . ")", false);
            if ($_existRes) {
                while ($_er = sql_fetch_array($_existRes)) {
                    $_itNameKey = trim((string)$_er['it_name']);
                    $existingItNames[$_itNameKey] = true;
                    if (!empty($_er['it_spec_grade'])) {
                        $itGradeMap[$_itNameKey] = trim((string)$_er['it_spec_grade']);
                    }
                }
            }
        }
        $filteredRows = array();
        foreach ($rows as $_r) {
            $_itN = trim(vpnlink_profile_pick_first($_r, array('it_name', 'member_memo', 'product_name')));
            if ($_itN === '' || isset($existingItNames[$_itN])) {
                $filteredRows[] = $_r;
            } else {
                $_safe_log_mb = function_exists('sql_real_escape_string') ? sql_real_escape_string($mb_id) : addslashes($mb_id);
                $_safe_log_it = function_exists('sql_real_escape_string') ? sql_real_escape_string($_itN) : addslashes($_itN);
                sql_query("INSERT INTO vpnlink_invalid_product_log (mb_id, vpn_it_name, shop_it_id, action_type, user_message) VALUES ('$_safe_log_mb', '$_safe_log_it', '', 'profile_hidden', '상품이 g5_shop_item에 없어 프로필 목록에서 숨김')", false);
                error_log("[PROFILE_CHECK][INVALID_PRODUCT] mb_id={$mb_id}, it_name={$_itN}, reason=shop_item_not_found");
            }
        }
        $rows = $filteredRows;
    }
    // ── /상품 DB 존재 여부 체크 ──────────────────────────────────────
    foreach ($rows as $row) {
        $vpn_user_id = trim(vpnlink_profile_pick_first($row, array('vpn_user_id', 'member_id', 'user_id', 'username', 'vpn_id', 'vpn_user')));
        $vpn_user_pwd = vpnlink_profile_pick_first($row, array('vpn_user_pwd', 'member_pwd', 'vpn_user_pw', 'user_password', 'password', 'vpn_pwd'));
        $server_name = trim(vpnlink_profile_pick_first($row, array('server_name', 'server_ip', 'gateway', 'svr_domain', 'host')));
        $it_name = trim(vpnlink_profile_pick_first($row, array('it_name', 'member_memo', 'product_name')));
        $vpn_no = (int)vpnlink_profile_pick_first($row, array('no', 'id', 'vpn_id'), '0');
        $expiry = trim(vpnlink_profile_pick_first($row, array('vpn_id_expire', 'member_id_expire', 'expire_datetime', 'expiry', 'expired_at')));
        $country_raw = trim(vpnlink_profile_pick_first($row, array('country_name', 'country', 'server_country')));

        // 자격증명이 비어있는 구형/이행 데이터는 프로비저닝 큐 성공 이력으로 보정
        if (($vpn_user_id === '' || $vpn_user_pwd === '') && $mb_id !== '' && $server_name !== '') {
            $safe_mb_q = function_exists('sql_real_escape_string') ? sql_real_escape_string($mb_id) : addslashes($mb_id);
            $safe_server_q = function_exists('sql_real_escape_string') ? sql_real_escape_string($server_name) : addslashes($server_name);
            $q = sql_fetch("SELECT *
                             FROM app_ikev2_provision_queue
                            WHERE mb_id='{$safe_mb_q}'
                              AND server_ip='{$safe_server_q}'
                              AND final_status='success'
                         ORDER BY queue_id DESC
                            LIMIT 1", false);
            if (is_array($q)) {
                if ($vpn_user_id === '') {
                    $vpn_user_id = trim(vpnlink_profile_pick_first($q, array('vpn_user_id', 'member_id', 'user_id', 'username')));
                }
                if ($vpn_user_pwd === '') {
                    $vpn_user_pwd = vpnlink_profile_pick_first($q, array('vpn_user_pwd', 'member_pwd', 'user_password', 'password'));
                }
                if ($expiry === '') {
                    $expiry = trim(vpnlink_profile_pick_first($q, array('expire_at', 'vpn_id_expire', 'member_id_expire')));
                }
            }
        }

        if ($vpn_user_id === '' || $server_name === '') {
            continue;
        }

        // country_name 정규화 (Android CountryAliasResolver 매핑과 일치)
        $country_code = strtoupper($country_raw);
        $country_normalize = array(
            'KOREA, REPUBLIC OF' => 'KOREA',
            'REPUBLIC OF KOREA'  => 'KOREA',
            'SOUTH KOREA'        => 'KOREA',
            'KOR'                => 'KOREA',
            'JPN'                => 'JAPAN',
            'CHN'                => 'CHINA',
        );
        if (isset($country_normalize[$country_code])) {
            $country_code = $country_normalize[$country_code];
        }
        if ($country_code === '-') {
            $country_code = '';
        }

        if ($it_name === '') {
            $it_name = 'VPN-' . $vpn_no;
        }

        // country_name 컬럼을 이용해 "VPNLink-KOREA-VPN-10785" 형식으로 조립
        // 앱의 extractVpnLinkCountryTag()가 국가 코드를 파싱해 국기 이모지를 표시함
        if ($country_code !== '' && stripos($it_name, $country_code . '-') !== 0) {
            $set_name = 'VPNLink-' . $country_code . '-' . $it_name;
        } else {
            $set_name = 'VPNLink-' . $it_name;
        }

        $plan_name_raw = trim(vpnlink_profile_pick_first($row, array('plan_name', 'grade', 'product_grade')));
        if (empty($plan_name_raw) && isset($itGradeMap[$it_name])) {
            $plan_name_raw = $itGradeMap[$it_name];
        }
        // enable=0 (해지/비활성) 프로필은 이름에 [해지됨]을 붙여 반환한다.
        // Android 앱이 이를 서버 동기화 프로필로 인식해 올바른 만료일을 로컬 DB에 갱신한다.
        $profile_enable = isset($row['enable']) ? (int)$row['enable'] : 1;
        $final_set_name = ($profile_enable === 0) ? ($set_name . ' [해지됨]') : $set_name;
        $profiles[] = array(
            'result' => 'success',
            'setName' => $final_set_name,
            'setGateway' => $server_name,
            'setUsername' => $vpn_user_id,
            'setPassword' => $vpn_user_pwd,
            'expiry' => $expiry,
            'plan_name' => strtoupper($plan_name_raw),
        );
    }
}

// 최종 JSON 응답 출력
echo json_encode($profiles, JSON_UNESCAPED_UNICODE);

?>