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

$role_id = $_SESSION['role_id'];
$user_id = $_SESSION['user_id'];

$date_from = $_GET['date_from'] ?? date('Y-m-01');
$date_to = $_GET['date_to'] ?? date('Y-m-d');
$filter_user = $_GET['user_id'] ?? '';
$reconciled_status = $_GET['status'] ?? '';
$page = max(1, intval($_GET['page'] ?? 1));
$per_page = 15;
$offset = ($page - 1) * $per_page;

// Sanitize & validate dates server side (basic)
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');

// Build where clause for filters
$where = "WHERE DATE(bt.transaction_date) BETWEEN ? AND ?";
$params = [$date_from, $date_to];
$types = "ss";

if (!empty($filter_user)) {
    $where .= " AND bt.created_by = ?";
    $params[] = $filter_user;
    $types .= "i";
}
if ($reconciled_status === 'reconciled') {
    $where .= " AND bt.reconciliation_status = 'reconciled'";
} elseif ($reconciled_status === 'unreconciled') {
    $where .= " AND (bt.reconciliation_status IS NULL OR bt.reconciliation_status = 'pending')";
}

// Count total records for pagination
$countSql = "SELECT COUNT(DISTINCT bt.id) AS cnt
FROM bank_transactions bt
LEFT JOIN users u ON bt.created_by = u.id
$where";

$countStmt = $conn->prepare($countSql);
if ($countStmt === false) die("Prepare failed: " . $conn->error);
$countStmt->bind_param($types, ...$params);
$countStmt->execute();
$countRes = $countStmt->get_result()->fetch_assoc();
$total_records = $countRes['cnt'] ?? 0;
$total_pages = ceil($total_records / $per_page);
$countStmt->close();

// Fetch paginated data with aggregated sales (sum of credits)
$sql = "
SELECT 
  bt.id AS bank_txn_id,
  bt.transaction_date,
  bt.amount AS bank_deposit,
  bt.status,
  bt.reconciliation_status,
  a.account_name,
  a.account_type,
  u.first_name,
  u.last_name,
  IFNULL(SUM(ft.credit), 0) AS sales_revenue
FROM bank_transactions bt
LEFT JOIN accounts a ON bt.account_id = a.id
LEFT JOIN financial_transactions ft ON ft.linked_bank_transaction_id = bt.id
LEFT JOIN users u ON bt.created_by = u.id
$where
GROUP BY bt.id
ORDER BY bt.transaction_date DESC
LIMIT ?, ?
";

$params[] = $offset;
$params[] = $per_page;
$types .= "ii";

$stmt = $conn->prepare($sql);
if ($stmt === false) die("Prepare failed: " . $conn->error);
$stmt->bind_param($types, ...$params);
$stmt->execute();
$res = $stmt->get_result();
$records = $res->fetch_all(MYSQLI_ASSOC);
$stmt->close();

// Fetch users for filter dropdown
$usersRes = $conn->query("SELECT id, first_name, last_name FROM users WHERE role_id IN (1,4) ORDER BY first_name");
$users = [];
while ($u = $usersRes->fetch_assoc()) {
    $users[] = $u;
}

// Aggregate totals for Chart.js visualization (group by account)
$chartWhere = $where; // same filters
$chartParams = $params;
$chartTypes = substr($types, 0, strlen($types) - 2); // remove pagination params

$chartSql = "
SELECT 
  a.account_name,
  IFNULL(SUM(bt.amount), 0) AS total_deposits,
  IFNULL(SUM(ft.credit), 0) AS total_sales
FROM bank_transactions bt
LEFT JOIN accounts a ON bt.account_id = a.id
LEFT JOIN financial_transactions ft ON ft.linked_bank_transaction_id = bt.id
$chartWhere
GROUP BY a.account_name
ORDER BY a.account_name ASC
";

$chartStmt = $conn->prepare($chartSql);
if ($chartStmt === false) die("Prepare failed: " . $conn->error);

if (strlen($chartTypes) > 0) {
    $chartStmt->bind_param($chartTypes, ...array_slice($chartParams, 0, -2)); // remove last two pagination params
} else {
    $chartStmt->bind_param("ss", $date_from, $date_to);
}

$chartStmt->execute();
$chartRes = $chartStmt->get_result()->fetch_all(MYSQLI_ASSOC);
$chartStmt->close();

$chartLabels = [];
$chartDeposits = [];
$chartSales = [];
foreach ($chartRes as $row) {
    $chartLabels[] = $row['account_name'] ?? 'N/A';
    $chartDeposits[] = floatval($row['total_deposits']);
    $chartSales[] = floatval($row['total_sales']);
}

// Calculate totals for footer
$total_deposits = 0;
$total_sales = 0;
foreach ($records as $r) {
    $total_deposits += floatval($r['bank_deposit']);
    $total_sales += floatval($r['sales_revenue']);
}
$total_variance = $total_sales - $total_deposits;

?>

<!-- Chart.js CDN -->
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>

<style>
body {
    background: url('../../images/profile.jpg') no-repeat center center fixed;
    background-size: cover;
    font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
    color: #eee;
    margin: 0;
}
.wrap {
    max-width: 1600px;
    margin: 100px auto 50px;
    padding: 25px 40px 40px;
    background: rgba(30,30,30,0.85);
    border-radius: 16px;
    box-shadow: 0 0 15px rgba(255,165,0,0.4);
    backdrop-filter: blur(10px);
}
h2 {
    text-align: center;
    color: #ffa500;
    margin-bottom: 20px;
    font-weight: 700;
    letter-spacing: 0.05em;
    text-transform: uppercase;
}
.filters {
    display: flex;
    flex-wrap: wrap;
    gap: 12px;
    justify-content: center;
    margin-bottom: 30px;
}
.filters input, .filters select, .filters button {
    padding: 12px 18px;
    border-radius: 10px;
    border: none;
    font-weight: 600;
    min-width: 170px;
    color: #222;
    transition: box-shadow 0.3s ease;
}
.filters button {
    background: #ffa500;
    cursor: pointer;
}
.filters input:focus, .filters select:focus, .filters button:hover {
    box-shadow: 0 0 10px #ffa500;
    outline: none;
}
.chart-container {
    background: #111;
    padding: 25px 30px;
    border-radius: 14px;
    box-shadow: 0 0 12px rgba(255, 165, 0, 0.6);
    margin-bottom: 40px;
    height: 320px;
}
table {
    width: 100%;
    border-collapse: collapse;
    font-size: 15px;
    border-radius: 10px;
    overflow: hidden;
    box-shadow: 0 0 15px rgba(255,165,0,0.25);
    background: rgba(20, 20, 20, 0.8);
}
th, td {
    padding: 14px 16px;
    border-bottom: 1px solid rgba(255,255,255,0.15);
    text-align: left;
    color: #ddd;
}
th {
    background: linear-gradient(90deg, #ffa500cc, #ffb733cc);
    color: #222;
    font-weight: 700;
    letter-spacing: 0.03em;
}
tbody tr:hover {
    background: rgba(255, 165, 0, 0.1);
    cursor: default;
}
td.numeric, td.text-center {
    text-align: right;
}
td.variance-positive {
    color: #0f0;
    font-weight: 700;
}
td.variance-negative {
    color: #f55;
    font-weight: 700;
}
.status-pending {
    background: #ffbf0050;
    color: #704800;
    font-weight: 600;
    text-align: center;
    border-radius: 6px;
    padding: 6px 10px;
    user-select: none;
    white-space: nowrap;
}
.status-reconciled {
    background: #00cc4450;
    color: #004d1a;
    font-weight: 600;
    text-align: center;
    border-radius: 6px;
    padding: 6px 10px;
    user-select: none;
    white-space: nowrap;
}
tfoot tr {
    background: rgba(255, 165, 0, 0.15);
    font-weight: 700;
    color: #222;
}
.pagination {
    margin-top: 35px;
    text-align: center;
}
.pagination a, .pagination span {
    background: #ffa500;
    color: #222;
    padding: 10px 18px;
    margin: 0 6px;
    border-radius: 8px;
    font-weight: 700;
    text-decoration: none;
    user-select: none;
    transition: background-color 0.3s ease;
}
.pagination a:hover {
    background: #ffb733;
}
@media (max-width: 900px) {
    .filters {
        flex-direction: column;
        gap: 14px;
    }
    .filters input, .filters select, .filters button {
        min-width: 100%;
    }
}
</style>

<div class="wrap">
    <h2>Audit-Grade Variance Report</h2>

    <form class="filters" method="get" autocomplete="off" aria-label="Filter variance report">
        <input type="date" name="date_from" value="<?= htmlspecialchars($date_from) ?>" required aria-label="Start date filter">
        <input type="date" name="date_to" value="<?= htmlspecialchars($date_to) ?>" required aria-label="End date filter">
        <select name="user_id" title="Filter by user" aria-label="Filter by user">
            <option value="">All Users</option>
            <?php foreach ($users as $u): ?>
                <option value="<?= $u['id'] ?>" <?= $filter_user == $u['id'] ? 'selected' : '' ?>>
                    <?= htmlspecialchars($u['first_name'] . ' ' . $u['last_name']) ?>
                </option>
            <?php endforeach; ?>
        </select>
        <select name="status" title="Filter by reconciliation status" aria-label="Filter by reconciliation status">
            <option value="">All Status</option>
            <option value="reconciled" <?= $reconciled_status === 'reconciled' ? 'selected' : '' ?>>Reconciled</option>
            <option value="unreconciled" <?= $reconciled_status === 'unreconciled' ? 'selected' : '' ?>>Unreconciled</option>
        </select>
        <button type="submit" title="Apply filters">Filter</button>
        <a href="reports_dashboard.php" style="margin-left:20px; color:#ffa500; font-weight:600; align-self:center; text-decoration:none;" aria-label="Back to reports dashboard">← Back to Dashboard</a>
    </form>

    <div class="chart-container" role="img" aria-label="Bar chart showing total deposits vs total sales by account">
        <canvas id="varianceChart" height="320"></canvas>
    </div>

    <table aria-describedby="varianceReportDesc" role="table">
        <caption id="varianceReportDesc" style="text-align:left; font-weight:bold; margin-bottom:8px; color:#ffa500;">
            Variance report showing deposits, sales, and variance per transaction with reconciliation status.
        </caption>
        <thead>
            <tr>
                <th scope="col">Transaction Date</th>
                <th scope="col">Account</th>
                <th scope="col" class="text-center">Bank Deposit (MWK)</th>
                <th scope="col" class="text-center">Sales Revenue (MWK)</th>
                <th scope="col" class="text-center">Variance (MWK)</th>
                <th scope="col" class="text-center">Reconciliation Status</th>
                <th scope="col">Created By</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach ($records as $r):
                $deposit = floatval($r['bank_deposit']);
                $sale = floatval($r['sales_revenue']);
                $variance = $sale - $deposit;
                $variance_class = $variance == 0 ? 'variance-positive' : ($variance > 0 ? 'variance-positive' : 'variance-negative');

                // Format date/time nicely
                $display_date = $r['transaction_date'] ? date('Y-m-d H:i', strtotime($r['transaction_date'])) : 'N/A';

                // Status display & class
                $status = strtolower($r['reconciliation_status'] ?? 'pending');
                $status_class = $status === 'reconciled' ? 'status-reconciled' : 'status-pending';

                $user_name = htmlspecialchars(trim($r['first_name'] . ' ' . $r['last_name'])) ?: 'Unknown';
            ?>
            <tr>
                <td><?= $display_date ?></td>
                <td><?= htmlspecialchars($r['account_name'] . ' (' . ucfirst($r['account_type']) . ')') ?></td>
                <td class="numeric"><?= number_format($deposit, 2) ?></td>
                <td class="numeric"><?= number_format($sale, 2) ?></td>
                <td class="numeric <?= $variance_class ?>"><?= number_format($variance, 2) ?></td>
                <td class="<?= $status_class ?>"><?= ucfirst($status) ?></td>
                <td><?= $user_name ?></td>
            </tr>
            <?php endforeach; ?>
            <?php if (empty($records)): ?>
            <tr>
                <td colspan="7" style="text-align:center; padding:30px; color:#ffa500;">
                    No records found for the selected criteria.
                </td>
            </tr>
            <?php endif; ?>
        </tbody>
        <tfoot>
            <tr>
                <td colspan="2" style="text-align:right;">TOTALS:</td>
                <td class="numeric"><?= number_format($total_deposits, 2) ?></td>
