<?php
session_start();
require_once '../config.php';

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

if (!isset($_SESSION['role']) || $_SESSION['role'] !== 'admin') {
    echo "Access Denied";
    exit;
}

function e($v) {
    return htmlspecialchars($v ?? '', ENT_QUOTES, 'UTF-8');
}

function money($v) {
    return number_format((float)$v, 2, '.', ',');
}

function fmt_date($d) {
    return (!empty($d)) ? date('d M Y', strtotime($d)) : '-';
}

function build_url($params = []) {
    $query = array_merge($_GET, $params);
    foreach ($query as $k => $v) {
        if ($v === '' || $v === null) unset($query[$k]);
    }
    return '?' . http_build_query($query);
}

$date_from     = trim($_GET['date_from'] ?? '');
$date_to       = trim($_GET['date_to'] ?? '');
$payment_status = trim($_GET['payment_status'] ?? 'all');
$created_by     = (int)($_GET['created_by'] ?? 0);
$q             = trim($_GET['q'] ?? '');
$agency_id = (int)($_GET['agency_id'] ?? 0);
$range         = trim($_GET['range'] ?? '');
$limit = (int)($_GET['limit'] ?? 10);
if ($limit <= 0) $limit = 10;
/* Quick ranges */
$today = date('Y-m-d');
$monthStart = date('Y-m-01');

if ($range === 'today') {
    $date_from = $today;
    $date_to   = $today;
} elseif ($range === 'month') {
    $date_from = $monthStart;
    $date_to   = $today;
}

/* Build WHERE */
$where = "WHERE 1=1";

if ($q !== '') {
    $qEsc = $conn->real_escape_string($q);
    $where .= " AND (
        CAST(b.id AS CHAR) LIKE '%{$qEsc}%'
        OR b.customer_name LIKE '%{$qEsc}%'
        OR b.customer_gst LIKE '%{$qEsc}%'
        OR b.phone LIKE '%{$qEsc}%'
        OR u.name LIKE '%{$qEsc}%'
    )";
}

if ($payment_status !== '' && $payment_status !== 'all') {
    $statusEsc = $conn->real_escape_string($payment_status);
    $where .= " AND b.payment_status = '{$statusEsc}'";
}

if ($created_by > 0) {
    $where .= " AND b.created_by = " . (int)$created_by;
}
if ($agency_id > 0) {
    if ($agency_id > 0) {
    $where .= " AND u.agency_id = " . (int)$agency_id;
}
}
if ($date_from !== '') {
    $df = $conn->real_escape_string($date_from);
    $where .= " AND DATE(b.created_at) >= '{$df}'";
}
if ($date_to !== '') {
    $dt = $conn->real_escape_string($date_to);
    $where .= " AND DATE(b.created_at) <= '{$dt}'";
}

/* Export CSV */
if (isset($_GET['export']) && $_GET['export'] === 'csv') {
    $sql = "
       SELECT
    b.id,
COALESCE(NULLIF(b.customer_name,''), NULLIF(b.buyer_name,''), NULLIF(b.consignee_name,''), '-') AS customer_name,
    b.customer_gst,
COALESCE(NULLIF(b.phone,''), NULLIF(b.buyer_phone,''), NULLIF(b.consignee_phone,''), '-') AS phone,
b.subtotal,
    b.taxable_amount,
    b.total_amount,
    b.payment_status,
    b.advance_amount,
    b.due_amount,
    b.created_at,
    u.name AS created_by_name,
    a.name AS agency_name
FROM bills b
LEFT JOIN users u ON b.created_by = u.id
LEFT JOIN users a ON u.agency_id = a.id AND a.role = 'agency'
{$where}
ORDER BY b.created_at ASC, b.id ASC
LIMIT {$limit}
            ";

    $result = $conn->query($sql);

    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename=sales_report_' . date('Ymd_His') . '.csv');

    $out = fopen('php://output', 'w');
   fputcsv($out, [
    'Bill No', 'Customer', 'GST', 'Phone', 'Created By', 'Status',
    'Subtotal', 'Total', 'Advance', 'Due', 'Date'
]);

    if ($result) {
        while ($row = $result->fetch_assoc()) {
            fputcsv($out, [
    $row['id'],
    $row['customer_name'],
    $row['customer_gst'],
    $row['phone'],
    $row['created_by_name'],
    $row['payment_status'],
  $row['subtotal'],
$row['total_amount'],
$row['subtotal'],
    $row['taxable_amount'],
    $row['total_amount'],
    $row['advance_amount'],
    $row['due_amount'],
    $row['created_at'],
]);
        }
    }

    fclose($out);
    exit;
}
    /* ================================
   WHOLESALER EXPORT CSV
================================ */

if (isset($_GET['export']) && $_GET['export'] === 'wholesaler_csv') {

    $export_sql = "
        SELECT
            w.id,
            p.product_name,
            w.sale_month,
            w.initial_stock,
            w.month_sales,
            w.balance_stock,
            w.created_at
        FROM wholesaler w
        LEFT JOIN products p
            ON p.id = w.product_id
        ORDER BY w.created_at DESC
    ";

    $export_res = $conn->query($export_sql);

    header('Content-Type: text/csv; charset=utf-8');

    header(
        'Content-Disposition: attachment; filename=wholesaler_report_' .
        date('Ymd_His') .
        '.csv'
    );

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

    fputcsv($out, [
        'ID',
        'Product',
        'Month',
        'Initial Stock',
        'Month Sales',
        'Balance Stock',
        'Created Date'
    ]);

    if ($export_res) {

        while ($row = $export_res->fetch_assoc()) {

            fputcsv($out, [

                $row['id'],
                $row['product_name'],
                $row['sale_month'],
                $row['initial_stock'],
                $row['month_sales'],
                $row['balance_stock'],
                $row['created_at']

            ]);
        }
    }

    fclose($out);
    exit;
}

/* Summary */
$summary_sql = "
    SELECT
        COUNT(*) AS total_bills,
        COALESCE(SUM(b.subtotal),0) AS subtotal_sum,
        COALESCE(SUM(b.total_amount),0) AS total_sales,
        COALESCE(SUM(b.advance_amount),0) AS advance_sum,
        COALESCE(SUM(b.due_amount),0) AS due_sum
    FROM bills b
    LEFT JOIN users u ON b.created_by = u.id
    {$where}
";
$summary_res = $conn->query($summary_sql);
$summary = $summary_res ? $summary_res->fetch_assoc() : [
    'total_bills' => 0,
    'subtotal_sum' => 0,
    'total_sales' => 0,
    'advance_sum' => 0,
    'due_sum' => 0
];

/* Sales by executive */
$exec_sql = "
    SELECT
        COALESCE(u.name, 'Unknown') AS staff_name,
        COALESCE(u.role, 'unknown') AS staff_role,
        COUNT(*) AS bills_count,
        COALESCE(SUM(b.total_amount),0) AS sales_amount,
        COALESCE(SUM(b.due_amount),0) AS due_amount
    FROM bills b
    LEFT JOIN users u ON b.created_by = u.id
    {$where}
    GROUP BY b.created_by
    ORDER BY sales_amount ASC, bills_count ASC
";
$exec_res = $conn->query($exec_sql);
$executive_rows = [];
if ($exec_res) {
    while ($row = $exec_res->fetch_assoc()) {
        $executive_rows[] = $row;
    }
}

/* Top products */
$top_product_sql = "
    SELECT
        bi.product_name,
        COALESCE(SUM(bi.qty),0) AS qty_sold,
        COALESCE(SUM(bi.total),0) AS sales_amount
    FROM bills b
    INNER JOIN bill_items bi ON bi.bill_id = b.id
    LEFT JOIN users u ON b.created_by = u.id
    LEFT JOIN users a ON u.agency_id = a.id AND a.role = 'agency'
    {$where}
    GROUP BY bi.product_name
    ORDER BY qty_sold ASC, sales_amount ASC
    LIMIT 10
";
$top_product_res = $conn->query($top_product_sql);
$top_products = [];
if ($top_product_res) {
    while ($row = $top_product_res->fetch_assoc()) {
        $top_products[] = $row;
    }
}

/* Main bill list */
$list_sql = "
   SELECT
    b.id,

    COALESCE(
        NULLIF(b.customer_name,''),
        NULLIF(b.buyer_name,''),
        NULLIF(b.consignee_name,''),
        '-'
    ) AS customer_name,

    b.customer_gst,

    COALESCE(
        NULLIF(b.phone,''),
        NULLIF(b.buyer_phone,''),
        NULLIF(b.consignee_phone,''),
        '-'
    ) AS phone,
        b.subtotal,
        b.taxable_amount,
        b.total_amount,
        b.payment_status,
        b.advance_amount,
        b.due_amount,
        b.created_at,
        u.name AS created_by_name,
        a.name AS agency_name
    FROM bills b
    LEFT JOIN users u ON b.created_by = u.id
    LEFT JOIN users a ON u.agency_id = a.id AND a.role = 'agency'
    {$where}
    ORDER BY b.created_at ASC, b.id ASC
    LIMIT {$limit}
    ";
$list_res = $conn->query($list_sql);
/* Wholesaler records */
$wholesaler_where = "WHERE 1=1";

if ($q !== '') {
    $qEsc = $conn->real_escape_string($q);

    $wholesaler_where .= " AND (
        CAST(w.id AS CHAR) LIKE '%{$qEsc}%'
        OR p.product_name LIKE '%{$qEsc}%'
        OR w.sale_month LIKE '%{$qEsc}%'
    )";
}

if ($date_from !== '') {
    $df = $conn->real_escape_string($date_from);
    $wholesaler_where .= " AND DATE(w.created_at) >= '{$df}'";
}

if ($date_to !== '') {
    $dt = $conn->real_escape_string($date_to);
    $wholesaler_where .= " AND DATE(w.created_at) <= '{$dt}'";
}

$wholesaler_sql = "
    SELECT
        w.id,
        p.product_name,
        w.sale_month,
        w.initial_stock,
        w.month_sales,
        w.balance_stock,
        w.created_at
    FROM wholesaler w
    LEFT JOIN products p ON p.id = w.product_id
    {$wholesaler_where}
    ORDER BY w.created_at DESC
    LIMIT {$limit}
";

$wholesaler_res = $conn->query($wholesaler_sql);
?>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Sales Reports</title>

<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.5.0/css/all.min.css">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js"></script>

<style>
   /* ===== BASE ===== */
body{
    background:#eef2f7;
    font-family:'Segoe UI',sans-serif;
    color:#0f172a;
}

/* ===== LAYOUT ===== */
.main-content{
    margin-left:250px;
    padding:0px 10px 10px 10px;
}

/* ===== CARD ===== */
.page-card{
    background:#fff;
    border-radius:14px;
    box-shadow:0 6px 18px rgba(15,23,42,.05);
    border:1px solid rgba(148,163,184,.12);
    padding:14px;
}

/* ===== TITLE ===== */
.page-title{
    display:flex;
    align-items:center;
    gap:8px;
    font-size:22px;
    font-weight:800;
    margin-bottom:4px;
}

.page-subtitle{
    color:#64748b;
    font-size:12px;
    margin-bottom:12px;
}

/* ===== STATS ===== */
.stat-card{
    background:#fff;
    border:1px solid #e5e7eb;
    border-radius:12px;
    padding:12px;
    height:100%;
}

.stat-label{
    color:#64748b;
    font-size:11px;
    font-weight:700;
    text-transform:uppercase;
}

.stat-value{
    font-size:18px;
    font-weight:800;
    margin-top:6px;
}

.stat-icon{
    width:34px;
    height:34px;
    border-radius:10px;
    display:flex;
    align-items:center;
    justify-content:center;
    background:#e2e8f0;
    color:#334155;
    font-size:14px;
}

/* ===== FILTER ===== */
.filter-box{
    background:#f8fafc;
    border:1px solid #e2e8f0;
    border-radius:12px;
    padding:12px;
    margin-bottom:16px;
}

.form-control, .form-select{
    border-radius:8px;
    border:1px solid #d6dde8;
    padding:6px 10px;
    font-size:12px;
}

.form-control:focus, .form-select:focus{
    border-color:#334155;
}

/* ===== BUTTON ===== */
.btn-pill{
    border-radius:10px;
    padding:6px 12px;
    font-weight:600;
    font-size:12px;
}

.btn-dark-pill{
    background:#374155;
    color:#fff;
    border:1px solid #374155;
}

.btn-dark-pill:hover{
    background:#1f2937;
}

.btn-light-pill{
    background:#fff;
    border:1px solid #d6dde8;
    color:#334155;
}

/* ===== TABLE ===== */
.table-wrap{
    overflow-x:auto;
}

.table thead th{
    background:#374155 !important;
    color:#fff !important;
    white-space:nowrap;
    border:0;
    font-size:11px;
    padding:8px;
}

.table td{
    vertical-align:middle;
    white-space:nowrap;
    font-size:12px;
    padding:8px;
}

/* ===== STATUS BADGE ===== */
.badge-soft{
    padding:4px 8px;
    border-radius:999px;
    font-weight:600;
    font-size:10px;
    text-transform:capitalize;
}
.wholesaler-export-btn{
    background:#374155;
    color:#fff;
    border:1px solid #374155;
}

.wholesaler-export-btn:hover{
    background:#1f2937;
    color:#fff;
}
.paid{ background:#dcfce7; color:#166534; }
.unpaid{ background:#fee2e2; color:#991b1b; }
.advance{ background:#fef3c7; color:#92400e; }

/* ===== SECTION TITLE ===== */
.section-title{
    font-size:15px;
    font-weight:700;
    margin:8px 0 10px;
    display:flex;
    align-items:center;
    gap:6px;
}

/* ===== SMALL BUTTON (VIEW) ===== */
.btn-sm{
    font-size:11px;
    padding:4px 8px;
}

/* ===== LIMIT DROPDOWN ===== */
select[name="limit"]{
    height:32px;
    font-size:12px;
}

/* ===== GRID GAP REDUCE ===== */
.row.g-3{
    --bs-gutter-x:8px;
    --bs-gutter-y:8px;
}

/* ===== MOBILE ===== */
@media(max-width:768px){
    .main-content{
        margin-left:0;
        padding:10px;
    }

    .page-title{
        font-size:20px;
    }
}
</style>
</head>
<body>

<?php include 'adminsidebar.php'; ?>
<?php include '../header.php'; ?>

<div class="main-content">
    <div class="page-card">
<div class="d-flex justify-content-between align-items-start flex-wrap mb-2">

    <!-- LEFT SIDE -->
    <div>
        <div class="page-title m-0" style="color:#000;">
            <i class="fa-solid fa-chart-pie"></i>
            Sales Reports
        </div>

        <div class="page-subtitle">
            Detailed bill summary, executive-wise performance, and top product sales.
        </div>
    </div>

    <!-- RIGHT SIDE -->
    <div class="d-flex gap-2 mt-2">
        <a href="sales_reports.php?export=csv" class="btn btn-dark-pill btn-pill">
            <i class="fa-solid fa-file-csv"></i> Export CSV
        </a>

        <a href="sales_reports.php" class="btn btn-light-pill btn-pill">
            <i class="fa-solid fa-rotate-left"></i> Reset
        </a>
    </div>

</div>
        <form method="GET" class="filter-box">
            <div class="row g-3 align-items-end">
                <div class="col-lg-2 col-md-6">

    <label class="form-label fw-bold">
        Agency
    </label>

    <select name="agency_id" class="form-select">

        <option value="0">
            All Agencies
        </option>

        <?php

        $country = trim($_GET['country'] ?? '');

        $agency_sql = "
            SELECT id, name
            FROM users
            WHERE role='agency'
        ";

        if($country != '' && $country != 'all'){

            $countryEsc = $conn->real_escape_string($country);

            $agency_sql .= "
                AND country = '{$countryEsc}'
            ";
        }

        $agency_sql .= "
            ORDER BY name ASC
        ";

        $agency_res = $conn->query($agency_sql);

        if($agency_res){

            while($a = $agency_res->fetch_assoc()){

                $sel = (
                    $agency_id == $a['id']
                ) ? 'selected' : '';

                ?>

                <option
                    value="<?= (int)$a['id'] ?>"
                    <?= $sel ?>
                >

                    <?= e($a['name']) ?>

                </option>

                <?php
            }
        }

        ?>

    </select>

</div>
                <div class="col-lg-3 col-md-6">
                    <label class="form-label fw-bold">Search</label>
                    <input type="text" name="q" class="form-control" value="<?php echo e($q); ?>" placeholder="Bill no, customer, phone">
                </div>

                <div class="col-lg-2 col-md-6">
                    <label class="form-label fw-bold">From</label>
                    <input type="date" name="date_from" class="form-control" value="<?php echo e($date_from); ?>">
                </div>

                <div class="col-lg-2 col-md-6">
                    <label class="form-label fw-bold">To</label>
                    <input type="date" name="date_to" class="form-control" value="<?php echo e($date_to); ?>">
                </div>

                <div class="col-lg-2 col-md-6">
                    <label class="form-label fw-bold">Status</label>
                    <select name="payment_status" class="form-select">
                        <option value="all" <?php echo $payment_status === 'all' ? 'selected' : ''; ?>>All</option>
                        <option value="paid" <?php echo $payment_status === 'paid' ? 'selected' : ''; ?>>Paid</option>
                        <option value="unpaid" <?php echo $payment_status === 'unpaid' ? 'selected' : ''; ?>>Unpaid</option>
                        <option value="advance" <?php echo $payment_status === 'advance' ? 'selected' : ''; ?>>Advance</option>
                    </select>
                </div>

<div class="col-lg-2 col-md-6">

    <label class="form-label fw-bold">
        Country
    </label>

    <select name="country" class="form-select">

        <option value="all">
            All Countries
        </option>

        <?php

        $country = trim($_GET['country'] ?? '');

        $country_res = $conn->query("
            SELECT DISTINCT country
            FROM users
            WHERE role='agency'
            AND country <> ''
            ORDER BY country ASC
        ");

        if($country_res){

            while($c = $country_res->fetch_assoc()){

                $sel = (
                    $country == $c['country']
                ) ? 'selected' : '';

                ?>

                <option
                    value="<?= e($c['country']) ?>"
                    <?= $sel ?>
                >

                    <?= e($c['country']) ?>

                </option>

                <?php
            }
        }

        ?>

    </select>

</div>

<div class="col-lg-2 col-md-6">

    <label class="form-label fw-bold">
        Quick Range
    </label>

    <select name="range" class="form-select">

        <option value=""
            <?php echo $range === '' ? 'selected' : ''; ?>>
            Custom
        </option>

        <option value="today"
            <?php echo $range === 'today' ? 'selected' : ''; ?>>
            Today
        </option>

        <option value="month"
            <?php echo $range === 'month' ? 'selected' : ''; ?>>
            This Month
        </option>

    </select>

</div>
                <div class="col-lg-1 col-md-6">
                    <button class="btn btn-dark-pill btn-pill w-100">
                        <i class="fa-solid fa-filter"></i>
                    </button>
                </div>
            </div>

            
        </form>

        <div class="row g-3 mb-4">
            <div class="col-lg-3 col-md-6">
                <div class="stat-card">
                    <div class="d-flex justify-content-between align-items-center">
                        <div class="stat-label">Total Bills</div>
                        <div class="stat-icon"><i class="fa-solid fa-receipt"></i></div>
                    </div>
                    <div class="stat-value"><?php echo (int)$summary['total_bills']; ?></div>
                </div>
            </div>
            <div class="col-lg-3 col-md-6">
                <div class="stat-card">
                    <div class="d-flex justify-content-between align-items-center">
                        <div class="stat-label">Total Sales</div>
                        <div class="stat-icon"><i class="fa-solid fa-indian-rupee-sign"></i></div>
                    </div>
                    <div class="stat-value">₹<?php echo money($summary['total_sales']); ?></div>
                </div>
            </div>
            <div class="col-lg-3 col-md-6">
                <div class="stat-card">
                    <div class="d-flex justify-content-between align-items-center">
                        <div class="stat-label">Advance Collected</div>
                        <div class="stat-icon"><i class="fa-solid fa-wallet"></i></div>
                    </div>
                    <div class="stat-value">₹<?php echo money($summary['advance_sum']); ?></div>
                </div>
            </div>
            <div class="col-lg-3 col-md-6">
                <div class="stat-card">
                    <div class="d-flex justify-content-between align-items-center">
                        <div class="stat-label">Due Amount</div>
                        <div class="stat-icon"><i class="fa-solid fa-circle-exclamation"></i></div>
                    </div>
                    <div class="stat-value">₹<?php echo money($summary['due_sum']); ?></div>
                </div>
            </div>
        </div>

        <div class="d-flex justify-content-between align-items-center mb-2">

    <div class="section-title m-0">

        <i class="fa-solid fa-table-list"></i>
        Bill Summary
                </div>
            <div class="d-flex flex-column align-items-center">
                <label class="form-label fw-bold">Show</label>
                <select name="limit" class="form-select" style="width:120px;">
                    <option value="10" <?php echo $limit==10?'selected':''; ?>>10</option>
                    <option value="25" <?php echo $limit==25?'selected':''; ?>>25</option>
                    <option value="50" <?php echo $limit==50?'selected':''; ?>>50</option>
                    <option value="100" <?php echo $limit==100?'selected':''; ?>>100</option>
                </select>
            </div>
               
            </div>

        <div class="table-wrap mb-4">
            <table class="table table-bordered align-middle">
                <thead>
                    <tr>
                     <th class="col-bill">Bill No</th>
                    <th class="col-customer">Customer</th>
                    <th class="col-phone">Phone</th>
                    <th class="col-user">Created By</th>
                    <th>Agency</th>
                    <th class="col-status">Status</th>
                    <th class="col-subtotal">Sales</th>
                    <th class="col-total">Total</th>
                    <th class="col-advance">Advance</th>
                    <th class="col-due">Due</th>
                    <th class="col-date">Date</th>
                    <th>Action</th>
                    </tr>
                </thead>
                <tbody>
                <?php if ($list_res && $list_res->num_rows > 0): ?>
                    <?php while ($row = $list_res->fetch_assoc()): 
                        $status = strtolower($row['payment_status'] ?? 'paid');
                        $cls = in_array($status, ['paid','unpaid','advance']) ? $status : 'paid';
                    ?>
                        <tr>
                          <td class="col-bill"><?php echo (int)$row['id']; ?></td>
                            <td class="col-customer"><?php echo e($row['customer_name'] ?: '-'); ?></td>
                            <td class="col-phone"><?php echo e($row['phone'] ?: '-'); ?></td>
                            <td class="col-user"><?php echo e($row['created_by_name'] ?: '-'); ?></td>
                            <td><?php echo e($row['agency_name'] ?? '-'); ?></td>
                            <td class="col-status"><span class="badge-soft <?php echo $cls; ?>"><?php echo e($status); ?></span></td>
                            
                            <td class="col-subtotal">₹<?php echo money($row['subtotal']); ?></td>
                            
                            
                            <td class="col-total"><strong>₹<?php echo money($row['total_amount']); ?></strong></td>
                            <td class="col-advance">₹<?php echo money($row['advance_amount']); ?></td>
                            <td class="col-due">₹<?php echo money($row['due_amount']); ?></td>
                            <td class="col-date"><?php echo fmt_date($row['created_at']); ?></td>
                            <td>
                                <a href="../Admin/view_bill.php?id=<?php echo (int)$row['id']; ?>" class="btn btn-sm btn-outline-dark">
                                    <i class="fa-solid fa-eye"></i> View
                                </a>
                            </td>
                        </tr>
                    <?php endwhile; ?>
                <?php else: ?>
                    <tr>
                        <td colspan="14" class="text-center text-muted py-4">No bills found for selected filters.</td>
                    </tr>
                <?php endif; ?>
                </tbody>
            </table>
        </div>
<div class="section-title mt-4">
    <i class="fa-solid fa-boxes-stacked"></i>
    Wholesaler Record Summary
</div>
<div class="text-end mb-2">

   <a
    href="sales_reports.php?export=wholesaler_csv"
    class="btn btn-sm wholesaler-export-btn"
>

        <i class="fa-solid fa-file-excel"></i>

       Export CSV

    </a>

</div>
<div class="table-wrap mb-4">
    <table class="table table-bordered align-middle">
        <thead>
            <tr>
                <th>ID</th>
                <th>Product</th>
                <th>Month</th>
                <th>Initial Stock</th>
                <th>Month Sales</th>
                <th>Balance Stock</th>
                <th>Date</th>
            </tr>
        </thead>

        <tbody>

        <?php if ($wholesaler_res && $wholesaler_res->num_rows > 0): ?>

            <?php while ($wrow = $wholesaler_res->fetch_assoc()): ?>

                <tr>
                    <td><?= (int)$wrow['id'] ?></td>

                    <td>
                        <?= e($wrow['product_name'] ?? '-') ?>
                    </td>

                    <td>
                        <?= e($wrow['sale_month']) ?>
                    </td>

                    <td>
                        <?= (int)$wrow['initial_stock'] ?>
                    </td>

                    <td>
                        <?= (int)$wrow['month_sales'] ?>
                    </td>

                    <td>
                        <strong>
                            <?= (int)$wrow['balance_stock'] ?>
                        </strong>
                    </td>

                    <td>
                        <?= fmt_date($wrow['created_at']) ?>
                    </td>
                </tr>

            <?php endwhile; ?>

        <?php else: ?>

            <tr>
                <td colspan="7" class="text-center text-muted py-4">
                    No wholesaler records found.
                </td>
            </tr>

        <?php endif; ?>

        </tbody>
    </table>
</div>
        <div class="row g-4">
            <div class="col-lg-6">
                <div class="section-title">
                    <i class="fa-solid fa-user-tie"></i>
                    Medical Representative Wise Report
                </div>
                <div class="table-wrap">
                    <table class="table table-bordered align-middle">
                        <thead>
                            <tr>
                                <th>Medical Representative</th>
                                <th>Role</th>
                                <th>Bills</th>
                                <th>Sales</th>
                                <th>Due</th>
                            </tr>
                        </thead>
                        <tbody>
                        <?php if (!empty($executive_rows)): ?>
                            <?php foreach ($executive_rows as $row): ?>
                                <tr>
                                    <td><?php echo e($row['staff_name']); ?></td>
                                    <td><?php echo e($row['staff_role']); ?></td>
                                    <td><?php echo (int)$row['bills_count']; ?></td>
                                    <td>₹<?php echo money($row['sales_amount']); ?></td>
                                    <td>₹<?php echo money($row['due_amount']); ?></td>
                                </tr>
                            <?php endforeach; ?>
                        <?php else: ?>
                            <tr><td colspan="5" class="text-center text-muted py-4">No medical representative data found.</td></tr>
                        <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>

            <div class="col-lg-6">
                <div class="section-title">
                    <i class="fa-solid fa-boxes-stacked"></i>
                    Top 10 Sold Products
                </div>
                <div class="table-wrap">
                    <table class="table table-bordered align-middle">
                        <thead>
                            <tr>
                                <th>Product</th>
                                <th>Qty Sold</th>
                                <th>Sales</th>
                            </tr>
                        </thead>
                        <tbody>
                        <?php if (!empty($top_products)): ?>
                            <?php foreach ($top_products as $row): ?>
                                <tr>
                                    <td><?php echo e($row['product_name']); ?></td>
                                    <td><?php echo money($row['qty_sold']); ?></td>
                                    <td>₹<?php echo money($row['sales_amount']); ?></td>
                                </tr>
                            <?php endforeach; ?>
                        <?php else: ?>
                            <tr><td colspan="3" class="text-center text-muted py-4">No product sales found.</td></tr>
                        <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>

    </div>
</div>
<script>
document.querySelectorAll('.col-toggle').forEach(cb => {
    cb.addEventListener('change', function () {
        let colClass = this.dataset.col;
        let display = this.checked ? '' : 'none';

        document.querySelectorAll('.' + colClass).forEach(el => {
            el.style.display = display;
        });
    });
});
</script>
<script>
const toggles = document.querySelectorAll('.col-toggle');

toggles.forEach(cb => {
    const saved = localStorage.getItem(cb.dataset.col);
    if (saved === '0') {
        cb.checked = false;
        document.querySelectorAll('.' + cb.dataset.col).forEach(el => el.style.display = 'none');
    }

    cb.addEventListener('change', function () {
        localStorage.setItem(this.dataset.col, this.checked ? '1' : '0');
    });
});
</script>
</body>
</html>