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

$filter_store = $_GET['store_id'] ?? '';
$date_from = $_GET['from_date'] ?? date('Y-m-01');
$date_to = $_GET['to_date'] ?? date('Y-m-d');

$store_filter_sql = "";
$store_param = null;
if ($filter_store !== '' && is_numeric($filter_store)) {
    $store_filter_sql = " AND st.store_id = ? ";
    $store_param = (int)$filter_store;
}

// 1. Total Income (net of VAT)
if ($store_param !== null) {
    $income_sql = "
        SELECT SUM(st.total_amount - st.vat_total) AS net_sales
        FROM sales_transactions st
        WHERE st.status = 'completed'
          AND st.created_at BETWEEN ? AND ?
          $store_filter_sql
    ";
    $stmt = $conn->prepare($income_sql);
    $stmt->bind_param("ssi", $date_from, $date_to, $store_param);
} else {
    $income_sql = "
        SELECT SUM(st.total_amount - st.vat_total) AS net_sales
        FROM sales_transactions st
        WHERE st.status = 'completed'
          AND st.created_at BETWEEN ? AND ?
    ";
    $stmt = $conn->prepare($income_sql);
    $stmt->bind_param("ss", $date_from, $date_to);
}
$stmt->execute();
$result = $stmt->get_result();
$total_income = ($row = $result->fetch_assoc()) ? ($row['net_sales'] ?? 0) : 0;
$stmt->close();

// 2. COGS
if ($store_param !== null) {
    $cogs_sql = "
        SELECT SUM(si.quantity * p.cost_price) AS total_cogs
        FROM sales_items si
        INNER JOIN products p ON si.product_id = p.id
        INNER JOIN sales_transactions st ON si.transaction_id = st.id
        WHERE st.status = 'completed'
          AND st.created_at BETWEEN ? AND ?
          $store_filter_sql
    ";
    $stmt = $conn->prepare($cogs_sql);
    $stmt->bind_param("ssi", $date_from, $date_to, $store_param);
} else {
    $cogs_sql = "
        SELECT SUM(si.quantity * p.cost_price) AS total_cogs
        FROM sales_items si
        INNER JOIN products p ON si.product_id = p.id
        INNER JOIN sales_transactions st ON si.transaction_id = st.id
        WHERE st.status = 'completed'
          AND st.created_at BETWEEN ? AND ?
    ";
    $stmt = $conn->prepare($cogs_sql);
    $stmt->bind_param("ss", $date_from, $date_to);
}
$stmt->execute();
$result = $stmt->get_result();
$cogs = ($row = $result->fetch_assoc()) ? ($row['total_cogs'] ?? 0) : 0;
$stmt->close();

$gross_profit = $total_income - $cogs;

// 3. Operating Expenses
if ($store_param !== null) {
    $expenses_sql = "
        SELECT SUM(amount) AS total_expense
        FROM expenses
        WHERE status = 'approved'
          AND deleted_at IS NULL
          AND expense_date BETWEEN ? AND ?
          AND store_id = ?
    ";
    $stmt = $conn->prepare($expenses_sql);
    $stmt->bind_param("ssi", $date_from, $date_to, $store_param);
} else {
    $expenses_sql = "
        SELECT SUM(amount) AS total_expense
        FROM expenses
        WHERE status = 'approved'
          AND deleted_at IS NULL
          AND expense_date BETWEEN ? AND ?
    ";
    $stmt = $conn->prepare($expenses_sql);
    $stmt->bind_param("ss", $date_from, $date_to);
}
$stmt->execute();
$result = $stmt->get_result();
$operating_expenses = ($row = $result->fetch_assoc()) ? ($row['total_expense'] ?? 0) : 0;
$stmt->close();

$net_profit = $gross_profit - $operating_expenses;



$page_title = "Income Statement (Profit & Loss)";
?>

<!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" />
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet"/>
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>

    <style>
    body {
        background: url('../../images/profile.jpg') center/cover fixed;
        color: #eee;
        font-family: 'Segoe UI', sans-serif;
        margin: 0;
        padding: 0;
    }
    .wrap {
        max-width: 1850px;
        margin: 80px auto;
        padding: 40px;
        background: rgba(255,255,255,0.08);
        border-radius: 15px;
    }
    .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;
    }
    h2 {
        color: #ffa500;
        margin-top: 1rem;
    }
    .filters {
        margin: 20px 0 30px;
        display: flex;
        flex-wrap: wrap;
        gap: 10px;
    }
    .filters input, .filters select, .filters button {
        padding: 8px 12px;
        border-radius: 8px;
        border: none;
        font-weight: 500;
    }
    .filters button {
        background: #ffa500;
        color: #222;
        cursor: pointer;
    }
    table {
        width: 100%;
        border-collapse: collapse;
        margin-top: 20px;
        font-size: 15px;
        color: #eee;
    }
    th, td {
        padding: 10px 12px;
        border-bottom: 1px solid rgba(18, 118, 34, 0.1);
        text-align: left;
    }
    td:last-child, th:last-child {
        text-align: right;
    }
    th {
        background: rgba(255,165,0,0.25);
        color: #fff;
    }
    .total {
        font-weight: bold;
    }
    .profit {
        background: rgba(255, 187, 0, 0.73);
        font-weight: bold;
        color: #000000ff;
    }
    .pointer {
        cursor: pointer;
        text-decoration: underline;
    }
    @media print {
        .filters, button, .page-header {
            display: none;
        }
        body {
            background: #fff;
            color: #000;
        }
        table {
            color: #000;
        }
    }
    </style>
</head>
<body>

<div class="wrap">
    <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="filters" method="GET" action="">
        <input type="date" name="from_date" value="<?= htmlspecialchars($date_from) ?>">
        <input type="date" name="to_date" value="<?= htmlspecialchars($date_to) ?>">
        <select name="store_id">
            <option value="">All Stores</option>
            <?php
            $stores = $conn->query("SELECT id, store_name FROM stores");
            while ($st = $stores->fetch_assoc()) {
                $selected = ($filter_store == $st['id']) ? 'selected' : '';
                echo "<option value='" . htmlspecialchars($st['id']) . "' $selected>" . htmlspecialchars($st['store_name']) . "</option>";
            }
            ?>
        </select>
        <button type="submit">Filter</button>
        <button type="button" onclick="window.print()">🖨️ Print</button>
        <button type="button" id="exportExcel" class="btn btn-success btn-sm ms-2">Export Excel</button>
        <button type="button" id="exportPDF" class="btn btn-danger btn-sm ms-2">Export PDF</button>
    </form>

    <table>
        <thead><tr><th colspan="2">Total Income (Net of VAT)</th></tr></thead>
        <tbody>
            <tr>
                <td><span class="pointer" data-type="income" id="drill-income">Completed Sales (click to see details)</span></td>
                <td><?= number_format($total_income, 2) ?></td>
            </tr>
            <tr class="total">
                <td>Total Income</td>
                <td><?= number_format($total_income, 2) ?></td>
            </tr>
        </tbody>

        <thead><tr><th colspan="2">Cost of Goods Sold (COGS)</th></tr></thead>
        <tbody>
            <tr>
                <td><span class="pointer" data-type="cogs" id="drill-cogs">Products Sold (click to see details)</span></td>
                <td><?= number_format($cogs, 2) ?></td>
            </tr>
            <tr class="total">
                <td>Total COGS</td>
                <td><?= number_format($cogs, 2) ?></td>
            </tr>
        </tbody>

        <thead><tr><th colspan="2">Gross Profit</th></tr></thead>
        <tbody>
            <tr class="profit">
                <td>Gross Profit</td>
                <td><?= number_format($gross_profit, 2) ?></td>
            </tr>
        </tbody>

        <thead><tr><th colspan="2">Operating Expenses</th></tr></thead>
        <tbody>
            <tr>
                <td><span class="pointer" data-type="expenses" id="drill-expenses">Approved Expenses (click to see details)</span></td>
                <td><?= number_format($operating_expenses, 2) ?></td>
            </tr>
            <tr class="total">
                <td>Total Expenses</td>
                <td><?= number_format($operating_expenses, 2) ?></td>
            </tr>
        </tbody>

        <thead><tr><th colspan="2">Net Profit</th></tr></thead>
        <tbody>
            <tr class="profit">
                <td><span class="pointer" data-type="netprofit" id="drill-netprofit">Net Profit (click to see breakdown)</span></td>
                <td><?= number_format($net_profit, 2) ?></td>
            </tr>
        </tbody>
    </table>
</div>

<!-- Modal for drill down -->
<div class="modal fade" id="drillModal" tabindex="-1" aria-labelledby="drillModalLabel" aria-hidden="true">
  <div class="modal-dialog modal-xl modal-dialog-scrollable">
    <div class="modal-content bg-dark text-light">
      <div class="modal-header">
        <h5 class="modal-title" id="drillModalLabel">Details</h5>
        <button type="button" class="btn-close btn-close-white" data-bs-dismiss="modal" aria-label="Close"></button>
      </div>
      <div class="modal-body" id="drillModalBody">
        Loading...
      </div>
    </div>
  </div>
</div>

<script>
$(document).ready(function(){
    function fetchDetails(type) {
        let data = {
            from_date: "<?= $date_from ?>",
            to_date: "<?= $date_to ?>",
            store_id: "<?= $filter_store ?>",
            detail_type: type
        };
        $("#drillModalBody").html("Loading...");
        $("#drillModalLabel").text(type.charAt(0).toUpperCase() + type.slice(1) + " Details");
        $("#drillModal").modal('show');

        $.ajax({
            url: 'income_statement_details.php',
            method: 'POST',
            data: data,
            success: function(response) {
                $("#drillModalBody").html(response);
            },
            error: function() {
                $("#drillModalBody").html("<p class='text-danger'>Failed to load details.</p>");
            }
        });
    }

    $("#drill-income").click(function(){ fetchDetails('income'); });
    $("#drill-cogs").click(function(){ fetchDetails('cogs'); });
    $("#drill-expenses").click(function(){ fetchDetails('expenses'); });
    $("#drill-netprofit").click(function(){ fetchDetails('netprofit'); });

    $("#exportExcel").click(function(){
        const params = new URLSearchParams({
            from_date: "<?= $date_from ?>",
            to_date: "<?= $date_to ?>",
            store_id: "<?= $filter_store ?>"
        });
        window.location.href = 'income_statement_export.php?export=excel&' + params.toString();
    });

    $("#exportPDF").click(function(){
        const params = new URLSearchParams({
            from_date: "<?= $date_from ?>",
            to_date: "<?= $date_to ?>",
            store_id: "<?= $filter_store ?>"
        });
        window.open('income_statement_export.php?export=pdf&' + params.toString(), '_blank');
    });
});
</script>

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