from __future__ import annotations

import logging
from typing import Any

from psycopg.connection import Connection

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


class DossierReviewFindingMutationsMixin:
    """Finding assignment and supplement submissions."""

    def assign_dossier_review_finding(
        self,
        review_id: str,
        finding_id: str,
        payload: dict[str, Any],
        *,
        connection: Connection[Any] | None = None,
    ) -> dict[str, Any]:
        """Assign a finding to a department or employee and update workflow state."""
        create_assignment_query = """
            INSERT INTO dossier_review_assignments (
                review_session_id, finding_id, assigned_department_code, assigned_employee_id, assigned_by_employee_id,
                assignment_note, status, priority, due_date, accepted_at, metadata
            )
            SELECT
                review.id, finding.id, %(assigned_department_code_insert)s::varchar,
                (SELECT employee.id FROM employees employee WHERE employee.employee_code = %(assigned_employee_code_lookup)s::varchar LIMIT 1),
                (SELECT employee.id FROM employees employee WHERE employee.employee_code = %(assigned_by_employee_code_lookup)s::varchar LIMIT 1),
                %(assignment_note_insert)s::text, %(status_insert)s::varchar, %(priority_insert)s::varchar, %(due_date_insert)s::date,
                CASE
                    WHEN %(status_acceptance)s::varchar IN ('accepted', 'in_progress', 'submitted', 'returned', 'verified', 'overdue')
                    THEN NOW()
                    ELSE NULL
                END,
                %(metadata_insert)s::jsonb
            FROM dossier_review_sessions review
            JOIN dossier_review_findings finding ON finding.review_session_id = review.id
            WHERE review.id::text = %(review_id)s::text AND finding.id::text = %(finding_id)s::text
            RETURNING id::text AS assignment_id
        """
        finding_state_query = """
            SELECT status, supplement_status, responsible_department_code, due_date
            FROM dossier_review_findings
            WHERE id::text = %(finding_id)s
        """
        update_finding_query = """
            UPDATE dossier_review_findings
            SET
                current_assignment_id = %(assignment_id_update)s::uuid,
                responsible_department_code = %(assigned_department_code_update)s::varchar,
                status = %(finding_status_update)s::varchar,
                supplement_status = %(supplement_status_update)s::varchar,
                due_date = COALESCE(%(due_date_update)s::date, due_date),
                updated_at = NOW()
            WHERE id::text = %(finding_id_update)s::text
        """
        assignment_id = self._execute_with_optional_connection(
            connection,
            lambda cursor: self._assign_dossier_review_finding_with_cursor(
                cursor=cursor,
                review_id=review_id,
                finding_id=finding_id,
                payload=payload,
                create_assignment_query=create_assignment_query,
                finding_state_query=finding_state_query,
                update_finding_query=update_finding_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 assign finding.",
        )
        return self._find_finding_assignment(session, finding_id, assignment_id)

    def submit_dossier_review_supplement(
        self,
        review_id: str,
        finding_id: str,
        payload: dict[str, Any],
        *,
        connection: Connection[Any] | None = None,
    ) -> dict[str, Any]:
        """Record a supplement submission for an assignment and update review status."""
        create_submission_query = """
            INSERT INTO dossier_review_submissions (
                review_session_id, assignment_id, finding_id, submitted_by_employee_id,
                submission_type, submission_note, attachment_count, submitted_payload
            )
            SELECT
                f.review_session_id, a.id, f.id, submitter.id, %(submission_type)s, %(submission_note)s,
                %(attachment_count)s, %(submitted_payload)s::jsonb
            FROM dossier_review_sessions rs
            JOIN dossier_review_findings f ON f.review_session_id = rs.id AND f.id::text = %(finding_id)s
            JOIN dossier_review_assignments a ON a.finding_id = f.id AND a.id::text = %(assignment_id)s
            LEFT JOIN employees submitter ON submitter.employee_code = %(submitted_by_employee_code)s
            WHERE rs.id::text = %(review_id)s
            RETURNING id::text AS submission_id
        """
        assignment_state_query = "SELECT status, latest_submission_note, submitted_at FROM dossier_review_assignments WHERE id::text = %(assignment_id)s"
        finding_state_query = "SELECT status, supplement_status FROM dossier_review_findings WHERE id::text = %(finding_id)s"
        update_assignment_query = """
            UPDATE dossier_review_assignments
            SET
                status = %(assignment_status)s::text,
                latest_submission_note = %(submission_note)s,
                accepted_at = CASE
                    WHEN accepted_at IS NULL AND %(assignment_status)s::text IN ('accepted', 'in_progress', 'submitted', 'returned', 'verified', 'overdue') THEN NOW()
                    ELSE accepted_at
                END,
                submitted_at = NOW(),
                updated_at = NOW()
            WHERE id::text = %(assignment_id)s
        """
        update_finding_query = """
            UPDATE dossier_review_findings
            SET status = %(finding_status)s::text, supplement_status = %(supplement_status)s::text, updated_at = NOW()
            WHERE id::text = %(finding_id)s
        """
        submission_id = self._execute_with_optional_connection(
            connection,
            lambda cursor: self._submit_dossier_review_supplement_with_cursor(
                cursor=cursor,
                review_id=review_id,
                finding_id=finding_id,
                payload=payload,
                create_submission_query=create_submission_query,
                assignment_state_query=assignment_state_query,
                finding_state_query=finding_state_query,
                update_assignment_query=update_assignment_query,
                update_finding_query=update_finding_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 nộp bổ sung.",
        )
        return self._find_assignment_submission(
            session, finding_id, payload.get("assignment_id"), submission_id
        )