<?php
// filename: export_recon_report.php (Fixed: Use patient_number or CONCAT names; schema-safe)
session_start();
include 'layouts/dbconn.php';

if (!isset($_GET['id']) || !is_numeric($_GET['id'])) {
    die('Invalid reconciliation ID.');
}

$reconciliation_id = (int)$_GET['id'];

// Fetch reconciliation details
$sql = "SELECT r.*, u.full_name as reconciler_name, ip.provider_name 
        FROM reconciliations r 
        LEFT JOIN users u ON r.reconciled_by = u.user_id 
        LEFT JOIN insurance_providers ip ON r.insurance_provider_id = ip.provider_id 
        WHERE r.reconciliation_id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param('i', $reconciliation_id);
$stmt->execute();
$recon = $stmt->get_result()->fetch_assoc();

if (!$recon) {
    die('Reconciliation not found.');
}

// Fetch matches - Fixed: Use CONCAT for patient_name (schema-safe: assume first_name/last_name or fallback to patient_number)
$matches_sql = "SELECT rm.*, i.invoice_number, ic.claim_number as system_claim_number, p.patient_number, 
                       COALESCE(CONCAT(COALESCE(p.first_name, ''), ' ', COALESCE(p.last_name, '')), p.patient_number) as patient_name 
                FROM reconciliation_matches rm 
                LEFT JOIN invoices i ON rm.invoice_id = i.invoice_id 
                LEFT JOIN insurance_claims ic ON rm.claim_id = ic.claim_id
                LEFT JOIN patients p ON COALESCE(i.patient_id, ic.patient_id) = p.patient_id
                WHERE rm.reconciliation_id = ?";
$matches_stmt = $conn->prepare($matches_sql);
$matches_stmt->bind_param('i', $reconciliation_id);
$matches_stmt->execute();
$matches_result = $matches_stmt->get_result();

$discrepancies_sql = "SELECT rd.*, i.invoice_number, ic.claim_number as system_claim_number, p.patient_number, 
                             COALESCE(CONCAT(COALESCE(p.first_name, ''), ' ', COALESCE(p.last_name, '')), p.patient_number) as patient_name, 
                             rd.discrepancy_type, rd.expected_amount, rd.actual_amount, rd.notes 
                      FROM reconciliation_discrepancies rd 
                      LEFT JOIN invoices i ON rd.invoice_id = i.invoice_id 
                      LEFT JOIN insurance_claims ic ON rd.claim_id = ic.claim_id
                      LEFT JOIN patients p ON COALESCE(i.patient_id, ic.patient_id) = p.patient_id
                      WHERE rd.reconciliation_id = ?";
$discrepancies_stmt = $conn->prepare($discrepancies_sql);
$discrepancies_stmt->bind_param('i', $reconciliation_id);
$discrepancies_stmt->execute();
$discrepancies_result = $discrepancies_stmt->get_result();

header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="reconciliation_report_' . $recon['reconciliation_number'] . '_' . date('Ymd') . '.csv"');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');

$output = fopen('php://output', 'w');

// Summary Section
fputcsv($output, ['=== RECONCILIATION REPORT ===']);
fputcsv($output, ['Reconciliation Number', $recon['reconciliation_number']]);
fputcsv($output, ['Date', $recon['reconciliation_date']]);
fputcsv($output, ['Provider', $recon['provider_name'] ?? 'N/A']);
fputcsv($output, ['Reconciler', $recon['reconciler_name'] ?? 'N/A']);
fputcsv($output, ['Status', $recon['status']]);
fputcsv($output, ['Total Records', $recon['records_uploaded']]);
fputcsv($output, ['Total Matches', $recon['total_matches']]);
fputcsv($output, ['Total Discrepancies', $recon['total_discrepancies']]);
fputcsv($output, []); // Empty row

// Matches Section
fputcsv($output, ['=== MATCHED INVOICES/CLAIMS ===']);
fputcsv($output, ['Invoice Number', 'System Claim Number', 'Uploaded Claim Number', 'Patient Number/Name', 'Amount (KES)', 'Match Confidence (%)']);
while ($match = $matches_result->fetch_assoc()) {
    fputcsv($output, [
        $match['invoice_number'] ?? 'N/A',
        $match['system_claim_number'] ?? 'N/A',
        $match['uploaded_claim_number'] ?? 'N/A',
        $match['patient_name'] ?? 'N/A',
        number_format($match['amount'], 2),
        number_format($match['match_confidence'] ?? 100, 2)
    ]);
}
fputcsv($output, []); // Empty row

// Discrepancies Section
fputcsv($output, ['=== DISCREPANCIES ===']);
fputcsv($output, ['Invoice Number', 'System Claim Number', 'Uploaded Claim Number', 'Patient Number/Name', 'Discrepancy Type', 'Expected Amount (KES)', 'Actual Amount (KES)', 'Notes', 'Resolved?']);
while ($disc = $discrepancies_result->fetch_assoc()) {
    fputcsv($output, [
        $disc['invoice_number'] ?? 'N/A',
        $disc['system_claim_number'] ?? 'N/A',
        $disc['uploaded_claim_number'] ?? 'N/A',
        $disc['patient_name'] ?? 'N/A',
        $disc['discrepancy_type'],
        number_format($disc['expected_amount'] ?? 0, 2),
        number_format($disc['actual_amount'] ?? 0, 2),
        $disc['notes'] ?? '',
        $disc['resolved'] ? 'Yes' : 'No'
    ]);
}

fclose($output);
exit();
?>