<?php
// receipt.php - Receipt Uploader (no login required)
// Supports .xls, .xlsx, .xlsm, .csv

// No session - standalone

// Database connection
$servername = "localhost";
$username   = "root";
$password   = "";
$dbname     = "paymentpack";

$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$conn->set_charset("utf8mb4");

// ---- Handle AJAX POST (CSV upload) ----
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['csv_file'])) {
    header('Content-Type: application/json');
    $response = ['success' => false, 'message' => '', 'inserted' => 0];

    try {
        // Get data from POST (not session)
        $facility_input   = isset($_POST['facility']) ? trim($_POST['facility']) : '';
        $district_input   = isset($_POST['district']) ? trim($_POST['district']) : '';
        $uploaded_by      = isset($_POST['uploaded_by']) ? trim($_POST['uploaded_by']) : 'Unknown';
        $del_point_input  = isset($_POST['del_point']) ? trim($_POST['del_point']) : '';

        if (empty($facility_input)) {
            throw new Exception('Facility is required.');
        }

        // ---- Check 30‑day upload limit for this facility ----
        $check_sql = "SELECT MAX(upload_date) as last_upload 
                      FROM receipts 
                      WHERE facility = '" . $conn->real_escape_string($facility_input) . "'";
        $check_result = $conn->query($check_sql);
        if ($check_result && $row = $check_result->fetch_assoc()) {
            $last_ts = $row['last_upload'] ? strtotime($row['last_upload']) : 0;
            $next_ts = strtotime('+30 days', $last_ts);
            if (time() < $next_ts && $row['last_upload'] !== null) {
                throw new Exception('You have already uploaded within the last 30 days. Please wait until ' . date('d M Y', $next_ts) . '.');
            }
        }

        $csv_file = $_FILES['csv_file']['tmp_name'];

        // ---- Ensure receipts table exists ----
        $table_exists = $conn->query("SHOW TABLES LIKE 'receipts'")->num_rows > 0;
        if (!$table_exists) {
            $create_sql = "CREATE TABLE IF NOT EXISTS receipts (
                id INT AUTO_INCREMENT PRIMARY KEY,
                DemanderCode_str VARCHAR(50),
                Demander VARCHAR(255),
                OrderNo_str VARCHAR(100),
                InvoiceNo_str VARCHAR(100) NOT NULL,
                ReceiptNo_str VARCHAR(100),
                OrderActivatedDate DATE,
                ReceivedDate DATE,
                NSN_str VARCHAR(50),
                ICN_str VARCHAR(50),
                packDescription_Str TEXT,
                QtyOrdered_int INT DEFAULT 0,
                QtyReceived_int INT DEFAULT 0,
                BatchNumber_str VARCHAR(50),
                Expiry_dat DATE,
                PackCost_mon DECIMAL(10,2) DEFAULT 0.00,
                TotalCost_mon DECIMAL(10,2) DEFAULT 0.00,
                Supplier_str VARCHAR(255),
                OrderType VARCHAR(50),
                Complete VARCHAR(10),
                facility VARCHAR(255),
                district VARCHAR(255),
                uploaded_by VARCHAR(255),
                del_point VARCHAR(7),
                upload_month VARCHAR(7),
                upload_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_invoice (InvoiceNo_str),
                INDEX idx_facility_month (facility, upload_month),
                INDEX idx_del_point (del_point)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
            $conn->query($create_sql);
            
            $conn->query("DROP TRIGGER IF EXISTS before_insert_receipts_paymentpack");
            $trigger_sql = "CREATE TRIGGER before_insert_receipts_paymentpack
                            BEFORE INSERT ON receipts
                            FOR EACH ROW
                            BEGIN
                                IF NEW.upload_month IS NULL THEN
                                    SET NEW.upload_month = DATE_FORMAT(NEW.upload_date, '%Y-%m');
                                END IF;
                            END";
            $conn->query($trigger_sql);
        }

        // ---- Read CSV ----
        $rows = [];
        if (($handle = fopen($csv_file, 'r')) !== false) {
            while (($row = fgetcsv($handle, 0, ',')) !== false) {
                if (!empty(array_filter($row, function($v) { return $v !== '' && $v !== null; }))) {
                    $rows[] = $row;
                }
            }
            fclose($handle);
        } else {
            throw new Exception('Could not read CSV file.');
        }

        if (count($rows) < 2) {
            throw new Exception('CSV contains no data rows.');
        }

        // ---- Determine headers ----
        $headers = array_shift($rows);
        $headers = array_map('trim', $headers);

        // ---- Map headers to DB columns ----
        $aliases = [
            'DemanderCode_str' => ['DemanderCode_str', 'DemanderCode', 'Facility Code', 'Code'],
            'Demander' => ['Demander', 'Facility', 'Demander Name', 'Clinic'],
            'OrderNo_str' => ['OrderNo_str', 'OrderNo', 'Order Number', 'Order #', 'Order'],
            'InvoiceNo_str' => ['InvoiceNo_str', 'InvoiceNo', 'Invoice Number', 'Invoice #', 'Invoice'],
            'ReceiptNo_str' => ['ReceiptNo_str', 'ReceiptNo', 'Receipt Number', 'Receipt #', 'Receipt'],
            'OrderActivatedDate' => ['OrderActivatedDate', 'ActivatedDate', 'Order Date', 'Date'],
            'ReceivedDate' => ['ReceivedDate', 'ReceiveDate', 'Receipt Date'],
            'NSN_str' => ['NSN_str', 'NSN', 'National Stock Number'],
            'ICN_str' => ['ICN_str', 'ICN', 'Item Code'],
            'packDescription_Str' => ['packDescription_Str', 'Description', 'Item', 'Product', 'Pack Description'],
            'QtyOrdered_int' => ['QtyOrdered_int', 'QtyOrdered', 'Ordered Qty', 'Order Qty', 'Qty Ord'],
            'QtyReceived_int' => ['QtyReceived_int', 'QtyReceived', 'Received Qty', 'Rec Qty', 'Qty Rec'],
            'BatchNumber_str' => ['BatchNumber_str', 'Batch', 'Batch No', 'Lot'],
            'Expiry_dat' => ['Expiry_dat', 'Expiry', 'Expiry Date', 'Exp Date'],
            'PackCost_mon' => ['PackCost_mon', 'PackCost', 'Unit Cost', 'Pack Price'],
            'TotalCost_mon' => ['TotalCost_mon', 'TotalCost', 'Total', 'Total Cost', 'Amount'],
            'Supplier_str' => ['Supplier_str', 'Supplier', 'Vendor'],
            'OrderType' => ['OrderType', 'Type', 'Order Type'],
            'Complete' => ['Complete', 'Completed', 'Status', 'Complete?']
        ];

        $col_map = [];
        foreach ($headers as $idx => $header) {
            $h = trim($header);
            foreach ($aliases as $db_field => $patterns) {
                if (in_array($h, $patterns)) {
                    $col_map[$idx] = $db_field;
                    break;
                }
            }
        }
        if (!in_array('InvoiceNo_str', $col_map)) {
            foreach ($headers as $idx => $h) {
                if (stripos($h, 'invoice') !== false) {
                    $col_map[$idx] = 'InvoiceNo_str';
                    break;
                }
            }
        }

        // ---- Validate Invoice format on server side ----
        $invalid_invoices = [];
        $invoice_idx = array_search('InvoiceNo_str', $col_map);
        if ($invoice_idx !== false) {
            foreach ($rows as $row_index => $row) {
                if (isset($row[$invoice_idx])) {
                    $inv = trim($row[$invoice_idx]);
                    if (!empty($inv)) {
                        if (!preg_match('/^[0-9]{7}\//', $inv) || !in_array(substr($inv, 0, 2), ['12','13','14','15','16','17','18','19','20'])) {
                            $invalid_invoices[] = ['row' => $row_index+2, 'value' => $inv];
                        }
                    }
                }
            }
        }
        if (!empty($invalid_invoices)) {
            $msg = "Invalid Invoice format in rows: " . implode(', ', array_column($invalid_invoices, 'row'));
            throw new Exception($msg . ". Please correct and re-upload.");
        }

        // ---- Get current month for upload_month ----
        $current_month = date('Y-m');

        // ---- Prepare insert statement ----
        $inserted = 0;
        $errors = [];

        $prefix_query = "SELECT MAX(InvoiceNo_str) as max_invoice FROM receipts";
        $prefix_result = $conn->query($prefix_query);
        $max_invoice = $prefix_result->fetch_assoc()['max_invoice'];
        $current_prefix = 16;
        if ($max_invoice && strlen($max_invoice) >= 2) {
            $current_prefix = intval(substr($max_invoice, 0, 2));
            if ($current_prefix < 16) $current_prefix = 16;
        }
        $num_query = "SELECT MAX(CAST(SUBSTRING(InvoiceNo_str, 3) AS UNSIGNED)) as max_num 
                      FROM receipts WHERE InvoiceNo_str LIKE '" . $current_prefix . "%'";
        $num_result = $conn->query($num_query);
        $next_number = 1;
        if ($num_result && $num_result->num_rows > 0) {
            $max_num = $num_result->fetch_assoc()['max_num'];
            $next_number = ($max_num ?? 0) + 1;
        }

        foreach ($rows as $row_index => $row) {
            $data = [];
            foreach ($col_map as $idx => $db_field) {
                if (isset($row[$idx])) {
                    $value = trim($row[$idx]);
                    $data[$db_field] = $value;
                }
            }

            if (empty(array_filter($data, function($v) { return $v !== '' && $v !== null; }))) {
                continue;
            }

            if (empty($data['InvoiceNo_str'])) {
                $data['InvoiceNo_str'] = $current_prefix . str_pad($next_number, 6, '0', STR_PAD_LEFT);
                $next_number++;
            } else {
                $check_sql = "SELECT id FROM receipts WHERE InvoiceNo_str = '" . 
                             $conn->real_escape_string($data['InvoiceNo_str']) . "'";
                $check_result = $conn->query($check_sql);
                if ($check_result && $check_result->num_rows > 0) {
                    $data['InvoiceNo_str'] = $current_prefix . str_pad($next_number, 6, '0', STR_PAD_LEFT);
                    $next_number++;
                }
            }

            $data['facility']    = $facility_input;
            $data['district']    = $district_input;
            $data['uploaded_by'] = $uploaded_by;
            $data['del_point']   = $del_point_input;
            $data['upload_month'] = $current_month;

            $date_fields = ['OrderActivatedDate', 'ReceivedDate', 'Expiry_dat'];
            foreach ($date_fields as $df) {
                if (isset($data[$df]) && !empty($data[$df])) {
                    $value = $data[$df];
                    // Date conversion
                    if (is_numeric($value) && strlen($value) > 5 && $value < 100000) {
                        $value = date('Y-m-d', ($value - 25569) * 86400);
                    } elseif (preg_match('/\d{4}\/\d{2}\/\d{2}/', $value)) {
                        $parts = explode('/', $value);
                        $value = $parts[0] . '-' . $parts[1] . '-' . $parts[2];
                    } elseif (preg_match('/\d{2}\/\d{2}\/\d{4}/', $value)) {
                        $parts = explode('/', $value);
                        $value = $parts[2] . '-' . $parts[1] . '-' . $parts[0];
                    } elseif (preg_match('/\d{2}-\d{2}-\d{4}/', $value)) {
                        $parts = explode('-', $value);
                        $value = $parts[2] . '-' . $parts[1] . '-' . $parts[0];
                    } elseif (!preg_match('/\d{4}-\d{2}-\d{2}/', $value)) {
                        $value = null;
                    }
                    $data[$df] = $value;
                }
            }

            foreach (['QtyOrdered_int', 'QtyReceived_int'] as $nf) {
                if (isset($data[$nf])) {
                    $data[$nf] = is_numeric($data[$nf]) ? intval($data[$nf]) : 0;
                }
            }
            foreach (['PackCost_mon', 'TotalCost_mon'] as $nf) {
                if (isset($data[$nf])) {
                    $data[$nf] = is_numeric($data[$nf]) ? floatval($data[$nf]) : 0;
                }
            }

            $fields = [];
            $values = [];
            foreach ($data as $field => $value) {
                $fields[] = "`$field`";
                if ($value !== null && $value !== '') {
                    $values[] = "'" . $conn->real_escape_string($value) . "'";
                } else {
                    $values[] = "NULL";
                }
            }
            $insert_sql = "INSERT INTO receipts (" . implode(', ', $fields) . ") 
                           VALUES (" . implode(', ', $values) . ")";
            if ($conn->query($insert_sql)) {
                $inserted++;
            } else {
                $errors[] = "Row " . ($row_index+2) . ": " . $conn->error;
            }
        }

        $response['success'] = true;
        $response['message'] = "Uploaded $inserted rows successfully.";
        $response['inserted'] = $inserted;
        if (!empty($errors)) {
            $response['message'] .= " Some errors: " . implode('; ', array_slice($errors, 0, 5));
        }

    } catch (Exception $e) {
        $response['message'] = $e->getMessage();
    }

    echo json_encode($response);
    exit;
}

// ---- If not POST, serve the HTML page ----
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>Receipt Upload | KZN Health</title>
    <script src="https://cdn.sheetjs.com/xlsx-0.20.1/package/dist/xlsx.full.min.js"></script>
    <style>
        /* (same styles as before – kept compact) */
        * { box-sizing: border-box; margin:0; padding:0; }
        body { font-family:'Inter',sans-serif; background:#f0f4fb; min-height:100vh; display:flex; align-items:stretch; margin:0; }
        .left-panel { flex:0 0 38%; min-height:100vh; background:url('receive.png') center/cover no-repeat; position:relative; display:flex; align-items:flex-start; justify-content:flex-start; }
        .left-panel .overlay { position:absolute; inset:0; background:rgba(10,60,50,0.45); }
        .left-panel .brand-text { position:relative; z-index:1; color:#fff; padding:30px 25px; font-size:24px; font-weight:700; text-shadow:0 2px 12px rgba(0,0,0,0.3); max-width:80%; }
        .left-panel .brand-text small { display:block; font-size:14px; font-weight:400; opacity:0.85; margin-top:4px; }
        .right-panel { flex:1; display:flex; justify-content:center; align-items:center; padding:20px; overflow-y:auto; min-height:100vh; }
        .card { background:#fff; border-radius:32px; box-shadow:0 20px 60px rgba(0,20,40,0.15); padding:36px 32px 32px; max-width:820px; width:100%; }
        .header { display:flex; align-items:center; justify-content:space-between; margin-bottom:6px; flex-wrap:wrap; gap:12px; }
        .header-left { display:flex; align-items:center; gap:12px; }
        .header-left .logo { height:46px; width:auto; object-fit:contain; }
        .header-left h1 { font-size:24px; font-weight:700; color:#0b1b2f; }
        .header-right .back-link { display:inline-flex; align-items:center; gap:6px; color:#1a4f8a; text-decoration:none; font-weight:600; font-size:14px; padding:8px 16px; border-radius:40px; background:#f0f6fe; border:1px solid #d6e4f5; transition:all .2s; }
        .header-right .back-link:hover { background:#e6edf8; border-color:#1a4f8a; }
        .subtitle { color:#5e6f88; font-size:14px; font-weight:400; margin-bottom:18px; display:flex; flex-wrap:wrap; align-items:center; gap:10px; }
        .badge { background:#e6edf8; color:#1a4f8a; font-size:12px; font-weight:600; padding:2px 14px; border-radius:30px; }
        .steps { display:flex; justify-content:space-between; align-items:center; margin-bottom:20px; background:#f8faff; padding:10px 18px; border-radius:16px; border:1px solid #e2ecf9; flex-wrap:wrap; gap:6px; }
        .step-item { display:flex; align-items:center; gap:6px; font-size:13px; font-weight:500; color:#5e6f88; }
        .step-item .step-num { display:inline-flex; align-items:center; justify-content:center; width:22px; height:22px; background:#d0d9e6; color:#fff; border-radius:50%; font-weight:700; font-size:11px; transition:background .3s; }
        .step-item.active .step-num { background:#1a4f8a; }
        .step-item.done .step-num { background:#1f8b4c; }
        .step-arrow { color:#c8d4e3; font-size:16px; }
        .drop-zone { border:2px dashed #d0d9e6; border-radius:20px; padding:38px 20px; text-align:center; cursor:pointer; transition:all .25s ease; background:#fafcff; position:relative; }
        .drop-zone:hover { border-color:#6c8cbf; background:#f5f9ff; }
        .drop-zone.dragover { border-color:#2a6bb0; background:#eaf1fb; transform:scale(1.005); }
        .drop-zone.has-file { border-color:#2a6bb0; background:#f5faff; padding:24px 20px; }
        .drop-zone .upload-icon { font-size:42px; display:block; margin-bottom:6px; color:#3b6ea5; }
        .drop-zone h3 { font-size:17px; font-weight:600; color:#0b1b2f; margin-bottom:2px; }
        .drop-zone p { color:#6a7f9b; font-size:14px; }
        .drop-zone .file-types { font-size:12px; color:#8a9bb5; margin-top:6px; background:#f0f4fa; display:inline-block; padding:3px 14px; border-radius:30px; font-weight:500; }
        .drop-zone input[type="file"] { position:absolute; inset:0; opacity:0; cursor:pointer; width:100%; height:100%; }
        .file-info { display:none; align-items:center; gap:14px; margin-top:16px; padding:12px 18px; background:#f0f6fe; border-radius:16px; border:1px solid #d6e4f5; }
        .file-info.visible { display:flex; }
        .file-info .file-icon { font-size:24px; flex-shrink:0; }
        .file-info .file-details { flex:1; min-width:0; }
        .file-info .file-name { font-weight:600; color:#0b1b2f; font-size:14px; white-space:nowrap; overflow:hidden; text-overflow:ellipsis; }
        .file-info .file-meta { font-size:12px; color:#5e6f88; margin-top:2px; }
        .file-info .file-status { font-size:18px; color:#1f8b4c; }
        .file-info .remove-file { background:none; border:none; font-size:18px; color:#8a9bb5; cursor:pointer; padding:2px 6px; border-radius:8px; transition:background .2s,color .2s; }
        .file-info .remove-file:hover { background:#e8edf5; color:#c0392b; }
        .sheet-selector { display:none; margin-top:16px; align-items:center; gap:12px; flex-wrap:wrap; }
        .sheet-selector.visible { display:flex; }
        .sheet-selector label { font-weight:500; color:#0b1b2f; font-size:13px; }
        .sheet-selector select { padding:8px 14px; border-radius:10px; border:1.5px solid #d0d9e6; background:#fff; font-size:13px; font-weight:500; color:#0b1b2f; min-width:160px; cursor:pointer; transition:border-color .2s; appearance:none; background-image:url("data:image/svg+xml,%3Csvg xmlns='http://www.w3.org/2000/svg' width='12' height='8' viewBox='0 0 12 8'%3E%3Cpath fill='%235e6f88' d='M6 8L0 0h12z'/%3E%3C/svg%3E"); background-repeat:no-repeat; background-position:right 12px center; padding-right:36px; }
        .sheet-selector select:focus { border-color:#2a6bb0; box-shadow:0 0 0 4px rgba(42,107,176,0.12); outline:none; }
        .sheet-selector .sheet-count { font-size:12px; color:#5e6f88; background:#f0f4fa; padding:3px 12px; border-radius:30px; font-weight:500; }
        .actions { display:none; gap:10px; margin-top:20px; flex-wrap:wrap; }
        .actions.visible { display:flex; }
        .btn { display:inline-flex; align-items:center; gap:6px; padding:10px 24px; border-radius:12px; font-size:14px; font-weight:600; border:none; cursor:pointer; transition:all .2s; font-family:inherit; }
        .btn-primary { background:#1a4f8a; color:#fff; box-shadow:0 4px 12px rgba(26,79,138,0.25); }
        .btn-primary:hover { background:#0f3d6b; transform:translateY(-2px); }
        .btn-primary:disabled { opacity:0.5; cursor:not-allowed; transform:none; }
        .btn-success { background:#1a8a4a; color:#fff; box-shadow:0 4px 12px rgba(26,138,74,0.25); }
        .btn-success:hover { background:#12703b; transform:translateY(-2px); }
        .btn-success:disabled { opacity:0.5; cursor:not-allowed; transform:none; }
        .btn-outline { background:transparent; color:#1a4f8a; border:1.5px solid #d0d9e6; }
        .btn-outline:hover { background:#f0f6fe; border-color:#1a4f8a; }
        .btn-upload { background:#8a2be2; color:#fff; box-shadow:0 4px 12px rgba(138,43,226,0.30); }
        .btn-upload:hover { background:#6f1db5; transform:translateY(-2px); }
        .btn-upload:disabled { opacity:0.5; cursor:not-allowed; transform:none; }
        .btn .spinner { width:16px; height:16px; border:2px solid rgba(255,255,255,0.3); border-top:2px solid #fff; border-radius:50%; animation:spin 0.7s linear infinite; display:none; }
        .btn.loading .spinner { display:inline-block; }
        .btn.loading .btn-text { display:none; }
        @keyframes spin { to { transform:rotate(360deg); } }
        .upload-section { display:none; margin-top:16px; padding:16px 18px 18px; background:#f8faff; border-radius:16px; border:1px solid #e2ecf9; text-align:center; }
        .upload-section.visible { display:block; }
        .upload-section .session-info { font-size:13px; color:#5e6f88; margin-bottom:12px; display:flex; flex-wrap:wrap; gap:10px; justify-content:center; }
        .upload-section .session-info label { font-weight:600; color:#0b1b2f; margin-right:4px; }
        .upload-section .session-info input { border:1px solid #d0d9e6; border-radius:6px; padding:4px 8px; font-size:13px; width:auto; min-width:100px; }
        .upload-status { margin-top:12px; padding:10px 14px; border-radius:12px; font-size:13px; background:#fef7e0; border:1px solid #f5e3b8; color:#8a6d1b; display:block; }
        .preview-section { display:none; margin-top:20px; border-top:1.5px solid #e9eef4; padding-top:18px; }
        .preview-section.visible { display:block; }
        .preview-header { display:flex; justify-content:space-between; align-items:center; margin-bottom:10px; flex-wrap:wrap; gap:6px; }
        .preview-header h4 { font-size:14px; font-weight:600; color:#0b1b2f; }
        .preview-header .row-count { font-size:12px; color:#5e6f88; background:#f0f4fa; padding:2px 12px; border-radius:30px; font-weight:500; }
        .preview-table-wrap { overflow:auto; border-radius:14px; border:1px solid #e9eef4; max-height:260px; background:#fafcff; }
        .preview-table-wrap table { width:100%; border-collapse:collapse; font-size:12px; white-space:nowrap; }
        .preview-table-wrap thead { position:sticky; top:0; z-index:2; }
        .preview-table-wrap th { background:#eef3fa; color:#1a2d44; font-weight:600; padding:8px 14px; text-align:left; border-bottom:2px solid #d6e0eb; font-size:11px; text-transform:uppercase; letter-spacing:0.03em; }
        .preview-table-wrap td { padding:7px 14px; border-bottom:1px solid #eef3f8; color:#1f334a; max-width:180px; overflow:hidden; text-overflow:ellipsis; }
        .preview-table-wrap tbody tr:hover td { background:#f4f9ff; }
        .edit-section { display:none; margin-top:20px; border-top:1.5px solid #e9eef4; padding-top:18px; }
        .edit-section.visible { display:block; }
        .edit-header { display:flex; justify-content:space-between; align-items:center; flex-wrap:wrap; gap:8px; margin-bottom:10px; }
        .edit-header h4 { font-size:14px; font-weight:600; color:#c0392b; display:flex; align-items:center; gap:8px; }
        .edit-header .badge-invalid { background:#fde9e9; color:#a32d2d; padding:2px 12px; border-radius:30px; font-size:12px; font-weight:600; }
        .edit-header .badge-valid { background:#e6f5ed; color:#0f6e3a; padding:2px 12px; border-radius:30px; font-size:12px; font-weight:600; display:none; }
        .edit-table-wrap { overflow:auto; border-radius:14px; border:1px solid #e9eef4; max-height:300px; background:#fafcff; }
        .edit-table { width:100%; border-collapse:collapse; font-size:13px; }
        .edit-table th { background:#eef3fa; color:#1a2d44; font-weight:600; padding:8px 12px; text-align:left; border-bottom:2px solid #d6e0eb; font-size:12px; text-transform:uppercase; }
        .edit-table td { padding:6px 12px; border-bottom:1px solid #eef3f8; vertical-align:middle; }
        .edit-table .edit-input { width:100%; min-width:180px; padding:6px 10px; border-radius:6px; border:2px solid #d0d9e6; font-size:13px; font-family:monospace; transition:border-color .2s,background .2s; background:#fff; }
        .edit-table .edit-input:focus { outline:none; border-color:#2a6bb0; box-shadow:0 0 0 4px rgba(42,107,176,0.10); }
        .edit-table .edit-input.valid { border-color:#1f8b4c; background:#f0fff4; }
        .edit-table .edit-input.invalid { border-color:#c0392b; background:#fff5f5; }
        .edit-table .row-invalid { background:#fff5f5; }
        .edit-table .row-valid { background:#f0fff4; }
        .edit-table .status-icon { font-size:16px; }
        .edit-status-msg { margin-top:10px; padding:8px 14px; border-radius:8px; font-size:13px; background:#fef7e0; border-left:4px solid #f5a623; color:#8a6d1b; }
        .edit-status-msg.success { background:#e6f5ed; border-left-color:#1f8b4c; color:#0f6e3a; }
        .status-msg { margin-top:14px; padding:10px 16px; border-radius:12px; font-size:13px; font-weight:500; display:none; }
        .status-msg.visible { display:block; }
        .status-msg.success { background:#e6f5ed; color:#0f6e3a; border:1px solid #b8e0cc; }
        .status-msg.error { background:#fde9e9; color:#a32d2d; border:1px solid #f5cdcd; }
        .status-msg.info { background:#e6edf8; color:#1a4f8a; border:1px solid #c5d7ed; }
        .status-msg.warning { background:#fef7e0; color:#8a6d1b; border:1px solid #f5e3b8; }
        .progress-wrap { display:none; margin-top:14px; }
        .progress-wrap.visible { display:block; }
        .progress-bar { width:100%; height:5px; background:#e9eef4; border-radius:10px; overflow:hidden; }
        .progress-bar .fill { height:100%; width:0%; background:linear-gradient(90deg,#6c8cbf,#2a6bb0); border-radius:10px; transition:width .4s; }
        .progress-text { font-size:12px; color:#5e6f88; margin-top:4px; text-align:right; }
        @media (max-width:820px) { body{flex-direction:column;align-items:center;} .left-panel{flex:0 0 200px;width:100%;min-height:200px;} .right-panel{padding:16px;min-height:auto;} .card{padding:24px 18px;} .header-left h1{font-size:20px;} }
        @media (max-width:480px) { .left-panel{flex:0 0 140px;min-height:140px;} .left-panel .brand-text{font-size:16px;padding:14px;} .steps{flex-direction:column;align-items:flex-start;} .step-arrow{display:none;} .drop-zone{padding:24px 14px;} .actions .btn{justify-content:center;width:100%;} .edit-table .edit-input{min-width:80px;font-size:12px;padding:4px 6px;} }
    </style>
</head>
<body>
<div class="left-panel">
    <div class="overlay"></div>
    <div class="brand-text">KZN Health<small>Provincial Pharmaceutical Supply Depot</small></div>
</div>
<div class="right-panel">
    <div class="card">
        <div class="header">
            <div class="header-left">
                <img src="../img/newkzn.png" alt="KZN Health" class="logo" onerror="this.style.display='none'" />
                <span class="icon">📊</span>
                <h1>Receipt Upload</h1>
            </div>
            <div class="header-right">
                <a href="delete.php" class="back-link" title="Manage Records">📋 Manage Records</a>
                <a href="../index.php" class="back-link" title="Home">🏠 Home</a>
            </div>
        </div>

        <p class="subtitle">
            <span>Upload pharmacy receipts to the system</span>
            <span class="badge">Standalone Mode</span>
        </p>

        <!-- Steps -->
        <div class="steps" id="steps">
            <div class="step-item active" id="step1"><span class="step-num">1</span><span class="step-label">Drop Excel</span></div>
            <span class="step-arrow">→</span>
            <div class="step-item" id="step2"><span class="step-num">2</span><span class="step-label">Convert</span></div>
            <span class="step-arrow">→</span>
            <div class="step-item" id="step3"><span class="step-num">3</span><span class="step-label">Review</span></div>
            <span class="step-arrow">→</span>
            <div class="step-item" id="step4"><span class="step-num">4</span><span class="step-label">Upload</span></div>
        </div>

        <!-- Upload status (cooldown message) -->
        <div class="upload-status" id="uploadStatus">Ready to upload.</div>

        <!-- Drop Zone -->
        <div class="drop-zone" id="dropZone">
            <span class="upload-icon">📁</span>
            <h3>Drop your Excel / CSV file here</h3>
            <p>or click to browse</p>
            <span class="file-types">.xls &bull; .xlsx &bull; .xlsm &bull; .csv</span>
            <input type="file" id="fileInput" accept=".xls,.xlsx,.xlsm,.csv" />
        </div>

        <!-- File Info -->
        <div class="file-info" id="fileInfo">
            <span class="file-icon">📄</span>
            <div class="file-details">
                <div class="file-name" id="fileName">example.xlsx</div>
                <div class="file-meta" id="fileMeta">2.4 MB • 3 sheets</div>
            </div>
            <span class="file-status" id="fileStatus">✅</span>
            <button class="remove-file" id="removeFile">✕</button>
        </div>

        <!-- Sheet Selector -->
        <div class="sheet-selector" id="sheetSelector">
            <label for="sheetSelect">📑 Sheet:</label>
            <select id="sheetSelect"></select>
            <span class="sheet-count" id="sheetCount">0 sheets</span>
        </div>

        <!-- Actions -->
        <div class="actions" id="actions">
            <button class="btn btn-primary" id="convertBtn">Convert to CSV</button>
            <button class="btn btn-success" id="downloadBtn" disabled>Download CSV</button>
            <button class="btn btn-outline" id="resetBtn">↻ Reset</button>
        </div>

        <!-- Upload Section with Input Fields -->
        <div class="upload-section" id="uploadSection">
            <div class="session-info">
                <span><label>Facility:</label> <input type="text" id="inputFacility" value="TEST" /></span>
                <span><label>District:</label> <input type="text" id="inputDistrict" value="Test District" /></span>
                <span><label>Uploaded By:</label> <input type="text" id="inputUploader" value="Admin" /></span>
                <span><label>Delivery Point:</label> <input type="text" id="inputDelPoint" value="DP001" /></span>
            </div>
            <button class="btn btn-upload" id="uploadBtn" disabled>
                <span class="btn-text">⬆ Upload to Database</span>
                <span class="spinner"></span>
            </button>
        </div>

        <!-- Progress -->
        <div class="progress-wrap" id="progressWrap">
            <div class="progress-bar"><div class="fill" id="progressFill"></div></div>
            <div class="progress-text" id="progressText">0%</div>
        </div>

        <!-- Status Message -->
        <div class="status-msg" id="statusMsg"></div>

        <!-- Preview -->
        <div class="preview-section" id="previewSection">
            <div class="preview-header"><h4>📋 CSV Preview</h4><span class="row-count" id="rowCount">0 rows</span></div>
            <div class="preview-table-wrap">
                <table><thead id="previewHead"></thead><tbody id="previewBody"></tbody></table>
            </div>
        </div>

        <!-- Editable Validation -->
        <div class="edit-section" id="editSection">
            <div class="edit-header">
                <h4><span>⚠️ Invalid Invoice Numbers</span><span class="badge-invalid" id="invalidBadge">0 invalid</span><span class="badge-valid" id="validBadge">✅ All valid</span></h4>
                <span style="font-size:13px;color:#5e6f88;">Edit to match required format</span>
            </div>
            <div class="edit-table-wrap">
                <table class="edit-table">
                    <thead><tr><th style="width:60px;">Row</th><th>Original Invoice</th><th>Corrected Invoice</th><th style="width:60px;">Status</th></tr></thead>
                    <tbody id="editBody"></tbody>
                </table>
            </div>
            <div class="edit-status-msg" id="editStatusMsg"><strong>⚠️ Please correct highlighted rows.</strong> Invoice must start with 7 digits, then '/', first two digits 12-20.</div>
        </div>
    </div>
</div>

<script>
(function() {
    'use strict';

    // DOM refs
    const dropZone = document.getElementById('dropZone');
    const fileInput = document.getElementById('fileInput');
    const fileInfo = document.getElementById('fileInfo');
    const fileName = document.getElementById('fileName');
    const fileMeta = document.getElementById('fileMeta');
    const fileStatus = document.getElementById('fileStatus');
    const removeFileBtn = document.getElementById('removeFile');
    const sheetSelector = document.getElementById('sheetSelector');
    const sheetSelect = document.getElementById('sheetSelect');
    const sheetCount = document.getElementById('sheetCount');
    const actions = document.getElementById('actions');
    const convertBtn = document.getElementById('convertBtn');
    const downloadBtn = document.getElementById('downloadBtn');
    const resetBtn = document.getElementById('resetBtn');
    const uploadSection = document.getElementById('uploadSection');
    const uploadBtn = document.getElementById('uploadBtn');
    const progressWrap = document.getElementById('progressWrap');
    const progressFill = document.getElementById('progressFill');
    const progressText = document.getElementById('progressText');
    const statusMsg = document.getElementById('statusMsg');
    const previewSection = document.getElementById('previewSection');
    const previewHead = document.getElementById('previewHead');
    const previewBody = document.getElementById('previewBody');
    const rowCount = document.getElementById('rowCount');
    const uploadStatus = document.getElementById('uploadStatus');
    const step1 = document.getElementById('step1');
    const step2 = document.getElementById('step2');
    const step3 = document.getElementById('step3');
    const step4 = document.getElementById('step4');
    const editSection = document.getElementById('editSection');
    const editBody = document.getElementById('editBody');
    const invalidBadge = document.getElementById('invalidBadge');
    const validBadge = document.getElementById('validBadge');
    const editStatusMsg = document.getElementById('editStatusMsg');
    const inputFacility = document.getElementById('inputFacility');
    const inputDistrict = document.getElementById('inputDistrict');
    const inputUploader = document.getElementById('inputUploader');
    const inputDelPoint = document.getElementById('inputDelPoint');

    let currentFile = null;
    let workbookData = null;
    let sheetNames = [];
    let currentCSV = '';
    let currentSheetName = '';
    let isUploading = false;
    let invalidRows = [];
    let allRowsData = [];
    let headers = [];
    let invoiceColumnIndex = -1;
    let isAllValid = false;

    function formatFileSize(bytes) {
        if (bytes < 1024) return bytes + ' B';
        if (bytes < 1048576) return (bytes / 1024).toFixed(1) + ' KB';
        return (bytes / 1048576).toFixed(1) + ' MB';
    }

    function showStatus(message, type = 'info') {
        statusMsg.textContent = message;
        statusMsg.className = 'status-msg visible ' + type;
    }

    function hideStatus() { statusMsg.className = 'status-msg'; }

    function setProgress(pct) {
        progressFill.style.width = Math.min(100, Math.max(0, pct)) + '%';
        progressText.textContent = Math.round(pct) + '%';
    }

    function updateSteps(active) {
        [step1, step2, step3, step4].forEach(el => el.classList.remove('active', 'done'));
        const map = {1: step1, 2: step2, 3: step3, 4: step4};
        for (let i = 1; i <= 4; i++) {
            if (i < active) map[i].classList.add('done');
            else if (i === active) map[i].classList.add('active');
        }
    }

    function resetUI(keepFile = false) {
        hideStatus();
        progressWrap.classList.remove('visible');
        setProgress(0);
        if (!keepFile) {
            currentFile = null; workbookData = null; sheetNames = []; currentCSV = ''; currentSheetName = '';
            fileInfo.classList.remove('visible');
            sheetSelector.classList.remove('visible');
            actions.classList.remove('visible');
            uploadSection.classList.remove('visible');
            previewSection.classList.remove('visible');
            editSection.classList.remove('visible');
            dropZone.classList.remove('has-file');
            fileInput.value = '';
            downloadBtn.disabled = true;
            convertBtn.disabled = false;
            uploadBtn.disabled = true;
            uploadBtn.classList.remove('loading');
            sheetSelect.innerHTML = '';
            previewHead.innerHTML = '';
            previewBody.innerHTML = '';
            rowCount.textContent = '0 rows';
            editBody.innerHTML = '';
            invalidRows = []; allRowsData = []; headers = []; invoiceColumnIndex = -1; isAllValid = false;
            updateSteps(1);
        } else {
            currentCSV = '';
            previewSection.classList.remove('visible');
            editSection.classList.remove('visible');
            downloadBtn.disabled = true;
            convertBtn.disabled = false;
            uploadBtn.disabled = true;
            uploadBtn.classList.remove('loading');
            previewHead.innerHTML = '';
            previewBody.innerHTML = '';
            rowCount.textContent = '0 rows';
            editBody.innerHTML = '';
            invalidRows = []; allRowsData = []; headers = []; invoiceColumnIndex = -1; isAllValid = false;
            updateSteps(2);
        }
        isUploading = false;
        uploadStatus.textContent = 'Ready to upload.';
        uploadStatus.style.display = 'block';
    }

    function updateFileInfo(file) {
        fileName.textContent = file.name;
        fileMeta.textContent = formatFileSize(file.size) + ' • ' + (sheetNames.length || '?') + ' sheets';
        fileInfo.classList.add('visible');
        dropZone.classList.add('has-file');
        fileStatus.textContent = '✅';
        updateSteps(2);
    }

    function populateSheetSelector(names) {
        sheetSelect.innerHTML = '';
        names.forEach((name, i) => {
            const opt = document.createElement('option');
            opt.value = name;
            opt.textContent = name + (i === 0 ? ' (default)' : '');
            if (i === 0) opt.selected = true;
            sheetSelect.appendChild(opt);
        });
        sheetCount.textContent = names.length + ' sheet' + (names.length > 1 ? 's' : '');
        sheetSelector.classList.add('visible');
        currentSheetName = names[0] || '';
        updateSteps(2);
    }

    // Parser for Excel and CSV
    function parseFile(file) {
        return new Promise((resolve, reject) => {
            const ext = file.name.split('.').pop().toLowerCase();
            const reader = new FileReader();
            if (ext === 'csv') {
                reader.onload = function(e) {
                    try {
                        const csvData = e.target.result;
                        const workbook = XLSX.read(csvData, { type: 'string', raw: true });
                        resolve(workbook);
                    } catch (err) { reject(err); }
                };
                reader.onerror = function() { reject(new Error('Failed to read CSV.')); };
                reader.readAsText(file);
            } else {
                reader.onload = function(e) {
                    try {
                        const data = new Uint8Array(e.target.result);
                        const workbook = XLSX.read(data, { type: 'array', cellDates: true });
                        resolve(workbook);
                    } catch (err) { reject(err); }
                };
                reader.onerror = function() { reject(new Error('Failed to read file.')); };
                reader.readAsArrayBuffer(file);
            }
        });
    }

    function sheetToCSV(workbook, sheetName) {
        const sheet = workbook.Sheets[sheetName];
        if (!sheet) return '';
        return XLSX.utils.sheet_to_csv(sheet, { FS: ',', RS: '\n', forceQuotes: false });
    }

    function isValidInvoice(inv) {
        if (!inv || typeof inv !== 'string') return false;
        inv = inv.trim();
        if (!/^[0-9]{7}\//.test(inv)) return false;
        const prefix = inv.substring(0, 2);
        return ['12','13','14','15','16','17','18','19','20'].includes(prefix);
    }

    function parseCSVLine(line) {
        const result = [];
        let current = '', inQuotes = false;
        for (let i = 0; i < line.length; i++) {
            const ch = line[i];
            if (inQuotes) {
                if (ch === '"' && line[i+1] === '"') { current += '"'; i++; }
                else if (ch === '"') { inQuotes = false; }
                else { current += ch; }
            } else {
                if (ch === '"') { inQuotes = true; }
                else if (ch === ',') { result.push(current.trim()); current = ''; }
                else { current += ch; }
            }
        }
        result.push(current.trim());
        return result;
    }

    function getInvoiceColumnIndex(headers) {
        const aliases = ['InvoiceNo_str','InvoiceNo','Invoice Number','Invoice #','Invoice'];
        for (let i=0; i<headers.length; i++) {
            if (aliases.some(a => headers[i].trim() === a)) return i;
        }
        for (let i=0; i<headers.length; i++) {
            if (headers[i].toLowerCase().includes('invoice')) return i;
        }
        return -1;
    }

    function renderPreview(csvText, maxRows = 100) {
        if (!csvText) { previewSection.classList.remove('visible'); return; }
        const lines = csvText.split('\n').filter(l => l.trim() !== '');
        if (lines.length === 0) { previewSection.classList.remove('visible'); return; }
        const headerLine = lines[0] || '';
        const h = parseCSVLine(headerLine);
        const dataLines = lines.slice(1, maxRows+1);
        const totalRows = lines.length - 1;
        let theadHtml = '<tr>'; h.forEach(col => { theadHtml += `<th>${escapeHtml(col||'')}</th>`; }); theadHtml += '</tr>';
        previewHead.innerHTML = theadHtml;
        let tbodyHtml = '';
        dataLines.forEach(line => {
            if (line.trim() === '') return;
            const cells = parseCSVLine(line);
            tbodyHtml += '<tr>';
            for (let i=0; i<h.length; i++) {
                const val = (i < cells.length) ? cells[i] : '';
                tbodyHtml += `<td>${escapeHtml(val)}</td>`;
            }
            tbodyHtml += '</tr>';
        });
        if (totalRows > maxRows) {
            tbodyHtml += `<tr><td colspan="${h.length}" style="text-align:center;color:#6a7f9b;font-style:italic;padding:12px;">… and ${totalRows - maxRows} more rows</td></tr>`;
        }
        previewBody.innerHTML = tbodyHtml;
        rowCount.textContent = totalRows + ' row' + (totalRows !== 1 ? 's' : '');
        previewSection.classList.add('visible');
        updateSteps(3);
    }

    function escapeHtml(str) { const div = document.createElement('div'); div.textContent = str; return div.innerHTML; }

    function renderEditTable(invalid, dataRows, headersArray, invIdx) {
        if (invalid.length === 0) { editSection.classList.remove('visible'); return; }
        invalidRows = invalid.map(r => ({ ...r, valid: false, corrected: r.value }));
        allRowsData = dataRows; headers = headersArray; invoiceColumnIndex = invIdx;
        let html = '';
        invalidRows.forEach((r, idx) => {
            const rowClass = r.valid ? 'row-valid' : 'row-invalid';
            const inputClass = r.valid ? 'edit-input valid' : 'edit-input invalid';
            const icon = r.valid ? '✅' : '⚠️';
            html += `<tr class="${rowClass}" data-index="${idx}">
                <td>${r.row}</td>
                <td>${escapeHtml(r.value)}</td>
                <td><input type="text" class="${inputClass}" value="${escapeHtml(r.corrected)}" data-index="${idx}" /></td>
                <td class="status-icon">${icon}</td>
            </tr>`;
        });
        editBody.innerHTML = html;
        editSection.classList.add('visible');
        const invalidCount = invalidRows.filter(r => !r.valid).length;
        invalidBadge.textContent = invalidCount + ' invalid';
        if (invalidCount === 0) {
            invalidBadge.style.display = 'none';
            validBadge.style.display = 'inline';
            editStatusMsg.className = 'edit-status-msg success';
            editStatusMsg.innerHTML = '<strong>✅ All invoices are valid!</strong> You can now upload.';
            isAllValid = true;
            uploadBtn.disabled = false;
        } else {
            invalidBadge.style.display = 'inline';
            validBadge.style.display = 'none';
            editStatusMsg.className = 'edit-status-msg';
            editStatusMsg.innerHTML = `<strong>⚠️ ${invalidCount} rows still need correction.</strong> The invoice must start with 7 digits, then '/', and the first two digits must be 12-20.`;
            isAllValid = false;
            uploadBtn.disabled = true;
        }
        document.querySelectorAll('.edit-input').forEach(input => {
            input.addEventListener('input', function(e) {
                const idx = parseInt(this.dataset.index);
                const val = this.value.trim();
                const isValid = isValidInvoice(val);
                invalidRows[idx].corrected = val;
                invalidRows[idx].valid = isValid;
                const tr = this.closest('tr');
                if (isValid) {
                    tr.className = 'row-valid';
                    this.className = 'edit-input valid';
                    tr.querySelector('.status-icon').textContent = '✅';
                } else {
                    tr.className = 'row-invalid';
                    this.className = 'edit-input invalid';
                    tr.querySelector('.status-icon').textContent = '⚠️';
                }
                const invalidCount2 = invalidRows.filter(r => !r.valid).length;
                invalidBadge.textContent = invalidCount2 + ' invalid';
                if (invalidCount2 === 0) {
                    invalidBadge.style.display = 'none';
                    validBadge.style.display = 'inline';
                    editStatusMsg.className = 'edit-status-msg success';
                    editStatusMsg.innerHTML = '<strong>✅ All invoices are valid!</strong> You can now upload.';
                    isAllValid = true;
                    uploadBtn.disabled = false;
                    updateDataRows();
                } else {
                    invalidBadge.style.display = 'inline';
                    validBadge.style.display = 'none';
                    editStatusMsg.className = 'edit-status-msg';
                    editStatusMsg.innerHTML = `<strong>⚠️ ${invalidCount2} rows still need correction.</strong> The invoice must start with 7 digits, then '/', and the first two digits must be 12-20.`;
                    isAllValid = false;
                    uploadBtn.disabled = true;
                    updateDataRows();
                }
            });
        });
    }

    function updateDataRows() {
        invalidRows.forEach(r => {
            const rowIdx = r.row - 2;
            if (rowIdx >= 0 && rowIdx < allRowsData.length && invoiceColumnIndex >= 0 && invoiceColumnIndex < allRowsData[rowIdx].length) {
                allRowsData[rowIdx][invoiceColumnIndex] = r.corrected;
            }
        });
        const headerLine = headers.join(',');
        const dataLines = allRowsData.map(row => row.join(','));
        currentCSV = [headerLine, ...dataLines].join('\n');
    }

    async function handleFile(file) {
        resetUI(false);
        const ext = file.name.split('.').pop().toLowerCase();
        if (!['xls', 'xlsx', 'xlsm', 'csv'].includes(ext)) {
            showStatus('Please select a valid .xls, .xlsx, .xlsm, or .csv file.', 'error');
            return;
        }
        currentFile = file;
        updateFileInfo(file);
        try {
            showStatus('Reading file…', 'info');
            const workbook = await parseFile(file);
            workbookData = workbook;
            sheetNames = workbook.SheetNames;
            if (sheetNames.length === 0) { showStatus('The file does not contain any sheets.', 'error'); return; }
            populateSheetSelector(sheetNames);
            actions.classList.add('visible');
            uploadSection.classList.add('visible');
            hideStatus();
            showStatus('File loaded. Select a sheet and click "Convert".', 'info');
            if (sheetNames.length === 1) setTimeout(() => convertBtn.click(), 200);
        } catch (err) {
            console.error(err);
            showStatus('Error reading file: ' + err.message, 'error');
        }
    }

    function performConversion() {
        if (!workbookData || !sheetNames.length) { showStatus('No file loaded. Drop a file first.', 'error'); return; }
        const selectedSheet = sheetSelect.value;
        if (!selectedSheet) { showStatus('Please select a sheet.', 'error'); return; }
        try {
            const csv = sheetToCSV(workbookData, selectedSheet);
            if (!csv || csv.trim() === '') { showStatus('The selected sheet is empty.', 'error'); return; }
            currentCSV = csv; currentSheetName = selectedSheet;
            const lines = csv.split('\n').filter(l => l.trim() !== '');
            if (lines.length === 0) { showStatus('No data found.', 'error'); return; }
            const h = parseCSVLine(lines[0]);
            const invIdx = getInvoiceColumnIndex(h);
            if (invIdx === -1) { showStatus('Could not find "InvoiceNo_str" column.', 'error'); return; }
            headers = h; invoiceColumnIndex = invIdx;
            const dataRows = lines.slice(1).map(line => parseCSVLine(line));
            allRowsData = dataRows;
            const invalid = [];
            dataRows.forEach((row, idx) => {
                const invVal = (invIdx < row.length) ? row[invIdx] : '';
                if (!isValidInvoice(invVal)) invalid.push({ row: idx + 2, value: invVal, corrected: invVal, valid: false });
            });
            renderPreview(csv);
            if (invalid.length > 0) {
                invalidRows = invalid;
                renderEditTable(invalid, dataRows, h, invIdx);
                downloadBtn.disabled = false;
                convertBtn.disabled = true;
                uploadBtn.disabled = true;
                showStatus('⚠️ Invalid invoices found. Correct them below.', 'error');
                updateSteps(3);
            } else {
                editSection.classList.remove('visible');
                downloadBtn.disabled = false;
                convertBtn.disabled = true;
                uploadBtn.disabled = false;
                showStatus('✅ All invoices are valid! Click "Upload to Database".', 'success');
                updateSteps(4);
            }
        } catch (err) {
            console.error(err);
            showStatus('Error converting: ' + err.message, 'error');
        }
    }

    async function uploadToDatabase() {
        if (isUploading) return;
        if (!currentCSV.trim()) { showStatus('No CSV data. Convert a sheet first.', 'warning'); return; }
        if (invalidRows.some(r => !r.valid) && editSection.classList.contains('visible')) {
            showStatus('Please correct all invalid invoices.', 'warning');
            return;
        }
        isUploading = true;
        uploadBtn.disabled = true;
        uploadBtn.classList.add('loading');
        progressWrap.classList.add('visible');
        setProgress(10);
        showStatus('Uploading CSV data…', 'info');
        try {
            const formData = new FormData();
            const csvBlob = new Blob([currentCSV], { type: 'text/csv' });
            const fileNameBase = currentFile ? currentFile.name.replace(/\.[^.]+$/, '') : 'receipts';
            const csvFile = new File([csvBlob], fileNameBase + '_' + currentSheetName + '.csv', { type: 'text/csv' });
            formData.append('csv_file', csvFile);
            formData.append('facility', inputFacility.value.trim());
            formData.append('district', inputDistrict.value.trim());
            formData.append('uploaded_by', inputUploader.value.trim());
            formData.append('del_point', inputDelPoint.value.trim());
            formData.append('sheet_name', currentSheetName);

            setProgress(30);
            const response = await fetch(window.location.href, { method: 'POST', body: formData });
            setProgress(80);
            const result = await response.json();
            setProgress(100);
            if (result.success) {
                showStatus('✅ ' + result.message + ' (' + (result.inserted || 0) + ' rows inserted)', 'success');
                uploadBtn.disabled = true;
                uploadBtn.innerHTML = '✓ Uploaded';
                uploadStatus.textContent = 'Upload successful. Next upload allowed after 30 days.';
                fileInput.disabled = true;
                convertBtn.disabled = true;
                downloadBtn.disabled = true;
            } else {
                showStatus('❌ ' + (result.message || 'Upload failed.'), 'error');
                uploadBtn.disabled = false;
                uploadBtn.classList.remove('loading');
            }
        } catch (err) {
            console.error(err);
            showStatus('⚠️ Network error: ' + err.message, 'error');
            uploadBtn.disabled = false;
            uploadBtn.classList.remove('loading');
        } finally {
            isUploading = false;
            if (!uploadBtn.disabled) uploadBtn.classList.remove('loading');
        }
    }

    function downloadCSV() {
        if (!currentCSV) { showStatus('No CSV data. Convert first.', 'error'); return; }
        const baseName = currentFile ? currentFile.name.replace(/\.[^.]+$/, '') : 'output';
        const sheetSuffix = currentSheetName ? '_' + currentSheetName.replace(/\s+/g, '_') : '';
        const fname = baseName + sheetSuffix + '.csv';
        const blob = new Blob(['\uFEFF' + currentCSV], { type: 'text/csv;charset=utf-8;' });
        const url = URL.createObjectURL(blob);
        const link = document.createElement('a');
        link.href = url; link.download = fname;
        document.body.appendChild(link); link.click(); document.body.removeChild(link);
        URL.revokeObjectURL(url);
        showStatus('📥 Downloaded ' + fname, 'success');
    }

    function resetAll() {
        resetUI(false);
        hideStatus();
        currentFile = null; workbookData = null; sheetNames = []; currentCSV = ''; currentSheetName = '';
        downloadBtn.disabled = true; convertBtn.disabled = false; uploadBtn.disabled = true;
        uploadBtn.classList.remove('loading');
        uploadBtn.innerHTML = '<span class="btn-text">⬆ Upload to Database</span><span class="spinner"></span>';
        fileInput.value = ''; fileInput.disabled = false;
        sheetSelect.innerHTML = ''; previewHead.innerHTML = ''; previewBody.innerHTML = ''; rowCount.textContent = '0 rows';
        editBody.innerHTML = ''; invalidRows = []; allRowsData = []; headers = []; invoiceColumnIndex = -1; isAllValid = false;
        dropZone.classList.remove('has-file'); fileInfo.classList.remove('visible'); sheetSelector.classList.remove('visible');
        actions.classList.remove('visible'); uploadSection.classList.remove('visible'); previewSection.classList.remove('visible');
        editSection.classList.remove('visible'); progressWrap.classList.remove('visible'); setProgress(0);
        statusMsg.className = 'status-msg'; isUploading = false; updateSteps(1);
        uploadStatus.textContent = 'Ready to upload.';
        uploadStatus.style.display = 'block';
    }

    // Event listeners
    fileInput.addEventListener('change', function(e) { if (this.files && this.files.length) handleFile(this.files[0]); this.value = ''; });
    dropZone.addEventListener('dragover', function(e) { e.preventDefault(); this.classList.add('dragover'); });
    dropZone.addEventListener('dragleave', function(e) { e.preventDefault(); this.classList.remove('dragover'); });
    dropZone.addEventListener('drop', function(e) { e.preventDefault(); this.classList.remove('dragover'); if (e.dataTransfer.files && e.dataTransfer.files.length) handleFile(e.dataTransfer.files[0]); });
    removeFileBtn.addEventListener('click', function(e) { e.stopPropagation(); resetAll(); });
    convertBtn.addEventListener('click', performConversion);
    downloadBtn.addEventListener('click', downloadCSV);
    resetBtn.addEventListener('click', resetAll);
    sheetSelect.addEventListener('change', function() { if (workbookData && sheetNames.length) performConversion(); });
    uploadBtn.addEventListener('click', uploadToDatabase);
    document.addEventListener('keydown', function(e) { if (e.key === 'Enter' && e.target.tagName !== 'INPUT' && e.target.tagName !== 'SELECT') { if (actions.classList.contains('visible') && !convertBtn.disabled) convertBtn.click(); } });

    resetAll();
    console.log('📊 Receipt Uploader (standalone) ready.');
})();
</script>
</body>
</html>