<?php
ob_start();
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

session_start();

/* ================= Session Timeout (12 hours) ================= */
$SESSION_TIMEOUT = 43200;
if (isset($_SESSION['LAST_ACTIVITY']) && time() - $_SESSION['LAST_ACTIVITY'] > $SESSION_TIMEOUT) {
    session_unset();
    session_destroy();
    header("Location: login.php?timeout=1");
    exit;
}
$_SESSION['LAST_ACTIVITY'] = time();

include('includes/db.php');

// ---------------- Helper (hide 0 / NULL amounts) ----------------
function fmt_amount($value, $decimals = 2) {
    if ($value === null || (float)$value == 0.0) {
        return '';
    }
    return number_format((float)$value, $decimals);
}

// ---------------- Access Control ----------------
if (!isset($_SESSION['user_role'])) {
    $_SESSION['flash'] = ["message" => "Please log in first!", "color" => "#dc3545"];
    header("Location: login.php");
    exit;
}

$username    = $_SESSION['username'] ?? 'User';
$facility    = $_SESSION['user_facility_name'] ?? 'N/A';
$facility_id = $_SESSION['facility_id'] ?? null;

if (!$facility_id) {
    $_SESSION['flash'] = [
        "message" => "No facility assigned to your account.",
        "color" => "#dc3545"
    ];
    header("Location: dashboard.php");
    exit;
}

// ---------------- Date Filter ----------------
$selectedMonth = $_GET['report_month'] ?? date('Y-m'); // format YYYY-MM
$monthStart = $selectedMonth . '-01';
$monthEnd   = date('Y-m-t', strtotime($monthStart));

// ---------------- Monthly Insurance Report ----------------
$monthlyInsurance = $pdo->prepare("
    SELECT 
        i.name AS insurance_name,
        COUNT(DISTINCT v.id) AS visit_count,  -- CHANGED: Count visits, not patients
        COUNT(DISTINCT v.patient_id) AS patient_count,  -- Keep this if you still want patient count
        COALESCE(SUM(inv.patient_co_pay),0) AS patient_copay,
        COALESCE(SUM(inv.insurance_amount),0) AS insurance_amount,
        COALESCE(SUM(inv.patient_co_pay + inv.insurance_amount),0) AS total_amount
    FROM visits v
    INNER JOIN invoices inv 
        ON v.id = inv.visit_id 
       AND inv.is_removed = 0
    INNER JOIN insurance_partners i 
        ON v.insurance_id = i.id
    WHERE v.visit_date BETWEEN ? AND ?
      AND v.facility_id = ?
    GROUP BY i.id
    HAVING total_amount > 0
    ORDER BY total_amount DESC
");
$monthlyInsurance->execute([$monthStart.' 00:00:00', $monthEnd.' 23:59:59', $facility_id]);
$monthlyInsuranceData = $monthlyInsurance->fetchAll(PDO::FETCH_ASSOC);

// ---------------- Monthly Staff Report ----------------
$monthlyStaff = $pdo->prepare("
    SELECT 
        u.username AS staff_name,
        COUNT(DISTINCT v.id) AS visit_count,  -- ADDED: Count visits for staff
        COUNT(DISTINCT v.patient_id) AS patient_count,  -- ADDED: Count patients for staff
        COALESCE(SUM(inv.patient_co_pay + inv.insurance_amount),0) AS total_amount
    FROM visits v
    INNER JOIN invoices inv ON v.id = inv.visit_id AND inv.is_removed = 0
    INNER JOIN users u ON v.created_by = u.id
    WHERE v.visit_date BETWEEN ? AND ? AND v.facility_id = ?
    GROUP BY u.id
    ORDER BY total_amount DESC
");
$monthlyStaff->execute([$monthStart.' 00:00:00', $monthEnd.' 23:59:59', $facility_id]);
$monthlyStaffData = $monthlyStaff->fetchAll(PDO::FETCH_ASSOC);

// ---------------- Monthly Payment Method Report ----------------
$monthlyPayment = $pdo->prepare("
    SELECT 
        inv.payment_method,
        COUNT(DISTINCT v.id) AS visit_count,  -- CHANGED: Count visits, not patients
        COUNT(DISTINCT v.patient_id) AS patient_count,  -- Keep this if you still want patient count
        COALESCE(SUM(inv.patient_co_pay + inv.insurance_amount),0) AS total_amount
    FROM visits v
    INNER JOIN invoices inv ON v.id = inv.visit_id AND inv.is_removed = 0
    WHERE v.visit_date BETWEEN ? AND ? AND v.facility_id = ?
      AND inv.payment_method IS NOT NULL AND inv.payment_method != ''
    GROUP BY inv.payment_method
    ORDER BY total_amount DESC
");
$monthlyPayment->execute([$monthStart.' 00:00:00', $monthEnd.' 23:59:59', $facility_id]);
$monthlyPaymentData = $monthlyPayment->fetchAll(PDO::FETCH_ASSOC);

include('includes/header.php');
?>

<nav class="header-navbar navbar navbar-with-menu navbar-fixed-top navbar-semi-dark navbar-shadow">
    <?php include('includes/navbar.php'); ?>
</nav>

<div data-scroll-to-active="true" class="main-menu menu-fixed menu-dark menu-accordion menu-shadow">
    <?php include('includes/sidebar.php'); ?>
</div>

<div class="app-content content container-fluid">
<div class="content-wrapper">

    <!-- Header & Month Filter -->
    <div class="content-header row">
        <div class="col-md-12">
            <h3 class="content-header-title">Monthly Insurance & Billing Report</h3>
            <form method="get" class="form-inline mt-2 mb-3">
                <label class="mr-2">Month:</label>
                <input type="month" name="report_month" class="form-control mr-2"
                       value="<?= htmlspecialchars($selectedMonth) ?>">
                <button type="submit" class="btn btn-primary">Show Report</button>
            </form>
        </div>
    </div>

    <div class="content-body">

        <!-- Insurance Table -->
        <div class="card mb-3 shadow-sm">
            <div class="card-body">
                <h5>Insurance Usage for <?= htmlspecialchars($selectedMonth) ?></h5>
                <div class="table-responsive">
                    <table id="monthlyInsuranceTable" class="table table-bordered table-striped">
                        <thead>
                            <tr>
                                <th>Insurance</th>
                                <th>Visits</th>
                                <th>Patient Co-Pay (RWF)</th>
                                <th>Insurance Amount (RWF)</th>
                                <th>Total (RWF)</th>
                            </tr>
                        </thead>
                        <tbody>
                        <?php foreach ($monthlyInsuranceData as $row): ?>
                            <tr>
                                <td><?= htmlspecialchars($row['insurance_name']) ?></td>
                                <td><?= (int)$row['visit_count'] ?></td>
                                <td><?= fmt_amount($row['patient_copay']) ?></td>
                                <td><?= fmt_amount($row['insurance_amount']) ?></td>
                                <td><?= fmt_amount($row['total_amount']) ?></td>
                            </tr>
                        <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>

        <!-- Staff Table -->
        <div class="card mb-3 shadow-sm">
            <div class="card-body">
                <h5>Staff Totals for <?= htmlspecialchars($selectedMonth) ?></h5>
                <div class="table-responsive">
                    <table id="monthlyStaffTable" class="table table-bordered table-striped">
                        <thead>
                            <tr>
                                <th>Staff</th>
                                <th>Visits</th>
                                <th>Total Amount (RWF)</th>
                            </tr>
                        </thead>
                        <tbody>
                        <?php foreach ($monthlyStaffData as $row): ?>
                            <tr>
                                <td><?= htmlspecialchars($row['staff_name']) ?></td>
                                <td><?= (int)$row['visit_count'] ?></td>
                                <td><?= fmt_amount($row['total_amount']) ?></td>
                            </tr>
                        <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>

        <!-- Payment Method Table -->
        <div class="card mb-3 shadow-sm">
            <div class="card-body">
                <h5>Payment Methods for <?= htmlspecialchars($selectedMonth) ?></h5>
                <div class="table-responsive">
                    <table id="monthlyPaymentTable" class="table table-bordered table-striped">
                        <thead>
                            <tr>
                                <th>Payment Method</th>
                                <th>Visits</th>
                                <th>Total Amount (RWF)</th>
                            </tr>
                        </thead>
                        <tbody>
                        <?php foreach ($monthlyPaymentData as $row): ?>
                            <tr>
                                <td><?= htmlspecialchars($row['payment_method']) ?></td>
                                <td><?= (int)$row['visit_count'] ?></td>
                                <td><?= fmt_amount($row['total_amount']) ?></td>
                            </tr>
                        <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>

    </div>
</div>
</div>

<?php include('includes/footer.php'); ?>

<!-- DataTables -->
<link rel="stylesheet" href="https://cdn.datatables.net/1.13.6/css/dataTables.bootstrap5.min.css">
<link rel="stylesheet" href="https://cdn.datatables.net/buttons/2.4.1/css/buttons.bootstrap5.min.css">

<script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.13.6/js/dataTables.bootstrap5.min.js"></script>
<script src="https://cdn.datatables.net/buttons/2.4.1/js/dataTables.buttons.min.js"></script>
<script src="https://cdn.datatables.net/buttons/2.4.1/js/buttons.bootstrap5.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.10.1/jszip.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.2.7/pdfmake.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.2.7/vfs_fonts.js"></script>
<script src="https://cdn.datatables.net/buttons/2.4.1/js/buttons.html5.min.js"></script>
<script src="https://cdn.datatables.net/buttons/2.4.1/js/buttons.print.min.js"></script>

<script>
$(function(){
    $('#monthlyInsuranceTable').DataTable({
        pageLength:10, 
        order:[[5,'desc']],  // Sort by Total column (6th column, 0-indexed)
        dom:'Bfrtip',
        buttons:['copy','csv','excel','pdf','print']
    });
    $('#monthlyStaffTable').DataTable({
        pageLength:10, 
        order:[[3,'desc']],  // Sort by Total Amount column (4th column)
        dom:'Bfrtip',
        buttons:['copy','csv','excel','pdf','print']
    });
    $('#monthlyPaymentTable').DataTable({
        pageLength:10, 
        order:[[3,'desc']],  // Sort by Total Amount column (4th column)
        dom:'Bfrtip',
        buttons:['copy','csv','excel','pdf','print']
    });
});
</script>