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

class Category {
    private $db;

    public function __construct() {
        $database = new Database();
        $this->db = $database->getConnection();
    }

    public function getAllCategories($limit = 12, $offset = 0) {
        $sql = "SELECT c.*, COUNT(g.id) as game_count 
                FROM categories c 
                LEFT JOIN games g ON c.id = g.category_id 
                GROUP BY c.id 
                ORDER BY c.sort_order IS NULL, c.sort_order ASC, RAND() 
                LIMIT :limit OFFSET :offset";
        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
        $stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();
        
        return $stmt->fetchAll();
    }
    
    public function getTotalCategories() {
        $sql = "SELECT COUNT(*) as total FROM categories";
        $stmt = $this->db->prepare($sql);
        $stmt->execute();
        $result = $stmt->fetch();
        
        return $result['total'];
    }

    public function getPopularCategories($limit = 10) {
        $sql = "SELECT c.*, COUNT(g.id) as game_count 
                FROM categories c 
                LEFT JOIN games g ON c.id = g.category_id 
                GROUP BY c.id 
                ORDER BY game_count DESC 
                LIMIT :limit";
        
        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();
        
        return $stmt->fetchAll();
    }

    public function getCategoryBySlug($slug) {
        $sql = "SELECT * FROM categories WHERE slug = :slug";
        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':slug', $slug);
        $stmt->execute();
        
        return $stmt->fetch();
    }

    public function getAllCategoriesSimple() {
        $sql = "SELECT id, name FROM categories ORDER BY name ASC";
        $stmt = $this->db->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll();
    }
    
    // Admin methods
    
    public function getCategoryById($id) {
        $sql = "SELECT * FROM categories WHERE id = :id";
        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':id', $id, PDO::PARAM_INT);
        $stmt->execute();
        
        return $stmt->fetch();
    }
    
    public function getAllCategoriesForAdmin($limit = null, $offset = null) {
        $sql = "SELECT c.*, COUNT(g.id) as game_count 
                FROM categories c 
                LEFT JOIN games g ON c.id = g.category_id 
                GROUP BY c.id 
                ORDER BY c.sort_order IS NULL, c.sort_order ASC, c.name ASC";
        
        // Add pagination if limit is provided
        if ($limit !== null) {
            $sql .= " LIMIT :limit";
            if ($offset !== null) {
                $sql .= " OFFSET :offset";
            }
        }
        
        $stmt = $this->db->prepare($sql);
        
        // Bind pagination parameters if provided
        if ($limit !== null) {
            $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
            if ($offset !== null) {
                $stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
            }
        }
        
        $stmt->execute();
        
        return $stmt->fetchAll();
    }
    
    public function addCategory($name, $slug = null, $sort_order = null) {
        // Generate slug if not provided
        if (empty($slug)) {
            $slug = $this->generateSlug($name);
        }
        
        $sql = "INSERT INTO categories (name, slug, sort_order) VALUES (:name, :slug, :sort_order)";
        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':name', $name);
        $stmt->bindParam(':slug', $slug);
        if ($sort_order === null) {
            $stmt->bindValue(':sort_order', null, PDO::PARAM_NULL);
        } else {
            $stmt->bindParam(':sort_order', $sort_order, PDO::PARAM_INT);
        }
        
        return $stmt->execute();
    }
    
    public function updateCategory($id, $name, $slug = null, $sort_order = null) {
        // Generate slug if not provided
        if (empty($slug)) {
            $slug = $this->generateSlug($name);
        }
        
        $sql = "UPDATE categories SET name = :name, slug = :slug, sort_order = :sort_order WHERE id = :id";
        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':id', $id, PDO::PARAM_INT);
        $stmt->bindParam(':name', $name);
        $stmt->bindParam(':slug', $slug);
        if ($sort_order === null) {
            $stmt->bindValue(':sort_order', null, PDO::PARAM_NULL);
        } else {
            $stmt->bindParam(':sort_order', $sort_order, PDO::PARAM_INT);
        }
        
        return $stmt->execute();
    }
    
    public function deleteCategory($id) {
        // First, update any games in this category to have no category
        $sql = "UPDATE games SET category_id = NULL WHERE category_id = :id";
        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':id', $id, PDO::PARAM_INT);
        $stmt->execute();
        
        // Then delete the category
        $sql = "DELETE FROM categories WHERE id = :id";
        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':id', $id, PDO::PARAM_INT);
        
        return $stmt->execute();
    }
    
    private function generateSlug($name) {
        // Convert to lowercase and replace spaces with hyphens
        $slug = strtolower(trim($name));
        $slug = preg_replace('/[^a-z0-9-]/', '-', $slug);
        $slug = preg_replace('/-+/', '-', $slug);
        $slug = trim($slug, '-');
        
        // Check if slug already exists
        $sql = "SELECT COUNT(*) FROM categories WHERE slug = :slug";
        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':slug', $slug);
        $stmt->execute();
        $count = $stmt->fetchColumn();
        
        // If slug exists, append a number
        if ($count > 0) {
            $i = 1;
            $newSlug = $slug . '-' . $i;
            
            while (true) {
                $sql = "SELECT COUNT(*) FROM categories WHERE slug = :slug";
                $stmt = $this->db->prepare($sql);
                $stmt->bindParam(':slug', $newSlug);
                $stmt->execute();
                $count = $stmt->fetchColumn();
                
                if ($count == 0) {
                    $slug = $newSlug;
                    break;
                }
                
                $i++;
                $newSlug = $slug . '-' . $i;
            }
        }
        
        return $slug;
    }

    public function updateSortOrder($categoryId, $sortOrder) {
        $sql = "UPDATE categories SET sort_order = :sort_order WHERE id = :id";
        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':sort_order', $sortOrder, PDO::PARAM_INT);
        $stmt->bindParam(':id', $categoryId, PDO::PARAM_INT);
        return $stmt->execute();
    }

    public function updateMultipleSortOrders($sortOrders) {
        $this->db->beginTransaction();
        try {
            foreach ($sortOrders as $categoryId => $sortOrder) {
                $this->updateSortOrder($categoryId, $sortOrder);
            }
            $this->db->commit();
            return true;
        } catch (Exception $e) {
            $this->db->rollback();
            return false;
        }
    }
}
