"""Persistence for typed scheduled-action execution history."""

from __future__ import annotations

import time
import uuid
from typing import Any

from media_library_viewer_api.services.service_data import ServiceDataHarness, StorageConcern

SCHEDULER_CONCERN = StorageConcern(
    concern_key="scheduler",
    db_filename="scheduler.db",
    migrations=[
        """
        CREATE TABLE IF NOT EXISTS scheduler_action_runs (
            id TEXT PRIMARY KEY,
            service_id TEXT NOT NULL,
            action_key TEXT NOT NULL,
            trigger TEXT NOT NULL,
            started_at INTEGER NOT NULL,
            finished_at INTEGER,
            status TEXT NOT NULL,
            attempt INTEGER NOT NULL DEFAULT 0,
            duration_ms INTEGER,
            error TEXT NOT NULL DEFAULT '',
            created_at INTEGER NOT NULL
        );
        CREATE INDEX IF NOT EXISTS idx_scheduler_runs_service_action_started
            ON scheduler_action_runs(service_id, action_key, started_at DESC);
        CREATE INDEX IF NOT EXISTS idx_scheduler_runs_status_started
            ON scheduler_action_runs(status, started_at DESC);
        """
    ],
    tables=["scheduler_action_runs"],
)

MAX_RUNS_PER_ACTION = 1_000
RUN_RETENTION_SECONDS = 30 * 24 * 60 * 60


def _safe_int(value: Any, default: int = 0) -> int:
    try:
        return int(value)
    except (TypeError, ValueError):
        return default


class SchedulerRunStore:
    """Store scheduler run records in the scheduler service-data concern."""

    def __init__(self, harness: ServiceDataHarness | None = None) -> None:
        if harness is None:
            from media_library_viewer_api.services.service_data import get_service_data_harness

            harness = get_service_data_harness()
        self._harness = harness

    def start_run(self, service_id: str, action_key: str, trigger: str, attempt: int = 0) -> dict[str, Any]:
        now = _safe_int(time.time())
        run = {
            "id": uuid.uuid4().hex[:12],
            "service_id": service_id,
            "action_key": action_key,
            "trigger": trigger,
            "started_at": now,
            "finished_at": None,
            "status": "running",
            "attempt": _safe_int(attempt),
            "duration_ms": None,
            "error": "",
            "created_at": now,
        }
        with self._harness.connect("scheduler") as conn:
            conn.execute(
                """
                INSERT INTO scheduler_action_runs
                (id, service_id, action_key, trigger, started_at, finished_at,
                 status, attempt, duration_ms, error, created_at)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """,
                (
                    run["id"],
                    run["service_id"],
                    run["action_key"],
                    run["trigger"],
                    run["started_at"],
                    run["finished_at"],
                    run["status"],
                    run["attempt"],
                    run["duration_ms"],
                    run["error"],
                    run["created_at"],
                ),
            )
            conn.commit()
        return run

    def finish_run(
        self,
        run_id: str,
        status: str,
        finished_at: int | None = None,
        duration_ms: int | None = None,
        error: str = "",
    ) -> dict[str, Any] | None:
        finished_at = _safe_int(finished_at if finished_at is not None else time.time())
        with self._harness.connect("scheduler") as conn:
            conn.execute(
                """
                UPDATE scheduler_action_runs
                SET finished_at = ?, status = ?, duration_ms = ?, error = ?
                WHERE id = ?
                """,
                (finished_at, status, duration_ms, str(error or "")[:1000], run_id),
            )
            conn.commit()
            row = conn.execute("SELECT * FROM scheduler_action_runs WHERE id = ?", (run_id,)).fetchone()
        return dict(row) if row else None

    def get_run(self, run_id: str) -> dict[str, Any] | None:
        with self._harness.connect("scheduler") as conn:
            row = conn.execute("SELECT * FROM scheduler_action_runs WHERE id = ?", (run_id,)).fetchone()
        return dict(row) if row else None

    def list_runs(
        self,
        service_id: str,
        action_key: str,
        *,
        status: str | None = None,
        trigger: str | None = None,
        limit: int = 50,
        offset: int = 0,
    ) -> tuple[list[dict[str, Any]], int]:
        limit = max(1, min(_safe_int(limit, 50), 100))
        offset = max(0, _safe_int(offset))
        with self._harness.connect("scheduler") as conn:
            rows = conn.execute(
                """
                SELECT * FROM scheduler_action_runs
                WHERE service_id = ? AND action_key = ?
                ORDER BY started_at DESC, id DESC LIMIT 1000
                """,
                (service_id, action_key),
            ).fetchall()
        filtered = [
            dict(row)
            for row in rows
            if (not status or row["status"] == status) and (not trigger or row["trigger"] == trigger)
        ]
        return filtered[offset : offset + limit], len(filtered)

    def prune(self, service_id: str, action_key: str, now: int | None = None) -> int:
        now = _safe_int(now if now is not None else time.time())
        cutoff = now - RUN_RETENTION_SECONDS
        with self._harness.connect("scheduler") as conn:
            cursor = conn.execute(
                """
                DELETE FROM scheduler_action_runs
                WHERE service_id = ? AND action_key = ?
                  AND (
                    created_at < ? OR rowid NOT IN (
                        SELECT rowid FROM scheduler_action_runs
                        WHERE service_id = ? AND action_key = ?
                        ORDER BY started_at DESC, rowid DESC LIMIT ?
                    )
                  )
                """,
                (service_id, action_key, cutoff, service_id, action_key, MAX_RUNS_PER_ACTION),
            )
            conn.commit()
        return _safe_int(cursor.rowcount)


__all__ = [
    "MAX_RUNS_PER_ACTION",
    "RUN_RETENTION_SECONDS",
    "SCHEDULER_CONCERN",
    "SchedulerRunStore",
]
