from __future__ import annotations

import io
import logging

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


class StatePriceExportMixin:
    """Mixin for exporting state published prices to styled Excel spreadsheets."""

    def export_full_state_prices_to_excel(
        self,
        publish_period: str = "2026-08",
        region_code: str = "HAI_PHONG_KHU_VUC_2",
    ) -> bytes:
        """Xuất toàn bộ bảng giá công bố chính thức Sở Xây dựng Hải Phòng ra file Excel (.xlsx) chuẩn doanh nghiệp."""
        import openpyxl
        from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
        from openpyxl.utils import get_column_letter

        catalog_data = self.get_full_state_price_catalog(
            publish_period, region_code, limit=1000
        )
        items = catalog_data.get("items", [])

        wb = openpyxl.Workbook()
        ws = wb.active
        ws.title = f"Gia_SXD_{publish_period}"
        ws.views.sheetView[0].showGridLines = True

        # Styles
        font_company = Font(name="Arial", size=11, bold=True, color="1E3A8A")
        font_title = Font(name="Arial", size=13, bold=True, color="0F172A")
        font_sub = Font(name="Arial", size=10, italic=True, color="475569")
        font_header = Font(name="Arial", size=10, bold=True, color="FFFFFF")
        font_row = Font(name="Arial", size=10, color="0F172A")
        font_group_hdr = Font(name="Arial", size=10, bold=True, color="0284C7")

        fill_header = PatternFill(
            start_color="1E293B", end_color="1E293B", fill_type="solid"
        )
        fill_group = PatternFill(
            start_color="E0F2FE", end_color="E0F2FE", fill_type="solid"
        )
        fill_zebra = PatternFill(
            start_color="F8FAFC", end_color="F8FAFC", fill_type="solid"
        )

        thin_side = Side(style="thin", color="CBD5E1")
        cell_border = Border(
            left=thin_side, right=thin_side, top=thin_side, bottom=thin_side
        )

        # Header Block
        ws.merge_cells("A1:G1")
        ws["A1"] = "CÔNG TY TNHH XÂY DỰNG ĐỊNH SƠN - DSCons ENTERPRISE ERP"
        ws["A1"].font = font_company

        ws.merge_cells("A2:G2")
        ws["A2"] = (
            f"BẢNG ĐƠN GIÁ VẬT LIỆU XÂY DỰNG & CA MÁY CÔNG BỐ CHÍNH THỨC - KỲ {publish_period}"
        )
        ws["A2"].font = font_title

        ws.merge_cells("A3:G3")
        ws["A3"] = (
            f"Địa bàn: {catalog_data.get('region_name')} | Căn cứ: {catalog_data.get('document_title')}"
        )
        ws["A3"].font = font_sub

        headers = [
            "STT",
            "Nhóm Vật Tư",
            "Mã Hiệu",
            "Tên Vật Liệu / Ca Máy / Nhân Công",
            "Quy Cách Kỹ Thuật (Spec)",
            "ĐVT",
            "Đơn Giá Công Bố (VNĐ)",
        ]
        for col_idx, h_text in enumerate(headers, start=1):
            cell = ws.cell(row=5, column=col_idx, value=h_text)
            cell.font = font_header
            cell.fill = fill_header
            cell.border = cell_border
            cell.alignment = Alignment(
                horizontal="center", vertical="center", wrap_text=True
            )

        ws.row_dimensions[5].height = 28

        current_group = ""
        row_cursor = 6

        for idx, itm in enumerate(items, start=1):
            # Group separator
            if itm["material_group"] != current_group:
                current_group = itm["material_group"]
                ws.merge_cells(
                    start_row=row_cursor,
                    start_column=1,
                    end_row=row_cursor,
                    end_column=7,
                )
                g_cell = ws.cell(
                    row=row_cursor, column=1, value=f"NHÓM VẬT TƯ: {current_group}"
                )
                g_cell.font = font_group_hdr
                g_cell.fill = fill_group
                g_cell.border = cell_border
                g_cell.alignment = Alignment(
                    horizontal="left", vertical="center", indent=1
                )
                ws.row_dimensions[row_cursor].height = 22
                row_cursor += 1

            ws.row_dimensions[row_cursor].height = 20
            c1 = ws.cell(row=row_cursor, column=1, value=idx)
            c2 = ws.cell(row=row_cursor, column=2, value=itm["material_group"])
            c3 = ws.cell(row=row_cursor, column=3, value=itm["material_code"])
            c4 = ws.cell(row=row_cursor, column=4, value=itm["material_name"])
            c5 = ws.cell(row=row_cursor, column=5, value=itm["specifications"])
            c6 = ws.cell(row=row_cursor, column=6, value=itm["unit"])
            c7 = ws.cell(row=row_cursor, column=7, value=itm["state_unit_price_vnd"])

            for c in [c1, c2, c3, c4, c5, c6, c7]:
                c.font = font_row
                c.border = cell_border
                if row_cursor % 2 == 0:
                    c.fill = fill_zebra

            c1.alignment = Alignment(horizontal="center", vertical="center")
            c2.alignment = Alignment(horizontal="center", vertical="center")
            c3.alignment = Alignment(horizontal="center", vertical="center")
            c4.alignment = Alignment(horizontal="left", vertical="center")
            c5.alignment = Alignment(horizontal="left", vertical="center")
            c6.alignment = Alignment(horizontal="center", vertical="center")
            c7.alignment = Alignment(horizontal="right", vertical="center")
            c7.number_format = "#,##0"

            row_cursor += 1

        # Column widths
        widths = [6, 18, 22, 45, 50, 10, 22]
        for c_idx, w in enumerate(widths, start=1):
            ws.column_dimensions[get_column_letter(c_idx)].width = w

        buf = io.BytesIO()
        wb.save(buf)
        buf.seek(0)
        return buf.getvalue()