from __future__ import annotations

import io
import logging
from decimal import Decimal
from typing import Any

logger = logging.getLogger("dscons.state_price_sync.import")


class StatePriceImportMixin:
    """Mixin for importing SXD published prices from Excel/CSV files."""

    def import_appendix_from_excel_or_csv(
        self,
        file_bytes: bytes,
        filename: str,
        publish_period: str = "2026-08",
        region_code: str = "HAI_PHONG_KHU_VUC_2",
        document_reference: Optional[str] = None,
    ) -> Dict[str, Any]:
        """Import and parse official Department of Construction Price Announcement Appendix (.xlsx, .csv)."""
        import hashlib

        import openpyxl

        doc_ref = (
            document_reference
            or f"Thông báo số 558/TB-SXD ngày 07/08/2026 của Sở Xây dựng Hải Phòng (Phụ lục tải lên từ {filename})"
        )
        items_to_upsert: List[Dict[str, Any]] = []

        if filename.lower().endswith(".xlsx") or filename.lower().endswith(".xls"):
            wb = openpyxl.load_workbook(io.BytesIO(file_bytes), data_only=True)
            ws = wb.active

            # Detect header row
            header_row_idx = 1
            col_map: Dict[str, int] = {}
            for r_idx in range(1, min(20, ws.max_row + 1)):
                row_vals = [
                    str(ws.cell(row=r_idx, 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 or "danh mục" in v or "hàng hóa" in v
                    for v in row_vals
                ) and any(
                    "giá" in v or "đơn giá" in v or "phụ lục" in v for v in row_vals
                ):
                    header_row_idx = r_idx
                    for c_idx, val in enumerate(row_vals, start=1):
                        if (
                            "tên" in val
                            or "vật liệu" in val
                            or "danh mục" in val
                            or "hàng hóa" in val
                        ):
                            col_map["name"] = c_idx
                        elif (
                            "quy cách" in val
                            or "tiêu chuẩn" in val
                            or "kỹ thuật" in val
                            or "spec" in val
                        ):
                            col_map["specs"] = c_idx
                        elif "đvt" in val or "đơn vị" in val:
                            col_map["unit"] = c_idx
                        elif "mã" in val:
                            col_map["code"] = c_idx
                        elif "nhóm" in val:
                            col_map["group"] = c_idx
                        elif "giá" in val or "đơn giá" in val or "phụ lục" in val:
                            if "price" not in col_map:
                                col_map["price"] = c_idx
                    break

            current_group = "AGGREGATE"
            for r_idx in range(header_row_idx + 1, ws.max_row + 1):
                name_col = col_map.get("name", 4)
                name_val = str(ws.cell(row=r_idx, column=name_col).value or "").strip()
                if not name_val or name_val == "None":
                    continue
                if name_val.upper().startswith("NHÓM") or "PHẦN" in name_val.upper():
                    current_group = (
                        name_val.replace("NHÓM VẬT TƯ:", "")
                        .replace("NHÓM:", "")
                        .strip()
                    )
                    continue

                specs_col = col_map.get("specs", 5)
                specs_val = str(
                    ws.cell(row=r_idx, column=specs_col).value or ""
                ).strip()
                unit_col = col_map.get("unit", 6)
                unit_val = str(
                    ws.cell(row=r_idx, column=unit_col).value or "m3"
                ).strip()
                price_col = col_map.get("price", 7)
                raw_price = ws.cell(row=r_idx, column=price_col).value

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

                code_col = col_map.get("code", 3)
                code_val = str(ws.cell(row=r_idx, column=code_col).value or "").strip()
                if not code_val or code_val == "None":
                    code_val = f"TBG-IMP-{hashlib.md5(name_val.encode('utf-8')).hexdigest()[:8].upper()}"

                group_col = col_map.get("group", 2)
                group_val = str(
                    ws.cell(row=r_idx, column=group_col).value or current_group
                ).strip()

                items_to_upsert.append(
                    {
                        "material_code": code_val,
                        "material_name": name_val,
                        "specifications": specs_val if specs_val != "None" else "",
                        "unit": unit_val if unit_val != "None" else "m3",
                        "material_group": group_val
                        if group_val != "None"
                        else "AGGREGATE",
                        "state_unit_price_vnd": price_dec,
                        "document_reference": doc_ref,
                        "publish_period": publish_period,
                        "region_code": region_code,
                        "notes": f"Imported từ Phụ lục {filename}",
                    }
                )

        upserted_count = 0
        with self.get_connection() as conn:
            with conn.cursor() as cur:
                for row in items_to_upsert:
                    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["specifications"],
                            row["unit"],
                            row["state_unit_price_vnd"],
                            row["document_reference"],
                            row["notes"],
                        ),
                    )
                    upserted_count += 1
                conn.commit()

        return {
            "status": "success",
            "message": f"Đã import thành công {upserted_count} danh mục từ Phụ lục Sở Xây dựng ({filename})!",
            "total_imported": upserted_count,
            "period": publish_period,
            "region_code": region_code,
            "sample_items": items_to_upsert[:5] if items_to_upsert else [],
        }