from datetime import datetime
from typing import Any
from uuid import UUID, uuid4

from psycopg import AsyncConnection, Connection
from psycopg.types.json import Jsonb


class AnalysisRepository:
    @staticmethod
    def create_run_sync(
        conn: Connection,
        *,
        name: str,
        run_type: str,
        created_by: str,
        started_at: datetime,
        status: str,
        parameters: dict[str, Any],
        run_id: UUID | None = None,
    ) -> dict[str, Any]:
        execution_id = run_id or uuid4()
        with conn.cursor() as cur:
            cur.execute(
                """
                INSERT INTO analysis.runs
                    (run_id, name, run_type, created_by, created_at,
                     started_at, status, parameters)
                VALUES (%s, %s, %s, %s, now(), %s, %s, %s)
                RETURNING run_id, name, run_type, created_by, created_at,
                          started_at, status, parameters
                """,
                (
                    execution_id,
                    name,
                    run_type,
                    created_by,
                    started_at,
                    status,
                    Jsonb(parameters),
                ),
            )
            created = cur.fetchone()
        if created is None:
            raise RuntimeError("analysis run insert returned no row")
        return created

    @staticmethod
    def update_run_sync(
        conn: Connection,
        run_id: UUID,
        *,
        status: str,
        created_by: str,
        parameters: dict[str, Any],
    ) -> None:
        with conn.cursor() as cur:
            cur.execute(
                """
                UPDATE analysis.runs
                SET created_by = %s, status = %s, parameters = %s
                WHERE run_id = %s
                """,
                (created_by, status, Jsonb(parameters), run_id),
            )
            if cur.rowcount != 1:
                raise LookupError(f"analysis run {run_id} does not exist")

    @staticmethod
    def insert_result_sync(
        conn: Connection,
        run_id: UUID,
        *,
        result_type: str,
        payload: dict[str, Any],
        node_id: str | None = None,
        link_id: str | None = None,
    ) -> None:
        with conn.cursor() as cur:
            cur.execute(
                """
                INSERT INTO analysis.results
                    (run_id, result_type, node_id, link_id, payload)
                VALUES (%s, %s, %s, %s, %s)
                """,
                (run_id, result_type, node_id, link_id, Jsonb(payload)),
            )

    @staticmethod
    def get_run_sync(conn: Connection, run_id: UUID) -> dict[str, Any] | None:
        with conn.cursor() as cur:
            cur.execute(
                """
                SELECT run_id, name, run_type, created_by, created_at,
                       started_at, status, parameters
                FROM analysis.runs
                WHERE run_id = %s
                """,
                (run_id,),
            )
            return cur.fetchone()

    @staticmethod
    async def list_runs(conn: AsyncConnection) -> list[dict]:
        async with conn.cursor() as cur:
            await cur.execute(
                """
                SELECT run_id, name, run_type, created_by, created_at,
                       started_at, status, parameters
                FROM analysis.runs
                ORDER BY created_at DESC, run_id
                """
            )
            return await cur.fetchall()

    @staticmethod
    async def get_run(conn: AsyncConnection, run_id: UUID) -> dict | None:
        async with conn.cursor() as cur:
            await cur.execute(
                """
                SELECT run_id, name, run_type, created_by, created_at,
                       started_at, status, parameters
                FROM analysis.runs
                WHERE run_id = %s
                """,
                (run_id,),
            )
            return await cur.fetchone()

    @staticmethod
    async def list_results(
        conn: AsyncConnection, run_id: UUID, result_type: str | None = None
    ) -> list[dict]:
        query = """
            SELECT result_id, run_id, result_type, node_id, link_id,
                   payload, created_at
            FROM analysis.results
            WHERE run_id = %s
        """
        params: tuple[UUID] | tuple[UUID, str] = (run_id,)
        if result_type is not None:
            query += " AND result_type = %s"
            params = (run_id, result_type)
        query += " ORDER BY created_at, result_id"

        async with conn.cursor() as cur:
            await cur.execute(query, params)
            return await cur.fetchall()