"""Workspaces domain - data access layer."""
from __future__ import annotations
from datetime import datetime, timezone
from typing import Optional
from uuid import UUID

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

from src.core.ref_tokens import generate_public_ref
from src.core.security.hashing import digest_token_for_lookup, verify_token_hash
from src.domain.auth.models import User
from src.domain.workspaces.models import (
    Role,
    Workspace,
    WorkspaceInvite,
    WorkspaceMembership,
    WorkspaceSettings,
    ROLE_PERMISSIONS,
)


class WorkspaceRepository:
    """Workspace CRUD."""

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

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

    async def get_by_slug(self, slug: str) -> Optional[Workspace]:
        result = await self._db.execute(select(Workspace).where(Workspace.slug == slug))
        return result.scalar_one_or_none()

    async def slug_exists(self, slug: str) -> bool:
        result = await self._db.execute(select(Workspace.id).where(Workspace.slug == slug))
        return result.scalar_one_or_none() is not None

    async def create(
        self,
        slug: str,
        name: str,
        owner_id: UUID,
        selected_plan_slug: Optional[str] = None,
        onboarding_status: Optional[str] = None,
        skip_plan_quota_limits: bool = False,
    ) -> Workspace:
        ws = Workspace(
            slug=slug,
            name=name,
            owner_id=owner_id,
            selected_plan_slug=selected_plan_slug,
            onboarding_status=onboarding_status,
            skip_plan_quota_limits=skip_plan_quota_limits,
        )
        self._db.add(ws)
        await self._db.flush()
        if not ws.tenant_ref:
            for _ in range(40):
                cand = "t" + generate_public_ref(7, prefix="")
                taken = await self._db.execute(
                    select(Workspace.id).where(Workspace.tenant_ref == cand).limit(1)
                )
                if taken.scalar_one_or_none() is None:
                    ws.tenant_ref = cand
                    break
            else:
                raise RuntimeError("Could not allocate unique tenant_ref")
            await self._db.flush()
        await self._db.refresh(ws)
        return ws

    async def list_for_user(self, user_id: UUID) -> list[Workspace]:
        """List workspaces for user. Deterministic order: owner first, then by created_at asc, id for tie-break."""
        result = await self._db.execute(
            select(Workspace)
            .join(WorkspaceMembership, Workspace.id == WorkspaceMembership.workspace_id)
            .where(WorkspaceMembership.user_id == user_id)
            .where(Workspace.deleted_at.is_(None))
            .order_by(
                case((Workspace.owner_id == user_id, 0), else_=1).asc(),  # owner first
                Workspace.created_at.asc(),
                Workspace.id.asc(),
            )
        )
        return list(result.scalars().all())

    async def update(
        self,
        workspace_id: UUID,
        name: Optional[str] = None,
        selected_plan_slug: Optional[str] = None,
        onboarding_status: Optional[str] = None,
    ) -> Optional[Workspace]:
        ws = await self.get_by_id(workspace_id)
        if not ws:
            return None
        if name is not None:
            ws.name = name
        if selected_plan_slug is not None:
            ws.selected_plan_slug = selected_plan_slug
        if onboarding_status is not None:
            ws.onboarding_status = onboarding_status
        if any(x is not None for x in (name, selected_plan_slug, onboarding_status)):
            await self._db.flush()
        return ws

    async def soft_delete(self, workspace_id: UUID) -> None:
        ws = await self.get_by_id(workspace_id)
        if ws:
            ws.deleted_at = datetime.now(timezone.utc)
            await self._db.flush()

    async def update_billing_checkout_tracking(
        self,
        workspace_id: UUID,
        *,
        session_id: Optional[str] = None,
        checkout_at: Optional[datetime] = None,
        checkout_return: Optional[str] = None,
    ) -> None:
        ws = await self.get_by_id(workspace_id)
        if not ws:
            return
        if session_id is not None:
            ws.billing_last_checkout_session_id = session_id
        if checkout_at is not None:
            ws.billing_last_checkout_at = checkout_at
        if checkout_return is not None:
            ws.billing_last_checkout_return = checkout_return or None
        await self._db.flush()

    async def clear_billing_checkout_return(self, workspace_id: UUID) -> None:
        ws = await self.get_by_id(workspace_id)
        if ws:
            ws.billing_last_checkout_return = None
            await self._db.flush()

    async def clear_billing_checkout_tracking(self, workspace_id: UUID) -> None:
        """Clear pending Stripe checkout markers (e.g. after choosing free tier)."""
        ws = await self.get_by_id(workspace_id)
        if ws:
            ws.billing_last_checkout_session_id = None
            ws.billing_last_checkout_at = None
            ws.billing_last_checkout_return = None
            await self._db.flush()


class RoleRepository:
    """Role CRUD."""

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

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

    async def get_system_role_by_name(self, name: str) -> Optional[Role]:
        result = await self._db.execute(
            select(Role).where(Role.name == name).where(Role.workspace_id.is_(None))
        )
        return result.scalar_one_or_none()

    async def get_or_create_system_role(self, name: str) -> Role:
        """Get or create system role.

        Re-syncs ``permissions`` on every call for an already-materialized role: permission
        constants evolve (e.g. ``codegen.run.desktop``) but system-role rows are created once and
        otherwise never touched again, so a stale row would silently miss new permissions without
        this self-healing check.
        """
        perms = ROLE_PERMISSIONS.get(name.lower(), [])
        role = await self.get_system_role_by_name(name)
        if role:
            if role.permissions != perms:
                role.permissions = perms
                await self._db.flush()
            return role
        role = Role(name=name, workspace_id=None, permissions=perms, is_system=True)
        self._db.add(role)
        await self._db.flush()
        await self._db.refresh(role)
        return role


class MembershipRepository:
    """Workspace membership CRUD."""

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

    async def get(self, workspace_id: UUID, user_id: UUID) -> Optional[WorkspaceMembership]:
        result = await self._db.execute(
            select(WorkspaceMembership)
            .where(WorkspaceMembership.workspace_id == workspace_id)
            .where(WorkspaceMembership.user_id == user_id)
        )
        return result.scalar_one_or_none()

    async def has_permission(self, workspace_id: UUID, user_id: UUID, permission: str) -> bool:
        """Check if user has permission in workspace."""
        result = await self._db.execute(
            select(WorkspaceMembership, Role)
            .join(Role, WorkspaceMembership.role_id == Role.id)
            .where(WorkspaceMembership.workspace_id == workspace_id)
            .where(WorkspaceMembership.user_id == user_id)
        )
        row = result.one_or_none()
        if not row:
            return False
        _, role = row
        return permission in role.permissions if role else False

    async def create(
        self,
        workspace_id: UUID,
        user_id: UUID,
        role_id: UUID,
        invited_by: Optional[UUID] = None,
    ) -> WorkspaceMembership:
        m = WorkspaceMembership(
            workspace_id=workspace_id,
            user_id=user_id,
            role_id=role_id,
            invited_by=invited_by,
        )
        self._db.add(m)
        await self._db.flush()
        await self._db.refresh(m)
        return m

    async def update_role(self, workspace_id: UUID, user_id: UUID, role_id: UUID) -> bool:
        m = await self.get(workspace_id, user_id)
        if m:
            m.role_id = role_id
            await self._db.flush()
            return True
        return False

    async def delete(self, workspace_id: UUID, user_id: UUID) -> bool:
        m = await self.get(workspace_id, user_id)
        if m:
            await self._db.delete(m)
            await self._db.flush()
            return True
        return False

    async def list_members(
        self, workspace_id: UUID
    ) -> list[tuple[WorkspaceMembership, Optional[Role], User]]:
        result = await self._db.execute(
            select(WorkspaceMembership, Role, User)
            .join(User, WorkspaceMembership.user_id == User.id)
            .outerjoin(Role, WorkspaceMembership.role_id == Role.id)
            .where(WorkspaceMembership.workspace_id == workspace_id)
        )
        return list(result.all())


class WorkspaceInviteRepository:
    """Workspace invite CRUD."""

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

    async def create(
        self,
        workspace_id: UUID,
        email: str,
        role_id: UUID,
        token_hash: str,
        expires_at: datetime,
        invited_by: UUID,
    ) -> WorkspaceInvite:
        inv = WorkspaceInvite(
            workspace_id=workspace_id,
            email=email.lower(),
            role_id=role_id,
            token_hash=token_hash,
            expires_at=expires_at,
            invited_by=invited_by,
        )
        self._db.add(inv)
        await self._db.flush()
        await self._db.refresh(inv)
        return inv

    async def resolve_pending_invite_token(
        self, workspace_id: UUID, plain_token: str
    ) -> Optional[WorkspaceInvite]:
        """Resolve a pending invite by raw token from the invite email/link.

        Stores SHA256 digest (see ``digest_token_for_lookup``) for O(1) lookup.
        Legacy rows used Argon2 (random salt); those are verified only among pending
        invites for the workspace in the URL (bounded set).
        """
        now = datetime.now(timezone.utc)
        digest = digest_token_for_lookup(plain_token)

        def _pending_filters(q):
            q = q.where(WorkspaceInvite.accepted_at.is_(None)).where(WorkspaceInvite.expires_at > now)
            if hasattr(WorkspaceInvite, "revoked_at"):
                q = q.where(WorkspaceInvite.revoked_at.is_(None))
            if hasattr(WorkspaceInvite, "status"):
                q = q.where(WorkspaceInvite.status == "pending")
            return q

        q_digest = _pending_filters(select(WorkspaceInvite)).where(WorkspaceInvite.token_hash == digest)
        result = await self._db.execute(q_digest)
        inv = result.scalar_one_or_none()
        if inv:
            return inv

        q_legacy = _pending_filters(select(WorkspaceInvite)).where(
            WorkspaceInvite.workspace_id == workspace_id,
            WorkspaceInvite.token_hash.like("$argon2%"),
        )
        result = await self._db.execute(q_legacy)
        for row in result.scalars().all():
            if verify_token_hash(plain_token, row.token_hash):
                return row
        return None

    async def count_pending_for_workspace(self, workspace_id: UUID) -> int:
        """
        Count pending invites for plan limit enforcement.
        Semantics: invites with status=pending, accepted_at IS NULL, revoked_at IS NULL,
        expires_at > now(). Excludes accepted and revoked invites.
        """
        q = (
            select(func.count())
            .select_from(WorkspaceInvite)
            .where(WorkspaceInvite.workspace_id == workspace_id)
            .where(WorkspaceInvite.accepted_at.is_(None))
            .where(WorkspaceInvite.expires_at > datetime.now(timezone.utc))
        )
        if hasattr(WorkspaceInvite, "revoked_at"):
            q = q.where(WorkspaceInvite.revoked_at.is_(None))
        if hasattr(WorkspaceInvite, "status"):
            q = q.where(WorkspaceInvite.status == "pending")
        result = await self._db.execute(q)
        return result.scalar() or 0

    async def has_pending_invite_for_email(self, email: str) -> bool:
        """
        True if there is at least one pending invite for this email.
        Used to detect invite-first flow: do not create unnecessary workspace on signup.
        """
        q = (
            select(WorkspaceInvite.id)
            .where(WorkspaceInvite.email == email.lower())
            .where(WorkspaceInvite.accepted_at.is_(None))
            .where(WorkspaceInvite.expires_at > datetime.now(timezone.utc))
        )
        if hasattr(WorkspaceInvite, "revoked_at"):
            q = q.where(WorkspaceInvite.revoked_at.is_(None))
        if hasattr(WorkspaceInvite, "status"):
            q = q.where(WorkspaceInvite.status == "pending")
        result = await self._db.execute(q)
        return result.scalar_one_or_none() is not None

    async def list_pending_invites_for_email(
        self, email: str
    ) -> list[tuple[WorkspaceInvite, Workspace, Optional[Role]]]:
        """Pending invites for an email (non-deleted workspace). Newest first."""
        now = datetime.now(timezone.utc)
        q = (
            select(WorkspaceInvite, Workspace, Role)
            .join(Workspace, WorkspaceInvite.workspace_id == Workspace.id)
            .outerjoin(Role, WorkspaceInvite.role_id == Role.id)
            .where(WorkspaceInvite.email == email.lower())
            .where(WorkspaceInvite.accepted_at.is_(None))
            .where(WorkspaceInvite.expires_at > now)
            .where(WorkspaceInvite.revoked_at.is_(None))
            .where(WorkspaceInvite.status == "pending")
            .where(Workspace.deleted_at.is_(None))
            .order_by(WorkspaceInvite.created_at.desc())
        )
        result = await self._db.execute(q)
        return list(result.all())

    async def list_pending_for_workspace(self, workspace_id: UUID) -> list[tuple[WorkspaceInvite, Optional[Role]]]:
        """Pending = not accepted, not revoked, not expired, status pending. Newest first."""
        now = datetime.now(timezone.utc)
        q = (
            select(WorkspaceInvite, Role)
            .outerjoin(Role, WorkspaceInvite.role_id == Role.id)
            .where(WorkspaceInvite.workspace_id == workspace_id)
            .where(WorkspaceInvite.accepted_at.is_(None))
            .where(WorkspaceInvite.expires_at > now)
            .where(WorkspaceInvite.revoked_at.is_(None))
            .where(WorkspaceInvite.status == "pending")
            .order_by(WorkspaceInvite.created_at.desc())
        )
        result = await self._db.execute(q)
        return list(result.all())

    async def get_pending_for_workspace(self, invite_id: UUID, workspace_id: UUID) -> Optional[WorkspaceInvite]:
        """Get one active invite while enforcing its workspace boundary."""
        now = datetime.now(timezone.utc)
        q = (
            select(WorkspaceInvite)
            .where(WorkspaceInvite.id == invite_id)
            .where(WorkspaceInvite.workspace_id == workspace_id)
            .where(WorkspaceInvite.accepted_at.is_(None))
            .where(WorkspaceInvite.expires_at > now)
            .where(WorkspaceInvite.revoked_at.is_(None))
            .where(WorkspaceInvite.status == "pending")
        )
        result = await self._db.execute(q)
        return result.scalar_one_or_none()

    async def revoke(self, invite_id: UUID, workspace_id: UUID) -> bool:
        invite = await self.get_pending_for_workspace(invite_id, workspace_id)
        if not invite:
            return False
        invite.revoked_at = datetime.now(timezone.utc)
        invite.status = "revoked"
        await self._db.flush()
        return True

    async def mark_accepted(self, invite_id: UUID) -> None:
        result = await self._db.execute(select(WorkspaceInvite).where(WorkspaceInvite.id == invite_id))
        inv = result.scalar_one_or_none()
        if inv:
            inv.accepted_at = datetime.now(timezone.utc)
            inv.status = "accepted"
            await self._db.flush()


class WorkspaceSettingsRepository:
    """Workspace settings CRUD."""

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

    async def get_or_create(self, workspace_id: UUID) -> WorkspaceSettings:
        result = await self._db.execute(
            select(WorkspaceSettings).where(WorkspaceSettings.workspace_id == workspace_id)
        )
        settings = result.scalar_one_or_none()
        if not settings:
            settings = WorkspaceSettings(workspace_id=workspace_id)
            self._db.add(settings)
            await self._db.flush()
            await self._db.refresh(settings)
        return settings

    async def set_local_execution_enabled(self, workspace_id: UUID, enabled: bool):
        settings = await self.get_or_create(workspace_id)
        settings.local_execution_enabled = enabled
        await self._db.flush()
        return settings