from __future__ import annotations

import logging
from typing import Any

from psycopg.connection import Connection

logger = logging.getLogger("dscons.postgres.dossier_reviews.queries")


class DossierReviewSessionQueriesMixin:
    """Query methods for Dossier Review sessions and associated findings."""

    def fetch_dossier_review_sessions(
        self,
        project_code: str | None = None,
        review_id: str | None = None,
    ) -> list[dict[str, Any]]:
        """Fetch dossier review workflow sessions with nested findings, actions, and snapshots."""
        with self.get_connection() as connection, connection.cursor() as cursor:
            cursor.execute(
                DOSSIER_REVIEW_SESSIONS_QUERY,
                {"project_code": project_code, "review_id": review_id},
            )
            rows = cursor.fetchall()
        return [self._normalize_dossier_review_session_row(row) for row in rows]

    def _fetch_single_dossier_review_session(
        self,
        *,
        review_id: str,
        connection: Connection[Any] | None = None,
        error_message: str,
    ) -> dict[str, Any]:
        """Fetch one dossier review session, using the active transaction when provided."""
        sessions = (
            self._fetch_dossier_review_sessions_with_connection(
                connection, review_id=review_id
            )
            if connection is not None
            else self.fetch_dossier_review_sessions(review_id=review_id)
        )
        if not sessions:
            raise ValueError(error_message)
        return sessions[0]

    def _fetch_dossier_review_sessions_with_connection(
        self,
        connection: Connection[Any],
        *,
        project_code: str | None = None,
        review_id: str | None = None,
    ) -> list[dict[str, Any]]:
        """Fetch dossier review sessions inside an existing transaction connection."""
        with connection.cursor() as cursor:
            cursor.execute(
                DOSSIER_REVIEW_SESSIONS_QUERY,
                {"project_code": project_code, "review_id": review_id},
            )
            rows = cursor.fetchall()
        return [self._normalize_dossier_review_session_row(row) for row in rows]