from __future__ import annotations

from datetime import date, datetime, timezone
from decimal import Decimal
from typing import Optional
from uuid import UUID, uuid4

from sqlalchemy import func as sa_func, select, text
from sqlalchemy.ext.asyncio import AsyncSession

from src.domain.cloud_credits.conversion import DEFAULT_FIXED_MONTHLY_FEE_CREDITS, DEFAULT_MARKUP_PCT
from src.domain.cloud_credits.models import (
    CloudCreditLedger,
    CloudCreditPackage,
    CloudCreditPurchase,
    CloudUsageConversionRate,
    ProjectBillingExtras,
    WorkspaceCloudCreditPolicy,
    WorkspaceWalletAlert,
)


class CloudCreditPackageRepository:
    def __init__(self, db: AsyncSession) -> None:
        self._db = db

    async def get_by_slug(self, slug: str) -> Optional[CloudCreditPackage]:
        result = await self._db.execute(select(CloudCreditPackage).where(CloudCreditPackage.slug == slug))
        return result.scalar_one_or_none()

    async def list_active(self) -> list[CloudCreditPackage]:
        result = await self._db.execute(
            select(CloudCreditPackage)
            .where(CloudCreditPackage.is_active.is_(True))
            .order_by(CloudCreditPackage.sort_order)
        )
        return list(result.scalars().all())

    async def update(self, package_id: UUID, **fields) -> Optional[CloudCreditPackage]:
        row = await self._db.get(CloudCreditPackage, package_id)
        if not row:
            return None
        for key, value in fields.items():
            setattr(row, key, value)
        await self._db.flush()
        return row


class CloudCreditPurchaseRepository:
    def __init__(self, db: AsyncSession) -> None:
        self._db = db

    async def get_by_session_id(self, session_id: str) -> Optional[CloudCreditPurchase]:
        result = await self._db.execute(
            select(CloudCreditPurchase).where(CloudCreditPurchase.stripe_checkout_session_id == session_id)
        )
        return result.scalar_one_or_none()

    async def create(self, **fields) -> CloudCreditPurchase:
        row = CloudCreditPurchase(id=uuid4(), **fields)
        self._db.add(row)
        await self._db.flush()
        return row


class CloudCreditLedgerRepository:
    def __init__(self, db: AsyncSession) -> None:
        self._db = db

    async def acquire_lock(self, workspace_id: UUID) -> None:
        await self._db.execute(
            text("SELECT pg_advisory_xact_lock(:ns, hashtext(:ws))"),
            {"ns": 43, "ws": f"cloud_credits:{workspace_id}"},
        )

    async def sum_balance(self, workspace_id: UUID) -> int:
        result = await self._db.execute(
            select(sa_func.coalesce(sa_func.sum(CloudCreditLedger.millicredits_delta), 0)).where(
                CloudCreditLedger.workspace_id == workspace_id
            )
        )
        return int(result.scalar() or 0)

    async def sum_consumption_since(self, workspace_id: UUID, since: datetime) -> int:
        result = await self._db.execute(
            select(sa_func.coalesce(sa_func.sum(CloudCreditLedger.millicredits_delta), 0)).where(
                CloudCreditLedger.workspace_id == workspace_id,
                CloudCreditLedger.entry_type == "consumption",
                CloudCreditLedger.created_at >= since,
            )
        )
        return int(result.scalar() or 0)

    async def list_by_source(self, workspace_id: UUID, source_type: str, source_id: str) -> list[CloudCreditLedger]:
        result = await self._db.execute(
            select(CloudCreditLedger)
            .where(CloudCreditLedger.workspace_id == workspace_id)
            .where(CloudCreditLedger.source_type == source_type)
            .where(CloudCreditLedger.source_id == source_id)
            .order_by(CloudCreditLedger.created_at, CloudCreditLedger.id)
        )
        return list(result.scalars().all())

    async def add_entry(
        self,
        *,
        workspace_id: UUID,
        entry_type: str,
        millicredits_delta: int,
        source_type: Optional[str] = None,
        source_id: Optional[str] = None,
        purchase_id: Optional[UUID] = None,
        project_id: Optional[UUID] = None,
        description: Optional[str] = None,
        metadata: Optional[dict] = None,
        allow_zero: bool = False,
    ) -> CloudCreditLedger:
        if millicredits_delta == 0 and not allow_zero:
            raise ValueError("millicredits_delta must be non-zero")
        balance = await self.sum_balance(workspace_id) + millicredits_delta
        row = CloudCreditLedger(
            id=uuid4(),
            workspace_id=workspace_id,
            entry_type=entry_type,
            millicredits_delta=millicredits_delta,
            balance_after=balance,
            source_type=source_type,
            source_id=source_id,
            purchase_id=purchase_id,
            project_id=project_id,
            description=description,
            metadata_json=metadata,
        )
        self._db.add(row)
        await self._db.flush()
        return row


class CloudUsageConversionRateRepository:
    def __init__(self, db: AsyncSession) -> None:
        self._db = db

    async def list_active(self) -> dict[str, Decimal]:
        result = await self._db.execute(
            select(CloudUsageConversionRate).where(CloudUsageConversionRate.is_active.is_(True))
        )
        return {row.metric_key: Decimal(row.credits_per_unit) for row in result.scalars().all()}


class WorkspaceCloudCreditPolicyRepository:
    def __init__(self, db: AsyncSession) -> None:
        self._db = db

    async def get(self, workspace_id: UUID) -> Optional[WorkspaceCloudCreditPolicy]:
        return await self._db.get(WorkspaceCloudCreditPolicy, workspace_id)

    async def get_or_default(self, workspace_id: UUID) -> WorkspaceCloudCreditPolicy:
        row = await self.get(workspace_id)
        if row:
            return row
        row = WorkspaceCloudCreditPolicy(workspace_id=workspace_id)
        self._db.add(row)
        await self._db.flush()
        return row

    async def mark_public_blocked(self, workspace_id: UUID, blocked: bool) -> WorkspaceCloudCreditPolicy:
        row = await self.get_or_default(workspace_id)
        row.public_blocked_at = datetime.now(timezone.utc) if blocked else None
        await self._db.flush()
        return row


class ProjectBillingExtrasRepository:
    def __init__(self, db: AsyncSession) -> None:
        self._db = db

    async def get(self, project_id: UUID) -> Optional[ProjectBillingExtras]:
        return await self._db.get(ProjectBillingExtras, project_id)

    async def get_or_default(self, project_id: UUID) -> ProjectBillingExtras:
        row = await self.get(project_id)
        if row:
            return row
        row = ProjectBillingExtras(
            project_id=project_id,
            markup_percentage=DEFAULT_MARKUP_PCT,
            fixed_monthly_fee_credits=DEFAULT_FIXED_MONTHLY_FEE_CREDITS,
        )
        self._db.add(row)
        await self._db.flush()
        return row


class WorkspaceWalletAlertRepository:
    def __init__(self, db: AsyncSession) -> None:
        self._db = db

    async def get_or_create(self, workspace_id: UUID, wallet_type: str, period_start: date) -> WorkspaceWalletAlert:
        result = await self._db.execute(
            select(WorkspaceWalletAlert).where(
                WorkspaceWalletAlert.workspace_id == workspace_id,
                WorkspaceWalletAlert.wallet_type == wallet_type,
                WorkspaceWalletAlert.period_start == period_start,
            )
        )
        row = result.scalar_one_or_none()
        if row:
            return row
        row = WorkspaceWalletAlert(
            workspace_id=workspace_id,
            wallet_type=wallet_type,
            period_start=period_start,
        )
        self._db.add(row)
        await self._db.flush()
        return row