from __future__ import annotations

import sqlite3
from typing import Any


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

    def list_eligible(
        self,
        method_id: str,
        method_version: int,
        limit: int,
    ) -> list[sqlite3.Row]:
        return self._conn.execute(
            """
            SELECT
                h.id AS handshake_id,
                h.hash22000,
                h.access_point_id,
                h.captured_at
            FROM handshakes AS h
            WHERE h.hash22000 IS NOT NULL
              AND TRIM(h.hash22000) <> ''
              AND NOT EXISTS (
                  SELECT 1
                  FROM credentials AS c
                  WHERE c.handshake_id = h.id
              )
              AND NOT EXISTS (
                  SELECT 1
                  FROM crack_attempts AS a
                  WHERE a.handshake_id = h.id
                    AND a.method_id = ?
                    AND a.method_version = ?
                    AND a.status = 'COMPLETED'
              )
              AND NOT EXISTS (
                  SELECT 1
                  FROM crack_reservations AS r
                  WHERE r.handshake_id = h.id
                    AND r.method_id = ?
                    AND r.method_version = ?
                    AND r.status = 'ACTIVE'
              )
            ORDER BY h.id
            LIMIT ?
            """,
            (
                method_id,
                method_version,
                method_id,
                method_version,
                limit,
            ),
        ).fetchall()