from __future__ import annotations

import sqlite3
from datetime import datetime, timezone


class AttemptsRepository:
    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_attempt(
        self,
        handshake_id: int,
        method_id: str,
        method_version: int,
        job_id: str,
        completed_at: str | None = None,
    ) -> None:
        self._conn.execute(
            """
            INSERT INTO crack_attempts (
                handshake_id,
                method_id,
                method_version,
                job_id,
                status,
                completed_at
            )
            VALUES (?, ?, ?, ?, ?, ?)
            """,
            (
                handshake_id,
                method_id,
                method_version,
                job_id,
                "COMPLETED",
                completed_at or self._now(),
            ),
        )

    def get_attempt(
        self,
        handshake_id: int,
        method_id: str,
        method_version: int,
    ) -> sqlite3.Row | None:
        return self._conn.execute(
            """
            SELECT
                handshake_id,
                method_id,
                method_version,
                job_id,
                status,
                completed_at
            FROM crack_attempts
            WHERE handshake_id = ?
              AND method_id = ?
              AND method_version = ?
            """,
            (
                handshake_id,
                method_id,
                method_version,
            ),
        ).fetchone()

    def has_completed(
        self,
        handshake_id: int,
        method_id: str,
        method_version: int,
    ) -> bool:
        row = self._conn.execute(
            """
            SELECT 1
            FROM crack_attempts
            WHERE handshake_id = ?
              AND method_id = ?
              AND method_version = ?
              AND status = 'COMPLETED'
            """,
            (
                handshake_id,
                method_id,
                method_version,
            ),
        ).fetchone()

        return row is not None

    def list_handshake_attempts(
        self,
        handshake_id: int,
    ) -> list[sqlite3.Row]:
        return self._conn.execute(
            """
            SELECT
                handshake_id,
                method_id,
                method_version,
                job_id,
                status,
                completed_at
            FROM crack_attempts
            WHERE handshake_id = ?
            ORDER BY method_id, method_version
            """,
            (handshake_id,),
        ).fetchall()

    def list_job_attempts(
        self,
        job_id: str,
    ) -> list[sqlite3.Row]:
        return self._conn.execute(
            """
            SELECT
                handshake_id,
                method_id,
                method_version,
                job_id,
                status,
                completed_at
            FROM crack_attempts
            WHERE job_id = ?
            ORDER BY handshake_id, method_id, method_version
            """,
            (job_id,),
        ).fetchall()

    def list_method_attempts(
        self,
        method_id: str,
        method_version: int,
    ) -> list[sqlite3.Row]:
        return self._conn.execute(
            """
            SELECT
                handshake_id,
                method_id,
                method_version,
                job_id,
                status,
                completed_at
            FROM crack_attempts
            WHERE method_id = ?
              AND method_version = ?
            ORDER BY handshake_id
            """,
            (
                method_id,
                method_version,
            ),
        ).fetchall()