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 Agency, Campaign, Proof, ProofVersion, User


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()
        # Re-fetch to get fresh data with eager-loaded relationships
        return await self.get_by_id(campaign_id)

    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)
            .options(selectinload(Campaign.agency))
            .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,
        }

    async def get_export_rows(self, agency_id: Optional[uuid.UUID] = None) -> list[dict]:
        """Return flat rows for CSV export: Campaign → Proof → ProofVersion joined with Agency and User."""
        query = (
            select(
                Campaign.name.label("campaign_name"),
                Campaign.workfront_id.label("campaign_workfront_id"),
                Campaign.client_lead,
                Campaign.agency_lead,
                Campaign.brand_guidelines,
                Campaign.status.label("campaign_status"),
                Campaign.created_at.label("campaign_created_at"),
                Agency.name.label("agency_name"),
                Proof.proof_name,
                Proof.channel,
                Proof.sub_channel,
                Proof.proof_type,
                Proof.workfront_id.label("proof_workfront_id"),
                Proof.created_at.label("proof_created_at"),
                User.name.label("proof_created_by_name"),
                User.email.label("proof_created_by_email"),
                ProofVersion.version,
                ProofVersion.overall_status,
                ProofVersion.workfront_id.label("version_workfront_id"),
                ProofVersion.created_at.label("version_created_at"),
                ProofVersion.agent_review,
            )
            .select_from(Campaign)
            .join(Agency, Campaign.agency_id == Agency.id, isouter=True)
            .join(Proof, Proof.campaign_id == Campaign.id, isouter=True)
            .join(User, Proof.created_by == User.id, isouter=True)
            .join(ProofVersion, ProofVersion.proof_id == Proof.id, isouter=True)
        )
        if agency_id:
            query = query.where(Campaign.agency_id == agency_id)
        query = query.order_by(Campaign.name, Proof.proof_name, ProofVersion.version)

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

        export_rows = []
        for row in rows:
            agent_review = row.agent_review or {}
            export_rows.append({
                "Campaign Name": row.campaign_name or "",
                "Campaign Workfront ID": row.campaign_workfront_id or "",
                "Client Lead": row.client_lead or "",
                "Agency Lead": row.agency_lead or "",
                "Brand Guidelines": row.brand_guidelines or "",
                "Campaign Status": row.campaign_status or "",
                "Agency": row.agency_name or "",
                "Campaign Created": row.campaign_created_at.strftime("%Y-%m-%d %H:%M:%S") if row.campaign_created_at else "",
                "Proof Name": row.proof_name or "",
                "Channel": row.channel or "",
                "Sub-Channel": row.sub_channel or "",
                "Proof Type": row.proof_type or "",
                "Proof Workfront ID": row.proof_workfront_id or "",
                "Proof Created By": row.proof_created_by_name or "",
                "Proof Created By Email": row.proof_created_by_email or "",
                "Proof Created": row.proof_created_at.strftime("%Y-%m-%d %H:%M:%S") if row.proof_created_at else "",
                "Version": str(row.version) if row.version is not None else "",
                "Overall Status": row.overall_status or "",
                "Version Workfront ID": row.version_workfront_id or "",
                "Version Created": row.version_created_at.strftime("%Y-%m-%d %H:%M:%S") if row.version_created_at else "",
                "Legal RAG": (agent_review.get("legalAgentReview") or {}).get("ragStatus") or "",
                "Brand RAG": (agent_review.get("brandAgentReview") or {}).get("ragStatus") or "",
                "Channel Best Practices RAG": (agent_review.get("channelBestPracticesAgentReview") or {}).get("ragStatus") or "",
                "Channel Tech Specs RAG": (agent_review.get("channelTechSpecsAgentReview") or {}).get("ragStatus") or "",
                "Lead Agent Summary": agent_review.get("leadAgentSummary") or "",
            })
        return export_rows

    async def get_analytics_by_agency(self) -> list[dict]:
        """Get analytics data grouped by agency."""
        query = (
            select(
                Agency.id.label("agency_id"),
                Agency.name.label("agency_name"),
                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)
            .join(Agency, Campaign.agency_id == Agency.id)
            .group_by(Agency.id, Agency.name)
            .order_by(func.count(ProofVersion.id).desc())
        )

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

        return [
            {
                "agency_id": row.agency_id,
                "agency_name": row.agency_name,
                "total_reviews": row.total,
                "passed": row.passed,
                "failed": row.failed,
                "errors": row.errors,
                "legal_review": row.legal_review,
            }
            for row in rows
        ]
