"""Auth domain - data access layer (CRUD only)."""
from __future__ import annotations

from datetime import datetime, timezone
from typing import Optional
from uuid import UUID

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

from src.domain.auth.models import MagicLinkToken, PendingRegistration, Session, User


class UserRepository:
    """User CRUD operations."""

    def __init__(self, db: AsyncSession):
        self._db = db

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

    async def get_by_email(self, email: str) -> Optional[User]:
        """Get user by email."""
        result = await self._db.execute(select(User).where(User.email == email.lower()))
        return result.scalar_one_or_none()

    async def get_by_discord_user_id(self, discord_user_id: str) -> Optional[User]:
        result = await self._db.execute(
            select(User).where(User.discord_user_id == discord_user_id)
        )
        return result.scalar_one_or_none()

    async def list_by_ids(self, user_ids: list[UUID]) -> list[User]:
        """Return users in the requested order for platform-admin batch actions."""
        if not user_ids:
            return []
        result = await self._db.execute(select(User).where(User.id.in_(user_ids)))
        users_by_id = {user.id: user for user in result.scalars().all()}
        return [users_by_id[user_id] for user_id in user_ids if user_id in users_by_id]

    async def exists(self, user_id: UUID) -> bool:
        """Check if user exists."""
        result = await self._db.execute(select(User.id).where(User.id == user_id))
        return result.scalar_one_or_none() is not None

    async def create(
        self,
        email: str,
        display_name: Optional[str] = None,
        first_name: Optional[str] = None,
        last_name: Optional[str] = None,
        phone: Optional[str] = None,
        company: Optional[str] = None,
    ) -> User:
        """Create user."""
        dn = display_name or (f"{first_name or ''} {last_name or ''}".strip() or None)
        user = User(
            email=email.lower(),
            display_name=dn,
            first_name=first_name,
            last_name=last_name,
            phone=phone,
            company=company,
        )
        self._db.add(user)
        await self._db.flush()
        await self._db.refresh(user)
        return user

    async def update_profile(
        self,
        user_id: UUID,
        *,
        first_name: Optional[str] = None,
        last_name: Optional[str] = None,
        phone: Optional[str] = None,
        company: Optional[str] = None,
        display_name: Optional[str] = None,
        avatar_url: Optional[str] = None,
        ui_theme: Optional[str] = None,
    ) -> Optional[User]:
        """Update user profile fields (only set non-None)."""
        user = await self.get_by_id(user_id)
        if not user:
            return None
        if first_name is not None:
            user.first_name = first_name
        if last_name is not None:
            user.last_name = last_name
        if phone is not None:
            user.phone = phone
        if company is not None:
            user.company = company
        if display_name is not None:
            user.display_name = display_name
        if avatar_url is not None:
            user.avatar_url = avatar_url
        if ui_theme is not None:
            user.ui_theme = ui_theme
        await self._db.flush()
        await self._db.refresh(user)
        return user

    async def set_discord_connection(
        self,
        user_id: UUID,
        *,
        discord_user_id: str,
        discord_username: Optional[str],
    ) -> Optional[User]:
        user = await self.get_by_id(user_id)
        if not user:
            return None
        user.discord_user_id = discord_user_id
        user.discord_username = discord_username
        user.discord_connected_at = datetime.now(timezone.utc)
        await self._db.flush()
        await self._db.refresh(user)
        return user

    async def update_email_verified(self, user_id: UUID) -> None:
        """Set email_verified_at on first successful magic-link verification."""
        user = await self.get_by_id(user_id)
        if user and user.email_verified_at is None:
            user.email_verified_at = datetime.now(timezone.utc)
            await self._db.flush()

    async def update_last_login(self, user_id: UUID, at: datetime) -> None:
        """Update last_login_at after login."""
        user = await self.get_by_id(user_id)
        if user:
            user.last_login_at = at
            await self._db.flush()

    async def count_for_admin_list(self, search: Optional[str] = None) -> int:
        q = select(func.count()).select_from(User)
        if search and search.strip():
            term = f"%{search.strip()}%"
            q = q.where(User.email.ilike(term))
        result = await self._db.execute(q)
        return int(result.scalar_one() or 0)

    async def list_for_admin(
        self,
        *,
        limit: int = 50,
        offset: int = 0,
        search: Optional[str] = None,
    ) -> list[User]:
        q = select(User).order_by(User.created_at.desc())
        if search and search.strip():
            term = f"%{search.strip()}%"
            q = q.where(User.email.ilike(term))
        q = q.limit(min(max(limit, 1), 200)).offset(max(offset, 0))
        result = await self._db.execute(q)
        return list(result.scalars().all())


class SessionRepository:
    """Session CRUD operations."""

    def __init__(self, db: AsyncSession):
        self._db = db

    async def get_by_id(self, session_id: UUID) -> Optional[Session]:
        """Get session by ID."""
        result = await self._db.execute(select(Session).where(Session.id == session_id))
        return result.scalar_one_or_none()

    async def create(
        self,
        user_id: UUID,
        refresh_token_hash: str,
        expires_at: datetime,
        device_info: Optional[dict] = None,
        ip_address: Optional[str] = None,
        user_agent: Optional[str] = None,
    ) -> Session:
        """Create session."""
        session = Session(
            user_id=user_id,
            refresh_token_hash=refresh_token_hash,
            expires_at=expires_at,
            device_info=device_info,
            ip_address=ip_address,
            user_agent=user_agent,
        )
        self._db.add(session)
        await self._db.flush()
        await self._db.refresh(session)
        return session

    async def revoke(self, session_id: UUID) -> None:
        """Revoke session."""
        session = await self.get_by_id(session_id)
        if session:
            session.revoked_at = datetime.now(timezone.utc)
            await self._db.flush()


class PendingRegistrationRepository:
    """Pending signups - promoted to users on first successful email verify."""

    def __init__(self, db: AsyncSession):
        self._db = db

    async def get_valid_by_email(self, email: str) -> Optional[PendingRegistration]:
        """Get non-expired pending registration."""
        result = await self._db.execute(
            select(PendingRegistration)
            .where(PendingRegistration.email == email.lower())
            .where(PendingRegistration.expires_at > datetime.now(timezone.utc))
        )
        return result.scalar_one_or_none()

    async def get_by_email(self, email: str) -> Optional[PendingRegistration]:
        """Get pending by email (any expiry)."""
        result = await self._db.execute(
            select(PendingRegistration).where(PendingRegistration.email == email.lower())
        )
        return result.scalar_one_or_none()

    async def create_or_update(
        self,
        email: str,
        *,
        first_name: Optional[str] = None,
        last_name: Optional[str] = None,
        phone: Optional[str] = None,
        company: Optional[str] = None,
        workspace_name: Optional[str] = None,
        plan_slug: Optional[str] = None,
        magic_link_sent_at: Optional[datetime] = None,
        expires_at: Optional[datetime] = None,
    ) -> PendingRegistration:
        from datetime import timedelta
        existing = await self.get_by_email(email)
        now = datetime.now(timezone.utc)
        exp = expires_at or (now + timedelta(hours=24))
        if existing:
            existing.first_name = first_name or existing.first_name
            existing.last_name = last_name or existing.last_name
            existing.phone = phone or existing.phone
            existing.company = company or existing.company
            existing.workspace_name = workspace_name or existing.workspace_name
            existing.plan_slug = plan_slug or existing.plan_slug
            existing.magic_link_sent_at = magic_link_sent_at or now
            existing.updated_at = now
            existing.expires_at = exp
            await self._db.flush()
            await self._db.refresh(existing)
            return existing
        pending = PendingRegistration(
            email=email.lower(),
            first_name=first_name,
            last_name=last_name,
            phone=phone,
            company=company,
            workspace_name=workspace_name,
            plan_slug=plan_slug,
            magic_link_sent_at=magic_link_sent_at or now,
            expires_at=exp,
        )
        self._db.add(pending)
        await self._db.flush()
        await self._db.refresh(pending)
        return pending

    async def get_by_id(self, pending_id: UUID) -> Optional[PendingRegistration]:
        result = await self._db.execute(
            select(PendingRegistration).where(PendingRegistration.id == pending_id)
        )
        return result.scalar_one_or_none()

    async def update_checkout(
        self,
        pending_id: UUID,
        *,
        checkout_session_id: str,
        checkout_expires_at: datetime,
        stripe_customer_id: str,
    ) -> None:
        pending = await self.get_by_id(pending_id)
        if pending:
            pending.checkout_session_id = checkout_session_id
            pending.checkout_expires_at = checkout_expires_at
            pending.stripe_customer_id = stripe_customer_id
            await self._db.flush()

    async def mark_paid(
        self,
        pending_id: UUID,
        *,
        paid_at: datetime,
        stripe_subscription_id: Optional[str] = None,
    ) -> None:
        pending = await self.get_by_id(pending_id)
        if pending:
            pending.paid_at = paid_at
            if stripe_subscription_id:
                pending.stripe_subscription_id = stripe_subscription_id
            await self._db.flush()

    async def get_abandoned_checkouts(self, *, expired_before: datetime) -> list[PendingRegistration]:
        """Pending registrations with an expired checkout that were never paid."""
        result = await self._db.execute(
            select(PendingRegistration)
            .where(PendingRegistration.checkout_session_id.isnot(None))
            .where(PendingRegistration.paid_at.is_(None))
            .where(PendingRegistration.checkout_expires_at < expired_before)
        )
        return list(result.scalars().all())

    async def delete(self, pending_id: UUID) -> None:
        result = await self._db.execute(select(PendingRegistration).where(PendingRegistration.id == pending_id))
        p = result.scalar_one_or_none()
        if p:
            await self._db.delete(p)
            await self._db.flush()


class MagicLinkRepository:
    """Magic link token CRUD operations."""

    def __init__(self, db: AsyncSession):
        self._db = db

    async def create(
        self,
        email: str,
        token_hash: str,
        expires_at: datetime,
        ip_address: Optional[str] = None,
    ) -> MagicLinkToken:
        """Create magic link token."""
        token = MagicLinkToken(
            email=email.lower(),
            token_hash=token_hash,
            expires_at=expires_at,
            ip_address=ip_address,
        )
        self._db.add(token)
        await self._db.flush()
        await self._db.refresh(token)
        return token

    async def get_valid_token(self, token_hash: str) -> Optional[MagicLinkToken]:
        """Get a magic link token that remains valid through its expiry.

        Magic links are intentionally reusable during their short lifetime so
        email-security scanners cannot prevent the recipient from signing in.
        """
        result = await self._db.execute(
            select(MagicLinkToken)
            .where(MagicLinkToken.token_hash == token_hash)
            .where(MagicLinkToken.expires_at > datetime.now(timezone.utc))
        )
        return result.scalar_one_or_none()

    async def mark_used(self, token_id: UUID) -> None:
        """Mark token as used."""
        result = await self._db.execute(select(MagicLinkToken).where(MagicLinkToken.id == token_id))
        token = result.scalar_one_or_none()
        if token:
            token.used_at = datetime.now(timezone.utc)
            await self._db.flush()