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

session_start();
include('includes/db.php');
include('includes/header.php');

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

/* ---------------- Facility Filter ---------------- */
$facility_id = $_SESSION['facility_id'] ?? null;

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

/* ---------------- Date Filter ---------------- */
$start_date = $_GET['start_date'] ?? date('Y-m-01');
$end_date   = $_GET['end_date'] ?? date('Y-m-d');

/* ---------------- Fetch Invoices (real data only) ---------------- */
$sql = "
    SELECT 
        i.*,
        p.name AS product_name,
        pa.first_name,
        pa.middle_name,
        pa.last_name,
        u.username AS staff_name
    FROM invoices i
    INNER JOIN products p ON i.product_id = p.id
    INNER JOIN patients pa ON i.patient_id = pa.id
    INNER JOIN users u ON i.created_by = u.id
    WHERE DATE(i.created_at) BETWEEN ? AND ?
      AND i.facility_id = ?
      AND i.is_removed = 0
    ORDER BY pa.id ASC, u.username ASC, i.created_at ASC
";

$stmt = $pdo->prepare($sql);
$stmt->execute([$start_date, $end_date, $facility_id]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

/* ---------------- Group by Patient + Staff ---------------- */
$grouped = [];

foreach ($rows as $r) {
    $key = $r['patient_id'] . '_' . $r['staff_name'];

    if (!isset($grouped[$key])) {
        $patient_name = trim(
            ($r['first_name'] ?? '') . ' ' .
            ($r['middle_name'] ?? '') . ' ' .
            ($r['last_name'] ?? '')
        );

        $grouped[$key] = [
            'patient_name' => $patient_name,
            'staff_name' => $r['staff_name'],
            'items' => []
        ];
    }

    $grouped[$key]['items'][] = $r;
}
?>

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

<div 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">

<div class="content-header row mb-3">
<div class="col-md-12">
    <h3 class="content-header-title">Sales Report (Patient & Staff)</h3>

    <form method="get" class="form-inline mt-2 mb-3">
        <label class="mr-2">Start Date</label>
        <input type="date" name="start_date" class="form-control mr-2"
               value="<?= htmlspecialchars($start_date) ?>">

        <label class="mr-2">End Date</label>
        <input type="date" name="end_date" class="form-control mr-2"
               value="<?= htmlspecialchars($end_date) ?>">

        <button class="btn btn-primary">Filter</button>
    </form>
</div>
</div>

<div class="content-body">
<div class="card">
<div class="card-body table-responsive">

<table id="salesReportTable" class="table table-bordered table-striped">
<thead>
<tr>
    <th>#</th>
    <th>Staff</th>
    <th>Items Purchased</th>
</tr>
</thead>

<tbody>
<?php $n = 1; foreach ($grouped as $g): ?>
<tr>
    <td class="text-center"><?= $n++ ?></td>
    <td><?= htmlspecialchars($g['staff_name']) ?></td>
    <td>

        <strong>Patient: <?= htmlspecialchars($g['patient_name']) ?></strong>

        <div style="overflow-x:auto;margin-top:6px;">
        <div style="max-height:250px;overflow-y:auto;">

        <table class="table table-sm table-bordered mb-0">
        <thead class="thead-light">
        <tr>
            <th>#</th>
            <th>Product</th>
            <th>Qty</th>
            <th>Amount</th>
            <th>Patient</th>
            <th>Insurance</th>
            <th>Paid</th>
        </tr>
        </thead>

        <tbody>
        <?php
        $i=1;
        $t=['q'=>0,'a'=>0,'p'=>0,'ins'=>0,'pd'=>0];
        foreach ($g['items'] as $it):
            $qty = (float)$it['quantity'];
            $amt = (float)$it['amount'];
            $pt  = (float)$it['patient_co_pay'];
            $ins = (float)$it['insurance_amount'];
            $pd  = (float)$it['amount_paid'];

            $t['q'] += $qty;
            $t['a'] += $amt;
            $t['p'] += $pt;
            $t['ins'] += $ins;
            $t['pd'] += $pd;
        ?>
        <tr>
            <td><?= $i++ ?></td>
            <td><?= htmlspecialchars($it['product_name']) ?></td>
            <td><?= number_format($qty,2) ?></td>
            <td><?= number_format($amt,2) ?></td>
            <td><?= number_format($pt,2) ?></td>
            <td><?= number_format($ins,2) ?></td>
            <td><?= number_format($pd,2) ?></td>
        </tr>
        <?php endforeach; ?>
        </tbody>

        <tfoot class="font-weight-bold">
        <tr>
            <td colspan="2">Total</td>
            <td><?= number_format($t['q'],2) ?></td>
            <td><?= number_format($t['a'],2) ?></td>
            <td><?= number_format($t['p'],2) ?></td>
            <td><?= number_format($t['ins'],2) ?></td>
            <td><?= number_format($t['pd'],2) ?></td>
        </tr>
        </tfoot>
        </table>

        </div>
        </div>

    </td>
</tr>
<?php endforeach; ?>
</tbody>
</table>

</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>
$(document).ready(function () {
    $('#salesReportTable').DataTable({
        pageLength: 10,
        dom: 'Bfrtip',
        buttons: [
            {
                extend: 'excelHtml5',
                title: 'Sales Report',
                exportOptions: {
                    format: {
                        body: function (data, row, col, node) {
                            if (col === 2) {
                                let out = '';
                                $(node).find('tbody tr').each(function () {
                                    let c = $(this).find('td');
                                    out += $(c[1]).text()+' | Qty '+$(c[2]).text()+
                                           ' | Amt '+$(c[3]).text()+
                                           ' | Pt '+$(c[4]).text()+
                                           ' | Ins '+$(c[5]).text()+
                                           ' | Paid '+$(c[6]).text()+'\n';
                                });
                                return out;
                            }
                            return data;
                        }
                    }
                }
            },
            'csvHtml5',
            'pdfHtml5',
            {
                extend: 'print',
                exportOptions:{stripHtml:false}
            }
        ]
    });
});
</script>
