"""SQLAlchemy repository for Integration entities."""

from __future__ import annotations

from collections.abc import Sequence
from uuid import UUID

from sqlalchemy import delete, func, select

from noteflow.domain.entities.integration import Integration, SyncRun
from noteflow.infrastructure.converters.integration_converters import (
    IntegrationConverter,
    SyncRunConverter,
)
from noteflow.infrastructure.persistence.models.integrations import (
    IntegrationModel,
    IntegrationSecretModel,
    IntegrationSyncRunModel,
)
from noteflow.infrastructure.persistence.repositories._base import (
    BaseRepository,
    DeleteByIdMixin,
    GetByIdMixin,
)


class SqlAlchemyIntegrationRepository(
    BaseRepository,
    GetByIdMixin[IntegrationModel, Integration],
    DeleteByIdMixin[IntegrationModel],
):
    """SQLAlchemy implementation of IntegrationRepository.

    Manages external service integrations and their encrypted secrets.
    Uses mixins for standardized get and delete operations.
    """

    _model_class: type[IntegrationModel] = IntegrationModel

    def _to_domain(self, model: IntegrationModel) -> Integration:
        """Convert ORM model to domain entity."""
        return IntegrationConverter.orm_to_domain(model)

    async def get(self, integration_id: UUID) -> Integration | None:
        """Retrieve an integration by ID.

        Args:
            integration_id: Integration UUID.

        Returns:
            Integration if found, None otherwise.
        """
        return await self._mixin_get_by_id(integration_id)

    async def get_by_provider(
        self,
        provider: str,
        integration_type: str | None = None,
    ) -> Integration | None:
        """Retrieve an integration by provider name.

        Args:
            provider: Provider name (stored in config['provider'] or name).
            integration_type: Optional type filter.

        Returns:
            Integration if found, None otherwise.
        """
        stmt = select(IntegrationModel).where(
            IntegrationModel.config["provider"].astext == provider,
        )
        if integration_type:
            stmt = stmt.where(IntegrationModel.type == integration_type)

        model = await self._execute_scalar(stmt)
        if model:
            return IntegrationConverter.orm_to_domain(model)

        # Fallback: check by name if provider not in config
        fallback_stmt = select(IntegrationModel).where(
            IntegrationModel.name.ilike(f"%{provider}%"),
        )
        if integration_type:
            fallback_stmt = fallback_stmt.where(IntegrationModel.type == integration_type)

        fallback_model = await self._execute_scalar(fallback_stmt)
        return IntegrationConverter.orm_to_domain(fallback_model) if fallback_model else None

    async def create(self, integration: Integration) -> Integration:
        """Persist a new integration.

        Args:
            integration: Integration to create.

        Returns:
            Created integration.
        """
        kwargs = IntegrationConverter.to_orm_kwargs(integration)
        model = IntegrationModel(**kwargs)
        await self._add_and_flush(model)
        return IntegrationConverter.orm_to_domain(model)

    async def update(self, integration: Integration) -> Integration:
        """Update an existing integration.

        Args:
            integration: Integration with updated fields.

        Returns:
            Updated integration.

        Raises:
            ValueError: If integration does not exist.
        """
        stmt = select(IntegrationModel).where(IntegrationModel.id == integration.id)
        model = await self._execute_scalar(stmt)
        if not model:
            msg = f"Integration {integration.id} not found"
            raise ValueError(msg)

        # Update fields
        model.name = integration.name
        model.type = integration.type.value
        model.status = integration.status.value
        model.config = integration.config
        model.last_sync = integration.last_sync
        model.error_message = integration.error_message
        model.updated_at = integration.updated_at

        await self._session.flush()
        return IntegrationConverter.orm_to_domain(model)

    async def delete(self, integration_id: UUID) -> bool:
        """Delete an integration and its secrets.

        Args:
            integration_id: Integration UUID.

        Returns:
            True if deleted, False if not found.
        """
        return await self._mixin_delete_by_id(integration_id)

    async def get_secrets(self, integration_id: UUID) -> dict[str, str] | None:
        """Get secrets for an integration.

        Note: Secrets are stored as bytes. The caller is responsible for
        encryption/decryption. This method decodes bytes to UTF-8 strings.

        Args:
            integration_id: Integration UUID.

        Returns:
            Dictionary of secret key-value pairs, or None if integration not found.
        """
        # Verify integration exists
        exists_stmt = select(IntegrationModel.id).where(
            IntegrationModel.id == integration_id,
        )
        if not await self._execute_exists(exists_stmt):
            return None

        # Fetch secrets
        stmt = select(IntegrationSecretModel).where(
            IntegrationSecretModel.integration_id == integration_id,
        )
        models = await self._execute_scalars(stmt)

        return {model.secret_key: model.secret_value.decode("utf-8") for model in models}

    async def set_secrets(self, integration_id: UUID, secrets: dict[str, str]) -> None:
        """Store secrets for an integration.

        Note: Secrets are stored as bytes. The caller is responsible for
        encryption/decryption. This method encodes strings to UTF-8 bytes.

        Args:
            integration_id: Integration UUID.
            secrets: Dictionary of secret key-value pairs.
        """
        # Delete existing secrets
        delete_stmt = delete(IntegrationSecretModel).where(
            IntegrationSecretModel.integration_id == integration_id,
        )
        await self._session.execute(delete_stmt)

        # Insert new secrets
        if secrets:
            models = [
                IntegrationSecretModel(
                    integration_id=integration_id,
                    secret_key=key,
                    secret_value=value.encode("utf-8"),
                )
                for key, value in secrets.items()
            ]
            await self._add_all_and_flush(models)

    async def list_by_type(self, integration_type: str) -> Sequence[Integration]:
        """List integrations by type.

        Args:
            integration_type: Integration type (e.g., 'calendar', 'email').

        Returns:
            List of integrations of the specified type.
        """
        stmt = (
            select(IntegrationModel)
            .where(IntegrationModel.type == integration_type)
            .order_by(IntegrationModel.created_at.desc())
        )
        models = await self._execute_scalars(stmt)
        return [IntegrationConverter.orm_to_domain(m) for m in models]

    async def list_all(self) -> Sequence[Integration]:
        """List all integrations for the current workspace context.

        Returns:
            All integrations the user has access to.
        """
        stmt = select(IntegrationModel).order_by(IntegrationModel.created_at.desc())
        models = await self._execute_scalars(stmt)
        return [IntegrationConverter.orm_to_domain(m) for m in models]

    # Sync run operations

    async def create_sync_run(self, sync_run: SyncRun) -> SyncRun:
        """Create a new sync run record.

        Args:
            sync_run: Sync run to persist.

        Returns:
            Created sync run.
        """
        kwargs = SyncRunConverter.to_orm_kwargs(sync_run)
        model = IntegrationSyncRunModel(**kwargs)
        await self._add_and_flush(model)
        return SyncRunConverter.orm_to_domain(model)

    async def get_sync_run(self, sync_run_id: UUID) -> SyncRun | None:
        """Retrieve a sync run by ID.

        Args:
            sync_run_id: Sync run UUID.

        Returns:
            SyncRun if found, None otherwise.
        """
        stmt = select(IntegrationSyncRunModel).where(
            IntegrationSyncRunModel.id == sync_run_id
        )
        model = await self._execute_scalar(stmt)
        return SyncRunConverter.orm_to_domain(model) if model else None

    async def update_sync_run(self, sync_run: SyncRun) -> SyncRun:
        """Update an existing sync run.

        Args:
            sync_run: Sync run with updated fields.

        Returns:
            Updated sync run.

        Raises:
            ValueError: If sync run does not exist.
        """
        stmt = select(IntegrationSyncRunModel).where(
            IntegrationSyncRunModel.id == sync_run.id
        )
        model = await self._execute_scalar(stmt)
        if not model:
            msg = f"SyncRun {sync_run.id} not found"
            raise ValueError(msg)

        # Update fields
        model.status = sync_run.status.value
        model.ended_at = sync_run.ended_at
        model.duration_ms = sync_run.duration_ms
        model.error_message = sync_run.error_message
        model.stats = sync_run.stats

        await self._session.flush()
        return SyncRunConverter.orm_to_domain(model)

    async def list_sync_runs(
        self,
        integration_id: UUID,
        limit: int = 20,
        offset: int = 0,
    ) -> tuple[Sequence[SyncRun], int]:
        """List sync runs for an integration with pagination.

        Args:
            integration_id: Integration to list runs for.
            limit: Maximum runs to return (max 100).
            offset: Pagination offset.

        Returns:
            Tuple of (sync runs newest first, total count).
        """
        # Clamp limit
        limit = min(limit, 100)

        # Count query
        count_stmt = (
            select(func.count())
            .select_from(IntegrationSyncRunModel)
            .where(IntegrationSyncRunModel.integration_id == integration_id)
        )
        total = await self._session.scalar(count_stmt) or 0

        # Fetch query
        stmt = (
            select(IntegrationSyncRunModel)
            .where(IntegrationSyncRunModel.integration_id == integration_id)
            .order_by(IntegrationSyncRunModel.started_at.desc())
            .offset(offset)
            .limit(limit)
        )
        models = await self._execute_scalars(stmt)
        runs = [SyncRunConverter.orm_to_domain(m) for m in models]

        return runs, total
