from __future__ import annotations

from .project_management_subqueries import (
    MILESTONES_JOIN,
    PHASE_PROGRESS_JOIN,
    PHASE_SNAPSHOT_JOIN,
    RISKS_JOIN,
    SUMMARY_CARDS_JOIN,
    TASKS_JOIN,
    TIMELINE_JOIN,
)

FETCH_PROJECT_MANAGEMENT_SQL = f"""
    SELECT
        p.project_code,
        p.project_name,
        COALESCE(p.client_name, c.display_name, c.legal_name, '') AS client_name,
        COALESCE(p.location, '') AS location,
        COALESCE(pm.full_name, '') AS project_manager,
        COALESCE(p.status, '') AS status,
        COALESCE(phase_snapshot.current_phase, '') AS current_phase,
        COALESCE(p.progress_percent, 0) AS overall_progress_percent,
        CASE
            WHEN p.contract_value IS NOT NULL AND p.budget_amount IS NOT NULL THEN
                CONCAT('Ngân sách ', TRIM(TO_CHAR(p.budget_amount, 'FM999999999990D00')), ' / Hợp đồng ', TRIM(TO_CHAR(p.contract_value, 'FM999999999990D00')))
            WHEN p.budget_amount IS NOT NULL THEN
                CONCAT('Ngân sách ', TRIM(TO_CHAR(p.budget_amount, 'FM999999999990D00')))
            WHEN p.contract_value IS NOT NULL THEN
                CONCAT('Giá trị hợp đồng ', TRIM(TO_CHAR(p.contract_value, 'FM999999999990D00')))
            ELSE ''
        END AS budget_status,
        COALESCE(summary_cards.summary_cards::jsonb, '[]'::jsonb) AS summary_cards,
        COALESCE(phase_progress.phase_progress::jsonb, '[]'::jsonb) AS phase_progress,
        COALESCE(milestones.milestones::jsonb, '[]'::jsonb) AS milestones,
        COALESCE(timeline.timeline::jsonb, '[]'::jsonb) AS timeline,
        COALESCE(risks.risks::jsonb, '[]'::jsonb) AS risks,
        COALESCE(tasks.upcoming_tasks::jsonb, '[]'::jsonb) AS upcoming_tasks
    FROM projects p
    LEFT JOIN companies c ON c.id = p.company_id
    LEFT JOIN employees pm ON pm.id = p.project_manager_id
    {PHASE_SNAPSHOT_JOIN}
    {SUMMARY_CARDS_JOIN}
    {PHASE_PROGRESS_JOIN}
    {MILESTONES_JOIN}
    {TIMELINE_JOIN}
    {RISKS_JOIN}
    {TASKS_JOIN}
    ORDER BY p.created_at DESC, p.id DESC;
"""