<?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');
include('../../includes/footer.php');

// Filters
$date_from = $_GET['date_from'] ?? date('Y-m-01');
$date_to = $_GET['date_to'] ?? date('Y-m-d');
$store_filter = isset($_GET['store_id']) ? intval($_GET['store_id']) : 0;
$cashier_filter = isset($_GET['cashier_id']) ? intval($_GET['cashier_id']) : 0;

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');

$stores = $conn->query("SELECT id, store_name FROM stores WHERE deleted_at IS NULL OR deleted_at = '0000-00-00 00:00:00' ORDER BY store_name")->fetch_all(MYSQLI_ASSOC);
$cashiers = $conn->query("SELECT id, CONCAT(first_name, ' ', last_name) AS name FROM users WHERE role_id = 3")->fetch_all(MYSQLI_ASSOC);

$where = "WHERE DATE(st.created_at) BETWEEN ? AND ?";
$params = [$date_from, $date_to];
$types = "ss";

if ($store_filter > 0) {
    $where .= " AND st.store_id = ?";
    $params[] = $store_filter;
    $types .= "i";
}
if ($cashier_filter > 0) {
    $where .= " AND st.sold_by = ?";
    $params[] = $cashier_filter;
    $types .= "i";
}

$stmt = $conn->prepare("
    SELECT st.*, s.store_name, u.first_name, u.last_name
    FROM sales_transactions st
    JOIN stores s ON st.store_id = s.id
    JOIN users u ON st.sold_by = u.id
    $where
    ORDER BY st.created_at DESC
");
$stmt->bind_param($types, ...$params);
$stmt->execute();
$sales = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();

$total_sales = $total_vat = $total_discount = $total_net_sales = 0;
$total_gross_profit = $total_net_profit = 0;
$store_summary = [];
$cashier_summary = [];

foreach ($sales as $sale) {
    $total_sales += $sale['total_amount'];
    $total_vat += $sale['vat_total'];
    $total_discount += $sale['discount_total'];
    $net = $sale['total_amount'] - $sale['vat_total'];
    $total_net_sales += $net;

    $store = $sale['store_name'];
    if (!isset($store_summary[$store])) {
        $store_summary[$store] = ['gross' => 0, 'net' => 0, 'vat' => 0, 'txns' => 0];
    }
    $store_summary[$store]['gross'] += $sale['total_amount'];
    $store_summary[$store]['net'] += $net;
    $store_summary[$store]['vat'] += $sale['vat_total'];
    $store_summary[$store]['txns']++;

    $cashier = $sale['first_name'] . ' ' . $sale['last_name'];
    if (!isset($cashier_summary[$cashier])) {
        $cashier_summary[$cashier] = ['gross' => 0, 'net' => 0, 'vat' => 0, 'txns' => 0];
    }
    $cashier_summary[$cashier]['gross'] += $sale['total_amount'];
    $cashier_summary[$cashier]['net'] += $net;
    $cashier_summary[$cashier]['vat'] += $sale['vat_total'];
    $cashier_summary[$cashier]['txns']++;
}

// Calculate profit per transaction correctly
$transaction_ids = array_column($sales, 'id');
if (!$transaction_ids) {
    $transaction_ids = [0]; // prevent SQL error if empty
}

// Updated SQL: calculate gross profit and net profit properly
$in = implode(',', array_fill(0, count($transaction_ids), '?'));
$types_profit = str_repeat('i', count($transaction_ids));

$sql = "
    SELECT si.transaction_id, 
           SUM((si.unit_price - p.cost_price) * si.quantity) AS gross_profit,
           SUM((si.unit_price - p.cost_price) * si.quantity) - SUM(si.discount) AS net_profit
    FROM sales_items si
    JOIN products p ON si.product_id = p.id
    WHERE si.transaction_id IN ($in)
    GROUP BY si.transaction_id
";

$profit_stmt = $conn->prepare($sql);
$profit_stmt->bind_param($types_profit, ...$transaction_ids);
$profit_stmt->execute();
$profit_result = $profit_stmt->get_result();

$transaction_profits = [];
$total_gross_profit = 0;
$total_net_profit = 0;

while ($row = $profit_result->fetch_assoc()) {
    $transaction_profits[$row['transaction_id']] = [
        'gross_profit' => floatval($row['gross_profit']),
        'net_profit' => floatval($row['net_profit'])
    ];
    $total_gross_profit += floatval($row['gross_profit']);
    $total_net_profit += floatval($row['net_profit']);
}
$profit_stmt->close();

$page_title = "Accountant Sales Report";
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8" />
    <title><?= htmlspecialchars($page_title) ?></title>
    <link rel="icon" type="image/png" href="../../images/logo.png" />
    <style>
        /* ... same styles as your previous code ... */
        body {
            margin: 0;
            background: url('../../images/profile.jpg') no-repeat center center fixed;
            background-size: cover;
            font-family: 'Segoe UI', sans-serif;
            color: #eee;
        }
        .content {
            margin: 2rem auto 0 auto;
            padding: 40px;
            max-width: 1850px;
        }
        .page-header {
            display: flex;
            justify-content: space-between;
            align-items: center;
            margin-bottom: 1rem;
        }
        h1.page-title {
            color: #ffa500;
            margin: 0;
            display: flex;
            align-items: center;
            gap: 10px;
            font-weight: 700;
            font-size: 2rem;
        }
        h1.page-title img {
            height: 40px;
            width: auto;
        }
        a.back-link {
            color: #ffa500;
            text-decoration: none;
            font-weight: 600;
            border: 1.5px solid #ffa500;
            padding: 6px 14px;
            border-radius: 8px;
            transition: background-color 0.3s ease, color 0.3s ease;
            white-space: nowrap;
        }
        a.back-link:hover {
            background-color: #ffa500;
            color: #222;
        }
        .filter-form {
            margin: 30px 0;
            display: flex;
            gap: 15px;
            flex-wrap: wrap;
        }
        .filter-form input,
        .filter-form select,
        .filter-form button,
        .btn-action {
            padding: 10px 15px;
            border-radius: 8px;
            border: none;
            font-weight: 600;
            background: rgba(255,255,255,0.15);
            color: #fff;
        }
        .filter-form button {
            background: #ffa500;
            color: #222;
            cursor: pointer;
        }
        .btn-action {
            background: #ffa500;
            color: #222;
            text-decoration: none;
        }
        .btn-action:hover {
            background: #fff;
            color: #000;
        }
        .summary, .cards {
            display: flex;
            flex-wrap: wrap;
            gap: 20px;
            margin-bottom: 30px;
        }
        .cards > div {
            flex: 1;
            min-width: 180px;
            padding: 20px;
            background: rgba(255,255,255,0.1);
            border-radius: 12px;
            box-shadow: 0 4px 15px rgba(255,165,0,0.25);
            text-align: center;
            font-weight: 700;
        }
        .cards strong {
            display: block;
            color: #ffa500;
            margin-bottom: 10px;
        }
        h2 {
            color: #ffa500;
            margin-bottom: 10px;
        }
        .card {
            background: rgba(255,255,255,0.1);
            border-radius: 15px;
            padding: 20px;
        }
        table {
            width: 100%;
            border-collapse: collapse;
            font-size: 14px;
        }
        table th, table td {
            padding: 10px 12px;
            border-bottom: 1px solid rgba(255,255,255,0.1);
            text-align: center;
        }
        table th {
            background: rgba(255,165,0,0.2);
            color: #fff;
        }
    </style>
</head>
<body>
<div class="content">
    <div class="page-header">
        <h1 class="page-title">
            <img src="../../images/logo.png" alt="Logo" />
            <?= htmlspecialchars($page_title) ?>
        </h1>
        <a href="reports_dashboard.php" class="back-link">&laquo; Back to Reports Dashboard</a>
    </div>

    <form class="filter-form" method="GET" novalidate>
        <input type="date" name="date_from" value="<?= htmlspecialchars($date_from) ?>" required>
        <input type="date" name="date_to" value="<?= htmlspecialchars($date_to) ?>" required>
        <select name="store_id">
            <option value="0">All Stores</option>
            <?php foreach ($stores as $s): ?>
                <option value="<?= $s['id'] ?>" <?= $store_filter == $s['id'] ? 'selected' : '' ?>>
                    <?= htmlspecialchars($s['store_name']) ?>
                </option>
            <?php endforeach; ?>
        </select>
        <select name="cashier_id">
            <option value="0">All Cashiers</option>
            <?php foreach ($cashiers as $c): ?>
                <option value="<?= $c['id'] ?>" <?= $cashier_filter == $c['id'] ? 'selected' : '' ?>>
                    <?= htmlspecialchars($c['name']) ?>
                </option>
            <?php endforeach; ?>
        </select>
        <button type="submit">Apply Filters</button>
        <a href="#" class="btn-action">📄 Export PDF</a>
        <a href="#" class="btn-action">📊 Export Excel</a>
    </form>

    <div class="cards">
        <div><strong>Total Gross Sales</strong> MWK <?= number_format($total_sales, 2) ?></div>
        <div><strong>Total Net Sales</strong> MWK <?= number_format($total_net_sales, 2) ?></div>
        <div><strong>Total VAT</strong> MWK <?= number_format($total_vat, 2) ?></div>
        <div><strong>Total Discounts</strong> MWK <?= number_format($total_discount, 2) ?></div>
        <div><strong>Total Gross Profit</strong> MWK <?= number_format($total_gross_profit, 2) ?></div>
        <div><strong>Total Net Profit</strong> MWK <?= number_format($total_net_profit, 2) ?></div>
    </div>

    <div class="card">
        <table>
            <thead>
                <tr>
                    <th>Date</th>
                    <th>Txn ID</th>
                    <th>Store</th>
                    <th>Cashier</th>
                    <th>Gross</th>
                    <th>Net</th>
                    <th>VAT</th>
                    <th>Discount</th>
                    <th>Profit</th>
                </tr>
            </thead>
            <tbody>
            <?php foreach ($sales as $sale):
                $txn_id = $sale['id'];
                $gross_profit = $transaction_profits[$txn_id]['gross_profit'] ?? 0;
                $net_profit = $transaction_profits[$txn_id]['net_profit'] ?? 0;
            ?>
                <tr>
                    <td><?= htmlspecialchars(date('Y-m-d', strtotime($sale['created_at']))) ?></td>
                    <td><?= htmlspecialchars($sale['receipt_number']) ?></td>
                    <td><?= htmlspecialchars($sale['store_name']) ?></td>
                    <td><?= htmlspecialchars($sale['first_name'] . ' ' . $sale['last_name']) ?></td>
                    <td><?= number_format($sale['total_amount'], 2) ?></td>
                    <td><?= number_format($sale['total_amount'] - $sale['vat_total'], 2) ?></td>
                    <td><?= number_format($sale['vat_total'], 2) ?></td>
                    <td><?= number_format($sale['discount_total'], 2) ?></td>
                    <td>
                        <small>Gross: MWK <?= number_format($gross_profit, 2) ?></small><br>
                        <small>Net: MWK <?= number_format($net_profit, 2) ?></small>
                    </td>
                </tr>
            <?php endforeach; ?>
            </tbody>
        </table>
    </div>
</div>
</body>
</html>
