from typing import Any, Optional, Sequence
from sqlalchemy import select, func, distinct
from sqlalchemy.ext.asyncio import AsyncSession
from modules.emojis.models import CustomEmoji, EmojiPack


class CustomEmojiRepository:
    def __init__(self, db: AsyncSession):
        self.db = db

    async def search(
        self,
        query: Optional[str] = None,
        category: Optional[str] = None,
        limit: int = 50,
        offset: int = 0,
    ) -> Sequence[CustomEmoji]:
        stmt = select(CustomEmoji)
        if category:
            stmt = stmt.where(CustomEmoji.category == category.lower().strip())
        if query:
            q = f"%{query.lower().strip()}%"
            stmt = stmt.where(
                (func.lower(CustomEmoji.name).like(q)) |
                (CustomEmoji.fallback_emoji.like(q))
            )
        stmt = stmt.order_by(CustomEmoji.name).limit(limit).offset(offset)
        res = await self.db.execute(stmt)
        return res.scalars().all()

    async def get_by_custom_emoji_id(self, custom_emoji_id: str) -> Optional[CustomEmoji]:
        stmt = select(CustomEmoji).where(CustomEmoji.custom_emoji_id == custom_emoji_id)
        res = await self.db.execute(stmt)
        return res.scalar_one_or_none()

    async def get_categories(self) -> Sequence[str]:
        stmt = select(CustomEmoji.category).distinct().order_by(CustomEmoji.category)
        res = await self.db.execute(stmt)
        return res.scalars().all()

    async def get_or_create_pack(
        self,
        name: str,
        title: str,
        is_animated: bool = True,
        is_video: bool = False,
    ) -> EmojiPack:
        stmt = select(EmojiPack).where(EmojiPack.name == name)
        res = await self.db.execute(stmt)
        pack = res.scalar_one_or_none()
        if not pack:
            pack = EmojiPack(
                name=name,
                title=title,
                is_animated=is_animated,
                is_video=is_video,
            )
            self.db.add(pack)
            await self.db.flush()
        return pack

    async def upsert_emoji(
        self,
        custom_emoji_id: str,
        name: str,
        fallback_emoji: str,
        category: str = "general",
        is_animated: bool = True,
        pack_id: Optional[str] = None,
    ) -> CustomEmoji:
        existing = await self.get_by_custom_emoji_id(custom_emoji_id)
        if existing:
            existing.name = name
            existing.fallback_emoji = fallback_emoji
            existing.category = category
            existing.is_animated = is_animated
            if pack_id:
                existing.pack_id = pack_id
            return existing

        emoji = CustomEmoji(
            custom_emoji_id=custom_emoji_id,
            name=name,
            fallback_emoji=fallback_emoji,
            category=category,
            is_animated=is_animated,
            pack_id=pack_id,
        )
        self.db.add(emoji)
        await self.db.flush()
        return emoji

    async def list_packs(self, limit: int = 50, offset: int = 0) -> Sequence[tuple[EmojiPack, int]]:
        stmt = (
            select(EmojiPack, func.count(CustomEmoji.id).label("emojis_count"))
            .outerjoin(CustomEmoji, EmojiPack.id == CustomEmoji.pack_id)
            .group_by(EmojiPack.id)
            .order_by(EmojiPack.title)
            .limit(limit)
            .offset(offset)
        )
        res = await self.db.execute(stmt)
        return res.all()

    async def get_pack_by_id(self, pack_id: Any) -> Optional[EmojiPack]:
        stmt = select(EmojiPack).where(EmojiPack.id == pack_id)
        res = await self.db.execute(stmt)
        return res.scalar_one_or_none()

    async def get_pack_emojis(self, pack_id: Any) -> Sequence[CustomEmoji]:
        stmt = select(CustomEmoji).where(CustomEmoji.pack_id == pack_id).order_by(CustomEmoji.name)
        res = await self.db.execute(stmt)
        return res.scalars().all()

    async def create_pack(
        self,
        name: str,
        title: str,
        is_animated: bool = True,
        is_video: bool = False,
    ) -> EmojiPack:
        pack = EmojiPack(
            name=name,
            title=title,
            is_animated=is_animated,
            is_video=is_video,
        )
        self.db.add(pack)
        await self.db.flush()
        return pack

    async def delete_pack(self, pack: EmojiPack) -> None:
        await self.db.delete(pack)
        await self.db.flush()

