from __future__ import annotations

import logging
from typing import Any

from psycopg.connection import Connection

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


class DossierReviewSessionMutationsMixin:
    """Creation and management mutations for Dossier Review sessions."""

    def create_dossier_review_session(
        self,
        payload: dict[str, Any],
        *,
        connection: Connection[Any] | None = None,
    ) -> dict[str, Any]:
        """Create a dossier review session with optional baseline snapshot and audit log."""
        create_session_query = """
            INSERT INTO dossier_review_sessions (
                company_id, project_id, review_code, dossier_scope, trigger_source, initiated_by_employee_id,
                lead_reviewer_employee_id, lead_agent_code, status, review_reason, review_summary,
                assigned_departments, metadata, last_reviewed_at
            )
            SELECT
                p.company_id, p.id, %(review_code)s, %(dossier_scope)s, %(trigger_source)s, initiator.id, lead.id,
                %(lead_agent_code)s, %(status)s, %(review_reason)s, %(review_summary)s, %(assigned_departments)s::jsonb,
                %(metadata)s::jsonb, NOW()
            FROM projects p
            LEFT JOIN employees initiator ON initiator.employee_code = %(initiated_by_employee_code)s
            LEFT JOIN employees lead ON lead.employee_code = %(lead_reviewer_employee_code)s
            WHERE p.project_code = %(project_code)s
            RETURNING id::text AS review_id
        """
        create_snapshot_query = """
            INSERT INTO dossier_review_snapshots (
                review_session_id, project_id, snapshot_type, captured_by_employee_id, captured_by_agent_code,
                label, summary, dossier_state, missing_matrix, readiness_summary
            )
            SELECT
                rs.id, rs.project_id, %(snapshot_type)s, employee.id, %(captured_by_agent_code)s, %(label)s, %(summary)s,
                %(dossier_state)s::jsonb, %(missing_matrix)s::jsonb, %(readiness_summary)s::jsonb
            FROM dossier_review_sessions rs
            LEFT JOIN employees employee ON employee.employee_code = %(captured_by_employee_code)s
            WHERE rs.id::text = %(review_id)s
            RETURNING id::text AS snapshot_id
        """
        review_id = self._execute_with_optional_connection(
            connection,
            lambda cursor: self._create_dossier_review_session_with_cursor(
                cursor=cursor,
                payload=payload,
                create_session_query=create_session_query,
                create_snapshot_query=create_snapshot_query,
            ),
        )
        return self._fetch_single_dossier_review_session(
            review_id=review_id,
            connection=connection,
            error_message="Không thể tải lại dossier review session vừa tạo.",
        )

    def create_dossier_review_finding(
        self,
        review_id: str,
        payload: dict[str, Any],
        *,
        connection: Connection[Any] | None = None,
    ) -> dict[str, Any]:
        """Create a finding under a dossier review session and attach evidences."""
        create_finding_query = """
            INSERT INTO dossier_review_findings (
                review_session_id, project_id, finding_code, finding_group, finding_type, title, description,
                document_type, dossier_stage, severity, impact_level, responsible_department_code,
                supplement_status, status, is_blocking, due_date, source_snapshot_id, metadata
            )
            SELECT
                rs.id, rs.project_id, %(finding_code)s, %(finding_group)s, %(finding_type)s, %(title)s, %(description)s,
                %(document_type)s, %(dossier_stage)s, %(severity)s, %(impact_level)s, %(responsible_department_code)s,
                %(supplement_status)s, %(status)s, %(is_blocking)s, %(due_date)s, snapshot.id, %(metadata)s::jsonb
            FROM dossier_review_sessions rs
            LEFT JOIN dossier_review_snapshots snapshot
                ON snapshot.id::text = %(source_snapshot_id)s
               AND snapshot.review_session_id = rs.id
            WHERE rs.id::text = %(review_id)s
            RETURNING id::text AS finding_id
        """
        create_evidence_query = """
            INSERT INTO dossier_review_finding_evidence (
                finding_id, evidence_type, source_name, source_uri, source_page, excerpt,
                rule_code, rationale, confidence_score, evidence_payload
            ) VALUES (
                %(finding_id)s::uuid, %(evidence_type)s, %(source_name)s, %(source_uri)s, %(source_page)s, %(excerpt)s,
                %(rule_code)s, %(rationale)s, %(confidence_score)s, %(evidence_payload)s::jsonb
            )
        """
        finding_id = self._execute_with_optional_connection(
            connection,
            lambda cursor: self._create_dossier_review_finding_with_cursor(
                cursor=cursor,
                review_id=review_id,
                payload=payload,
                create_finding_query=create_finding_query,
                create_evidence_query=create_evidence_query,
            ),
        )
        session = self._fetch_single_dossier_review_session(
            review_id=review_id,
            connection=connection,
            error_message="Không thể tải lại review session sau khi tạo finding.",
        )
        return self._find_session_finding(session, finding_id)