from __future__ import annotations

import json
import sqlite3
from datetime import datetime, timezone
from typing import Any


class ResultsRepository:
    def __init__(self, conn: sqlite3.Connection) -> None:
        self._conn = conn

    @staticmethod
    def _now() -> str:
        return datetime.now(timezone.utc).isoformat(timespec="milliseconds").replace(
            "+00:00",
            "Z",
        )

    def create_result(
        self,
        job_id: str,
        status: str,
        result: dict[str, Any],
        imported_at: str | None = None,
    ) -> None:
        self._conn.execute(
            """
            INSERT INTO crack_results (
                job_id,
                status,
                result_json,
                imported_at
            )
            VALUES (?, ?, ?, ?)
            """,
            (
                job_id,
                status,
                json.dumps(
                    result,
                    ensure_ascii=False,
                    separators=(",", ":"),
                    sort_keys=True,
                ),
                imported_at or self._now(),
            ),
        )

    def get_result(
        self,
        job_id: str,
    ) -> sqlite3.Row | None:
        return self._conn.execute(
            """
            SELECT
                job_id,
                status,
                result_json,
                imported_at
            FROM crack_results
            WHERE job_id = ?
            """,
            (job_id,),
        ).fetchone()

    def list_results(
        self,
        status: str | None = None,
    ) -> list[sqlite3.Row]:
        query = """
            SELECT
                job_id,
                status,
                result_json,
                imported_at
            FROM crack_results
            WHERE 1 = 1
        """
        params: list[Any] = []

        if status is not None:
            query += "\n              AND status = ?"
            params.append(status)

        query += "\n            ORDER BY imported_at DESC, job_id DESC"

        return self._conn.execute(query, params).fetchall()

    @staticmethod
    def decode_result(row: sqlite3.Row) -> dict[str, Any]:
        return json.loads(row["result_json"])