import uuid
from typing import Optional

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

from app.models.models import Campaign, Proof, ProofVersion


class CampaignRepository:
    """Repository for campaign-related database operations."""

    def __init__(self, session: AsyncSession):
        self.session = session

    async def create(
        self,
        name: str,
        workfront_id: Optional[str] = None,
        client_lead: Optional[str] = None,
        agency_lead: Optional[str] = None,
        brand_guidelines: Optional[str] = None,
        agency_id: Optional[uuid.UUID] = None,
        created_by: Optional[uuid.UUID] = None,
    ) -> Campaign:
        """Create a new campaign."""
        campaign = Campaign(
            name=name,
            workfront_id=workfront_id,
            client_lead=client_lead,
            agency_lead=agency_lead,
            brand_guidelines=brand_guidelines,
            agency_id=agency_id,
            created_by=created_by,
        )
        self.session.add(campaign)
        await self.session.flush()
        return campaign

    async def get_by_id(self, campaign_id: uuid.UUID) -> Optional[Campaign]:
        """Get campaign by ID with related data."""
        result = await self.session.execute(
            select(Campaign)
            .options(
                selectinload(Campaign.agency),
                selectinload(Campaign.created_by_user),
                selectinload(Campaign.proofs).selectinload(Proof.versions),
            )
            .where(Campaign.id == campaign_id)
        )
        return result.scalar_one_or_none()

    async def get_by_name(self, name: str) -> Optional[Campaign]:
        """Get campaign by name."""
        result = await self.session.execute(
            select(Campaign).where(Campaign.name == name)
        )
        return result.scalar_one_or_none()

    async def list_all(
        self,
        agency_id: Optional[uuid.UUID] = None,
        limit: int = 100,
        offset: int = 0,
    ) -> list[Campaign]:
        """List campaigns, optionally filtered by agency."""
        query = select(Campaign).options(
            selectinload(Campaign.agency),
            selectinload(Campaign.proofs),
        )
        if agency_id:
            query = query.where(Campaign.agency_id == agency_id)
        query = query.order_by(Campaign.updated_at.desc()).limit(limit).offset(offset)
        result = await self.session.execute(query)
        return list(result.scalars().all())

    async def update(
        self,
        campaign_id: uuid.UUID,
        **kwargs,
    ) -> Optional[Campaign]:
        """Update campaign fields."""
        campaign = await self.get_by_id(campaign_id)
        if not campaign:
            return None

        for key, value in kwargs.items():
            if hasattr(campaign, key) and value is not None:
                setattr(campaign, key, value)

        await self.session.flush()
        return campaign

    async def delete(self, campaign_id: uuid.UUID) -> bool:
        """Delete a campaign and all related proofs."""
        campaign = await self.get_by_id(campaign_id)
        if not campaign:
            return False

        await self.session.delete(campaign)
        await self.session.flush()
        return True

    async def get_with_proof_counts(
        self,
        agency_id: Optional[uuid.UUID] = None,
    ) -> list[dict]:
        """Get campaigns with proof counts."""
        query = (
            select(
                Campaign,
                func.count(Proof.id).label("proof_count"),
            )
            .outerjoin(Proof)
            .group_by(Campaign.id)
        )
        if agency_id:
            query = query.where(Campaign.agency_id == agency_id)
        query = query.order_by(Campaign.updated_at.desc())

        result = await self.session.execute(query)
        rows = result.all()

        campaigns_with_counts = []
        for row in rows:
            campaign = row[0]
            proof_count = row[1]
            campaigns_with_counts.append({
                "campaign": campaign,
                "proof_count": proof_count,
            })
        return campaigns_with_counts

    async def get_analytics(
        self,
        agency_id: Optional[uuid.UUID] = None,
    ) -> dict:
        """Get analytics data for campaigns."""
        # Base query for proof versions with status counts
        query = (
            select(
                func.count(ProofVersion.id).label("total"),
                func.count(ProofVersion.id).filter(ProofVersion.overall_status == "Passed").label("passed"),
                func.count(ProofVersion.id).filter(ProofVersion.overall_status == "Failed").label("failed"),
                func.count(ProofVersion.id).filter(ProofVersion.overall_status == "Analysis Error").label("errors"),
                func.count(ProofVersion.id).filter(ProofVersion.overall_status == "Requires Manual Legal Review").label("legal_review"),
            )
            .select_from(ProofVersion)
            .join(Proof)
            .join(Campaign)
        )
        if agency_id:
            query = query.where(Campaign.agency_id == agency_id)

        result = await self.session.execute(query)
        row = result.one()

        return {
            "total_reviews": row.total,
            "passed": row.passed,
            "failed": row.failed,
            "errors": row.errors,
            "legal_review": row.legal_review,
        }
