from __future__ import annotations

import logging
from typing import Any

logger = logging.getLogger("dscons.postgres.project_crud.queries")


class ProjectsQueriesMixin:
    """Project master list and detailed project view query operations."""

    def list_projects(
        self,
        status: str | None = None,
        search: str | None = None,
        company_id: str | None = None,
    ) -> list[dict[str, Any]]:
        """Lấy danh sách dự án kèm chỉ số WBS và tiến độ tổng hợp."""
        sql = """
            SELECT
                p.id,
                p.project_code,
                p.project_name,
                p.client_name,
                p.location,
                p.project_type,
                p.status,
                p.priority,
                p.budget_amount,
                p.contract_value,
                p.start_date,
                p.expected_end_date,
                p.actual_end_date,
                p.progress_percent,
                p.contract_number,
                p.contract_type,
                p.contract_duration_days,
                p.advance_payment_percent,
                p.retention_percent,
                p.contract_signing_date,
                p.notes,
                p.created_at,
                p.updated_at,
                pm.full_name AS project_manager_name,
                COALESCE(wbs_stats.total_tasks, 0) AS total_wbs_tasks,
                COALESCE(wbs_stats.completed_tasks, 0) AS completed_wbs_tasks
            FROM projects p
            LEFT JOIN employees pm ON p.project_manager_id = pm.id
            LEFT JOIN LATERAL (
                SELECT
                    COUNT(w.id) FILTER (WHERE w.task_type = 'task') AS total_tasks,
                    COUNT(w.id) FILTER (WHERE w.task_type = 'task' AND w.status = 'completed') AS completed_tasks
                FROM erp_project_wbs w
                WHERE w.project_id = p.id
            ) wbs_stats ON TRUE
            WHERE 1=1
        """
        params: list[Any] = []
        if company_id:
            sql += " AND p.company_id = %s"
            params.append(company_id)
        if status and status != "all":
            if status == "active" or status == "in_progress":
                sql += " AND p.status IN ('active', 'in_progress')"
            else:
                sql += " AND p.status = %s"
                params.append(status)
        if search:
            sql += " AND (p.project_code ILIKE %s OR p.project_name ILIKE %s OR p.client_name ILIKE %s OR p.contract_number ILIKE %s)"
            pattern = f"%{search}%"
            params.extend([pattern, pattern, pattern, pattern])

        sql += """ ORDER BY 
            CASE 
                WHEN p.project_code LIKE 'DA-2026%%' OR p.project_code IN ('DA-2608281122', 'DA-IB2600471890', 'DA-KM-DENCS-2026', 'DA-THCS-DAIDONG-2026', 'DA-DADO-PHULIEN-2026', 'DA-THIENDUYEN-KIENHAI-2026') THEN 2026
                WHEN p.project_code LIKE 'DA-2025%%' OR p.project_code LIKE '%%-2025%%' THEN 2025
                WHEN p.project_code LIKE 'DA-2024%%' OR p.project_code LIKE '%%-2024%%' THEN 2024
                WHEN p.project_code LIKE 'DA-2023%%' OR p.project_code LIKE '%%-2023%%' THEN 2023
                WHEN p.project_code LIKE 'DA-2022%%' OR p.project_code LIKE '%%-2022%%' THEN 2022
                ELSE 2020
            END DESC,
            COALESCE(p.start_date, p.contract_signing_date, p.created_at::date) DESC,
            p.project_code DESC;
        """

        with self.get_connection() as conn, conn.cursor() as cur:
            cur.execute(sql, params)
            return cur.fetchall()

    def get_project_detail(self, project_id_or_code: str) -> dict[str, Any] | None:
        """Lấy chi tiết toàn diện của 1 dự án kèm WBS tree, milestones, timeline, risks."""
        # 1. Project Record
        sql_proj = """
            SELECT 
                p.*,
                pm.full_name AS project_manager_name,
                c.legal_name AS company_legal_name
            FROM projects p
            LEFT JOIN employees pm ON p.project_manager_id = pm.id
            LEFT JOIN companies c ON p.company_id = c.id
            WHERE p.id::text = %(val)s OR p.project_code = %(val)s
            LIMIT 1;
        """
        with self.get_connection() as conn, conn.cursor() as cur:
            cur.execute(sql_proj, {"val": project_id_or_code})
            project = cur.fetchone()
            if not project:
                return None

            project_id = project["id"]

            # 2. WBS Hierarchy
            sql_wbs = """
                    SELECT * FROM erp_project_wbs
                    WHERE project_id = %s
                    ORDER BY sort_order ASC, wbs_code ASC;
                """
            cur.execute(sql_wbs, (project_id,))
            wbs_rows = cur.fetchall()

            # 3. Milestones
            sql_ms = """
                    SELECT * FROM project_milestones
                    WHERE project_id = %s
                    ORDER BY sort_order ASC, target_date ASC NULLS LAST;
                """
            cur.execute(sql_ms, (project_id,))
            milestones = cur.fetchall()

            # 4. Timeline Events
            sql_tl = """
                    SELECT * FROM project_timeline_events
                    WHERE project_id = %s
                    ORDER BY event_date DESC, created_at DESC;
                """
            cur.execute(sql_tl, (project_id,))
            timeline_events = cur.fetchall()

            # 5. Risks
            sql_risk = """
                    SELECT * FROM project_risks
                    WHERE project_id = %s
                    ORDER BY identified_date DESC;
                """
            cur.execute(sql_risk, (project_id,))
            risks = cur.fetchall()

        # Build hierarchical WBS tree
        wbs_tree = self._build_wbs_tree(wbs_rows)

        return {
            **project,
            "wbs_items": wbs_rows,
            "wbs_tree": wbs_tree,
            "milestones": milestones,
            "timeline_events": timeline_events,
            "risks": risks,
        }

    def get_project_bim_elements(self, project_id: str) -> list[dict[str, Any]]:
        """Lấy danh sách cấu kiện 3D BIM của dự án gắn liền với mã WBS."""
        sql_bim = """
            SELECT 
                b.*,
                w.task_name AS wbs_task_name,
                w.wbs_code
            FROM erp_project_bim_elements b
            LEFT JOIN erp_project_wbs w ON b.wbs_id = w.id
            WHERE b.project_id = %s
            ORDER BY b.installation_day ASC, b.element_guid ASC;
        """
        with self.get_connection() as conn, conn.cursor() as cur:
            cur.execute(sql_bim, (project_id,))
            return cur.fetchall()