from __future__ import annotations


class BasePostgresPilotMixin:
    """Legacy employee work log queries mixin."""

    def _employee_pilot_logs_query() -> str:
        """SQL fallback query using the legacy pilot log source."""
        return """
            SELECT
                COALESCE(employee_code, CONCAT('EMP-', employee_id::text), 'EMP-UNKNOWN') AS employee_code,
                COALESCE(employee_name, 'Chưa cập nhật') AS employee_name,
                COALESCE(role_title, 'Chưa cập nhật') AS role_title,
                COALESCE(agent_role, role_group, 'Theo dõi pilot') AS agent_role,
                COALESCE(project_code, '') AS project_code,
                COALESCE(project_name, 'Chưa gán dự án') AS project_name,
                COALESCE(status, 'Đang theo dõi') AS status,
                COALESCE(TO_CHAR(shift_date, 'YYYY-MM-DD'), TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD')) AS shift_date,
                COALESCE(summary, 'Chưa có tóm tắt ca làm việc pilot.') AS summary,
                COALESCE(priority, 'Trung bình') AS priority,
                COALESCE(role_group, agent_role, 'Chưa phân nhóm') AS role_group,
                COALESCE(next_priority, 'Chưa xác định ưu tiên tiếp theo') AS next_priority,
                COALESCE(progress_percent, 0) AS progress_percent,
                COALESCE(completed_count, 0) AS completed_count,
                COALESCE(missing_count, 0) AS missing_count,
                COALESCE(findings::jsonb, '[]'::jsonb) AS findings,
                COALESCE(missing_items::jsonb, '[]'::jsonb) AS missing_items,
                COALESCE(notes::jsonb, '[]'::jsonb) AS notes,
                COALESCE(logs::jsonb, '[]'::jsonb) AS logs
            FROM (
                SELECT
                    base.employee_id,
                    base.project_id,
                    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,
                    summary_lists.findings,
                    summary_lists.missing_items,
                    summary_lists.notes,
                    detail_logs.logs
                FROM (
                    SELECT DISTINCT ON (l.employee_id)
                        l.employee_id,
                        l.project_id,
                        COALESCE(e.employee_code, CONCAT('EMP-', l.employee_id::text)) AS employee_code,
                        COALESCE(e.full_name, 'Chưa cập nhật') AS employee_name,
                        COALESCE(e.job_title, 'Chưa cập nhật') AS role_title,
                        COALESCE(e.department, l.role_group, 'Theo dõi pilot') AS agent_role,
                        COALESCE(p.project_code, '') AS project_code,
                        COALESCE(p.project_name, 'Chưa gán dự án') AS project_name,
                        COALESCE(l.status, 'Đang theo dõi') AS status,
                        l.shift_date,
                        COALESCE(l.summary, 'Chưa có tóm tắt ca làm việc pilot.') AS summary,
                        COALESCE(l.priority, 'Trung bình') AS priority,
                        COALESCE(l.role_group, 'Chưa phân nhóm') AS role_group,
                        COALESCE(l.next_priority, 'Chưa xác định ưu tiên tiếp theo') AS next_priority,
                        COALESCE(l.progress_percent, 0) AS progress_percent,
                        COALESCE(l.completed_count, 0) AS completed_count,
                        COALESCE(l.missing_count, 0) AS missing_count
                    FROM employee_pilot_logs l
                    LEFT JOIN employees e ON e.id = l.employee_id
                    LEFT JOIN projects p ON p.id = l.project_id
                    ORDER BY l.employee_id, l.shift_date DESC, l.action_timestamp DESC, l.created_at DESC, l.id DESC
                ) base
                LEFT JOIN LATERAL (
                    SELECT
                        json_agg(DISTINCT finding.value) FILTER (WHERE finding.value IS NOT NULL) AS findings,
                        json_agg(DISTINCT missing_item.value) FILTER (WHERE missing_item.value IS NOT NULL) AS missing_items,
                        json_agg(DISTINCT note_item.value) FILTER (WHERE note_item.value IS NOT NULL) AS notes
                    FROM employee_pilot_logs l
                    LEFT JOIN LATERAL jsonb_array_elements_text(
                        CASE
                            WHEN jsonb_typeof(COALESCE(l.findings::jsonb, '[]'::jsonb)) = 'array' THEN COALESCE(l.findings::jsonb, '[]'::jsonb)
                            ELSE '[]'::jsonb
                        END
                    ) AS finding(value) ON TRUE
                    LEFT JOIN LATERAL jsonb_array_elements_text(
                        CASE
                            WHEN jsonb_typeof(COALESCE(l.missing_items::jsonb, '[]'::jsonb)) = 'array' THEN COALESCE(l.missing_items::jsonb, '[]'::jsonb)
                            ELSE '[]'::jsonb
                        END
                    ) AS missing_item(value) ON TRUE
                    LEFT JOIN LATERAL jsonb_array_elements_text(
                        CASE
                            WHEN jsonb_typeof(COALESCE(l.notes::jsonb, '[]'::jsonb)) = 'array' THEN COALESCE(l.notes::jsonb, '[]'::jsonb)
                            ELSE '[]'::jsonb
                        END
                    ) AS note_item(value) ON TRUE
                    WHERE l.employee_id = base.employee_id
                      AND l.project_id = base.project_id
                      AND l.shift_date = base.shift_date
                ) summary_lists ON TRUE
                LEFT JOIN LATERAL (
                    SELECT jsonb_agg(
                        jsonb_build_object(
                            'timestamp', COALESCE(TO_CHAR(log_item.action_timestamp, 'HH24:MI'), ''),
                            'action', COALESCE(log_item.action, 'Cập nhật pilot'),
                            'ai_request', to_jsonb(log_item.ai_request),
                            'ai_result', CASE
                                WHEN jsonb_typeof(COALESCE(log_item.ai_result::jsonb, '[]'::jsonb)) = 'array' THEN COALESCE(log_item.ai_result::jsonb, '[]'::jsonb)
                                ELSE jsonb_build_array(log_item.ai_result::text)
                            END,
                            'findings', CASE
                                WHEN jsonb_typeof(COALESCE(log_item.findings::jsonb, '[]'::jsonb)) = 'array' THEN COALESCE(log_item.findings::jsonb, '[]'::jsonb)
                                ELSE jsonb_build_array(log_item.findings::text)
                            END,
                            'notes', CASE
                                WHEN jsonb_typeof(COALESCE(log_item.notes::jsonb, '[]'::jsonb)) = 'array' THEN COALESCE(log_item.notes::jsonb, '[]'::jsonb)
                                ELSE jsonb_build_array(log_item.notes::text)
                            END
                        )
                        ORDER BY log_item.shift_date DESC, log_item.action_timestamp DESC, log_item.created_at DESC, log_item.id DESC
                    ) AS logs
                    FROM employee_pilot_logs log_item
                    WHERE log_item.employee_id = base.employee_id
                      AND log_item.project_id = base.project_id
                      AND log_item.shift_date = base.shift_date
                ) detail_logs ON TRUE
            ) aggregated
            ORDER BY employee_code
        """