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

$username = $_SESSION['username'] ?? 'Guest';
$allowed_roles = ['admin','stores','finance'];
$user_role = $_SESSION['role'] ?? 'guest';

function canManageMenu(){
    global $user_role, $allowed_roles;
    return in_array($user_role, $allowed_roles);
}

// ===================== FETCH PRODUCTS =====================
$products = $conn->query("
    SELECT p.*, c.name AS category_name,
           COALESCE(SUM(ps.quantity),0) AS total_qty,
           (p.selling_price * COALESCE(SUM(ps.quantity),0)) AS total_value,
           sd.last_dispatched_by,
           sd.last_dispatch_date,
           d.name AS department_name
    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 departments d ON ps.department_id = d.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
    GROUP BY p.id, d.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;
}

// ===================== DISPATCH TOTALS =====================
$dispatch_stats = $conn->query("
    SELECT 
        COUNT(*) AS total_dispatches,
        COALESCE(SUM(quantity),0) AS total_dispatched
    FROM stock_dispatch_requests
")->fetch_assoc();

$pending_dispatches = $conn->query("
    SELECT COUNT(*) AS pending_requests
    FROM stock_dispatch_requests
    WHERE status='Pending'
")->fetch_assoc();

// ===================== HANDLE RESTOCK =====================
if(isset($_POST['restock_product'])){
    $product_id    = intval($_POST['product_id']);
    $qty           = intval($_POST['restock_qty']);
   // FORCE MAIN STORE
$department_id = 5;

    if($qty <= 0){
        echo "<div class='alert alert-danger'>Invalid restock quantity.</div>";
    } else {
        // Check if row exists
        $checkStmt = $conn->prepare("
            SELECT id, quantity FROM product_stock 
            WHERE product_id=? AND department_id=?
            LIMIT 1
        ");
        $checkStmt->bind_param("ii", $product_id, $department_id);
        $checkStmt->execute();
        $exists = $checkStmt->get_result()->fetch_assoc();

        if($exists){
            $updateStmt = $conn->prepare("
                UPDATE product_stock 
                SET quantity = quantity + ? 
                WHERE id = ?
            ");
            $updateStmt->bind_param("ii", $qty, $exists['id']);
            $updateStmt->execute();
        } else {
           $insertStmt = $conn->prepare("
    INSERT INTO product_stock (product_id, department_id, quantity)
    VALUES (?,?,?)
    ON DUPLICATE KEY UPDATE quantity = quantity + VALUES(quantity)
");
$insertStmt->bind_param("iii", $product_id, $department_id, $qty);
$insertStmt->execute();
        }

        // Update product total quantity
        $sumStmt = $conn->prepare("
SELECT COALESCE(SUM(quantity),0) AS total_qty
FROM product_stock
WHERE product_id = ?
AND department_id = ?
");
$sumStmt->bind_param("ii", $product_id, $department_id);
$sumStmt->execute();
$total_qty = $sumStmt->get_result()->fetch_assoc()['total_qty'];

        $updateProd = $conn->prepare("
            UPDATE products SET quantity=? WHERE id=?
        ");
        $updateProd->bind_param("ii", $total_qty, $product_id);
        $updateProd->execute();

        // Send email
        $prod_info = $conn->query("SELECT name, sku FROM products WHERE id=$product_id")->fetch_assoc();
        $dept_info = $conn->query("SELECT name FROM departments WHERE id=$department_id")->fetch_assoc();

        $to = "giantplusmw@gmail.com,rams.investments@gmail.com";
        $subject = "Restock Alert: {$prod_info['name']} (SKU: {$prod_info['sku']})";
        $message = "
            <h3>Product Restocked</h3>
            <p><strong>Product:</strong> {$prod_info['name']} (SKU: {$prod_info['sku']})</p>
            <p><strong>Quantity Restocked:</strong> $qty</p>
            <p><strong>Department:</strong> {$dept_info['name']}</p>
            <p><strong>Updated Total Quantity:</strong> $total_qty</p>
            <p><strong>Restocked By:</strong> {$_SESSION['username']}</p>
            <p><strong>Time:</strong> ".date('Y-m-d H:i:s')."</p>
        ";
        $headers  = "MIME-Version: 1.0\r\n";
        $headers .= "Content-type:text/html;charset=UTF-8\r\n";
        $headers .= "From: cloud@gianterp.space\r\n";
        mail($to, $subject, $message, $headers);

        echo "<script>alert('Product restocked successfully and email sent.');window.location='inventory_data.php';</script>";
        exit;
    }
}

// ===================== HANDLE DISPATCH (AUTO-APPROVED) =====================
// ===================== HANDLE DISPATCH (AUTO-APPROVED) =====================
if(isset($_POST['dispatch_product'])){
    $product_id    = intval($_POST['product_id']);
    $qty           = intval($_POST['dispatch_qty']);
    $to_department = intval($_POST['department_id']);

    $main_store_id = 5;

    if($qty <= 0){
        echo "<div class='alert alert-danger'>Invalid dispatch quantity.</div>";
    } else {

        // CHECK STOCK ONLY FROM MAIN STORE
        $stock_check = $conn->prepare("
            SELECT COALESCE(SUM(quantity),0) AS total_qty
            FROM product_stock
            WHERE product_id = ?
            AND department_id = ?
        ");
        $stock_check->bind_param("ii", $product_id, $main_store_id);
        $stock_check->execute();
        $available = $stock_check->get_result()->fetch_assoc()['total_qty'];

        if($available < $qty){
            echo "<div class='alert alert-danger'>Insufficient stock. Available: $available</div>";
        } else {

            $conn->begin_transaction();

            try {

                $now = date('Y-m-d H:i:s');

                // ================= INSERT DISPATCH =================
                $status = 'Approved';
                $dispatched_by = $username;
                $dispatch_date = $now;

                $stmt = $conn->prepare("
                    INSERT INTO stock_dispatch_requests
                    (product_id, department_id, quantity, requested_by, status, dispatched_by, dispatch_date)
                    VALUES (?,?,?,?,?,?,?)
                ");

                $stmt->bind_param(
                    "iiissss",
                    $product_id,
                    $to_department,
                    $qty,
                    $username,
                    $status,
                    $dispatched_by,
                    $dispatch_date
                );
                $stmt->execute();

                // ================= DEDUCT FROM MAIN STORE ONLY =================
                $remaining = $qty;

                while($remaining > 0){

                    $stock_row = $conn->query("
                        SELECT id, quantity
                        FROM product_stock
                        WHERE product_id = $product_id
                        AND department_id = $main_store_id
                        AND quantity > 0
                        ORDER BY id ASC
                        LIMIT 1
                    ")->fetch_assoc();

                    if(!$stock_row) break;

                    $deduct = min($remaining, $stock_row['quantity']);

                    $upd = $conn->prepare("
                        UPDATE product_stock
                        SET quantity = quantity - ?
                        WHERE id = ?
                    ");
                    $upd->bind_param("ii", $deduct, $stock_row['id']);
                    $upd->execute();

                    $remaining -= $deduct;
                }

                // ================= UPDATE PRODUCT TOTAL =================
                $sumStmt = $conn->prepare("
                    SELECT COALESCE(SUM(quantity),0) AS total_qty
                    FROM product_stock
                    WHERE product_id=?
                ");
                $sumStmt->bind_param("i", $product_id);
                $sumStmt->execute();
                $new_total = $sumStmt->get_result()->fetch_assoc()['total_qty'];

                $updateProd = $conn->prepare("
                    UPDATE products SET quantity=? WHERE id=?
                ");
                $updateProd->bind_param("ii", $new_total, $product_id);
                $updateProd->execute();

                $conn->commit();

                // ================= EMAIL ALERT (RESTORED) =================
                $dispatch_emails = ["giantplusmw@gmail.com","rams.investments@gmail.com"];

                $prod_info = $conn->query("
                    SELECT name, sku 
                    FROM products 
                    WHERE id=$product_id
                ")->fetch_assoc();

                $subject = "Stock Dispatch Alert: {$prod_info['name']} (SKU: {$prod_info['sku']})";

                $message = "
                    <h3>Product Dispatched</h3>
                    <p><strong>Product:</strong> {$prod_info['name']} (SKU: {$prod_info['sku']})</p>
                    <p><strong>Quantity Dispatched:</strong> $qty</p>
                    <p><strong>Remaining Stock:</strong> $new_total</p>
                    <p><strong>Dispatched From:</strong> Main Store</p>
                    <p><strong>Dispatched To Department:</strong> $to_department</p>
                    <p><strong>Dispatched By:</strong> {$username}</p>
                    <p><strong>Dispatch Date:</strong> $now</p>
                ";

                $headers  = "MIME-Version: 1.0\r\n";
                $headers .= "Content-type:text/html;charset=UTF-8\r\n";
                $headers .= "From: cloud@gianterp.space\r\n";

                foreach($dispatch_emails as $to){
                    mail($to, $subject, $message, $headers);
                }

                // ================= SUCCESS ALERT (KEPT) =================
               echo "<div class='alert alert-success'>Stock dispatched successfully.</div>";

echo "<script>
setTimeout(function(){
    window.location.href = window.location.href;
}, 1200);
</script>";

            } catch(Exception $e){
                $conn->rollback();
                echo "<div class='alert alert-danger'>Dispatch failed: ".$e->getMessage()."</div>";
            }
        }
    }
}

// ===================== TRANSFER =====================
// ===================== HANDLE TRANSFER =====================
if(isset($_POST['transfer_product'])){
    $product_id       = intval($_POST['product_id']);
    $from_department  = intval($_POST['from_department_id']);
    $to_department    = intval($_POST['to_department_id']);
    $qty              = intval($_POST['transfer_qty']);

    if($qty <= 0){
        echo "<div class='alert alert-danger'>Invalid transfer quantity.</div>";
    } elseif($from_department == $to_department){
        echo "<div class='alert alert-danger'>Source and destination departments cannot be the same.</div>";
    } else {
        // Check available stock in source
        $stock_check = $conn->prepare("
            SELECT COALESCE(SUM(quantity),0) AS total_qty
            FROM product_stock
            WHERE product_id=? AND department_id=?
        ");
        $stock_check->bind_param("ii", $product_id, $from_department);
        $stock_check->execute();
        $available = $stock_check->get_result()->fetch_assoc()['total_qty'];

        if($available < $qty){
            echo "<div class='alert alert-danger'>Insufficient stock in source department. Available: $available</div>";
        } else {
            $conn->begin_transaction();
            try {
                // Deduct from source department
                $remaining = $qty;
                while($remaining > 0){
                    $stock_row = $conn->query("
                        SELECT id, quantity
                        FROM product_stock
                        WHERE product_id = $product_id
                          AND department_id = $from_department
                          AND quantity > 0
                        ORDER BY id ASC
                        LIMIT 1
                    ")->fetch_assoc();

                    if(!$stock_row) break;
                    $deduct = min($remaining, $stock_row['quantity']);
                    $upd = $conn->prepare("
                        UPDATE product_stock
                        SET quantity = quantity - ?
                        WHERE id = ?
                    ");
                    $upd->bind_param("ii", $deduct, $stock_row['id']);
                    $upd->execute();
                    $remaining -= $deduct;
                }

                // Add to destination
                $checkStmt = $conn->prepare("
                    SELECT id FROM product_stock 
                    WHERE product_id=? AND department_id=? LIMIT 1
                ");
                $checkStmt->bind_param("ii", $product_id, $to_department);
                $checkStmt->execute();
                $exists = $checkStmt->get_result()->fetch_assoc();

                if($exists){
                    $upd = $conn->prepare("
                        UPDATE product_stock SET quantity = quantity + ? WHERE id=?
                    ");
                    $upd->bind_param("ii", $qty, $exists['id']);
                    $upd->execute();
                } else {
                    $ins = $conn->prepare("
                        INSERT INTO product_stock (product_id, department_id, quantity) VALUES (?,?,?)
                    ");
                    $ins->bind_param("iii", $product_id, $to_department, $qty);
                    $ins->execute();
                }

                $conn->commit();
                echo "<div class='alert alert-success'>Product transferred successfully.</div>";

            } catch(Exception $e){
                $conn->rollback();
                echo "<div class='alert alert-danger'>Transfer failed: ".$e->getMessage()."</div>";
            }
        }
    }
}
// ===================== FETCH DEPARTMENTS =====================
$departments = $conn->query("SELECT id,name FROM departments ORDER BY name");
?>

<div class="container mt-4">

    <div class="welcome-msg">
        <i class="fa fa-user-circle"></i>
        Welcome, <strong><?= htmlspecialchars($username) ?></strong>!
    </div>

    <div class="mb-3">
        <button class="btn btn-secondary" onclick="history.back()">
            <i class="fa fa-arrow-left"></i> Back
        </button>
        <div class="mb-3">
    <a href="export_inv_excel.php?search=" id="exportExcel" class="btn btn-success">
        <i class="fa fa-file-excel"></i> Export to Excel
    </a>
    <a href="export_inv_pdf.php?search=" id="exportPDF" class="btn btn-danger">
        <i class="fa fa-file-pdf"></i> Export to PDF
    </a>
</div>
        <button class="btn btn-secondary" onclick="window.location.href='products.php'">
            <i class="fa fa-plus"></i> Add Product
        </button>

         <button class="btn btn-secondary" onclick="window.location.href='perishable_products.php'">
            <i class="fa fa-plus"></i>Perishables
        </button>
        
         <button class="btn btn-danger" onclick="window.location.href='damaged_goods.php'">
                <i class="fa fa-exclamation-triangle"></i>Damaged Goods
        </button>
    </div>

    <h3 class="text-center">📦 Inventory & Dispatch Dashboard</h3>
    <hr>

    <!-- ================= 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 Dispatched:</strong><br>
                <?= intval($dispatch_stats['total_dispatched']) ?> items
            </div>
        </div>
        <div class="col-md-3">
            <div class="alert alert-warning text-center">
                <strong>Pending Dispatch Requests:</strong><br>
                <?= intval($pending_dispatches['pending_requests']) ?>
            </div>
        </div>
        <div class="col-md-3">
            <div class="alert alert-danger text-center">
                <strong>Low Stock Items:</strong><br>
                <?= intval($low_stock_count) ?>
            </div>
        </div>
    </div>

    <!-- ================= 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 = $conn->query("SELECT id,name FROM categories ORDER BY name");
                while($c = $categories->fetch_assoc()):
                ?>
                <option value="<?= $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>
<?php
// Identify duplicate SKUs or names safely
$sku_count = [];
$name_count = [];
$duplicates = [];

foreach($all_products as $prod){
    $sku = strtolower($prod['sku'] ?? '');   // <-- use ?? '' to avoid null
    $name = strtolower($prod['name'] ?? ''); // <-- use ?? '' to avoid null

    $sku_count[$sku] = ($sku_count[$sku] ?? 0) + 1;
    $name_count[$name] = ($name_count[$name] ?? 0) + 1;
}

// Mark duplicates
foreach($all_products as &$prod){
    $sku = strtolower($prod['sku'] ?? '');
    $name = strtolower($prod['name'] ?? '');
    $prod['is_duplicate'] = ($sku_count[$sku] > 1 || $name_count[$name] > 1);
}
unset($prod);

?>
    <!-- ================= PRODUCTS TABLE ================= -->
    <table class="table table-bordered table-striped" id="inventoryTable" style="font-size:14px;">
        <thead class="bg-dark text-white">
            <tr>
                <th>SKU</th>
                <th>Name</th>
                <th>Category</th>
                <th>Qty</th>
                <th>Min</th>
                <th>CP</th>
                <th>SP</th>
                <th>P</th>
                <th>Total</th>
                  <th>Dept</th>
                <th>Low Stock</th>
                <th>Dispatched By</th>
                <th>Date</th>
               
                <th>Actions</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach($all_products as $prod): 
                $low_class = $prod['total_qty'] <= $prod['min_level'] ? 'bg-danger text-white fw-bold' : '';
            ?>
<tr 
    data-name="<?= strtolower($prod['name'] ?? '') ?>"
    data-sku="<?= strtolower($prod['sku'] ?? '') ?>"
    data-category="<?= $prod['category_name'] ?? '' ?>"
      data-department-id="<?= $prod['department_id'] ?? 0 ?>"
    data-lowstock="<?= $prod['total_qty'] <= $prod['min_level'] ? 'Low' : 'Normal' ?>"
    class="<?= $prod['is_duplicate'] ? 'table-warning fw-bold' : '' ?>"
>
                <td><?= $prod['sku'] ?></td>
                <td><?= $prod['name'] ?></td>
                <td><?= $prod['category_name'] ?></td>
              
                <td class="<?= $low_class ?>"><?= 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['selling_price'] - $prod['buying_price']) ?></td>
                <td><?= number_format($prod['total_value'],2) ?></td>
                  <td><?= htmlspecialchars($prod['department_name'] ?? '-') ?></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'])) : '-' ?>
                    
<br/>

<!-- ===== TRANSFER ===== -->
<form method="POST" style="display:inline-block;">
    <input type="hidden" name="product_id" value="<?= $prod['id'] ?>">

  <!-- Display From Dept as read-only text -->
<?php
$first_stock_dept = $conn->query("
    SELECT department_id 
    FROM product_stock 
    WHERE product_id={$prod['id']} AND quantity>0 
    ORDER BY id ASC LIMIT 1
")->fetch_assoc();
$from_dept_id = $first_stock_dept['department_id'] ?? 0;
?>
<input type="text" 
       value="<?= htmlspecialchars($prod['department_name'] ?? '-') ?>" 
       style="width:110px;" 
       readonly>
<input type="hidden" name="from_department_id" value="<?= $from_dept_id ?>">

    <select name="to_department_id" required style="width:110px;">
        <option value="">To Dept</option>
        <?php
        $departments->data_seek(0);
        while($d = $departments->fetch_assoc()):
        ?>
            <option value="<?= $d['id'] ?>"><?= htmlspecialchars($d['name']) ?></option>
        <?php endwhile; ?>
    </select>

    <input type="number" name="transfer_qty" placeholder="Qty" style="width:65px;" required>
    <button type="submit" name="transfer_product" class="btn btn-sm btn-warning">
        <i class="fa fa-exchange-alt"></i> Transfer
    </button>
</form>








                </td>
                 
                <td>
                    <?php if(canManageMenu()): ?>
                    <!-- ===== RESTOCK ===== -->
                    <!-- ===== RESTOCK ===== -->
<form method="POST" style="display:inline-block;">
    
    <input type="hidden" name="product_id" value="<?= $prod['id'] ?>">

    <!-- MAIN STORE ONLY -->
    <input type="hidden" name="department_id" value="5">

    <input type="hidden"
           value="Main Store"
           readonly
           style="width:110px;background:#f5f5f5;">

    <input type="number"
           name="restock_qty"
           placeholder="Qty"
           style="width:65px;"
           required>

    <button type="submit"
            name="restock_product"
            class="btn btn-sm btn-success">
        <i class="fa fa-plus-circle"></i> Restock
    </button>

</form>

                    <!-- ===== DISPATCH ===== -->
                    <!-- ===== DISPATCH ===== -->
<?php
$pending_check = $conn->query("
    SELECT id FROM stock_dispatch_requests
    WHERE product_id={$prod['id']}
    AND status='Pending'
    LIMIT 1
")->num_rows;
?>

<?php if($pending_check > 0): ?>

    <button class="btn btn-sm btn-secondary" disabled title="Dispatch locked while request is pending">
        <i class="fa fa-lock"></i>
    </button>

<?php else: ?>

<form method="POST" style="display:inline-block;">
    <input type="hidden" name="product_id" value="<?= $prod['id'] ?>">

    <!-- Department must be selected -->
    <select name="department_id" required style="width:110px;">
        <option value="">Dept</option>
        <?php
        $departments->data_seek(0);
        while($d = $departments->fetch_assoc()):
        ?>
            <option value="<?= $d['id'] ?>"><?= htmlspecialchars($d['name']) ?></option>
        <?php endwhile; ?>
    </select>

    <input type="number" name="dispatch_qty" placeholder="Qty" style="width:65px;" required>
    <button type="submit" name="dispatch_product" class="btn btn-sm btn-primary">
        <i class="fa fa-paper-plane"></i> Dispatch
    </button>
</form>

<?php endif; ?>
                    <?php else: ?>
                        <div class="text-center text-muted">
                            <i class="fa fa-lock"></i> No Access
                        </div>
                    <?php endif; ?>
                </td>
            </tr>
            <?php endforeach; ?>
        </tbody>
    </table>
</div>

<script>
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;
            const sku = row.dataset.sku;
            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);
});
</script>

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