<?php
date_default_timezone_set('Asia/Colombo');
include '../config.php';
include '../auth.php';
checkAuth();

// Handle delete product
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['delete_product'])) {
    // Enable error reporting for debugging
    error_reporting(E_ALL);
    ini_set('display_errors', 1);
    
    try {
        // Log the attempt
        error_log("Delete product attempt - Product ID: " . ($_POST['product_id'] ?? 'not set'));
        
        if (!isset($_POST['product_id']) || empty($_POST['product_id'])) {
            throw new Exception("Product ID is required");
        }
        
        $product_id = (int)$_POST['product_id'];
        
        if ($product_id <= 0) {
            throw new Exception("Invalid product ID: " . $product_id);
        }
        
        // Check database connection
        if (!$conn || $conn->connect_error) {
            throw new Exception("Database connection failed: " . ($conn->connect_error ?? 'Unknown connection error'));
        }
        
        // Check if product exists and get details for logging
        $check_stmt = $conn->prepare('SELECT name FROM products WHERE id = ?');
        if (!$check_stmt) {
            throw new Exception("Prepare statement failed: " . $conn->error);
        }
        
        $check_stmt->bind_param('i', $product_id);
        
        if (!$check_stmt->execute()) {
            throw new Exception("Execute statement failed: " . $check_stmt->error);
        }
        
        $result = $check_stmt->get_result();
        
        if ($result->num_rows > 0) {
            $product = $result->fetch_assoc();
            error_log("Found product to delete: " . $product['name']);
            
            // Start transaction for safe deletion
            $conn->autocommit(FALSE);
            
            try {
                // First, delete related inventory transactions
                $delete_transactions_stmt = $conn->prepare('DELETE FROM inventory_transactions WHERE product_id = ?');
                if ($delete_transactions_stmt) {
                    $delete_transactions_stmt->bind_param('i', $product_id);
                    $delete_transactions_stmt->execute();
                    $transactions_deleted = $delete_transactions_stmt->affected_rows;
                    $delete_transactions_stmt->close();
                    error_log("Deleted {$transactions_deleted} inventory transactions for product ID: {$product_id}");
                }
                
                // Then, delete related sale items (if any)
                $delete_sale_items_stmt = $conn->prepare('DELETE FROM sale_items WHERE product_id = ?');
                if ($delete_sale_items_stmt) {
                    $delete_sale_items_stmt->bind_param('i', $product_id);
                    $delete_sale_items_stmt->execute();
                    $sale_items_deleted = $delete_sale_items_stmt->affected_rows;
                    $delete_sale_items_stmt->close();
                    error_log("Deleted {$sale_items_deleted} sale items for product ID: {$product_id}");
                }
                
                // Finally, delete the product
                $stmt = $conn->prepare('DELETE FROM products WHERE id = ?');
                if (!$stmt) {
                    throw new Exception("Delete prepare failed: " . $conn->error);
                }
                
                $stmt->bind_param('i', $product_id);
                
                if ($stmt->execute()) {
                    if ($stmt->affected_rows > 0) {
                        // Commit the transaction
                        $conn->commit();
                        $success_message = "Product '{$product['name']}' and all related records deleted successfully.";
                        error_log("Successfully deleted product: " . $product['name']);
                    } else {
                        throw new Exception("No rows affected. Product may have already been deleted.");
                    }
                } else {
                    throw new Exception("Delete execution failed: " . $stmt->error);
                }
                $stmt->close();
                
            } catch (Exception $e) {
                // Rollback on error
                $conn->rollback();
                throw $e;
            } finally {
                // Restore autocommit
                $conn->autocommit(TRUE);
            }
        } else {
            throw new Exception("Product with ID {$product_id} not found.");
        }
        $check_stmt->close();
        
    } catch (Exception $e) {
        $error_message = "Error: " . $e->getMessage();
        error_log("Product deletion error: " . $error_message);
    } catch (Error $e) {
        $error_message = "PHP Error: " . $e->getMessage();
        error_log("Product deletion PHP error: " . $error_message);
    }
    
    // Redirect with message
    if (isset($success_message)) {
        header('Location: products.php?success=' . urlencode($success_message));
    } else {
        header('Location: products.php?error=' . urlencode($error_message ?? 'Unknown error occurred'));
    }
    exit();
}

// Handle edit product
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['edit_product'])) {
    try {
        $product_id = (int)$_POST['product_id'];
        $name = trim($_POST['edit_name']);
        $category = $_POST['edit_category'];
        $wholesale_price = (float)($_POST['edit_wholesale_price'] ?? 0);
        $price = (float)$_POST['edit_price'];
        
        // Validate required fields
        if (empty($name) || empty($category) || $price <= 0) {
            throw new Exception("Please fill all required fields with valid values.");
        }
        
        // Handle different pricing types for editing
        $pricing_type = $_POST['edit_pricing_type'] ?? 'single';
        $half_price = 0;
        
        if ($pricing_type === 'dual') {
            $half_price = $price / 2; // Auto-calculate 50%
        } elseif ($pricing_type === 'custom' && !empty($_POST['edit_half_price'])) {
            $half_price = (float)$_POST['edit_half_price']; // Use custom value
        }
        // For 'single' pricing type, half_price remains 0
        
        $stock = (int)$_POST['edit_stock'];
        $barcode = !empty($_POST['edit_barcode']) ? trim($_POST['edit_barcode']) : null;
        
        $stmt = $conn->prepare('UPDATE products SET name = ?, category = ?, wholesale_price = ?, price = ?, half_price = ?, stock = ?, barcode = ? WHERE id = ?');
        $stmt->bind_param('ssdddisi', $name, $category, $wholesale_price, $price, $half_price, $stock, $barcode, $product_id);
        
        if ($stmt->execute()) {
            $success_message = "Product '$name' updated successfully.";
        } else {
            throw new Exception("Database error: " . $conn->error);
        }
        $stmt->close();
        
    } catch (Exception $e) {
        $error_message = "Error updating product: " . $e->getMessage();
    }
    
    // Redirect with message
    if (isset($success_message)) {
        header('Location: products.php?success=' . urlencode($success_message));
    } else {
        header('Location: products.php?error=' . urlencode($error_message ?? 'Unknown error occurred'));
    }
    exit();
}

// Handle delete category
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['delete_category'])) {
    try {
        $category_id = (int)$_POST['category_id'];
        
        // Check if category has products
        $check_stmt = $conn->prepare('SELECT COUNT(*) as count FROM products WHERE category_id = ?');
        $check_stmt->bind_param('i', $category_id);
        $check_stmt->execute();
        $result = $check_stmt->get_result()->fetch_assoc();
        $check_stmt->close();
        
        if ($result['count'] > 0) {
            throw new Exception("Cannot delete category: it contains {$result['count']} products. Move or delete products first.");
        }
        
        $stmt = $conn->prepare('DELETE FROM categories WHERE id = ?');
        $stmt->bind_param('i', $category_id);
        
        if ($stmt->execute()) {
            $success_message = "Category deleted successfully.";
        } else {
            throw new Exception("Database error: " . $conn->error);
        }
        $stmt->close();
        
    } catch (Exception $e) {
        $error_message = "Error deleting category: " . $e->getMessage();
    }
    
    // Redirect with message
    if (isset($success_message)) {
        header('Location: products.php?success=' . urlencode($success_message));
    } else {
        header('Location: products.php?error=' . urlencode($error_message ?? 'Unknown error occurred'));
    }
    exit();
}

// Handle add category (with optional image)
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['new_category']) && !isset($_POST['edit_category_id'])) {
    try {
        $new_category = trim($_POST['new_category']);
        if (empty($new_category)) {
            throw new Exception("Category name cannot be empty.");
        }
        
        // Check if category already exists
        $check_stmt = $conn->prepare('SELECT id FROM categories WHERE name = ?');
        $check_stmt->bind_param('s', $new_category);
        $check_stmt->execute();
        
        if ($check_stmt->get_result()->num_rows > 0) {
            throw new Exception("Category '$new_category' already exists.");
        }
        $check_stmt->close();
        
        // Optional image upload
        $imagePath = null;
        if (!empty($_FILES['category_image']['name']) && $_FILES['category_image']['error'] === UPLOAD_ERR_OK) {
            $allowed = ['jpg','jpeg','png','gif','webp'];
            $ext = strtolower(pathinfo($_FILES['category_image']['name'], PATHINFO_EXTENSION));
            if (!in_array($ext, $allowed)) {
                throw new Exception('Invalid image type. Allowed: jpg, jpeg, png, gif, webp');
            }
            $uploadDir = __DIR__ . '/uploads';
            if (!is_dir($uploadDir)) { @mkdir($uploadDir, 0775, true); }
            $filename = 'category_' . time() . '_' . random_int(1000,9999) . '.' . $ext;
            $dest = $uploadDir . '/' . $filename;
            if (!move_uploaded_file($_FILES['category_image']['tmp_name'], $dest)) {
                throw new Exception('Failed to save uploaded image.');
            }
            // Store path relative to products/ for POS to resolve as ../products/<path>
            $imagePath = 'uploads/' . $filename;
        }
        
        if ($imagePath) {
            $stmt = $conn->prepare('INSERT INTO categories (name, image) VALUES (?, ?)');
            $stmt->bind_param('ss', $new_category, $imagePath);
        } else {
            $stmt = $conn->prepare('INSERT INTO categories (name) VALUES (?)');
            $stmt->bind_param('s', $new_category);
        }
        
        if ($stmt->execute()) {
            $success_message = "Category '$new_category' added successfully.";
        } else {
            throw new Exception("Database error: " . $conn->error);
        }
        $stmt->close();
        
        header('Location: products.php?success=' . urlencode($success_message));
        exit();
        
    } catch (Exception $e) {
        $error_message = "Error adding category: " . $e->getMessage();
        header('Location: products.php?error=' . urlencode($error_message));
        exit();
    }
}

// Handle edit/update category (name and optional image) - SEPARATE from add handler
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['edit_category_id'])) {
    try {
        $cat_id = (int)$_POST['edit_category_id'];
        $cat_name = trim($_POST['edit_category_name'] ?? '');
        if ($cat_id <= 0 || $cat_name === '') {
            throw new Exception('Invalid category data.');
        }

        $imageClause = '';
        $params = [$cat_name];
        $types = 's';

        if (isset($_FILES['edit_category_image']) && $_FILES['edit_category_image']['error'] === UPLOAD_ERR_OK) {
            $allowed = ['jpg','jpeg','png','gif','webp'];
            $ext = strtolower(pathinfo($_FILES['edit_category_image']['name'], PATHINFO_EXTENSION));
            if (!in_array($ext, $allowed)) {
                throw new Exception('Invalid image type. Allowed: jpg, jpeg, png, gif, webp');
            }
            $uploadDir = __DIR__ . '/uploads';
            if (!is_dir($uploadDir)) { @mkdir($uploadDir, 0775, true); }
            $filename = 'category_' . time() . '_' . random_int(1000,9999) . '.' . $ext;
            $dest = $uploadDir . '/' . $filename;
            if (!move_uploaded_file($_FILES['edit_category_image']['tmp_name'], $dest)) {
                throw new Exception('Failed to save uploaded image.');
            }
            $imagePath = 'uploads/' . $filename;
            $imageClause = ', image = ?';
            $params[] = $imagePath;
            $types .= 's';
        }
        
        $params[] = $cat_id;
        $types .= 'i';

        $sql = 'UPDATE categories SET name = ?' . $imageClause . ' WHERE id = ?';
        $stmt = $conn->prepare($sql);
        $stmt->bind_param($types, ...$params);
        if ($stmt->execute()) {
            $success_message = 'Category updated successfully.';
        } else {
            throw new Exception('Database error: ' . $conn->error);
        }
        $stmt->close();

        header('Location: products.php?success=' . urlencode($success_message));
        exit();
    } catch (Exception $e) {
        $error_message = 'Error updating category: ' . $e->getMessage();
        header('Location: products.php?error=' . urlencode($error_message));
        exit();
    }
}

// Handle add product
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['name'])) {
    try {
        $name = trim($_POST['name']);
        $category = $_POST['category'];
        $wholesale_price = (float)($_POST['wholesale_price'] ?? 0);
        $price = (float)$_POST['price'];
        
        // Validate required fields
        if (empty($name) || $price <= 0) {
            throw new Exception("Product name and price are required and price must be greater than 0.");
        }
        // No duplicate barcode check: allow multiple products with the same barcode
        
        // Handle different pricing types
        $pricing_type = $_POST['pricing_type'] ?? 'single';
        $half_price = 0;
        
        if ($pricing_type === 'dual') {
            $half_price = $price / 2; // Auto-calculate 50%
        } elseif ($pricing_type === 'custom' && !empty($_POST['half_price'])) {
            $half_price = (float)$_POST['half_price']; // Use custom value
        }
        // For 'single' pricing type, half_price remains 0
        
        $stock = (int)$_POST['stock'];
        $barcode = !empty($_POST['barcode']) ? trim($_POST['barcode']) : null;
        
        $stmt = $conn->prepare('INSERT INTO products (name, category, wholesale_price, price, half_price, stock, barcode) VALUES (?, ?, ?, ?, ?, ?, ?)');
        $stmt->bind_param('ssdddis', $name, $category, $wholesale_price, $price, $half_price, $stock, $barcode);
        
        if ($stmt->execute()) {
            $success_message = "Product '$name' added successfully.";
        } else {
            throw new Exception("Database error: " . $conn->error);
        }
        $stmt->close();
        
        // Redirect with success message
        header('Location: products.php?success=' . urlencode($success_message));
        exit();
        
    } catch (Exception $e) {
        $error_message = "Error adding product: " . $e->getMessage();
        header('Location: products.php?error=' . urlencode($error_message));
        exit();
    }
}
// Fetch categories
$categories_result = $conn->query('SELECT * FROM categories ORDER BY name');
$categories = [];
while ($row = $categories_result->fetch_assoc()) {
    $categories[] = $row;
}

// Fetch products (with optional search) - Paginated for better performance
$page = isset($_GET['page']) ? max(1, intval($_GET['page'])) : 1;
$per_page = 50; // Show 50 products per page
$offset = ($page - 1) * $per_page;

$search_query = isset($_GET['q']) ? trim($_GET['q']) : '';
if ($search_query !== '') {
    $like = '%' . $search_query . '%';
    // Count total for pagination
    $count_stmt = $conn->prepare('SELECT COUNT(*) as total FROM products WHERE name LIKE ? OR barcode LIKE ? OR category LIKE ?');
    $count_stmt->bind_param('sss', $like, $like, $like);
    $count_stmt->execute();
    $total_products = $count_stmt->get_result()->fetch_assoc()['total'];
    $count_stmt->close();
    
    $stmt = $conn->prepare('SELECT id, name, category, wholesale_price, price, half_price, stock, barcode FROM products WHERE name LIKE ? OR barcode LIKE ? OR category LIKE ? ORDER BY name LIMIT ? OFFSET ?');
    $stmt->bind_param('sssii', $like, $like, $like, $per_page, $offset);
    $stmt->execute();
    $result = $stmt->get_result();
} else {
    // Count total for pagination
    $total_products = $conn->query('SELECT COUNT(*) as total FROM products')->fetch_assoc()['total'];
    $result = $conn->query('SELECT id, name, category, wholesale_price, price, half_price, stock, barcode FROM products ORDER BY name LIMIT ' . $per_page . ' OFFSET ' . $offset);
}
$total_pages = ceil($total_products / $per_page);
?>
<!DOCTYPE html>
<html>
<head>
    <title>Products</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css" rel="stylesheet">
    <style>
        body { background: #f8f9fa; }
        .container { margin-top: 40px; }
        .table th, .table td { vertical-align: middle; }
    </style>
</head>
<body>
    <div class="container">
        <?php if (isset($_GET['success'])): ?>
            <div class="alert alert-success alert-dismissible fade show" role="alert">
                <i class="fas fa-check-circle"></i> <?= htmlspecialchars($_GET['success']) ?>
                <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
            </div>
        <?php endif; ?>
        
        <?php if (isset($_GET['error'])): ?>
            <div class="alert alert-danger alert-dismissible fade show" role="alert">
                <i class="fas fa-exclamation-triangle"></i> <?= htmlspecialchars($_GET['error']) ?>
                <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
            </div>
        <?php endif; ?>
        
        <div class="d-flex justify-content-between align-items-center mb-4">
            <h1 class="fw-bold">Products</h1>
            <a href="../index.php" class="btn btn-secondary">Home</a>
        </div>
        <div class="card mb-4">
            <div class="card-header bg-primary text-white d-flex justify-content-between align-items-center">
                <span><i class="fas fa-plus"></i> Add New Product</span>
                <div>
                    <button type="button" class="btn btn-light btn-sm me-2" data-bs-toggle="modal" data-bs-target="#categoryModal">Add Category</button>
                    <button type="button" class="btn btn-light btn-sm me-2">Choose File</button>
                    <button type="submit" form="productForm" class="btn btn-success btn-sm">Add</button>
                </div>
            </div>
            <div class="card-body">
                <form method="post" id="productForm">
                    <div class="row g-3">
                        <div class="col-md-2">
                            <label class="form-label">Product Name *</label>
                            <input type="text" name="name" class="form-control" required>
                        </div>
                        <div class="col-md-2">
                            <label class="form-label">Category</label>
                            <select name="category" class="form-select">
                                <option value="">Select...</option>
                                <?php foreach ($categories as $cat): ?>
                                    <option value="<?= htmlspecialchars($cat['name']) ?>"><?= htmlspecialchars($cat['name']) ?></option>
                                <?php endforeach; ?>
                            </select>
                        </div>
                        <div class="col-md-2">
                            <label class="form-label">Wholesale Price *</label>
                            <input type="number" step="0.01" name="wholesale_price" class="form-control" required>
                        </div>
                        <div class="col-md-2">
                            <label class="form-label">Pricing Type</label>
                            <select name="pricing_type" class="form-select" id="pricingType">
                                <option value="Pac">Pac</option>
                                <option value="Yara">Yara</option>
                                <option value="Box">Box</option>
                            </select>
                        </div>
                        <div class="col-md-2">
                            <label class="form-label">Price *</label>
                            <input type="number" step="0.01" name="price" class="form-control" required>
                        </div>
                        <div class="col-md-2">
                            <label class="form-label">Stock *</label>
                            <input type="number" name="stock" class="form-control" required>
                        </div>
                        <div class="col-md-2">
                            <label class="form-label">Barcode</label>
                            <input type="text" name="barcode" class="form-control" placeholder="Optional">
                        </div>
                    </div>
                </form>
            </div>
        </div>

        <!-- Category Management -->
        <div class="card mb-4">
            <div class="card-header bg-info text-white">
                <i class="fas fa-tags"></i> Category Management
            </div>
            <div class="card-body">
                <div class="row">
                    <?php foreach ($categories as $cat): ?>
                        <div class="col-md-3 mb-2">
                            <div class="d-flex align-items-center justify-content-between p-2 border rounded">
                                <span class="d-flex align-items-center gap-2">
                                    <?php if (!empty($cat['image'])): ?>
                                        <img src="<?= htmlspecialchars($cat['image']) ?>" alt="" style="width:32px;height:32px;object-fit:cover;border-radius:6px;">
                                    <?php endif; ?>
                                    <?= htmlspecialchars($cat['name']) ?>
                                </span>
                                <div>
                                    <button class="btn btn-sm btn-primary me-1" onclick="openEditCategoryModal(<?= $cat['id'] ?>, '<?= htmlspecialchars($cat['name']) ?>', '<?= htmlspecialchars($cat['image'] ?? '') ?>')">
                                        <i class="fas fa-edit"></i>
                                    </button>
                                    <button class="btn btn-sm btn-danger" onclick="deleteCategory(<?= $cat['id'] ?>, '<?= htmlspecialchars($cat['name']) ?>')">
                                        <i class="fas fa-trash"></i>
                                    </button>
                                </div>
                            </div>
                        </div>
                    <?php endforeach; ?>
                </div>
            </div>
        </div>

        <div class="card">
            <div class="card-header bg-success text-white d-flex justify-content-between align-items-center">
                <span>Product List</span>
                <form method="get" class="d-flex align-items-center gap-2" style="max-width:480px;width:100%;">
                    <input type="text" name="q" value="<?= htmlspecialchars($search_query) ?>" class="form-control form-control-sm" placeholder="Search by name, barcode, or category...">
                    <?php if ($search_query !== ''): ?>
                        <a href="products.php" class="btn btn-outline-light btn-sm">Clear</a>
                    <?php endif; ?>
                    <button type="submit" class="btn btn-light btn-sm"><i class="fas fa-search"></i></button>
                </form>
            </div>
            <div class="card-body">
                <?php if ($search_query !== ''): ?>
                    <div class="alert alert-info py-2">Showing results for <strong><?= htmlspecialchars($search_query) ?></strong> — <?= $result->num_rows ?> found</div>
                <?php endif; ?>
                <table class="table table-bordered table-striped">
                    <thead>
                        <tr>
                            <th>ID</th>
                            <th>Barcode</th>
                            <th>Name</th>
                            <th>Category</th>
                            <th>Wholesale Price</th>
                            <th>Full Price</th>
                            <th>Half Price</th>
                            <th>Profit Margin</th>
                            <th>Stock</th>
                            <th>Actions</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php while ($row = $result->fetch_assoc()): ?>
                        <?php 
                            $wholesale = $row['wholesale_price'] ?? 0;
                            $selling = $row['price'] ?? 0;
                            $half_price = $row['half_price'] ?? 0;
                            $profit_margin = $wholesale > 0 ? (($selling - $wholesale) / $wholesale) * 100 : 0;
                        ?>
                        <tr>
                            <td><?= $row['id'] ?></td>
                            <td>
                                <?php if (!empty($row['barcode'])): ?>
                                    <span class="font-monospace"><?= htmlspecialchars($row['barcode'], ENT_QUOTES) ?></span>
                                    <button type="button" class="btn btn-sm btn-outline-secondary ms-1" title="Copy barcode" onclick="copyText('<?= htmlspecialchars($row['barcode'], ENT_QUOTES) ?>')">
                                        <i class="far fa-copy"></i>
                                    </button>
                                <?php else: ?>
                                    <span class="text-muted">N/A</span>
                                <?php endif; ?>
                            </td>
                            <td><?= htmlspecialchars($row['name']) ?></td>
                            <td><?= htmlspecialchars($row['category']) ?></td>
                            <td>Rs <?= number_format($wholesale, 2) ?></td>
                            <td>Rs <?= number_format($selling, 2) ?></td>
                            <td><?= $half_price > 0 ? 'Rs ' . number_format($half_price, 2) : '<span class="text-muted">N/A</span>' ?></td>
                            <td>
                                <span class="badge bg-<?= $profit_margin > 20 ? 'success' : ($profit_margin > 10 ? 'warning' : 'danger') ?>">
                                    <?= number_format($profit_margin, 1) ?>%
                                </span>
                            </td>
                            <td><?= $row['stock'] ?></td>
                            <td>
                                <button class="btn btn-sm btn-primary me-1" onclick="editProduct(<?= $row['id'] ?>, '<?= htmlspecialchars($row['name'], ENT_QUOTES) ?>', '<?= htmlspecialchars($row['category'], ENT_QUOTES) ?>', <?= $wholesale ?>, <?= $selling ?>, <?= $half_price ?>, <?= $row['stock'] ?>, '<?= htmlspecialchars($row['barcode'] ?? '', ENT_QUOTES) ?>')">
                                    <i class="fas fa-edit"></i>
                                </button>
                                <button class="btn btn-sm btn-danger" onclick="deleteProduct(<?= $row['id'] ?>, '<?= htmlspecialchars($row['name'], ENT_QUOTES) ?>')">
                                    <i class="fas fa-trash"></i>
                                </button>
                            </td>
                        </tr>
                        <?php endwhile; ?>
                    </tbody>
                </table>
            
            <?php if ($total_pages > 1): ?>
            <!-- Pagination -->
            <nav aria-label="Product pagination" class="mt-3">
                <ul class="pagination justify-content-center">
                    <?php if ($page > 1): ?>
                    <li class="page-item">
                        <a class="page-link" href="?page=<?= $page - 1 ?><?= $search_query ? '&q=' . urlencode($search_query) : '' ?>">&laquo; Previous</a>
                    </li>
                    <?php endif; ?>
                    
                    <?php 
                    $start = max(1, $page - 2);
                    $end = min($total_pages, $page + 2);
                    for ($i = $start; $i <= $end; $i++): 
                    ?>
                    <li class="page-item <?= $i == $page ? 'active' : '' ?>">
                        <a class="page-link" href="?page=<?= $i ?><?= $search_query ? '&q=' . urlencode($search_query) : '' ?>"><?= $i ?></a>
                    </li>
                    <?php endfor; ?>
                    
                    <?php if ($page < $total_pages): ?>
                    <li class="page-item">
                        <a class="page-link" href="?page=<?= $page + 1 ?><?= $search_query ? '&q=' . urlencode($search_query) : '' ?>">Next &raquo;</a>
                    </li>
                    <?php endif; ?>
                </ul>
                <p class="text-center text-muted small">Page <?= $page ?> of <?= $total_pages ?> (<?= $total_products ?> total products)</p>
            </nav>
            <?php endif; ?>
    </div>

    <!-- Add Category Modal -->
    <div class="modal fade" id="categoryModal" tabindex="-1">
        <div class="modal-dialog">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title">Add New Category</h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
                </div>
                <form method="post" enctype="multipart/form-data">
                    <div class="modal-body">
                        <div class="mb-3">
                            <label class="form-label">Category Name *</label>
                            <input type="text" name="new_category" class="form-control" required>
                        </div>
                        <div class="mb-3">
                            <label class="form-label">Category Image (optional)</label>
                            <input type="file" name="category_image" accept="image/*" class="form-control">
                            <small class="text-muted">JPG, PNG, GIF, or WEBP.</small>
                        </div>
                    </div>
                    <div class="modal-footer">
                        <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Cancel</button>
                        <button type="submit" class="btn btn-primary">Add Category</button>
                    </div>
                </form>
            </div>
        </div>
    </div>

    <!-- Edit Product Modal -->
    <div class="modal fade" id="editProductModal" tabindex="-1">
        <div class="modal-dialog modal-lg">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title">Edit Product</h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
                </div>
                <form method="post" id="editProductForm">
                    <input type="hidden" name="edit_product" value="1">
                    <input type="hidden" name="product_id" id="edit_product_id">
                    <div class="modal-body">
                        <div class="row g-3">
                            <div class="col-md-6">
                                <label class="form-label">Product Name *</label>
                                <input type="text" name="edit_name" id="edit_name" class="form-control" required>
                            </div>
                            <div class="col-md-6">
                                <label class="form-label">Category</label>
                                <select name="edit_category" id="edit_category" class="form-select">
                                    <option value="">Select...</option>
                                    <?php foreach ($categories as $cat): ?>
                                        <option value="<?= htmlspecialchars($cat['name']) ?>"><?= htmlspecialchars($cat['name']) ?></option>
                                    <?php endforeach; ?>
                                </select>
                            </div>
                            <div class="col-md-6">
                                <label class="form-label">Pricing Type</label>
                                <select name="edit_pricing_type" class="form-select" id="edit_pricing_type">
                                    <option value="Yards">Yards</option>
                                    <option value="Pack">Pack</option>
                                    <option value="Box">Box</option>
                                </select>
                            </div>
                            <div class="col-md-6">
                                <label class="form-label">Wholesale Price *</label>
                                <input type="number" step="0.01" name="edit_wholesale_price" id="edit_wholesale_price" class="form-control" required>
                            </div>
                            <div class="col-md-6">
                                <label class="form-label">Price *</label>
                                <input type="number" step="0.01" name="edit_price" id="edit_price" class="form-control" required>
                            </div>
                            <div class="col-md-6" style="display:none;">
                                <label class="form-label">Half Price</label>
                                <input type="number" step="0.01" name="edit_half_price" id="edit_half_price" class="form-control">
                            </div>
                            <div class="col-md-6">
                                <label class="form-label">Stock *</label>
                                <input type="number" name="edit_stock" id="edit_stock" class="form-control" required>
                            </div>
                            <div class="col-md-6">
                                <label class="form-label">Barcode</label>
                                <input type="text" name="edit_barcode" id="edit_barcode" class="form-control">
                            </div>
                        </div>
                    </div>
                    <div class="modal-footer">
                        <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Cancel</button>
                        <button type="submit" class="btn btn-primary">Update Product</button>
                    </div>
                </form>
            </div>
        </div>
    </div>

    <!-- Edit Category Modal -->
    <div class="modal fade" id="editCategoryModal" tabindex="-1">
        <div class="modal-dialog">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title">Edit Category</h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
                </div>
                <form method="post" id="editCategoryForm" enctype="multipart/form-data" action="products.php">
                    <input type="hidden" name="edit_category_id" id="edit_category_id">
                    <div class="modal-body">
                        <div class="mb-3">
                            <label class="form-label">Category Name *</label>
                            <input type="text" name="edit_category_name" id="edit_category_name" class="form-control" required>
                        </div>
                        <div class="mb-3">
                            <label class="form-label">Category Image (JPG, PNG, GIF, WEBP)</label>
                            <input type="file" name="edit_category_image" id="edit_category_image" accept="image/jpeg,image/png,image/gif,image/webp" class="form-control">
                            <small class="form-text text-muted">Select a new image to replace the current one</small>
                            <div class="mt-2" id="edit_category_current_wrap" style="display:none;">
                                <small class="text-muted d-block">Current image:</small>
                                <img id="edit_category_current" src="" alt="" style="width:80px;height:80px;object-fit:cover;border-radius:8px;border:1px solid #dee2e6;">
                            </div>
                        </div>
                    </div>
                    <div class="modal-footer">
                        <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Cancel</button>
                        <button type="submit" class="btn btn-primary">Update Category</button>
                    </div>
                </form>
            </div>
        </div>
    </div>

    <!-- Delete Forms (hidden) -->
    <form method="post" id="deleteProductForm" style="display: none;">
        <input type="hidden" name="delete_product" value="1">
        <input type="hidden" name="product_id" id="delete_product_id">
    </form>

    <form method="post" id="deleteCategoryForm" style="display: none;">
        <input type="hidden" name="delete_category" value="1">
        <input type="hidden" name="category_id" id="delete_category_id">
    </form>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
    <script>
        function handlePricingType() {
            const pricingType = document.getElementById('pricingType').value;
            const halfPriceField = document.getElementById('halfPrice');
            const fullPriceField = document.getElementById('fullPrice');
            
            if (pricingType === 'single') {
                halfPriceField.value = '';
                halfPriceField.readOnly = true;
                halfPriceField.placeholder = 'N/A - Single price only';
            } else if (pricingType === 'dual') {
                halfPriceField.readOnly = true;
                halfPriceField.placeholder = 'Auto-calculated (50%)';
                calculateHalfPrice();
            } else if (pricingType === 'custom') {
                halfPriceField.readOnly = false;
                halfPriceField.placeholder = 'Enter custom half price';
            }
        }

        function calculateHalfPrice() {
            const pricingType = document.getElementById('pricingType').value;
            if (pricingType === 'dual') {
                const fullPrice = document.getElementById('fullPrice').value;
                const halfPrice = fullPrice / 2;
                document.getElementById('halfPrice').value = halfPrice.toFixed(2);
            }
        }

        function handleEditPricingType() {
            const pricingType = document.getElementById('edit_pricing_type').value;
            const halfPriceField = document.getElementById('edit_half_price');
            
            if (pricingType === 'single') {
                halfPriceField.value = '';
                halfPriceField.readOnly = true;
                halfPriceField.placeholder = 'N/A - Single price only';
            } else if (pricingType === 'dual') {
                halfPriceField.readOnly = true;
                halfPriceField.placeholder = 'Auto-calculated (50%)';
                calculateEditHalfPrice();
            } else if (pricingType === 'custom') {
                halfPriceField.readOnly = false;
                halfPriceField.placeholder = 'Enter custom half price';
            }
        }

        function calculateEditHalfPrice() {
            const pricingType = document.getElementById('edit_pricing_type').value;
            if (pricingType === 'dual') {
                const fullPrice = document.getElementById('edit_price').value;
                const halfPrice = fullPrice / 2;
                document.getElementById('edit_half_price').value = halfPrice.toFixed(2);
            }
        }

        function editProduct(id, name, category, wholesale, selling, halfPrice, stock, barcode) {
            document.getElementById('edit_product_id').value = id;
            document.getElementById('edit_name').value = name;
            document.getElementById('edit_category').value = category;
            document.getElementById('edit_wholesale_price').value = wholesale;
            document.getElementById('edit_price').value = selling;
            document.getElementById('edit_half_price').value = halfPrice;
            document.getElementById('edit_stock').value = stock;
            document.getElementById('edit_barcode').value = barcode;
            
            // Determine pricing type based on existing values
            const pricingTypeSelect = document.getElementById('edit_pricing_type');
            if (halfPrice == 0) {
                pricingTypeSelect.value = 'single';
            } else if (Math.abs(halfPrice - (selling / 2)) < 0.01) {
                pricingTypeSelect.value = 'dual';
            } else {
                pricingTypeSelect.value = 'custom';
            }
            handleEditPricingType();
            
            new bootstrap.Modal(document.getElementById('editProductModal')).show();
        }

        function deleteProduct(id, name) {
            // Validate inputs
            if (!id || id <= 0) {
                alert('Invalid product ID: ' + id);
                return;
            }
            
            if (!name) {
                name = 'Unknown Product';
            }
            
            // Enhanced confirmation with warning about related data
            const confirmMessage = `Are you sure you want to delete "${name}"?\n\n` +
                                 `⚠️ WARNING: This will also delete:\n` +
                                 `• All inventory transaction history\n` +
                                 `• All sale records for this product\n\n` +
                                 `This action cannot be undone!`;
            
            if (confirm(confirmMessage)) {
                try {
                    // Set the product ID
                    const productIdField = document.getElementById('delete_product_id');
                    if (!productIdField) {
                        alert('Error: Delete form not found. Please refresh the page.');
                        return;
                    }
                    
                    productIdField.value = id;
                    
                    // Get the form
                    const deleteForm = document.getElementById('deleteProductForm');
                    if (!deleteForm) {
                        alert('Error: Delete form not found. Please refresh the page.');
                        return;
                    }
                    
                    // Show loading state
                    const button = event.target;
                    const originalText = button.innerHTML;
                    button.innerHTML = '<i class="fas fa-spinner fa-spin"></i> Deleting...';
                    button.disabled = true;
                    
                    // Submit the form
                    deleteForm.submit();
                    
                } catch (error) {
                    alert('Error preparing delete: ' + error.message);
                    console.error('Delete product error:', error);
                }
            }
        }

        function deleteCategory(id, name) {
            if (confirm('Are you sure you want to delete category "' + name + '"?\n\nNote: This may affect products using this category.')) {
                document.getElementById('delete_category_id').value = id;
                document.getElementById('deleteCategoryForm').submit();
            }
        }

        function openEditCategoryModal(id, name, image) {
            document.getElementById('edit_category_id').value = id;
            document.getElementById('edit_category_name').value = name;
            const wrap = document.getElementById('edit_category_current_wrap');
            const img = document.getElementById('edit_category_current');
            if (image) {
                img.src = image;
                wrap.style.display = '';
            } else {
                wrap.style.display = 'none';
                img.src = '';
            }
            var modal = new bootstrap.Modal(document.getElementById('editCategoryModal'));
            modal.show();
        }

        // Submit form normally to handle file uploads properly
        // Fetch API doesn't handle multipart/form-data file uploads well
        document.getElementById('editCategoryForm').onsubmit = function(e) {
            // Allow normal form submission
            return true;
        };

        // Copy helper for barcode and similar short fields
        function copyText(text) {
            if (!text) return;
            if (navigator.clipboard && navigator.clipboard.writeText) {
                navigator.clipboard.writeText(text).then(() => {
                    // Optional: brief visual feedback
                }).catch(() => {
                    legacyCopy(text);
                });
            } else {
                legacyCopy(text);
            }
        }

        function legacyCopy(text) {
            try {
                const ta = document.createElement('textarea');
                ta.value = text;
                ta.setAttribute('readonly', '');
                ta.style.position = 'absolute';
                ta.style.left = '-9999px';
                document.body.appendChild(ta);
                ta.select();
                document.execCommand('copy');
                document.body.removeChild(ta);
            } catch (e) {
                console.warn('Copy failed:', e);
            }
        }
    </script>
</body>
</html>
