"""Historical workflow figures, derived rather than duplicated.

Everything here comes from `workflow_runs` and `workflow_steps`, which already
record what happened and when. A parallel metrics table would be a second copy
of the same facts, kept in step by hand, and eventually disagreeing with the
first — at which point nobody knows which to believe.

Counters in `metrics.py` reset on restart, which is correct for a scraped
gauge and useless for "how many workflows completed last week". This answers the
second question.
"""

from __future__ import annotations

from dataclasses import dataclass
from datetime import UTC, datetime, timedelta


@dataclass(frozen=True, slots=True)
class WorkflowStats:
    """What has happened over a window."""

    days: int
    started: int
    completed: int
    failed: int
    awaiting: int

    median_seconds: int | None
    retried: int

    @property
    def success_rate(self) -> float | None:
        """Of the runs that finished, how many finished well.

        None rather than zero when nothing has finished. "0% success" and
        "nothing has run" mean very different things to somebody deciding
        whether to trust this, and a dashboard renders both identically unless
        the difference survives to it.
        """
        finished = self.completed + self.failed

        return None if finished == 0 else round(self.completed / finished * 100, 1)

    def to_dict(self) -> dict:
        return {
            "days": self.days,
            "started": self.started,
            "completed": self.completed,
            "failed": self.failed,
            "awaiting": self.awaiting,
            "success_rate": self.success_rate,
            "median_seconds": self.median_seconds,
            "retried": self.retried,
        }


def workflow_stats(connect, days: int = 7) -> WorkflowStats | None:
    """Read the window from the database, or None when there is no database."""
    if connect is None:
        return None

    since = datetime.now(UTC) - timedelta(days=days)

    with connect() as connection, connection.cursor() as cursor:
        cursor.execute(
            """
            SELECT
                COUNT(*),
                COUNT(*) FILTER (WHERE state = 'completed'),
                COUNT(*) FILTER (WHERE state = 'failed'),
                COUNT(*) FILTER (WHERE state IN ('pending', 'running', 'awaiting_approval'))
            FROM workflow_runs
            WHERE created_at >= %s
            """,
            (since,),
        )
        started, completed, failed, awaiting = cursor.fetchone()

        # Median rather than mean: one run left awaiting approval over a weekend
        # drags a mean into uselessness, and the question being asked is how long
        # a typical document takes.
        cursor.execute(
            """
            SELECT percentile_cont(0.5) WITHIN GROUP (
                ORDER BY EXTRACT(EPOCH FROM (updated_at - created_at))
            )
            FROM workflow_runs
            WHERE created_at >= %s AND state IN ('completed', 'failed')
            """,
            (since,),
        )
        median = cursor.fetchone()[0]

        # A step attempted more than once. The interesting number is how often
        # anything had to be retried at all, not the total attempts — which one
        # persistently failing run would dominate.
        cursor.execute(
            """
            SELECT COUNT(DISTINCT s.run_id)
            FROM workflow_steps s
            JOIN workflow_runs r ON r.id = s.run_id
            WHERE r.created_at >= %s AND s.attempts > 1
            """,
            (since,),
        )
        retried = cursor.fetchone()[0]

    return WorkflowStats(
        days=days,
        started=int(started or 0),
        completed=int(completed or 0),
        failed=int(failed or 0),
        awaiting=int(awaiting or 0),
        median_seconds=int(median) if median is not None else None,
        retried=int(retried or 0),
    )