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

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

// Fetch stores
$stores = $conn->query("SELECT id, store_name FROM stores");

// Build inventory valuation query
$where = "inv.deleted_at IS NULL";
$params = [];
$types = "";

if ($store_id && is_numeric($store_id)) {
    $where .= " AND inv.store_id = ?";
    $params[] = $store_id;
    $types .= "i";
}

$sql = "
    SELECT 
        inv.product_id,
        p.product_name,
        p.unit,
        p.cost_price,
        p.selling_price,
        p.vat_rate,
        SUM(inv.quantity) AS total_qty,
        SUM(inv.cost_price * inv.quantity) AS total_cost,
        SUM(p.selling_price * inv.quantity) AS expected_sales
    FROM inventory inv
    JOIN products p ON inv.product_id = p.id
    WHERE $where
    GROUP BY inv.product_id
    ORDER BY total_qty DESC
";

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

$total_qty = $total_cost = $total_sales = $total_profit = 0;
foreach ($results as &$row) {
    $row['expected_profit'] = $row['expected_sales'] - $row['total_cost'];
    $total_qty += $row['total_qty'];
    $total_cost += $row['total_cost'];
    $total_sales += $row['expected_sales'];
    $total_profit += $row['expected_profit'];
}

$page_title = "Inventory Valuation 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>
    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;
    }

    form.filter-form {
        margin: 20px 0 30px 0;
        display: flex;
        flex-wrap: wrap;
        gap: 20px 30px;
        align-items: flex-end;
    }
    form.filter-form label {
        color: #eee;
        font-weight: 600;
        font-size: 0.9rem;
        display: flex;
        flex-direction: column;
        font-family: 'Segoe UI', sans-serif;
        white-space: nowrap;
    }
    form.filter-form input[type="date"],
    form.filter-form select {
        margin-top: 6px;
        padding: 8px 12px;
        border-radius: 8px;
        border: none;
        background: rgba(255,255,255,0.1);
        color: #eee;
        min-width: 150px;
        font-weight: 500;
        font-size: 1rem;
    }
    form.filter-form button {
        background: #ffa500;
        color: #222;
        font-weight: 700;
        padding: 10px 18px;
        border-radius: 8px;
        border: none;
        cursor: pointer;
        transition: background-color 0.3s ease;
        white-space: nowrap;
        font-size: 1rem;
    }
    form.filter-form button:hover {
        background-color: #cc8400;
        color: #fff;
    }
    table {
        width: 100%;
        border-collapse: collapse;
        font-size: 15px;
        background: rgba(255,255,255,0.1);
        border-radius: 15px;
        overflow: hidden;
    }
    thead tr {
        background: rgba(255,165,0,0.3);
        color: #fff;
    }
    th, td {
        padding: 12px 14px;
        border-bottom: 1px solid rgba(255,255,255,0.2);
    }
    th {
        text-align: right;
    }
    td:first-child, th:first-child {
        text-align: left;
    }
    .total-row {
        font-weight: bold;
        background: rgba(255,165,0,0.15);
    }
    a {
        color: #ffa500;
        text-decoration: none;
    }
    a:hover {
        text-decoration: underline;
    }
    canvas {
        margin-top: 40px;
        border-radius: 15px;
        background: rgba(255,255,255,0.1);
        padding: 15px;
        max-width: 700px;
        width: 100%;
        height: auto !important;
    }
    .charts-wrapper {
        display: flex;
        gap: 40px;
        flex-wrap: wrap;
        justify-content: center;
        margin-top: 40px;
    }
    </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 method="get" class="filter-form" novalidate>
     From:
            <input id="from_date" type="date" name="from_date" value="<?= htmlspecialchars($date_from) ?>">
        
        To:
            <input id="to_date" type="date" name="to_date" value="<?= htmlspecialchars($date_to) ?>">
        
  Store:
            <select id="store_id" name="store_id">
                <option value="">All Stores</option>
                <?php foreach ($stores as $store): ?>
                    <option value="<?= htmlspecialchars($store['id']) ?>" <?= $store_id == $store['id'] ? 'selected' : '' ?>>
                        <?= htmlspecialchars($store['store_name']) ?>
                    </option>
                <?php endforeach; ?>
            </select>
    

        <button type="submit">🔎 Filter</button>
        <button type="button" onclick="window.print()" class="print-btn">🖨 Print</button>
        <button type="button" onclick="exportExcel()" class="excel-btn">📊 Excel</button>
        <button type="button" onclick="exportPDF()" class="pdf-btn">📄 PDF</button>
    </form>

    <table>
        <thead>
            <tr>
                <th>Product</th>
                <th>Qty</th>
                <th>Unit</th>
                <th>Cost Price</th>
                <th>Total Cost</th>
                <th>Selling Price</th>
                <th>Expected Sales</th>
                <th>Expected Profit</th>
            </tr>
        </thead>
        <tbody>
        <?php foreach ($results as $row): ?>
            <tr>
                <td>
                    <a href="product_sales_history.php?id=<?= htmlspecialchars($row['product_id']) ?>">
                        <?= htmlspecialchars($row['product_name']) ?>
                    </a>
                </td>
                <td><?= number_format($row['total_qty'], 2) ?></td>
                <td><?= htmlspecialchars($row['unit']) ?></td>
                <td><?= number_format($row['cost_price'], 2) ?></td>
                <td><?= number_format($row['total_cost'], 2) ?></td>
                <td><?= number_format($row['selling_price'], 2) ?></td>
                <td><?= number_format($row['expected_sales'], 2) ?></td>
                <td><?= number_format($row['expected_profit'], 2) ?></td>
            </tr>
        <?php endforeach; ?>
            <tr class="total-row">
                <td>Total</td>
                <td><?= number_format($total_qty, 2) ?></td>
                <td></td>
                <td></td>
                <td><?= number_format($total_cost, 2) ?></td>
                <td></td>
                <td><?= number_format($total_sales, 2) ?></td>
                <td><?= number_format($total_profit, 2) ?></td>
            </tr>
        </tbody>
    </table>

    <div class="charts-wrapper">
        <canvas id="barChart" height="300"></canvas>
        <canvas id="pieChart" height="300"></canvas>
    </div>

</div>

<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
const data = <?= json_encode($results) ?>;

function exportExcel() {
    const table = document.querySelector("table").outerHTML;
    const blob = new Blob([table], {type: 'application/vnd.ms-excel'});
    const url = URL.createObjectURL(blob);
    const a = document.createElement("a");
    a.href = url;
    a.download = "inventory_valuation_<?= date('Y-m-d') ?>.xls";
    a.click();
    URL.revokeObjectURL(url);
}

function exportPDF() {
    window.print();
}

const labels = data.map(r => r.product_name);
const values = data.map(r => parseFloat(r.total_cost));

new Chart(document.getElementById('barChart'), {
    type: 'bar',
    data: {
        labels,
        datasets: [{
            label: 'Total Cost (MWK)',
            data: values,
            backgroundColor: '#ffa50088',
            borderColor: '#ffa500',
            borderWidth: 1
        }]
    },
    options: {
        responsive: true,
        maintainAspectRatio: false,
        plugins: {
            legend: { display: false },
            title: { display: true, text: 'Inventory Value by Product' }
        },
        scales: {
            y: {
                beginAtZero: true
            }
        }
    }
});

new Chart(document.getElementById('pieChart'), {
    type: 'pie',
    data: {
        labels,
        datasets: [{
            data: values,
            backgroundColor: labels.map((_, i) => `hsl(${i * 30 % 360}, 70%, 65%)`)
        }]
    },
    options: {
        responsive: true,
        maintainAspectRatio: false,
        plugins: {
            title: { display: true, text: 'Inventory Distribution (Pie)' }
        }
    }
});
</script>

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