<?php
session_start();

require_once '../../config/database.php';
require_once '../../includes/auth.php';

$db = (new Database())->connect();

$search = trim($_GET['search'] ?? '');
$fromDate = trim($_GET['from_date'] ?? '');
$toDate = trim($_GET['to_date'] ?? '');
$page = max(1, (int)($_GET['page'] ?? 1));

$allowedLimits = [10, 25, 50, 100];
$limit = (int)($_GET['limit'] ?? 10);

if (!in_array($limit, $allowedLimits)) {
    $limit = 10;
}

$offset = ($page - 1) * $limit;

$where = [];
$params = [];

$where[] = "o.status = 'PAID'";

/*
    Item search
*/
if (!empty($search)) {
    $where[] = "i.name LIKE ?";
    $params[] = "%$search%";
}

/*
    Date filters
*/
if (!empty($fromDate)) {
    $where[] = "DATE(o.order_date) >= ?";
    $params[] = $fromDate;
}

if (!empty($toDate)) {
    $where[] = "DATE(o.order_date) <= ?";
    $params[] = $toDate;
}

$whereSql = ' WHERE ' . implode(' AND ', $where);

/*
    COUNT
*/
$countSql = "
    SELECT COUNT(*)
    FROM (
        SELECT oi.item_id
        FROM order_items oi
        INNER JOIN items i ON oi.item_id = i.id
        INNER JOIN orders o ON oi.order_id = o.id
        $whereSql
        GROUP BY oi.item_id
    ) x
";

$stmt = $db->prepare($countSql);
$stmt->execute($params);
$totalRecords = $stmt->fetchColumn();

$totalPages = ceil($totalRecords / $limit);

/*
    DATA
*/
$dataSql = "
    SELECT
        i.id,
        i.name,
        SUM(oi.quantity) AS qty_sold,
        AVG(oi.price) AS avg_sale_price,
        SUM(oi.quantity * oi.price) AS revenue,

        (
            SELECT se.rate
            FROM stock_entries se
            WHERE se.item_id = i.id
            AND se.type = 'IN'
            ORDER BY se.stock_date DESC, se.id DESC
            LIMIT 1
        ) AS latest_cost

    FROM order_items oi
    INNER JOIN items i ON oi.item_id = i.id
    INNER JOIN orders o ON oi.order_id = o.id
    $whereSql
    GROUP BY oi.item_id
    ORDER BY revenue DESC
    LIMIT $limit OFFSET $offset
";

$stmt = $db->prepare($dataSql);
$stmt->execute($params);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

/*
    Totals
*/
$totalRevenue = 0;
$totalCost = 0;
$totalProfit = 0;
?>

<div class="card shadow-sm">
    <div class="card-body table-responsive">

        <table class="table table-bordered table-hover align-middle">

            <thead class="table-dark">
                <tr>
                    <th>Item</th>
                    <th>Qty Sold</th>
                    <th>Avg Sale Price</th>
                    <th>Revenue</th>
                    <th>Cost Price</th>
                    <th>Total Cost</th>
                    <th>Profit</th>
                    <th>Margin %</th>
                </tr>
            </thead>

            <tbody>

            <?php if (empty($rows)): ?>
                <tr>
                    <td colspan="8" class="text-center">
                        No profit data found.
                    </td>
                </tr>
            <?php endif; ?>

            <?php foreach ($rows as $row): ?>

                <?php
                $qty = (float)$row['qty_sold'];
                $revenue = (float)$row['revenue'];
                $costPrice = (float)($row['latest_cost'] ?? 0);
                $totalCostRow = $qty * $costPrice;
                $profit = $revenue - $totalCostRow;

                $margin = 0;
                if ($revenue > 0) {
                    $margin = ($profit / $revenue) * 100;
                }

                $totalRevenue += $revenue;
                $totalCost += $totalCostRow;
                $totalProfit += $profit;
                ?>

                <tr>
                    <td><?= htmlspecialchars($row['name']) ?></td>

                    <td><?= number_format($qty, 2) ?></td>

                    <td>
                        ₹ <?= number_format((float)$row['avg_sale_price'], 2) ?>
                    </td>

                    <td>
                        ₹ <?= number_format($revenue, 2) ?>
                    </td>

                    <td>
                        ₹ <?= number_format($costPrice, 2) ?>
                    </td>

                    <td>
                        ₹ <?= number_format($totalCostRow, 2) ?>
                    </td>

                    <td>
                        ₹ <?= number_format($profit, 2) ?>
                    </td>

                    <td>
                        <?= number_format($margin, 2) ?>%
                    </td>
                </tr>

            <?php endforeach; ?>

            </tbody>

            <?php if (!empty($rows)): ?>

            <tfoot class="table-light">
                <tr>
                    <th colspan="3">Totals</th>

                    <th>
                        ₹ <?= number_format($totalRevenue, 2) ?>
                    </th>

                    <th></th>

                    <th>
                        ₹ <?= number_format($totalCost, 2) ?>
                    </th>

                    <th>
                        ₹ <?= number_format($totalProfit, 2) ?>
                    </th>

                    <th>
                        <?php
                        $overallMargin = 0;
                        if ($totalRevenue > 0) {
                            $overallMargin = ($totalProfit / $totalRevenue) * 100;
                        }
                        ?>
                        <?= number_format($overallMargin, 2) ?>%
                    </th>
                </tr>
            </tfoot>

            <?php endif; ?>

        </table>

        <!-- PAGINATION -->
        <?php if ($totalPages > 1): ?>
            <nav>
                <ul class="pagination justify-content-center">

                    <?php if ($page > 1): ?>
                        <li class="page-item">
                            <button
                                class="page-link"
                                onclick="loadProfitReport(<?= $page - 1 ?>)"
                            >
                                Previous
                            </button>
                        </li>
                    <?php endif; ?>

                    <?php for ($i = 1; $i <= $totalPages; $i++): ?>
                        <li class="page-item <?= $page == $i ? 'active' : '' ?>">
                            <button
                                class="page-link"
                                onclick="loadProfitReport(<?= $i ?>)"
                            >
                                <?= $i ?>
                            </button>
                        </li>
                    <?php endfor; ?>

                    <?php if ($page < $totalPages): ?>
                        <li class="page-item">
                            <button
                                class="page-link"
                                onclick="loadProfitReport(<?= $page + 1 ?>)"
                            >
                                Next
                            </button>
                        </li>
                    <?php endif; ?>

                </ul>
            </nav>
        <?php endif; ?>

    </div>
</div>