<?php

namespace App\Models;

use App\Models\BaseModel;

/**
 * Category Model
 * Handles hierarchical product categories with nested structure support
 */
class Category extends BaseModel
{
    protected string $table = 'categories';
    protected array $fillable = [
        'name', 'slug', 'parent_id', 'description', 'image',
        'sort_order', 'is_active', 'meta_title', 'meta_description'
    ];
    protected array $casts = [
        'parent_id' => 'integer',
        'sort_order' => 'integer',
        'is_active' => 'boolean'
    ];

    /**
     * Get category with children
     */
    public function getWithChildren(int $id): ?array
    {
        $category = $this->find($id);
        if (!$category) return null;

        $category['children'] = $this->getChildren($id);
        $category['parent'] = $this->getParent($category['parent_id']);
        $category['product_count'] = $this->getProductCount($id);

        return $category;
    }

    /**
     * Get child categories
     */
    public function getChildren(int $parentId): array
    {
        $children = $this->db->query(
            "SELECT * FROM categories
             WHERE parent_id = ? AND is_active = 1
             ORDER BY sort_order ASC, name ASC",
            [$parentId]
        );

        // Recursively get children for each child
        foreach ($children as &$child) {
            $child = $this->castAttributes($child);
            $child['children'] = $this->getChildren($child['id']);
            $child['product_count'] = $this->getProductCount($child['id']);
        }

        return $children;
    }

    /**
     * Get parent category
     */
    public function getParent(?int $parentId): ?array
    {
        if (!$parentId) return null;

        $parent = $this->find($parentId);
        return $parent ? $this->castAttributes($parent) : null;
    }

    /**
     * Get all root categories (no parent)
     */
    public function getRootCategories(): array
    {
        $categories = $this->db->query(
            "SELECT * FROM categories
             WHERE parent_id IS NULL AND is_active = 1
             ORDER BY sort_order ASC, name ASC"
        );

        foreach ($categories as &$category) {
            $category = $this->castAttributes($category);
            $category['children'] = $this->getChildren($category['id']);
            $category['product_count'] = $this->getProductCount($category['id'], true);
        }

        return $categories;
    }

    /**
     * Get category tree (hierarchical structure)
     */
    public function getCategoryTree(): array
    {
        return $this->getRootCategories();
    }

    /**
     * Get flat list of all categories
     */
    public function getFlatList(bool $activeOnly = true): array
    {
        $sql = "SELECT * FROM categories";
        $params = [];

        if ($activeOnly) {
            $sql .= " WHERE is_active = 1";
        }

        $sql .= " ORDER BY sort_order ASC, name ASC";

        $categories = $this->db->query($sql, $params);

        foreach ($categories as &$category) {
            $category = $this->castAttributes($category);
            $category['product_count'] = $this->getProductCount($category['id']);

            // Add breadcrumb path
            $category['breadcrumb'] = $this->getBreadcrumb($category['id']);
        }

        return $categories;
    }

    /**
     * Get category by slug
     */
    public function getBySlug(string $slug): ?array
    {
        $category = $this->db->queryOne(
            "SELECT * FROM categories WHERE slug = ? AND is_active = 1",
            [$slug]
        );

        if (!$category) return null;

        return $this->getWithChildren($category['id']);
    }

    /**
     * Get product count for category
     */
    public function getProductCount(int $categoryId, bool $includeChildren = false): int
    {
        if ($includeChildren) {
            // Get all descendant category IDs
            $categoryIds = $this->getAllDescendantIds($categoryId);
            $categoryIds[] = $categoryId;

            $placeholders = str_repeat('?,', count($categoryIds) - 1) . '?';

            $result = $this->db->queryOne(
                "SELECT COUNT(*) as count
                 FROM products
                 WHERE category_id IN ({$placeholders}) AND status = 'active'",
                $categoryIds
            );
        } else {
            $result = $this->db->queryOne(
                "SELECT COUNT(*) as count
                 FROM products
                 WHERE category_id = ? AND status = 'active'",
                [$categoryId]
            );
        }

        return (int)($result['count'] ?? 0);
    }

    /**
     * Get all descendant category IDs
     */
    public function getAllDescendantIds(int $categoryId): array
    {
        $descendants = [];
        $children = $this->db->query(
            "SELECT id FROM categories WHERE parent_id = ?",
            [$categoryId]
        );

        foreach ($children as $child) {
            $descendants[] = $child['id'];
            $descendants = array_merge($descendants, $this->getAllDescendantIds($child['id']));
        }

        return $descendants;
    }

    /**
     * Get breadcrumb path for category
     */
    public function getBreadcrumb(int $categoryId): array
    {
        $breadcrumb = [];
        $category = $this->find($categoryId);

        while ($category) {
            array_unshift($breadcrumb, [
                'id' => $category['id'],
                'name' => $category['name'],
                'slug' => $category['slug']
            ]);

            if ($category['parent_id']) {
                $category = $this->find($category['parent_id']);
            } else {
                break;
            }
        }

        return $breadcrumb;
    }

    /**
     * Create category with auto-generated slug
     */
    public function createCategory(array $data): int
    {
        // Generate slug if not provided
        if (empty($data['slug'])) {
            $data['slug'] = $this->generateSlug($data['name']);
        } else {
            $data['slug'] = $this->generateSlug($data['slug']);
        }

        // Set default sort order
        if (!isset($data['sort_order'])) {
            $data['sort_order'] = $this->getNextSortOrder($data['parent_id'] ?? null);
        }

        return $this->create($data);
    }

    /**
     * Update category
     */
    public function updateCategory(int $id, array $data): bool
    {
        // Generate new slug if name changed
        if (!empty($data['name']) && empty($data['slug'])) {
            $data['slug'] = $this->generateSlug($data['name'], $id);
        } elseif (!empty($data['slug'])) {
            $data['slug'] = $this->generateSlug($data['slug'], $id);
        }

        return $this->updateById($id, $data) > 0;
    }

    /**
     * Delete category (with children handling)
     */
    public function deleteCategory(int $id): bool
    {
        // Check if category has children
        $children = $this->db->query("SELECT id FROM categories WHERE parent_id = ?", [$id]);

        if (!empty($children)) {
            throw new \Exception("Cannot delete category with child categories");
        }

        // Check if category has products
        $productCount = $this->getProductCount($id);
        if ($productCount > 0) {
            throw new \Exception("Cannot delete category with products");
        }

        return $this->deleteById($id) > 0;
    }

    /**
     * Move category to new parent
     */
    public function moveCategory(int $categoryId, ?int $newParentId): bool
    {
        // Prevent moving category to its own descendant
        if ($newParentId && $this->isDescendant($categoryId, $newParentId)) {
            throw new \Exception("Cannot move category to its own descendant");
        }

        return $this->updateById($categoryId, ['parent_id' => $newParentId]) > 0;
    }

    /**
     * Check if target is descendant of source
     */
    private function isDescendant(int $sourceId, int $targetId): bool
    {
        $descendants = $this->getAllDescendantIds($sourceId);
        return in_array($targetId, $descendants);
    }

    /**
     * Reorder categories
     */
    public function reorderCategories(array $categoryOrders): bool
    {
        return $this->db->transaction(function() use ($categoryOrders) {
            foreach ($categoryOrders as $order) {
                $this->updateById($order['id'], ['sort_order' => $order['sort_order']]);
            }
            return true;
        });
    }

    /**
     * Generate unique slug
     */
    private function generateSlug(string $name, ?int $excludeId = null): string
    {
        $slug = strtolower(trim(preg_replace('/[^A-Za-z0-9-]+/', '-', $name)));
        $originalSlug = $slug;
        $counter = 1;

        do {
            $query = "SELECT id FROM categories WHERE slug = ?";
            $params = [$slug];

            if ($excludeId) {
                $query .= " AND id != ?";
                $params[] = $excludeId;
            }

            $exists = $this->db->queryOne($query, $params);

            if ($exists) {
                $slug = $originalSlug . '-' . $counter;
                $counter++;
            }
        } while ($exists);

        return $slug;
    }

    /**
     * Get next sort order for parent
     */
    private function getNextSortOrder(?int $parentId): int
    {
        $result = $this->db->queryOne(
            "SELECT MAX(sort_order) as max_order FROM categories WHERE parent_id " .
            ($parentId ? "= ?" : "IS NULL"),
            $parentId ? [$parentId] : []
        );

        return ((int)($result['max_order'] ?? 0)) + 1;
    }

    /**
     * Get categories for admin with statistics
     */
    public function getAdminCategories(): array
    {
        $categories = $this->db->query(
            "SELECT c.*,
                    COUNT(p.id) as product_count,
                    parent.name as parent_name
             FROM categories c
             LEFT JOIN products p ON c.id = p.category_id AND p.status = 'active'
             LEFT JOIN categories parent ON c.parent_id = parent.id
             GROUP BY c.id
             ORDER BY c.sort_order ASC, c.name ASC"
        );

        foreach ($categories as &$category) {
            $category = $this->castAttributes($category);
            $category['product_count'] = (int)$category['product_count'];

            // Get children count
            $childrenCount = $this->db->queryOne(
                "SELECT COUNT(*) as count FROM categories WHERE parent_id = ?",
                [$category['id']]
            );
            $category['children_count'] = (int)($childrenCount['count'] ?? 0);
        }

        return $categories;
    }

    /**
     * Get categories with optional filters
     */
    public function getCategories(array $filters = []): array
    {
        $conditions = [];
        $params = [];

        // Apply filters
        if (!empty($filters['search'])) {
            $conditions[] = "(c.name LIKE ? OR c.description LIKE ?)";
            $params[] = "%{$filters['search']}%";
            $params[] = "%{$filters['search']}%";
        }

        if (!empty($filters['status'])) {
            if ($filters['status'] === 'active') {
                $conditions[] = "c.is_active = 1";
            } elseif ($filters['status'] === 'inactive') {
                $conditions[] = "c.is_active = 0";
            }
        }

        if (isset($filters['parent_id'])) {
            if ($filters['parent_id'] === null || $filters['parent_id'] === '') {
                $conditions[] = "c.parent_id IS NULL";
            } else {
                $conditions[] = "c.parent_id = ?";
                $params[] = (int)$filters['parent_id'];
            }
        }

        $whereClause = '';
        if (!empty($conditions)) {
            $whereClause = 'WHERE ' . implode(' AND ', $conditions);
        }

        $categories = $this->db->query(
            "SELECT c.*,
                    COUNT(p.id) as product_count,
                    parent.name as parent_name
             FROM categories c
             LEFT JOIN products p ON c.id = p.category_id AND p.status = 'active'
             LEFT JOIN categories parent ON c.parent_id = parent.id
             {$whereClause}
             GROUP BY c.id
             ORDER BY c.sort_order ASC, c.name ASC",
            $params
        );

        foreach ($categories as &$category) {
            $category = $this->castAttributes($category);
            $category['product_count'] = (int)$category['product_count'];

            // Get children count
            $childrenCount = $this->db->queryOne(
                "SELECT COUNT(*) as count FROM categories WHERE parent_id = ?",
                [$category['id']]
            );
            $category['children_count'] = (int)($childrenCount['count'] ?? 0);
        }

        return $categories;
    }

    /**
     * Count subcategories of a category
     */
    public function countSubcategories(int $categoryId): int
    {
        $result = $this->db->queryOne(
            "SELECT COUNT(*) as count FROM categories WHERE parent_id = ?",
            [$categoryId]
        );

        return (int)($result['count'] ?? 0);
    }

    /**
     * Search categories
     */
    public function searchCategories(string $query): array
    {
        $categories = $this->db->query(
            "SELECT * FROM categories
             WHERE (name LIKE ? OR description LIKE ?) AND is_active = 1
             ORDER BY name ASC",
            ["%{$query}%", "%{$query}%"]
        );

        foreach ($categories as &$category) {
            $category = $this->castAttributes($category);
            $category['breadcrumb'] = $this->getBreadcrumb($category['id']);
        }

        return $categories;
    }
}