from datetime import datetime
from typing import Optional, List, Tuple

from fastapi import UploadFile, HTTPException
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import selectinload

from src.category.models import Category, CategoryStatus
from src.category.schemas import CategoryCreateForm
from src.error import _ERROR
from src.utils.image_manager import handle_image_upload


class CategoryService:
    @staticmethod
    async def get_categories(
            db: AsyncSession,
            needle: Optional[str] = None,
            status: Optional[int] = None,
            type_filter: str = "all",
            sort_by: str = "name_path",
            sort_order: str = "asc",
            page: int = 1,
            limit: int = 10,
    ) -> Tuple[dict, List[dict]]:

        query = select(Category).options(
            selectinload(Category.parent),
            selectinload(Category.children)
        )

        if needle:
            if needle.isdigit():
                query = query.where(Category.category_id == int(needle))
            else:
                query = query.where(Category.name.ilike(f"%{needle}%"))

        if status is not None:
            if status in (0, 1):
                query = query.where(Category.status == status)
            else:
                return {"total_quantity": 0, "main_category": 0, "sub_category": 0}, []

        result = await db.execute(query)
        categories = result.scalars().all()

        if type_filter == "main":
            categories = [c for c in categories if c.parent_category_id is None]
        elif type_filter == "final":
            categories = [c for c in categories if not c.children]

        # Витягуємо всі категорії для правильного відновлення шляхів
        all_result = await db.execute(select(Category))
        all_cats = all_result.scalars().all()
        all_categories = {c.category_id: c for c in all_cats}

        def build_full_path(cat: Category) -> str:
            path = [cat.name]
            current = cat
            while current.parent_category_id:
                parent = all_categories.get(current.parent_category_id)
                if not parent:
                    break
                path.insert(0, parent.name)
                current = parent
            return " > ".join(path)

        enriched = [
            {
                "category_id": c.category_id,
                "status": c.status,
                "full_name": build_full_path(c),
                "parent_text": build_full_path(all_categories.get(c.parent_category_id))
                if c.parent_category_id else None,
                "date_added": c.date_added.strftime("%Y-%m-%d %H:%M") if c.date_added else None,
                "date_modify": c.date_modify.strftime("%Y-%m-%d %H:%M") if c.date_modify else None,
                "sort_order": c.sort_order,
                "icon": c.icon,
            }
            for c in categories
        ]

        # Підрахунок для dashboard
        total_quantity = len(categories)
        main_category = sum(1 for c in categories if c.parent_category_id is None)
        sub_category = total_quantity - main_category

        # Покращене сортування з урахуванням sort_order
        def sort_key_function(item):
            """
            Функція сортування:
            1. Спочатку категорії з визначеним sort_order (в порядку зростання)
            2. Потім категорії без sort_order, сортовані за обраним критерієм
            """
            sort_order_val = item["sort_order"]

            # Якщо є sort_order, використовуємо його як першочерговий критерій
            if sort_order_val is not None:
                # Для категорій з sort_order: (0, sort_order_value, secondary_sort)
                if sort_by == "name_path":
                    return (0, sort_order_val, item["full_name"])
                elif sort_by == "id":
                    return (0, sort_order_val, item["category_id"])
                else:
                    return (0, sort_order_val, item["full_name"])
            else:
                # Для категорій без sort_order: (1, secondary_sort)
                if sort_by == "name_path":
                    return (1, item["full_name"])
                elif sort_by == "id":
                    return (1, item["category_id"])
                else:
                    return (1, item["full_name"])

        reverse = sort_order.lower() == "desc"
        enriched.sort(key=sort_key_function, reverse=reverse)

        start = (page - 1) * limit
        end = start + limit
        paginated = enriched[start:end]

        dashboard = {
            "total_quantity": total_quantity,
            "main_category": main_category,
            "sub_category": sub_category,
        }

        return dashboard, paginated

    @staticmethod
    async def get_category_by_id(db: AsyncSession, category_id: int) -> Category | None:
        result = await db.execute(select(Category).options(
            selectinload(Category.parent),
            selectinload(Category.children)
        ).where(Category.category_id == category_id))
        category = result.scalar_one_or_none()

        if not category:
            _ERROR("CategoryNotFound")

        return category

    @staticmethod
    async def create_category(db: AsyncSession, data: CategoryCreateForm) -> Category:
        if data.status not in [0, 1]:
            _ERROR("ValidationError")

        # Перевіряємо чи існує батьківська категорія
        if data.parent_category_id and data.parent_category_id != 0:
            parent_result = await db.execute(select(Category).where(Category.category_id == data.parent_category_id))
            if not parent_result.scalar_one_or_none():
                _ERROR("ParentCategoryNotFound")

        image_url = await handle_image_upload(data.image)
        icon_url = await handle_image_upload(data.icon)

        new_category = Category(
            name=data.name,
            description=data.description,
            parent_category_id=data.parent_category_id if data.parent_category_id != 0 else None,
            seo_keyword=data.seo_keyword,
            meta_title=data.meta_title,
            meta_description=data.meta_description,
            meta_keyword=data.meta_keyword,
            status=data.status,
            sort_order=data.sort_order if hasattr(data, 'sort_order') else None,
            image=image_url,
            icon=icon_url,
            date_added=datetime.utcnow(),
        )

        db.add(new_category)
        await db.commit()
        await db.refresh(new_category)
        return new_category

    @staticmethod
    async def update_category(
            db: AsyncSession,
            category_id: int,
            name: Optional[str] = None,
            description: Optional[str] = None,
            parent_id: Optional[int] = None,
            slug: Optional[str] = None,
            meta_title: Optional[str] = None,
            meta_description: Optional[str] = None,
            meta_keywords: Optional[str] = None,
            status: Optional[int] = None,
            sort_order: Optional[int] = None,
            image: Optional[UploadFile] = None,
            icon: Optional[UploadFile] = None,
    ) -> Optional[dict]:
        result = await db.execute(select(Category).where(Category.category_id == category_id))
        category = result.scalars().first()

        if not category:
            _ERROR("CategoryNotFound")

        # Перевіряємо батьківську категорію якщо вказана
        if parent_id is not None and parent_id != 0:
            parent_result = await db.execute(select(Category).where(Category.category_id == parent_id))
            if not parent_result.scalar_one_or_none():
                _ERROR("ParentCategoryNotFound")

        if name is not None:
            category.name = name
        if description is not None:
            category.description = description
        if parent_id is not None:
            category.parent_category_id = parent_id if parent_id != 0 else None
        if slug is not None:
            category.seo_keyword = slug
        if meta_title is not None:
            category.meta_title = meta_title
        if meta_description is not None:
            category.meta_description = meta_description
        if meta_keywords is not None:
            category.meta_keyword = meta_keywords
        if sort_order is not None:
            category.sort_order = sort_order

        if status is not None:
            if status not in (0, 1):
                _ERROR("ValidationError")
            category.status = 1 if status == 1 else 0

        if image is not None:
            category.image = await handle_image_upload(image)

        if icon is not None:  # Оновлюємо іконку
            category.icon = await handle_image_upload(icon)

        category.date_modify = datetime.utcnow()

        await db.commit()
        await db.refresh(category)

        return {
            "category_id": category.category_id,
            "name": category.name,
            "description": category.description,
            "parent_id": category.parent_category_id,
            "slug": category.seo_keyword,
            "meta_title": category.meta_title,
            "meta_description": category.meta_description,
            "meta_keywords": category.meta_keyword,
            "status": 1 if category.status == CategoryStatus.ENABLED else 0,
            "sort_order": category.sort_order,
            "image": category.image,
            "icon": category.icon,  # Додаємо іконку у відповідь
            "date_added": category.date_added.isoformat(),
            "date_modify": category.date_modify.isoformat(),
        }

    @staticmethod
    async def delete_category_recursive(db: AsyncSession, category_id: int) -> bool:
        result = await db.execute(select(Category).where(Category.category_id == category_id))
        category = result.scalars().first()

        if not category:
            _ERROR("CategoryNotFound")

        # Перевіряємо чи є пов'язані продукти (якщо є таблиця продуктів)
        # Тут можна додати перевірку на ProductCategory або Product.category_id

        async def recursive_delete(cat: Category):
            result = await db.execute(
                select(Category).where(Category.parent_category_id == cat.category_id)
            )
            children = result.scalars().all()

            for child in children:
                await recursive_delete(child)
            await db.delete(cat)

        await recursive_delete(category)
        await db.commit()
        return True