"""External admin domain - data access."""
from __future__ import annotations

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

from datetime import timedelta

from sqlalchemy import and_, case, delete, or_, select, func as sqlfunc
from sqlalchemy.ext.asyncio import AsyncSession

from src.domain.external_admin.models import (
    INSTALLATION_STATUS_CONNECTED,
    INSTALLATION_STATUS_PENDING,
    AssistantActionSession,
    AssistantDataConsent,
    AssistantProjectSettings,
    ExternalAdminChatJob,
    ExternalAdminChatMessage,
    ExternalAdminChatSession,
    ExternalAdminInstallation,
    ExternalCredential,
    SiteUnderstandingRun,
    WP_MUTATION_ACTION_STATUS_PENDING,
    WP_MUTATION_ACTION_STATUS_RUNNING,
    WpMutationAction,
)


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

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

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

    async def get_by_installation_token(self, token_enc: str) -> Optional[ExternalAdminInstallation]:
        result = await self._db.execute(
            select(ExternalAdminInstallation).where(ExternalAdminInstallation.installation_token_enc == token_enc)
        )
        return result.scalar_one_or_none()

    async def get_by_installation_token_hash(self, token_hash: str) -> Optional[ExternalAdminInstallation]:
        result = await self._db.execute(
            select(ExternalAdminInstallation).where(
                ExternalAdminInstallation.metadata_json["installation_token_hash"].astext == token_hash
            )
        )
        return result.scalar_one_or_none()

    async def redact_installation_data(self, installation_id: UUID) -> dict[str, int]:
        """Erase everything Core stored for an installation (Shopify ``shop/redact``).

        Deletes in dependency order and returns rows removed per table (for the audit
        record; the counts carry no personal data). The installation row itself is kept
        by the caller as a tombstone.
        """
        removed: dict[str, int] = {}
        for model in (
            ExternalAdminChatJob,
            ExternalAdminChatMessage,
            ExternalAdminChatSession,
            AssistantActionSession,
            SiteUnderstandingRun,
            WpMutationAction,
            ExternalCredential,
        ):
            result = await self._db.execute(
                delete(model).where(model.installation_id == installation_id)
            )
            removed[model.__tablename__] = int(result.rowcount or 0)
        return removed

    @staticmethod
    def _shop_preference():
        """Which installation "is" the shop when several rows carry its domain.

        Uninstalling keeps the row, and a shop can be connected again from another project or
        workspace, so a domain can legitimately match more than one installation. The live one
        wins (connected, then pending), then the most recently touched. The lookup never raises.
        """
        return (
            case(
                (ExternalAdminInstallation.status == INSTALLATION_STATUS_CONNECTED, 0),
                (ExternalAdminInstallation.status == INSTALLATION_STATUS_PENDING, 1),
                else_=2,
            ),
            ExternalAdminInstallation.updated_at.desc(),
        )

    async def get_by_shop_domain(self, shop_domain: str) -> Optional[ExternalAdminInstallation]:
        result = await self._db.execute(
            select(ExternalAdminInstallation)
            .where(ExternalAdminInstallation.shop_domain == shop_domain)
            .order_by(*self._shop_preference())
            .limit(1)
        )
        return result.scalars().first()

    async def list_by_shop_domain(self, shop_domain: str) -> list[ExternalAdminInstallation]:
        result = await self._db.execute(
            select(ExternalAdminInstallation)
            .where(ExternalAdminInstallation.shop_domain == shop_domain)
            .order_by(*self._shop_preference())
        )
        return list(result.scalars().all())

    async def get_by_workspace_and_site_url(
        self, workspace_id: UUID, site_url: str
    ) -> Optional[ExternalAdminInstallation]:
        """Find the most recent WordPress installation for a workspace + site_url.

        Used to avoid creating a duplicate project when a site reconnects. site_url
        must already be normalized by the caller (scheme://host[:port], no trailing slash).
        """
        result = await self._db.execute(
            select(ExternalAdminInstallation)
            .where(
                and_(
                    ExternalAdminInstallation.workspace_id == workspace_id,
                    ExternalAdminInstallation.site_url == site_url,
                    ExternalAdminInstallation.installation_type == "wordpress",
                )
            )
            .order_by(ExternalAdminInstallation.created_at.desc())
            .limit(1)
        )
        return result.scalar_one_or_none()

    async def create(self, obj: ExternalAdminInstallation) -> ExternalAdminInstallation:
        self._db.add(obj)
        await self._db.flush()
        return obj

    async def save(self, obj: ExternalAdminInstallation) -> ExternalAdminInstallation:
        self._db.add(obj)
        await self._db.flush()
        return obj


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

    async def list_by_installation(self, installation_id: UUID) -> list[ExternalCredential]:
        result = await self._db.execute(
            select(ExternalCredential)
            .where(ExternalCredential.installation_id == installation_id)
            .order_by(ExternalCredential.credential_type, ExternalCredential.version.desc())
        )
        return list(result.scalars().all())

    async def get_active(self, installation_id: UUID, credential_type: str) -> Optional[ExternalCredential]:
        result = await self._db.execute(
            select(ExternalCredential)
            .where(
                and_(
                    ExternalCredential.installation_id == installation_id,
                    ExternalCredential.credential_type == credential_type,
                    ExternalCredential.status == "active",
                )
            )
            .order_by(ExternalCredential.version.desc())
            .limit(1)
        )
        return result.scalar_one_or_none()

    async def get_max_version(self, installation_id: UUID, credential_type: str) -> int:
        result = await self._db.execute(
            select(sqlfunc.max(ExternalCredential.version)).where(
                and_(
                    ExternalCredential.installation_id == installation_id,
                    ExternalCredential.credential_type == credential_type,
                )
            )
        )
        return result.scalar_one() or 0

    async def create(self, obj: ExternalCredential) -> ExternalCredential:
        self._db.add(obj)
        await self._db.flush()
        return obj

    async def save(self, obj: ExternalCredential) -> ExternalCredential:
        self._db.add(obj)
        await self._db.flush()
        return obj


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

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

    async def upsert(self, obj: AssistantProjectSettings) -> AssistantProjectSettings:
        self._db.add(obj)
        await self._db.flush()
        return obj


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

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

    async def get_by_correlation_id(self, correlation_id: str) -> Optional[AssistantActionSession]:
        result = await self._db.execute(
            select(AssistantActionSession).where(AssistantActionSession.correlation_id == correlation_id)
        )
        return result.scalar_one_or_none()

    async def list_by_project(
        self,
        project_id: UUID,
        status: Optional[str] = None,
        limit: int = 50,
        offset: int = 0,
    ) -> tuple[list[AssistantActionSession], int]:
        q = select(AssistantActionSession).where(AssistantActionSession.project_id == project_id)
        count_q = select(sqlfunc.count()).select_from(
            select(AssistantActionSession).where(AssistantActionSession.project_id == project_id).subquery()
        )
        if status:
            q = q.where(AssistantActionSession.status == status)
            count_q = select(sqlfunc.count()).select_from(
                select(AssistantActionSession)
                .where(
                    and_(
                        AssistantActionSession.project_id == project_id,
                        AssistantActionSession.status == status,
                    )
                )
                .subquery()
            )
        q = q.order_by(AssistantActionSession.created_at.desc()).limit(limit).offset(offset)
        rows = list((await self._db.execute(q)).scalars().all())
        total = (await self._db.execute(count_q)).scalar_one()
        return rows, total

    async def create(self, obj: AssistantActionSession) -> AssistantActionSession:
        self._db.add(obj)
        await self._db.flush()
        return obj

    async def save(self, obj: AssistantActionSession) -> AssistantActionSession:
        self._db.add(obj)
        await self._db.flush()
        return obj


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

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

    async def get(self, project_id: UUID, consent_type: str) -> Optional[AssistantDataConsent]:
        result = await self._db.execute(
            select(AssistantDataConsent).where(
                and_(
                    AssistantDataConsent.project_id == project_id,
                    AssistantDataConsent.consent_type == consent_type,
                )
            )
        )
        return result.scalar_one_or_none()

    async def upsert(self, obj: AssistantDataConsent) -> AssistantDataConsent:
        self._db.add(obj)
        await self._db.flush()
        return obj


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

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

    async def get_active_for_installation(self, installation_id: UUID) -> Optional[ExternalAdminChatSession]:
        now = datetime.now(timezone.utc)
        result = await self._db.execute(
            select(ExternalAdminChatSession)
            .where(
                and_(
                    ExternalAdminChatSession.installation_id == installation_id,
                    ExternalAdminChatSession.status == "active",
                    ExternalAdminChatSession.expires_at > now,
                )
            )
            .order_by(ExternalAdminChatSession.created_at.desc())
            .limit(1)
        )
        return result.scalar_one_or_none()

    async def create(self, obj: ExternalAdminChatSession) -> ExternalAdminChatSession:
        self._db.add(obj)
        await self._db.flush()
        return obj

    async def save(self, obj: ExternalAdminChatSession) -> ExternalAdminChatSession:
        self._db.add(obj)
        await self._db.flush()
        return obj


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

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

    async def list_by_session(
        self,
        session_id: UUID,
        after_message_id: Optional[UUID] = None,
        limit: int = 50,
    ) -> list[ExternalAdminChatMessage]:
        q = select(ExternalAdminChatMessage).where(
            ExternalAdminChatMessage.session_id == session_id
        )
        if after_message_id is not None:
            cursor_result = await self._db.execute(
                select(ExternalAdminChatMessage.created_at, ExternalAdminChatMessage.id).where(
                    and_(
                        ExternalAdminChatMessage.id == after_message_id,
                        ExternalAdminChatMessage.session_id == session_id,
                    )
                )
            )
            cursor = cursor_result.one_or_none()
            if cursor:
                cursor_created_at, cursor_id = cursor
                q = q.where(
                    or_(
                        ExternalAdminChatMessage.created_at > cursor_created_at,
                        and_(
                            ExternalAdminChatMessage.created_at == cursor_created_at,
                            ExternalAdminChatMessage.id > cursor_id,
                        ),
                    )
                )
        q = q.order_by(
            ExternalAdminChatMessage.created_at.asc(),
            ExternalAdminChatMessage.id.asc(),
        ).limit(limit)
        result = await self._db.execute(q)
        return list(result.scalars().all())

    async def list_recent_by_session(
        self,
        session_id: UUID,
        limit: int = 50,
    ) -> list[ExternalAdminChatMessage]:
        result = await self._db.execute(
            select(ExternalAdminChatMessage)
            .where(ExternalAdminChatMessage.session_id == session_id)
            .order_by(
                ExternalAdminChatMessage.created_at.desc(),
                ExternalAdminChatMessage.id.desc(),
            )
            .limit(limit)
        )
        rows = list(result.scalars().all())
        rows.reverse()
        return rows

    async def create(self, obj: ExternalAdminChatMessage) -> ExternalAdminChatMessage:
        self._db.add(obj)
        await self._db.flush()
        return obj


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

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

    async def get_by_idempotency_key(
        self, installation_id: UUID, idempotency_key: str
    ) -> Optional[ExternalAdminChatJob]:
        result = await self._db.execute(
            select(ExternalAdminChatJob).where(
                and_(
                    ExternalAdminChatJob.installation_id == installation_id,
                    ExternalAdminChatJob.idempotency_key == idempotency_key,
                )
            )
        )
        return result.scalar_one_or_none()

    async def create(self, obj: ExternalAdminChatJob) -> ExternalAdminChatJob:
        self._db.add(obj)
        await self._db.flush()
        return obj

    async def save(self, obj: ExternalAdminChatJob) -> ExternalAdminChatJob:
        self._db.add(obj)
        await self._db.flush()
        return obj


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

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

    async def get_latest_for_installation(self, installation_id: UUID) -> Optional[SiteUnderstandingRun]:
        result = await self._db.execute(
            select(SiteUnderstandingRun)
            .where(SiteUnderstandingRun.installation_id == installation_id)
            .order_by(SiteUnderstandingRun.created_at.desc())
            .limit(1)
        )
        return result.scalar_one_or_none()

    async def get_latest_completed_for_installation(self, installation_id: UUID) -> Optional[SiteUnderstandingRun]:
        result = await self._db.execute(
            select(SiteUnderstandingRun)
            .where(
                and_(
                    SiteUnderstandingRun.installation_id == installation_id,
                    SiteUnderstandingRun.status == "completed",
                )
            )
            .order_by(SiteUnderstandingRun.completed_at.desc().nullslast(), SiteUnderstandingRun.created_at.desc())
            .limit(1)
        )
        return result.scalar_one_or_none()

    async def create(self, obj: SiteUnderstandingRun) -> SiteUnderstandingRun:
        self._db.add(obj)
        await self._db.flush()
        return obj

    async def save(self, obj: SiteUnderstandingRun) -> SiteUnderstandingRun:
        self._db.add(obj)
        await self._db.flush()
        return obj


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

    async def list_by_session(self, action_session_id: UUID) -> list[WpMutationAction]:
        result = await self._db.execute(
            select(WpMutationAction)
            .where(WpMutationAction.action_session_id == action_session_id)
            .order_by(WpMutationAction.sequence_number.asc())
        )
        return list(result.scalars().all())

    async def create(self, obj: WpMutationAction) -> WpMutationAction:
        self._db.add(obj)
        await self._db.flush()
        return obj

    async def save(self, obj: WpMutationAction) -> WpMutationAction:
        self._db.add(obj)
        await self._db.flush()
        return obj

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

    async def get_next_pending(self, installation_id: UUID) -> Optional[WpMutationAction]:
        result = await self._db.execute(
            select(WpMutationAction)
            .where(
                WpMutationAction.installation_id == installation_id,
                WpMutationAction.status == WP_MUTATION_ACTION_STATUS_PENDING,
            )
            .order_by(WpMutationAction.created_at.asc(), WpMutationAction.sequence_number.asc())
            .limit(1)
        )
        return result.scalar_one_or_none()

    async def reset_stale_running(self, installation_id: UUID) -> None:
        stale_cutoff = datetime.now(timezone.utc) - timedelta(minutes=5)
        result = await self._db.execute(
            select(WpMutationAction).where(
                WpMutationAction.installation_id == installation_id,
                WpMutationAction.status == WP_MUTATION_ACTION_STATUS_RUNNING,
                WpMutationAction.updated_at < stale_cutoff,
            )
        )
        for row in result.scalars().all():
            row.status = WP_MUTATION_ACTION_STATUS_PENDING
            row.error = "stale_running_reset"
            self._db.add(row)
        await self._db.flush()

    async def count_pending_or_running_for_session(self, session_id: UUID) -> int:
        result = await self._db.execute(
            select(sqlfunc.count())
            .select_from(WpMutationAction)
            .where(
                WpMutationAction.action_session_id == session_id,
                WpMutationAction.status.in_([
                    WP_MUTATION_ACTION_STATUS_PENDING,
                    WP_MUTATION_ACTION_STATUS_RUNNING,
                ]),
            )
        )
        return result.scalar() or 0

    async def mark_pending_as_skipped(self, session_id: UUID) -> None:
        result = await self._db.execute(
            select(WpMutationAction).where(
                WpMutationAction.action_session_id == session_id,
                WpMutationAction.status == WP_MUTATION_ACTION_STATUS_PENDING,
            )
        )
        for row in result.scalars().all():
            row.status = "skipped"
            self._db.add(row)
        await self._db.flush()