from __future__ import annotations

from typing import Any

from ..base import BasePostgresClient


class EmployeeLogsFetchMixin(BasePostgresClient):
    """Employee log queries and row normalization."""

    def fetch_employee_log_records(self) -> list[dict[str, Any]]:
        """Fetch employee activity log records strictly from operational work log tables."""
        real_query = """
            WITH base AS (
                SELECT DISTINCT ON (ws.employee_id, ws.project_id, ws.shift_date, ws.shift_code)
                    ws.id,
                    ws.employee_id,
                    ws.project_id,
                    ws.shift_date,
                    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,
                    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
                ORDER BY ws.employee_id, ws.project_id, ws.shift_date DESC, ws.shift_code, ws.updated_at DESC, ws.created_at DESC, ws.id DESC
            )
            SELECT
                base.employee_code,
                base.employee_name,
                base.role_title,
                base.agent_role,
                base.project_code,
                base.project_name,
                base.status,
                TO_CHAR(base.shift_date, 'YYYY-MM-DD') AS 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
            ORDER BY base.employee_code, base.project_code, base.shift_date DESC
        """
        with self.get_connection() as connection:
            with connection.cursor() as cursor:
                if not self._table_exists(
                    cursor, "employee_work_log_sessions"
                ) or not self._table_exists(
                    cursor,
                    "employee_work_log_actions",
                ):
                    return []
                cursor.execute(real_query)
                rows = cursor.fetchall()
        return [self._normalize_employee_log_row(row) for row in rows]

    def _normalize_employee_log_row(self, row: dict[str, Any]) -> dict[str, Any]:
        """Convert PostgreSQL employee log data into API-compatible structures."""
        return {
            "employee_code": row["employee_code"],
            "employee_name": row["employee_name"],
            "role_title": row["role_title"],
            "agent_role": row["agent_role"],
            "project_code": row["project_code"],
            "project_name": row["project_name"],
            "status": row["status"],
            "shift_date": row["shift_date"],
            "summary": row["summary"],
            "priority": row["priority"],
            "role_group": row.get("role_group"),
            "next_priority": row.get("next_priority"),
            "progress_percent": row.get("progress_percent", 0),
            "completed_count": row.get("completed_count", 0),
            "missing_count": row.get("missing_count", 0),
            "findings": self._ensure_string_list(row.get("findings")),
            "missing_items": self._ensure_string_list(row.get("missing_items")),
            "notes": self._ensure_string_list(row.get("notes")),
            "logs": self._normalize_employee_logs(row.get("logs")),
        }

    def _normalize_employee_logs(self, value: Any) -> list[dict[str, Any]]:
        """Normalize PostgreSQL JSON output into employee log item dictionaries."""
        items = self._ensure_list(value)
        normalized_items: list[dict[str, Any]] = []
        for item in items:
            normalized_items.append(
                {
                    "timestamp": str(item.get("timestamp") or ""),
                    "action": str(item.get("action") or ""),
                    "ai_request": item.get("ai_request"),
                    "ai_result": self._ensure_string_list(item.get("ai_result")),
                    "findings": self._ensure_string_list(item.get("findings")),
                    "notes": self._ensure_string_list(item.get("notes")),
                }
            )
        return normalized_items