<?php
// filename: upload_reconciliation.php (Fixed activity_log INSERT column count)
session_start();
include 'layouts/dbconn.php';
include 'layouts/session.php';

if (!isset($_SESSION['user_id'])) {
    header('Location: login.php');
    exit();
}

$allowed_roles = ['SUPER_ADMIN', 'HOSPITAL_ADMIN', 'BILLING_CLERK', 'FINANCE_MANAGER'];
if (!in_array($_SESSION['role_code'], $allowed_roles)) {
    header('Location: dashboard.php');
    exit();
}

$user_id = $_SESSION['user_id'];
$message = '';
$error = '';
$description = ''; // For form persistence
$process_mode = 'manual'; // Default
$insurance_provider_id = 0;

// Handle form submission
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    if (isset($_FILES['insurance_file']) && $_FILES['insurance_file']['error'] === UPLOAD_ERR_OK) {
        $file = $_FILES['insurance_file'];
        $file_type = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
       
        if (!in_array($file_type, ['csv'])) { // Limit to CSV for simplicity; add XLSX later
            $error = 'Only CSV files are supported currently.';
        } elseif ($file['size'] > 10 * 1024 * 1024) {
            $error = 'File too large. Maximum 10MB.';
        } else {
            $upload_dir = 'uploads/reconciliations/';
            if (!is_dir($upload_dir)) {
                mkdir($upload_dir, 0755, true);
            }
           
            $file_name = 'recon_' . date('Ymd_His') . '_' . $user_id . '.' . $file_type;
            $file_path = $upload_dir . $file_name;
           
            if (move_uploaded_file($file['tmp_name'], $file_path)) {
                $process_mode = $_POST['process_mode'] ?? 'manual';
                $insurance_provider_id = (int)$_POST['insurance_provider'];
                $description = trim($_POST['description'] ?? 'Insurance Bill Reconciliation');
               
                if ($insurance_provider_id <= 0) {
                    $error = 'Please select an insurance provider.';
                } else {
                    // Generate reconciliation number
                    $today = date('Ymd');
                    $max_sql = "SELECT COALESCE(MAX(CAST(SUBSTRING(reconciliation_number, -4) AS UNSIGNED)), 0) + 1 as next_num
                                FROM reconciliations WHERE reconciliation_number LIKE 'RECON-$today-%'";
                    $max_result = $conn->query($max_sql);
                    $next_num = $max_result->fetch_assoc()['next_num'];
                    $recon_number = 'RECON-' . $today . '-' . str_pad($next_num, 4, '0', STR_PAD_LEFT);
                   
                    $reconciliation_date = date('Y-m-d H:i:s');
                    $records_uploaded = 0;
                   
                    $insert_sql = "INSERT INTO reconciliations (reconciliation_number, description, insurance_provider_id, file_path, file_type, records_uploaded, process_mode, reconciled_by, reconciliation_date, status)
                                   VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'PENDING')";
                   
                    $stmt = $conn->prepare($insert_sql);
                    $stmt->bind_param('ssisssiss', $recon_number, $description, $insurance_provider_id, $file_path, strtoupper($file_type), $records_uploaded, $process_mode, $user_id, $reconciliation_date);
                    if ($stmt->execute()) {
                        $reconciliation_id = $conn->insert_id;
                       
                        // Parse CSV and insert into reconciliation_uploaded_records
                        $handle = fopen($file_path, 'r');
                        if ($handle !== FALSE) {
                            $header = fgetcsv($handle); // Skip header row
                            $match_count = 0;
                            $discrepancy_count = 0;
                           
                            while (($row = fgetcsv($handle)) !== FALSE) {
                                if (count($row) < 5) continue; // Skip invalid rows
                               
                                $claim_number = trim($row[0] ?? '');
                                $patient_number = trim($row[1] ?? '');
                                $amount = (float)($row[2] ?? 0);
                                $claim_date = $row[3] ?? null;
                                $status = trim($row[4] ?? '');
                                $policy_no = trim($row[5] ?? '');
                                $service_desc = $row[6] ?? '';
                               
                                if (empty($claim_number) || $amount <= 0) continue;
                               
                                // Insert uploaded record
                                $uploaded_sql = "INSERT INTO reconciliation_uploaded_records (reconciliation_id, claim_number, patient_number, amount, claim_date, status, insurance_policy_no, service_description, raw_row_data)
                                                 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
                                $uploaded_stmt = $conn->prepare($uploaded_sql);
                                $raw_row = json_encode($row);
                                $uploaded_stmt->bind_param('issdsssss', $reconciliation_id, $claim_number, $patient_number, $amount, $claim_date, $status, $policy_no, $service_desc, $raw_row);
                                $uploaded_stmt->execute();
                               
                                $records_uploaded++;
                               
                                // Simple matching simulation (expand with real query)
                                // Query system for match: SELECT * FROM invoices i JOIN insurance_claims ic ON i.invoice_id = ic.invoice_id WHERE ic.claim_number = ? AND i.grand_total = ?
                                $match_sql = "SELECT i.invoice_id, ic.claim_id FROM invoices i LEFT JOIN insurance_claims ic ON i.invoice_id = ic.invoice_id WHERE i.invoice_number = ? LIMIT 1";
                                $match_stmt = $conn->prepare($match_sql);
                                $match_stmt->bind_param('s', $claim_number);
                                $match_stmt->execute();
                                $match_res = $match_stmt->get_result()->fetch_assoc();
                               
                                if ($match_res) {
                                    // Match found
                                    $insert_match_sql = "INSERT INTO reconciliation_matches (reconciliation_id, invoice_id, claim_id, uploaded_claim_number, amount, match_confidence) VALUES (?, ?, ?, ?, ?, 100.00)";
                                    $match_insert = $conn->prepare($insert_match_sql);
                                    $match_insert->bind_param('iiisd', $reconciliation_id, $match_res['invoice_id'], $match_res['claim_id'], $claim_number, $amount);
                                    $match_insert->execute();
                                    $match_count++;
                                } else {
                                    // Discrepancy
                                    $disc_type = 'CLAIM_NOT_FOUND'; // Or AMOUNT_MISMATCH if partial match
                                    $disc_sql = "INSERT INTO reconciliation_discrepancies (reconciliation_id, uploaded_claim_number, discrepancy_type, expected_amount, actual_amount, notes) VALUES (?, ?, ?, ?, ?, 'No matching invoice/claim found')";
                                    $disc_stmt = $conn->prepare($disc_sql);
                                    $disc_stmt->bind_param('issdd', $reconciliation_id, $claim_number, $disc_type, $amount, $amount);
                                    $disc_stmt->execute();
                                    $discrepancy_count++;
                                }
                            }
                            fclose($handle);
                           
                            // Update reconciliation totals
                            $update_sql = "UPDATE reconciliations SET records_uploaded = ?, total_matches = ?, total_discrepancies = ? WHERE reconciliation_id = ?";
                            $update_stmt = $conn->prepare($update_sql);
                            $update_stmt->bind_param('iiii', $records_uploaded, $match_count, $discrepancy_count, $reconciliation_id);
                            $update_stmt->execute();
                           
                            // Log activity - Fixed: Added literal for record_type to match column count
                            $log_sql = "INSERT INTO activity_log (user_id, action, module, record_id, record_type, new_values) VALUES (?, 'CREATE', 'RECONCILIATION', ?, 'RECONCILIATION', ?)";
                            $log_values = json_encode(['number' => $recon_number, 'status' => 'PENDING', 'matches' => $match_count, 'discrepancies' => $discrepancy_count]);
                            $log_stmt = $conn->prepare($log_sql);
                            $log_stmt->bind_param('iis', $user_id, $reconciliation_id, $log_values);
                            $log_stmt->execute();
                           
                            if ($process_mode === 'AUTOMATED') {
                                $update_status_sql = "UPDATE reconciliations SET status = 'COMPLETED' WHERE reconciliation_id = ?";
                                $status_stmt = $conn->prepare($update_status_sql);
                                $status_stmt->bind_param('i', $reconciliation_id);
                                $status_stmt->execute();
                            }
                           
                            $message = "File uploaded and processed! $records_uploaded records parsed. Matches: $match_count | Discrepancies: $discrepancy_count";
                        } else {
                            $error = 'Failed to read uploaded file.';
                        }
                    } else {
                        $error = 'Failed to create reconciliation record.';
                    }
                }
            } else {
                $error = 'Failed to upload file.';
            }
        }
    } else {
        $error = 'Please select a file to upload.';
    }
}
// Fetch insurance providers
$ip_sql = "SELECT provider_id, provider_name FROM insurance_providers ORDER BY provider_name";
$ip_result = $conn->query($ip_sql);
?>
<!-- HTML remains the same as before -->
<?php include 'layouts/main.php'; ?>
<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Upload Reconciliation')); ?>
    <?php include 'layouts/head-css.php'; ?>
    <link href="assets/libs/sweetalert2/sweetalert2.min.css" rel="stylesheet">
</head>
<body>
    <!-- Body content same as previous -->
    <div id="layout-wrapper">
        <?php include 'layouts/menu.php'; ?>
       
        <div class="main-content">
            <div class="page-content">
                <div class="container-fluid">
                    <div class="row">
                        <div class="col-12">
                            <div class="page-title-box d-sm-flex align-items-center justify-content-between">
                                <h4 class="mb-sm-0">Upload Insurance Reconciliation</h4>
                                <div class="page-title-right">
                                    <ol class="breadcrumb m-0">
                                        <li class="breadcrumb-item"><a href="dashboard.php">Dashboard</a></li>
                                        <li class="breadcrumb-item"><a href="reconciliation.php">Reconciliations</a></li>
                                        <li class="breadcrumb-item active">Upload</li>
                                    </ol>
                                </div>
                            </div>
                        </div>
                    </div>
                    <div class="row">
                        <div class="col-lg-8 mx-auto">
                            <div class="card recon-card">
                                <div class="card-header recon-header">
                                    <h5 class="mb-0 text-white"><i class="ri-upload-cloud-2-line me-2"></i>Upload Insurance Claims File</h5>
                                </div>
                                <div class="card-body">
                                    <?php if ($message): ?>
                                    <div class="alert alert-success alert-dismissible fade show" role="alert">
                                        <?php echo htmlspecialchars($message); ?>
                                        <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
                                    </div>
                                    <?php endif; ?>
                                   
                                    <?php if ($error): ?>
                                    <div class="alert alert-danger alert-dismissible fade show" role="alert">
                                        <?php echo htmlspecialchars($error); ?>
                                        <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
                                    </div>
                                    <?php endif; ?>
                                   
                                    <form method="POST" enctype="multipart/form-data">
                                        <div class="mb-3">
                                            <label class="form-label">Insurance Provider <span class="text-danger">*</span></label>
                                            <select class="form-select" name="insurance_provider" required>
                                                <option value="">Select Provider</option>
                                                <?php $ip_result->data_seek(0); while ($ip = $ip_result->fetch_assoc()): ?>
                                                <option value="<?php echo $ip['provider_id']; ?>" <?php echo $insurance_provider_id == $ip['provider_id'] ? 'selected' : ''; ?>>
                                                    <?php echo htmlspecialchars($ip['provider_name']); ?>
                                                </option>
                                                <?php endwhile; ?>
                                            </select>
                                        </div>
                                       
                                        <div class="mb-3">
                                            <label class="form-label">Description</label>
                                            <input type="text" class="form-control" name="description" placeholder="e.g., Monthly claims for ABC Insurance - November 2025" maxlength="255" value="<?php echo htmlspecialchars($description); ?>">
                                        </div>
                                       
                                        <div class="mb-3">
                                            <label class="form-label">Insurance Claims File <span class="text-danger">*</span></label>
                                            <input type="file" class="form-control" name="insurance_file" accept=".csv" required>
                                            <div class="form-text">CSV with columns: claim_number, patient_number, amount, claim_date, status, policy_no, service_description. Max 10MB.</div>
                                        </div>
                                       
                                        <div class="mb-3">
                                            <label class="form-label">Process Mode</label>
                                            <div class="form-check">
                                                <input class="form-check-input" type="radio" name="process_mode" id="manual" value="manual" <?php echo $process_mode === 'manual' ? 'checked' : ''; ?>>
                                                <label class="form-check-label" for="manual">Manual (Review in details modal)</label>
                                            </div>
                                            <div class="form-check">
                                                <input class="form-check-input" type="radio" name="process_mode" id="automated" value="automated" <?php echo $process_mode === 'automated' ? 'checked' : ''; ?>>
                                                <label class="form-check-label" for="automated">Automated (Auto-match & flag)</label>
                                            </div>
                                        </div>
                                       
                                        <div class="d-flex gap-2">
                                            <button type="submit" class="btn btn-primary">
                                                <i class="ri-upload-line me-1"></i>Upload & Process
                                            </button>
                                            <a href="reconciliation.php" class="btn btn-secondary">
                                                <i class="ri-arrow-left-line me-1"></i>Back to List
                                            </a>
                                        </div>
                                    </form>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
            <?php include 'layouts/footer.php'; ?>
        </div>
    </div>
    <?php include 'layouts/vendor-scripts.php'; ?>
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script src="assets/libs/sweetalert2/sweetalert2.min.js"></script>
    <script src="assets/js/app.js"></script>
   
    <script>
        $('form').on('submit', function(e) {
            Swal.fire({
                title: 'Process File?',
                text: 'This will parse CSV and match against system records.',
                icon: 'question',
                showCancelButton: true,
                confirmButtonText: 'Yes, process!'
            }).then((result) => {
                if (!result.isConfirmed) e.preventDefault();
            });
        });
    </script>
</body>
</html>