from __future__ import annotations

import hashlib
import os
import re
from decimal import Decimal
from typing import Any

import openpyxl


class SxdPl2ParserMixin:
    """Parser for SXD Phụ lục 2 manufacturer & brand materials (Steel, Cement, Concrete, Asphalt)."""

    def _parse_pl2_excel(
        self,
        excel_path: str,
        period: str = "2026-08",
        doc_ref: str = "Thông báo số 558/TB-SXD ngày 07/08/2026 của Sở Xây dựng Hải Phòng",
    ) -> list[dict[str, Any]]:
        """Parse all 22 sheets of Phụ lục 2 Excel file and extract thousands of exact prices."""
        items: list[dict[str, Any]] = []
        if not os.path.exists(excel_path):
            return items

        wb = openpyxl.load_workbook(excel_path, data_only=True)

        sheet_group_map = {
            "1. Thép": "STEEL",
            "2. XM": "CEMENT",
            "3.Cấu kiện BT": "PRECAST_PILES",
            "3. 1 Cau kien duc san": "PRECAST_PILES",
            "4.Nhựa đường": "ASPHALT_ROAD",
            "5. KC thep": "STEEL_STRUCTURE",
            "6.1.Sơn": "PAINT_COATING",
            "6.2VL điện": "ELECTRICAL_LIGHTING",
            "6.3 VT Tien phong": "PIPES_M_E",
            "6.3 VT nước ": "PIPES_M_E",
            "6.3 VT Thuan phat": "PIPES_M_E",
            "6.3 Dong ho nuoc": "PIPES_M_E",
            "6.4 Cua NK": "DOORS_WINDOWS",
            "6.5.Gạch ốp lát": "TILES_FINISHING",
            "7.1.VL khac": "GEOTEXTILE_WATERPROOF",
            "7.2 Tam nhua": "CEILING_PARTITION",
            "7.3 Tam 3D": "CEILING_PARTITION",
            "7.4.Tam thach cao": "CEILING_PARTITION",
            "7.5 Dat san lap": "EARTHWORK_FILL",
            "7.6 Gach": "BRICK_EARTH",
            "7.7 Cac bon": "ASPHALT_ROAD",
        }

        for sname in wb.sheetnames:
            if sname == "ML":
                continue
            ws = wb[sname]
            group = sheet_group_map.get(sname, "OTHER_MATERIALS")

            header_row = 1
            name_col, unit_col, spec_col, mfg_col, price_col = 3, 4, 5, 7, 8

            for r in range(1, min(10, ws.max_row + 1)):
                row_vals = [
                    str(ws.cell(row=r, column=c).value or "").strip().lower()
                    for c in range(1, ws.max_column + 1)
                ]
                if any("tên" in v or "vật liệu" in v for v in row_vals) and any(
                    "giá" in v or "đơn giá" in v for v in row_vals
                ):
                    header_row = r
                    for c_idx, val in enumerate(row_vals, start=1):
                        if "tên" in val or "vật liệu" in val:
                            name_col = c_idx
                        elif "đvt" in val or "đơn vị" in val:
                            unit_col = c_idx
                        elif (
                            "tiêu chuẩn" in val
                            or "quy cách" in val
                            or "kỹ thuật" in val
                        ):
                            spec_col = c_idx
                        elif "nhà sản xuất" in val or "hãng" in val or "đơn vị" in val:
                            mfg_col = c_idx
                        elif "giá" in val or "đơn giá" in val:
                            if price_col == 8:
                                price_col = c_idx
                    break

            current_mfg = ""
            for r in range(header_row + 1, ws.max_row + 1):
                name_val = str(ws.cell(row=r, column=name_col).value or "").strip()
                if not name_val or name_val == "None":
                    continue

                if (
                    name_val.upper().startswith("NHÓM")
                    or name_val.upper().startswith("PHẦN")
                    or name_val.startswith("I.")
                    or name_val.startswith("II.")
                ):
                    continue

                unit_val = str(ws.cell(row=r, column=unit_col).value or "Cái").strip()
                if unit_val == "None":
                    unit_val = "Cái"

                spec_val = str(ws.cell(row=r, column=spec_col).value or "").strip()
                if spec_val == "None":
                    spec_val = ""

                mfg_val = str(ws.cell(row=r, column=mfg_col).value or "").strip()
                if mfg_val and mfg_val != "None" and len(mfg_val) > 5:
                    current_mfg = mfg_val

                raw_price = ws.cell(row=r, column=price_col).value
                if not raw_price:
                    for c_try in [price_col + 1, price_col - 1, 8, 7, 9, 10]:
                        if c_try <= ws.max_column:
                            val_try = ws.cell(row=r, column=c_try).value
                            if isinstance(val_try, (int, float)) and val_try > 0:
                                raw_price = val_try
                                break

                try:
                    if isinstance(raw_price, (int, float)) and raw_price > 0:
                        price_dec = Decimal(str(raw_price))
                    elif raw_price:
                        cleaned = re.sub(
                            r"[^\d.]", "", str(raw_price).replace(",", ".")
                        )
                        price_dec = (
                            Decimal(cleaned)
                            if cleaned and float(cleaned) > 0
                            else Decimal("0.00")
                        )
                    else:
                        price_dec = Decimal("0.00")
                except Exception:
                    price_dec = Decimal("0.00")

                if price_dec <= 0:
                    continue

                code_hash = (
                    hashlib.md5(f"{sname}_{name_val}_{spec_val}_{unit_val}".encode())
                    .hexdigest()[:8]
                    .upper()
                )
                mat_code = f"TBG-558-{group[:4]}-{code_hash}"

                notes = f"Phụ lục 2 - Sheet '{sname}'"
                if current_mfg:
                    notes += f" | NSX: {current_mfg[:60]}"

                for reg in ["HAI_PHONG_KHU_VUC_2", "HAI_PHONG_KHU_VUC_1"]:
                    items.append(
                        {
                            "publish_period": period,
                            "region_code": reg,
                            "material_group": group,
                            "material_code": mat_code,
                            "material_name": name_val,
                            "specifications": spec_val,
                            "unit": unit_val,
                            "state_unit_price_vnd": price_dec,
                            "document_reference": doc_ref,
                            "notes": notes,
                        }
                    )

        return items