"""Revisions domain - data access layer (private to this domain)."""
from __future__ import annotations
from typing import Optional
from uuid import UUID

from sqlalchemy import func as sa_func, select, text
from sqlalchemy.ext.asyncio import AsyncSession

from src.domain.revisions.models import ProjectRevision, RevisionTag

# Dedicated advisory-lock namespace for (project_id, revision_number) allocation.
_REVISION_NUMBER_LOCK_NS = 87421001


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

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

    async def get_latest(self, project_id: UUID) -> Optional[ProjectRevision]:
        result = await self._db.execute(
            select(ProjectRevision)
            .where(ProjectRevision.project_id == project_id)
            .order_by(ProjectRevision.revision_number.desc())
            .limit(1)
        )
        return result.scalar_one_or_none()

    async def get_by_git_sha(self, project_id: UUID, git_sha: str) -> Optional[ProjectRevision]:
        result = await self._db.execute(
            select(ProjectRevision)
            .where(ProjectRevision.project_id == project_id)
            .where(ProjectRevision.git_sha == git_sha)
            .limit(1)
        )
        return result.scalar_one_or_none()

    async def get_by_source(self, project_id: UUID, source: str) -> Optional[ProjectRevision]:
        result = await self._db.execute(
            select(ProjectRevision)
            .where(ProjectRevision.project_id == project_id)
            .where(ProjectRevision.source == source)
            .order_by(ProjectRevision.revision_number.desc())
            .limit(1)
        )
        return result.scalar_one_or_none()

    async def get_by_source_and_source_id(
        self, project_id: UUID, source: str, source_id: UUID
    ) -> Optional[ProjectRevision]:
        result = await self._db.execute(
            select(ProjectRevision)
            .where(ProjectRevision.project_id == project_id)
            .where(ProjectRevision.source == source)
            .where(ProjectRevision.source_id == source_id)
            .limit(1)
        )
        return result.scalar_one_or_none()

    async def list_for_project(self, project_id: UUID) -> list[ProjectRevision]:
        result = await self._db.execute(
            select(ProjectRevision)
            .where(ProjectRevision.project_id == project_id)
            .order_by(ProjectRevision.revision_number.desc())
        )
        return list(result.scalars().all())

    async def _lock_revision_allocation(self, project_id: UUID) -> None:
        """Serialize max(revision_number)+1 for one project until the transaction ends."""
        await self._db.execute(
            text("SELECT pg_advisory_xact_lock(:ns, hashtext(:pid))"),
            {"ns": _REVISION_NUMBER_LOCK_NS, "pid": str(project_id)},
        )

    async def _next_revision_number(self, project_id: UUID) -> int:
        result = await self._db.execute(
            select(sa_func.max(ProjectRevision.revision_number)).where(
                ProjectRevision.project_id == project_id
            )
        )
        current_max = result.scalar_one_or_none()
        return (current_max or 0) + 1

    async def create(
        self,
        project_id: UUID,
        git_sha: str,
        source: str,
        source_id: Optional[UUID] = None,
        commit_message: Optional[str] = None,
        created_by: Optional[UUID] = None,
    ) -> ProjectRevision:
        await self._lock_revision_allocation(project_id)
        num = await self._next_revision_number(project_id)
        rev = ProjectRevision(
            project_id=project_id,
            revision_number=num,
            git_sha=git_sha,
            commit_message=commit_message,
            created_by=created_by,
            source=source,
            source_id=source_id,
        )
        self._db.add(rev)
        await self._db.flush()
        await self._db.refresh(rev)
        return rev


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

    async def create(
        self, revision_id: UUID, tag: str, created_by: Optional[UUID] = None
    ) -> RevisionTag:
        t = RevisionTag(revision_id=revision_id, tag=tag, created_by=created_by)
        self._db.add(t)
        await self._db.flush()
        await self._db.refresh(t)
        return t

    async def list_for_revision(self, revision_id: UUID) -> list[RevisionTag]:
        result = await self._db.execute(
            select(RevisionTag).where(RevisionTag.revision_id == revision_id)
        )
        return list(result.scalars().all())