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

include('../../includes/config.php');
include('../../includes/header.php');

$date_from = $_GET['date_from'] ?? date('Y-m-01');
$date_to = $_GET['date_to'] ?? date('Y-m-d');
$store_id = intval($_GET['store_id'] ?? 0);
$show_only_approved = isset($_GET['show_only_approved']) ? (bool)$_GET['show_only_approved'] : false;

// Fetch stores for filter
$stores = [];
$res = $conn->query("SELECT id, store_name FROM stores ORDER BY store_name");
while ($row = $res->fetch_assoc()) $stores[] = $row;

// Base WHERE clause and parameters for date/store
$where = "WHERE s.created_at BETWEEN ? AND ?";
$params = [$date_from . " 00:00:00", $date_to . " 23:59:59"];
$types = "ss";

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

// Always join financial_transactions ft to get approval/reconciliation info (left join to keep all sales)
$join_ft = "LEFT JOIN financial_transactions ft ON ft.linked_sale_id = s.id";

// Additional filter if only showing approved & reconciled sales
$ft_conditions = "";
if ($show_only_approved) {
    $ft_conditions = " AND ft.status = 'approved' AND ft.reconciliation_status = 'reconciled'";
}

// Main query: sales + cogs per sale + approval status
$sql = "
SELECT 
    s.id,
    s.created_at AS transaction_date,
    s.store_id,
    st.store_name,
    u.first_name,
    u.last_name,
    s.receipt_number AS txn_id,
    s.total_amount AS gross,
    (s.total_amount - s.vat_total) AS net,
    s.vat_total AS vat,
    s.discount_total AS discount,
    IFNULL(SUM(si.quantity * p.cost_price), 0) AS cogs,
    (s.total_amount - s.vat_total - IFNULL(SUM(si.quantity * p.cost_price), 0)) AS gross_profit,
    ft.status AS ft_status,
    ft.reconciliation_status AS ft_reconciliation_status
FROM sales_transactions s
JOIN stores st ON s.store_id = st.id
JOIN users u ON s.sold_by = u.id
LEFT JOIN sales_items si ON si.transaction_id = s.id
LEFT JOIN products p ON si.product_id = p.id
$join_ft
$where
$ft_conditions
GROUP BY s.id
ORDER BY s.created_at DESC, s.id DESC
LIMIT 500
";

$stmt = $conn->prepare($sql);
if ($types) {
    $stmt->bind_param($types, ...$params);
}
$stmt->execute();
$result = $stmt->get_result();

$total_gross = 0;
$total_net = 0;
$total_vat = 0;
$total_discount = 0;
$total_cogs = 0;
$total_gross_profit = 0;


$page_title = "COGS & Profit Report";
?>

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1" />
<title><?= htmlspecialchars($page_title) ?></title>
<link rel="icon" type="image/png" href="../../images/logo.png" />
<style>
body {
    margin: 0;
    background: url('../../images/profile.jpg') no-repeat center center fixed;
    background-size: cover;
    font-family: 'Segoe UI', sans-serif;
    color: #eee;
    -webkit-font-smoothing: antialiased;
    -moz-osx-font-smoothing: grayscale;
    overflow-x: hidden;
}

.page-header {
    max-width: 1850px;
    margin: 2rem auto 0;
    padding: 45 40px;
    display: flex;
    justify-content: space-between;
    align-items: center;
}

h1.page-title {
    font-weight: 700;
    font-size: 2rem;
    color: #ffa500;
    letter-spacing: 1.1px;
    margin: 0;
    user-select: none;
    display: flex;
    align-items: center;
    gap: 12px;
}

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: 8px 16px;
    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;
}

.content {
    max-width: 1850px;
    margin: 1rem auto 3rem;
    padding: 40px 40px;
    background: rgba(255, 255, 255, 0.08);
    border-radius: 15px;
    box-shadow: 0 4px 30px rgba(0,0,0,0.1);
    border: 1px solid rgba(255,255,255,0.2);
    backdrop-filter: blur(8px);
    -webkit-backdrop-filter: blur(8px);
}

h2 {
    color: #ffa500;
    font-weight: 700;
    font-size: 2rem;
    margin-bottom: 30px;
    text-align: center;
    user-select: none;
}

.filters {
    display: flex;
    flex-wrap: wrap;
    justify-content: center;
    gap: 15px;
    margin-bottom: 30px;
}

.filters select,
.filters input[type="date"] {
    min-width: 180px;
    padding: 12px 14px;
    border-radius: 8px;
    border: none;
    background: rgba(255, 255, 255, 0.12);
    color: #eee;
    font-size: 1rem;
    transition: background-color 0.3s ease;
}

.filters select:focus,
.filters input[type="date"]:focus {
    background: rgba(255, 165, 0, 0.15);
    color: #222;
    outline: none;
}

.filters label {
    color: #ffa500;
    font-weight: 600;
    align-self: center;
    user-select: none;
}

.filters button {
    background: rgba(255, 165, 0, 0.9);
    border: none;
    border-radius: 8px;
    padding: 12px 28px;
    font-weight: 700;
    font-size: 1rem;
    color: #222;
    cursor: pointer;
    transition: background-color 0.3s ease;
}

.filters button:hover {
    background: rgba(255, 165, 0, 1);
}

.actions {
    margin-bottom: 20px;
    display: flex;
    justify-content: center;
    gap: 15px;
}

button.export-btn {
    background: rgba(255, 165, 0, 0.9);
    border: none;
    border-radius: 8px;
    padding: 10px 22px;
    font-weight: 700;
    font-size: 1rem;
    color: #222;
    cursor: pointer;
    transition: background-color 0.3s ease;
}

button.export-btn:hover {
    background: rgba(255, 165, 0, 1);
}

table {
    width: 100%;
    border-collapse: collapse;
    font-size: 14px;
    color: #eee;
}

table th, table td {
    padding: 12px 15px;
    border-bottom: 1px solid rgba(255, 255, 255, 0.2);
    vertical-align: middle;
}

table th {
    background: rgba(255, 165, 0, 0.3);
    color: #fff;
    font-weight: 700;
    text-align: left;
}

table td.amount,
table th.amount {
    text-align: right;
    font-feature-settings: "tnum";
    font-variant-numeric: tabular-nums;
}

.status-approved {
    color: #4caf50;
    font-weight: 700;
    display: flex;
    align-items: center;
    gap: 5px;
    user-select: none;
}

.status-warning {
    color: #e53935;
    font-weight: 700;
    display: flex;
    align-items: center;
    gap: 5px;
    user-select: none;
}

.status-icon {
    font-weight: 900;
    font-size: 1.1rem;
}

.summary-box {
    margin-top: 20px;
    padding: 20px;
    background: rgba(255,255,255,0.1);
    border-radius: 12px;
    font-weight: 700;
    font-size: 1.2rem;
    text-align: center;
    color: #ffa500;
    user-select: none;
}

/* Responsive */
@media (max-width: 900px) {
    .page-header,
    .content {
        margin: 1.5rem 1rem 2rem;
        padding: 25px 20px;
    }
    h1.page-title {
        font-size: 1.6rem;
    }
    table th, table td {
        padding: 10px 12px;
        font-size: 13px;
    }
    .filters select,
    .filters input[type="date"] {
        min-width: 140px;
        padding: 10px 12px;
        font-size: 0.9rem;
    }
    .filters button {
        padding: 10px 20px;
        font-size: 0.9rem;
    }
}
</style>
</head>
<body>

<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>


<div class="content">

    <form method="get" class="filters">
        <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 $store): ?>
                <option value="<?= $store['id'] ?>" <?= ($store_id === intval($store['id'])) ? 'selected' : '' ?>>
                    <?= htmlspecialchars($store['store_name']) ?>
                </option>
            <?php endforeach; ?>
        </select>
        <label>
            <input type="checkbox" name="show_only_approved" value="1" <?= $show_only_approved ? 'checked' : '' ?>>
            Show only Approved & Reconciled Sales
        </label>
        <button type="submit">Apply Filters</button>
    </form>

    <div class="actions">
        <button class="export-btn" onclick="exportTableToExcel('cogsTable', 'COGS_Report')">⬇ Export to Excel</button>
        <button class="export-btn" onclick="generatePDF()">📄 Generate PDF</button>
    </div>

    <?php if ($result->num_rows === 0): ?>
        <div class="summary-box">No sales found for the selected criteria.</div>
    <?php else: ?>
        <table id="cogsTable" border="0" cellspacing="0" cellpadding="0" role="table" aria-label="COGS & Profit Report Table">
            <thead>
                <tr>
                    <th>Date</th>
                    <th>Txn ID</th>
                    <th>Store</th>
                    <th>Cashier</th>
                    <th>Status</th>
                    <th class="amount">Gross (MWK)</th>
                    <th class="amount">Net (MWK)</th>
                    <th class="amount">VAT (MWK)</th>
                    <th class="amount">Discount (MWK)</th>
                    <th class="amount">COGS (MWK)</th>
                    <th class="amount">Gross Profit (MWK)</th>
                    <th class="amount">Profit Margin (%)</th>
                </tr>
            </thead>
            <tbody>
                <?php 
                $total_gross = $total_net = $total_vat = $total_discount = $total_cogs = $total_gross_profit = 0;
                while ($row = $result->fetch_assoc()):
                    $total_gross += $row['gross'];
                    $total_net += $row['net'];
                    $total_vat += $row['vat'];
                    $total_discount += $row['discount'];
                    $total_cogs += $row['cogs'];
                    $total_gross_profit += $row['gross_profit'];
                    $margin = $row['gross'] != 0 ? ($row['gross_profit'] / $row['gross']) * 100 : 0;

                    $is_approved_and_reconciled = ($row['ft_status'] === 'approved' && $row['ft_reconciliation_status'] === 'reconciled');
                ?>
                <tr>
                    <td><?= htmlspecialchars(date('Y-m-d', strtotime($row['transaction_date']))) ?></td>
                    <td><?= htmlspecialchars($row['txn_id']) ?></td>
                    <td><?= htmlspecialchars($row['store_name']) ?></td>
                    <td><?= htmlspecialchars(trim($row['first_name'] . ' ' . $row['last_name'])) ?></td>
                    <td>
                        <?php if ($is_approved_and_reconciled): ?>
                            <span class="status-approved">&#10004; Approved & Reconciled</span>
                        <?php else: ?>
                            <span class="status-warning">&#9888; NOT Approved/Reconciled</span>
                        <?php endif; ?>
                    </td>
                    <td class="amount"><?= number_format($row['gross'], 2) ?></td>
                    <td class="amount"><?= number_format($row['net'], 2) ?></td>
                    <td class="amount"><?= number_format($row['vat'], 2) ?></td>
                    <td class="amount"><?= number_format($row['discount'], 2) ?></td>
                    <td class="amount"><?= number_format($row['cogs'], 2) ?></td>
                    <td class="amount"><?= number_format($row['gross_profit'], 2) ?></td>
                    <td class="amount"><?= number_format($margin, 2) ?>%</td>
                </tr>
                <?php endwhile; ?>
                <tr style="font-weight:bold; background: rgba(255, 165, 0, 0.3);">
                    <td colspan="5" style="text-align:right;">Totals:</td>
                    <td class="amount"><?= number_format($total_gross, 2) ?></td>
                    <td class="amount"><?= number_format($total_net, 2) ?></td>
                    <td class="amount"><?= number_format($total_vat, 2) ?></td>
                    <td class="amount"><?= number_format($total_discount, 2) ?></td>
                    <td class="amount"><?= number_format($total_cogs, 2) ?></td>
                    <td class="amount"><?= number_format($total_gross_profit, 2) ?></td>
                    <td class="amount"><?= $total_gross ? number_format(($total_gross_profit / $total_gross) * 100, 2) : '0.00' ?>%</td>
                </tr>
            </tbody>
        </table>
        <div class="summary-box" aria-live="polite">
            <p><strong>Overall Profit Margin:</strong> <?= $total_gross ? number_format(($total_gross_profit / $total_gross) * 100, 2) : '0.00' ?>%</p>
        </div>
    <?php endif; ?>
</div>

<script src="https://cdnjs.cloudflare.com/ajax/libs/jspdf/2.5.1/jspdf.umd.min.js"></script>
<script>
function exportTableToExcel(tableID, filename = '') {
    const downloadLink = document.createElement("a");
    const dataType = 'application/vnd.ms-excel';
    const tableHTML = encodeURIComponent(document.getElementById(tableID).outerHTML);
    filename = filename ? filename + '.xls' : 'export_data.xls';
    downloadLink.href = 'data:' + dataType + ', ' + tableHTML;
    downloadLink.download = filename;
    downloadLink.click();
}

async function generatePDF() {
    const { jsPDF } = window.jspdf;
    const doc = new jsPDF('l', 'pt', 'a4');
    doc.setFontSize(18);
    doc.setTextColor('#ffa500');
    doc.text("COGS & Profit Report", 40, 40);

    const dateFrom = "<?= htmlspecialchars($date_from) ?>";
    const dateTo = "<?= htmlspecialchars($date_to) ?>";
    doc.setFontSize(11);
    doc.setTextColor('#fff');
    doc.text(`Date Range: ${dateFrom} to ${dateTo}`, 40, 60);

    alert('PDF generation with full table requires jsPDF AutoTable plugin.\nExport to Excel is recommended.');
}
</script>

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

</body>
</html>
