"""Platform runtime health from existing durable operational ledgers."""

from __future__ import annotations

from collections.abc import Callable, Sequence
from datetime import UTC, datetime, time, timedelta
from decimal import Decimal

from sqlalchemy import and_, case, func, or_, select
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.ext.asyncio import AsyncSession

from app.domains.provisioning.models import (
    EXEC_AUTO_API,
    EXEC_SSO_AUTO,
    ProvisioningTask,
)
from app.domains.provisioning.models import (
    STATUS_COMPLETED as PROVISION_COMPLETED,
)
from app.domains.provisioning.models import (
    STATUS_FAILED as PROVISION_FAILED,
)
from app.domains.provisioning.models import (
    STATUS_IN_PROGRESS as PROVISION_IN_PROGRESS,
)
from app.domains.provisioning.models import (
    STATUS_PENDING as PROVISION_PENDING,
)
from app.identity.data_quality_models import (
    IdentityDataQualityFinding,
    IdentityDataQualityRun,
)
from app.identity.person_models import IdentityPerson
from app.integrations.models import Integration, PlatformSetting
from app.intelligence.privacy import EvidenceObject
from app.intelligence.snapshot_service import (
    MetricObservation,
    ProviderResult,
    ProviderUnavailable,
    SnapshotSubject,
)
from app.notifications.models import (
    STATUS_DEAD,
    STATUS_DELIVERED,
    STATUS_DELIVERY_UNCERTAIN,
    STATUS_FAILED,
    STATUS_PARTIAL,
    NotificationOutbox,
)
from app.observability.client_errors import ClientErrorDailyAggregate
from app.scheduled_jobs.models import ScheduledJob, ScheduledJobRun

_EVIDENCE_LIMIT = 50
_TELEMETRY_START_KEY = "observability.client_error_telemetry_started"
_NOTIFICATION_TERMINAL = frozenset({
    STATUS_DELIVERED, STATUS_PARTIAL, STATUS_FAILED, STATUS_DEAD,
    STATUS_DELIVERY_UNCERTAIN,
})
_NOTIFICATION_FAILURE = frozenset({
    STATUS_FAILED, STATUS_DEAD, STATUS_DELIVERY_UNCERTAIN,
})
_DQ_LIVE_STATUSES = frozenset({"open", "acknowledged", "accepted_risk"})


def _utc(value: datetime) -> datetime:
    return value.replace(tzinfo=UTC) if value.tzinfo is None else value.astimezone(UTC)


def _pct(value: int, total: int) -> Decimal:
    return (Decimal(value) * 100 / Decimal(total)).quantize(Decimal("0.01"))


class PlatformOperationsFactProvider:
    dimension = "platform_ops"

    def __init__(self, *, now: Callable[[], datetime] | None = None) -> None:
        self._now = now or (lambda: datetime.now(UTC))

    @staticmethod
    def _validate_subject(subject: SnapshotSubject) -> None:
        if subject.subject_type == "platform" and subject.subject_id == "platform":
            return
        if subject.subject_type == "domain" and subject.subject_id == "platform_ops":
            return
        raise ValueError(
            "Platform operations facts only support platform or platform_ops snapshots"
        )

    async def collect(self, db: AsyncSession, subject: SnapshotSubject) -> ProviderResult:
        self._validate_subject(subject)
        try:
            return await self._collect(db)
        except SQLAlchemyError as exc:
            raise ProviderUnavailable("platform operations datastore unavailable") from exc

    async def _collect(self, db: AsyncSession) -> ProviderResult:
        now = _utc(self._now())
        since_30d = now - timedelta(days=30)
        since_7d = now - timedelta(days=7)

        people_stats = (await db.execute(
                select(
                    func.count(IdentityPerson.id),
                    func.max(IdentityPerson.updated_at),
                ).where(
                    IdentityPerson.deleted_at.is_(None),
                    IdentityPerson.employment_status == "active",
                )
            )).one()
        active_people = int(people_stats[0] or 0)
        people_as_of = _utc(people_stats[1]) if people_stats[1] else None

        enabled_jobs = list((await db.execute(
            select(ScheduledJob).where(ScheduledJob.enabled.is_(True))
        )).scalars())
        job_ids = {row.id for row in enabled_jobs}
        job_stats = (await db.execute(
            select(
                func.count(ScheduledJobRun.id),
                func.coalesce(func.sum(case((ScheduledJobRun.status == "failed", 1), else_=0)), 0),
                func.count(func.distinct(ScheduledJobRun.job_id)),
                func.max(func.coalesce(ScheduledJobRun.finished_at, ScheduledJobRun.started_at)),
            ).where(
                ScheduledJobRun.job_id.in_(job_ids),
                ScheduledJobRun.started_at >= since_7d,
                ScheduledJobRun.status.in_(("ok", "failed")),
            )
        )).one() if job_ids else (0, 0, 0, None)
        job_run_count = int(job_stats[0] or 0)
        failed_job_run_count = int(job_stats[1] or 0)
        observed_job_count = int(job_stats[2] or 0)
        jobs_as_of = _utc(job_stats[3]) if job_stats[3] else None
        failed_runs = list((await db.execute(
            select(ScheduledJobRun).where(
                ScheduledJobRun.job_id.in_(job_ids),
                ScheduledJobRun.started_at >= since_7d,
                ScheduledJobRun.status == "failed",
            ).order_by(ScheduledJobRun.started_at.desc()).limit(_EVIDENCE_LIMIT)
        )).scalars()) if job_ids else []

        provision_filters = (
            ProvisioningTask.created_at >= since_30d,
            ProvisioningTask.execution_mode.in_((EXEC_AUTO_API, EXEC_SSO_AUTO)),
            or_(ProvisioningTask.not_before.is_(None), ProvisioningTask.not_before <= now),
        )
        provision_stale_cutoff = now - timedelta(hours=24)
        provision_stale = and_(
            ProvisioningTask.status.in_((PROVISION_PENDING, PROVISION_IN_PROGRESS)),
            ProvisioningTask.updated_at <= provision_stale_cutoff,
        )
        provision_evaluable = or_(
            ProvisioningTask.status.in_((PROVISION_COMPLETED, PROVISION_FAILED)),
            provision_stale,
        )
        provision_unhealthy = or_(
            ProvisioningTask.status == PROVISION_FAILED,
            provision_stale,
        )
        provision_stats = (await db.execute(
            select(
                func.count(ProvisioningTask.id),
                func.coalesce(func.sum(case((provision_evaluable, 1), else_=0)), 0),
                func.coalesce(func.sum(case((provision_unhealthy, 1), else_=0)), 0),
                func.max(ProvisioningTask.updated_at),
            ).where(*provision_filters)
        )).one()
        provisioning_count = int(provision_stats[0] or 0)
        provision_evaluable_count = int(provision_stats[1] or 0)
        provision_failed_count = int(provision_stats[2] or 0)
        provisioning_as_of = _utc(provision_stats[3]) if provision_stats[3] else None
        provision_failed = list((await db.execute(
            select(ProvisioningTask).where(
                *provision_filters, provision_unhealthy,
            ).order_by(ProvisioningTask.updated_at.desc()).limit(_EVIDENCE_LIMIT)
        )).scalars())

        notification_stats = (await db.execute(
            select(
                func.count(NotificationOutbox.id),
                func.coalesce(func.sum(case((NotificationOutbox.status.in_(
                    _NOTIFICATION_TERMINAL,
                ), 1), else_=0)), 0),
                func.coalesce(func.sum(case((NotificationOutbox.status.in_(
                    _NOTIFICATION_FAILURE,
                ), 1), else_=0)), 0),
                func.max(NotificationOutbox.updated_at),
            ).where(NotificationOutbox.created_at >= since_30d)
        )).one()
        notification_count = int(notification_stats[0] or 0)
        notification_terminal_count = int(notification_stats[1] or 0)
        notification_failed_count = int(notification_stats[2] or 0)
        notification_as_of = (
            _utc(notification_stats[3]) if notification_stats[3] else None
        )
        notification_failed = list((await db.execute(
            select(NotificationOutbox).where(
                NotificationOutbox.created_at >= since_30d,
                NotificationOutbox.status.in_(_NOTIFICATION_FAILURE),
            ).order_by(NotificationOutbox.updated_at.desc()).limit(_EVIDENCE_LIMIT)
        )).scalars())

        integrations = list((await db.execute(
            select(Integration).where(Integration.enabled.is_(True))
        )).scalars())
        connector_cutoff = now - timedelta(days=30)
        healthy_integrations = [
            row for row in integrations
            if row.last_test_status == "ok"
            and row.last_tested_at is not None
            and _utc(row.last_tested_at) >= connector_cutoff
        ]
        observed_integrations = [row for row in integrations if row.last_tested_at]
        connector_gaps = [row for row in integrations if row not in healthy_integrations]
        connector_as_of = max(
            (_utc(row.last_tested_at) for row in integrations if row.last_tested_at),
            default=None,
        )

        last_complete_telemetry_day = now.date() - timedelta(days=1)
        first_telemetry_day = last_complete_telemetry_day - timedelta(days=6)
        client_error_rows = list((await db.execute(
            select(ClientErrorDailyAggregate).where(
                ClientErrorDailyAggregate.day >= first_telemetry_day,
                ClientErrorDailyAggregate.day <= last_complete_telemetry_day,
            )
        )).scalars())
        client_error_count = sum(row.report_count for row in client_error_rows)
        telemetry_start = await db.get(PlatformSetting, _TELEMETRY_START_KEY)
        telemetry_days = (
            min(7, max(0, (
                last_complete_telemetry_day
                - _utc(telemetry_start.created_at).date()
            ).days + 1))
            if telemetry_start is not None else 0
        )
        telemetry_confidence = _pct(telemetry_days, 7) if active_people else Decimal("0")
        client_error_rate = min(
            Decimal("100"), _pct(client_error_count, active_people)
        ) if active_people else Decimal("0")
        telemetry_cutoff = datetime.combine(
            last_complete_telemetry_day, time.max, tzinfo=UTC,
        )
        client_error_as_of = min(telemetry_cutoff, people_as_of) if (
            telemetry_days and people_as_of
        ) else None

        latest_dq_run = (await db.execute(
            select(IdentityDataQualityRun)
            .order_by(IdentityDataQualityRun.started_at.desc()).limit(1)
        )).scalar_one_or_none()
        dq_stats = (await db.execute(
            select(
                func.count(IdentityDataQualityFinding.id),
                func.coalesce(func.sum(case((
                    IdentityDataQualityFinding.severity == "high", 1,
                ), else_=0)), 0),
            ).where(
                IdentityDataQualityFinding.status.in_(_DQ_LIVE_STATUSES)
            )
        )).one()
        dq_finding_count = int(dq_stats[0] or 0)
        high_dq_finding_count = int(dq_stats[1] or 0)
        dq_findings = list((await db.execute(
            select(IdentityDataQualityFinding).where(
                IdentityDataQualityFinding.status.in_(_DQ_LIVE_STATUSES)
            ).order_by(
                IdentityDataQualityFinding.last_seen_at.desc()
            ).limit(_EVIDENCE_LIMIT)
        )).scalars())
        dq_as_of = (
            _utc(latest_dq_run.finished_at or latest_dq_run.started_at)
            if latest_dq_run else None
        )
        dq_age_days = (now - dq_as_of).days if dq_as_of else 999
        dq_confidence = (
            100 if latest_dq_run and latest_dq_run.status == "ok" and dq_age_days <= 1
            else 60 if latest_dq_run and latest_dq_run.status == "ok" and dq_age_days <= 2
            else 20 if latest_dq_run and latest_dq_run.status == "ok" else 0
        )
        if not active_people:
            dq_confidence = 0

        def refs(kind: str, rows: Sequence[object]) -> tuple[EvidenceObject, ...]:
            return tuple(
                EvidenceObject(kind, str(getattr(row, "id")))
                for row in rows[:_EVIDENCE_LIMIT]
            )

        metric_times = [
            value for value in (
                jobs_as_of, provisioning_as_of, notification_as_of,
                connector_as_of, client_error_as_of, dq_as_of,
            ) if value is not None
        ]
        return ProviderResult(
            dimension=self.dimension,
            metrics=(
                MetricObservation(
                    "platform_ops.scheduled_job_failure_rate",
                    _pct(failed_job_run_count, job_run_count) if job_run_count else 0,
                    confidence=_pct(observed_job_count, len(enabled_jobs))
                    if enabled_jobs else 0,
                    evidence=refs("jobrun", failed_runs), data_as_of=jobs_as_of,
                ),
                MetricObservation(
                    "platform_ops.provisioning_failure_rate",
                    _pct(provision_failed_count, provision_evaluable_count)
                    if provision_evaluable_count else 0,
                    confidence=_pct(provision_evaluable_count, provisioning_count)
                    if provisioning_count else 0,
                    evidence=refs("provisiontask", provision_failed),
                    data_as_of=provisioning_as_of,
                ),
                MetricObservation(
                    "platform_ops.notification_failure_rate",
                    _pct(notification_failed_count, notification_terminal_count)
                    if notification_terminal_count else 0,
                    confidence=_pct(notification_terminal_count, notification_count)
                    if notification_count else 0,
                    evidence=refs("notification", notification_failed),
                    data_as_of=notification_as_of,
                ),
                MetricObservation(
                    "platform_ops.connector_health_coverage",
                    _pct(len(healthy_integrations), len(integrations)) if integrations else 0,
                    confidence=_pct(len(observed_integrations), len(integrations))
                    if integrations else 0,
                    evidence=refs("integration", connector_gaps), data_as_of=connector_as_of,
                ),
                MetricObservation(
                    "platform_ops.client_error_report_rate",
                    client_error_rate, confidence=telemetry_confidence,
                    data_as_of=client_error_as_of,
                ),
                MetricObservation(
                    "platform_ops.data_quality_finding_rate",
                    min(Decimal("100"), _pct(dq_finding_count, active_people))
                    if active_people else 0,
                    confidence=dq_confidence,
                    evidence=refs("dataquality", dq_findings), data_as_of=dq_as_of,
                ),
            ),
            aggregate_facts={
                "enabled_job_count": len(enabled_jobs),
                "observed_job_count": observed_job_count,
                "terminal_job_run_count_7d": job_run_count,
                "failed_job_run_count_7d": failed_job_run_count,
                "eligible_automatic_provisioning_count_30d": provisioning_count,
                "evaluable_automatic_provisioning_count_30d": provision_evaluable_count,
                "failed_automatic_provisioning_count_30d": provision_failed_count,
                "notification_count_30d": notification_count,
                "terminal_notification_count_30d": notification_terminal_count,
                "notification_failure_count_30d": notification_failed_count,
                "enabled_connector_count": len(integrations),
                "healthy_connector_count": len(healthy_integrations),
                "client_error_report_count_7d": client_error_count,
                "client_error_telemetry_coverage_days": telemetry_days,
                "active_person_count": active_people,
                "open_data_quality_finding_count": dq_finding_count,
                "high_data_quality_finding_count": high_dq_finding_count,
            },
            data_as_of=min(metric_times) if metric_times else None,
        )