from datetime import timedelta
from decimal import Decimal
from typing import Any

from sqlalchemy import func, select
from sqlalchemy.ext.asyncio import AsyncSession

from app.models import Order, OrderStatus, Payment, PaymentStatus, User, Wallet
from app.utils.helpers import money, utcnow

PAID_STATUSES = (OrderStatus.PAID, OrderStatus.PROCESSING, OrderStatus.COMPLETED)


class DashboardService:
    def __init__(self, session: AsyncSession) -> None:
        self.session = session

    async def _scalar(self, stmt: Any) -> Any:
        return (await self.session.execute(stmt)).scalar()

    async def summary(self) -> dict[str, Any]:
        now = utcnow()
        today = now.replace(hour=0, minute=0, second=0, microsecond=0)

        today_orders = await self._scalar(
            select(func.count()).select_from(Order).where(Order.created_at >= today)
        )
        today_revenue = await self._scalar(
            select(func.coalesce(func.sum(Order.final_price), 0)).where(
                Order.created_at >= today, Order.status.in_(PAID_STATUSES)
            )
        )
        total_revenue = await self._scalar(
            select(func.coalesce(func.sum(Order.final_price), 0)).where(
                Order.status.in_(PAID_STATUSES)
            )
        )
        total_users = await self._scalar(select(func.count()).select_from(User))
        new_users = await self._scalar(
            select(func.count()).select_from(User).where(User.created_at >= today)
        )
        pending_orders = await self._scalar(
            select(func.count())
            .select_from(Order)
            .where(Order.status.in_([OrderStatus.WAITING_PAYMENT, OrderStatus.PROCESSING]))
        )
        pending_payments = await self._scalar(
            select(func.count())
            .select_from(Payment)
            .where(Payment.status.in_([PaymentStatus.PENDING, PaymentStatus.WAITING_APPROVAL]))
        )
        wallet_total = await self._scalar(select(func.coalesce(func.sum(Wallet.balance), 0)))

        return {
            "today_orders": int(today_orders or 0),
            "today_revenue": str(money(Decimal(str(today_revenue or 0)))),
            "total_revenue": str(money(Decimal(str(total_revenue or 0)))),
            "total_users": int(total_users or 0),
            "new_users": int(new_users or 0),
            "pending_orders": int(pending_orders or 0),
            "pending_payments": int(pending_payments or 0),
            "wallet_balance": str(money(Decimal(str(wallet_total or 0)))),
        }

    async def sales_chart(self, days: int = 14) -> list[dict[str, Any]]:
        since = utcnow().replace(hour=0, minute=0, second=0, microsecond=0) - timedelta(days=days - 1)
        day = func.date(Order.created_at).label("day")
        stmt = (
            select(day, func.count().label("orders"), func.coalesce(func.sum(Order.final_price), 0))
            .where(Order.created_at >= since, Order.status.in_(PAID_STATUSES))
            .group_by(day)
            .order_by(day)
        )
        rows = (await self.session.execute(stmt)).all()
        return [
            {
                # PostgreSQL returns a date object here, SQLite a 'YYYY-MM-DD' string.
                "date": str(row[0])[:10],
                "orders": int(row[1]),
                "revenue": str(money(Decimal(str(row[2])))),
            }
            for row in rows
        ]

    async def latest_orders(self, limit: int = 10) -> list[Order]:
        stmt = select(Order).order_by(Order.created_at.desc()).limit(limit)
        return list((await self.session.execute(stmt)).scalars().all())

    async def latest_payments(self, limit: int = 10) -> list[Payment]:
        stmt = select(Payment).order_by(Payment.created_at.desc()).limit(limit)
        return list((await self.session.execute(stmt)).scalars().all())