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

$store_id = $_GET['store_id'] ?? '';
$from = $_GET['from_date'] ?? date('Y-m-01');
$to = $_GET['to_date'] ?? date('Y-m-d');

$where = ["ft.status = 'approved'", "ft.transaction_date BETWEEN ? AND ?"];
$params = [$from, $to];
$types = "ss";

if ($store_id !== '' && is_numeric($store_id)) {
    $where[] = "ft.store_id = ?";
    $params[] = $store_id;
    $types .= "i";
}

$whereSql = implode(" AND ", $where);

// Fetch Transactions
$sql = "
SELECT ft.*, s.store_name, u.first_name, u.last_name
FROM financial_transactions ft
LEFT JOIN stores s ON ft.store_id = s.id
LEFT JOIN users u ON ft.created_by = u.id
WHERE $whereSql
ORDER BY ft.transaction_date ASC
";
$stmt = $conn->prepare($sql);
$stmt->bind_param($types, ...$params);
$stmt->execute();
$res = $stmt->get_result();

// Group and summarize
$groups = [];
$inflows = $outflows = 0;

while ($row = $res->fetch_assoc()) {
    $month = date('Y-m', strtotime($row['transaction_date']));
    $groups[$month][] = $row;
    $inflows += floatval($row['credit']);
    $outflows += floatval($row['debit']);
}

$net = $inflows - $outflows;
?>

<style>
body {
    background: url('../../images/profile.jpg') center/cover fixed;
    color: #eee;
    font-family: 'Segoe UI', sans-serif;
    margin: 0;
}
.wrap {
    max-width: 1850px;
    margin: 40px auto;
    padding: 0 20px;
}
h2 {
    color: #ffa500;
}
.filters {
    margin: 20px 0;
    display: flex;
    gap: 15px;
    flex-wrap: wrap;
}
.filters input, .filters select, .filters button, .filters a {
    padding: 8px 12px;
    border: none;
    border-radius: 8px;
    font-weight: 500;
}
.filters button, .filters a {
    background: #ffa500;
    color: #222;
    text-decoration: none;
}
table {
    width: 100%;
    border-collapse: collapse;
    font-size: 13px;
    margin-bottom: 40px;
    background: rgba(255,255,255,0.1);
    border-radius: 10px;
}
th, td {
    padding: 10px 12px;
    border-bottom: 1px solid rgba(255,255,255,0.2);
}
th {
    background: rgba(255,165,0,0.3);
    color: #fff;
}
tfoot td {
    font-weight: bold;
}
</style>

<div class="wrap">
    <h2>Cash Flow Report</h2>

    <form class="filters" method="GET">
        <input type="date" name="from_date" value="<?= htmlspecialchars($from) ?>">
        <input type="date" name="to_date" value="<?= htmlspecialchars($to) ?>">
        <select name="store_id">
            <option value="">All Stores</option>
            <?php 
            $stores = $conn->query("SELECT id, store_name FROM stores");
            while ($store = $stores->fetch_assoc()) {
                $selected = ($store_id == $store['id']) ? 'selected' : '';
                echo "<option value='{$store['id']}' $selected>" . htmlspecialchars($store['store_name']) . "</option>";
            }
            ?>
        </select>
        <button type="submit">Filter</button>
        <a href="?<?= http_build_query(array_merge($_GET, ['export'=>'csv'])) ?>">Export CSV</a>
        <a href="?<?= http_build_query(array_merge($_GET, ['export'=>'pdf'])) ?>" target="_blank">Export PDF</a>
    </form>

    <?php foreach ($groups as $month => $transactions): ?>
        <h3><?= htmlspecialchars($month) ?></h3>
        <table>
            <thead>
                <tr>
                    <th>Date</th>
                    <th>Store</th>
                    <th>Type</th>
                    <th>Description</th>
                    <th>Debit (Outflow)</th>
                    <th>Credit (Inflow)</th>
                    <th>Created By</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($transactions as $txn): ?>
                <tr>
                    <td><?= htmlspecialchars($txn['transaction_date']) ?></td>
                    <td><?= htmlspecialchars($txn['store_name']) ?></td>
                    <td><?= ucwords(str_replace('_',' ',$txn['type'])) ?></td>
                    <td><?= htmlspecialchars($txn['description']) ?></td>
                    <td style="text-align:right;"><?= number_format($txn['debit'], 2) ?></td>
                    <td style="text-align:right;"><?= number_format($txn['credit'], 2) ?></td>
                    <td><?= htmlspecialchars($txn['first_name'].' '.$txn['last_name']) ?></td>
                </tr>
                <?php endforeach; ?>
            </tbody>
            <tfoot>
                <tr>
                    <td colspan="4" style="text-align:right;">Monthly Total:</td>
                    <td style="text-align:right;"><?= number_format(array_sum(array_column($transactions, 'debit')),2) ?></td>
                    <td style="text-align:right;"><?= number_format(array_sum(array_column($transactions, 'credit')),2) ?></td>
                    <td></td>
                </tr>
            </tfoot>
        </table>
    <?php endforeach; ?>

    <h3>Overall Summary</h3>
    <table>
        <tfoot>
            <tr>
                <td style="text-align:right;">Total Outflows (Debits):</td>
                <td><?= number_format($outflows,2) ?></td>
            </tr>
            <tr>
                <td style="text-align:right;">Total Inflows (Credits):</td>
                <td><?= number_format($inflows,2) ?></td>
            </tr>
            <tr>
                <td style="text-align:right;">Net Cash Flow:</td>
                <td><?= number_format($net,2) ?></td>
            </tr>
        </tfoot>
    </table>
</div>

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