<?php
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', 'ACCOUNTANT'];
if (!in_array($_SESSION['role_code'], $allowed_roles)) {
    header('Location: ../index.php');
    exit();
}

// Get date range from URL or use defaults
$start_date = isset($_GET['start_date']) ? $_GET['start_date'] : date('Y-m-01');
$end_date = isset($_GET['end_date']) ? $_GET['end_date'] : date('Y-m-d');

// Fetch reconciliation data
$reconciliation_query = "SELECT 
    i.invoice_id,
    i.invoice_number,
    i.invoice_date,
    i.total_amount,
    i.amount_paid,
    i.balance,
    i.status as invoice_status,
    u.full_name as patient_name,
    pat.patient_number,
    COUNT(p.payment_id) as payment_count,
    GROUP_CONCAT(
        CONCAT(p.payment_number, ' (KES ', FORMAT(p.amount, 2), ')')
        ORDER BY p.payment_date 
        SEPARATOR ', '
    ) as payments_list
FROM invoices i
JOIN patients pat ON i.patient_id = pat.patient_id
JOIN users u ON pat.user_id = u.user_id
LEFT JOIN payments p ON i.invoice_id = p.invoice_id AND p.status = 'COMPLETED'
WHERE i.invoice_date BETWEEN '$start_date' AND '$end_date'
GROUP BY i.invoice_id
ORDER BY i.invoice_date DESC, i.invoice_number";

$result = mysqli_query($conn, $reconciliation_query);

// Calculate summary statistics
$summary_query = "SELECT 
    COUNT(DISTINCT i.invoice_id) as total_invoices,
    SUM(i.total_amount) as total_invoiced,
    SUM(i.amount_paid) as total_paid,
    SUM(i.balance) as total_outstanding,
    COUNT(CASE WHEN i.status = 'PAID' THEN 1 END) as fully_paid_count,
    COUNT(CASE WHEN i.status = 'PARTIAL' THEN 1 END) as partially_paid_count,
    COUNT(CASE WHEN i.status = 'PENDING' THEN 1 END) as unpaid_count,
    COUNT(DISTINCT p.payment_id) as total_payments,
    AVG(DATEDIFF(p.payment_date, i.invoice_date)) as avg_payment_days
FROM invoices i
LEFT JOIN payments p ON i.invoice_id = p.invoice_id AND p.status = 'COMPLETED'
WHERE i.invoice_date BETWEEN '$start_date' AND '$end_date'";

$summary_result = mysqli_query($conn, $summary_query);
$summary = mysqli_fetch_assoc($summary_result);

// Fetch hospital settings for header
$settings_query = "SELECT * FROM hospital_settings LIMIT 1";
$settings_result = mysqli_query($conn, $settings_query);
$hospital = mysqli_fetch_assoc($settings_result);

?>
<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>Reconciliation Report - <?php echo date('d M Y', strtotime($start_date)); ?> to <?php echo date('d M Y', strtotime($end_date)); ?></title>
    <style>
        @media print {
            .no-print { display: none !important; }
            body { margin: 0; }
        }
        
        body {
            font-family: Arial, sans-serif;
            margin: 20px;
            font-size: 12px;
            color: #333;
        }
        
        .header {
            text-align: center;
            margin-bottom: 30px;
            padding-bottom: 20px;
            border-bottom: 3px solid #6366f1;
        }
        
        .header h1 {
            color: #6366f1;
            margin: 10px 0;
            font-size: 24px;
        }
        
        .header p {
            margin: 5px 0;
            color: #64748b;
        }
        
        .report-info {
            background: #f8fafc;
            padding: 15px;
            border-radius: 8px;
            margin-bottom: 20px;
        }
        
        .info-grid {
            display: grid;
            grid-template-columns: repeat(2, 1fr);
            gap: 10px;
        }
        
        .info-item {
            display: flex;
            justify-content: space-between;
            padding: 5px 0;
        }
        
        .info-label {
            font-weight: bold;
            color: #64748b;
        }
        
        .summary-cards {
            display: grid;
            grid-template-columns: repeat(4, 1fr);
            gap: 15px;
            margin-bottom: 30px;
        }
        
        .summary-card {
            background: white;
            padding: 15px;
            border-radius: 8px;
            border: 1px solid #e2e8f0;
            text-align: center;
        }
        
        .summary-card h3 {
            margin: 0 0 5px 0;
            font-size: 24px;
            color: #6366f1;
        }
        
        .summary-card p {
            margin: 0;
            color: #64748b;
            font-size: 11px;
        }
        
        table {
            width: 100%;
            border-collapse: collapse;
            margin-bottom: 20px;
        }
        
        th {
            background: #6366f1;
            color: white;
            padding: 10px;
            text-align: left;
            font-weight: 600;
        }
        
        td {
            padding: 8px 10px;
            border-bottom: 1px solid #e2e8f0;
        }
        
        tr:hover {
            background: #f8fafc;
        }
        
        .status-badge {
            padding: 4px 8px;
            border-radius: 4px;
            font-size: 10px;
            font-weight: 600;
            display: inline-block;
        }
        
        .status-paid { background: #d1fae5; color: #047857; }
        .status-partial { background: #fef3c7; color: #92400e; }
        .status-pending { background: #fee2e2; color: #991b1b; }
        
        .text-success { color: #059669; font-weight: 600; }
        .text-danger { color: #dc2626; font-weight: 600; }
        .text-warning { color: #f59e0b; font-weight: 600; }
        
        .footer {
            margin-top: 30px;
            padding-top: 20px;
            border-top: 2px solid #e2e8f0;
            text-align: center;
            color: #64748b;
            font-size: 10px;
        }
        
        .btn {
            padding: 10px 20px;
            border: none;
            border-radius: 6px;
            cursor: pointer;
            font-weight: 600;
            margin: 5px;
        }
        
        .btn-primary {
            background: #6366f1;
            color: white;
        }
        
        .btn-secondary {
            background: #64748b;
            color: white;
        }
        
        .actions {
            margin-bottom: 20px;
        }
    </style>
</head>
<body>
    <div class="actions no-print">
        <button onclick="window.print()" class="btn btn-primary">🖨️ Print Report</button>
        <button onclick="window.close()" class="btn btn-secondary">✖ Close</button>
    </div>

    <div class="header">
        <h1><?php echo htmlspecialchars($hospital['hospital_name'] ?? 'ENT Hospital'); ?></h1>
        <p><?php echo htmlspecialchars($hospital['address'] ?? ''); ?></p>
        <p><?php echo htmlspecialchars($hospital['phone'] ?? ''); ?> | <?php echo htmlspecialchars($hospital['email'] ?? ''); ?></p>
        <h2 style="color: #6366f1; margin-top: 20px;">Payment Reconciliation Report</h2>
    </div>

    <div class="report-info">
        <div class="info-grid">
            <div class="info-item">
                <span class="info-label">Report Period:</span>
                <span><?php echo date('d M Y', strtotime($start_date)); ?> - <?php echo date('d M Y', strtotime($end_date)); ?></span>
            </div>
            <div class="info-item">
                <span class="info-label">Generated On:</span>
                <span><?php echo date('d M Y, h:i A'); ?></span>
            </div>
            <div class="info-item">
                <span class="info-label">Generated By:</span>
                <span><?php echo htmlspecialchars($_SESSION['full_name']); ?></span>
            </div>
            <div class="info-item">
                <span class="info-label">Total Invoices:</span>
                <span><?php echo number_format($summary['total_invoices'] ?? 0); ?></span>
            </div>
        </div>
    </div>

    <div class="summary-cards">
        <div class="summary-card">
            <h3>KES <?php echo number_format($summary['total_invoiced'] ?? 0, 2); ?></h3>
            <p>TOTAL INVOICED</p>
        </div>
        <div class="summary-card">
            <h3 class="text-success">KES <?php echo number_format($summary['total_paid'] ?? 0, 2); ?></h3>
            <p>TOTAL PAID</p>
        </div>
        <div class="summary-card">
            <h3 class="text-danger">KES <?php echo number_format($summary['total_outstanding'] ?? 0, 2); ?></h3>
            <p>OUTSTANDING</p>
        </div>
        <div class="summary-card">
            <h3><?php echo number_format($summary['total_payments'] ?? 0); ?></h3>
            <p>TOTAL PAYMENTS</p>
        </div>
    </div>

    <table>
        <thead>
            <tr>
                <th>Invoice #</th>
                <th>Date</th>
                <th>Patient</th>
                <th>Total</th>
                <th>Paid</th>
                <th>Balance</th>
                <th>Payments</th>
                <th>Status</th>
            </tr>
        </thead>
        <tbody>
            <?php if (mysqli_num_rows($result) > 0): ?>
                <?php while ($row = mysqli_fetch_assoc($result)): ?>
                <tr>
                    <td><strong><?php echo htmlspecialchars($row['invoice_number']); ?></strong></td>
                    <td><?php echo date('d M Y', strtotime($row['invoice_date'])); ?></td>
                    <td>
                        <?php echo htmlspecialchars($row['patient_name']); ?><br>
                        <small style="color: #64748b;"><?php echo htmlspecialchars($row['patient_number']); ?></small>
                    </td>
                    <td>KES <?php echo number_format($row['total_amount'], 2); ?></td>
                    <td class="text-success">KES <?php echo number_format($row['amount_paid'], 2); ?></td>
                    <td class="text-danger">KES <?php echo number_format($row['balance'], 2); ?></td>
                    <td>
                        <small><?php echo $row['payment_count']; ?> payment(s)</small><br>
                        <small style="color: #64748b;"><?php echo $row['payments_list'] ? substr($row['payments_list'], 0, 50) . '...' : 'None'; ?></small>
                    </td>
                    <td>
                        <?php
                        $statusClass = 'status-pending';
                        if ($row['invoice_status'] == 'PAID') $statusClass = 'status-paid';
                        elseif ($row['invoice_status'] == 'PARTIAL') $statusClass = 'status-partial';
                        ?>
                        <span class="status-badge <?php echo $statusClass; ?>">
                            <?php echo $row['invoice_status']; ?>
                        </span>
                    </td>
                </tr>
                <?php endwhile; ?>
            <?php else: ?>
                <tr>
                    <td colspan="8" style="text-align: center; padding: 20px;">No invoices found for this period</td>
                </tr>
            <?php endif; ?>
        </tbody>
    </table>

    <div class="summary-cards" style="margin-top: 30px;">
        <div class="summary-card">
            <h3 class="text-success"><?php echo number_format($summary['fully_paid_count'] ?? 0); ?></h3>
            <p>FULLY PAID</p>
        </div>
        <div class="summary-card">
            <h3 class="text-warning"><?php echo number_format($summary['partially_paid_count'] ?? 0); ?></h3>
            <p>PARTIALLY PAID</p>
        </div>
        <div class="summary-card">
            <h3 class="text-danger"><?php echo number_format($summary['unpaid_count'] ?? 0); ?></h3>
            <p>UNPAID</p>
        </div>
        <div class="summary-card">
            <h3><?php echo number_format($summary['avg_payment_days'] ?? 0, 1); ?></h3>
            <p>AVG. DAYS TO PAY</p>
        </div>
    </div>

    <div class="footer">
        <p>This is a computer-generated report from <?php echo htmlspecialchars($hospital['hospital_name'] ?? 'ENT Hospital Management System'); ?></p>
        <p>Generated on <?php echo date('l, d F Y \a\t h:i A'); ?></p>
    </div>

    <?php mysqli_close($conn); ?>
</body>
</html>