"""Data access for user Azure DevOps connections and project mirrors."""
from __future__ import annotations

from datetime import datetime
from typing import Optional
from uuid import UUID

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

from src.domain.azure_user_mirror.models import (
    ProjectUserAzureMirrorTarget,
    WorkspaceUserAzureConnection,
)


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

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

    async def get_by_workspace_and_org(
        self, workspace_id: UUID, azure_org_url: str
    ) -> Optional[WorkspaceUserAzureConnection]:
        r = await self._db.execute(
            select(WorkspaceUserAzureConnection).where(
                WorkspaceUserAzureConnection.workspace_id == workspace_id,
                WorkspaceUserAzureConnection.azure_org_url == azure_org_url.rstrip("/"),
            )
        )
        return r.scalar_one_or_none()

    async def list_for_workspace(self, workspace_id: UUID) -> list[WorkspaceUserAzureConnection]:
        r = await self._db.execute(
            select(WorkspaceUserAzureConnection)
            .where(WorkspaceUserAzureConnection.workspace_id == workspace_id)
            .order_by(WorkspaceUserAzureConnection.created_at.desc())
        )
        return list(r.scalars().all())

    async def upsert(
        self,
        *,
        workspace_id: UUID,
        azure_org_url: str,
        username: str,
        display_name: Optional[str],
        pat_ciphertext: str,
        created_by: Optional[UUID],
    ) -> WorkspaceUserAzureConnection:
        org_url = azure_org_url.rstrip("/")
        existing = await self.get_by_workspace_and_org(workspace_id, org_url)
        if existing:
            existing.username = username
            existing.display_name = display_name
            existing.auth_type = "pat"
            existing.pat_ciphertext = pat_ciphertext
            existing.access_token_ciphertext = None
            existing.refresh_token_ciphertext = None
            existing.token_expires_at = None
            existing.revoked_at = None
            await self._db.flush()
            await self._db.refresh(existing)
            return existing
        row = WorkspaceUserAzureConnection(
            workspace_id=workspace_id,
            azure_org_url=org_url,
            username=username,
            display_name=display_name,
            auth_type="pat",
            pat_ciphertext=pat_ciphertext,
            created_by=created_by,
        )
        self._db.add(row)
        await self._db.flush()
        await self._db.refresh(row)
        return row

    async def upsert_oauth(
        self,
        *,
        workspace_id: UUID,
        azure_org_url: str,
        username: str,
        display_name: Optional[str],
        access_token_ciphertext: str,
        refresh_token_ciphertext: Optional[str],
        token_expires_at: Optional[datetime],
        created_by: Optional[UUID],
        empty_pat_ciphertext: str,
    ) -> WorkspaceUserAzureConnection:
        org_url = azure_org_url.rstrip("/")
        existing = await self.get_by_workspace_and_org(workspace_id, org_url)
        if existing:
            existing.username = username
            existing.display_name = display_name
            existing.auth_type = "oauth"
            existing.pat_ciphertext = empty_pat_ciphertext
            existing.access_token_ciphertext = access_token_ciphertext
            existing.refresh_token_ciphertext = refresh_token_ciphertext
            existing.token_expires_at = token_expires_at
            existing.revoked_at = None
            await self._db.flush()
            await self._db.refresh(existing)
            return existing
        row = WorkspaceUserAzureConnection(
            workspace_id=workspace_id,
            azure_org_url=org_url,
            username=username,
            display_name=display_name,
            auth_type="oauth",
            pat_ciphertext=empty_pat_ciphertext,
            access_token_ciphertext=access_token_ciphertext,
            refresh_token_ciphertext=refresh_token_ciphertext,
            token_expires_at=token_expires_at,
            created_by=created_by,
        )
        self._db.add(row)
        await self._db.flush()
        await self._db.refresh(row)
        return row

    async def update_oauth_tokens(
        self,
        row_id: UUID,
        *,
        access_token_ciphertext: str,
        refresh_token_ciphertext: Optional[str],
        token_expires_at: Optional[datetime],
    ) -> Optional[WorkspaceUserAzureConnection]:
        row = await self.get_by_id(row_id)
        if not row:
            return None
        row.access_token_ciphertext = access_token_ciphertext
        if refresh_token_ciphertext is not None:
            row.refresh_token_ciphertext = refresh_token_ciphertext
        row.token_expires_at = token_expires_at
        await self._db.flush()
        await self._db.refresh(row)
        return row

    async def delete(self, row_id: UUID) -> bool:
        row = await self.get_by_id(row_id)
        if not row:
            return False
        await self._db.delete(row)
        await self._db.flush()
        return True


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

    async def get_by_project_id(self, project_id: UUID) -> Optional[ProjectUserAzureMirrorTarget]:
        r = await self._db.execute(
            select(ProjectUserAzureMirrorTarget).where(
                ProjectUserAzureMirrorTarget.project_id == project_id
            )
        )
        return r.scalar_one_or_none()

    async def list_by_target_repo(
        self,
        target_repo_full_name: str,
    ) -> list[ProjectUserAzureMirrorTarget]:
        r = await self._db.execute(
            select(ProjectUserAzureMirrorTarget).where(
                ProjectUserAzureMirrorTarget.target_repo_full_name == target_repo_full_name
            )
        )
        return list(r.scalars().all())

    async def upsert(
        self,
        *,
        project_id: UUID,
        workspace_user_azure_connection_id: UUID,
        target_repo_full_name: str,
        target_default_branch: str,
        enabled: bool,
    ) -> ProjectUserAzureMirrorTarget:
        existing = await self.get_by_project_id(project_id)
        if existing:
            existing.workspace_user_azure_connection_id = workspace_user_azure_connection_id
            existing.target_repo_full_name = target_repo_full_name
            existing.target_default_branch = target_default_branch
            existing.enabled = enabled
            await self._db.flush()
            await self._db.refresh(existing)
            return existing
        row = ProjectUserAzureMirrorTarget(
            project_id=project_id,
            workspace_user_azure_connection_id=workspace_user_azure_connection_id,
            target_repo_full_name=target_repo_full_name,
            target_default_branch=target_default_branch,
            enabled=enabled,
        )
        self._db.add(row)
        await self._db.flush()
        await self._db.refresh(row)
        return row

    async def update_push_result(
        self,
        project_id: UUID,
        *,
        last_push_sha: Optional[str] = None,
        last_error: Optional[str] = None,
    ) -> Optional[ProjectUserAzureMirrorTarget]:
        row = await self.get_by_project_id(project_id)
        if not row:
            return None
        if last_push_sha is not None:
            row.last_push_sha = last_push_sha
        row.last_error = last_error
        await self._db.flush()
        return row

    async def disable_by_target_repo(self, target_repo_full_name: str, reason: str) -> int:
        rows = await self.list_by_target_repo(target_repo_full_name)
        for row in rows:
            row.enabled = False
            row.last_error = reason
        await self._db.flush()
        return len(rows)

    async def rename_target_repo(self, old_full_name: str, new_full_name: str) -> int:
        rows = await self.list_by_target_repo(old_full_name)
        for row in rows:
            row.target_repo_full_name = new_full_name
            row.last_error = None
        await self._db.flush()
        return len(rows)

    async def update_webhook_id(
        self,
        project_id: UUID,
        *,
        repo_id: Optional[str] = None,
        webhook_id: Optional[str] = None,
    ) -> Optional[ProjectUserAzureMirrorTarget]:
        row = await self.get_by_project_id(project_id)
        if not row:
            return None
        if repo_id is not None:
            row.target_repo_id = repo_id
        row.target_repo_webhook_id = webhook_id
        await self._db.flush()
        return row

    async def list_by_connection_id(
        self, connection_id: UUID
    ) -> list[ProjectUserAzureMirrorTarget]:
        r = await self._db.execute(
            select(ProjectUserAzureMirrorTarget).where(
                ProjectUserAzureMirrorTarget.workspace_user_azure_connection_id == connection_id
            )
        )
        return list(r.scalars().all())

    async def delete_by_project_id(self, project_id: UUID) -> bool:
        row = await self.get_by_project_id(project_id)
        if not row:
            return False
        await self._db.delete(row)
        await self._db.flush()
        return True