<?php
include 'includes/auth.php';
include 'db.php';
include 'includes/header.php';

// ===================== FETCH PRODUCTS & STOCK =====================
$products = $conn->query("
    SELECT p.id, p.name, p.sku, p.buying_price, p.selling_price, p.min_level,
           c.name AS category_name,
           COALESCE(SUM(ps.quantity),0) AS total_qty,
           (p.buying_price * COALESCE(SUM(ps.quantity),0)) AS total_value,
           sd.last_dispatched_by,
           sd.last_dispatch_date,
           COALESCE(SUM(sdr.quantity),0) AS total_dispatched
    FROM products p
    LEFT JOIN categories c ON p.category_id=c.id
    LEFT JOIN product_stock ps ON p.id = ps.product_id
    LEFT JOIN (
        SELECT s1.product_id, s1.dispatched_by AS last_dispatched_by, s1.dispatch_date AS last_dispatch_date
        FROM stock_dispatch_requests s1
        INNER JOIN (
            SELECT product_id, MAX(dispatch_date) AS max_date
            FROM stock_dispatch_requests
            WHERE status='Approved'
            GROUP BY product_id
        ) s2 ON s1.product_id = s2.product_id AND s1.dispatch_date = s2.max_date
        WHERE s1.status='Approved'
    ) sd ON p.id = sd.product_id
    LEFT JOIN stock_dispatch_requests sdr ON p.id = sdr.product_id AND sdr.status='Approved'
    GROUP BY p.id
    ORDER BY p.name ASC
");

// ===================== CALCULATE TOTALS =====================
$total_inventory = 0;
$low_stock_count = 0;
$all_products = [];

while($p = $products->fetch_assoc()){
    $total_inventory += $p['total_value'];
    if($p['total_qty'] <= $p['min_level']) $low_stock_count++;
    $all_products[] = $p;
}

// ===================== STOCK & DISPATCH CHART DATA =====================
$categories = [];
$category_totals = [];
$low_stock = 0;
$normal_stock = 0;

foreach($all_products as $prod){
    $cat = $prod['category_name'] ?? 'Uncategorized';
    if(!isset($category_totals[$cat])) $category_totals[$cat] = 0;
    $category_totals[$cat] += $prod['total_qty'];

    if($prod['total_qty'] <= $prod['min_level']) $low_stock++;
    else $normal_stock++;
}

$chart_categories = json_encode(array_keys($category_totals));
$chart_values     = json_encode(array_values($category_totals));

// ===================== FETCH RESTOCK VS DISPATCH =====================
$dates = [];
$restock_data = [];
$dispatch_data = [];

$startDate = date('Y-m-d', strtotime('-30 days'));
$endDate = date('Y-m-d');

for($i = 0; $i <= 30; $i++){
    $day = date('Y-m-d', strtotime("$startDate +$i days"));
    $dates[] = $day;

    $restock_qty = $conn->query("
        SELECT COALESCE(SUM(quantity),0) AS qty 
        FROM product_stock_log 
        WHERE type='Restock' AND DATE(created_at)='$day'
    ")->fetch_assoc()['qty'] ?? 0;

    $dispatch_qty = $conn->query("
        SELECT COALESCE(SUM(quantity),0) AS qty 
        FROM stock_dispatch_requests 
        WHERE status='Approved' AND DATE(dispatch_date)='$day'
    ")->fetch_assoc()['qty'] ?? 0;

    $restock_data[] = intval($restock_qty);
    $dispatch_data[] = intval($dispatch_qty);
}

$line_dates = json_encode($dates);
$line_restock = json_encode($restock_data);
$line_dispatch = json_encode($dispatch_data);
?>

<div class="container mt-4">
    <h2 class="text-center mb-3">📊 Inventory Report</h2>

    <!-- ================= FILTERS ================= -->
    <div class="row mb-3">
        <div class="col-md-3">
            <input type="text" id="searchInput" class="form-control" placeholder="🔍 Search Product Name or SKU">
        </div>
        <div class="col-md-3">
            <select id="categoryFilter" class="form-control">
                <option value="">-- All Categories --</option>
                <?php
                $categories_query = $conn->query("SELECT id,name FROM categories ORDER BY name");
                while($c = $categories_query->fetch_assoc()):
                ?>
                <option value="<?= htmlspecialchars($c['name']) ?>"><?= htmlspecialchars($c['name']) ?></option>
                <?php endwhile; ?>
            </select>
        </div>
        <div class="col-md-3">
            <select id="lowStockFilter" class="form-control">
                <option value="">-- Stock Status --</option>
                <option value="Low">Low Stock</option>
                <option value="Normal">Normal</option>
            </select>
        </div>
    </div>

    <!-- ================= STATS ================= -->
    <div class="row mb-4">
        <div class="col-md-3">
            <div class="alert alert-success text-center">
                <strong>Total Inventory Value:</strong><br>
                MK <?= number_format($total_inventory,2) ?>
            </div>
        </div>
        <div class="col-md-3">
            <div class="alert alert-info text-center">
                <strong>Total Products:</strong><br>
                <?= count($all_products) ?>
            </div>
        </div>
        <div class="col-md-3">
            <div class="alert alert-warning text-center">
                <strong>Low Stock Items:</strong><br>
                <?= $low_stock ?>
            </div>
        </div>
        <div class="col-md-3">
            <div class="alert alert-primary text-center">
                <strong>Normal Stock Items:</strong><br>
                <?= $normal_stock ?>
            </div>
        </div>
    </div>

    <!-- ================= STOCK SUMMARY TABLE ================= -->
    <h4>Stock Summary</h4>
    <div class="table-responsive">
        <style>
#inventoryTable {
    font-size: 0.85rem; /* smaller text */
}
#inventoryTable th, #inventoryTable td {
    padding: 0.3rem 0.5rem; /* less padding */
    vertical-align: middle;
}
#inventoryTable thead th {
    font-size: 0.9rem;
}
.table-responsive {
    overflow-x: auto;
}
</style>
        <table class="table table-bordered table-striped" id="inventoryTable">
            <thead class="bg-dark text-white">
                <tr>
                    <th>SKU</th>
                    <th>Name</th>
                    <th>Category</th>
                    <th>Quantity</th>
                    <th>Min Level</th>
                    <th>Buying Price</th>
                    <th>Selling Price</th>
                    <th>Total Value</th>
                    <th>Low Stock</th>
                    <th>Last Dispatched By</th>
                    <th>Last Dispatch Date</th>
                    <th>Total Dispatched</th>
                    <th>Details</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach($all_products as $prod): ?>
                <tr <?= $prod['total_qty'] <= $prod['min_level'] ? 'class="bg-danger text-white"' : '' ?>
                    data-name="<?= $prod['name'] ?>"
                    data-sku="<?= $prod['sku'] ?>"
                    data-category="<?= $prod['category_name'] ?>"
                    data-lowstock="<?= $prod['total_qty'] <= $prod['min_level'] ? 'Low' : 'Normal' ?>"
                >
                    <td><?= $prod['sku'] ?></td>
                    <td><?= $prod['name'] ?></td>
                    <td><?= $prod['category_name'] ?></td>
                    <td><?= intval($prod['total_qty']) ?></td>
                    <td><?= intval($prod['min_level']) ?></td>
                    <td><?= number_format($prod['buying_price'],2) ?></td>
                    <td><?= number_format($prod['selling_price'],2) ?></td>
                    <td><?= number_format($prod['total_value'],2) ?></td>
                    <td><?= $prod['total_qty'] <= $prod['min_level'] ? 'Yes' : 'No' ?></td>
                    <td><?= htmlspecialchars($prod['last_dispatched_by'] ?? '-') ?></td>
                    <td><?= $prod['last_dispatch_date'] ? date('Y-m-d H:i', strtotime($prod['last_dispatch_date'])) : '-' ?></td>
                    <td><?= intval($prod['total_dispatched']) ?></td>
                    <td>
                        <button class="btn btn-sm btn-info" data-bs-toggle="modal" data-bs-target="#productModal"
                            data-productid="<?= $prod['id'] ?>"
                            data-productname="<?= $prod['name'] ?>"
                            data-productsku="<?= $prod['sku'] ?>"
                        >
                            Details
                        </button>
                    </td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>

    <!-- ================= VISUALS ================= -->
    <h4 class="mt-5">Visual Summaries</h4>
    <div class="row">
        <div class="col-md-4">
            <canvas id="categoryChart" height="250"></canvas>
        </div>
        <div class="col-md-4">
            <canvas id="stockStatusChart" height="250"></canvas>
        </div>
        <div class="col-md-4">
            <canvas id="lineChart" height="250"></canvas>
        </div>
    </div>
</div>

<!-- ================= PRODUCT DETAILS MODAL ================= -->
<div class="modal fade" id="productModal" tabindex="-1" aria-labelledby="productModalLabel" aria-hidden="true">
  <div class="modal-dialog modal-lg modal-dialog-scrollable">
    <div class="modal-content">
      <div class="modal-header bg-primary text-white">
        <h5 class="modal-title" id="productModalLabel">Product Details</h5>
        <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
      </div>
      <div class="modal-body">
        <h6 id="modalProductInfo"></h6>

        <h6 class="mt-3">Restocks</h6>
        <table class="table table-sm table-bordered" id="restockTable">
          <thead>
            <tr>
              <th>Date</th>
              <th>Quantity</th>
            </tr>
          </thead>
          <tbody></tbody>
        </table>

        <h6 class="mt-3">Dispatches</h6>
        <table class="table table-sm table-bordered" id="dispatchTable">
          <thead>
            <tr>
              <th>Date</th>
              <th>Quantity</th>
              <th>Dispatched By</th>
            </tr>
          </thead>
          <tbody></tbody>
        </table>
      </div>
      <div class="modal-footer">
        <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
      </div>
    </div>
  </div>
</div>

<!-- ================= CHART.JS ================= -->
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
// ---------------- CATEGORY BAR CHART ----------------
const ctxCategory = document.getElementById('categoryChart').getContext('2d');
const categoryChart = new Chart(ctxCategory, {
    type: 'bar',
    data: {
        labels: <?= $chart_categories ?>,
        datasets: [{
            label: 'Stock Quantity by Category',
            data: <?= $chart_values ?>,
            backgroundColor: 'rgba(54, 162, 235, 0.7)',
            borderColor: 'rgba(54, 162, 235, 1)',
            borderWidth: 1
        }]
    },
    options: { 
        responsive: true, 
        plugins: { legend: { display: false }, tooltip: { enabled: true } },
        scales: { y: { beginAtZero: true } }
    }
});

// ---------------- STOCK STATUS DOUGHNUT ----------------
const ctxStock = document.getElementById('stockStatusChart').getContext('2d');
const stockStatusChart = new Chart(ctxStock, {
    type: 'doughnut',
    data: {
        labels: ['Low Stock', 'Normal Stock'],
        datasets: [{
            data: [<?= $low_stock ?>, <?= $normal_stock ?>],
            backgroundColor: ['rgba(255, 99, 132, 0.7)', 'rgba(75, 192, 192, 0.7)']
        }]
    },
    options: { responsive: true, plugins: { legend: { position: 'bottom' }, tooltip: { enabled: true } } }
});

// ---------------- LINE CHART: Restocks vs Dispatch ----------------
const ctxLine = document.getElementById('lineChart').getContext('2d');
const lineChart = new Chart(ctxLine, {
    type: 'line',
    data: {
        labels: <?= $line_dates ?>,
        datasets: [
            {
                label: 'Restocks',
                data: <?= $line_restock ?>,
                fill: false,
                borderColor: 'rgba(75, 192, 192, 1)',
                backgroundColor: 'rgba(75, 192, 192,0.2)',
                tension: 0.3
            },
            {
                label: 'Dispatches',
                data: <?= $line_dispatch ?>,
                fill: false,
                borderColor: 'rgba(255, 99, 132, 1)',
                backgroundColor: 'rgba(255, 99, 132,0.2)',
                tension: 0.3
            }
        ]
    },
    options: {
        responsive: true,
        plugins: { legend: { position: 'top' }, tooltip: { enabled: true } },
        scales: { x: { ticks: { maxTicksLimit: 10 } }, y: { beginAtZero: true } }
    }
});

// ---------------- FILTER TABLE ----------------
document.addEventListener('DOMContentLoaded', function(){
    const searchInput = document.getElementById('searchInput');
    const categoryFilter = document.getElementById('categoryFilter');
    const lowStockFilter = document.getElementById('lowStockFilter');
    const table = document.getElementById('inventoryTable').getElementsByTagName('tbody')[0];

    function filterTable(){
        const searchVal = searchInput.value.toLowerCase();
        const categoryVal = categoryFilter.value;
        const lowStockVal = lowStockFilter.value;

        Array.from(table.rows).forEach(row => {
            const name = row.dataset.name.toLowerCase();
            const sku = row.dataset.sku.toLowerCase();
            const category = row.dataset.category;
            const lowstock = row.dataset.lowstock;
            let show = true;

            if(searchVal && !(name.includes(searchVal) || sku.includes(searchVal))){
                show = false;
            }
            if(categoryVal && category !== categoryVal){
                show = false;
            }
            if(lowStockVal && lowstock !== lowStockVal){
                show = false;
            }
            row.style.display = show ? '' : 'none';
        });
    }

    searchInput.addEventListener('input', filterTable);
    categoryFilter.addEventListener('change', filterTable);
    lowStockFilter.addEventListener('change', filterTable);
});

// ---------------- PRODUCT DETAILS MODAL ----------------
const productModal = document.getElementById('productModal');
productModal.addEventListener('show.bs.modal', function(event){
    const button = event.relatedTarget;
    const productId = button.getAttribute('data-productid');
    const productName = button.getAttribute('data-productname');
    const productSKU = button.getAttribute('data-productsku');

    document.getElementById('modalProductInfo').textContent = `${productName} (SKU: ${productSKU})`;

    // Clear previous data
    document.querySelector('#restockTable tbody').innerHTML = '';
    document.querySelector('#dispatchTable tbody').innerHTML = '';

    // Fetch details via AJAX
    fetch('product_details.php?product_id=' + productId)
        .then(res => res.json())
        .then(data => {
            // Restocks
            data.restocks.forEach(r => {
                const row = document.createElement('tr');
                row.innerHTML = `<td>${r.date}</td><td>${r.quantity}</td>`;
                document.querySelector('#restockTable tbody').appendChild(row);
            });
            // Dispatches
            data.dispatches.forEach(d => {
                const row = document.createElement('tr');
                row.innerHTML = `<td>${d.date}</td><td>${d.quantity}</td><td>${d.dispatched_by}</td>`;
                document.querySelector('#dispatchTable tbody').appendChild(row);
            });
        })
        .catch(err => console.error(err));
});
</script>

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