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

$username = $_SESSION['username'] ?? 'Guest';

/* =====================================================
   FUNCTIONS
===================================================== */

// Generate random barcode
function generateBarcode($length = 8) {
    return strtoupper(substr(md5(uniqid(rand(), true)), 0, $length));
}

// Send HTML email
function sendEmail($to, $subject, $htmlContent) {
    $headers  = "MIME-Version: 1.0\r\n";
    $headers .= "Content-type:text/html;charset=UTF-8\r\n";
    $headers .= "From: noreply@giantcloudservices.com\r\n";
    mail($to, $subject, $htmlContent, $headers);
}

/* =====================================================
   ADD PRODUCT
===================================================== */

if (isset($_POST['add_product'])) {

    $sku = $_POST['sku'] ?? '';
    $name = $_POST['name'] ?? '';
    $raw_materials = $_POST['raw_materials'] ?? 'No';
    $category_id = !empty($_POST['category_id']) ? intval($_POST['category_id']) : NULL;
    $department_id = !empty($_POST['department_id']) ? intval($_POST['department_id']) : NULL;

    $buying_price = floatval($_POST['buying_price'] ?? 0);
    $selling_price = floatval($_POST['selling_price'] ?? 0);
    $vat_rate = floatval($_POST['vat_rate'] ?? 0);
    $quantity = intval($_POST['quantity'] ?? 0);
    $min_level = intval($_POST['min_level'] ?? 0);
    $barcode = !empty($_POST['barcode']) ? $_POST['barcode'] : generateBarcode();
    $can_sell = isset($_POST['can_sell']) ? 1 : 0;

    $stmt = $conn->prepare("
        INSERT INTO products 
        (sku, name, raw_materials, category_id, buying_price, selling_price, vat_rate, quantity, min_level, barcode, can_sell_when_out_of_stock) 
        VALUES (?,?,?,?,?,?,?,?,?,?,?)
    ");

    $stmt->bind_param(
        "sssidddiisi",
        $sku,
        $name,
        $raw_materials,
        $category_id,
        $buying_price,
        $selling_price,
        $vat_rate,
        $quantity,
        $min_level,
        $barcode,
        $can_sell
    );

    if ($stmt->execute()) {

        $product_id = $stmt->insert_id;

        if ($quantity > 0) {
            $stmtStock = $conn->prepare("
                INSERT INTO product_stock (product_id, store_id, department_id, quantity)
                VALUES (?,?,?,?)
            ");
            $store_id = 1;
            $stmtStock->bind_param("iiii", $product_id, $store_id, $department_id, $quantity);
            $stmtStock->execute();
        }

        echo "<script>alert('Product \"$name\" added successfully');window.location='products.php';</script>";
        exit;

    } else {
        echo "Error: " . $stmt->error;
    }
}

/* =====================================================
   DELETE PRODUCT
===================================================== */

if (isset($_GET['delete'])) {
    $delete_id = intval($_GET['delete']);

    $prodRow = $conn->query("SELECT * FROM products WHERE id=$delete_id")->fetch_assoc();
    $product_name = $prodRow['name'] ?? '';
    $product_sku = $prodRow['sku'] ?? '';
    $product_barcode = $prodRow['barcode'] ?? '';

    $stmtStock = $conn->prepare("DELETE FROM product_stock WHERE product_id=?");
    $stmtStock->bind_param("i", $delete_id);
    $stmtStock->execute();

    $stmtProd = $conn->prepare("DELETE FROM products WHERE id=?");
    $stmtProd->bind_param("i", $delete_id);

    if ($stmtProd->execute()) {

        $to = "giantplusmw@gmail.com";
        $subject = "Product Deleted Notification";
        $htmlContent = "
            <h3>Product Deleted from Inventory</h3>
            <p><strong>SKU:</strong> {$product_sku}</p>
            <p><strong>Name:</strong> {$product_name}</p>
            <p><strong>Barcode:</strong> {$product_barcode}</p>
            <p>Deleted by: <strong>{$_SESSION['username']}</strong></p>
            <p>Time: " . date('Y-m-d H:i:s') . "</p>
        ";

        sendEmail($to, $subject, $htmlContent);

        echo "<script>alert('Product deleted successfully');window.location='products.php';</script>";
        exit;
    }
}

/* =====================================================
   FILTERS
===================================================== */

$search = $_GET['search'] ?? '';
$filter_department = $_GET['department_id'] ?? '';
$filter_category = $_GET['category_id'] ?? '';
$filter_stock = $_GET['stock_status'] ?? '';

$where = [];
$params = [];
$types = '';

if (!empty($search)) {
    $where[] = "(p.name LIKE ? OR p.sku LIKE ?)";
    $searchLike = "%$search%";
    $params[] = $searchLike;
    $params[] = $searchLike;
    $types .= 'ss';
}

if (!empty($filter_category)) {
    $where[] = "p.category_id = ?";
    $params[] = $filter_category;
    $types .= 'i';
}

if (!empty($filter_department)) {
    $where[] = "ps.department_id = ?";
    $params[] = $filter_department;
    $types .= 'i';
}

if ($filter_stock == 'out') {
    $where[] = "IFNULL(ps.quantity,0) = 0";
}

if ($filter_stock == 'low') {
    $where[] = "IFNULL(ps.quantity,0) <= p.min_level AND IFNULL(ps.quantity,0) > 0";
}

if ($filter_stock == 'ok') {
    $where[] = "IFNULL(ps.quantity,0) > p.min_level";
}

$whereSQL = $where ? ('WHERE ' . implode(' AND ', $where)) : '';
/* =====================================================
   EXPORT TO EXCEL
===================================================== */

if (isset($_GET['export']) && $_GET['export'] === 'excel') {

    // Clean output buffer (VERY IMPORTANT)
    if (ob_get_length()) ob_end_clean();

    $exportSql = "
    SELECT 
        p.sku,
        p.name,
        c.name AS category,
        d.name AS department,
        IFNULL(ps.quantity,0) AS stock,
        p.min_level,
        p.selling_price,
        p.barcode,
        CASE WHEN p.can_sell_when_out_of_stock = 1 THEN 'Yes' ELSE 'No' END AS can_sell
    FROM products p
    LEFT JOIN categories c ON p.category_id = c.id
    LEFT JOIN product_stock ps ON ps.product_id = p.id AND ps.store_id = 1
    LEFT JOIN departments d ON ps.department_id = d.id
    $whereSQL
    ORDER BY p.id DESC
    ";

    $exportStmt = $conn->prepare($exportSql);

    if (!empty($params)) {
        $exportStmt->bind_param($types, ...$params);
    }

    $exportStmt->execute();
    $exportResult = $exportStmt->get_result();

    // Excel headers
    header("Content-Type: application/vnd.ms-excel; charset=utf-8");
    header("Content-Disposition: attachment; filename=products_export_" . date('YmdHis') . ".xls");
    header("Pragma: no-cache");
    header("Expires: 0");

    echo "SKU\tName\tCategory\tDepartment\tStock\tMin Level\tSelling Price\tBarcode\tCan Sell\n";

    while ($row = $exportResult->fetch_assoc()) {
        echo implode("\t", [
            $row['sku'],
            $row['name'],
            $row['category'],
            $row['department'],
            $row['stock'],
            $row['min_level'],
            $row['selling_price'],
            $row['barcode'],
            $row['can_sell']
        ]) . "\n";
    }

    exit;
}


/* =====================================================
   PAGINATION
===================================================== */

/* =====================================================
   PAGINATION
===================================================== */

/* =====================================================
   PAGINATION
===================================================== */

$limit = $_GET['limit'] ?? 40;

// ✅ Handle "all" option
if ($limit === 'all') {
    $limit = 999999999; // very large number
    $showAll = true;
} else {
    $limit = intval($limit);
    if ($limit <= 0) $limit = 40;
    $showAll = false;
}

$page = isset($_GET['page']) ? max(1, intval($_GET['page'])) : 1;
$offset = ($page - 1) * $limit;



/* =====================================================
   COUNT TOTAL
===================================================== */

$countSql = "
SELECT COUNT(*) AS total
FROM products p
LEFT JOIN product_stock ps ON ps.product_id = p.id AND ps.store_id = 1
$whereSQL
";

$countStmt = $conn->prepare($countSql);

if (!empty($params)) {
    $countStmt->bind_param($types, ...$params);
}

$countStmt->execute();
$totalRows = $countStmt->get_result()->fetch_assoc()['total'];
$totalPages = ceil($totalRows / $limit);

/* =====================================================
   MAIN QUERY
===================================================== */

$sql = "
SELECT 
    p.*,
    c.name AS category_name,
    d.name AS department_name,
    IFNULL(ps.quantity,0) AS stock_qty
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN product_stock ps ON ps.product_id = p.id AND ps.store_id = 1
LEFT JOIN departments d ON ps.department_id = d.id
$whereSQL
ORDER BY p.id DESC
LIMIT ? OFFSET ?
";

$stmt = $conn->prepare($sql);

$params2 = $params;
$types2 = $types;

$types2 .= 'ii';
$params2[] = $limit;
$params2[] = $offset;

$stmt->bind_param($types2, ...$params2);
$stmt->execute();
$products = $stmt->get_result();

/* =====================================================
   SUPPORT DATA
===================================================== */

$categories = $conn->query("SELECT * FROM categories ORDER BY name ASC");
$departments = $conn->query("SELECT * FROM departments ORDER BY name ASC");

$result = $conn->query("SELECT MAX(id) AS last_id FROM products");
$row = $result->fetch_assoc();
$next_id = ($row['last_id'] ?? 0) + 1;
$sku = $next_id;
?>

<div class="container mt-4">

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

<button class="btn btn-secondary mb-3" onclick="history.back()">
    <i class="fa fa-arrow-left"></i> Back
</button>

<h3 class="text-center">Product Management</h3>
<hr>

<!-- ================= ADD PRODUCT ================= -->
<form method="POST" class="row g-3 mb-4">

<input type="hidden" name="sku" value="RL<?= $sku ?>">

<div class="col-md-3">
<input type="text" name="name" class="form-control" placeholder="Product Name" required>
</div>

<div class="col-md-3">
<select name="category_id" class="form-control">
<option value="">-- Select Category --</option>
<?php while ($cat = $categories->fetch_assoc()): ?>
<option value="<?= $cat['id'] ?>"><?= htmlspecialchars($cat['name']) ?></option>
<?php endwhile; ?>
</select>
</div>

<div class="col-md-3">
<select name="department_id" class="form-control" required>
<option value="">-- Select Department --</option>
<?php while ($dep = $departments->fetch_assoc()): ?>
<option value="<?= $dep['id'] ?>"><?= htmlspecialchars($dep['name']) ?></option>
<?php endwhile; ?>
</select>
</div>

<div class="col-md-2">
<input type="number" step="0.01" name="buying_price" class="form-control" placeholder="Buying Price">
</div>

<div class="col-md-2">
<input type="number" step="0.01" name="selling_price" class="form-control" placeholder="Selling Price">
</div>

<div class="col-md-2">
<input type="number" step="0.01" name="vat_rate" class="form-control" placeholder="VAT Rate (%)">
</div>

<div class="col-md-2">
<input type="number" name="quantity" class="form-control" placeholder="Quantity">
</div>

<div class="col-md-2">
<input type="number" name="min_level" class="form-control" placeholder="Min Level">
</div>

<div class="col-md-2">
<select name="raw_materials" class="form-control" required>
<option value="" disabled selected>Raw Material?</option>
<option value="No">No</option>
<option value="Yes">Yes</option>
</select>
</div>

<div class="col-md-3 d-flex align-items-center">
<input type="checkbox" name="can_sell" class="mr-2">
<label>Can sell when out of stock</label>
</div>

<div class="col-md-2">
<button type="submit" name="add_product" class="btn btn-success w-100">
Add Product
</button>
</div>

</form>

<!-- ================= PRODUCT TABLE ================= -->
<div class="mb-2 text-muted d-flex justify-content-between align-items-center flex-wrap">

    <div>
        <?php if($showAll): ?>
            Showing <strong>ALL</strong> records |
        <?php else: ?>
            Showing <?= $limit ?> records per page |
        <?php endif; ?>
        Total Records: <strong><?= $totalRows ?></strong>
    </div>

    <!-- Records per page selector -->
    <form method="GET" class="d-flex">
        <?php
        // preserve other filters
        foreach ($_GET as $key => $val) {
            if ($key != 'limit' && $key != 'page') {
                echo '<input type="hidden" name="'.htmlspecialchars($key).'" value="'.htmlspecialchars($val).'">';
            }
        }
        ?>
        <select name="limit" class="form-select form-select-sm" onchange="this.form.submit()">
            <option value="10" <?= ($_GET['limit'] ?? '') == 10 ? 'selected' : '' ?>>10</option>
            <option value="20" <?= ($_GET['limit'] ?? '') == 20 ? 'selected' : '' ?>>20</option>
            <option value="40" <?= ($_GET['limit'] ?? 40) == 40 ? 'selected' : '' ?>>40</option>
            <option value="100" <?= ($_GET['limit'] ?? '') == 100 ? 'selected' : '' ?>>100</option>
            <option value="all" <?= ($_GET['limit'] ?? '') == 'all' ? 'selected' : '' ?>>All</option>
        </select>
    </form>

</div>
<div class="mb-3 d-flex justify-content-between flex-wrap">

    <div>
        <a href="?export=excel&<?= http_build_query($_GET) ?>" 
           class="btn btn-success">
            <i class="fa fa-file-excel"></i> Export to Excel
        </a>
    </div>

</div>
<div class="row mb-3">
    <div class="col-md-4">
        <div class="input-group">
            <span class="input-group-text">
                <i class="fa fa-search"></i>
            </span>
            <input type="text" id="liveSearch" class="form-control"
                   placeholder="Live search...">
        </div>
    </div>
</div>


<table class="table table-bordered table-striped" id="dataTable">

<thead>
<tr>
<th>SKU</th>
<th>Name</th>
<th>Category</th>
<th>Department</th>
<th>Stock</th>
<th>Min Level</th>
<th>SP</th>
<th>Barcode</th>
<th>Can Sell?</th>
<th>Actions</th>
</tr>
</thead>

<tbody>
<?php while ($prod = $products->fetch_assoc()):
$class = '';
if ($prod['stock_qty'] == 0) $class = 'table-danger';
elseif ($prod['stock_qty'] <= $prod['min_level']) $class = 'table-warning';
?>
<tr class="<?= $class ?>">

<td><?= $prod['sku'] ?></td>
<td><?= htmlspecialchars($prod['name']) ?></td>
<td><?= htmlspecialchars($prod['category_name'] ?? '') ?></td>
<td><span class="badge bg-primary"><?= htmlspecialchars($prod['department_name'] ?? 'N/A') ?></span></td>
<td><span class="badge bg-info"><?= intval($prod['stock_qty']) ?></span></td>
<td><?= intval($prod['min_level']) ?></td>
<td><?= number_format($prod['selling_price'],2) ?></td>
<td><?= htmlspecialchars($prod['barcode']) ?></td>
<td><?= $prod['can_sell_when_out_of_stock'] ? 'Yes' : 'No' ?></td>

<td>
<a href="edit_product.php?id=<?= $prod['id'] ?>" class="btn btn-sm btn-info">Edit</a>
<a href="products.php?delete=<?= $prod['id'] ?>" class="btn btn-sm btn-danger" onclick="return confirm('Are you sure?')">Delete</a>
<a href="restock.php?id=<?= $prod['id'] ?>" class="btn btn-sm btn-success">Restock</a>
</td>

</tr>
<?php endwhile; ?>
</tbody>

</table>

<!-- ================= PAGINATION ================= -->

<?php if ($totalPages > 1 && !$showAll): ?>

<nav>
<ul class="pagination justify-content-center">
<?php $queryParams = $_GET; ?>

<?php if ($page > 1): $queryParams['page'] = $page - 1; ?>
<li class="page-item">
<a class="page-link" href="?<?= http_build_query($queryParams) ?>">&laquo; Prev</a>
</li>
<?php endif; ?>

<?php for ($i = 1; $i <= $totalPages; $i++):
$queryParams['page'] = $i; ?>
<li class="page-item <?= $i == $page ? 'active' : '' ?>">
<a class="page-link" href="?<?= http_build_query($queryParams) ?>"><?= $i ?></a>
</li>
<?php endfor; ?>

<?php if ($page < $totalPages): $queryParams['page'] = $page + 1; ?>
<li class="page-item">
<a class="page-link" href="?<?= http_build_query($queryParams) ?>">Next &raquo;</a>
</li>
<?php endif; ?>

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

</div>
<script>
document.getElementById("liveSearch").addEventListener("keyup", function () {
    let value = this.value.toLowerCase();
    let rows = document.querySelectorAll("#dataTable tbody tr");

    rows.forEach(function(row) {
        row.style.display =
            row.textContent.toLowerCase().includes(value)
            ? ""
            : "none";
    });
});
</script>

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