<?php

require_once __DIR__ . '/collection_provider_inc.php';

define('JKEX_CONFIG_API_KEY', 'jkex_dailydata_api_key');
define('JKEX_CONFIG_BASE_URL', 'jkex_dailydata_base_url');
define('JKEX_DEFAULT_BASE_URL', 'https://jkexx.amoikiss.com');

function jkex_get_default_banks()
{
    return ['INLINE', 'MC SELL', 'MC BUY', 'BANK BURN', 'OTHER', 'EXPENSE'];
}

function jkex_get_valid_data_types()
{
    return ['normal', 'cutoff', 'closing'];
}

function jkex_normalize_data_type($dataType)
{
    $dataType = strtolower(trim((string)$dataType));
    if (!in_array($dataType, jkex_get_valid_data_types(), true)) {
        return 'normal';
    }
    return $dataType;
}

function jkex_is_data_type_segment($segment)
{
    $segment = strtolower(trim((string)$segment));
    return $segment === 'cutoff' || $segment === 'closing';
}

function jkex_build_single_import_url($baseUrl, $companySlug, $bankName, $dateYmd, $apiKey, $dataType = 'normal')
{
    $dataType = jkex_normalize_data_type($dataType);
    $bankSegment = rawurlencode($bankName);
    $apiKey = trim((string)$apiKey);

    if ($dataType === 'normal') {
        if (jkex_is_global_bank($bankName)) {
            return $baseUrl . '/api/dailydata/exclude/Expense/'
                . $dateYmd
                . '?api_key=' . rawurlencode($apiKey);
        }
        return $baseUrl . '/api/dailydata/exclude/'
            . rawurlencode($companySlug) . '/'
            . $bankSegment . '/'
            . $dateYmd
            . '?api_key=' . rawurlencode($apiKey);
    }

    // cutoff / closing：/api/dailydata/exclude/{bank}/{cutoff|closing}/{YYYYMMDD}
    return $baseUrl . '/api/dailydata/exclude/'
        . $bankSegment . '/'
        . $dataType . '/'
        . $dateYmd
        . '?api_key=' . rawurlencode($apiKey);
}

// 全局类型：链接不含 company（如 /api/dailydata/exclude/Expense/{YYYYMMDD}），返回所有公司的记录
function jkex_is_global_bank($bankName)
{
    $normalized = strtoupper(trim(str_replace('+', ' ', (string)$bankName)));
    return $normalized === 'EXPENSE';
}

function jkex_get_config_value($conn, $configKey)
{
    $sql = "SELECT ConfigValue FROM SystemConfig WHERE ConfigKey = ? ORDER BY ID DESC LIMIT 1";
    $stmt = mysqli_prepare($conn, $sql);
    if (!$stmt) {
        return null;
    }
    mysqli_stmt_bind_param($stmt, 's', $configKey);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    $value = null;
    if ($row = mysqli_fetch_assoc($result)) {
        $value = $row['ConfigValue'];
    }
    mysqli_stmt_close($stmt);
    return $value;
}

function jkex_normalize_company_slug($value)
{
    $value = trim((string)$value);
    if ($value === '') {
        return '';
    }

    if (preg_match('/^https?:\/\//i', $value)) {
        $host = parse_url($value, PHP_URL_HOST);
        if (is_string($host) && $host !== '') {
            $parts = explode('.', strtolower($host));
            return $parts[0] ?? strtolower($host);
        }
    }

    return strtolower($value);
}

function jkex_resolve_company_slug_for_website($conn, $websiteId)
{
    $websiteId = intval($websiteId);
    if ($websiteId <= 0) {
        return null;
    }

    $sql = "SELECT Website, Name, Code FROM Website WHERE ID = ? LIMIT 1";
    $stmt = mysqli_prepare($conn, $sql);
    if (!$stmt) {
        return null;
    }
    mysqli_stmt_bind_param($stmt, 'i', $websiteId);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    $row = mysqli_fetch_assoc($result);
    mysqli_stmt_close($stmt);

    if (!$row) {
        return null;
    }

    // 优先 Name（如 ON9AUS），其次 Code，最后从 Website URL 提取 slug
    foreach (['Name', 'Code', 'Website'] as $field) {
        $value = isset($row[$field]) ? trim((string)$row[$field]) : '';
        if ($value !== '') {
            $slug = jkex_normalize_company_slug($value);
            if ($slug !== '') {
                return $slug;
            }
        }
    }

    return null;
}

function jkex_normalize_date_ymd($date)
{
    $formats = ['Y-m-d', 'Y-n-j', 'Y-m-j', 'Y-n-d'];
    foreach ($formats as $format) {
        $dateObj = DateTime::createFromFormat($format, $date);
        if ($dateObj) {
            return $dateObj->format('Y-m-d');
        }
    }
    throw new Exception('无效的日期格式: ' . $date);
}

function jkex_build_urls_from_range($startDate, $endDate, $companySlug, $banks, $apiKey, $baseUrl = JKEX_DEFAULT_BASE_URL, $dataTypes = null)
{
    $startDate = jkex_normalize_date_ymd($startDate);
    $endDate = jkex_normalize_date_ymd($endDate);

    $startTs = strtotime($startDate);
    $endTs = strtotime($endDate);
    if ($startTs === false || $endTs === false) {
        throw new Exception('无法解析开始或结束日期');
    }
    if ($startTs > $endTs) {
        throw new Exception('开始日期不能晚于结束日期');
    }

    $dayCount = intval(($endTs - $startTs) / 86400) + 1;
    if ($dayCount > 62) {
        throw new Exception('日期范围不能超过 62 天');
    }

    $companySlug = jkex_normalize_company_slug($companySlug);
    if ($companySlug === '') {
        throw new Exception('请提供 company slug');
    }

    $apiKey = trim((string)$apiKey);
    if ($apiKey === '') {
        throw new Exception('请先保存或填写 JKEX API Key');
    }

    if (!is_array($banks) || count($banks) === 0) {
        $banks = jkex_get_default_banks();
    }

    $normalizedBanks = [];
    foreach ($banks as $bank) {
        $bankName = strtoupper(trim(str_replace('+', ' ', (string)$bank)));
        if ($bankName === '') {
            continue;
        }
        jkex_map_bank_to_table_type($bankName);
        $normalizedBanks[] = $bankName;
    }

    if (count($normalizedBanks) === 0) {
        throw new Exception('请至少选择一个 Bank 类型');
    }

    if (!is_array($dataTypes) || count($dataTypes) === 0) {
        $dataTypes = ['normal'];
    }
    $normalizedDataTypes = [];
    foreach ($dataTypes as $dataType) {
        $normalizedDataTypes[] = jkex_normalize_data_type($dataType);
    }
    $normalizedDataTypes = array_values(array_unique($normalizedDataTypes));

    $baseUrl = rtrim(trim((string)$baseUrl), '/');
    if ($baseUrl === '') {
        $baseUrl = JKEX_DEFAULT_BASE_URL;
    }

    $urls = [];
    for ($ts = $startTs; $ts <= $endTs; $ts += 86400) {
        $dateYmd = date('Ymd', $ts);
        foreach ($normalizedBanks as $bankName) {
            foreach ($normalizedDataTypes as $dataType) {
                $urls[] = jkex_build_single_import_url(
                    $baseUrl,
                    $companySlug,
                    $bankName,
                    $dateYmd,
                    $apiKey,
                    $dataType
                );
            }
        }
    }

    return $urls;
}

function jkex_resolve_import_urls($conn, $data)
{
    $urls = [];
    if (!empty($data['urls']) && is_array($data['urls'])) {
        $urls = $data['urls'];
    } elseif (!empty($data['url'])) {
        $urls = preg_split('/\r\n|\r|\n/', (string)$data['url']);
    }

    $urls = array_values(array_filter(array_map('trim', $urls), function ($item) {
        return $item !== '';
    }));

    if (count($urls) > 0) {
        return $urls;
    }

    $mode = isset($data['mode']) ? trim((string)$data['mode']) : 'range';
    if ($mode !== 'range') {
        throw new Exception('请提供至少一个 JKEX 链接');
    }

    $startDate = $data['startDate'] ?? null;
    $endDate = $data['endDate'] ?? null;
    if (!$startDate || !$endDate) {
        throw new Exception('请选择开始与结束日期');
    }

    $companySlug = isset($data['companySlug']) ? jkex_normalize_company_slug($data['companySlug']) : '';
    if ($companySlug === '' && !empty($data['websiteId'])) {
        $companySlug = jkex_resolve_company_slug_for_website($conn, $data['websiteId']) ?: '';
    }
    if ($companySlug === '') {
        throw new Exception('无法识别 company slug，请检查所选 Website 或手动填写');
    }

    $apiKey = isset($data['apiKey']) ? trim((string)$data['apiKey']) : '';
    if ($apiKey === '' && !empty($data['websiteId'])) {
        require_once __DIR__ . '/jkex_master_inc.php';
        $apiKey = trim((string)jkex_master_get_api_key_for_website($conn, $data['websiteId']));
    }
    if ($apiKey === '') {
        throw new Exception('当前 Website 所属 JKEX Master 对账组尚未设置 API Key');
    }

    $baseUrl = isset($data['baseUrl']) ? trim((string)$data['baseUrl']) : '';
    if ($baseUrl === '') {
        $storedBaseUrl = trim((string)jkex_get_config_value($conn, JKEX_CONFIG_BASE_URL));
        $baseUrl = $storedBaseUrl !== '' ? $storedBaseUrl : JKEX_DEFAULT_BASE_URL;
    }

    $banks = isset($data['banks']) && is_array($data['banks']) ? $data['banks'] : jkex_get_default_banks();
    $dataTypes = isset($data['dataTypes']) && is_array($data['dataTypes']) ? $data['dataTypes'] : ['normal'];

    return jkex_build_urls_from_range($startDate, $endDate, $companySlug, $banks, $apiKey, $baseUrl, $dataTypes);
}

function jkex_parse_import_url($url)
{
    $url = trim((string)$url);
    if ($url === '') {
        throw new Exception('链接不能为空');
    }

    $parts = parse_url($url);
    if (!$parts || empty($parts['host'])) {
        throw new Exception('无效的链接格式');
    }

    $path = $parts['path'] ?? '';
    $companySlug = null;
    $bankName = null;
    $dateRaw = null;
    $dataType = 'normal';

    // /dailydata/exclude/{company}/{bank}/{cutoff|closing}/{YYYYMMDD}
    if (preg_match('#/dailydata/exclude/([^/]+)/([^/]+)/(cutoff|closing)/(\d{8})\/?$#i', $path, $matches)) {
        $companySlug = strtolower(urldecode($matches[1]));
        $bankName = strtoupper(trim(urldecode(str_replace('+', ' ', $matches[2]))));
        $dataType = strtolower($matches[3]);
        $dateRaw = $matches[4];
    } elseif (preg_match('#/dailydata/exclude/([^/]+)/(cutoff|closing)/(\d{8})\/?$#i', $path, $matches)) {
        // /dailydata/exclude/{bank}/{cutoff|closing}/{YYYYMMDD}
        $bankName = strtoupper(trim(urldecode(str_replace('+', ' ', $matches[1]))));
        $dataType = strtolower($matches[2]);
        $dateRaw = $matches[3];
    } elseif (preg_match('#/dailydata/exclude/([^/]+)/([^/]+)/(\d{8})\/?$#i', $path, $matches)) {
        $companySlug = strtolower(urldecode($matches[1]));
        $bankName = strtoupper(trim(urldecode(str_replace('+', ' ', $matches[2]))));
        $dateRaw = $matches[3];
    } elseif (preg_match('#/dailydata/exclude/([^/]+)/(\d{8})\/?$#i', $path, $matches)) {
        // 全局链接（如 EXPENSE）：/api/dailydata/exclude/Expense/{YYYYMMDD}
        $bankName = strtoupper(trim(urldecode(str_replace('+', ' ', $matches[1]))));
        $dateRaw = $matches[2];
    } else {
        throw new Exception('链接格式应为 /api/dailydata/exclude/{company}/{bank}/{YYYYMMDD}、/api/dailydata/exclude/{bank}/{cutoff|closing}/{YYYYMMDD} 等');
    }

    $dataType = jkex_normalize_data_type($dataType);

    $dateObj = DateTime::createFromFormat('Ymd', $dateRaw);
    if (!$dateObj) {
        throw new Exception('无法解析日期: ' . $dateRaw);
    }

    return [
        'url' => $url,
        'companySlug' => $companySlug,
        'bankName' => $bankName,
        'dataType' => $dataType,
        'date' => $dateObj->format('Y-m-d'),
    ];
}

function jkex_map_bank_to_table_type($bankName)
{
    $normalized = strtoupper(trim(str_replace('+', ' ', $bankName)));
    $map = [
        'INLINE' => 'Inline',
        'MC SELL' => 'Sell',
        'MC BUY' => 'Buy',
        'BANK BURN' => 'Bank Burn',
        'OTHER' => 'Other',
        'EXPENSE' => 'Expense',
    ];

    if (!isset($map[$normalized])) {
        throw new Exception('不支持的 Bank 类型: ' . $bankName);
    }

    return $map[$normalized];
}

function jkex_fetch_api_json($url)
{
    if (!function_exists('curl_init')) {
        throw new Exception('服务器未启用 cURL，无法抓取 JKEX 数据');
    }

    $ch = curl_init($url);
    curl_setopt_array($ch, [
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_FOLLOWLOCATION => true,
        CURLOPT_TIMEOUT => 30,
        CURLOPT_CONNECTTIMEOUT => 10,
        CURLOPT_SSL_VERIFYPEER => true,
        CURLOPT_HTTPHEADER => ['Accept: application/json'],
    ]);

    $body = curl_exec($ch);
    $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    $curlError = curl_error($ch);
    curl_close($ch);

    if ($body === false) {
        throw new Exception('抓取 JKEX 数据失败: ' . $curlError);
    }

    if ($httpCode < 200 || $httpCode >= 300) {
        throw new Exception('JKEX API 返回 HTTP ' . $httpCode);
    }

    $json = json_decode($body, true);
    if (!is_array($json)) {
        throw new Exception('JKEX API 返回的不是有效 JSON');
    }

    if (empty($json['ok'])) {
        $message = $json['message'] ?? $json['error'] ?? 'JKEX API 返回失败';
        throw new Exception((string)$message);
    }

    return $json;
}

function jkex_get_website_row_by_id($conn, $websiteId)
{
    $websiteId = intval($websiteId);
    if ($websiteId <= 0) {
        return null;
    }

    $sql = "SELECT ID, Name, Website, Code FROM Website WHERE ID = ? LIMIT 1";
    $stmt = mysqli_prepare($conn, $sql);
    if (!$stmt) {
        return null;
    }
    mysqli_stmt_bind_param($stmt, 'i', $websiteId);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    $row = mysqli_fetch_assoc($result);
    mysqli_stmt_close($stmt);

    return $row ?: null;
}

function jkex_collect_website_slugs($website)
{
    $slugs = [];
    if (!is_array($website)) {
        return $slugs;
    }
    foreach (['Name', 'Code', 'Website'] as $field) {
        $value = isset($website[$field]) ? trim((string)$website[$field]) : '';
        if ($value === '') {
            continue;
        }
        $slug = jkex_normalize_company_slug($value);
        if ($slug !== '' && !in_array($slug, $slugs, true)) {
            $slugs[] = $slug;
        }
    }
    return $slugs;
}

function jkex_resolve_website_by_slug($conn, $slug)
{
    $slug = strtolower(trim((string)$slug));
    if ($slug === '') {
        return null;
    }

    $sql = "SELECT ID, Name, Website, Code
            FROM Website
            WHERE LOWER(TRIM(Website)) = ?
               OR LOWER(TRIM(Name)) = ?
               OR LOWER(TRIM(Code)) = ?
            ORDER BY ID ASC
            LIMIT 1";
    $stmt = mysqli_prepare($conn, $sql);
    if (!$stmt) {
        return null;
    }

    mysqli_stmt_bind_param($stmt, 'sss', $slug, $slug, $slug);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    $row = mysqli_fetch_assoc($result);
    mysqli_stmt_close($stmt);

    return $row ?: null;
}

function jkex_resolve_inline_web_id($conn, $name)
{
    $name = trim((string)$name);
    if ($name === '') {
        return null;
    }

    $sql = "SELECT ID, Name FROM Website WHERE UPPER(TRIM(Name)) = UPPER(?) LIMIT 1";
    $stmt = mysqli_prepare($conn, $sql);
    if (!$stmt) {
        return null;
    }

    mysqli_stmt_bind_param($stmt, 's', $name);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    $row = mysqli_fetch_assoc($result);
    mysqli_stmt_close($stmt);

    return $row ? intval($row['ID']) : null;
}

function jkex_extract_rate_from_description($description)
{
    if ($description === null || $description === '') {
        return null;
    }

    $patterns = [
        '/RATE\s*:\s*([\d.]+)/i',
        '/RATE\s+([\d.]+)/i',
    ];

    foreach ($patterns as $pattern) {
        if (preg_match($pattern, $description, $matches)) {
            $rate = floatval($matches[1]);
            if ($rate > 0) {
                return $rate;
            }
        }
    }

    return null;
}

function jkex_extract_mc_name_from_description($description)
{
    if ($description === null || $description === '') {
        return null;
    }
    if (preg_match('/MCPGK[-\s]?(\d+)/i', $description, $matches)) {
        return 'MCPGK-' . str_pad($matches[1], 3, '0', STR_PAD_LEFT);
    }
    // MC-033 / MC 033 / MC033
    if (preg_match('/MC[-\s]*(\d+)/i', $description, $matches)) {
        return 'MC-' . str_pad($matches[1], 3, '0', STR_PAD_LEFT);
    }
    return null;
}

function jkex_get_or_create_mc_id($conn, $mcName)
{
    if ($mcName === null || trim($mcName) === '') {
        return null;
    }

    $mcName = trim($mcName);
    $checkSql = "SELECT ID FROM SplitMonthlyMC WHERE MCName = ? LIMIT 1";
    $checkStmt = mysqli_prepare($conn, $checkSql);
    if ($checkStmt) {
        mysqli_stmt_bind_param($checkStmt, 's', $mcName);
        mysqli_stmt_execute($checkStmt);
        $result = mysqli_stmt_get_result($checkStmt);
        $row = mysqli_fetch_assoc($result);
        mysqli_stmt_close($checkStmt);
        if ($row) {
            return intval($row['ID']);
        }
    }

    $insertSql = "INSERT INTO SplitMonthlyMC (MCName, Status) VALUES (?, 1)";
    $insertStmt = mysqli_prepare($conn, $insertSql);
    if (!$insertStmt) {
        return null;
    }
    mysqli_stmt_bind_param($insertStmt, 's', $mcName);
    if (!mysqli_stmt_execute($insertStmt)) {
        mysqli_stmt_close($insertStmt);
        return null;
    }
    $mcId = mysqli_insert_id($conn);
    mysqli_stmt_close($insertStmt);
    return $mcId > 0 ? intval($mcId) : null;
}

function jkex_get_inline_fix_rate($conn, $websiteId)
{
    $currency = null;
    $currencySql = "SELECT wg.Currency
                    FROM Website w
                    LEFT JOIN WebsiteGroup wg ON w.GroupID = wg.ID
                    WHERE w.ID = ?
                    LIMIT 1";
    $currencyStmt = mysqli_prepare($conn, $currencySql);
    if ($currencyStmt) {
        mysqli_stmt_bind_param($currencyStmt, 'i', $websiteId);
        mysqli_stmt_execute($currencyStmt);
        $currencyResult = mysqli_stmt_get_result($currencyStmt);
        if ($currencyRow = mysqli_fetch_assoc($currencyResult)) {
            $currency = $currencyRow['Currency'];
        }
        mysqli_stmt_close($currencyStmt);
    }

    $keys = [];
    if ($currency && trim($currency) !== '') {
        $keys[] = 'inline_fix_rate_' . strtoupper(trim($currency));
    }
    $keys[] = 'inline_fix_rate_AUD';
    $keys[] = 'inline_fix_rate';

    foreach ($keys as $configKey) {
        $configSql = "SELECT ConfigValue FROM SystemConfig WHERE ConfigKey = ? ORDER BY ID DESC LIMIT 1";
        $configStmt = mysqli_prepare($conn, $configSql);
        if (!$configStmt) {
            continue;
        }
        mysqli_stmt_bind_param($configStmt, 's', $configKey);
        mysqli_stmt_execute($configStmt);
        $configResult = mysqli_stmt_get_result($configStmt);
        if ($configRow = mysqli_fetch_assoc($configResult)) {
            $value = trim((string)$configRow['ConfigValue']);
            if ($value !== '' && floatval($value) > 0) {
                mysqli_stmt_close($configStmt);
                return floatval($value);
            }
        }
        mysqli_stmt_close($configStmt);
    }

    return 2.7;
}

function jkex_normalize_amount($value)
{
    if ($value === null || $value === '' || $value === '-') {
        return null;
    }
    $num = floatval($value);
    return abs($num) < 0.0000001 ? null : $num;
}

function jkex_find_existing_record_id($conn, $payload)
{
    $websiteId = intval($payload['WebsiteID']);
    $tableType = $payload['TableType'];
    $date = $payload['Date'];
    $dataType = $payload['DataType'] ?? 'normal';
    $description = $payload['DESCRIPTION'] ?? null;
    $time = $payload['TIME'] ?? null;

    if ($time !== null && $time !== '') {
        if ($description !== null && $description !== '') {
            $sql = "SELECT ID FROM DailyData
                    WHERE WebsiteID = ? AND TableType = ? AND `Date` = ? AND TIME = ? AND DataType = ? AND DESCRIPTION = ?
                    LIMIT 1";
            $stmt = mysqli_prepare($conn, $sql);
            if ($stmt) {
                mysqli_stmt_bind_param($stmt, 'isssss', $websiteId, $tableType, $date, $time, $dataType, $description);
                mysqli_stmt_execute($stmt);
                $result = mysqli_stmt_get_result($stmt);
                $row = mysqli_fetch_assoc($result);
                mysqli_stmt_close($stmt);
                if ($row) {
                    return intval($row['ID']);
                }
            }
        } else {
            $sql = "SELECT ID FROM DailyData
                    WHERE WebsiteID = ? AND TableType = ? AND `Date` = ? AND TIME = ? AND DataType = ? AND DESCRIPTION IS NULL
                    LIMIT 1";
            $stmt = mysqli_prepare($conn, $sql);
            if ($stmt) {
                mysqli_stmt_bind_param($stmt, 'issss', $websiteId, $tableType, $date, $time, $dataType);
                mysqli_stmt_execute($stmt);
                $result = mysqli_stmt_get_result($stmt);
                $row = mysqli_fetch_assoc($result);
                mysqli_stmt_close($stmt);
                if ($row) {
                    return intval($row['ID']);
                }
            }
        }
    } else {
        if ($description !== null && $description !== '') {
            $sql = "SELECT ID FROM DailyData
                    WHERE WebsiteID = ? AND TableType = ? AND `Date` = ? AND (TIME IS NULL OR TIME = '') AND DataType = ? AND DESCRIPTION = ?
                    LIMIT 1";
            $stmt = mysqli_prepare($conn, $sql);
            if ($stmt) {
                mysqli_stmt_bind_param($stmt, 'issss', $websiteId, $tableType, $date, $dataType, $description);
                mysqli_stmt_execute($stmt);
                $result = mysqli_stmt_get_result($stmt);
                $row = mysqli_fetch_assoc($result);
                mysqli_stmt_close($stmt);
                if ($row) {
                    return intval($row['ID']);
                }
            }
        }
    }

    return null;
}

function jkex_get_existing_scope_records($conn, $websiteId, $tableType, $date, $dataType)
{
    $sql = "SELECT dd.ID, dd.DESCRIPTION, dd.`IN`, dd.`OUT`, dd.TIME, dd.ID_Field,
                   dd.`MATCH`, dd.FEE, dd.REMARKS, dd.RATE, dd.MYR_TOTAL, mc.MCName AS MC
            FROM DailyData dd
            LEFT JOIN SplitMonthlyMC mc ON mc.ID = dd.MCID
            WHERE dd.WebsiteID = ? AND dd.TableType = ? AND dd.`Date` = ? AND dd.DataType = ?
            ORDER BY COALESCE(dd.TIME, ''), dd.ID";
    $stmt = mysqli_prepare($conn, $sql);
    if (!$stmt) throw new Exception('读取覆盖范围旧资料失败: ' . mysqli_error($conn));
    mysqli_stmt_bind_param($stmt, 'isss', $websiteId, $tableType, $date, $dataType);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    $rows = [];
    while ($row = mysqli_fetch_assoc($result)) {
        $row['WebsiteID'] = $websiteId;
        $row['TableType'] = $tableType;
        $row['Date'] = $date;
        $row['DataType'] = $dataType;
        $rows[] = $row;
    }
    mysqli_stmt_close($stmt);
    return $rows;
}

function jkex_delete_import_scope($conn, $scope)
{
    $websiteId = intval($scope['WebsiteID'] ?? 0);
    $tableType = trim((string)($scope['TableType'] ?? ''));
    $date = trim((string)($scope['Date'] ?? ''));
    $dataType = jkex_normalize_data_type($scope['DataType'] ?? 'normal');
    $allowedTableTypes = ['Inline', 'Sell', 'Buy', 'Bank Burn', 'Other', 'Expense'];
    if ($websiteId <= 0 || !in_array($tableType, $allowedTableTypes, true) ||
        !preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) {
        throw new Exception('无效的覆盖删除范围');
    }
    $sql = "DELETE FROM DailyData WHERE WebsiteID = ? AND TableType = ? AND `Date` = ? AND DataType = ?";
    $stmt = mysqli_prepare($conn, $sql);
    if (!$stmt) throw new Exception('准备覆盖删除失败: ' . mysqli_error($conn));
    mysqli_stmt_bind_param($stmt, 'isss', $websiteId, $tableType, $date, $dataType);
    if (!mysqli_stmt_execute($stmt)) {
        $message = mysqli_stmt_error($stmt);
        mysqli_stmt_close($stmt);
        throw new Exception('覆盖删除失败: ' . $message);
    }
    $deleted = mysqli_stmt_affected_rows($stmt);
    mysqli_stmt_close($stmt);
    return $deleted;
}

function jkex_build_import_payloads($conn, $apiData, $parsed, $expectedWebsiteId = null)
{
    $tableType = jkex_map_bank_to_table_type($parsed['bankName']);
    $importDataType = jkex_normalize_data_type($parsed['dataType'] ?? 'normal');

    $website = null;
    $companyFilterSlugs = null;
    $websiteWarning = null;

    if ($parsed['companySlug'] !== null && $parsed['companySlug'] !== '') {
        $website = jkex_resolve_website_by_slug($conn, $parsed['companySlug']);
        if (!$website) {
            throw new Exception('找不到对应 Website: ' . $parsed['companySlug']);
        }
        $websiteId = intval($website['ID']);
        if ($expectedWebsiteId !== null && intval($expectedWebsiteId) !== $websiteId) {
            $websiteWarning = '链接对应 Website 为 ' . ($website['Name'] ?? $parsed['companySlug']) . '，与当前页面所选 Website 不一致';
        }
    } else {
        // 全局链接（如 EXPENSE）：返回所有公司记录，需按当前所选 Website 过滤
        if ($expectedWebsiteId === null || intval($expectedWebsiteId) <= 0) {
            throw new Exception($parsed['bankName'] . ' 链接不含 company，请先在页面选择 Website');
        }
        $website = jkex_get_website_row_by_id($conn, $expectedWebsiteId);
        if (!$website) {
            throw new Exception('找不到当前所选 Website (ID: ' . intval($expectedWebsiteId) . ')');
        }
        $websiteId = intval($website['ID']);
        $companyFilterSlugs = jkex_collect_website_slugs($website);
        if (count($companyFilterSlugs) === 0) {
            throw new Exception('无法识别当前 Website 的 company slug，无法过滤 ' . $parsed['bankName'] . ' 记录');
        }
    }

    $records = $apiData['records'] ?? [];
    if (!is_array($records)) {
        $records = [];
    }

    $inlineFixRate = $tableType === 'Inline' ? jkex_get_inline_fix_rate($conn, $websiteId) : null;
    $mappedRecords = [];
    $counts = ['new' => 0, 'exists' => 0, 'skip' => 0];
    $existingScopeRecords = jkex_get_existing_scope_records(
        $conn,
        $websiteId,
        $tableType,
        $parsed['date'],
        $importDataType
    );

    foreach ($records as $index => $record) {
        if (!is_array($record)) {
            continue;
        }

        // 全局链接：仅导入属于当前所选 Website 的公司记录
        if ($companyFilterSlugs !== null) {
            $recordCompany = jkex_normalize_company_slug($record['company'] ?? '');
            if ($recordCompany === '' || !in_array($recordCompany, $companyFilterSlugs, true)) {
                $counts['skip']++;
                continue;
            }
        }

        $in = jkex_normalize_amount($record['amount_in'] ?? null);
        $out = jkex_normalize_amount($record['amount_out'] ?? null);
        if ($in === null && $out === null) {
            $counts['skip']++;
            continue;
        }

        $description = isset($record['description']) && trim((string)$record['description']) !== ''
            ? trim((string)$record['description'])
            : null;
        $time = isset($record['record_time']) && trim((string)$record['record_time']) !== ''
            ? trim((string)$record['record_time'])
            : null;
        $fee = jkex_normalize_amount($record['fee'] ?? null);
        $remarks = isset($record['remarks']) && trim((string)$record['remarks']) !== ''
            ? trim((string)$record['remarks'])
            : null;

        $idField = null;
        if (isset($record['id']) && $record['id'] !== '' && $record['id'] !== null) {
            $idField = (string)$record['id'];
        } elseif (isset($record['external_id']) && $record['external_id'] !== '' && $record['external_id'] !== '-' && $record['external_id'] !== null) {
            $idField = (string)$record['external_id'];
        }

        $payload = [
            'WebsiteID' => $websiteId,
            'TableType' => $tableType,
            'Date' => $parsed['date'],
            'DataType' => $importDataType,
            'DESCRIPTION' => $description,
            'IN' => $in,
            'OUT' => $out,
            'TIME' => $time,
            'ID' => $idField,
            'MATCH' => null,
            'FEE' => $fee,
            'REMARKS' => $remarks,
            'MCID' => null,
            'MC' => null,
            'InlineWebID' => null,
            'RATE' => null,
            'MYR_TOTAL' => null,
        ];

        if ($tableType === 'Inline') {
            $inlineName = $description;
            if ($inlineName === null && isset($record['company'])) {
                $inlineName = trim((string)$record['company']);
            }
            $payload['InlineWebID'] = jkex_resolve_inline_web_id($conn, $inlineName);
            $payload['RATE'] = $inlineFixRate;
            $baseAmount = $in !== null ? $in : $out;
            if ($baseAmount !== null && $inlineFixRate !== null) {
                $payload['MYR_TOTAL'] = $baseAmount * $inlineFixRate;
            }
        } elseif ($tableType === 'Buy' || $tableType === 'Sell') {
            $rate = jkex_extract_rate_from_description($description);
            $payload['RATE'] = $rate;
            $baseAmount = $in !== null ? $in : $out;
            if ($baseAmount !== null && $rate !== null) {
                $payload['MYR_TOTAL'] = $baseAmount * $rate;
            }
            $mcName = jkex_extract_mc_name_from_description($description);
            $provider = jkbo_collection_extract_provider($description, $conn, $tableType);
            if ($provider !== null) {
                $collectionSettings = jkbo_collection_get_website_settings($conn, $websiteId);
                $collectionFields = jkbo_collection_apply_buy_sell_fields(
                    $description,
                    $collectionSettings,
                    null,
                    null,
                    $conn,
                    $tableType
                );
                $mcName = $collectionFields['mcName'];
            }
            if ($mcName !== null) {
                $payload['MC'] = $mcName;
                $payload['MCID'] = jkex_get_or_create_mc_id($conn, $mcName);
            }
        }

        $existingId = jkex_find_existing_record_id($conn, $payload);
        $status = $existingId ? 'exists' : 'new';
        $counts[$status]++;

        $mappedRecords[] = [
            'index' => $index,
            'status' => $status,
            'existingId' => $existingId,
            'payload' => $payload,
            'preview' => [
                'DESCRIPTION' => $description,
                'IN' => $in,
                'OUT' => $out,
                'TIME' => $time,
                'ID_Field' => $idField,
                'FEE' => $fee,
                'REMARKS' => $remarks,
                'RATE' => $payload['RATE'],
                'MYR_TOTAL' => $payload['MYR_TOTAL'],
                'MC' => $payload['MC'],
                'InlineWebID' => $payload['InlineWebID'],
            ],
        ];
    }

    return [
        'meta' => [
            'url' => $parsed['url'],
            'companySlug' => $parsed['companySlug'] !== null && $parsed['companySlug'] !== ''
                ? $parsed['companySlug']
                : ($companyFilterSlugs[0] ?? null),
            'bankName' => $parsed['bankName'],
            'dataType' => $importDataType,
            'tableType' => $tableType,
            'date' => $parsed['date'],
            'websiteId' => $websiteId,
            'websiteName' => $website['Name'] ?? null,
            'websiteWarning' => $websiteWarning,
            'summary' => $apiData['summary'] ?? null,
            'apiCount' => count($records),
        ],
        'records' => $mappedRecords,
        'counts' => $counts,
        'replacementScope' => [
            'WebsiteID' => $websiteId,
            'TableType' => $tableType,
            'Date' => $parsed['date'],
            'DataType' => $importDataType,
        ],
        'existingScopeRecords' => $existingScopeRecords,
    ];
}

function jkex_sql_value($conn, $value, $type = 'string')
{
    if ($value === null || $value === '') {
        return 'NULL';
    }
    if ($type === 'int') {
        return (string)intval($value);
    }
    if ($type === 'float') {
        return (string)floatval($value);
    }
    return "'" . mysqli_real_escape_string($conn, (string)$value) . "'";
}

function jkex_patch_existing_import_record($conn, $existingId, $payload)
{
    $fields = [];
    $tableType = $payload['TableType'] ?? '';

    if (array_key_exists('RATE', $payload) && $payload['RATE'] !== null) {
        $fields[] = 'RATE = ' . jkex_sql_value($conn, $payload['RATE'], 'float');
    }
    if (array_key_exists('MYR_TOTAL', $payload) && $payload['MYR_TOTAL'] !== null) {
        $fields[] = 'MYR_TOTAL = ' . jkex_sql_value($conn, $payload['MYR_TOTAL'], 'float');
    }
    if (array_key_exists('MCID', $payload) && $payload['MCID'] !== null) {
        $fields[] = 'MCID = ' . jkex_sql_value($conn, $payload['MCID'], 'int');
    }
    if (($tableType === 'Buy' || $tableType === 'Sell') && !empty($payload['DESCRIPTION'])) {
        $fields[] = 'DESCRIPTION = ' . jkex_sql_value($conn, $payload['DESCRIPTION']);
    }
    if (array_key_exists('ID', $payload) && $payload['ID'] !== null && $payload['ID'] !== '') {
        $fields[] = 'ID_Field = ' . jkex_sql_value($conn, $payload['ID']);
    }
    if (array_key_exists('REMARKS', $payload) && $payload['REMARKS'] !== null && $payload['REMARKS'] !== '') {
        $fields[] = 'REMARKS = ' . jkex_sql_value($conn, $payload['REMARKS']);
    }
    if (array_key_exists('FEE', $payload) && $payload['FEE'] !== null) {
        $fields[] = 'FEE = ' . jkex_sql_value($conn, $payload['FEE'], 'float');
    }
    if (array_key_exists('TIME', $payload) && $payload['TIME'] !== null && $payload['TIME'] !== '') {
        $fields[] = 'TIME = ' . jkex_sql_value($conn, $payload['TIME']);
    }

    if (count($fields) === 0) {
        return ['action' => 'skipped', 'id' => $existingId];
    }

    $fields[] = 'Updated_at = CURRENT_TIMESTAMP';
    $sql = 'UPDATE DailyData SET ' . implode(', ', $fields) . ' WHERE ID = ' . intval($existingId);
    if (!mysqli_query($conn, $sql)) {
        throw new Exception('补写失败: ' . mysqli_error($conn));
    }

    return ['action' => 'patched', 'id' => $existingId];
}

function jkex_import_payload($conn, $payload, $overwriteExisting = false)
{
    $existingId = jkex_find_existing_record_id($conn, $payload);
    if ($existingId && !$overwriteExisting) {
        return jkex_patch_existing_import_record($conn, $existingId, $payload);
    }

    $websiteId = intval($payload['WebsiteID']);
    $tableType = $payload['TableType'];
    $date = $payload['Date'];
    $dataType = $payload['DataType'] ?? 'normal';
    $description = $payload['DESCRIPTION'] ?? null;
    $in = $payload['IN'] ?? null;
    $out = $payload['OUT'] ?? null;
    $time = $payload['TIME'] ?? null;
    $idField = $payload['ID'] ?? null;
    $match = $payload['MATCH'] ?? null;
    $fee = $payload['FEE'] ?? null;
    $remarks = $payload['REMARKS'] ?? null;
    $rate = $payload['RATE'] ?? null;
    $myrTotal = $payload['MYR_TOTAL'] ?? null;
    $mcId = $payload['MCID'] ?? null;
    $inlineWebId = $payload['InlineWebID'] ?? null;

    if ($existingId) {
        $sql = "UPDATE DailyData SET
                    DESCRIPTION = " . jkex_sql_value($conn, $description) . ",
                    MCID = " . jkex_sql_value($conn, $mcId, 'int') . ",
                    InlineWebID = " . jkex_sql_value($conn, $inlineWebId, 'int') . ",
                    `IN` = " . jkex_sql_value($conn, $in, 'float') . ",
                    `OUT` = " . jkex_sql_value($conn, $out, 'float') . ",
                    TIME = " . jkex_sql_value($conn, $time) . ",
                    ID_Field = " . jkex_sql_value($conn, $idField) . ",
                    `MATCH` = " . jkex_sql_value($conn, $match) . ",
                    FEE = " . jkex_sql_value($conn, $fee, 'float') . ",
                    REMARKS = " . jkex_sql_value($conn, $remarks) . ",
                    RATE = " . jkex_sql_value($conn, $rate, 'float') . ",
                    MYR_TOTAL = " . jkex_sql_value($conn, $myrTotal, 'float') . ",
                    Updated_at = CURRENT_TIMESTAMP
                WHERE ID = " . intval($existingId);
        if (!mysqli_query($conn, $sql)) {
            throw new Exception('更新失败: ' . mysqli_error($conn));
        }
        return ['action' => 'updated', 'id' => $existingId];
    }

    $sql = "INSERT INTO DailyData
            (WebsiteID, TableType, `Date`, DESCRIPTION, MCID, InlineWebID, `IN`, `OUT`, TIME, ID_Field, `MATCH`, FEE, REMARKS, RATE, MYR_TOTAL, DataType)
            VALUES (
                " . intval($websiteId) . ",
                " . jkex_sql_value($conn, $tableType) . ",
                " . jkex_sql_value($conn, $date) . ",
                " . jkex_sql_value($conn, $description) . ",
                " . jkex_sql_value($conn, $mcId, 'int') . ",
                " . jkex_sql_value($conn, $inlineWebId, 'int') . ",
                " . jkex_sql_value($conn, $in, 'float') . ",
                " . jkex_sql_value($conn, $out, 'float') . ",
                " . jkex_sql_value($conn, $time) . ",
                " . jkex_sql_value($conn, $idField) . ",
                " . jkex_sql_value($conn, $match) . ",
                " . jkex_sql_value($conn, $fee, 'float') . ",
                " . jkex_sql_value($conn, $remarks) . ",
                " . jkex_sql_value($conn, $rate, 'float') . ",
                " . jkex_sql_value($conn, $myrTotal, 'float') . ",
                " . jkex_sql_value($conn, $dataType) . "
            )";
    if (!mysqli_query($conn, $sql)) {
        throw new Exception('插入失败: ' . mysqli_error($conn));
    }
    return ['action' => 'inserted', 'id' => mysqli_insert_id($conn)];
}