Complete Product Management System

<?php
// ========================================
// DATABASE CONNECTION CLASS
// ========================================

class Database {
    private static $instance = null;
    private $pdo;
    
    private function __construct() {
        $config = [
            'host' => 'localhost',
            'dbname' => 'shop',
            'username' => 'root',
            'password' => '',
            'charset' => 'utf8mb4'
        ];
        
        try {
            $dsn = "mysql:host={$config['host']};dbname={$config['dbname']};charset={$config['charset']}";
            
            $this->pdo = new PDO($dsn, $config['username'], $config['password'], [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false
            ]);
        } catch (PDOException $e) {
            die("Connection failed: " . $e->getMessage());
        }
    }
    
    public static function getInstance() {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }
    
    public function getConnection() {
        return $this->pdo;
    }
}

// ========================================
// PRODUCT MODEL CLASS
// ========================================

class Product {
    private $db;
    private $table = 'products';
    
    public function __construct() {
        $this->db = Database::getInstance()->getConnection();
    }
    
    /**
     * CREATE - Add new product
     */
    public function create($data) {
        try {
            // Validate required fields
            $required = ['name', 'price', 'category_id'];
            foreach ($required as $field) {
                if (empty($data[$field])) {
                    throw new Exception("Field '$field' is required");
                }
            }
            
            // Sanitize data
            $data = $this->sanitizeData($data);
            
            // Prepare SQL
            $sql = "INSERT INTO {$this->table} 
                    (name, description, price, category_id, stock_quantity, sku, created_at) 
                    VALUES 
                    (:name, :description, :price, :category_id, :stock_quantity, :sku, NOW())";
            
            $stmt = $this->db->prepare($sql);
            
            // Bind parameters
            $stmt->bindParam(':name', $data['name']);
            $stmt->bindParam(':description', $data['description']);
            $stmt->bindParam(':price', $data['price']);
            $stmt->bindParam(':category_id', $data['category_id']);
            $stmt->bindParam(':stock_quantity', $data['stock_quantity']);
            $stmt->bindParam(':sku', $data['sku']);
            
            // Execute
            if ($stmt->execute()) {
                return [
                    'success' => true,
                    'id' => $this->db->lastInsertId(),
                    'message' => 'Product created successfully'
                ];
            }
            
            return ['success' => false, 'message' => 'Failed to create product'];
            
        } catch (Exception $e) {
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }
    
    /**
     * READ - Get products with pagination and filtering
     */
    public function read($params = []) {
        try {
            // Base query
            $sql = "SELECT p.*, c.name as category_name 
                    FROM {$this->table} p 
                    LEFT JOIN categories c ON p.category_id = c.id 
                    WHERE 1=1";
            
            $bindings = [];
            
            // Add search filter
            if (!empty($params['search'])) {
                $sql .= " AND (p.name LIKE :search OR p.description LIKE :search)";
                $bindings[':search'] = '%' . $params['search'] . '%';
            }
            
            // Add category filter
            if (!empty($params['category'])) {
                $sql .= " AND p.category_id = :category";
                $bindings[':category'] = $params['category'];
            }
            
            // Add price range filter
            if (!empty($params['min_price'])) {
                $sql .= " AND p.price >= :min_price";
                $bindings[':min_price'] = $params['min_price'];
            }
            
            if (!empty($params['max_price'])) {
                $sql .= " AND p.price <= :max_price";
                $bindings[':max_price'] = $params['max_price'];
            }
            
            // Count total records
            $countSql = "SELECT COUNT(*) as total FROM ({$sql}) as count_table";
            $countStmt = $this->db->prepare($countSql);
            $countStmt->execute($bindings);
            $total = $countStmt->fetch()['total'];
            
            // Add sorting
            $sortColumn = $params['sort'] ?? 'name';
            $sortOrder = $params['order'] ?? 'ASC';
            $allowedColumns = ['name', 'price', 'stock_quantity', 'created_at'];
            
            if (in_array($sortColumn, $allowedColumns)) {
                $sql .= " ORDER BY p.{$sortColumn} {$sortOrder}";
            }
            
            // Add pagination
            $page = max(1, $params['page'] ?? 1);
            $perPage = $params['per_page'] ?? 10;
            $offset = ($page - 1) * $perPage;
            
            $sql .= " LIMIT :limit OFFSET :offset";
            
            // Prepare and execute
            $stmt = $this->db->prepare($sql);
            foreach ($bindings as $key => $value) {
                $stmt->bindValue($key, $value);
            }
            $stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
            $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
            
            $stmt->execute();
            
            return [
                'success' => true,
                'data' => $stmt->fetchAll(),
                'pagination' => [
                    'total' => $total,
                    'per_page' => $perPage,
                    'current_page' => $page,
                    'total_pages' => ceil($total / $perPage)
                ]
            ];
            
        } catch (Exception $e) {
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }
    
    /**
     * READ - Get single product
     */
    public function findById($id) {
        try {
            $sql = "SELECT p.*, c.name as category_name 
                    FROM {$this->table} p 
                    LEFT JOIN categories c ON p.category_id = c.id 
                    WHERE p.id = :id";
            
            $stmt = $this->db->prepare($sql);
            $stmt->execute([':id' => $id]);
            
            $product = $stmt->fetch();
            
            if ($product) {
                return ['success' => true, 'data' => $product];
            }
            
            return ['success' => false, 'message' => 'Product not found'];
            
        } catch (Exception $e) {
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }
    
    /**
     * UPDATE - Update product
     */
    public function update($id, $data) {
        try {
            // Check if product exists
            $existing = $this->findById($id);
            if (!$existing['success']) {
                return ['success' => false, 'message' => 'Product not found'];
            }
            
            // Sanitize data
            $data = $this->sanitizeData($data);
            
            // Build update query dynamically
            $fields = [];
            $bindings = [':id' => $id];
            
            foreach (['name', 'description', 'price', 'category_id', 'stock_quantity', 'sku'] as $field) {
                if (isset($data[$field])) {
                    $fields[] = "{$field} = :{$field}";
                    $bindings[":{$field}"] = $data[$field];
                }
            }
            
            if (empty($fields)) {
                return ['success' => false, 'message' => 'No fields to update'];
            }
            
            // Add updated_at
            $fields[] = "updated_at = NOW()";
            
            $sql = "UPDATE {$this->table} SET " . implode(', ', $fields) . " WHERE id = :id";
            
            $stmt = $this->db->prepare($sql);
            
            if ($stmt->execute($bindings)) {
                return [
                    'success' => true,
                    'message' => 'Product updated successfully',
                    'affected_rows' => $stmt->rowCount()
                ];
            }
            
            return ['success' => false, 'message' => 'Failed to update product'];
            
        } catch (Exception $e) {
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }
    
    /**
     * DELETE - Delete product (with soft delete option)
     */
    public function delete($id, $soft = true) {
        try {
            if ($soft) {
                // Soft delete - mark as deleted
                $sql = "UPDATE {$this->table} 
                        SET deleted_at = NOW() 
                        WHERE id = :id AND deleted_at IS NULL";
            } else {
                // Hard delete - remove from database
                $sql = "DELETE FROM {$this->table} WHERE id = :id";
            }
            
            $stmt = $this->db->prepare($sql);
            $stmt->execute([':id' => $id]);
            
            if ($stmt->rowCount() > 0) {
                return [
                    'success' => true,
                    'message' => 'Product deleted successfully'
                ];
            }
            
            return ['success' => false, 'message' => 'Product not found or already deleted'];
            
        } catch (Exception $e) {
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }
    
    /**
     * Bulk operations
     */
    public function bulkDelete($ids) {
        try {
            $placeholders = str_repeat('?,', count($ids) - 1) . '?';
            $sql = "UPDATE {$this->table} 
                    SET deleted_at = NOW() 
                    WHERE id IN ($placeholders)";
            
            $stmt = $this->db->prepare($sql);
            $stmt->execute($ids);
            
            return [
                'success' => true,
                'message' => $stmt->rowCount() . ' products deleted'
            ];
            
        } catch (Exception $e) {
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }
    
    /**
     * Data sanitization
     */
    private function sanitizeData($data) {
        $sanitized = [];
        
        foreach ($data as $key => $value) {
            switch ($key) {
                case 'name':
                case 'description':
                case 'sku':
                    $sanitized[$key] = htmlspecialchars(trim($value), ENT_QUOTES, 'UTF-8');
                    break;
                    
                case 'price':
                    $sanitized[$key] = filter_var($value, FILTER_VALIDATE_FLOAT);
                    break;
                    
                case 'category_id':
                case 'stock_quantity':
                    $sanitized[$key] = filter_var($value, FILTER_VALIDATE_INT);
                    break;
                    
                default:
                    $sanitized[$key] = $value;
            }
        }
        
        return $sanitized;
    }
}