import uuid
from typing import Optional

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

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


class ProofRepository:
    """Repository for proof-related database operations."""

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

    async def create(
        self,
        campaign_id: uuid.UUID,
        proof_name: str,
        channel: Optional[str] = None,
        sub_channel: Optional[str] = None,
        proof_type: Optional[str] = None,
        workfront_id: Optional[str] = None,
        created_by: Optional[uuid.UUID] = None,
    ) -> Proof:
        """Create a new proof."""
        proof = Proof(
            campaign_id=campaign_id,
            proof_name=proof_name,
            channel=channel,
            sub_channel=sub_channel,
            proof_type=proof_type,
            workfront_id=workfront_id,
            created_by=created_by,
        )
        self.session.add(proof)
        await self.session.flush()
        return proof

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

    async def get_by_campaign_and_name(
        self,
        campaign_id: uuid.UUID,
        proof_name: str,
    ) -> Optional[Proof]:
        """Get proof by campaign ID and proof name."""
        result = await self.session.execute(
            select(Proof)
            .options(selectinload(Proof.versions))
            .where(Proof.campaign_id == campaign_id, Proof.proof_name == proof_name)
        )
        return result.scalar_one_or_none()

    async def list_by_campaign(
        self,
        campaign_id: uuid.UUID,
    ) -> list[Proof]:
        """List all proofs for a campaign."""
        result = await self.session.execute(
            select(Proof)
            .options(selectinload(Proof.versions))
            .where(Proof.campaign_id == campaign_id)
            .order_by(Proof.created_at.desc())
        )
        return list(result.scalars().all())

    async def delete(self, proof_id: uuid.UUID) -> bool:
        """Delete a proof and all its versions."""
        proof = await self.get_by_id(proof_id)
        if not proof:
            return False

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

    async def create_version(
        self,
        proof_id: uuid.UUID,
        version: int,
        file_storage_key: Optional[str] = None,
        thumbnail_url: Optional[str] = None,
        agent_review: Optional[dict] = None,
        overall_status: Optional[str] = None,
        workfront_id: Optional[str] = None,
    ) -> ProofVersion:
        """Create a new version of a proof."""
        proof_version = ProofVersion(
            proof_id=proof_id,
            version=version,
            file_storage_key=file_storage_key,
            thumbnail_url=thumbnail_url,
            agent_review=agent_review,
            overall_status=overall_status,
            workfront_id=workfront_id,
        )
        self.session.add(proof_version)
        await self.session.flush()
        return proof_version

    async def get_version(
        self,
        proof_id: uuid.UUID,
        version: int,
    ) -> Optional[ProofVersion]:
        """Get a specific version of a proof."""
        result = await self.session.execute(
            select(ProofVersion)
            .where(
                ProofVersion.proof_id == proof_id,
                ProofVersion.version == version,
            )
        )
        return result.scalar_one_or_none()

    async def get_version_by_id(
        self,
        version_id: uuid.UUID,
    ) -> Optional[ProofVersion]:
        """Get proof version by ID."""
        result = await self.session.execute(
            select(ProofVersion)
            .options(selectinload(ProofVersion.proof).selectinload(Proof.campaign))
            .where(ProofVersion.id == version_id)
        )
        return result.scalar_one_or_none()

    async def get_latest_version_number(self, proof_id: uuid.UUID) -> int:
        """Get the latest version number for a proof."""
        result = await self.session.execute(
            select(ProofVersion.version)
            .where(ProofVersion.proof_id == proof_id)
            .order_by(ProofVersion.version.desc())
            .limit(1)
        )
        version = result.scalar_one_or_none()
        return version if version else 0

    async def get_or_create_proof(
        self,
        campaign_id: uuid.UUID,
        proof_name: str,
        channel: Optional[str] = None,
        sub_channel: Optional[str] = None,
        proof_type: Optional[str] = None,
        created_by: Optional[uuid.UUID] = None,
    ) -> tuple[Proof, bool]:
        """Get existing proof or create new one. Returns (proof, is_new)."""
        proof = await self.get_by_campaign_and_name(campaign_id, proof_name)
        if proof:
            return proof, False

        proof = await self.create(
            campaign_id=campaign_id,
            proof_name=proof_name,
            channel=channel,
            sub_channel=sub_channel,
            proof_type=proof_type,
            created_by=created_by,
        )
        return proof, True

    async def add_version_with_review(
        self,
        campaign_id: uuid.UUID,
        proof_name: str,
        channel: Optional[str],
        sub_channel: Optional[str],
        proof_type: Optional[str],
        file_storage_key: Optional[str],
        thumbnail_url: Optional[str],
        agent_review: dict,
        overall_status: str,
        created_by: Optional[uuid.UUID] = None,
    ) -> tuple[Proof, ProofVersion]:
        """Create or get proof and add a new version with review results."""
        proof, is_new = await self.get_or_create_proof(
            campaign_id=campaign_id,
            proof_name=proof_name,
            channel=channel,
            sub_channel=sub_channel,
            proof_type=proof_type,
            created_by=created_by,
        )

        latest_version = await self.get_latest_version_number(proof.id)
        new_version_number = latest_version + 1

        # Get campaign's workfront_id to use as base for proof workfront IDs
        campaign_result = await self.session.execute(
            select(Campaign).where(Campaign.id == campaign_id)
        )
        campaign = campaign_result.scalar_one_or_none()
        campaign_workfront_id = campaign.workfront_id if campaign else None

        # Use campaign's workfront_id as base, or proof's existing workfront_id,
        # or generate random one as fallback
        import random
        base_id = proof.workfront_id or campaign_workfront_id or f"#WF_{random.randint(10000, 99999)}"
        version_workfront_id = f"{base_id.split('-V')[0]}-V{new_version_number}"

        if not proof.workfront_id:
            proof.workfront_id = base_id

        version = await self.create_version(
            proof_id=proof.id,
            version=new_version_number,
            file_storage_key=file_storage_key,
            thumbnail_url=thumbnail_url,
            agent_review=agent_review,
            overall_status=overall_status,
            workfront_id=version_workfront_id,
        )

        return proof, version
