<?php
session_start();
include 'php/db_connection.php';

// শুধুমাত্র লগইন করা ইউজার পেজ দেখতে পারবে
if (!isset($_SESSION['user_id'])) {
    header("Location: login.php");
    exit();
}

$userName = $_SESSION['user_name'];
$role = $_SESSION['role'];
$isAdmin = ($_SESSION['role'] == 'admin');

?>

<?php include 'heade.php';?>
<?php include 'sidebar.php';?>

<?php
// Function to get suppliers for dropdown
function getSuppliers() {
    global $con;
    $result = mysqli_query($con, "SELECT id, name FROM suppliers");
    return $result;
}

// Handle Add Product Form Submission
if (isset($_POST['addProduct'])) {
    $productName = $_POST['productName'];
    $productId = $_POST['productId'];
    $price = $_POST['price'];
    $purchasePrice = $_POST['purchasePrice'];
    $supplierId = $_POST['supplier'];

    // Check if the product already exists
    $checkQuery = "SELECT * FROM product_cat WHERE Product_id = '$productId'";
    $checkResult = mysqli_query($con, $checkQuery);
    if (mysqli_num_rows($checkResult) > 0) {
        echo "<div class='alert alert-danger'>Product with this ID already exists!</div>";
    } else {
        $sql = "INSERT INTO product_cat (Product_name, Product_id, price, purchase_price, supplier_id) 
                VALUES ('$productName', '$productId', '$price', '$purchasePrice', '$supplierId')";
        if (mysqli_query($con, $sql)) {
            echo "<div class='success-message'>Product added successfully!</div>";
            echo "<script>setTimeout(function(){ location.reload(); }, 2000);</script>";
        } else {
            echo "<div class='alert alert-danger'>Error: " . mysqli_error($con) . "</div>";
        }
    }
}

// Handle Delete Product
if (isset($_POST['action']) && $_POST['action'] == 'deleteProduct') {
    $productId = $_POST['id'];
    $sql = "DELETE FROM product_cat WHERE id = $productId";
    if (mysqli_query($con, $sql)) {
        echo json_encode(['status' => 'success', 'message' => 'Product deleted successfully!']);
    } else {
        echo json_encode(['status' => 'error', 'message' => 'Failed to delete product.']);
    }
    exit();
}

// Pagination variables
$limit = 5;
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$offset = ($page - 1) * $limit;

// Get search query
$searchQuery = isset($_GET['search']) ? mysqli_real_escape_string($con, $_GET['search']) : '';

// Fetch product data with pagination, supplier info and search
function getProductWithSupplier($offset, $limit, $searchQuery) {
    global $con;
    
    $whereClause = "";
    if (!empty($searchQuery)) {
        $whereClause = "WHERE pc.Product_name LIKE '%$searchQuery%' 
                        OR pc.Product_id LIKE '%$searchQuery%' 
                        OR s.name LIKE '%$searchQuery%'";
    }
    
    $result = mysqli_query($con, "
        SELECT pc.id, pc.Product_name, pc.Product_id, pc.price, pc.purchase_price, 
               s.name AS supplier_name, 
               SUM(p.quantity) AS total_quantity, 
               SUM(p.quantity * pc.price) AS total_price,
               SUM(p.quantity * pc.purchase_price) AS total_purchase_price
        FROM product_cat pc
        LEFT JOIN suppliers s ON pc.supplier_id = s.id
        LEFT JOIN product p ON pc.Product_id = p.product_id
        $whereClause
        GROUP BY pc.id, pc.Product_name, pc.Product_id, pc.price, pc.purchase_price, s.name
        ORDER BY pc.id DESC
        LIMIT $offset, $limit
    ");
    return $result;
}

// Count total products for pagination with search
function getTotalProducts($searchQuery) {
    global $con;
    
    $whereClause = "";
    if (!empty($searchQuery)) {
        $whereClause = "WHERE pc.Product_name LIKE '%$searchQuery%' 
                        OR pc.Product_id LIKE '%$searchQuery%' 
                        OR s.name LIKE '%$searchQuery%'";
    }
    
    $result = mysqli_query($con, "
        SELECT COUNT(DISTINCT pc.id) as count 
        FROM product_cat pc
        LEFT JOIN suppliers s ON pc.supplier_id = s.id
        $whereClause
    ");
    return mysqli_fetch_assoc($result)['count'];
}

$totalProducts = getTotalProducts($searchQuery);
$totalPages = ceil($totalProducts / $limit);
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>product</title>
    <link rel="stylesheet" href="css/cusatom.css">
    <style>
        .search-box {
            margin: 20px 0;
            padding: 15px;
            background: #f5f5f5;
            border-radius: 5px;
        }
        .search-box input[type="text"] {
            padding: 8px 12px;
            width: 300px;
            border: 1px solid #ddd;
            border-radius: 4px;
            font-size: 14px;
        }
        .search-box button {
            padding: 8px 20px;
            background: #007bff;
            color: white;
            border: none;
            border-radius: 4px;
            cursor: pointer;
            margin-left: 10px;
        }
        .search-box button:hover {
            background: #0056b3;
        }
        .clear-search {
            background: #6c757d !important;
            margin-left: 5px;
        }
        .clear-search:hover {
            background: #5a6268 !important;
        }
    </style>
    <script>
        function deleteProduct(productId) {
            if (confirm("Are you sure you want to delete this product?")) {
                var xhr = new XMLHttpRequest();
                xhr.open("POST", "", true);  
                xhr.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");
                xhr.onreadystatechange = function () {
                    if (xhr.readyState == 4 && xhr.status == 200) {
                        var response = JSON.parse(xhr.responseText);
                        if (response.status === 'success') {
                            alert(response.message);
                            location.reload();
                        } else {
                            alert(response.message);
                        }
                    }
                };
                xhr.send("action=deleteProduct&id=" + productId);
            }
        }
    </script>
</head>
<body>
    
    <!-- Add Product Form -->
    <div style="margin-top: 20px;">
        <h2>Add New Product</h2>
        <form method="POST" action="">
            <div>
                <label for="productName">Product Name:</label>
                <input type="text" id="productName" name="productName" required>
            </div>
            <div>
                <label for="productId">Product ID:</label>
                <input type="text" id="productId" name="productId" required>
            </div>
            <div>
                <label for="price">Price:</label>
                <input type="number" id="price" name="price" step="0.01" required>
            </div>
            <div>
                <label for="purchasePrice">Purchase Price:</label>
                <input type="number" id="purchasePrice" name="purchasePrice" step="0.01" required>
            </div>
            <div>
                <label for="supplier">Supplier:</label>
                <select id="supplier" name="supplier" required>
                    <option value="">Select Supplier</option>
                    <?php
                    $suppliers = getSuppliers();
                    while ($supplier = mysqli_fetch_assoc($suppliers)) {
                        echo "<option value='{$supplier['id']}'>{$supplier['name']}</option>";
                    }
                    ?>
                </select>
            </div>
            <button type="submit" name="addProduct"><i class="bi bi-plus-square"></i></button>
        </form>
    </div>

    <!-- Search Box -->
    <!-- Search Box--------------of---- 
    <div class="search-box">
        <form method="GET" action="">
            <input type="text" name="search" placeholder="Search by Product Name, ID or Supplier..." 
                   value="<?php echo htmlspecialchars($searchQuery); ?>">
            <button type="submit"><i class="bi bi-search"></i> Search</button>
            <?php if (!empty($searchQuery)): ?>
                <a href="?" class="clear-search" style="text-decoration: none; padding: 8px 20px; display: inline-block; color: white; border-radius: 4px;">
                    <i class="bi bi-x-circle"></i> Clear
                </a>
            <?php endif; ?>
        </form>
    </div>---------------------->

    <h5>Product Category</h5>

    <table>
        <tr>
            <th colspan="12"> <form method="GET" action="">
            <input type="text" name="search" placeholder="Search by Product Name, ID or Supplier..." 
                   value="<?php echo htmlspecialchars($searchQuery); ?>">
            <button type="submit"><i class="bi bi-search"></i> Search</button>
            <?php if (!empty($searchQuery)): ?>
                <a href="?" class="clear-search" style="text-decoration: none; padding: 8px 20px; display: inline-block; color: white; border-radius: 4px;">
                    <i class="bi bi-x-circle"></i> Clear
                </a>
            <?php endif; ?>
        </form></th>
        </tr>
        <tr>
            <th>Sl No</th>
            <th>Product Name</th>
            <th>Product ID</th>
            <th>Price</th>
            <th>Purchase Price</th>
            <th>Supplier Name</th>
            <th>Total Quantity</th>
            <th>Total Price</th>
            <th>Total Purchase Price</th>
            <th colspan="3">Action</th>
        </tr>

        <?php 
        $result = getProductWithSupplier($offset, $limit, $searchQuery);
        if ($result->num_rows > 0): ?>
            <?php while ($row = $result->fetch_assoc()): ?>
                <tr>
                    <td><?php echo $row['id']; ?></td>
                    <td><?php echo htmlspecialchars($row['Product_name']); ?></td>
                    <td><?php echo htmlspecialchars($row['Product_id']); ?></td>
                    <td><?php echo htmlspecialchars($row['price']); ?></td>
                    <td><?php echo htmlspecialchars($row['purchase_price']); ?></td>
                    <td><?php echo htmlspecialchars($row['supplier_name']); ?></td>
                    <td><?php echo htmlspecialchars($row['total_quantity']); ?></td>
                    <td><?php echo htmlspecialchars($row['total_price']); ?></td>
                    <td><?php echo htmlspecialchars($row['total_purchase_price']); ?></td>
                    <td>
                        <a href="add_product.php?category=<?= urlencode($row['Product_id']) ?>" class="btn btn-primary"><i class="bi bi-plus-square"></i></a>
                    </td>
                    <td>
                        <button class="action-button" onclick="window.open('barcode_labels.php?search=<?= $row['Product_id'] ?>', '_blank')"><i class="fas fa-barcode"></i></button>
                    </td>
                    <td>
                        <button class='delete-button' onclick='deleteProduct(<?= $row["id"] ?>)'><i class="bi bi-trash"></i></button>
                    </td>
                </tr>
            <?php endwhile; ?>
        <?php else: ?>
            <tr>
                <td colspan="12">
                    <?php echo !empty($searchQuery) ? "No products found matching your search" : "No products found"; ?>
                </td>
            </tr>
        <?php endif; ?>
    </table>

    <!-- Pagination Links -->
    <div class="pagination">
        <?php if ($page > 1): ?>
            <a href="?page=<?php echo $page - 1; ?><?php echo !empty($searchQuery) ? '&search='.urlencode($searchQuery) : ''; ?>">Prev</a>
        <?php endif; ?>

        <span class="active">Page <?php echo $page; ?> of <?php echo $totalPages; ?></span>

        <?php if ($page < $totalPages): ?>
            <a href="?page=<?php echo $page + 1; ?><?php echo !empty($searchQuery) ? '&search='.urlencode($searchQuery) : ''; ?>">Next</a>
        <?php endif; ?>
    </div>

    <?php
    $con->close();
    include 'footer.php';
    ?>
</body>
</html>