<?php
require_once __DIR__ . '/../config/Database.php';

class Product {
    private $db;
    private $conn;
    
    public function __construct() {
        $this->db = new Database();
        $this->conn = $this->db->getConnection();
    }
    
    public function getAllProducts($categoryId = null) {
        $query = "SELECT p.*, c.name as category FROM products p 
                  LEFT JOIN categories c ON p.category_id = c.id 
                  WHERE p.status = 'active'";
        
        if ($categoryId) {
            $query .= " AND p.category_id = " . intval($categoryId);
        }
        $query .= " ORDER BY p.name";
        
        $result = $this->conn->query($query);
        $products = $result->fetch_all(MYSQLI_ASSOC);
        
        // Fetch variations for each product
        foreach ($products as &$product) {
            $product['variations'] = $this->getProductVariations($product['id']);
        }
        
        return $products;
    }
    
    public function getProduct($productId) {
        $query = "SELECT p.*, c.name as category FROM products p 
                  LEFT JOIN categories c ON p.category_id = c.id 
                  WHERE p.id = ?";
        $stmt = $this->conn->prepare($query);
        $stmt->bind_param("i", $productId);
        $stmt->execute();
        
        $product = $stmt->get_result()->fetch_assoc();
        if ($product) {
            $product['variations'] = $this->getProductVariations($productId);
        }
        
        return $product;
    }
    
    public function getProductVariations($productId) {
        $query = "SELECT * FROM products_variations WHERE product_id = ? ORDER BY id";
        $stmt = $this->conn->prepare($query);
        $stmt->bind_param("i", $productId);
        $stmt->execute();
        return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    }
    
    public function getProductByBarcode($barcode) {
        $query = "SELECT pv.*, p.*, c.name as category FROM products_variations pv 
                  LEFT JOIN products p ON pv.product_id = p.id 
                  LEFT JOIN categories c ON p.category_id = c.id 
                  WHERE pv.barcode = ?";
        $stmt = $this->conn->prepare($query);
        $stmt->bind_param("s", $barcode);
        $stmt->execute();
        return $stmt->get_result()->fetch_assoc();
    }
    
    public function createProduct($data) {
        $this->conn->begin_transaction();
        try {
            // 1. Handle category (get ID by name or create)
            $categoryId = $this->getOrCreateCategoryId($data['category']);
            
            // 2. Insert product
            $query = "INSERT INTO products (name, category_id, brand, cost, price, image_url, description) 
                      VALUES (?, ?, ?, ?, ?, ?, ?)";
            $stmt = $this->conn->prepare($query);
            $stmt->bind_param("sisddss", $data['name'], $categoryId, $data['brand'], 
                              $data['cost'], $data['price'], $data['image'], $data['description']);
            
            if (!$stmt->execute()) {
                throw new Exception("Failed to insert product: " . $stmt->error);
            }
            
            $productId = $this->conn->insert_id;
            
            // 3. Insert variations
            if (isset($data['variations']) && is_array($data['variations'])) {
                foreach ($data['variations'] as $v) {
                    $this->createVariation($productId, $v);
                }
            }
            
            $this->conn->commit();
            return true;
        } catch (Exception $e) {
            $this->conn->rollback();
            error_log("Product creation failed: " . $e->getMessage());
            return false;
        }
    }
    
    public function updateProduct($productId, $data) {
        $this->conn->begin_transaction();
        try {
            // 1. Handle category
            $categoryId = $this->getOrCreateCategoryId($data['category']);
            
            // 2. Update product
            $query = "UPDATE products SET name=?, category_id=?, brand=?, cost=?, price=?, image_url=?, description=? WHERE id=?";
            $stmt = $this->conn->prepare($query);
            $stmt->bind_param("sisddssi", $data['name'], $categoryId, $data['brand'], 
                              $data['cost'], $data['price'], $data['image'], $data['description'], $productId);
            
            if (!$stmt->execute()) {
                throw new Exception("Failed to update product: " . $stmt->error);
            }
            
            // 3. Update variations
            // This is a simplified approach: delete existing and re-insert 
            // Better to match by ID but for now this ensures consistency with the frontend state
            $delQuery = "DELETE FROM products_variations WHERE product_id = ?";
            $delStmt = $this->conn->prepare($delQuery);
            $delStmt->bind_param("i", $productId);
            $delStmt->execute();
            
            if (isset($data['variations']) && is_array($data['variations'])) {
                foreach ($data['variations'] as $v) {
                    $this->createVariation($productId, $v);
                }
            }
            
            $this->conn->commit();
            return true;
        } catch (Exception $e) {
            $this->conn->rollback();
            error_log("Product update failed: " . $e->getMessage());
            return false;
        }
    }
    
    private function getOrCreateCategoryId($categoryName) {
        $query = "SELECT id FROM categories WHERE name = ?";
        $stmt = $this->conn->prepare($query);
        $stmt->bind_param("s", $categoryName);
        $stmt->execute();
        $res = $stmt->get_result()->fetch_assoc();
        
        if ($res) {
            return $res['id'];
        }
        
        // Create new category
        $query = "INSERT INTO categories (name) VALUES (?)";
        $stmt = $this->conn->prepare($query);
        $stmt->bind_param("s", $categoryName);
        $stmt->execute();
        return $this->conn->insert_id;
    }
    
    private function createVariation($productId, $v) {
        $query = "INSERT INTO products_variations (id, product_id, size, color, stock, sku, barcode, low_stock_threshold) 
                  VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        $stmt = $this->conn->prepare($query);
        $stmt->bind_param("sississi", $v['id'], $productId, $v['size'], $v['color'], 
                          $v['stock'], $v['sku'], $v['barcode'], $v['lowStockThreshold']);
        if (!$stmt->execute()) {
            throw new Exception("Failed to insert variation: " . $stmt->error);
        }
    }
    
    public function updateVariationStock($variationId, $newStock) {
        $query = "UPDATE products_variations SET stock = ? WHERE id = ?";
        $stmt = $this->conn->prepare($query);
        $stmt->bind_param("is", $newStock, $variationId);
        return $stmt->execute();
    }
    
    public function getLowStockProducts($threshold = null) {
        $query = "SELECT p.*, pv.stock, pv.low_stock_threshold, c.name as category 
                  FROM products p 
                  LEFT JOIN products_variations pv ON p.id = pv.product_id 
                  LEFT JOIN categories c ON p.category_id = c.id 
                  WHERE pv.stock < pv.low_stock_threshold 
                  ORDER BY pv.stock ASC";
        
        $result = $this->conn->query($query);
        return $result->fetch_all(MYSQLI_ASSOC);
    }
}
?>
