<?php
session_start();
if (!isset($_SESSION['username']) || ($_SESSION['role'] ?? '') !== 'admin') {
    header("Location: login.php");
    exit();
}

require 'db_connect.php';
date_default_timezone_set('Africa/Nairobi');

$page_title = "Reports & Analytics | Admin POS";

/* -----------------------------
   Date range (GET)
------------------------------ */
$today = date('Y-m-d');

$from = isset($_GET['from']) && $_GET['from'] !== '' ? $_GET['from'] : $today;
$to   = isset($_GET['to'])   && $_GET['to']   !== '' ? $_GET['to']   : $today;

// Ensure valid order
if ($from > $to) {
    [$from, $to] = [$to, $from];
}

/* -----------------------------
   Helper for CSV export
------------------------------ */
function outputCsvRow(array $fields)
{
    $escaped = [];
    foreach ($fields as $field) {
        $field = (string)$field;
        $field = str_replace('"', '""', $field);
        $escaped[] = '"' . $field . '"';
    }
    echo implode(',', $escaped) . "\r\n";
}

/* -----------------------------
   Fetch Sales by Day
------------------------------ */
$sales_by_day = [];

$salesDayStmt = $conn->prepare("
    SELECT DATE(sale_datetime) AS sale_date,
           SUM(total_sale)      AS total_sales,
           SUM(profit)          AS total_profit
    FROM sales
    WHERE refunded = 0
      AND DATE(sale_datetime) BETWEEN ? AND ?
    GROUP BY sale_date
    ORDER BY sale_date ASC
");
$salesDayStmt->bind_param("ss", $from, $to);
$salesDayStmt->execute();
$result = $salesDayStmt->get_result();
while ($row = $result->fetch_assoc()) {
    $sales_by_day[] = $row;
}
$salesDayStmt->close();

/* -----------------------------
   Sales summary (totals)
------------------------------ */
$salesSummaryStmt = $conn->prepare("
    SELECT
        COUNT(DISTINCT receipt_id) AS receipts_count,
        SUM(quantity)              AS items_sold,
        SUM(discount)              AS total_discounts,
        SUM(total_sale)            AS total_sales,
        SUM(profit)                AS total_profit
    FROM sales
    WHERE refunded = 0
      AND DATE(sale_datetime) BETWEEN ? AND ?
");
$salesSummaryStmt->bind_param("ss", $from, $to);
$salesSummaryStmt->execute();
$salesSummaryRow = $salesSummaryStmt->get_result()->fetch_assoc() ?? [];
$salesSummaryStmt->close();

$period_total_sales    = (float)($salesSummaryRow['total_sales'] ?? 0);
$period_total_profit   = (float)($salesSummaryRow['total_profit'] ?? 0);
$period_total_discount = (float)($salesSummaryRow['total_discounts'] ?? 0);
$period_receipts       = (int)($salesSummaryRow['receipts_count'] ?? 0);
$period_items_sold     = (int)($salesSummaryRow['items_sold'] ?? 0);

/* -----------------------------
   Expenses by Day
------------------------------ */
$expenses_by_day = [];

$expDayStmt = $conn->prepare("
    SELECT DATE(expense_date) AS exp_date,
           SUM(amount)        AS total_expenses
    FROM expenses
    WHERE DATE(expense_date) BETWEEN ? AND ?
    GROUP BY exp_date
    ORDER BY exp_date ASC
");
$expDayStmt->bind_param("ss", $from, $to);
$expDayStmt->execute();
$expRes = $expDayStmt->get_result();
while ($row = $expRes->fetch_assoc()) {
    $expenses_by_day[] = $row;
}
$expDayStmt->close();

/* -----------------------------
   Expenses summary
------------------------------ */
$expSummaryStmt = $conn->prepare("
    SELECT SUM(amount) AS total_expenses
    FROM expenses
    WHERE DATE(expense_date) BETWEEN ? AND ?
");
$expSummaryStmt->bind_param("ss", $from, $to);
$expSummaryStmt->execute();
$expSummaryRow = $expSummaryStmt->get_result()->fetch_assoc() ?? [];
$expSummaryStmt->close();

$period_total_expenses = (float)($expSummaryRow['total_expenses'] ?? 0);

/* -----------------------------
   Net profit
------------------------------ */
$period_net_profit = $period_total_profit - $period_total_expenses;

/* -----------------------------
   CSV / Excel export
------------------------------ */
if (isset($_GET['export']) && $_GET['export'] === '1') {
    $filename = "pos_report_{$from}_to_{$to}.csv";

    header('Content-Type: text/csv; charset=utf-8');
    header("Content-Disposition: attachment; filename=\"$filename\"");

    outputCsvRow(["POS Report"]);
    outputCsvRow(["Period", "$from to $to"]);
    echo "\r\n";

    // Sales & Profit
    outputCsvRow(["Sales & Profit Report"]);
    outputCsvRow(["Date", "Total Sales (KES)", "Total Profit (KES)"]);
    foreach ($sales_by_day as $row) {
        outputCsvRow([
            $row['sale_date'],
            number_format((float)$row['total_sales'], 2, '.', ''),
            number_format((float)$row['total_profit'], 2, '.', '')
        ]);
    }
    outputCsvRow([
        'Period Total',
        number_format($period_total_sales, 2, '.', ''),
        number_format($period_total_profit, 2, '.', '')
    ]);
    echo "\r\n";

    // Quick summary
    outputCsvRow(["Sales Quick Summary"]);
    outputCsvRow(["Total receipts", $period_receipts]);
    outputCsvRow(["Total items sold", $period_items_sold]);
    outputCsvRow(["Total discounts (KES)", number_format($period_total_discount, 2, '.', '')]);
    outputCsvRow(["Total sales (KES)", number_format($period_total_sales, 2, '.', '')]);
    outputCsvRow(["Total profit (KES)", number_format($period_total_profit, 2, '.', '')]);
    echo "\r\n";

    // Expenses
    outputCsvRow(["Expenses Report"]);
    outputCsvRow(["Date", "Total Expenses (KES)"]);
    foreach ($expenses_by_day as $row) {
        outputCsvRow([
            $row['exp_date'],
            number_format((float)$row['total_expenses'], 2, '.', '')
        ]);
    }
    outputCsvRow([
        'Period Total',
        number_format($period_total_expenses, 2, '.', '')
    ]);
    echo "\r\n";

    // Net profit summary
    outputCsvRow(["Net Profit Summary"]);
    outputCsvRow(["Total sales profit (KES)", number_format($period_total_profit, 2, '.', '')]);
    outputCsvRow(["Total expenses (KES)", number_format($period_total_expenses, 2, '.', '')]);
    outputCsvRow(["Net profit (KES)", number_format($period_net_profit, 2, '.', '')]);

    exit();
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <title><?= htmlspecialchars($page_title); ?></title>
    <?php include 'head.php'; ?>
    <style>
        body {
            font-family: 'Poppins', Arial, sans-serif;
            background: var(--bg-main);
            color: var(--text-main);
        }

        .content-area {
            margin-left: 260px;
            padding: 24px 30px 34px;
            min-width: 0;
        }

        @media (max-width: 900px) {
            .content-area {
                margin-left: 80px;
                padding: 18px 16px 26px;
            }
        }

        .page-header {
            display: flex;
            justify-content: space-between;
            align-items: flex-end;
            flex-wrap: wrap;
            gap: 10px;
            margin-bottom: 16px;
        }

        .page-title {
            font-size: 22px;
            font-weight: 600;
            color: var(--text-heading);
        }

        .page-subtitle {
            font-size: 13px;
            color: var(--accent-gold);
            margin-top: 3px;
        }

        /* Filter bar */

        .filter-bar {
            display: flex;
            flex-wrap: wrap;
            gap: 12px;
            align-items: flex-end;
            margin-bottom: 18px;
        }

        .filter-group {
            display: flex;
            flex-direction: column;
            gap: 4px;
            font-size: 13px;
        }

        .filter-group label {
            font-weight: 500;
            color: var(--text-main);
        }

        .filter-group input[type="date"] {
            padding: 7px 10px;
            border-radius: 999px;
            border: 1px solid #0a4a85;
            background: var(--bg-card);
            color: var(--text-main);
            font-size: 13px;
        }

        body[data-theme="light"] .filter-group input[type="date"] {
            border-color: rgba(148,163,184,0.6);
        }

        .filter-actions {
            display: flex;
            gap: 8px;
        }

        .btn-primary,
        .btn-outline {
            padding: 8px 16px;
            border-radius: 999px;
            border: none;
            font-size: 13px;
            font-weight: 600;
            cursor: pointer;
            display: inline-flex;
            align-items: center;
            justify-content: center;
            text-decoration: none;
            white-space: nowrap;
        }

        .btn-primary {
            background: linear-gradient(to right, #22d3ee, #0ea5e9);
            color: #011b35;
        }

        .btn-outline {
            background: transparent;
            border: 1px solid #22d3ee;
            color: #e0f2fe;
        }

        body[data-theme="light"] .btn-outline {
            color: #0369a1;
        }

        .btn-primary:hover,
        .btn-outline:hover {
            filter: brightness(1.05);
        }

        /* Summary cards */

        .summary-grid {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(220px, 1fr));
            gap: 14px;
            margin-bottom: 20px;
        }

        .summary-card {
            background: var(--bg-card);
            border-radius: 14px;
            padding: 14px 18px;
            border: 1px solid #0a4a85;
            box-shadow: 0 8px 20px rgba(0,0,0,0.4);
        }

        body[data-theme="light"] .summary-card {
            border-color: rgba(148,163,184,0.55);
            box-shadow: 0 10px 24px rgba(15,23,42,0.12);
        }

        .summary-title {
            font-size: 12px;
            text-transform: uppercase;
            letter-spacing: 0.08em;
            color: #9ca3af;
            margin-bottom: 4px;
        }

        .summary-value {
            font-size: 20px;
            font-weight: 600;
            margin-bottom: 4px;
        }

        .summary-meta {
            font-size: 12px;
            color: #e5e7eb;
        }

        body[data-theme="light"] .summary-meta {
            color: #4b5563;
        }

        .summary-card.sales .summary-value {
            color: #38bdf8;
        }
        .summary-card.profit .summary-value {
            color: #22c55e;
        }
        .summary-card.expenses .summary-value {
            color: #f97316;
        }
        .summary-card.net .summary-value {
            color: #a5b4fc;
        }

        /* Report sections */

        .report-card {
            background: var(--bg-card);
            border-radius: 16px;
            padding: 18px 20px 20px;
            border: 1px solid #0a4a85;
            box-shadow: 0 10px 24px rgba(0,0,0,0.45);
            margin-bottom: 18px;
        }

        body[data-theme="light"] .report-card {
            border-color: rgba(148,163,184,0.6);
            box-shadow: 0 12px 30px rgba(15,23,42,0.12);
        }

        .report-header {
            display: flex;
            justify-content: space-between;
            align-items: flex-start;
            gap: 14px;
            margin-bottom: 10px;
        }

        .report-title {
            font-size: 16px;
            font-weight: 500;
            color: var(--text-heading);
        }

        .report-subtitle {
            font-size: 12px;
            color: #9ca3af;
            margin-top: 3px;
        }

        body[data-theme="light"] .report-subtitle {
            color: #4b5563;
        }

        .report-body {
            display: grid;
            grid-template-columns: minmax(0, 2.1fr) minmax(0, 1.2fr);
            gap: 20px;
        }

        @media (max-width: 1024px) {
            .report-body {
                grid-template-columns: minmax(0, 1fr);
            }
        }

        .table-wrapper {
            width: 100%;
            overflow-x: auto;
        }

        table.report-table {
            width: 100%;
            border-collapse: collapse;
            font-size: 13px;
            min-width: 520px;
        }

        .report-table th,
        .report-table td {
            padding: 9px 8px;
            text-align: center;
            border-bottom: 1px solid rgba(148,163,184,0.25);
        }

        .report-table th {
            background: var(--bg-table-head);
            font-weight: 600;
            color: #e5e7eb;
        }

        .report-table tbody tr:nth-child(even) {
            background: var(--bg-table-row);
        }

        .report-table tbody tr:nth-child(odd) {
            background: rgba(8,47,73,0.8);
        }

        .report-table tbody tr:hover {
            background: var(--bg-table-hover);
        }

        body[data-theme="light"] .report-table th {
            color: #111827;
            border-bottom-color: rgba(148,163,184,0.5);
        }

        body[data-theme="light"] .report-table td {
            border-bottom-color: rgba(148,163,184,0.35);
        }

        .report-table tfoot tr {
            font-weight: 600;
            background: rgba(15,23,42,0.95);
        }

        body[data-theme="light"] .report-table tfoot tr {
            background: #e5f3ff;
        }

        .cell-left {
            text-align: left;
        }

        .summary-list {
            font-size: 13px;
            padding-left: 16px;
            line-height: 1.6;
        }

        .summary-list li {
            margin-bottom: 2px;
        }

        .summary-label {
            color: #e5e7eb;
        }

        .summary-value-inline {
            color: #38bdf8;
            font-weight: 500;
        }

        body[data-theme="light"] .summary-label {
            color: #111827;
        }

        body[data-theme="light"] .summary-value-inline {
            color: #0369a1;
        }

        footer {
            background: transparent;
        }
    </style>
</head>
<body>

<?php include 'sidebar.php'; ?>
<?php include 'topbar.php'; ?>

<div class="content-area">

    <div class="page-header">
        <div>
            <div class="page-title">Reports &amp; Analytics</div>
            <div class="page-subtitle">
                Sales, profit and expense summary between
                <strong><?= htmlspecialchars($from); ?></strong>
                and
                <strong><?= htmlspecialchars($to); ?></strong>.
            </div>
        </div>
    </div>

    <!-- Filter -->
    <form method="get" class="filter-bar">
        <div class="filter-group">
            <label for="from">From</label>
            <input type="date" id="from" name="from" value="<?= htmlspecialchars($from); ?>">
        </div>
        <div class="filter-group">
            <label for="to">To</label>
            <input type="date" id="to" name="to" value="<?= htmlspecialchars($to); ?>">
        </div>
        <div class="filter-actions">
            <button type="submit" class="btn-primary" name="filter" value="1">
                Filter
            </button>
            <button type="submit" class="btn-outline" name="export" value="1">
                Export to Excel
            </button>
        </div>
    </form>

    <!-- Summary cards -->
    <div class="summary-grid">
        <div class="summary-card sales">
            <div class="summary-title">Total Sales</div>
            <div class="summary-value">KES <?= number_format($period_total_sales, 2); ?></div>
            <div class="summary-meta">
                Receipts: <?= $period_receipts; ?>,
                Items sold: <?= $period_items_sold; ?>
            </div>
        </div>

        <div class="summary-card profit">
            <div class="summary-title">Total Profit</div>
            <div class="summary-value">KES <?= number_format($period_total_profit, 2); ?></div>
            <div class="summary-meta">
                Discounts given: KES <?= number_format($period_total_discount, 2); ?>
            </div>
        </div>

        <div class="summary-card expenses">
            <div class="summary-title">Total Expenses</div>
            <div class="summary-value">KES <?= number_format($period_total_expenses, 2); ?></div>
            <div class="summary-meta">
                All expenses recorded in this period.
            </div>
        </div>

        <div class="summary-card net">
            <div class="summary-title">Net Profit</div>
            <div class="summary-value">KES <?= number_format($period_net_profit, 2); ?></div>
            <div class="summary-meta">
                Profit &minus; Expenses.
            </div>
        </div>
    </div>

    <!-- Sales & Profit report -->
    <section class="report-card">
        <div class="report-header">
            <div>
                <div class="report-title">Sales &amp; Profit Report</div>
                <div class="report-subtitle">
                    Breakdown of total sales and profit per day.
                </div>
            </div>
        </div>

        <div class="report-body">
            <div class="table-wrapper">
                <table class="report-table">
                    <thead>
                        <tr>
                            <th>Date</th>
                            <th>Total Sales (KES)</th>
                            <th>Total Profit (KES)</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (!empty($sales_by_day)): ?>
                            <?php foreach ($sales_by_day as $row): ?>
                                <tr>
                                    <td><?= htmlspecialchars($row['sale_date']); ?></td>
                                    <td><?= number_format((float)$row['total_sales'], 2); ?></td>
                                    <td><?= number_format((float)$row['total_profit'], 2); ?></td>
                                </tr>
                            <?php endforeach; ?>
                        <?php else: ?>
                            <tr>
                                <td colspan="3" style="padding: 12px;">No sales found in this period.</td>
                            </tr>
                        <?php endif; ?>
                    </tbody>
                    <tfoot>
                        <tr>
                            <td style="text-align:right;">Period total</td>
                            <td><?= number_format($period_total_sales, 2); ?></td>
                            <td><?= number_format($period_total_profit, 2); ?></td>
                        </tr>
                    </tfoot>
                </table>
            </div>

            <div>
                <ul class="summary-list">
                    <li>
                        <span class="summary-label">Total receipts:</span>
                        <span class="summary-value-inline"><?= $period_receipts; ?></span>
                    </li>
                    <li>
                        <span class="summary-label">Total items sold:</span>
                        <span class="summary-value-inline"><?= $period_items_sold; ?></span>
                    </li>
                    <li>
                        <span class="summary-label">Total discounts:</span>
                        <span class="summary-value-inline">
                            KES <?= number_format($period_total_discount, 2); ?>
                        </span>
                    </li>
                    <li>
                        <span class="summary-label">Total sales:</span>
                        <span class="summary-value-inline">
                            KES <?= number_format($period_total_sales, 2); ?>
                        </span>
                    </li>
                    <li>
                        <span class="summary-label">Total profit:</span>
                        <span class="summary-value-inline">
                            KES <?= number_format($period_total_profit, 2); ?>
                        </span>
                    </li>
                </ul>
            </div>
        </div>
    </section>

    <!-- Expenses report -->
    <section class="report-card">
        <div class="report-header">
            <div>
                <div class="report-title">Expenses Report</div>
                <div class="report-subtitle">
                    Daily expenses and overall net profit for the selected period.
                </div>
            </div>
        </div>

        <div class="report-body">
            <div class="table-wrapper">
                <table class="report-table">
                    <thead>
                        <tr>
                            <th>Date</th>
                            <th>Total Expenses (KES)</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (!empty($expenses_by_day)): ?>
                            <?php foreach ($expenses_by_day as $row): ?>
                                <tr>
                                    <td><?= htmlspecialchars($row['exp_date']); ?></td>
                                    <td><?= number_format((float)$row['total_expenses'], 2); ?></td>
                                </tr>
                            <?php endforeach; ?>
                        <?php else: ?>
                            <tr>
                                <td colspan="2" style="padding: 12px;">No expenses recorded in this period.</td>
                            </tr>
                        <?php endif; ?>
                    </tbody>
                    <tfoot>
                        <tr>
                            <td style="text-align:right;">Period total</td>
                            <td><?= number_format($period_total_expenses, 2); ?></td>
                        </tr>
                    </tfoot>
                </table>
            </div>

            <div>
                <ul class="summary-list">
                    <li>
                        <span class="summary-label">Total sales profit:</span>
                        <span class="summary-value-inline">
                            KES <?= number_format($period_total_profit, 2); ?>
                        </span>
                    </li>
                    <li>
                        <span class="summary-label">Total expenses:</span>
                        <span class="summary-value-inline">
                            KES <?= number_format($period_total_expenses, 2); ?>
                        </span>
                    </li>
                    <li>
                        <span class="summary-label">Net profit:</span>
                        <span class="summary-value-inline">
                            KES <?= number_format($period_net_profit, 2); ?>
                        </span>
                    </li>
                </ul>
            </div>
        </div>
    </section>

    <?php include 'footer.php'; ?>
</div>

</body>
</html>
