import uuid
from typing import Optional, Sequence
from sqlalchemy import select, func, or_, delete, String
from sqlalchemy.ext.asyncio import AsyncSession
from modules.marketplace.models import (
    MarketplaceTemplate,
    MarketplaceModule,
    InstalledBotInstance,
)
from modules.marketplace.manifests_catalog import BOT_MANIFESTS, REUSABLE_MODULES


class MarketplaceRepository:
    def __init__(self, session: AsyncSession):
        self.session = session

    async def list_templates(
        self,
        category: Optional[str] = None,
        search: Optional[str] = None,
        tag: Optional[str] = None,
        limit: int = 50,
        offset: int = 0,
    ) -> Sequence[MarketplaceTemplate]:
        query = select(MarketplaceTemplate).where(MarketplaceTemplate.is_active.is_(True))

        if category and category != "all":
            query = query.where(MarketplaceTemplate.category == category)

        if search:
            search_term = f"%{search.lower()}%"
            query = query.where(
                or_(
                    func.lower(MarketplaceTemplate.id).like(search_term),
                    func.lower(MarketplaceTemplate.name).like(search_term),
                    func.lower(MarketplaceTemplate.headline).like(search_term),
                    func.lower(MarketplaceTemplate.description).like(search_term),
                    func.cast(MarketplaceTemplate.tags, String).ilike(search_term),
                )
            )

        # Sort by featured first, then highest rating, then install_count
        query = query.order_by(
            MarketplaceTemplate.is_featured.desc(),
            MarketplaceTemplate.rating.desc(),
            MarketplaceTemplate.install_count.desc(),
        ).limit(limit).offset(offset)

        result = await self.session.execute(query)
        templates = result.scalars().all()

        if tag:
            # Filter in Python for JSON tag containment
            templates = [t for t in templates if tag.lower() in [tg.lower() for tg in (t.tags or [])]]

        return templates

    async def get_template_by_id(self, template_id: str) -> Optional[MarketplaceTemplate]:
        query = select(MarketplaceTemplate).where(
            MarketplaceTemplate.id == template_id,
            MarketplaceTemplate.is_active.is_(True),
        )
        result = await self.session.execute(query)
        return result.scalar_one_or_none()

    async def list_modules(self, category: Optional[str] = None) -> Sequence[MarketplaceModule]:
        query = select(MarketplaceModule).where(MarketplaceModule.is_active.is_(True))
        if category and category != "all":
            query = query.where(MarketplaceModule.category == category)
        query = query.order_by(MarketplaceModule.name.asc())
        result = await self.session.execute(query)
        return result.scalars().all()

    async def get_module_by_id(self, module_id: str) -> Optional[MarketplaceModule]:
        query = select(MarketplaceModule).where(MarketplaceModule.id == module_id)
        result = await self.session.execute(query)
        return result.scalar_one_or_none()

    async def create_instance(self, instance: InstalledBotInstance) -> InstalledBotInstance:
        self.session.add(instance)
        await self.session.flush()
        return instance

    async def get_instance_by_id(
        self, instance_id: uuid.UUID, tenant_id: uuid.UUID
    ) -> Optional[InstalledBotInstance]:
        query = (
            select(InstalledBotInstance)
            .where(
                InstalledBotInstance.id == instance_id,
                InstalledBotInstance.tenant_id == tenant_id,
            )
        )
        result = await self.session.execute(query)
        return result.scalar_one_or_none()

    async def list_instances_by_tenant(
        self, tenant_id: uuid.UUID
    ) -> Sequence[InstalledBotInstance]:
        query = (
            select(InstalledBotInstance)
            .where(InstalledBotInstance.tenant_id == tenant_id)
            .order_by(InstalledBotInstance.created_at.desc())
        )
        result = await self.session.execute(query)
        return result.scalars().all()

    async def delete_instance(self, instance: InstalledBotInstance) -> None:
        await self.session.delete(instance)
        await self.session.flush()

    async def increment_install_count(self, template_id: str) -> None:
        template = await self.get_template_by_id(template_id)
        if template:
            template.install_count += 1
            await self.session.flush()

    async def count_by_categories(self) -> dict[str, int]:
        query = (
            select(MarketplaceTemplate.category, func.count(MarketplaceTemplate.id))
            .where(MarketplaceTemplate.is_active.is_(True))
            .group_by(MarketplaceTemplate.category)
        )
        result = await self.session.execute(query)
        return {cat: count for cat, count in result.all()}

    async def seed_defaults_if_empty(self) -> int:
        """Seeds the 25 ready-made bot manifests and reusable modules if table is empty."""
        count_res = await self.session.execute(select(func.count(MarketplaceTemplate.id)))
        count = count_res.scalar_one()
        if count > 0:
            return 0  # Already seeded

        seeded_count = 0
        for item in BOT_MANIFESTS:
            manifest_copy = dict(item)
            template = MarketplaceTemplate(
                id=item["id"],
                name=item["name"],
                headline=item["headline"],
                description=item["description"],
                category=item["category"],
                icon=item.get("icon", "bot"),
                version=item.get("version", "1.0.0"),
                author=item.get("author", "Platform Core"),
                is_official=item.get("is_official", True),
                is_featured=item.get("is_featured", False),
                rating=item.get("rating", 5.0),
                install_count=item.get("install_count", 0),
                supported_channels=item.get("supported_channels", ["telegram"]),
                required_permissions=item.get("required_permissions", []),
                config_schema=item.get("config_schema", {}),
                default_config=item.get("default_config", {}),
                system_templates=item.get("system_templates", []),
                mcp_tools=item.get("mcp_tools", []),
                modules=item.get("modules", []),
                manifest=manifest_copy,
                tags=item.get("tags", []),
                is_active=True,
            )
            self.session.add(template)
            seeded_count += 1

        for mod in REUSABLE_MODULES:
            module = MarketplaceModule(
                id=mod["id"],
                name=mod["name"],
                description=mod["description"],
                category=mod["category"],
                version=mod.get("version", "1.0.0"),
                icon=mod.get("icon", "package"),
                config_schema=mod.get("config_schema", {}),
                is_official=mod.get("is_official", True),
                is_active=True,
            )
            self.session.add(module)

        await self.session.flush()
        return seeded_count
