"""Aggregate statistics with honest metric names."""

from __future__ import annotations

from datetime import datetime, timedelta

from sqlalchemy import func, select

from app.database.base import utcnow
from app.database.models import (
    AccessToken,
    MediaAsset,
    MembershipCheck,
    Post,
    PostDestination,
    PostStatus,
    RequiredChannel,
    ScheduledJob,
    User,
    UserDelivery,
)
from app.database.repositories.base import BaseRepository


class StatisticsRepository(BaseRepository):
    async def media_count(self) -> int:
        async with self.db.session() as session:
            return int(await session.scalar(select(func.count(MediaAsset.id))) or 0)

    async def media_status_counts(self) -> dict[str, int]:
        async with self.db.session() as session:
            rows = await session.execute(
                select(MediaAsset.status, func.count(MediaAsset.id)).group_by(MediaAsset.status)
            )
            return {status.value: int(count) for status, count in rows}

    async def post_status_counts(self) -> dict[str, int]:
        async with self.db.session() as session:
            rows = await session.execute(
                select(Post.status, func.count(Post.id)).group_by(Post.status)
            )
            return {status.value: int(count) for status, count in rows}

    async def user_count(self) -> int:
        async with self.db.session() as session:
            return int(await session.scalar(select(func.count(User.id))) or 0)

    async def delivery_count(self) -> int:
        async with self.db.session() as session:
            return int(await session.scalar(select(func.count(UserDelivery.id))) or 0)

    async def deleted_delivery_count(self) -> int:
        async with self.db.session() as session:
            return int(
                await session.scalar(
                    select(func.count(UserDelivery.id)).where(
                        UserDelivery.delete_status == "DELETED"
                    )
                )
                or 0
            )

    async def link_request_count(self) -> int:
        """Number of successful token consumptions (not channel views)."""

        async with self.db.session() as session:
            return int(await session.scalar(select(func.coalesce(func.sum(AccessToken.use_count), 0))) or 0)

    async def expired_token_count(self) -> int:
        async with self.db.session() as session:
            return int(
                await session.scalar(
                    select(func.count(AccessToken.id)).where(
                        AccessToken.expires_at.is_not(None),
                        AccessToken.expires_at < utcnow(),
                    )
                )
                or 0
            )

    async def membership_check_count(self) -> int:
        async with self.db.session() as session:
            return int(await session.scalar(select(func.count(MembershipCheck.id))) or 0)

    async def destination_failure_count(self) -> int:
        async with self.db.session() as session:
            return int(
                await session.scalar(
                    select(func.count(PostDestination.id)).where(
                        PostDestination.status == "FAILED"
                    )
                )
                or 0
            )

    async def required_channel_count(self) -> int:
        async with self.db.session() as session:
            return int(
                await session.scalar(
                    select(func.count(RequiredChannel.id)).where(
                        RequiredChannel.is_active.is_(True)
                    )
                )
                or 0
            )

    async def pending_job_count(self) -> int:
        async with self.db.session() as session:
            return int(
                await session.scalar(
                    select(func.count(ScheduledJob.id)).where(ScheduledJob.status == "PENDING")
                )
                or 0
            )

    async def popular_posts(self, limit: int = 5) -> list[tuple[int, str | None, int, int]]:
        """Return (post_id, title, successful_link_requests, deliveries)."""

        async with self.db.session() as session:
            usage = (
                select(
                    AccessToken.post_id.label("post_id"),
                    func.coalesce(func.sum(AccessToken.use_count), 0).label("uses"),
                )
                .group_by(AccessToken.post_id)
                .subquery()
            )
            deliveries = (
                select(
                    UserDelivery.post_id.label("post_id"),
                    func.count(UserDelivery.id).label("deliveries"),
                )
                .group_by(UserDelivery.post_id)
                .subquery()
            )
            rows = await session.execute(
                select(
                    Post.id,
                    Post.title,
                    func.coalesce(usage.c.uses, 0),
                    func.coalesce(deliveries.c.deliveries, 0),
                )
                .outerjoin(usage, usage.c.post_id == Post.id)
                .outerjoin(deliveries, deliveries.c.post_id == Post.id)
                .order_by(func.coalesce(usage.c.uses, 0).desc())
                .limit(limit)
            )
            return [
                (int(post_id), title, int(uses), int(delivery_count))
                for post_id, title, uses, delivery_count in rows
            ]

    async def counts_since(self, since: datetime) -> dict[str, int]:
        async with self.db.session() as session:
            deliveries = int(
                await session.scalar(
                    select(func.count(UserDelivery.id)).where(UserDelivery.sent_at >= since)
                )
                or 0
            )
            checks = int(
                await session.scalar(
                    select(func.count(MembershipCheck.id)).where(
                        MembershipCheck.created_at >= since
                    )
                )
                or 0
            )
            new_users = int(
                await session.scalar(select(func.count(User.id)).where(User.created_at >= since))
                or 0
            )
            return {"deliveries": deliveries, "membership_checks": checks, "new_users": new_users}

    async def period_counts(self) -> dict[str, dict[str, int]]:
        now = utcnow()
        return {
            "today": await self.counts_since(now - timedelta(days=1)),
            "week": await self.counts_since(now - timedelta(days=7)),
            "month": await self.counts_since(now - timedelta(days=30)),
        }

    async def record_membership_check(
        self, telegram_id: int, post_id: int | None, all_joined: bool, error_count: int
    ) -> None:
        async with self.db.session() as session:
            session.add(
                MembershipCheck(
                    telegram_id=telegram_id,
                    post_id=post_id,
                    all_joined=all_joined,
                    error_count=error_count,
                )
            )

    async def published_count(self) -> int:
        async with self.db.session() as session:
            return int(
                await session.scalar(
                    select(func.count(Post.id)).where(Post.status == PostStatus.PUBLISHED)
                )
                or 0
            )