from __future__ import annotations

import os
from typing import Any

from app.core.postgres.erp_client import ErpDatabaseClient

from .sxd_pl1_parser import SxdPl1ParserMixin
from .sxd_pl2_parser import SxdPl2ParserMixin
from .sxd_rates import SxdRatesMixin


class OfficialSxdIngestionEngine(
    SxdPl1ParserMixin,
    SxdPl2ParserMixin,
    SxdRatesMixin,
):
    """Official Department of Construction (Sở Xây Dựng Hải Phòng) Ingestion Engine."""

    def __init__(self, price_ref_dir: str = "Price_Ref"):
        self.price_ref_dir = price_ref_dir
        self.pg_client = ErpDatabaseClient()

    def parse_all_price_ref_data(self) -> list[dict[str, Any]]:
        """Parse all official SXD documents in Price_Ref directory."""
        all_items: list[dict[str, Any]] = []

        # 1. Parse TB 558 (Tháng 7/2026 - Áp dụng từ 01/07/2026 và công bố tháng 08/2026)
        tb558_dir = os.path.join(self.price_ref_dir, "TB 558")
        if os.path.exists(tb558_dir):
            pl1_pdf = os.path.join(
                tb558_dir,
                "1.-phu-luc-1-t7-10-8-17h-in-trinh-ky.signed639221316246679423.pdf",
            )
            pl2_xlsx = os.path.join(
                tb558_dir, "2.-phu-luc-2-in-ky-639221316296269313.xlsx"
            )

            doc_ref_558 = (
                "Thông báo số 558/TB-SXD ngày 07/08/2026 của Sở Xây dựng Hải Phòng"
            )
            all_items.extend(
                self._parse_pl1_pdf(pl1_pdf, period="2026-08", doc_ref=doc_ref_558)
            )
            all_items.extend(
                self._parse_pl2_excel(pl2_xlsx, period="2026-08", doc_ref=doc_ref_558)
            )

        # 2. Parse TB 478 (Tháng 6/2026)
        tb478_dir = os.path.join(self.price_ref_dir, "TB 478")
        if os.path.exists(tb478_dir):
            pl1_478 = os.path.join(
                tb478_dir, "phu-luc-1-chuan.signed639192057863514961.pdf"
            )
            doc_ref_478 = (
                "Thông báo số 478/TB-SXD ngày 30/06/2026 của Sở Xây dựng Hải Phòng"
            )
            all_items.extend(
                self._parse_pl1_pdf(pl1_478, period="2026-06", doc_ref=doc_ref_478)
            )

        # 3. Dynamically discover any other TB directories (e.g. TB 622 or future periods)
        known_dirs = {"TB 558", "TB 478"}
        if os.path.exists(self.price_ref_dir):
            for entry in os.listdir(self.price_ref_dir):
                full_entry_path = os.path.join(self.price_ref_dir, entry)
                if not os.path.isdir(full_entry_path) or entry in known_dirs:
                    continue
                if not entry.upper().startswith("TB"):
                    continue

                tb_num = entry.replace("TB", "").strip()
                doc_ref = f"Thông báo số {tb_num}/TB-SXD của Sở Xây dựng Hải Phòng"
                period = "2026-08"

                # Locate files in directory
                pl1_candidates = []
                pl2_candidates = []
                for fname in os.listdir(full_entry_path):
                    fl = fname.lower()
                    fpath = os.path.join(full_entry_path, fname)
                    if fl.endswith((".xlsx", ".xls")):
                        pl2_candidates.append(fpath)
                    elif fl.endswith(".pdf") and ("phu-luc-1" in fl or "phu_luc_1" in fl or "pl1" in fl):
                        pl1_candidates.append(fpath)

                for pl1_path in pl1_candidates:
                    all_items.extend(
                        self._parse_pl1_pdf(pl1_path, period=period, doc_ref=doc_ref)
                    )
                for pl2_path in pl2_candidates:
                    all_items.extend(
                        self._parse_pl2_excel(pl2_path, period=period, doc_ref=doc_ref)
                    )

        # 4. Add Labor Rates from QĐ 65/QĐ-SXD
        all_items.extend(self._get_official_labor_rates(period="2026-08"))
        all_items.extend(self._get_official_labor_rates(period="2026-06"))

        # 5. Add Equipment Shift Rates
        all_items.extend(self._get_official_equipment_shifts(period="2026-08"))
        all_items.extend(self._get_official_equipment_shifts(period="2026-06"))

        return all_items

    def ingest_to_postgres(self) -> int:
        """Execute full ingestion into PostgreSQL erp_state_published_prices."""
        all_items = self.parse_all_price_ref_data()
        if not all_items:
            return 0

        upserted_count = 0
        with self.pg_client.get_connection() as conn:
            with conn.cursor() as cur:
                for row in all_items:
                    cur.execute(
                        """
                        INSERT INTO erp_state_published_prices (
                            id, publish_period, region_code, material_group, material_code,
                            material_name, specifications, unit, state_unit_price_vnd,
                            document_reference, effective_date, notes, created_at, updated_at
                        ) VALUES (
                            gen_random_uuid(), %s, %s, %s, %s,
                            %s, %s, %s, %s,
                            %s, NOW(), %s, NOW(), NOW()
                        )
                        ON CONFLICT (publish_period, region_code, material_code)
                        DO UPDATE SET
                            material_name = EXCLUDED.material_name,
                            specifications = EXCLUDED.specifications,
                            unit = EXCLUDED.unit,
                            state_unit_price_vnd = EXCLUDED.state_unit_price_vnd,
                            document_reference = EXCLUDED.document_reference,
                            notes = EXCLUDED.notes,
                            updated_at = NOW();
                    """,
                        (
                            row["publish_period"],
                            row["region_code"],
                            row["material_group"],
                            row["material_code"],
                            row["material_name"],
                            row.get("specifications", ""),
                            row["unit"],
                            row["state_unit_price_vnd"],
                            row["document_reference"],
                            row.get("notes", ""),
                        ),
                    )
                    upserted_count += 1
                conn.commit()

        return upserted_count