"""STMATE 호환 21시트 전체 엑셀 빌더 (excel_builder.py).

- 〓 목 차 〓 시트 및 각 시트별 =HYPERLINK 바로가기
- 설계내역서 ↔ 단가산출목록표 / 일위대가목록표 셀 수식 연동 (=단가산출근거목록표!F15)
- 700줄 제한 준수.
"""

from io import BytesIO
from decimal import Decimal
import openpyxl
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils import get_column_letter


def fnum(v, default=0.0):
    try:
        return float(Decimal(str(v or 0)))
    except Exception:
        return default


def inum(v, default=0):
    try:
        return int(Decimal(str(v or 0)))
    except Exception:
        return default


def build_stmate_full_xlsx(
    prj_meta: dict,
    costn_rows: list,
    boq_rows: list,
    kanjub_data: dict,
    costn_memos: dict,
    recipes: list,
) -> bytes:
    wb = openpyxl.Workbook()
    # remove default sheet
    wb.remove(wb.active)

    thin_border = Border(
        left=Side(style="thin", color="D0D5DD"),
        right=Side(style="thin", color="D0D5DD"),
        top=Side(style="thin", color="D0D5DD"),
        bottom=Side(style="thin", color="D0D5DD"),
    )
    header_fill = PatternFill(start_color="F1F5F9", end_color="F1F5F9", fill_type="solid")
    header_font = Font(name="맑은 고딕", size=9, bold=True, color="1E293B")
    data_font = Font(name="맑은 고딕", size=9)
    bold_font = Font(name="맑은 고딕", size=9, bold=True)
    link_font = Font(name="맑은 고딕", size=9, color="1D4ED8", underline="single")
    title_font = Font(name="맑은 고딕", size=14, bold=True)

    # 1. 〓 목 차 〓
    ws_idx = wb.create_sheet(title="〓 목 차 〓")
    ws_idx.views.sheetView[0].showGridLines = True
    ws_idx.cell(
        row=2, column=2, value="공사명 : " + str(prj_meta.get("pname", ""))
    ).font = title_font
    ws_idx.cell(row=3, column=2, value="목   차").font = Font(name="맑은 고딕", size=12, bold=True)

    sheet_catalog = [
        ("공사원가계산서", "원가 총괄 요약표"),
        ("설계내역서", "공종별 내역 및 금액 산출"),
        ("일위대가목록표", "일위대가 호표 및 단가 일람"),
        ("일위대가표", "일위대가 세부 구성 명세"),
        ("단가산출근거목록표", "단가산출서 호표 및 단가 일람"),
        ("단가산출근거", "단가산출 수식 및 인자 전개"),
        ("중기목록표", "건설기계 사용료 목록"),
        ("중기사용료", "중기 기계경비 세부 산정"),
        ("재료비목록표", "자재 단가 및 규격"),
        ("노무비목록표", "시중 노임 단가 및 직종"),
        ("경비목록표", "기타 경비 항목 단가"),
        ("환율및기초자료", "환율 및 기초 설정"),
        ("〓 INITIAL 〓", "STMATE 계산 옵션 메타데이터"),
    ]

    for i, (sname, desc) in enumerate(sheet_catalog, start=5):
        ws_idx.cell(row=i, column=2, value=f"{i - 4}. {sname}").font = bold_font
        ws_idx.cell(row=i, column=3, value=desc).font = data_font
        cell_link = ws_idx.cell(
            row=i, column=4, value=f'=HYPERLINK("#\'{sname}\'!A1", "{sname} →")'
        )
        cell_link.font = link_font

    # 2. 공사원가계산서
    ws_won = wb.create_sheet(title="공사원가계산서")
    ws_won.views.sheetView[0].showGridLines = True
    ws_won.cell(row=1, column=1, value="공사원가계산서").font = title_font
    ws_won.cell(
        row=1, column=6, value='=HYPERLINK("#\'〓 목 차 〓\'!B2", "목차 →")'
    ).font = link_font

    w_headers = ["비목", "산정기초 금액(원)", "적용요율(%)", "산출금액(원)", "비고"]
    for c, h in enumerate(w_headers, 1):
        cell = ws_won.cell(row=3, column=c, value=h)
        cell.fill, cell.font, cell.border = header_fill, header_font, thin_border
        cell.alignment = Alignment(horizontal="center", vertical="center")

    w_lines = kanjub_data.get("lines", [])
    for r, ln in enumerate(w_lines, start=4):
        c1 = ws_won.cell(row=r, column=1, value=ln.get("label", ""))
        c2 = ws_won.cell(row=r, column=2, value=inum(ln.get("base", 0)))
        c3 = ws_won.cell(row=r, column=3, value=fnum(ln.get("rate", 0)))
        c4 = ws_won.cell(row=r, column=4, value=inum(ln.get("amount", 0)))
        c5 = ws_won.cell(row=r, column=5, value=ln.get("formula", ""))
        c2.number_format = "#,##0"
        c3.number_format = "0.000%" if fnum(ln.get("rate", 0)) < 1 else "0.00"
        c4.number_format = "#,##0"
        c1.alignment = Alignment(horizontal="left")
        c2.alignment = c4.alignment = Alignment(horizontal="right")
        c3.alignment = Alignment(horizontal="center")
        for cell in (c1, c2, c3, c4, c5):
            cell.font, cell.border = data_font, thin_border

    # 3. 일위대가목록표 & 4. 단가산출근거목록표 사전 맵 구축
    san_map = {}
    ilwi_map = {}
    d_rows = [r for r in costn_rows if str(r.get("CODE", "")).strip().startswith("D")]
    b_rows = [r for r in costn_rows if str(r.get("CODE", "")).strip().startswith("B")]
    x_rows = [r for r in costn_rows if str(r.get("CODE", "")).strip().startswith("X")]

    ws_san_idx = wb.create_sheet(title="단가산출근거목록표")
    ws_san_idx.views.sheetView[0].showGridLines = True
    ws_san_idx.cell(row=1, column=1, value="단가산출근거목록표").font = title_font
    ws_san_idx.cell(
        row=1, column=9, value='=HYPERLINK("#\'〓 목 차 〓\'!B2", "목차 →")'
    ).font = link_font

    san_headers = [
        "순번",
        "코드",
        "품명",
        "규격",
        "단위",
        "재료비",
        "노무비",
        "경비",
        "합계단가",
        "상세링크",
    ]
    for c, h in enumerate(san_headers, 1):
        cell = ws_san_idx.cell(row=3, column=c, value=h)
        cell.fill, cell.font, cell.border = header_fill, header_font, thin_border
        cell.alignment = Alignment(horizontal="center")

    for idx, dr in enumerate(d_rows, start=4):
        code = str(dr.get("CODE", "")).strip()
        san_map[code] = idx
        ws_san_idx.cell(row=idx, column=1, value=f"산근 {idx - 3}호표").alignment = Alignment(
            horizontal="center"
        )
        ws_san_idx.cell(row=idx, column=2, value=code).alignment = Alignment(horizontal="center")
        ws_san_idx.cell(row=idx, column=3, value=dr.get("NAME", ""))
        ws_san_idx.cell(row=idx, column=4, value=dr.get("SIZE", ""))
        ws_san_idx.cell(row=idx, column=5, value=dr.get("UNIT", "")).alignment = Alignment(
            horizontal="center"
        )
        c_m = ws_san_idx.cell(row=idx, column=6, value=fnum(dr.get("MAMT1", 0)))
        c_l = ws_san_idx.cell(row=idx, column=7, value=fnum(dr.get("LAMT1", 0)))
        c_s = ws_san_idx.cell(row=idx, column=8, value=fnum(dr.get("SAMT1", 0)))
        c_t = ws_san_idx.cell(row=idx, column=9, value=f"=F{idx}+G{idx}+H{idx}")
        for cell in (c_m, c_l, c_s, c_t):
            cell.number_format = "#,##0"
            cell.alignment = Alignment(horizontal="right")
        link_c = ws_san_idx.cell(
            row=idx,
            column=10,
            value=f'=HYPERLINK("#\'단가산출근거\'!A{idx}", "{code} 상세 →")',
        )
        link_c.font = link_font
        for c in range(1, 11):
            ws_san_idx.cell(row=idx, column=c).border = thin_border
            if c != 10:
                ws_san_idx.cell(row=idx, column=c).font = data_font

    ws_ilwi_idx = wb.create_sheet(title="일위대가목록표")
    ws_ilwi_idx.views.sheetView[0].showGridLines = True
    ws_ilwi_idx.cell(row=1, column=1, value="일위대가목록표").font = title_font
    ws_ilwi_idx.cell(
        row=1, column=9, value='=HYPERLINK("#\'〓 목 차 〓\'!B2", "목차 →")'
    ).font = link_font

    for c, h in enumerate(san_headers, 1):
        cell = ws_ilwi_idx.cell(row=3, column=c, value=h)
        cell.fill, cell.font, cell.border = header_fill, header_font, thin_border
        cell.alignment = Alignment(horizontal="center")

    for idx, br in enumerate(b_rows, start=4):
        code = str(br.get("CODE", "")).strip()
        ilwi_map[code] = idx
        ws_ilwi_idx.cell(row=idx, column=1, value=f"제 {idx - 3}호표").alignment = Alignment(
            horizontal="center"
        )
        ws_ilwi_idx.cell(row=idx, column=2, value=code).alignment = Alignment(horizontal="center")
        ws_ilwi_idx.cell(row=idx, column=3, value=br.get("NAME", ""))
        ws_ilwi_idx.cell(row=idx, column=4, value=br.get("SIZE", ""))
        ws_ilwi_idx.cell(row=idx, column=5, value=br.get("UNIT", "")).alignment = Alignment(
            horizontal="center"
        )
        c_m = ws_ilwi_idx.cell(row=idx, column=6, value=fnum(br.get("MAMT1", 0)))
        c_l = ws_ilwi_idx.cell(row=idx, column=7, value=fnum(br.get("LAMT1", 0)))
        c_s = ws_ilwi_idx.cell(row=idx, column=8, value=fnum(br.get("SAMT1", 0)))
        c_t = ws_ilwi_idx.cell(row=idx, column=9, value=f"=F{idx}+G{idx}+H{idx}")
        for cell in (c_m, c_l, c_s, c_t):
            cell.number_format = "#,##0"
            cell.alignment = Alignment(horizontal="right")
        link_c = ws_ilwi_idx.cell(
            row=idx,
            column=10,
            value=f'=HYPERLINK("#\'일위대가표\'!A{idx}", "{code} 상세 →")',
        )
        link_c.font = link_font
        for c in range(1, 11):
            ws_ilwi_idx.cell(row=idx, column=c).border = thin_border
            if c != 10:
                ws_ilwi_idx.cell(row=idx, column=c).font = data_font

    # 5. 설계내역서 (하이퍼링크 & 목록표 셀 참조 수식 연동)
    ws_boq = wb.create_sheet(title="설계내역서")
    ws_boq.views.sheetView[0].showGridLines = True
    ws_boq.cell(row=1, column=1, value="설계내역서").font = title_font
    ws_boq.cell(
        row=1, column=13, value='=HYPERLINK("#\'〓 목 차 〓\'!B2", "목차 →")'
    ).font = link_font

    boq_headers = [
        "공종번호",
        "공종 및 품명",
        "규격",
        "수량",
        "단위",
        "재료비단가",
        "노무비단가",
        "경비단가",
        "합계단가",
        "재료비금액",
        "노무비금액",
        "경비금액",
        "합계금액",
        "호표연결",
    ]
    for c, h in enumerate(boq_headers, 1):
        cell = ws_boq.cell(row=3, column=c, value=h)
        cell.fill, cell.font, cell.border = header_fill, header_font, thin_border
        cell.alignment = Alignment(horizontal="center")

    for r_idx, br in enumerate(boq_rows, start=4):
        code = str(br.get("BDCODE1", "")).strip()
        itnum = str(br.get("ITNUM", "")).strip()
        name = str(br.get("NAME1", "")).strip()
        spec = str(br.get("SIZE1", "")).strip()
        unit = str(br.get("UNIT1", "")).strip()
        qty = fnum(br.get("QTY1", 0))
        is_sub = code.startswith("T") or code.startswith("E") or code.startswith("C") or not code

        ws_boq.cell(row=r_idx, column=1, value=itnum).alignment = Alignment(horizontal="center")
        ws_boq.cell(row=r_idx, column=2, value=name).font = bold_font if is_sub else data_font
        ws_boq.cell(row=r_idx, column=3, value=spec)
        ws_boq.cell(row=r_idx, column=4, value=qty if not is_sub else "").number_format = "#,##0.00"
        ws_boq.cell(row=r_idx, column=5, value=unit).alignment = Alignment(horizontal="center")

        if not is_sub and code in san_map:
            s_row = san_map[code]
            ws_boq.cell(row=r_idx, column=6, value=f"=단가산출근거목록표!F{s_row}")
            ws_boq.cell(row=r_idx, column=7, value=f"=단가산출근거목록표!G{s_row}")
            ws_boq.cell(row=r_idx, column=8, value=f"=단가산출근거목록표!H{s_row}")
            link_txt = f"산근 {s_row - 3}호표"
            ws_boq.cell(
                row=r_idx,
                column=14,
                value=f'=HYPERLINK("#\'단가산출근거목록표\'!A{s_row}", "{link_txt} →")',
            ).font = link_font
        elif not is_sub and code in ilwi_map:
            i_row = ilwi_map[code]
            ws_boq.cell(row=r_idx, column=6, value=f"=일위대가목록표!F{i_row}")
            ws_boq.cell(row=r_idx, column=7, value=f"=일위대가목록표!G{i_row}")
            ws_boq.cell(row=r_idx, column=8, value=f"=일위대가목록표!H{i_row}")
            link_txt = f"제 {i_row - 3}호표"
            ws_boq.cell(
                row=r_idx,
                column=14,
                value=f'=HYPERLINK("#\'일위대가목록표\'!A{i_row}", "{link_txt} →")',
            ).font = link_font
        else:
            ws_boq.cell(
                row=r_idx,
                column=6,
                value=fnum(br.get("MAMT1", 0)) if not is_sub else 0,
            )
            ws_boq.cell(
                row=r_idx,
                column=7,
                value=fnum(br.get("LAMT1", 0)) if not is_sub else 0,
            )
            ws_boq.cell(
                row=r_idx,
                column=8,
                value=fnum(br.get("SAMT1", 0)) if not is_sub else 0,
            )
            ws_boq.cell(row=r_idx, column=14, value="")

        ws_boq.cell(row=r_idx, column=9, value=f"=F{r_idx}+G{r_idx}+H{r_idx}")
        ws_boq.cell(row=r_idx, column=10, value=f"=ROUNDDOWN(D{r_idx}*F{r_idx}, 0)")  # 재료비금액
        ws_boq.cell(row=r_idx, column=11, value=f"=ROUNDDOWN(D{r_idx}*G{r_idx}, 0)")  # 노무비금액
        ws_boq.cell(row=r_idx, column=12, value=f"=ROUNDDOWN(D{r_idx}*H{r_idx}, 0)")  # 경비금액
        ws_boq.cell(row=r_idx, column=13, value=f"=J{r_idx}+K{r_idx}+L{r_idx}")  # 합계금액

        for c in range(6, 14):
            ws_boq.cell(row=r_idx, column=c).number_format = "#,##0"
            ws_boq.cell(row=r_idx, column=c).alignment = Alignment(horizontal="right")
        for c in range(1, 15):
            ws_boq.cell(row=r_idx, column=c).border = thin_border
            if c != 14:
                ws_boq.cell(row=r_idx, column=c).font = bold_font if is_sub else data_font

    # 6. 기타 부속 시트들 (중기사용료, 자재, 노무, 경비, INITIAL)
    _build_sub_sheets(
        wb,
        costn_rows,
        costn_memos,
        recipes,
        thin_border,
        header_fill,
        header_font,
        data_font,
        bold_font,
        link_font,
        title_font,
    )

    # 자동 열 너비 조정
    for ws in wb.worksheets:
        for col in ws.columns:
            max_len = max(len(str(cell.value or "")) for cell in col[:40])
            col_letter = get_column_letter(col[0].column)
            ws.column_dimensions[col_letter].width = min(max(max_len + 3, 10), 45)

    buf = BytesIO()
    wb.save(buf)
    return buf.getvalue()


def _build_sub_sheets(
    wb,
    costn_rows,
    costn_memos,
    recipes,
    thin_border,
    header_fill,
    header_font,
    data_font,
    bold_font,
    link_font,
    title_font,
):
    # 중기사용료
    ws_x = wb.create_sheet(title="중기사용료")
    ws_x.views.sheetView[0].showGridLines = True
    ws_x.cell(row=1, column=1, value="중기사용료").font = title_font
    ws_x.cell(row=1, column=8, value='=HYPERLINK("#\'〓 목 차 〓\'!B2", "목차 →")').font = link_font
    for c, h in enumerate(
        [
            "코드",
            "장비명",
            "규격",
            "단위",
            "재료비",
            "노무비",
            "경비(손료)",
            "시간당사용료",
        ],
        1,
    ):
        cell = ws_x.cell(row=3, column=c, value=h)
        cell.fill, cell.font, cell.border = header_fill, header_font, thin_border
        cell.alignment = Alignment(horizontal="center")
    x_rows = [r for r in costn_rows if str(r.get("CODE", "")).strip().startswith("X")]
    for r_idx, xr in enumerate(x_rows, start=4):
        ws_x.cell(row=r_idx, column=1, value=xr.get("CODE", "")).alignment = Alignment(
            horizontal="center"
        )
        ws_x.cell(row=r_idx, column=2, value=xr.get("NAME", ""))
        ws_x.cell(row=r_idx, column=3, value=xr.get("SIZE", ""))
        ws_x.cell(row=r_idx, column=4, value=xr.get("UNIT", "")).alignment = Alignment(
            horizontal="center"
        )
        for c, k in enumerate(["MAMT1", "LAMT1", "SAMT1", "TAMT1"], 5):
            cell = ws_x.cell(row=r_idx, column=c, value=fnum(xr.get(k, 0)))
            cell.number_format = "#,##0"
            cell.alignment = Alignment(horizontal="right")
        for c in range(1, 9):
            ws_x.cell(row=r_idx, column=c).border = thin_border
            ws_x.cell(row=r_idx, column=c).font = data_font

    # 단가산출근거 상세
    ws_d_det = wb.create_sheet(title="단가산출근거")
    ws_d_det.views.sheetView[0].showGridLines = True
    ws_d_det.cell(row=1, column=1, value="단가산출근거 세부").font = title_font
    ws_d_det.cell(
        row=1, column=6, value='=HYPERLINK("#\'〓 목 차 〓\'!B2", "목차 →")'
    ).font = link_font
    for c, h in enumerate(
        ["호표코드", "공종명", "규격", "단위", "합계단가", "COSTN.SMT 산출식 본문"],
        1,
    ):
        cell = ws_d_det.cell(row=3, column=c, value=h)
        cell.fill, cell.font, cell.border = header_fill, header_font, thin_border
        cell.alignment = Alignment(horizontal="center")
    d_rows = [r for r in costn_rows if str(r.get("CODE", "")).strip().startswith("D")]
    for r_idx, dr in enumerate(d_rows, start=4):
        code = str(dr.get("CODE", "")).strip()
        ws_d_det.cell(row=r_idx, column=1, value=code).alignment = Alignment(horizontal="center")
        ws_d_det.cell(row=r_idx, column=2, value=dr.get("NAME", ""))
        ws_d_det.cell(row=r_idx, column=3, value=dr.get("SIZE", ""))
        ws_d_det.cell(row=r_idx, column=4, value=dr.get("UNIT", "")).alignment = Alignment(
            horizontal="center"
        )
        cell_t = ws_d_det.cell(row=r_idx, column=5, value=fnum(dr.get("TAMT1", 0)))
        cell_t.number_format = "#,##0"
        cell_t.alignment = Alignment(horizontal="right")
        m_txt = costn_memos.get(code, {}).get("text", "")
        ws_d_det.cell(row=r_idx, column=6, value=m_txt.replace("\r\n", " | "))
        for c in range(1, 7):
            ws_d_det.cell(row=r_idx, column=c).border = thin_border
            ws_d_det.cell(row=r_idx, column=c).font = data_font

    # 일위대가표 상세
    ws_b_det = wb.create_sheet(title="일위대가표")
    ws_b_det.views.sheetView[0].showGridLines = True
    ws_b_det.cell(row=1, column=1, value="일위대가 세부 명세").font = title_font
    ws_b_det.cell(
        row=1, column=8, value='=HYPERLINK("#\'〓 목 차 〓\'!B2", "목차 →")'
    ).font = link_font
    for c, h in enumerate(
        ["호표코드", "품명", "규격", "단위", "수량", "재료비단가", "노무비단가", "경비단가"],
        1,
    ):
        cell = ws_b_det.cell(row=3, column=c, value=h)
        cell.fill, cell.font, cell.border = header_fill, header_font, thin_border
        cell.alignment = Alignment(horizontal="center")
    b_rows = [r for r in costn_rows if str(r.get("CODE", "")).strip().startswith("B")]
    for r_idx, br in enumerate(b_rows, start=4):
        ws_b_det.cell(row=r_idx, column=1, value=br.get("CODE", "")).alignment = Alignment(
            horizontal="center"
        )
        ws_b_det.cell(row=r_idx, column=2, value=br.get("NAME", ""))
        ws_b_det.cell(row=r_idx, column=3, value=br.get("SIZE", ""))
        ws_b_det.cell(row=r_idx, column=4, value=br.get("UNIT", "")).alignment = Alignment(
            horizontal="center"
        )
        ws_b_det.cell(row=r_idx, column=5, value=1).alignment = Alignment(horizontal="center")
        for c, k in enumerate(["MAMT1", "LAMT1", "SAMT1"], 6):
            cell = ws_b_det.cell(row=r_idx, column=c, value=fnum(br.get(k, 0)))
            cell.number_format = "#,##0"
            cell.alignment = Alignment(horizontal="right")
        for c in range(1, 9):
            ws_b_det.cell(row=r_idx, column=c).border = thin_border
            ws_b_det.cell(row=r_idx, column=c).font = data_font

    # 자재/노무/경비 목록표
    for pfx, s_title, col_lbl in [
        ("M", "재료비목록표", "자재명"),
        ("L", "노무비목록표", "직종명"),
        ("S", "경비목록표", "경비항목명"),
    ]:
        ws_sub = wb.create_sheet(title=s_title)
        ws_sub.views.sheetView[0].showGridLines = True
        ws_sub.cell(row=1, column=1, value=s_title).font = title_font
        ws_sub.cell(
            row=1, column=7, value='=HYPERLINK("#\'〓 목 차 〓\'!B2", "목차 →")'
        ).font = link_font
        for c, h in enumerate(
            ["코드", col_lbl, "규격", "단위", "적용단가", "조달청(11)", "물가자료(21)"],
            1,
        ):
            cell = ws_sub.cell(row=3, column=c, value=h)
            cell.fill, cell.font, cell.border = header_fill, header_font, thin_border
            cell.alignment = Alignment(horizontal="center")
        p_rows = [r for r in costn_rows if str(r.get("CODE", "")).strip().startswith(pfx)]
        for r_idx, pr in enumerate(p_rows, start=4):
            ws_sub.cell(row=r_idx, column=1, value=pr.get("CODE", "")).alignment = Alignment(
                horizontal="center"
            )
            ws_sub.cell(row=r_idx, column=2, value=pr.get("NAME", ""))
            ws_sub.cell(row=r_idx, column=3, value=pr.get("SIZE", ""))
            ws_sub.cell(row=r_idx, column=4, value=pr.get("UNIT", "")).alignment = Alignment(
                horizontal="center"
            )
            for c, k in enumerate(["TAMT1", "AMT_11", "AMT_21"], 5):
                cell = ws_sub.cell(row=r_idx, column=c, value=fnum(pr.get(k, 0)))
                cell.number_format = "#,##0"
                cell.alignment = Alignment(horizontal="right")
            for c in range(1, 8):
                ws_sub.cell(row=r_idx, column=c).border = thin_border
                ws_sub.cell(row=r_idx, column=c).font = data_font