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 User, Agency, UserChangeLog


class UserRepository:
    """Repository for user-related database operations."""

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

    async def get_by_id(self, user_id: uuid.UUID) -> Optional[User]:
        """Get user by ID."""
        result = await self.session.execute(
            select(User)
            .options(selectinload(User.agency))
            .where(User.id == user_id)
        )
        return result.scalar_one_or_none()

    async def get_by_azure_oid(self, azure_ad_oid: str) -> Optional[User]:
        """Get user by Azure AD Object ID."""
        result = await self.session.execute(
            select(User)
            .options(selectinload(User.agency))
            .where(User.azure_ad_oid == azure_ad_oid)
        )
        return result.scalar_one_or_none()

    async def get_or_create_from_azure(
        self,
        azure_ad_oid: str,
        email: str,
        name: str,
        agency_name: Optional[str] = None
    ) -> User:
        """Get existing user or create new one from Azure AD claims."""
        user = await self.get_by_azure_oid(azure_ad_oid)
        if user:
            # Sync email from Azure AD if not yet stored
            if not user.email and email:
                user.email = email
                await self.session.flush()
            return user

        # Create new user
        agency = None
        if agency_name:
            agency = await self.get_or_create_agency(agency_name)

        user = User(
            azure_ad_oid=azure_ad_oid,
            email=email,
            name=name,
            role="basic_user",
            agency_id=agency.id if agency else None,
        )
        self.session.add(user)
        await self.session.flush()

        # Log user creation
        await self.create_change_log(
            user_id=user.id,
            changed_by_id=None,
            change_type="user_created",
            field_changed=None,
            old_value=None,
            new_value="basic_user",
        )

        return user

    async def get_or_create_agency(self, name: str) -> Agency:
        """Get existing agency or create new one."""
        result = await self.session.execute(
            select(Agency).where(Agency.name == name)
        )
        agency = result.scalar_one_or_none()
        if agency:
            return agency

        agency = Agency(name=name)
        self.session.add(agency)
        await self.session.flush()
        return agency

    async def list_all(self) -> list[User]:
        """List all users."""
        result = await self.session.execute(
            select(User).options(selectinload(User.agency))
        )
        return list(result.scalars().all())

    async def update_role(self, user_id: uuid.UUID, role: str) -> Optional[User]:
        """Update user role."""
        user = await self.get_by_id(user_id)
        if user:
            user.role = role
            await self.session.flush()
        return user

    async def update_user(
        self,
        user_id: uuid.UUID,
        role: Optional[str] = None,
        agency_id: Optional[uuid.UUID] = ...,  # type: ignore[assignment]
    ) -> Optional[User]:
        """Update user role and/or agency. Pass agency_id=None to unassign."""
        user = await self.get_by_id(user_id)
        if not user:
            return None
        if role is not None:
            user.role = role
        # Use sentinel (...) to distinguish "not provided" from "set to None"
        if agency_id is not ...:
            user.agency_id = agency_id
        await self.session.flush()
        self.session.expire(user)  # Clear cached state so relationship reloads
        return await self.get_by_id(user_id)

    async def list_agencies(self) -> list[Agency]:
        """List all agencies."""
        result = await self.session.execute(select(Agency))
        return list(result.scalars().all())

    async def create_agency(self, name: str) -> Agency:
        """Create a new agency."""
        agency = Agency(name=name)
        self.session.add(agency)
        await self.session.flush()
        return agency

    async def create_change_log(
        self,
        user_id: uuid.UUID,
        changed_by_id: Optional[uuid.UUID],
        change_type: str,
        field_changed: Optional[str],
        old_value: Optional[str],
        new_value: Optional[str],
    ) -> UserChangeLog:
        """Insert a user change log entry."""
        entry = UserChangeLog(
            user_id=user_id,
            changed_by_id=changed_by_id,
            change_type=change_type,
            field_changed=field_changed,
            old_value=old_value,
            new_value=new_value,
        )
        self.session.add(entry)
        await self.session.flush()
        return entry

    async def get_change_logs(self, user_id: uuid.UUID, limit: int = 100) -> list[UserChangeLog]:
        """Fetch change history for a user, most recent first."""
        result = await self.session.execute(
            select(UserChangeLog)
            .options(selectinload(UserChangeLog.changed_by))
            .where(UserChangeLog.user_id == user_id)
            .order_by(UserChangeLog.created_at.desc())
            .limit(limit)
        )
        return list(result.scalars().all())
