"""Database repositories for Publishing Integrity tables."""

import json
import uuid
import logging
from datetime import datetime, timezone
from typing import Optional, List, Dict, Any

import psycopg2
import psycopg2.extras

from .config import DB_HOST, DB_PORT, DB_NAME, DB_USER, DB_PASS
from .enums import ValidationState, AvailabilityStatus, LineupStatus, AuditAction
from .models import (
    PropEligibility, AuditLogEntry, RegressionFailure,
    AvailabilityReport, LineupConfirmation, ConflictDetail,
)

log = logging.getLogger("pub-integrity")


def get_connection():
    return psycopg2.connect(
        host=DB_HOST, port=DB_PORT, dbname=DB_NAME,
        user=DB_USER, password=DB_PASS,
    )


class PropEligibilityRepo:
    """CRUD for pi_prop_eligibility table."""

    @staticmethod
    def upsert(conn, elig: PropEligibility) -> str:
        cur = conn.cursor()
        elig_id = elig.id or str(uuid.uuid4())
        cur.execute("""
            INSERT INTO pi_prop_eligibility
                (id, run_id, game_id, player_id, player_name, team_id, league,
                 validation_state, market_presence_flag,
                 roster_verified_at, availability_verified_at, lineup_verified_at,
                 suppress_reason, suppress_reason_human, winning_source,
                 conflict_payload, publish_allowed)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            ON CONFLICT (game_id, player_id) DO UPDATE SET
                run_id = EXCLUDED.run_id,
                validation_state = EXCLUDED.validation_state,
                market_presence_flag = EXCLUDED.market_presence_flag,
                roster_verified_at = EXCLUDED.roster_verified_at,
                availability_verified_at = EXCLUDED.availability_verified_at,
                lineup_verified_at = EXCLUDED.lineup_verified_at,
                suppress_reason = EXCLUDED.suppress_reason,
                suppress_reason_human = EXCLUDED.suppress_reason_human,
                winning_source = EXCLUDED.winning_source,
                conflict_payload = EXCLUDED.conflict_payload,
                publish_allowed = EXCLUDED.publish_allowed,
                updated_at = NOW()
            RETURNING id
        """, (
            elig_id, elig.run_id, elig.game_id, elig.player_id,
            elig.player_name, elig.team_id, elig.league,
            elig.validation_state.value, elig.market_presence_flag,
            elig.roster_verified_at, elig.availability_verified_at,
            elig.lineup_verified_at,
            elig.suppress_reason, elig.suppress_reason_human,
            elig.winning_source,
            json.dumps(elig.conflict_payload) if elig.conflict_payload else None,
            elig.publish_allowed,
        ))
        result_id = cur.fetchone()[0]
        cur.close()
        return result_id

    @staticmethod
    def get_by_game(conn, game_id: str) -> List[Dict[str, Any]]:
        cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
        cur.execute("""
            SELECT * FROM pi_prop_eligibility WHERE game_id = %s ORDER BY player_name
        """, (game_id,))
        rows = cur.fetchall()
        cur.close()
        return rows

    @staticmethod
    def get_by_player(conn, player_id: str) -> List[Dict[str, Any]]:
        cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
        cur.execute("""
            SELECT * FROM pi_prop_eligibility WHERE player_id = %s ORDER BY created_at DESC
        """, (player_id,))
        rows = cur.fetchall()
        cur.close()
        return rows

    @staticmethod
    def get_suppressions(conn, league: Optional[str] = None, limit: int = 100) -> List[Dict[str, Any]]:
        cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
        if league:
            cur.execute("""
                SELECT * FROM pi_prop_eligibility
                WHERE publish_allowed = false AND league = %s
                ORDER BY updated_at DESC LIMIT %s
            """, (league, limit))
        else:
            cur.execute("""
                SELECT * FROM pi_prop_eligibility
                WHERE publish_allowed = false
                ORDER BY updated_at DESC LIMIT %s
            """, (limit,))
        rows = cur.fetchall()
        cur.close()
        return rows

    @staticmethod
    def get_prior_state(conn, game_id: str, player_id: str) -> Optional[str]:
        cur = conn.cursor()
        cur.execute("""
            SELECT validation_state FROM pi_prop_eligibility
            WHERE game_id = %s AND player_id = %s
        """, (game_id, player_id))
        row = cur.fetchone()
        cur.close()
        return row[0] if row else None


class AuditLogRepo:
    @staticmethod
    def insert(conn, entry: AuditLogEntry) -> str:
        cur = conn.cursor()
        entry_id = str(uuid.uuid4())
        cur.execute("""
            INSERT INTO pi_publish_audit_log
                (id, run_id, game_id, player_id, player_name, team_id, league,
                 action, validation_state, suppress_reason, suppress_reason_human,
                 winning_source, conflict_payload, prior_state, acted_at)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """, (
            entry_id, entry.run_id, entry.game_id, entry.player_id,
            entry.player_name, entry.team_id, entry.league,
            entry.action.value, entry.validation_state.value,
            entry.suppress_reason, entry.suppress_reason_human,
            entry.winning_source,
            json.dumps(entry.conflict_payload) if entry.conflict_payload else None,
            entry.prior_state,
            entry.acted_at or datetime.now(timezone.utc),
        ))
        cur.close()
        return entry_id

    @staticmethod
    def get_recent(conn, limit: int = 200) -> List[Dict[str, Any]]:
        cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
        cur.execute("""
            SELECT * FROM pi_publish_audit_log ORDER BY acted_at DESC LIMIT %s
        """, (limit,))
        rows = cur.fetchall()
        cur.close()
        return rows


class RegressionRepo:
    @staticmethod
    def insert(conn, failure: RegressionFailure) -> str:
        cur = conn.cursor()
        fail_id = str(uuid.uuid4())
        cur.execute("""
            INSERT INTO pi_regression_failures
                (id, run_id, test_name, league, entity_type, entity_id,
                 severity, failure_reason, failure_payload)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
        """, (
            fail_id, failure.run_id, failure.test_name, failure.league,
            failure.entity_type, failure.entity_id, failure.severity,
            failure.failure_reason,
            json.dumps(failure.failure_payload) if failure.failure_payload else None,
        ))
        cur.close()
        return fail_id

    @staticmethod
    def get_by_run(conn, run_id: str) -> List[Dict[str, Any]]:
        cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
        cur.execute("""
            SELECT * FROM pi_regression_failures WHERE run_id = %s ORDER BY created_at
        """, (run_id,))
        rows = cur.fetchall()
        cur.close()
        return rows