<?php
// app/Models/Category.php - Category Model

class Category {
    private $db;
    
    public function __construct() {
        $this->db = Database::getInstance();
    }
    
    /**
     * Create category
     */
    public function create($data) {
    $sql = "INSERT INTO categories (name, slug, description, icon, image, parent_id, status, display_order, commission_percentage, level, created_at) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())";
    
        $result = $this->db->execute($sql, [
            $data['name'],
            $data['slug'],
            $data['description'] ?? null,
            $data['icon'] ?? null,
            $data['image'] ?? null,
            $data['parent_id'] ?? null,
            $data['status'] ?? 'active',
            $data['display_order'] ?? 0,
            $data['commission_percentage'] ?? 10.00,
            $data['level'] ?? 'medium'
        ]);
        
        return $result ? $this->db->lastInsertId() : false;
    }
    
    /**
     * Find category by ID
     */
    public function find($id) {
        $sql = "SELECT c.*, 
        (SELECT name FROM categories WHERE id = c.parent_id) as parent_name,
        (SELECT COUNT(*) FROM items WHERE category_id = c.id) as total_items
        FROM categories c
        WHERE c.id = ? LIMIT 1";
        return $this->db->fetch($sql, [$id]);
    }

    
    /**
     * Find category by slug
     */
    public function findBySlug($slug) {
        $sql = "SELECT c.*, 
                (SELECT name FROM categories WHERE id = c.parent_id) as parent_name,
                (SELECT COUNT(*) FROM items WHERE category_id = c.id AND approval_status = 'approved') as total_items
                FROM categories c
                WHERE c.slug = ? LIMIT 1";
        return $this->db->fetch($sql, [$slug]);
    }

    
    /**
     * Get all categories
     */
    public function getAll($filters = []) {
        $sql = "SELECT c.*, 
            (SELECT name FROM categories WHERE id = c.parent_id) as parent_name,
            (SELECT COUNT(*) FROM items WHERE category_id = c.id) as total_items,
            (SELECT COUNT(*) FROM items WHERE category_id = c.id AND approval_status = 'approved') as approved_items
            FROM categories c
            WHERE 1=1";
        
        $params = [];
        
        if (isset($filters['status'])) {
            $sql .= " AND c.status = ?";
            $params[] = $filters['status'];
        }
        
        if (isset($filters['parent_id'])) {
            if ($filters['parent_id'] === 'null') {
                $sql .= " AND c.parent_id IS NULL";
            } else {
                $sql .= " AND c.parent_id = ?";
                $params[] = $filters['parent_id'];
            }
        }
        
        if (!empty($filters['search'])) {
            $sql .= " AND (c.name LIKE ? OR c.description LIKE ?)";
            $search = '%' . $filters['search'] . '%';
            $params[] = $search;
            $params[] = $search;
        }
        
        $sql .= " ORDER BY c.display_order ASC, c.name ASC";
        
        return $this->db->fetchAll($sql, $params);
    }
    
    /**
     * Get active categories
     */
    // public function getActive() {
    //     $sql = "SELECT c.*, 
    //             (SELECT COUNT(*) FROM items WHERE category_id = c.id AND approval_status = 'approved') as total_items
    //             FROM categories c
    //             WHERE c.status = 'active'
    //             ORDER BY c.display_order ASC, c.name ASC";
        
    //     return $this->db->fetchAll($sql);
    // }
    public function getActive($limit = null) {
        $sql = "SELECT c.*, 
                (SELECT COUNT(*) FROM items WHERE category_id = c.id AND approval_status = 'approved' AND status = 'approved') as total_items
                FROM categories c
                WHERE c.status = 'active'
                ORDER BY c.display_order ASC, c.name ASC";
        
        if ($limit !== null) {
            $sql .= " LIMIT ?";
            return $this->db->fetchAll($sql, [(int)$limit]);
        }
        
        return $this->db->fetchAll($sql);
    }
    
    /**
     * Get parent categories
     */
    public function getParents() {
        $sql = "SELECT c.*, 
                (SELECT COUNT(*) FROM categories WHERE parent_id = c.id) as subcategories_count,
                (SELECT COUNT(*) FROM items WHERE category_id = c.id AND approval_status = 'approved') as total_items
                FROM categories c
                WHERE c.parent_id IS NULL AND c.status = 'active'
                ORDER BY c.display_order ASC, c.name ASC";
        
        return $this->db->fetchAll($sql);
    }
    
    /**
     * Get subcategories
     */
    public function getSubcategories($parent_id) {
        $sql = "SELECT c.*, 
                (SELECT COUNT(*) FROM items WHERE category_id = c.id AND approval_status = 'approved') as total_items
                FROM categories c
                WHERE c.parent_id = ? AND c.status = 'active'
                ORDER BY c.display_order ASC, c.name ASC";
        
        return $this->db->fetchAll($sql, [$parent_id]);
    }
    
    /**
     * Update category
     */
    public function update($id, $data) {
        $fields = [];
        $params = [];
        
        foreach ($data as $key => $value) {
            $fields[] = "{$key} = ?";
            $params[] = $value;
        }
        
        $params[] = $id;
        
        $sql = "UPDATE categories SET " . implode(', ', $fields) . ", updated_at = NOW() WHERE id = ?";
        return $this->db->execute($sql, $params);
    }
    
    /**
     * Update status
     */
    public function updateStatus($id, $status) {
        $sql = "UPDATE categories SET status = ?, updated_at = NOW() WHERE id = ?";
        return $this->db->execute($sql, [$status, $id]);
    }
    
    /**
     * Delete category
     */
    public function delete($id) {
        // Check if category has items
        $check_sql = "SELECT COUNT(*) as count FROM items WHERE category_id = ?";
        $result = $this->db->fetch($check_sql, [$id]);
        
        if ($result['count'] > 0) {
            return false; // Cannot delete category with items
        }
        
        // Check if category has subcategories
        $check_sub_sql = "SELECT COUNT(*) as count FROM categories WHERE parent_id = ?";
        $result = $this->db->fetch($check_sub_sql, [$id]);
        
        if ($result['count'] > 0) {
            return false; // Cannot delete category with subcategories
        }
        
        $sql = "DELETE FROM categories WHERE id = ?";
        return $this->db->execute($sql, [$id]);
    }
    
    /**
     * Check if slug exists
     */
    public function slugExists($slug, $exclude_id = null) {
        if ($exclude_id) {
            $sql = "SELECT id FROM categories WHERE slug = ? AND id != ? LIMIT 1";
            $result = $this->db->fetch($sql, [$slug, $exclude_id]);
        } else {
            $sql = "SELECT id FROM categories WHERE slug = ? LIMIT 1";
            $result = $this->db->fetch($sql, [$slug]);
        }
        
        return $result !== null;
    }
    
    /**
     * Get category statistics
     */
    public function getStats() {
        $sql = "SELECT 
                COUNT(*) as total_categories,
                SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_categories,
                SUM(CASE WHEN parent_id IS NULL THEN 1 ELSE 0 END) as parent_categories,
                (SELECT COUNT(*) FROM items) as total_items
                FROM categories";
        
        return $this->db->fetch($sql);
    }
    
    /**
     * Get popular categories
     */
    public function getPopular($limit = 10) {
        $sql = "SELECT c.*, 
                (SELECT COUNT(*) FROM items WHERE category_id = c.id AND approval_status = 'approved') as total_items,
                (SELECT COUNT(*) FROM orders o JOIN items i ON o.item_id = i.id WHERE i.category_id = c.id) as total_orders
                FROM categories c
                WHERE c.status = 'active'
                ORDER BY total_items DESC, total_orders DESC
                LIMIT ?";
        
        return $this->db->fetchAll($sql, [$limit]);
    }
    
    /**
     * Reorder categories
     */
    public function reorder($category_ids) {
        $this->db->beginTransaction();
        
        try {
            foreach ($category_ids as $order => $id) {
                $sql = "UPDATE categories SET display_order = ? WHERE id = ?";
                $this->db->execute($sql, [$order, $id]);
            }
            
            $this->db->commit();
            return true;
        } catch (Exception $e) {
            $this->db->rollback();
            return false;
        }
    }


    /**
     * Find category by ID
     */
    public function findById($id) {
        $sql = "SELECT c.*, 
                (SELECT name FROM categories WHERE id = c.parent_id) as parent_name,
                (SELECT COUNT(*) FROM items WHERE category_id = c.id AND approval_status = 'approved' AND status = 'approved') as total_items
                FROM categories c
                WHERE c.id = ? LIMIT 1";
        return $this->db->fetch($sql, [$id]);
    }
    
}