<?php
session_start();
if (!isset($_SESSION['role_id']) || $_SESSION['role_id'] != 4) {
    header("Location: ../../index.php");
    exit();
}

include('../../includes/config.php');
include('../../includes/footer.php');
include('../../includes/header.php');

// Get filters
$store_id = $_GET['store_id'] ?? ($_SESSION['store_id'] ?? 0);
$date_from = $_GET['date_from'] ?? date('Y-m-01');
$date_to = $_GET['date_to'] ?? date('Y-m-d');
$show_rejected_expenses = isset($_GET['show_rejected_expenses']);
$show_rejected_sales = isset($_GET['show_rejected_sales']);

// Validate dates
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $date_from)) $date_from = date('Y-m-01');
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $date_to)) $date_to = date('Y-m-d');

// Store Filter SQL
$storeFilterSql = $store_id > 0 ? "AND st.store_id = ?" : "";

// Get Sales Totals
if ($store_id > 0) {
    $stmt = $conn->prepare("SELECT COALESCE(SUM(total_amount), 0), COALESCE(SUM(vat_total), 0) FROM sales_transactions st WHERE DATE(st.created_at) BETWEEN ? AND ? AND st.store_id = ?");
    $stmt->bind_param("ssi", $date_from, $date_to, $store_id);
} else {
    $stmt = $conn->prepare("SELECT COALESCE(SUM(total_amount), 0), COALESCE(SUM(vat_total), 0) FROM sales_transactions WHERE DATE(created_at) BETWEEN ? AND ?");
    $stmt->bind_param("ss", $date_from, $date_to);
}
$stmt->execute();
$stmt->bind_result($totalSales, $totalVat);
$stmt->fetch();
$stmt->close();

// Get Expenses Totals (Approved)
if ($store_id > 0) {
    $stmt = $conn->prepare("SELECT COALESCE(SUM(amount), 0) FROM expenses WHERE DATE(expense_date) BETWEEN ? AND ? AND store_id = ? AND status = 'approved'");
    $stmt->bind_param("ssi", $date_from, $date_to, $store_id);
} else {
    $stmt = $conn->prepare("SELECT COALESCE(SUM(amount), 0) FROM expenses WHERE DATE(expense_date) BETWEEN ? AND ? AND status = 'approved'");
    $stmt->bind_param("ss", $date_from, $date_to);
}
$stmt->execute();
$stmt->bind_result($totalExpenses);
$stmt->fetch();
$stmt->close();

$totalProfit = $totalSales - $totalExpenses;

// Get Sales Transactions
$sqlSales = "SELECT st.id, st.created_at, st.total_amount, st.vat_total, u.first_name, u.last_name
             FROM sales_transactions st
             JOIN users u ON st.sold_by = u.id
             WHERE DATE(st.created_at) BETWEEN ? AND ?";

if ($store_id > 0) $sqlSales .= " AND st.store_id = ?";
if ($show_rejected_sales) $sqlSales .= " AND st.status = 'rejected'";

$sqlSales .= " ORDER BY st.created_at DESC LIMIT 500";
$stmt = $conn->prepare($store_id > 0 ? "SELECT * FROM ($sqlSales) AS sub" : $sqlSales);
$store_id > 0 ? $stmt->bind_param("ssi", $date_from, $date_to, $store_id) : $stmt->bind_param("ss", $date_from, $date_to);
$stmt->execute();
$salesDetails = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();

// Get Expenses (Approved or Rejected)
$sqlExpenses = "SELECT e.id, e.expense_date, s.store_name, e.amount, e.description
                FROM expenses e
                LEFT JOIN stores s ON e.store_id = s.id
                WHERE DATE(e.expense_date) BETWEEN ? AND ?";

if ($store_id > 0) $sqlExpenses .= " AND e.store_id = ?";
$sqlExpenses .= $show_rejected_expenses ? " AND e.status = 'rejected'" : " AND e.status = 'approved'";
$sqlExpenses .= " ORDER BY e.expense_date DESC LIMIT 500";
$stmt = $conn->prepare($store_id > 0 ? $sqlExpenses : str_replace("AND e.store_id = ?", "", $sqlExpenses));
$store_id > 0 ? $stmt->bind_param("ssi", $date_from, $date_to, $store_id) : $stmt->bind_param("ss", $date_from, $date_to);
$stmt->execute();
$expensesDetails = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();

// Get store list
$storeOptions = $conn->query("SELECT id, store_name FROM stores")->fetch_all(MYSQLI_ASSOC);
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Accountant Reports - Digital POS</title>
<link rel="icon" type="image/png" href="../../images/logo.png">
<meta name="viewport" content="width=device-width, initial-scale=1">
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<title>Accountant Reports - Digital POS</title>
<link rel="icon" type="image/png" href="../../images/logo.png" />
<meta name="viewport" content="width=device-width, initial-scale=1" />
<style>
    body {
        margin: 0;
        background: url('../../images/profile.jpg') no-repeat center center fixed;
        background-size: cover;
        font-family: 'Segoe UI', sans-serif;
        color: #eee;
    }
    .page-header {
        max-width: 1850px;
        margin: 60px auto 20px auto;
        padding: 30px 40px;
        display: flex;
        justify-content: space-between;
        align-items: center;
    }
    h1.page-title {
        color: #ffa500;
        margin: 0;
        font-weight: 700;
        font-size: 2rem;
        display: flex;
        align-items: center;
        gap: 12px;
    }
    h1.page-title img {
        height: 40px;
        width: auto;
        display: block;
    }
    a.back-link {
        color: #ffa500;
        font-weight: 600;
        border: 1.5px solid #ffa500;
        padding: 6px 14px;
        border-radius: 8px;
        text-decoration: none;
        white-space: nowrap;
        transition: background-color 0.3s ease, color 0.3s ease;
    }
    a.back-link:hover {
        background-color: #ffa500;
        color: #222;
    }

    .content {
        max-width: 1850px;
        margin: 0 auto 60px auto;
        padding: 40px;
        background: rgba(255, 255, 255, 0.08);
        border-radius: 18px;
        backdrop-filter: blur(2px);
        box-shadow: 0 0 20px rgba(255, 165, 0, 0.3);
    }

    .filters {
        display: flex;
        flex-wrap: wrap;
        gap: 15px;
        margin-bottom: 30px;
        justify-content: center;
        align-items: center;
    }
    .filters label {
        color: #ffa500;
        font-weight: 600;
    }
    .filters input[type="date"],
    .filters select {
        padding: 10px 14px;
        border-radius: 10px;
        border: none;
        background: rgba(255, 255, 255, 0.15);
        color: #fff;
        font-size: 15px;
        min-width: 160px;
    }
    .filters button {
        background: #ffa500;
        border: none;
        padding: 12px 25px;
        font-weight: 700;
        border-radius: 12px;
        cursor: pointer;
        color: #222;
        font-size: 15px;
    }

    table {
        width: 100%;
        border-collapse: collapse;
        margin-bottom: 40px;
        background: rgba(255,255,255,0.05);
        border-radius: 12px;
        font-size: 15px;
    }
    thead {
        background: rgba(0,0,0,0.4);
        color: #ffa500;
    }
    th, td {
        padding: 8px 18px;
        border-bottom: 1px solid rgba(255,255,255,0.1);
        color: #eee;
        text-align: right;
    }
    th:nth-child(4),
    th:nth-child(5),
    td:nth-child(4),
    td:nth-child(5) {
        text-align: right;
    }

    .summary-cards {
        display: flex;
        justify-content: space-around;
        gap: 20px;
        margin-bottom: 40px;
        flex-wrap: wrap;
    }
    .card {
        background: rgba(255, 255, 255, 0.12);
        padding: 20px;
        border-radius: 12px;
        text-align: center;
        flex: 1;
        min-width: 220px;
        font-weight: 700;
        font-size: 18px;
        color: #ffa500;
        box-shadow: 0 6px 18px rgba(0,0,0,0.3);
        backdrop-filter: blur(12px);
    }

    .section-title {
        font-size: 20px;
        font-weight: 700;
        color: #ff8c00;
        margin-bottom: 15px;
        border-bottom: 2px solid #ffa500;
        padding-bottom: 6px;
    }

    @media (max-width: 700px) {
        .filters {
            flex-direction: column;
        }
        .summary-cards {
            flex-direction: column;
        }
        th, td {
            font-size: 14px;
            padding: 10px 8px;
        }
    }
</style>
</head>
<body>

<div class="page-header">
    <h1 class="page-title">
        <img src="../../images/logo.png" alt="Logo" />
        Accountant Comprehensive Reports
    </h1>
    <a href="reports_dashboard.php" class="back-link">&laquo; Back to Reports Dashboard</a>
</div>

<div class="content">
    <form class="filters" method="GET" action="">
        <label>From:</label>
        <input type="date" name="date_from" value="<?= htmlspecialchars($date_from) ?>" required>
        <label>To:</label>
        <input type="date" name="date_to" value="<?= htmlspecialchars($date_to) ?>" required>

        <label>Store:</label>
        <select name="store_id">
            <option value="0">All Stores</option>
            <?php foreach ($storeOptions as $store): ?>
                <option value="<?= $store['id'] ?>" <?= $store_id == $store['id'] ? 'selected' : '' ?>>
                    <?= htmlspecialchars($store['store_name']) ?>
                </option>
            <?php endforeach; ?>
        </select>

        <label><input type="checkbox" name="show_rejected_sales" value="1" <?= $show_rejected_sales ? 'checked' : '' ?>> Rejected Sales</label>
        <label><input type="checkbox" name="show_rejected_expenses" value="1" <?= $show_rejected_expenses ? 'checked' : '' ?>> Rejected Expenses</label>

        <button type="submit">Apply Filter</button>
        <button type="button" onclick="window.print()">Print Report</button>
    </form>

    <div class="summary-cards">
        <div class="card">Total Sales: MWK <?= number_format($totalSales, 2) ?></div>
        <div class="card">Total Expenses: MWK <?= number_format($totalExpenses, 2) ?></div>
        <div class="card">Total Profit: MWK <?= number_format($totalProfit, 2) ?></div>
    </div>

    <div class="section-title">Sales Transactions (max 500)</div>
    <table>
        <thead>
            <tr>
                <th>ID</th><th>Date & Time</th><th>Cashier</th><th>Total Amount (MWK)</th><th>VAT (MWK)</th>
            </tr>
        </thead>
        <tbody>
            <?php if (empty($salesDetails)): ?>
                <tr><td colspan="5" style="text-align:center; color:#ffa500;">No sales found.</td></tr>
            <?php else: foreach ($salesDetails as $sale): ?>
                <tr>
                    <td><?= $sale['id'] ?></td>
                    <td><?= htmlspecialchars($sale['created_at']) ?></td>
                    <td><?= htmlspecialchars($sale['first_name'] . ' ' . $sale['last_name']) ?></td>
                    <td><?= number_format($sale['total_amount'], 2) ?></td>
                    <td><?= number_format($sale['vat_total'], 2) ?></td>
                </tr>
            <?php endforeach; endif; ?>
        </tbody>
    </table>

    <div class="section-title">Expenses (<?= $show_rejected_expenses ? 'Rejected' : 'Approved' ?>, max 500)</div>
    <table>
        <thead>
            <tr>
                <th>ID</th><th>Date</th><th>Store</th><th>Amount (MWK)</th><th>Description</th>
            </tr>
        </thead>
        <tbody>
            <?php if (empty($expensesDetails)): ?>
                <tr><td colspan="5" style="text-align:center; color:#ffa500;">No expenses found.</td></tr>
            <?php else: foreach ($expensesDetails as $exp): ?>
                <tr>
                    <td><?= $exp['id'] ?></td>
                    <td><?= htmlspecialchars($exp['expense_date']) ?></td>
                    <td><?= htmlspecialchars($exp['store_name'] ?? 'N/A') ?></td>
                    <td style="text-align:right;"><?= number_format($exp['amount'], 2) ?></td>
                    <td><?= htmlspecialchars($exp['description']) ?></td>
                </tr>
            <?php endforeach; endif; ?>
        </tbody>
    </table>
</div>
</body>
</html>
