from __future__ import annotations

import json
from typing import Any

from ..base import BasePostgresClient


class EmployeeLogsWriteMixin(BasePostgresClient):
    """Employee work log session upsert and action appending."""

    def upsert_employee_work_log_session(
        self, payload: dict[str, Any]
    ) -> dict[str, Any]:
        """Create or update an employee work log session for a project/shift pair."""
        query = """
            INSERT INTO employee_work_log_sessions (
                company_id, employee_id, project_id, shift_date, shift_code, role_title, agent_role, status,
                summary, priority, role_group, next_priority, progress_percent, completed_count, missing_count,
                findings, missing_items, notes, metadata
            )
            SELECT
                project.company_id, employee.id, project.id, COALESCE(%(shift_date)s::date, CURRENT_DATE), %(shift_code)s,
                %(role_title)s, %(agent_role)s, %(status)s, %(summary)s, %(priority)s, %(role_group)s, %(next_priority)s,
                %(progress_percent)s, %(completed_count)s, %(missing_count)s, %(findings)s::jsonb, %(missing_items)s::jsonb,
                %(notes)s, %(metadata)s::jsonb
            FROM employees employee
            JOIN projects project ON project.project_code = %(project_code)s
            WHERE employee.employee_code = %(employee_code)s
            ON CONFLICT (employee_id, project_id, shift_date, shift_code)
            DO UPDATE SET
                role_title = COALESCE(EXCLUDED.role_title, employee_work_log_sessions.role_title),
                agent_role = COALESCE(EXCLUDED.agent_role, employee_work_log_sessions.agent_role),
                status = COALESCE(EXCLUDED.status, employee_work_log_sessions.status),
                summary = COALESCE(EXCLUDED.summary, employee_work_log_sessions.summary),
                priority = COALESCE(EXCLUDED.priority, employee_work_log_sessions.priority),
                role_group = COALESCE(EXCLUDED.role_group, employee_work_log_sessions.role_group),
                next_priority = COALESCE(EXCLUDED.next_priority, employee_work_log_sessions.next_priority),
                progress_percent = COALESCE(EXCLUDED.progress_percent, employee_work_log_sessions.progress_percent),
                completed_count = COALESCE(EXCLUDED.completed_count, employee_work_log_sessions.completed_count),
                missing_count = COALESCE(EXCLUDED.missing_count, employee_work_log_sessions.missing_count),
                findings = CASE
                    WHEN EXCLUDED.findings IS NULL OR EXCLUDED.findings = '[]'::jsonb THEN employee_work_log_sessions.findings
                    ELSE EXCLUDED.findings
                END,
                missing_items = CASE
                    WHEN EXCLUDED.missing_items IS NULL OR EXCLUDED.missing_items = '[]'::jsonb THEN employee_work_log_sessions.missing_items
                    ELSE EXCLUDED.missing_items
                END,
                notes = COALESCE(EXCLUDED.notes, employee_work_log_sessions.notes),
                metadata = COALESCE(employee_work_log_sessions.metadata, '{}'::jsonb) || COALESCE(EXCLUDED.metadata, '{}'::jsonb),
                updated_at = NOW()
            RETURNING id::text AS work_log_session_id
        """
        with self.get_connection() as connection:
            with connection.cursor() as cursor:
                cursor.execute(
                    query, self._build_employee_work_log_session_params(payload)
                )
                row = cursor.fetchone()
                if not row:
                    raise ValueError(
                        "Không tìm thấy nhân sự hoặc dự án để tạo nhật ký công việc."
                    )
            connection.commit()
        return self.get_employee_work_log_session_by_id(row["work_log_session_id"])

    def append_employee_work_log_action(
        self, payload: dict[str, Any]
    ) -> dict[str, Any]:
        """Append one detailed action under an employee work log session."""
        query = """
            INSERT INTO employee_work_log_actions (
                work_log_session_id, company_id, employee_id, project_id, shift_date, action_timestamp, action_type,
                action, ai_request, ai_result, findings, notes, external_ref, metadata
            )
            SELECT
                session.id, session.company_id, session.employee_id, session.project_id, session.shift_date,
                COALESCE(%(action_timestamp)s::timestamptz, NOW()), %(action_type)s, %(action)s, %(ai_request)s,
                %(ai_result)s::jsonb, %(findings)s::jsonb, %(notes)s, %(external_ref)s, %(metadata)s::jsonb
            FROM employee_work_log_sessions session
            WHERE session.id::text = %(work_log_session_id)s
            ON CONFLICT (work_log_session_id, action_timestamp, action, COALESCE(external_ref, ''))
            DO UPDATE SET
                action_type = COALESCE(EXCLUDED.action_type, employee_work_log_actions.action_type),
                ai_request = COALESCE(EXCLUDED.ai_request, employee_work_log_actions.ai_request),
                ai_result = CASE
                    WHEN EXCLUDED.ai_result IS NULL OR EXCLUDED.ai_result = '[]'::jsonb THEN employee_work_log_actions.ai_result
                    ELSE EXCLUDED.ai_result
                END,
                findings = CASE
                    WHEN EXCLUDED.findings IS NULL OR EXCLUDED.findings = '[]'::jsonb THEN employee_work_log_actions.findings
                    ELSE EXCLUDED.findings
                END,
                notes = COALESCE(EXCLUDED.notes, employee_work_log_actions.notes),
                metadata = COALESCE(employee_work_log_actions.metadata, '{}'::jsonb) || COALESCE(EXCLUDED.metadata, '{}'::jsonb)
            RETURNING id::text AS action_id
        """
        with self.get_connection() as connection:
            with connection.cursor() as cursor:
                cursor.execute(
                    query, self._build_employee_work_log_action_params(payload)
                )
                row = cursor.fetchone()
                if not row:
                    raise ValueError(
                        "Không tìm thấy work log session để ghi nhận hành động."
                    )
            connection.commit()
        return {
            "action_id": row["action_id"],
            "work_log_session_id": payload.get("work_log_session_id"),
        }

    def get_employee_work_log_session_by_id(
        self, work_log_session_id: str
    ) -> dict[str, Any]:
        """Fetch a single normalized employee work log record by session id."""
        query = """
            WITH base AS (
                SELECT
                    ws.id,
                    COALESCE(e.employee_code, CONCAT('EMP-', ws.employee_id::text), 'EMP-UNKNOWN') AS employee_code,
                    COALESCE(e.full_name, 'Chưa cập nhật') AS employee_name,
                    COALESCE(ws.role_title, e.job_title, 'Chưa cập nhật') AS role_title,
                    COALESCE(ws.agent_role, e.department, ws.role_group, 'Theo dõi vận hành') AS agent_role,
                    COALESCE(p.project_code, '') AS project_code,
                    COALESCE(p.project_name, 'Chưa gán dự án') AS project_name,
                    COALESCE(ws.status, 'Đang theo dõi') AS status,
                    TO_CHAR(ws.shift_date, 'YYYY-MM-DD') AS shift_date,
                    COALESCE(ws.summary, 'Chưa có tóm tắt ca làm việc.') AS summary,
                    COALESCE(ws.priority, 'Trung bình') AS priority,
                    COALESCE(ws.role_group, 'Chưa phân nhóm') AS role_group,
                    COALESCE(ws.next_priority, 'Chưa xác định ưu tiên tiếp theo') AS next_priority,
                    COALESCE(ws.progress_percent, 0) AS progress_percent,
                    COALESCE(ws.completed_count, 0) AS completed_count,
                    COALESCE(ws.missing_count, 0) AS missing_count,
                    CASE WHEN jsonb_typeof(COALESCE(ws.findings, '[]'::jsonb)) = 'array' THEN COALESCE(ws.findings, '[]'::jsonb) ELSE '[]'::jsonb END AS findings,
                    CASE WHEN jsonb_typeof(COALESCE(ws.missing_items, '[]'::jsonb)) = 'array' THEN COALESCE(ws.missing_items, '[]'::jsonb) ELSE '[]'::jsonb END AS missing_items,
                    CASE WHEN ws.notes IS NULL OR BTRIM(ws.notes) = '' THEN '[]'::jsonb ELSE jsonb_build_array(ws.notes) END AS notes
                FROM employee_work_log_sessions ws
                LEFT JOIN employees e ON e.id = ws.employee_id
                LEFT JOIN projects p ON p.id = ws.project_id
                WHERE ws.id::text = %(work_log_session_id)s
            )
            SELECT
                base.employee_code, base.employee_name, base.role_title, base.agent_role, base.project_code,
                base.project_name, base.status, base.shift_date, base.summary, base.priority, base.role_group,
                base.next_priority, base.progress_percent, base.completed_count, base.missing_count,
                base.findings, base.missing_items, base.notes, COALESCE(detail_logs.logs, '[]'::jsonb) AS logs
            FROM base
            LEFT JOIN LATERAL (
                SELECT jsonb_agg(
                    jsonb_build_object(
                        'timestamp', COALESCE(TO_CHAR(COALESCE(log_item.action_timestamp, log_item.created_at), 'HH24:MI'), ''),
                        'action', COALESCE(log_item.action, log_item.action_type, 'Cập nhật công việc'),
                        'ai_request', to_jsonb(log_item.ai_request),
                        'ai_result', CASE
                            WHEN jsonb_typeof(COALESCE(log_item.ai_result, '[]'::jsonb)) = 'array' THEN COALESCE(log_item.ai_result, '[]'::jsonb)
                            WHEN log_item.ai_result IS NULL THEN '[]'::jsonb
                            ELSE jsonb_build_array(log_item.ai_result)
                        END,
                        'findings', CASE
                            WHEN jsonb_typeof(COALESCE(log_item.findings, '[]'::jsonb)) = 'array' THEN COALESCE(log_item.findings, '[]'::jsonb)
                            WHEN log_item.findings IS NULL THEN '[]'::jsonb
                            ELSE jsonb_build_array(log_item.findings)
                        END,
                        'notes', CASE
                            WHEN log_item.notes IS NULL OR BTRIM(log_item.notes) = '' THEN '[]'::jsonb
                            ELSE jsonb_build_array(log_item.notes)
                        END
                    )
                    ORDER BY COALESCE(log_item.action_timestamp, log_item.created_at) DESC, log_item.created_at DESC, log_item.id DESC
                ) AS logs
                FROM employee_work_log_actions log_item
                WHERE log_item.work_log_session_id = base.id
            ) detail_logs ON TRUE
        """
        with self.get_connection() as connection, connection.cursor() as cursor:
            cursor.execute(query, {"work_log_session_id": work_log_session_id})
            row = cursor.fetchone()
        if not row:
            raise ValueError("Không tìm thấy employee work log session.")
        return {
            "work_log_session_id": work_log_session_id,
            **self._normalize_employee_log_row(row),
        }

    def _build_employee_work_log_session_params(
        payload: dict[str, Any],
    ) -> dict[str, Any]:
        return {
            "employee_code": payload.get("employee_code"),
            "project_code": payload.get("project_code"),
            "shift_date": payload.get("shift_date"),
            "shift_code": payload.get("shift_code") or "day",
            "role_title": payload.get("role_title"),
            "agent_role": payload.get("agent_role"),
            "status": payload.get("status") or "Đang xử lý",
            "summary": payload.get("summary"),
            "priority": payload.get("priority") or "Trung bình",
            "role_group": payload.get("role_group"),
            "next_priority": payload.get("next_priority"),
            "progress_percent": payload.get("progress_percent") or 0,
            "completed_count": payload.get("completed_count") or 0,
            "missing_count": payload.get("missing_count") or 0,
            "findings": json.dumps(payload.get("findings") or [], ensure_ascii=False),
            "missing_items": json.dumps(
                payload.get("missing_items") or [], ensure_ascii=False
            ),
            "notes": "\n".join(
                str(item) for item in (payload.get("notes") or []) if str(item).strip()
            ),
            "metadata": json.dumps(payload.get("metadata") or {}, ensure_ascii=False),
        }

    def _build_employee_work_log_action_params(
        payload: dict[str, Any],
    ) -> dict[str, Any]:
        return {
            "work_log_session_id": payload.get("work_log_session_id"),
            "action_timestamp": payload.get("action_timestamp"),
            "action_type": payload.get("action_type") or payload.get("action"),
            "action": payload.get("action"),
            "ai_request": payload.get("ai_request"),
            "ai_result": json.dumps(payload.get("ai_result") or [], ensure_ascii=False),
            "findings": json.dumps(payload.get("findings") or [], ensure_ascii=False),
            "notes": "\n".join(
                str(item) for item in (payload.get("notes") or []) if str(item).strip()
            ),
            "external_ref": payload.get("external_ref"),
            "metadata": json.dumps(payload.get("metadata") or {}, ensure_ascii=False),
        }