from typing import Any, List, Optional, Dict

from sqlalchemy import func, desc, select

from server.db.es_connector import ES_INSTANCE
from server.db.utils import BaseQueryExecutor
from server.dna.category.models import Category, CategoryEntity
from server.dna.utils import QueryWrapper


class CategoryRepository:
    @staticmethod
    async def get_categories_data(filters: dict, limit: Optional[int], offset: Optional[int]):
        results = await BaseQueryExecutor.get(
            model=Category,
            many=True,
            filters=filters,
            orders=(desc(Category.is_homepage), desc(Category.updated_at)),
            limit=limit,
            offset=offset,
        )
        return results

    @staticmethod
    async def get_categories_count(filters: dict):
        return await BaseQueryExecutor.get_count(model=Category, filters=filters)

    @staticmethod
    async def get_category_entities_counters(categories_id: list):
        count_query = (
            select(CategoryEntity.category_id, CategoryEntity.entity_type, func.count(CategoryEntity.category_id))
            .where(CategoryEntity.category_id.in_(categories_id))
            .group_by(CategoryEntity.entity_type, CategoryEntity.category_id)
            .order_by(CategoryEntity.category_id)
        )
        count_results = await QueryWrapper().fetchall(query=count_query)
        return count_results

    @staticmethod
    async def get_category_data_by_id(id: int, user_id: str):
        query = (
            select(
                Category,
                CategoryEntity.entity_type.label("entity_type"),
                func.count(CategoryEntity.entity_id).label("count"),
            )
            .join(CategoryEntity, isouter=True)
            .where(Category.user_id == user_id, Category.is_deleted.is_(None), Category.id == id)
            .group_by(Category.id, CategoryEntity.entity_type)
            .order_by(desc(Category.created_at))
        )
        result = await QueryWrapper().fetchall(query)
        return result

    @staticmethod
    async def get_category_entities(categories_id: list) -> Dict[int, List[Dict[str, Any]]]:
        query = (
            select(CategoryEntity.category_id, CategoryEntity.entity_type, CategoryEntity.entity_id)
            .where(CategoryEntity.category_id.in_(categories_id))
            .order_by(desc(CategoryEntity.created_at))
        )
        results = await QueryWrapper().fetchall(query)

        entities: Dict = {}
        for result in results:
            entities[result.get("category_id")] = []

        for result in results:
            category_id = result.get("category_id")
            if category_id in entities:
                entity = dict(
                    entity_type=result.get("entity_type"),
                    entity_id=result.get("entity_id"),
                )
                entities[category_id].append(entity)
        return entities

    @staticmethod
    async def get_entity_categories(user_id: str, ids: list, entity_type: str) -> list:
        query = (
            select(Category, CategoryEntity.entity_id.label("entity_id"))
            .join(CategoryEntity, Category.id == CategoryEntity.category_id)
            .where(
                Category.user_id == user_id,
                CategoryEntity.entity_type == entity_type,
                CategoryEntity.entity_id.in_(ids),
                Category.is_deleted.is_(None),
            )
            .order_by(Category.name)
        )
        results = await QueryWrapper._execute(query, commit=False)
        res = results.fetchall()
        return [
            {"id": id_, "categories": [models.Category for models in res if models.entity_id == id_]} for id_ in ids
        ]

    @staticmethod
    async def get_entity_data(query: dict, index: str) -> list:
        _, result, _ = await ES_INSTANCE.search(
            index=index, query=query, size=ES_INSTANCE.MAX_PAGE_SIZE  # TODO: add limit and offset funtional
        )

        return result

    @staticmethod
    async def create_category(data: dict):
        result = await BaseQueryExecutor.insert(model=Category, values=data)
        return result

    @staticmethod
    async def update_category(values: dict, filters: dict):
        result = await BaseQueryExecutor.update(model=Category, values=values, filters=filters)
        return result

    @staticmethod
    async def set_all_user_categories_homepage_false(user_id):
        return await BaseQueryExecutor.update(
            model=Category,
            values={"is_homepage": False},
            filters={"user_id": user_id, "is_deleted": None, "is_homepage": True},
        )

    @staticmethod
    async def get_categories_entities(filters: dict, limit: int, offset: int):
        results = await BaseQueryExecutor.get(
            model=CategoryEntity,
            filters=filters,
            orders=(desc(CategoryEntity.created_at),),
            many=True,
            limit=limit,
            offset=offset,
        )
        return results

    @staticmethod
    async def create_category_entity(values):
        result = await BaseQueryExecutor.insert(
            model=CategoryEntity,
            values=values,
            constraint=CategoryEntity.__table_args__[0],
            set_condition={
                CategoryEntity.created_at: func.now(),
                CategoryEntity.chart_type: values["chart_type"],
                CategoryEntity.cover_image: values.get("cover_image"),
            },
        )
        return result

    @staticmethod
    async def update_category_entity(values: dict, filters: dict):
        result = await BaseQueryExecutor.update(model=CategoryEntity, values=values, filters=filters)
        return result

    @staticmethod
    async def delete_categories_entities(filters: dict):
        await BaseQueryExecutor.delete(model=CategoryEntity, filters=filters)