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

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

// Build sales filter
$where_sales = "WHERE DATE(st.created_at) BETWEEN ? AND ?";
$params_sales = [$date_from, $date_to];
$types_sales = "ss";

if ($store_filter > 0) {
    $where_sales .= " AND st.store_id = ?";
    $params_sales[] = $store_filter;
    $types_sales .= "i";
}
if ($cashier_filter > 0) {
    $where_sales .= " AND st.sold_by = ?";
    $params_sales[] = $cashier_filter;
    $types_sales .= "i";
}

// Fetch sales transactions
$stmt = $conn->prepare("
    SELECT st.id, st.total_amount, st.discount_total, st.vat_total
    FROM sales_transactions st
    $where_sales
");
$stmt->bind_param($types_sales, ...$params_sales);
$stmt->execute();
$sales = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();

// Calculate totals from sales
$total_revenue = 0;
$total_discount = 0;
$total_vat = 0;
$sale_ids = [];

foreach ($sales as $sale) {
    $total_revenue += $sale['total_amount'];
    $total_discount += $sale['discount_total'];
    $total_vat += $sale['vat_total'];
    $sale_ids[] = $sale['id'];
}

// Calculate COGS from sales_items and products
$total_cogs = 0;
if (count($sale_ids) > 0) {
    $placeholders = implode(',', array_fill(0, count($sale_ids), '?'));
    $types_cogs = str_repeat('i', count($sale_ids));
    $sql_cogs = "
        SELECT SUM(p.cost_price * si.quantity) AS cogs
        FROM sales_items si
        JOIN products p ON si.product_id = p.id
        WHERE si.transaction_id IN ($placeholders)
    ";
    $stmt = $conn->prepare($sql_cogs);
    $stmt->bind_param($types_cogs, ...$sale_ids);
    $stmt->execute();
    $res = $stmt->get_result()->fetch_assoc();
    $total_cogs = $res['cogs'] ?? 0;
    $stmt->close();
}

// Fetch expenses for the selected period, store and cashier (if applicable)
$where_expenses = "WHERE DATE(e.created_at) BETWEEN ? AND ?";
$params_expenses = [$date_from, $date_to];
$types_expenses = "ss";

if ($store_filter > 0) {
    $where_expenses .= " AND e.store_id = ?";
    $params_expenses[] = $store_filter;
    $types_expenses .= "i";
}
if ($cashier_filter > 0) {
    $where_expenses .= " AND e.entered_by = ?";
    $params_expenses[] = $cashier_filter;
    $types_expenses .= "i";
}

$stmt = $conn->prepare("
    SELECT SUM(e.amount) AS total_expenses
    FROM expenses e
    $where_expenses
");
$stmt->bind_param($types_expenses, ...$params_expenses);
$stmt->execute();
$expense_row = $stmt->get_result()->fetch_assoc();
$total_expenses = $expense_row['total_expenses'] ?? 0;
$stmt->close();

// Calculate profits
$gross_profit = $total_revenue - $total_cogs;
$net_profit = $gross_profit - $total_expenses;

?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8" />
    <title>Profit & Loss Statement</title>
    <meta name="viewport" content="width=device-width, initial-scale=1" />
    <style>
        body {
            font-family: 'Segoe UI', sans-serif;
            background: url('../../images/profile.jpg') no-repeat center center fixed;
            background-size: cover;
            margin: 0;
            color: white;
        }
        .overlay {
            position: fixed;
            inset: 0;
            background: rgba(0,0,0,0.65);
            z-index: -1;
        }
        .container {
            max-width: 1100px;
            margin: 50px auto;
            padding: 30px 35px;
            border-radius: 15px;
            background: rgba(255,255,255,0.1);
            backdrop-filter: blur(12px);
            box-shadow: 0 8px 32px 0 rgba(255,165,0,0.25);
        }
        h2, h3 {
            text-align: center;
            color: #ffa500;
            margin-bottom: 25px;
        }
        .filters form {
            display: flex;
            flex-wrap: wrap;
            justify-content: center;
            gap: 12px;
            margin-bottom: 35px;
        }
        .filters input, .filters select, .filters button {
            padding: 10px 14px;
            border-radius: 8px;
            border: none;
            background: rgba(255,255,255,0.15);
            color: white;
            font-weight: 600;
            transition: background-color 0.3s ease;
            min-width: 140px;
        }
        .filters input:hover, .filters select:hover, .filters button:hover {
            background: rgba(255,165,0,0.3);
            cursor: pointer;
        }
        .filters button {
            background: #ffa500;
            color: black;
            min-width: 130px;
        }
        .summary-cards {
            display: flex;
            flex-wrap: wrap;
            gap: 18px;
            justify-content: center;
            margin-bottom: 40px;
        }
        .summary-cards div {
            background: rgba(255,255,255,0.12);
            border-radius: 12px;
            padding: 25px 20px;
            flex: 1 1 220px;
            text-align: center;
            box-shadow: 0 4px 15px rgba(255,165,0,0.3);
            font-weight: 700;
            font-size: 1.15rem;
        }
        .summary-cards div span {
            color: #ffa500;
            display: block;
            margin-bottom: 12px;
            font-size: 1.3rem;
        }
        table {
            width: 100%;
            border-collapse: collapse;
            background: rgba(255,255,255,0.05);
            margin-top: 20px;
            border-radius: 12px;
            overflow: hidden;
            box-shadow: 0 8px 32px 0 rgba(255,165,0,0.15);
        }
        th, td {
            padding: 14px 12px;
            border-bottom: 1px solid rgba(255,255,255,0.1);
            text-align: center;
            font-size: 1rem;
        }
        th {
            background: rgba(0,0,0,0.45);
            color: #ffa500;
            font-weight: 700;
            letter-spacing: 0.05em;
        }
        tbody tr:hover {
            background: rgba(255,165,0,0.1);
        }
        .export {
            text-align: right;
            margin-top: 18px;
        }
        .export a {
            color: #ffa500;
            text-decoration: none;
            margin-left: 20px;
            font-weight: 700;
            transition: color 0.3s ease;
        }
        .export a:hover {
            color: #fff;
        }
        @media (max-width: 768px) {
            .filters form {
                flex-direction: column;
                align-items: center;
            }
            .summary-cards {
                flex-direction: column;
                gap: 25px;
            }
            table, thead, tbody, th, td, tr {
                display: block;
            }
            thead tr {
                position: absolute;
                top: -9999px;
                left: -9999px;
            }
            tr {
                margin-bottom: 1.7rem;
            }
            td {
                padding-left: 50%;
                position: relative;
                text-align: left;
                border-bottom: 1px solid rgba(255,255,255,0.1);
            }
            td::before {
                position: absolute;
                top: 14px;
                left: 14px;
                width: 45%;
                padding-right: 10px;
                white-space: nowrap;
                font-weight: 700;
                color: #ffa500;
            }
            td:nth-of-type(1)::before { content: "Date"; }
            td:nth-of-type(2)::before { content: "Description"; }
            td:nth-of-type(3)::before { content: "Amount (MWK)"; }
        }
    </style>
</head>
<body>
<div class="overlay"></div>
<div class="container">
    <h2>📊 Profit & Loss Statement</h2>

    <div class="filters">
        <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>
        </form>
    </div>

    <div class="summary-cards">
        <div>
            <span>Total Revenue</span>
            MWK <?= number_format($total_revenue, 2) ?>
        </div>
        <div>
            <span>Cost of Goods Sold (COGS)</span>
            MWK <?= number_format($total_cogs, 2) ?>
        </div>
        <div>
            <span>Gross Profit</span>
            MWK <?= number_format($gross_profit, 2) ?>
        </div>
        <div>
            <span>Total Expenses</span>
            MWK <?= number_format($total_expenses, 2) ?>
        </div>
        <div>
            <span>Net Profit</span>
            MWK <?= number_format($net_profit, 2) ?>
        </div>
    </div>

    <h3>Expenses Details</h3>
    <table>
        <thead>
        <tr>
            <th>Date</th>
            <th>Description</th>
            <th>Amount (MWK)</th>
        </tr>
        </thead>
        <tbody>
        <?php
        // Fetch expense details for listing
        $stmt = $conn->prepare("
            SELECT e.created_at, e.description, e.amount
            FROM expenses e
            WHERE DATE(e.created_at) BETWEEN ? AND ?
            " . ($store_filter > 0 ? " AND e.store_id = ?" : "") . ($cashier_filter > 0 ? " AND e.entered_by = ?" : "") . "
            ORDER BY e.created_at DESC
        ");
        $params_exp_details = [$date_from, $date_to];
        $types_exp_details = "ss";
        if ($store_filter > 0) {
            $params_exp_details[] = $store_filter;
            $types_exp_details .= "i";
        }
        if ($cashier_filter > 0) {
            $params_exp_details[] = $cashier_filter;
            $types_exp_details .= "i";
        }
        $stmt->bind_param($types_exp_details, ...$params_exp_details);
        $stmt->execute();
        $expenses_list = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
        $stmt->close();

        if (count($expenses_list) === 0) {
            echo '<tr><td colspan="3" style="color:#ffa500;">No expenses recorded.</td></tr>';
        } else {
            foreach ($expenses_list as $expense) {
                echo '<tr>
                    <td>' . htmlspecialchars(date('Y-m-d', strtotime($expense['created_at']))) . '</td>
                    <td>' . htmlspecialchars($expense['description']) . '</td>
                    <td>' . number_format($expense['amount'], 2) . '</td>
                </tr>';
            }
        }
        ?>
        </tbody>
    </table>

    <div class="export">
        <a href="#">📄 Export PDF</a>
        <a href="#">📊 Export Excel</a>
    </div>
</div>
</body>
</html>
