"""Data access for user Bitbucket OAuth 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.bitbucket_user_mirror.models import (
    ProjectUserBitbucketMirrorTarget,
    WorkspaceUserBitbucketConnection,
)


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

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

    async def get_by_workspace_and_account(
        self,
        workspace_id: UUID,
        bitbucket_account_id: str,
    ) -> Optional[WorkspaceUserBitbucketConnection]:
        r = await self._db.execute(
            select(WorkspaceUserBitbucketConnection).where(
                WorkspaceUserBitbucketConnection.workspace_id == workspace_id,
                WorkspaceUserBitbucketConnection.bitbucket_account_id == bitbucket_account_id,
            )
        )
        return r.scalar_one_or_none()

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

    async def upsert(
        self,
        *,
        workspace_id: UUID,
        bitbucket_account_id: 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],
    ) -> WorkspaceUserBitbucketConnection:
        existing = await self.get_by_workspace_and_account(workspace_id, bitbucket_account_id)
        if existing:
            existing.username = username
            existing.display_name = display_name
            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 = WorkspaceUserBitbucketConnection(
            workspace_id=workspace_id,
            bitbucket_account_id=bitbucket_account_id,
            username=username,
            display_name=display_name,
            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 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 ProjectUserBitbucketMirrorRepository:
    def __init__(self, db: AsyncSession):
        self._db = db

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

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

    async def upsert(
        self,
        *,
        project_id: UUID,
        workspace_user_bitbucket_connection_id: UUID,
        target_repo_full_name: str,
        target_default_branch: str,
        enabled: bool,
    ) -> ProjectUserBitbucketMirrorTarget:
        existing = await self.get_by_project_id(project_id)
        if existing:
            existing.workspace_user_bitbucket_connection_id = workspace_user_bitbucket_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 = ProjectUserBitbucketMirrorTarget(
            project_id=project_id,
            workspace_user_bitbucket_connection_id=workspace_user_bitbucket_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[ProjectUserBitbucketMirrorTarget]:
        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, webhook_uuid: Optional[str]
    ) -> Optional[ProjectUserBitbucketMirrorTarget]:
        row = await self.get_by_project_id(project_id)
        if not row:
            return None
        row.target_repo_webhook_uuid = webhook_uuid
        await self._db.flush()
        return row

    async def list_by_connection_id(
        self, connection_id: UUID
    ) -> list[ProjectUserBitbucketMirrorTarget]:
        r = await self._db.execute(
            select(ProjectUserBitbucketMirrorTarget).where(
                ProjectUserBitbucketMirrorTarget.workspace_user_bitbucket_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