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

from typing import Optional
from uuid import UUID, uuid4

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

from src.domain.connectors.models import (
    Connector,
    ConnectorAction,
    ConnectorConnection,
    ConnectorPermission,
    ConnectorTrigger,
    WorkspaceConnectorDefault,
)


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

    async def list_connectors(self) -> list[Connector]:
        result = await self._db.execute(select(Connector).order_by(Connector.key))
        return list(result.scalars().all())

    async def get_connector_by_key(self, key: str) -> Optional[Connector]:
        result = await self._db.execute(select(Connector).where(Connector.key == key))
        return result.scalar_one_or_none()

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

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

    async def list_actions_for_connector(self, connector_id: UUID) -> list[ConnectorAction]:
        result = await self._db.execute(
            select(ConnectorAction)
            .where(ConnectorAction.connector_id == connector_id)
            .order_by(ConnectorAction.action_key)
        )
        return list(result.scalars().all())

    async def list_triggers_for_connector(self, connector_id: UUID) -> list[ConnectorTrigger]:
        result = await self._db.execute(
            select(ConnectorTrigger)
            .where(ConnectorTrigger.connector_id == connector_id)
            .order_by(ConnectorTrigger.trigger_key)
        )
        return list(result.scalars().all())

    async def upsert_catalog_connector(self, key: str, **fields) -> Connector:
        row = await self.get_connector_by_key(key)
        if row:
            for k, v in fields.items():
                setattr(row, k, v)
        else:
            row = Connector(id=uuid4(), key=key, **fields)
            self._db.add(row)
        await self._db.flush()
        await self._db.refresh(row)
        return row

    async def upsert_catalog_action(
        self, connector_id: UUID, action_key: str, **fields
    ) -> ConnectorAction:
        result = await self._db.execute(
            select(ConnectorAction).where(
                ConnectorAction.connector_id == connector_id,
                ConnectorAction.action_key == action_key,
            )
        )
        row = result.scalar_one_or_none()
        if row:
            for k, v in fields.items():
                setattr(row, k, v)
        else:
            row = ConnectorAction(
                id=uuid4(),
                connector_id=connector_id,
                action_key=action_key,
                **fields,
            )
            self._db.add(row)
        await self._db.flush()
        return row

    async def upsert_catalog_trigger(
        self, connector_id: UUID, trigger_key: str, **fields
    ) -> ConnectorTrigger:
        result = await self._db.execute(
            select(ConnectorTrigger).where(
                ConnectorTrigger.connector_id == connector_id,
                ConnectorTrigger.trigger_key == trigger_key,
            )
        )
        row = result.scalar_one_or_none()
        if row:
            for k, v in fields.items():
                setattr(row, k, v)
        else:
            row = ConnectorTrigger(
                id=uuid4(),
                connector_id=connector_id,
                trigger_key=trigger_key,
                **fields,
            )
            self._db.add(row)
        await self._db.flush()
        return row

    async def list_connections_for_project(self, project_id: UUID) -> list[ConnectorConnection]:
        result = await self._db.execute(
            select(ConnectorConnection)
            .where(ConnectorConnection.project_id == project_id)
            .order_by(ConnectorConnection.name)
        )
        return list(result.scalars().all())

    async def list_active_email_connections(
        self, project_id: UUID, *, connector_keys: tuple[str, ...]
    ) -> list[tuple[ConnectorConnection, Connector]]:
        result = await self._db.execute(
            select(ConnectorConnection, Connector)
            .join(Connector, Connector.id == ConnectorConnection.connector_id)
            .where(
                ConnectorConnection.project_id == project_id,
                ConnectorConnection.status == "active",
                Connector.category == "email",
                Connector.key.in_(connector_keys),
            )
            .order_by(ConnectorConnection.created_at)
        )
        return list(result.all())

    async def list_active_connections_for_connector_keys(
        self, project_id: UUID, *, connector_keys: tuple[str, ...]
    ) -> list[tuple[ConnectorConnection, Connector]]:
        result = await self._db.execute(
            select(ConnectorConnection, Connector)
            .join(Connector, Connector.id == ConnectorConnection.connector_id)
            .where(
                ConnectorConnection.project_id == project_id,
                ConnectorConnection.status == "active",
                Connector.key.in_(connector_keys),
            )
            .order_by(ConnectorConnection.created_at)
        )
        return list(result.all())

    async def list_active_connections_for_connector_key(
        self, key: str
    ) -> list[tuple[ConnectorConnection, Connector]]:
        result = await self._db.execute(
            select(ConnectorConnection, Connector)
            .join(Connector, Connector.id == ConnectorConnection.connector_id)
            .where(
                Connector.key == key,
                ConnectorConnection.status == "active",
            )
            .order_by(ConnectorConnection.created_at)
        )
        return list(result.all())

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

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

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

    async def create_connection(self, **fields) -> ConnectorConnection:
        row = ConnectorConnection(id=uuid4(), **fields)
        self._db.add(row)
        await self._db.flush()
        await self._db.refresh(row)
        return row

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

    async def delete_connection(self, row: ConnectorConnection) -> None:
        await self._db.delete(row)
        await self._db.flush()

    async def list_permissions(self, connection_id: UUID) -> list[ConnectorPermission]:
        result = await self._db.execute(
            select(ConnectorPermission)
            .where(ConnectorPermission.connection_id == connection_id)
            .order_by(ConnectorPermission.action_slug)
        )
        return list(result.scalars().all())

    async def get_permission(
        self, connection_id: UUID, action_slug: str
    ) -> Optional[ConnectorPermission]:
        result = await self._db.execute(
            select(ConnectorPermission).where(
                ConnectorPermission.connection_id == connection_id,
                ConnectorPermission.action_slug == action_slug,
            )
        )
        return result.scalar_one_or_none()

    async def upsert_permission(
        self,
        connection_id: UUID,
        action_slug: str,
        *,
        mode: str,
        policy_json: Optional[dict] = None,
    ) -> ConnectorPermission:
        row = await self.get_permission(connection_id, action_slug)
        if row:
            row.mode = mode
            row.policy_json = policy_json
        else:
            row = ConnectorPermission(
                id=uuid4(),
                connection_id=connection_id,
                action_slug=action_slug,
                mode=mode,
                policy_json=policy_json,
            )
            self._db.add(row)
        await self._db.flush()
        # `created_at`/`updated_at` are server-generated columns.  Refresh the
        # row before returning it because the caller may serialize it after
        # external Cloud synchronization; otherwise SQLAlchemy can attempt an
        # implicit async refresh during response serialization and raise
        # MissingGreenlet.
        await self._db.refresh(row)
        return row


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

    async def list_for_workspace(self, workspace_id: UUID) -> list[WorkspaceConnectorDefault]:
        result = await self._db.execute(
            select(WorkspaceConnectorDefault)
            .where(WorkspaceConnectorDefault.workspace_id == workspace_id)
            .order_by(WorkspaceConnectorDefault.connector_key)
        )
        return list(result.scalars().all())

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

    async def upsert(
        self, workspace_id: UUID, connector_key: str, **fields
    ) -> WorkspaceConnectorDefault:
        row = await self.get(workspace_id, connector_key)
        if row:
            for k, v in fields.items():
                setattr(row, k, v)
        else:
            row = WorkspaceConnectorDefault(
                id=uuid4(),
                workspace_id=workspace_id,
                connector_key=connector_key,
                **fields,
            )
            self._db.add(row)
        await self._db.flush()
        return row

    async def delete(self, row: WorkspaceConnectorDefault) -> None:
        await self._db.delete(row)
        await self._db.flush()