from __future__ import annotations

PHASE_SNAPSHOT_JOIN = """
    LEFT JOIN LATERAL (
        SELECT COALESCE(m.milestone_name, '') AS current_phase
        FROM project_milestones m
        WHERE m.project_id = p.id
        ORDER BY
            CASE WHEN COALESCE(m.status, '') IN ('completed', 'done', 'Hoàn thành') THEN 1 ELSE 0 END ASC,
            COALESCE(m.sort_order, 9999), m.target_date NULLS LAST, m.id
        LIMIT 1
    ) phase_snapshot ON TRUE
"""

SUMMARY_CARDS_JOIN = """
    LEFT JOIN LATERAL (
        SELECT json_agg(
            json_build_object('label', card.label, 'value', card.value, 'note', card.note, 'trend', card.trend)
            ORDER BY card.display_order, card.id
        ) AS summary_cards
        FROM (
            SELECT 1 AS id, 1 AS display_order, 'Tiến độ tổng' AS label, CONCAT(COALESCE(p.progress_percent, 0), '%') AS value, NULL::text AS note,
                   CASE WHEN COALESCE(p.progress_percent, 0) >= 60 THEN 'up' ELSE 'stable' END AS trend
            UNION ALL
            SELECT 2, 2, 'Ngân sách',
                   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 'Chưa cập nhật' END, NULL::text, 'stable'
            UNION ALL
            SELECT 3, 3, 'Rủi ro mở', COUNT(*)::text, NULL::text, 'stable'
            FROM project_risks r WHERE r.project_id = p.id AND COALESCE(r.status, 'open') NOT IN ('resolved', 'closed')
            UNION ALL
            SELECT 4, 4, 'Công việc sắp tới', COUNT(*)::text, NULL::text, 'stable'
            FROM project_tasks t WHERE t.project_id = p.id AND COALESCE(t.status, 'todo') NOT IN ('completed', 'done')
        ) card
    ) summary_cards ON TRUE
"""

PHASE_PROGRESS_JOIN = """
    LEFT JOIN LATERAL (
        SELECT json_agg(
            json_build_object('phase_code', COALESCE(m.milestone_code, CONCAT('phase-', m.id::text)), 'phase_name', m.milestone_name,
                              'progress_percent', COALESCE(m.progress_percent, 0), 'status', COALESCE(m.status, 'planned'),
                              'owner', COALESCE(e.full_name, 'Chưa phân công'), 'note', m.description)
            ORDER BY COALESCE(m.sort_order, 9999), m.target_date NULLS LAST, m.id
        ) AS phase_progress
        FROM project_milestones m
        LEFT JOIN employees e ON e.id = m.owner_id
        WHERE m.project_id = p.id
    ) phase_progress ON TRUE
"""

MILESTONES_JOIN = """
    LEFT JOIN LATERAL (
        SELECT json_agg(
            json_build_object('title', m.milestone_name, 'due_date', TO_CHAR(m.target_date, 'YYYY-MM-DD'),
                              'status', COALESCE(m.status, 'planned'), 'owner', COALESCE(e.full_name, 'Chưa phân công'),
                              'note', m.description, 'completed', CASE WHEN COALESCE(m.status, '') IN ('completed', 'done', 'Hoàn thành') THEN true ELSE false END)
            ORDER BY m.target_date NULLS LAST, m.id
        ) AS milestones
        FROM project_milestones m
        LEFT JOIN employees e ON e.id = m.owner_id
        WHERE m.project_id = p.id
    ) milestones ON TRUE
"""

TIMELINE_JOIN = """
    LEFT JOIN LATERAL (
        SELECT json_agg(
            json_build_object('event_date', TO_CHAR(COALESCE(e.event_date, e.created_at), 'YYYY-MM-DD'), 'title', e.title,
                              'badge', COALESCE(e.badge, e.event_type, 'Sự kiện'), 'description', e.description)
            ORDER BY COALESCE(e.event_date, e.created_at) DESC, e.id DESC
        ) AS timeline
        FROM project_timeline_events e
        WHERE e.project_id = p.id
    ) timeline ON TRUE
"""

RISKS_JOIN = """
    LEFT JOIN LATERAL (
        SELECT json_agg(
            json_build_object('title', r.risk_title, 'severity', COALESCE(r.severity, 'Trung bình'),
                              'owner', COALESCE(e.full_name, 'Chưa phân công'), 'mitigation', r.mitigation_plan)
            ORDER BY CASE WHEN LOWER(COALESCE(r.severity, '')) = 'cao' THEN 1 WHEN LOWER(COALESCE(r.severity, '')) = 'trung bình' THEN 2 ELSE 3 END, r.id
        ) AS risks
        FROM project_risks r
        LEFT JOIN employees e ON e.id = r.owner_id
        WHERE r.project_id = p.id AND COALESCE(r.status, 'open') NOT IN ('resolved', 'closed')
    ) risks ON TRUE
"""

TASKS_JOIN = """
    LEFT JOIN LATERAL (
        SELECT json_agg(
            json_build_object('title', t.task_name, 'due_date', TO_CHAR(t.due_date, 'YYYY-MM-DD'),
                              'priority', COALESCE(t.priority, 'Trung bình'), 'owner', COALESCE(e.full_name, 'Chưa phân công'))
            ORDER BY t.due_date NULLS LAST, t.id
        ) AS upcoming_tasks
        FROM project_tasks t
        LEFT JOIN employees e ON e.id = t.assigned_to_id
        WHERE t.project_id = p.id AND COALESCE(t.status, 'todo') NOT IN ('completed', 'done')
    ) tasks ON TRUE
"""