# app/src/storage/repositories/journal.py

from __future__ import annotations

import json
from typing import Any

from src.storage.session import get_connection


class JournalRepository:
    def add_event(
        self,
        *,
        level: str,
        event_type: str,
        message: str,
        payload: dict[str, Any] | None = None,
    ) -> None:
        payload_json = (
            json.dumps(payload, ensure_ascii=False)
            if payload is not None
            else None
        )

        with get_connection() as connection:
            with connection.cursor() as cursor:
                cursor.execute(
                    """
                    INSERT INTO journal_events (level, event_type, message, payload_json)
                    VALUES (%s, %s, %s, %s::jsonb)
                    """,
                    (
                        level.upper().strip(),
                        event_type.strip(),
                        message.strip(),
                        payload_json,
                    ),
                )

    def _parse_payload(self, raw_payload: Any) -> dict[str, Any] | None:
        if raw_payload is None:
            return None

        if isinstance(raw_payload, dict):
            return raw_payload

        if isinstance(raw_payload, str):
            try:
                parsed = json.loads(raw_payload)
                return parsed if isinstance(parsed, dict) else None
            except json.JSONDecodeError:
                return None

        return None

    def list_recent_events(self, limit: int = 10) -> list[dict[str, Any]]:
        with get_connection() as connection:
            with connection.cursor() as cursor:
                cursor.execute(
                    """
                    SELECT id, created_at, level, event_type, message, payload_json
                    FROM journal_events
                    ORDER BY created_at DESC, id DESC
                    LIMIT %s
                    """,
                    (limit,),
                )
                rows = cursor.fetchall()

        return [self._row_to_dict(row) for row in rows]

    def list_recent_with_offset(
        self,
        limit: int,
        offset: int,
    ) -> list[dict[str, Any]]:
        with get_connection() as connection:
            with connection.cursor() as cursor:
                cursor.execute(
                    """
                    SELECT id, created_at, level, event_type, message, payload_json
                    FROM journal_events
                    ORDER BY created_at DESC, id DESC
                    LIMIT %s OFFSET %s
                    """,
                    (limit, offset),
                )
                rows = cursor.fetchall()

        return [self._row_to_dict(row) for row in rows]

    def list_export_rows(
        self,
        limit: int = 5000,
        export_filter: str = "all",
    ) -> list[dict[str, Any]]:
        where_sql = ""

        if export_filter == "auto":
            where_sql = """
            WHERE
                COALESCE(payload_json ->> 'screen', '') = 'auto'
                OR event_type LIKE 'position_%%'
                OR event_type LIKE 'trade_%%'
                OR event_type LIKE 'runtime_%%'
                OR event_type IN (
                    'signal_summary',
                    'signal_ready',
                    'signal_changed',
                    'signal_blocked',
                    'execution_blocked',
                    'execution_quality_changed'
                )
            """

        elif export_filter == "trades":
            where_sql = """
            WHERE
                event_type IN (
                    'position_opened',
                    'position_closed',
                    'position_flipped',
                    'position_flip_blocked',
                    'trade_opened',
                    'trade_closed',
                    'trade_flipped'
                )
                OR event_type LIKE 'trade_%%'
            """

        elif export_filter == "errors":
            where_sql = """
            WHERE
                level IN ('ERROR', 'CRITICAL')
                OR (
                    level = 'WARNING'
                    AND (
                        event_type LIKE '%%error%%'
                        OR event_type LIKE '%%blocked%%'
                        OR event_type LIKE '%%failed%%'
                        OR COALESCE(payload_json ->> 'error_type', '') <> ''
                        OR COALESCE(payload_json ->> 'raw_error', '') <> ''
                    )
                )
            """

        elif export_filter == "not_auto":
            where_sql = """
            WHERE NOT (
                COALESCE(payload_json ->> 'screen', '') = 'auto'
                OR event_type LIKE 'position_%%'
                OR event_type LIKE 'trade_%%'
                OR event_type LIKE 'runtime_%%'
                OR event_type IN (
                    'signal_summary',
                    'signal_ready',
                    'signal_changed',
                    'signal_blocked',
                    'execution_blocked',
                    'execution_quality_changed'
                )
            )
            """

        with get_connection() as connection:
            with connection.cursor() as cursor:
                cursor.execute(
                    f"""
                    SELECT id, created_at, level, event_type, message, payload_json
                    FROM journal_events
                    {where_sql}
                    ORDER BY created_at DESC, id DESC
                    LIMIT %s
                    """,
                    (limit,),
                )
                rows = cursor.fetchall()

        return [self._row_to_dict(row) for row in rows]

    def count_events(self) -> int:
        with get_connection() as connection:
            with connection.cursor() as cursor:
                cursor.execute("SELECT COUNT(*) FROM journal_events")
                row = cursor.fetchone()

        return int(row[0]) if row else 0

    def delete_all(self) -> int:
        with get_connection() as connection:
            with connection.cursor() as cursor:
                cursor.execute("DELETE FROM journal_events")
                deleted_count = cursor.rowcount

        return int(deleted_count or 0)

    def delete_older_than_days(self, days: int) -> int:
        with get_connection() as connection:
            with connection.cursor() as cursor:
                cursor.execute(
                    """
                    DELETE FROM journal_events
                    WHERE created_at < NOW() - (%s * INTERVAL '1 day')
                    """,
                    (days,),
                )
                deleted_count = cursor.rowcount

        return int(deleted_count or 0)

    def _row_to_dict(self, row: tuple[Any, ...]) -> dict[str, Any]:
        return {
            "id": str(row[0]),
            "created_at": str(row[1]),
            "level": str(row[2]),
            "event_type": str(row[3]),
            "message": str(row[4]),
            "payload": self._parse_payload(row[5]),
        }