"""cloud_infra domain — data access layer."""
from __future__ import annotations

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

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

from src.domain.cloud_infra.models import (
    CFAccount,
    CFAccountStatus,
    CFAccountTier,
    CloudInstance,
    CloudInstanceStatus,
    CloudShard,
    CloudShardMigrationStatus,
    CloudShardStatus,
    CloudShardType,
    ProjectCloudAssignment,
    ProjectShardStatus,
    WorkspaceCloudAssignment,
    WorkspaceCloudAssignmentStatus,
)


class CFAccountRepository:
    def __init__(self, db: AsyncSession) -> None:
        self._db = db

    async def create(
        self,
        *,
        account_id: str,
        account_name: str,
        tier: CFAccountTier,
        max_workspaces: int,
        config_enc: str,
        metadata: Optional[dict] = None,
    ) -> CFAccount:
        row = CFAccount(
            id=uuid4(),
            account_id=account_id,
            account_name=account_name,
            tier=tier,
            max_workspaces=max_workspaces,
            config_enc=config_enc,
            metadata_=metadata,
        )
        self._db.add(row)
        await self._db.flush()
        await self._db.refresh(row)
        return row

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

    async def get_by_cf_account_id(self, cf_account_id: str) -> Optional[CFAccount]:
        result = await self._db.execute(
            select(CFAccount).where(CFAccount.account_id == cf_account_id)
        )
        return result.scalar_one_or_none()

    async def list_active(self, tier: Optional[CFAccountTier] = None) -> list[CFAccount]:
        q = select(CFAccount).where(CFAccount.status == CFAccountStatus.active)
        if tier:
            q = q.where(CFAccount.tier == tier)
        result = await self._db.execute(q)
        return list(result.scalars().all())

    async def list_all(self) -> list[CFAccount]:
        result = await self._db.execute(select(CFAccount))
        return list(result.scalars().all())

    async def update(self, row: CFAccount, **fields) -> CFAccount:
        for k, v in fields.items():
            setattr(row, k, v)
        await self._db.flush()
        await self._db.refresh(row)
        return row

    async def increment_workspace_count(self, account_id: UUID, delta: int = 1) -> None:
        await self._db.execute(
            update(CFAccount)
            .where(CFAccount.id == account_id)
            .values(workspace_count=CFAccount.workspace_count + delta)
        )


class CloudInstanceRepository:
    def __init__(self, db: AsyncSession) -> None:
        self._db = db

    async def create(
        self,
        *,
        instance_id: str,
        cf_account_id: UUID,
        tier: CFAccountTier,
        gateway_url: str,
        internal_token_enc: str,
        metadata: Optional[dict] = None,
    ) -> CloudInstance:
        row = CloudInstance(
            id=uuid4(),
            instance_id=instance_id,
            cf_account_id=cf_account_id,
            tier=tier,
            gateway_url=gateway_url,
            internal_token_enc=internal_token_enc,
            metadata_=metadata,
        )
        self._db.add(row)
        await self._db.flush()
        await self._db.refresh(row)
        return row

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

    async def get_by_instance_id(self, instance_id: str) -> Optional[CloudInstance]:
        result = await self._db.execute(
            select(CloudInstance).where(CloudInstance.instance_id == instance_id)
        )
        return result.scalar_one_or_none()

    async def list_active(self, tier: Optional[CFAccountTier] = None) -> list[CloudInstance]:
        q = select(CloudInstance).where(CloudInstance.status == CloudInstanceStatus.active)
        if tier:
            q = q.where(CloudInstance.tier == tier)
        result = await self._db.execute(q)
        return list(result.scalars().all())

    async def list_all(self) -> list[CloudInstance]:
        result = await self._db.execute(select(CloudInstance))
        return list(result.scalars().all())

    async def update(self, row: CloudInstance, **fields) -> CloudInstance:
        for k, v in fields.items():
            setattr(row, k, v)
        await self._db.flush()
        await self._db.refresh(row)
        return row

    async def touch_heartbeat(self, instance_id: str) -> None:
        await self._db.execute(
            update(CloudInstance)
            .where(CloudInstance.instance_id == instance_id)
            .values(last_heartbeat_at=datetime.now(timezone.utc))
        )


class WorkspaceCloudAssignmentRepository:
    def __init__(self, db: AsyncSession) -> None:
        self._db = db

    async def get_active_for_workspace(
        self, workspace_id: UUID
    ) -> Optional[WorkspaceCloudAssignment]:
        result = await self._db.execute(
            select(WorkspaceCloudAssignment).where(
                WorkspaceCloudAssignment.workspace_id == workspace_id,
                WorkspaceCloudAssignment.status == WorkspaceCloudAssignmentStatus.active,
            )
        )
        return result.scalar_one_or_none()

    async def create(
        self,
        *,
        workspace_id: UUID,
        cloud_instance_id: UUID,
    ) -> WorkspaceCloudAssignment:
        row = WorkspaceCloudAssignment(
            id=uuid4(),
            workspace_id=workspace_id,
            cloud_instance_id=cloud_instance_id,
            status=WorkspaceCloudAssignmentStatus.active,
        )
        self._db.add(row)
        await self._db.flush()
        return row

    async def release(self, row: WorkspaceCloudAssignment) -> WorkspaceCloudAssignment:
        row.status = WorkspaceCloudAssignmentStatus.released
        row.released_at = datetime.now(timezone.utc)
        await self._db.flush()
        return row

    async def reassign(
        self,
        row: WorkspaceCloudAssignment,
        *,
        cloud_instance_id: UUID,
    ) -> WorkspaceCloudAssignment:
        """Move an active workspace assignment without creating a second row.

        ``workspace_id`` is intentionally unique, including for released
        history rows, so reassignment must update the existing row in place.
        """
        row.cloud_instance_id = cloud_instance_id
        row.status = WorkspaceCloudAssignmentStatus.active
        row.assigned_at = datetime.now(timezone.utc)
        row.released_at = None
        await self._db.flush()
        return row

    async def list_for_instance(
        self, cloud_instance_id: UUID
    ) -> list[WorkspaceCloudAssignment]:
        result = await self._db.execute(
            select(WorkspaceCloudAssignment).where(
                WorkspaceCloudAssignment.cloud_instance_id == cloud_instance_id,
                WorkspaceCloudAssignment.status == WorkspaceCloudAssignmentStatus.active,
            )
        )
        return list(result.scalars().all())


class CloudShardRepository:
    def __init__(self, db: AsyncSession) -> None:
        self._db = db

    async def create(
        self,
        *,
        id: UUID,
        shard_key: str,
        region: str,
        shard_type: CloudShardType,
        cf_account_id: UUID,
        d1_database_id: Optional[str] = None,
        r2_bucket_name: Optional[str] = None,
        dispatch_namespace: Optional[str] = None,
        max_projects: int = 10000,
    ) -> CloudShard:
        row = CloudShard(
            id=id,
            shard_key=shard_key,
            region=region,
            shard_type=shard_type,
            cf_account_id=cf_account_id,
            d1_database_id=d1_database_id,
            r2_bucket_name=r2_bucket_name,
            dispatch_namespace=dispatch_namespace,
            max_projects=max_projects,
        )
        self._db.add(row)
        await self._db.flush()
        await self._db.refresh(row)
        return row

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

    async def get_by_key(self, shard_key: str) -> Optional[CloudShard]:
        result = await self._db.execute(
            select(CloudShard).where(CloudShard.shard_key == shard_key)
        )
        return result.scalar_one_or_none()

    async def get_default_shared(self) -> Optional[CloudShard]:
        """Return the first accepting shared shard (used for auto-assignment)."""
        result = await self._db.execute(
            select(CloudShard)
            .where(
                CloudShard.shard_type == CloudShardType.shared,
                CloudShard.status == CloudShardStatus.active,
                CloudShard.is_accepting.is_(True),
            )
            .order_by(CloudShard.created_at)
            .limit(1)
        )
        return result.scalar_one_or_none()

    async def list_all(self) -> list[CloudShard]:
        result = await self._db.execute(select(CloudShard).order_by(CloudShard.created_at))
        return list(result.scalars().all())

    async def update(self, row: CloudShard, **fields) -> CloudShard:
        for k, v in fields.items():
            setattr(row, k, v)
        await self._db.flush()
        await self._db.refresh(row)
        return row


class ProjectCloudAssignmentRepository:
    def __init__(self, db: AsyncSession) -> None:
        self._db = db

    async def get_for_project(self, project_id: UUID) -> Optional[ProjectCloudAssignment]:
        result = await self._db.execute(
            select(ProjectCloudAssignment).where(
                ProjectCloudAssignment.project_id == project_id
            )
        )
        return result.scalar_one_or_none()

    async def create(
        self,
        *,
        project_id: UUID,
        shard_id: UUID,
    ) -> ProjectCloudAssignment:
        row = ProjectCloudAssignment(
            id=uuid4(),
            project_id=project_id,
            shard_id=shard_id,
            status=ProjectShardStatus.active,
        )
        self._db.add(row)
        await self._db.flush()
        return row

    async def update(self, row: ProjectCloudAssignment, **fields) -> ProjectCloudAssignment:
        for k, v in fields.items():
            setattr(row, k, v)
        await self._db.flush()
        return row


class CloudShardMigrationStatusRepository:
    def __init__(self, db: AsyncSession) -> None:
        self._db = db

    async def upsert(
        self,
        *,
        shard_id: UUID,
        migration_version: str,
        status: str,
        applied_at: datetime,
        error: Optional[str] = None,
        reported_by: Optional[str] = None,
    ) -> CloudShardMigrationStatus:
        result = await self._db.execute(
            select(CloudShardMigrationStatus).where(
                CloudShardMigrationStatus.shard_id == shard_id,
                CloudShardMigrationStatus.migration_version == migration_version,
            )
        )
        existing = result.scalar_one_or_none()
        if existing:
            existing.status = status
            existing.applied_at = applied_at
            existing.error = error
            existing.reported_by = reported_by
            await self._db.flush()
            return existing
        row = CloudShardMigrationStatus(
            id=uuid4(),
            shard_id=shard_id,
            migration_version=migration_version,
            status=status,
            applied_at=applied_at,
            error=error,
            reported_by=reported_by,
        )
        self._db.add(row)
        await self._db.flush()
        return row

    async def list_for_shard(self, shard_id: UUID) -> list[CloudShardMigrationStatus]:
        result = await self._db.execute(
            select(CloudShardMigrationStatus)
            .where(CloudShardMigrationStatus.shard_id == shard_id)
            .order_by(CloudShardMigrationStatus.applied_at.desc())
        )
        return list(result.scalars().all())

    async def get_latest_for_shard(self, shard_id: UUID) -> Optional[CloudShardMigrationStatus]:
        result = await self._db.execute(
            select(CloudShardMigrationStatus)
            .where(CloudShardMigrationStatus.shard_id == shard_id)
            .order_by(CloudShardMigrationStatus.applied_at.desc())
            .limit(1)
        )
        return result.scalar_one_or_none()

    async def get_latest_per_shard(self) -> list[CloudShardMigrationStatus]:
        """One latest row per shard — used for fleet drift detection."""
        from sqlalchemy import func as sqlfunc
        subq = (
            select(
                CloudShardMigrationStatus.shard_id,
                sqlfunc.max(CloudShardMigrationStatus.applied_at).label("max_at"),
            )
            .group_by(CloudShardMigrationStatus.shard_id)
            .subquery()
        )
        result = await self._db.execute(
            select(CloudShardMigrationStatus).join(
                subq,
                (CloudShardMigrationStatus.shard_id == subq.c.shard_id)
                & (CloudShardMigrationStatus.applied_at == subq.c.max_at),
            )
        )
        return list(result.scalars().all())