from typing import Any, Optional, Sequence
from uuid import UUID
from sqlalchemy import select, func, and_, delete
from sqlalchemy.orm import selectinload
from sqlalchemy.ext.asyncio import AsyncSession
from modules.bots.models import (
    Bot,
    BotCredential,
    BotWebhook,
    BotSetting,
    BotCommand,
    BotUpdate,
    BotUser,
)


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

    async def list_by_tenant(self, tenant_id: UUID) -> Sequence[Bot]:
        stmt = (
            select(Bot)
            .options(
                selectinload(Bot.webhook),
                selectinload(Bot.settings),
            )
            .where(Bot.tenant_id == tenant_id)
            .order_by(Bot.created_at.desc())
        )
        res = await self.db.execute(stmt)
        return res.scalars().all()

    async def list_all(self, limit: int = 100, offset: int = 0) -> Sequence[Bot]:
        """Superadmin operation across all tenants."""
        stmt = (
            select(Bot)
            .options(
                selectinload(Bot.webhook),
                selectinload(Bot.settings),
            )
            .order_by(Bot.created_at.desc())
            .limit(limit)
            .offset(offset)
        )
        res = await self.db.execute(stmt)
        return res.scalars().all()

    async def get_by_id(self, bot_id: UUID, tenant_id: Optional[UUID] = None) -> Optional[Bot]:
        stmt = (
            select(Bot)
            .options(
                selectinload(Bot.webhook),
                selectinload(Bot.settings),
                selectinload(Bot.commands),
            )
            .where(Bot.id == bot_id)
        )
        if tenant_id:
            stmt = stmt.where(Bot.tenant_id == tenant_id)
        res = await self.db.execute(stmt)
        return res.scalar_one_or_none()

    async def get_by_username(self, username: str) -> Optional[Bot]:
        clean_user = username.lstrip("@").lower()
        stmt = (
            select(Bot)
            .options(
                selectinload(Bot.webhook),
                selectinload(Bot.settings),
            )
            .where(func.lower(Bot.username) == clean_user)
        )
        res = await self.db.execute(stmt)
        return res.scalar_one_or_none()

    async def create_bot_with_relations(
        self,
        bot: Bot,
        encrypted_token: str,
        key_version: int,
        webhook_url: str,
        secret_hash: str,
        settings_dict: Optional[dict[str, Any]] = None,
    ) -> Bot:
        self.db.add(bot)
        await self.db.flush()

        # Credentials
        creds = BotCredential(
            bot_id=bot.id,
            encrypted_token=encrypted_token,
            key_version=key_version,
        )
        self.db.add(creds)

        # Webhook
        webhook = BotWebhook(
            bot_id=bot.id,
            secret_hash=secret_hash,
            webhook_url=webhook_url,
            is_active=True,
        )
        self.db.add(webhook)

        # Settings
        b_settings = BotSetting(
            bot_id=bot.id,
            settings_json=settings_dict or {},
        )
        self.db.add(b_settings)

        await self.db.flush()
        return bot

    async def get_credentials(self, bot_id: UUID) -> Optional[BotCredential]:
        stmt = select(BotCredential).where(BotCredential.bot_id == bot_id)
        res = await self.db.execute(stmt)
        return res.scalar_one_or_none()

    async def get_webhook(self, bot_id: UUID) -> Optional[BotWebhook]:
        stmt = select(BotWebhook).where(BotWebhook.bot_id == bot_id)
        res = await self.db.execute(stmt)
        return res.scalar_one_or_none()

    async def update_webhook_status(
        self,
        bot_id: UUID,
        is_active: bool,
        secret_hash: Optional[str] = None,
    ) -> None:
        webhook = await self.get_webhook(bot_id)
        if webhook:
            webhook.is_active = is_active
            if secret_hash:
                webhook.secret_hash = secret_hash
            await self.db.flush()

    async def delete_bot(self, bot: Bot) -> None:
        await self.db.delete(bot)
        await self.db.flush()

    async def count_bot_users(self, bot_id: UUID) -> int:
        stmt = select(func.count(BotUser.id)).where(BotUser.bot_id == bot_id)
        res = await self.db.execute(stmt)
        return res.scalar() or 0

    async def check_and_record_update(self, bot_id: UUID, update_id: int) -> bool:
        """
        Atomic update deduplication check.
        Returns True if update is new and successfully recorded; False if already processed.
        """
        stmt = select(BotUpdate).where(
            BotUpdate.bot_id == bot_id,
            BotUpdate.update_id == update_id,
        )
        res = await self.db.execute(stmt)
        if res.scalar_one_or_none():
            return False  # Duplicate!

        new_update = BotUpdate(bot_id=bot_id, update_id=update_id, status="processed")
        self.db.add(new_update)
        try:
            await self.db.flush()
            return True
        except Exception:
            # Caught by unique constraint
            return False

    async def upsert_bot_user(
        self,
        bot_id: UUID,
        telegram_user_id: int,
        username: Optional[str] = None,
        first_name: Optional[str] = None,
        last_name: Optional[str] = None,
        preferred_locale: str = "en",
    ) -> BotUser:
        stmt = select(BotUser).where(
            BotUser.bot_id == bot_id,
            BotUser.telegram_user_id == telegram_user_id,
        )
        res = await self.db.execute(stmt)
        user = res.scalar_one_or_none()

        if user:
            if username is not None:
                user.username = username
            if first_name is not None:
                user.first_name = first_name
            if last_name is not None:
                user.last_name = last_name
        else:
            user = BotUser(
                bot_id=bot_id,
                telegram_user_id=telegram_user_id,
                username=username,
                first_name=first_name,
                last_name=last_name,
                preferred_locale=preferred_locale,
                is_blocked=False,
            )
            self.db.add(user)

        await self.db.flush()
        return user

    async def get_bot_users(
        self,
        bot_id: UUID,
        limit: int = 100,
        offset: int = 0,
    ) -> Sequence[BotUser]:
        stmt = (
            select(BotUser)
            .where(BotUser.bot_id == bot_id)
            .order_by(BotUser.created_at.desc())
            .limit(limit)
            .offset(offset)
        )
        res = await self.db.execute(stmt)
        return res.scalars().all()
