import uuid
from datetime import datetime, timezone
from decimal import Decimal
from typing import Any

from sqlalchemy import select

from app.models import Order, OrderStatus, Payment, PaymentMethod, PaymentStatus
from app.repositories.base import BaseRepository


class OrderRepository(BaseRepository[Order]):
    model = Order

    def _query(self, filters: dict[str, Any]) -> Any:
        stmt = select(Order).order_by(Order.created_at.desc())
        if filters.get("user_id"):
            stmt = stmt.where(Order.user_id == filters["user_id"])
        if filters.get("status"):
            stmt = stmt.where(Order.status == filters["status"])
        if filters.get("payment_status"):
            stmt = stmt.where(Order.payment_status == filters["payment_status"])
        if filters.get("flow"):
            stmt = stmt.where(Order.product_snapshot["flow"].astext == filters["flow"])
        if filters.get("search"):
            stmt = stmt.where(Order.order_number.ilike(f"%{filters['search']}%"))
        if filters.get("statuses"):
            stmt = stmt.where(Order.status.in_(filters["statuses"]))
        return stmt

    async def search(self, page: int, size: int, **filters: Any) -> tuple[list[Order], int]:
        return await self.paginate(self._query(filters), page, size)

    async def recent_for_user(self, user_id: uuid.UUID, limit: int = 10) -> list[Order]:
        stmt = (
            select(Order)
            .where(Order.user_id == user_id, Order.status != OrderStatus.DRAFT)
            .order_by(Order.created_at.desc())
            .limit(limit)
        )
        return list((await self.session.execute(stmt)).scalars().all())

    async def get_by_number(self, order_number: str) -> Order | None:
        return await self.get_by(order_number=order_number.strip().upper())

    async def list_by_status(self, statuses: list[str], limit: int = 50) -> list[Order]:
        stmt = (
            select(Order)
            .where(Order.status.in_(statuses))
            .order_by(Order.created_at)
            .limit(limit)
        )
        return list((await self.session.execute(stmt)).scalars().all())


class PaymentRepository(BaseRepository[Payment]):
    model = Payment

    def _query(self, filters: dict[str, Any]) -> Any:
        stmt = select(Payment).order_by(Payment.created_at.desc())
        if filters.get("status"):
            stmt = stmt.where(Payment.status == filters["status"])
        if filters.get("method"):
            stmt = stmt.where(Payment.method == filters["method"])
        if filters.get("order_id"):
            stmt = stmt.where(Payment.order_id == filters["order_id"])
        return stmt

    async def search(self, page: int, size: int, **filters: Any) -> tuple[list[Payment], int]:
        return await self.paginate(self._query(filters), page, size)

    async def active_for_order(self, order_id: uuid.UUID) -> Payment | None:
        stmt = (
            select(Payment)
            .where(
                Payment.order_id == order_id,
                Payment.status.in_([PaymentStatus.PENDING, PaymentStatus.WAITING_APPROVAL]),
            )
            .order_by(Payment.created_at.desc())
            .limit(1)
        )
        return (await self.session.execute(stmt)).scalar_one_or_none()

    async def payable_amount_taken(self, amount: Decimal) -> bool:
        """An amount is taken while another invoice is still active or already paid."""
        stmt = select(Payment.id).where(
            Payment.method == PaymentMethod.TRON,
            Payment.payable_amount == amount,
            Payment.status.in_([PaymentStatus.PENDING, PaymentStatus.PAID]),
        )
        return (await self.session.execute(stmt.limit(1))).first() is not None

    async def find_active_tron_by_amount(self, amount: Decimal) -> Payment | None:
        stmt = (
            select(Payment)
            .where(
                Payment.method == PaymentMethod.TRON,
                Payment.status == PaymentStatus.PENDING,
                Payment.payable_amount == amount,
            )
            .order_by(Payment.created_at)
            .limit(1)
        )
        return (await self.session.execute(stmt)).scalar_one_or_none()

    async def get_by_tx_hash(self, tx_hash: str) -> Payment | None:
        return await self.get_by(tx_hash=tx_hash)

    async def list_expired(self, limit: int = 100) -> list[Payment]:
        now = datetime.now(timezone.utc)
        stmt = (
            select(Payment)
            .where(
                Payment.status == PaymentStatus.PENDING,
                Payment.expires_at.is_not(None),
                Payment.expires_at < now,
            )
            .limit(limit)
        )
        return list((await self.session.execute(stmt)).scalars().all())

    async def list_active_tron(self, limit: int = 200) -> list[Payment]:
        stmt = (
            select(Payment)
            .where(
                Payment.method == PaymentMethod.TRON,
                Payment.status == PaymentStatus.PENDING,
            )
            .order_by(Payment.created_at)
            .limit(limit)
        )
        return list((await self.session.execute(stmt)).scalars().all())