<?php
require_once 'config.php';
require_once 'image_helpers.php';
header('Content-Type: application/json');
$action = $_REQUEST['action'] ?? '';

if ($action === 'login') {
    $username = trim($_POST['username'] ?? '');
    $password = trim($_POST['password'] ?? '');
    $ip = $_SERVER['REMOTE_ADDR'] ?? '0.0.0.0';

    // Brute-force throttle: 5 failed attempts from the same IP within 15
    // minutes blocks further attempts from it until the window passes -
    // previously there was no limit at all (unlimited password guessing).
    // A login_attempts table already existed in this DB from an earlier,
    // never-wired-in attempt at this same feature - reusing its actual
    // schema (attempt_time, not attempted_at) rather than assuming one.
    $pdo->exec("CREATE TABLE IF NOT EXISTS login_attempts (
        id INT AUTO_INCREMENT PRIMARY KEY,
        ip_address VARCHAR(45) NOT NULL,
        username VARCHAR(50) NOT NULL,
        success TINYINT(1) DEFAULT 0,
        attempt_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX (ip_address, attempt_time)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $countStmt = $pdo->prepare("SELECT COUNT(*) FROM login_attempts WHERE ip_address = ? AND success = 0 AND attempt_time > (NOW() - INTERVAL 15 MINUTE)");
    $countStmt->execute([$ip]);
    if ($countStmt->fetchColumn() >= 5) {
        echo json_encode(['status' => 'error', 'message' => '登入嘗試次數過多，請 15 分鐘後再試']);
        exit;
    }

    $stmt = $pdo->prepare("SELECT * FROM users WHERE username = ?");
    $stmt->execute([$username]);
    $u = $stmt->fetch();
    $loginOk = $u && password_verify($password, $u['password_hash']);

    $pdo->prepare("INSERT INTO login_attempts (ip_address, username, success) VALUES (?, ?, ?)")
        ->execute([$ip, $username, $loginOk ? 1 : 0]);

    if ($loginOk) {
        // Regenerated so a session ID that existed before login (e.g. one
        // an attacker could have planted) can't be reused to hijack the
        // now-authenticated session - the ID changes at the privilege
        // boundary instead of staying fixed across it.
        session_regenerate_id(true);
        $_SESSION['user'] = ['id' => $u['id'], 'username' => $u['username'], 'role' => $u['role']];
        echo json_encode(['status' => 'success', 'message' => '登入成功']);
    } else {
        echo json_encode(['status' => 'error', 'message' => '帳號或密碼錯誤']);
    }
    exit;
}

if ($action === 'logout') {
    unset($_SESSION['user']);
    session_destroy();
    echo json_encode(['status' => 'success', 'message' => '已登出']);
    exit;
}

$currentUser = $_SESSION['user'] ?? null;
if (!$currentUser) {
    echo json_encode(['status' => 'error', 'message' => '請先登入系統']);
    exit;
}

function validateCSRF() {
    $token = $_SERVER['HTTP_X_CSRF_TOKEN'] ?? $_POST['csrf_token'] ?? '';
    if (!hash_equals($_SESSION['csrf_token'] ?? '', $token)) { 
        echo json_encode(['status' => 'error', 'message' => 'CSRF 驗證失敗']); 
        exit; 
    }
}


function requireManagerOrAdmin($user) {
    $role = $user['role'] ?? 'ROLE_VIEWER';
    if ($role !== 'ROLE_ADMIN' && $role !== 'ROLE_MANAGER') {
        echo json_encode(['status' => 'error', 'message' => '權限不足 (需要管理員或經辦人身分)']);
        exit;
    }
}

function requireAdmin($user) { 
    if (($user['role'] ?? '') !== 'ROLE_ADMIN') { 
        echo json_encode(['status' => 'error', 'message' => '權限不足 (需要管理員)']); 
        exit; 
    } 
}

function triggerDaemonIfIdle() {
    // Explicit processor pick (settings.json's active_processor, set from
    // the API 金鑰設定 modal): when the user has designated their PC as the
    // one that should process bills, the NAS must NOT also auto-launch its
    // own daemon on every upload - that's exactly the race this setting
    // exists to remove. "nas" or unset (the default) keeps launching here
    // as before; bill_ai_daemon_pc.py makes the same check in reverse.
    $settingsFile = '/volume1/web/nan/settings.json';
    $settings = file_exists($settingsFile) ? json_decode(file_get_contents($settingsFile), true) : [];
    if (($settings['active_processor'] ?? '') === 'pc') return;

    $debugLog = '/volume1/web/nan/php_trigger_debug.log';
    $timestamp = date('Y-m-d H:i:s');

    // NOTE: deliberately no "is it already running" pre-check here. A ps-aux
    // based check was tried and reverted - a killed process can briefly show
    // as a zombie/defunct entry, which made this silently refuse to start a
    // new daemon at all (no error, nothing in the UI - just nothing
    // happening). Python's flock() in ensure_single_instance() is the real
    // single-instance guarantee and doesn't have that failure mode; a
    // redundant launch attempt here just exits immediately and costs almost
    // nothing.
    $pyBin = file_exists('/usr/local/bin/python3') ? '/usr/local/bin/python3' : '/usr/bin/python3';
    $cmd = "/bin/sh -c 'cd /volume1/web/nan && {$pyBin} -u bill_ai_daemon.py >> daemon.log 2>&1 &'";
    file_put_contents($debugLog, "[$timestamp] Executing DSM6 trigger: $cmd\n", FILE_APPEND);
    $handle = popen($cmd, "r");
    if ($handle !== false) {
        pclose($handle);
        file_put_contents($debugLog, "[$timestamp] DSM6 popen process launched successfully.\n", FILE_APPEND);
    } else {
        file_put_contents($debugLog, "[$timestamp] ERROR: popen failed.\n", FILE_APPEND);
    }
}

// The following used to run before the login check above, so anyone who
// could reach api.php - no account needed - could trigger daemon runs or
// Excel export and burn this NAS's limited CPU. Moved below the auth gate;
// behavior for logged-in users is unchanged.
// (sync_cloud_invoices/載具同步 action removed entirely - no MOF App ID
// available for individual accounts. mof_sync.py itself is left in place,
// unreferenced, same as this project's other retired one-off scripts.)

if ($action === 'get_docs') {
    $type = $_GET['doc'] ?? 'changelog';
    $filePath = ($type === 'gemini') ? '/volume1/web/nan/gemini.md' : '/volume1/web/nan/changelog.md';
    if (file_exists($filePath)) {
        echo json_encode(['status' => 'success', 'content' => file_get_contents($filePath)]);
    } else {
        echo json_encode(['status' => 'error', 'message' => '文件不存在: ' . basename($filePath)]);
    }
    exit;
}

if ($action === 'rescan_failed') {
    validateCSRF(); requireManagerOrAdmin($currentUser);
    $stmt = $pdo->prepare("UPDATE bills SET raw_ocr_text = NULL WHERE total_amount = 0 OR raw_ocr_text = 'Failed'");
    $stmt->execute();
    triggerDaemonIfIdle();
    echo json_encode(['status' => 'success']);
    exit;
}

// Re-derives due_date/invoice_date for every already-processed bill from its
// ALREADY-STORED OCR text - no fresh OCR.Space call, useful right after
// fixing a date-parsing bug (like the invoice-period-vs-real-date one)
// without burning new API calls or waiting on the daemon queue.
if ($action === 'rescan_dates') {
    validateCSRF(); requireManagerOrAdmin($currentUser);
    $pyBin = file_exists('/usr/local/bin/python3') ? '/usr/local/bin/python3' : '/usr/bin/python3';
    $out = shell_exec("cd /volume1/web/nan && {$pyBin} bill_ai_daemon.py --rescan-dates 2>&1");
    echo json_encode(['status' => 'success', 'message' => '日期重新解析完成，詳情請查看日誌', 'log' => $out]);
    exit;
}

if ($action === 'reformat_fields') {
    validateCSRF(); requireManagerOrAdmin($currentUser);
    $pyBin = file_exists('/usr/local/bin/python3') ? '/usr/local/bin/python3' : '/usr/bin/python3';
    $out = shell_exec("cd /volume1/web/nan && {$pyBin} bill_ai_daemon.py --reformat-fields 2>&1");
    echo json_encode(['status' => 'success', 'message' => 'OCR 欄位重新整理完成，詳情請查看日誌', 'log' => $out]);
    exit;
}

if ($action === 'export_excel') {
    validateCSRF();
    $pyBin = file_exists('/usr/local/bin/python3') ? '/usr/local/bin/python3' : '/usr/bin/python3';
    shell_exec("{$pyBin} /volume1/web/nan/export_excel.py");
    echo json_encode(['status' => 'success', 'url' => 'uploads/bills_export.xlsx']);
    exit;
}

if ($action === 'sync_lottery_xml') {
    $pyBin = file_exists('/usr/local/bin/python3') ? '/usr/local/bin/python3' : '/usr/bin/python3';
    shell_exec("{$pyBin} /volume1/web/nan/sync_lottery.py");
    echo json_encode(['status' => 'success']);
    exit;
}

// Serves the client installer pair generated by Build-ClientInstaller.ps1
// (run on the admin's own PC, which also uploads the output here) - those
// files are pre-configured for the shared, heavily-restricted dbtunnel
// account, so this download stays admin-only and outside the normal
// uploads/ tree the rest of the app serves from.
if ($action === 'download_installer') {
    requireAdmin($currentUser);
    $which = ($_GET['file'] ?? '') === 'bat' ? 'Setup-BillDaemon.bat' : 'Setup-BillDaemon.ps1';
    $path = '/volume1/web/nan/dist/' . $which;
    if (!file_exists($path)) {
        http_response_code(404);
        echo '找不到安裝檔，請先在管理員的電腦上執行 Build-ClientInstaller.ps1。';
        exit;
    }
    header('Content-Type: application/octet-stream');
    header('Content-Disposition: attachment; filename="' . $which . '"');
    header('Content-Length: ' . filesize($path));
    readfile($path);
    exit;
}

if ($action === 'get_settings') {
    requireAdmin($currentUser);
    $file = '/volume1/web/nan/settings.json';
    $data = file_exists($file) ? json_decode(file_get_contents($file), true) : [];
    echo json_encode(['status' => 'success', 'settings' => $data]);
    exit;
}

if ($action === 'save_settings') {
    validateCSRF(); requireAdmin($currentUser);
    $file = '/volume1/web/nan/settings.json';
    $existing = file_exists($file) ? json_decode(file_get_contents($file), true) : [];
    $existing['ocrspace_api_key'] = trim($_POST['ocrspace_api_key'] ?? '');
    $existing['gemini_api_key'] = trim($_POST['gemini_api_key'] ?? '');
    $existing['anthropic_api_key'] = trim($_POST['anthropic_api_key'] ?? '');
    // mof_barcode/mof_password/mof_app_id no longer have UI fields (載具同步 hidden
    // until an individual MOF App ID is available) - preserve whatever's already
    // stored instead of wiping them on every save.
    if (isset($_POST['mof_barcode'])) $existing['mof_barcode'] = trim($_POST['mof_barcode']);
    if (isset($_POST['mof_password'])) $existing['mof_password'] = trim($_POST['mof_password']);
    if (isset($_POST['mof_app_id'])) $existing['mof_app_id'] = trim($_POST['mof_app_id']);
    // 'nas' (default/unset), 'pc', or 'auto' (explicitly unrestricted - both
    // may process, first one there wins, same as before this setting existed)
    $existing['active_processor'] = trim($_POST['active_processor'] ?? '');
    file_put_contents($file, json_encode($existing, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE));
    chmod($file, 0777);
    echo json_encode(['status' => 'success', 'message' => 'API 金鑰設定已儲存']);
    exit;
}

if ($action === 'check_daemon_status') {
    $psOutput = trim(shell_exec("/bin/ps aux | grep [b]ill_ai_daemon.py 2>/dev/null") ?? '');
    $isRunning = !empty($psOutput);
    $pid = '';
    if ($isRunning) {
        $parts = preg_split('/\s+/', $psOutput);
        $pid = $parts[1] ?? 'Active';
    }
    $stmt = $pdo->query("SELECT MAX(created_at) as last_run FROM bills WHERE raw_ocr_text LIKE 'Extracted%'");
    $lastRun = $stmt->fetch()['last_run'] ?? '尚無資料';
    $pendingStmt = $pdo->query("SELECT COUNT(*) as pending_cnt FROM bills WHERE raw_ocr_text IS NULL OR raw_ocr_text = 'Processing'");
    $pendingCount = intval($pendingStmt->fetch()['pending_cnt'] ?? 0);

    // Surface any not-yet-announced duplicate detections as a one-time notice for the dashboard toast.
    $newDuplicates = [];
    $dupStmt = $pdo->query("SELECT id, title, duplicate_of_id FROM bills WHERE is_duplicate = 1 AND duplicate_notified = 0");
    $dupRows = $dupStmt->fetchAll();
    if ($dupRows) {
        $newDuplicates = $dupRows;
        $ids = array_column($dupRows, 'id');
        $in = implode(',', array_map('intval', $ids));
        $pdo->exec("UPDATE bills SET duplicate_notified = 1 WHERE id IN ($in)");
    }

    echo json_encode(['status' => 'success', 'is_running' => $isRunning, 'pid' => $pid, 'last_extraction' => $lastRun, 'pending_count' => $pendingCount, 'new_duplicates' => $newDuplicates]);
    exit;
}

if ($action === 'start_daemon') {
    validateCSRF(); requireAdmin($currentUser);
    triggerDaemonIfIdle();
    echo json_encode(['status' => 'success', 'message' => 'OCR 背景啟動指令已送出']);
    exit;
}

if ($action === 'stop_daemon') {
    validateCSRF(); requireAdmin($currentUser);
    shell_exec("/usr/bin/pkill -9 -f bill_ai_daemon.py 2>/dev/null; /usr/bin/pkill -9 -f curl 2>/dev/null");
    @unlink('/volume1/web/nan/daemon.lock');
    $pdo->exec("UPDATE bills SET raw_ocr_text = NULL WHERE raw_ocr_text = 'Processing';");
    echo json_encode(['status' => 'success', 'message' => '已強制終止 OCR 背景進程，並即時釋放 NAS 伺服器資源！']);
    exit;
}

if ($action === 'get_daemon_logs') {
    requireAdmin($currentUser);
    $logFile = '/volume1/web/nan/daemon.log';
    if (file_exists($logFile)) {
        $lines = shell_exec("tail -n 35 " . escapeshellarg($logFile));
        echo json_encode(['status' => 'success', 'logs' => $lines ?: '日誌內容為空']);
    } else {
        echo json_encode(['status' => 'error', 'message' => '找不到 daemon.log']);
    }
    exit;
}

if ($action === 'upload_bill') {
    validateCSRF();
    if (!isset($_FILES['bill_image'])) { echo json_encode(['status' => 'error', 'message' => '未收到檔案']); exit; }
    $file = $_FILES['bill_image'];
    requireManagerOrAdmin($currentUser);

    // TIER 1: Pre-OCR SHA-256 Duplicate Check
    $fileHash = hash_file('sha256', $file['tmp_name']);
    if ($fileHash) {
        $dupStmt = $pdo->prepare("SELECT id, title FROM bills WHERE image_hash = ? LIMIT 1");
        $dupStmt->execute([$fileHash]);
        $existing = $dupStmt->fetch();
        if ($existing) {
            echo json_encode([
                'status' => 'error',
                'message' => "重複上傳：此照片與單據 #{$existing['id']} ({$existing['title']}) 完全相同，系統已自動拒絕重複上傳。"
            ]);
            exit;
        }
    } $cat = $_POST['category'] ?? 'General'; $title = "處理中 (" . ucfirst($cat) . ")";
    if (!is_dir('uploads/')) mkdir('uploads/', 0755, true);
    $target = 'uploads/' . time() . '_' . rand(100,999) . '_' . preg_replace('/[^a-zA-Z0-9_\.-]/', '', basename($file['name']));

    // QR payload decoded client-side (browser, jsQR) - some documents (e.g.
    // 臺北市 street-parking notices) never print the actual amount due, only
    // a QR code to a payment portal that calculates it. Re-validated here
    // (don't trust the client) - only accepted if it's actually a URL.
    $qrUrl = trim($_POST['qr_url'] ?? '');
    if ($qrUrl !== '' && !filter_var($qrUrl, FILTER_VALIDATE_URL)) $qrUrl = '';

    // Width/quality raised to match the client-side compression target
    // (1800px) - uploads already arrive pre-compressed from the browser, so
    // this now mostly acts as a safety net (a client that couldn't compress
    // - old browser, HEIC failure, etc.) and a format normalizer, rather
    // than a second lossy re-compression pass stacked on top of the first.
    if (compressAndResizeImage($file['tmp_name'], $target, 1800, 85)) {
        generateWebpDisplayCopy($target);
        $pdo->prepare("INSERT INTO bills (category, title, billing_month, due_date, total_amount, image_path, details_json, image_hash, qr_payment_url) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)")
            ->execute([$cat, $title, date('Y-m'), date('Y-m-d'), 0, $target, json_encode(['Status' => 'Pending OCR Processing']), $fileHash, $qrUrl ?: null]);
        triggerDaemonIfIdle();
        echo json_encode(['status' => 'success', 'message' => '上傳與壓縮成功']);
    } else {
        echo json_encode(['status' => 'error', 'message' => '檔案儲存失敗']);
    }
    exit;
}

if ($action === 'update_bill_details') {
    validateCSRF();
    $bill_id = intval($_POST['bill_id'] ?? 0);
    if ($bill_id <= 0) {
        echo json_encode(['status' => 'error', 'message' => '無效的單據 ID']);
        exit;
    }
    // An empty date-picker value arrives as '' (the field is present, just
    // blank), not missing - so `?? date('Y-m-d')` never catches it and a bad
    // empty string gets bound straight to the DATE column. That's expected
    // now that due_date is legitimately NULL for many bills (receipts
    // especially) instead of always holding a fake placeholder.
    $dueDateInput = trim($_POST['due_date'] ?? '');
    $dueDate = $dueDateInput !== '' ? $dueDateInput : null;
    try {
        $stmt = $pdo->prepare("UPDATE bills SET title=?, total_amount=?, invoice_number=?, due_date=?, payment_status=?, payment_method=? WHERE id=?");
        $stmt->execute([trim($_POST['title'] ?? ''), floatval($_POST['total_amount'] ?? 0), trim($_POST['invoice_number'] ?? ''), $dueDate, intval($_POST['payment_status'] ?? 0), trim($_POST['payment_method'] ?? ''), $bill_id]);
        echo json_encode(['status' => 'success', 'message' => '更新成功']);
    } catch (\PDOException $e) {
        echo json_encode(['status' => 'error', 'message' => '更新失敗: ' . $e->getMessage()]);
    }
    exit;
}

if ($action === 'delete_bill') {
    validateCSRF(); requireAdmin($currentUser);
    $id = intval($_POST['bill_id'] ?? 0);
    $stmt = $pdo->prepare("SELECT image_path FROM bills WHERE id = ?"); $stmt->execute([$id]); $bill = $stmt->fetch();
    if ($bill && file_exists($bill['image_path'])) @unlink($bill['image_path']);
    if ($bill) {
        $webpPath = preg_replace('/\.[^.]+$/', '.webp', $bill['image_path']);
        if (file_exists($webpPath)) @unlink($webpPath);
    }
    $pdo->prepare("DELETE FROM bills WHERE id = ?")->execute([$id]);
    echo json_encode(['status' => 'success', 'message' => '單據已刪除']); exit;
}

// Retroactive multi-signal duplicate scan across ALL existing bills - the
// per-upload check only ever compares a newly-processed bill against
// already-extracted ones, so anything that slipped through earlier (OCR
// misread the invoice number slightly differently between two photos of the
// same physical receipt, etc.) never gets a second chance. This re-checks
// every pair using several corroborating signals, strongest first, instead
// of relying on invoice_number being read identically both times.
if ($action === 'scan_duplicates') {
    validateCSRF(); requireManagerOrAdmin($currentUser);
    $bills = $pdo->query("SELECT * FROM bills WHERE raw_ocr_text = 'Extracted' AND (is_duplicate = 0 OR is_duplicate IS NULL) ORDER BY id ASC")->fetchAll();
    $n = count($bills);
    $flaggedIds = [];
    $flagged = 0;

    for ($i = 0; $i < $n; $i++) {
        $a = $bills[$i];
        if (in_array($a['id'], $flaggedIds)) continue;
        for ($j = $i + 1; $j < $n; $j++) {
            $b = $bills[$j];
            if (in_array($b['id'], $flaggedIds)) continue;
            if ($a['category'] !== $b['category']) continue;
            if (abs(floatval($a['total_amount']) - floatval($b['total_amount'])) >= 0.01) continue;

            $reason = null;
            $invA = $a['invoice_number'] ?? ''; $invB = $b['invoice_number'] ?? '';
            $custA = $a['customer_number'] ?? ''; $custB = $b['customer_number'] ?? '';

            if ($invA !== '' && $invB !== '' && $invA === $invB) {
                $reason = '發票號碼相同';
            } elseif ($invA !== '' && $invB !== '' && strlen($invA) === strlen($invB) && levenshtein($invA, $invB) <= 1) {
                $reason = '發票號碼近似 (可能為 OCR 誤讀單一字元)';
            } elseif ($custA !== '' && $custA === $custB && (
                        (!empty($a['due_date']) && $a['due_date'] === $b['due_date']) ||
                        (!empty($a['invoice_date']) && $a['invoice_date'] === $b['invoice_date'])
                      )) {
                $reason = '用戶編號與帳單日期相同';
            } elseif ($a['title'] === $b['title'] && (
                        (!empty($a['invoice_date']) && $a['invoice_date'] === $b['invoice_date']) ||
                        (!empty($a['due_date']) && $a['due_date'] === $b['due_date'])
                      )) {
                $reason = '標題、金額、日期相同';
            }

            if ($reason) {
                $pdo->prepare("UPDATE bills SET is_duplicate = 1, duplicate_of_id = ? WHERE id = ?")->execute([$a['id'], $b['id']]);
                $details = json_decode($b['details_json'], true) ?? [];
                $details['重複標記'] = "掃描比對發現與 #{$a['id']} 重複 ({$reason})";
                $pdo->prepare("UPDATE bills SET details_json = ? WHERE id = ?")->execute([json_encode($details, JSON_UNESCAPED_UNICODE), $b['id']]);
                $flaggedIds[] = $b['id'];
                $flagged++;
            }
        }
    }

    echo json_encode(['status' => 'success', 'message' => "掃描完成！本次找到並標記 {$flagged} 筆疑似重複項目。"]);
    exit;
}

// Correct a false positive from either the automatic checks or scan_duplicates.
if ($action === 'unmark_duplicate') {
    validateCSRF(); requireManagerOrAdmin($currentUser);
    $id = intval($_POST['bill_id'] ?? 0);
    $pdo->prepare("UPDATE bills SET is_duplicate = 0, duplicate_of_id = NULL WHERE id = ?")->execute([$id]);
    echo json_encode(['status' => 'success', 'message' => '已取消重複標記，此筆將重新計入總額']);
    exit;
}

// RESTORED: purge_database
if ($action === 'purge_database') {
    validateCSRF(); requireAdmin($currentUser);
    foreach (glob('uploads/*') as $file) if (is_file($file)) @unlink($file);
    $pdo->exec("SET FOREIGN_KEY_CHECKS = 0; TRUNCATE TABLE bills; SET FOREIGN_KEY_CHECKS = 1;");
    echo json_encode(['status' => 'success', 'message' => '資料庫已重置清空']); exit;
}

// Taiwan's Uniform Invoice lottery draws bi-monthly (01-02, 03-04, 05-06, 07-08,
// 09-10, 11-12); only invoices issued inside a period's own 2-month window are
// eligible for that period's prize numbers. Returns e.g. "11505" for any date
// in ROC year 115, May-Jun. Null if the date is missing/invalid.
function derive_lottery_period_code($dateStr) {
    if (empty($dateStr) || $dateStr === '0000-00-00') return null;
    $ts = strtotime($dateStr);
    if ($ts === false) return null;
    $rocYear = (int)date('Y', $ts) - 1911;
    $month = (int)date('n', $ts);
    $bucketStartMonth = $month - (($month - 1) % 2); // 1,3,5,7,9,11
    return sprintf('%d%02d', $rocYear, $bucketStartMonth);
}

if ($action === 'fetch_internet_lottery') {
    validateCSRF();
    // Always refresh from the live government feed first - sync_lottery.py
    // used to be a separate, never-wired-up action, so lottery_periods was
    // silently never populated (a title-format regex bug made every sync
    // parse zero rows) and every bill showed "尚未開獎" even for periods
    // that had actually been drawn. Folding the sync into every "對獎" click
    // means a real prize can't be missed just because nobody remembered to
    // sync separately.
    $pyBin = file_exists('/usr/local/bin/python3') ? '/usr/local/bin/python3' : '/usr/bin/python3';
    @shell_exec("{$pyBin} /volume1/web/nan/sync_lottery.py 2>&1");

    $periods = $pdo->query("SELECT * FROM lottery_periods ORDER BY period_code DESC")->fetchAll();
    if (empty($periods)) {
        // Live sync unreachable/failed and nothing has ever synced successfully -
        // fall back to this one known-good period rather than show nothing at
        // all, but this should self-heal the next time the network sync works.
        $periods = [
            ['period_code' => '11505', 'period_name' => '115年05-06月', 'super_prize' => '38548029', 'grand_prize' => '10138845', 'first_prizes' => '24121106,28589937,83663333']
        ];
    }
    $periodsByCode = [];
    foreach ($periods as $p) { $periodsByCode[$p['period_code']] = $p; }

    $bills = $pdo->query("SELECT * FROM bills WHERE is_duplicate = 0 OR is_duplicate IS NULL")->fetchAll();
    $matched = 0; $pending = 0;
    foreach ($bills as $b) {
        $inv = preg_replace('/[^0-9]/', '', $b['invoice_number'] ?? '');
        if (strlen($inv) < 8) continue;
        $num8 = substr($inv, -8);

        $billPeriodCode = derive_lottery_period_code($b['invoice_date'] ?? null);
        $p = $billPeriodCode ? ($periodsByCode[$billPeriodCode] ?? null) : null;

        if (!$p) {
            // This invoice's 2-month window hasn't been drawn/synced yet - don't
            // mark it as a loss, it just isn't eligible to check yet.
            $pending++;
            $pdo->prepare("UPDATE bills SET is_lottery_winning=0, lottery_prize_info=?, lottery_prize_amount=0 WHERE id=?")->execute(['尚未開獎', $b['id']]);
            continue;
        }

        $isWin = 0; $pText = '未中獎'; $pAmt = 0;
        if ($num8 === $p['super_prize']) { $isWin=1; $pText='特別獎 (1000萬)'; $pAmt=10000000; }
        elseif ($num8 === $p['grand_prize']) { $isWin=1; $pText='特獎 (200萬)'; $pAmt=2000000; }
        else {
            foreach (explode(',', $p['first_prizes']) as $fp) {
                $fp = trim($fp);
                if ($num8 === $fp) { $isWin=1; $pText='頭獎 (20萬)'; $pAmt=200000; break; }
                if (substr($num8,-7) === substr($fp,-7)) { $isWin=1; $pText='二獎 (4萬)'; $pAmt=40000; break; }
                if (substr($num8,-6) === substr($fp,-6)) { $isWin=1; $pText='三獎 (1萬)'; $pAmt=10000; break; }
                if (substr($num8,-5) === substr($fp,-5)) { $isWin=1; $pText='四獎 (4千)'; $pAmt=4000; break; }
                if (substr($num8,-4) === substr($fp,-4)) { $isWin=1; $pText='五獎 (1千)'; $pAmt=1000; break; }
                if (substr($num8,-3) === substr($fp,-3)) { $isWin=1; $pText='六獎 (200)'; $pAmt=200; break; }
            }
        }
        if ($isWin) { $matched++; }
        $pdo->prepare("UPDATE bills SET is_lottery_winning=?, lottery_prize_info=?, lottery_prize_amount=? WHERE id=?")->execute([$isWin, $pText, $pAmt, $b['id']]);
    }
    echo json_encode(['status' => 'success', 'message' => "對獎完成！共有 {$matched} 筆中獎，{$pending} 筆尚未開獎"]); exit;
}

if ($action === 'list_users') {
    requireAdmin($currentUser);
    echo json_encode(['status' => 'success', 'users' => $pdo->query("SELECT id, username, role, created_at FROM users")->fetchAll()]);
    exit;
}

if ($action === 'create_user') {
    validateCSRF(); requireAdmin($currentUser);
    $username = trim($_POST['username'] ?? ''); $password = trim($_POST['password'] ?? ''); $role = trim($_POST['role'] ?? 'ROLE_USER');
    if (empty($username) || empty($password)) { echo json_encode(['status' => 'error', 'message' => '欄位不可為空']); exit; }
    try {
        $pdo->prepare("INSERT INTO users (username, password_hash, role) VALUES (?, ?, ?)")->execute([$username, password_hash($password, PASSWORD_BCRYPT), $role]);
        echo json_encode(['status' => 'success', 'message' => '使用者建立成功']);
    } catch (\PDOException $e) { echo json_encode(['status' => 'error', 'message' => '帳號名稱重複']); }
    exit;
}

if ($action === 'delete_user') {
    validateCSRF(); requireAdmin($currentUser);
    $userId = intval($_POST['user_id'] ?? 0);
    if ($userId === $currentUser['id']) { echo json_encode(['status' => 'error', 'message' => '無法刪除當前登入者']); exit; }
    $pdo->prepare("DELETE FROM users WHERE id = ?")->execute([$userId]);
    echo json_encode(['status' => 'success', 'message' => '使用者已刪除']);
    exit;
}

echo json_encode(['status' => 'error', 'message' => '無效指令']);
?>