#!/usr/bin/env python3
"""Build standalone HTML + Excel for ACCI Systems Pricer (phone-friendly)."""
from __future__ import annotations

import json
import re
from pathlib import Path

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.datavalidation import DataValidation

ROOT = Path(__file__).resolve().parents[1]
LIB = json.loads((ROOT / "library" / "systems.json").read_text())


def build_html() -> Path:
    app_js = (ROOT / "app" / "app.js").read_text()
    boot_old = """async function boot() {
  try {
    const res = await fetch("../library/systems.json?v=" + Date.now(), { cache: "no-store" });
    if (!res.ok) throw new Error("Could not load systems.json — serve from systems-pricer folder");
    LIB = await res.json();
  } catch (e) {
    $("lib-updated").textContent = "Load failed — run: python3 scripts/serve.py";
    console.error(e);
    return;
  }"""
    boot_new = """async function boot() {
  try {
    if (typeof EMBEDDED_LIB !== "undefined" && EMBEDDED_LIB) {
      LIB = EMBEDDED_LIB;
    } else {
      const res = await fetch("../library/systems.json");
      if (!res.ok) throw new Error("Could not load systems.json");
      LIB = await res.json();
    }
  } catch (e) {
    $("lib-updated").textContent = "Load failed";
    console.error(e);
    return;
  }"""
    if boot_old not in app_js:
        raise SystemExit("app.js boot() block changed — update build_standalone.py")
    app_js = app_js.replace(boot_old, boot_new)
    # Point TDS links at the office-Mac library so clicks open the real PDFs
    tds_base = "file:///Users/acci/ACCI-Arkansas/knowledge/sales/systems-pricer"
    app_js = "const TDS_BASE = " + json.dumps(tds_base) + ";\n" + app_js

    css = (ROOT / "app" / "styles.css").read_text()
    html = (ROOT / "app" / "index.html").read_text()
    html = html.replace(
        '<link rel="stylesheet" href="styles.css" />',
        f"<style>\n{css}\n</style>",
    )
    html = html.replace(
        '<script src="app.js"></script>',
        f"<script>\nconst EMBEDDED_LIB = {json.dumps(LIB)};\n{app_js}\n</script>",
    )
    html = html.replace(
        '<p class="sub">ACCI epoxy / resinous sales pricer · systems · material · sell · TDS</p>',
        '<p class="sub">i-poxy · standalone · ACCI sales pricer</p>',
    )

    outs = [
        ROOT / "i-poxy.html",
        Path("/Users/acci/Desktop/Tyler/i-poxy.html"),
        Path("/Users/acci/ACCI-Arkansas/knowledge/output/sales/i-poxy.html"),
        ROOT / "ACCI_Systems_Pricer.html",
        Path("/Users/acci/ACCI-Arkansas/knowledge/output/sales/ACCI_Systems_Pricer.html"),
    ]
    for p in outs:
        p.parent.mkdir(parents=True, exist_ok=True)
        p.write_text(html)
        print(f"HTML {p} ({p.stat().st_size} bytes)")
    return outs[0]


def build_xlsx() -> Path:
    wb = Workbook()
    header_fill = PatternFill("solid", fgColor="1F4E79")
    header_font = Font(color="FFFFFF", bold=True, name="Calibri", size=11)
    title_font = Font(bold=True, name="Calibri", size=16, color="1F4E79")
    alt = PatternFill("solid", fgColor="E9F2FB")
    green = PatternFill("solid", fgColor="E2EFDA")
    input_fill = PatternFill("solid", fgColor="FFF2CC")

    def style_header(ws, row, cols):
        for c in range(1, cols + 1):
            cell = ws.cell(row, c)
            cell.fill = header_fill
            cell.font = header_font
            cell.alignment = Alignment(wrap_text=True, vertical="center")

    ws = wb.active
    ws.title = "Price Calculator"
    ws["A1"] = "i-poxy"
    ws["A1"].font = title_font
    ws["A2"] = "ACCI epoxy / resinous sales pricer. Material $ from Brad/Danny/Randall quotes. Sell rates planning."
    ws["A2"].font = Font(italic=True, color="666666")

    ws["A4"] = "System"
    ws["B4"] = LIB["systems"][0]["name"]
    ws["B4"].fill = input_fill
    ws["A5"] = "Square feet"
    ws["B5"] = 10000
    ws["B5"].fill = input_fill
    ws["A6"] = "Waste %"
    ws["B6"] = 10
    ws["B6"].fill = input_fill
    ws["A7"] = "Material $/sf override (blank = median)"
    ws["B7"] = None
    ws["B7"].fill = input_fill
    ws["A8"] = "Sell $/sf override (blank = default)"
    ws["B8"] = None
    ws["B8"].fill = input_fill
    ws["A9"] = "Cove LF"
    ws["B9"] = 0
    ws["B9"].fill = input_fill
    ws["A10"] = "Cove sell $/LF"
    ws["B10"] = 8
    ws["B10"].fill = input_fill

    ws["A12"] = "RESULTS"
    ws["A12"].font = Font(bold=True, size=13, color="1F4E79")
    # CONCAT with and operator written carefully for Excel formulas
    amp = chr(38)
    ws["A13"] = "Manufacturer"
    ws["B13"] = '=IFERROR(VLOOKUP(B4,Systems!B:L,3,FALSE),"")'
    ws["A14"] = "Family"
    ws["B14"] = '=IFERROR(VLOOKUP(B4,Systems!B:L,4,FALSE),"")'
    ws["A15"] = "Vendor rep"
    ws["B15"] = '=IFERROR(VLOOKUP(B4,Systems!B:L,5,FALSE),"")'
    ws["A16"] = "Mat low / median / high"
    ws["B16"] = (
        '=IFERROR(TEXT(VLOOKUP(B4,Systems!B:L,6,FALSE),"$0.00")'
        + amp
        + '" / "'
        + amp
        + 'TEXT(VLOOKUP(B4,Systems!B:L,7,FALSE),"$0.00")'
        + amp
        + '" / "'
        + amp
        + 'TEXT(VLOOKUP(B4,Systems!B:L,8,FALSE),"$0.00"),"")'
    )
    ws["A17"] = "Material $/sf used"
    ws["B17"] = '=IF(B7="",IFERROR(VLOOKUP(B4,Systems!B:L,7,FALSE),0),B7)'
    ws["A18"] = "Sell $/sf used"
    ws["B18"] = '=IF(B8="",IFERROR(VLOOKUP(B4,Systems!B:L,9,FALSE),0),B8)'
    ws["A19"] = "Est MATERIAL (sf x (1+waste) x mat)"
    ws["B19"] = "=B5*(1+B6/100)*B17"
    ws["A20"] = "Est SELL floor"
    ws["B20"] = "=B5*B18"
    ws["A21"] = "Est SELL cove"
    ws["B21"] = "=B9*B10"
    ws["A22"] = "Est SELL total"
    ws["B22"] = "=B20+B21"
    ws["A23"] = "Sell minus material (not full margin)"
    ws["B23"] = "=B22-B19"
    ws["A24"] = "Gross after material %"
    ws["B24"] = "=IF(B22=0,0,B23/B22)"

    for r in range(17, 24):
        ws[f"B{r}"].number_format = '"$"#,##0.00'
    ws["B24"].number_format = "0.0%"
    for r in (19, 22, 23):
        ws[f"A{r}"].font = Font(bold=True)
        ws[f"B{r}"].font = Font(bold=True, size=14)
        ws[f"B{r}"].fill = green

    ws["A26"] = "Summary"
    ws["B26"] = '=IFERROR(VLOOKUP(B4,Systems!B:L,10,FALSE),"")'
    ws["B26"].alignment = Alignment(wrap_text=True)
    ws.row_dimensions[26].height = 60
    ws["A27"] = "Good for"
    ws["B27"] = '=IFERROR(VLOOKUP(B4,Systems!B:L,11,FALSE),"")'
    ws["B27"].alignment = Alignment(wrap_text=True)
    ws["A28"] = "Proof / basis"
    ws["B28"] = '=IFERROR(VLOOKUP(B4,Systems!B:L,12,FALSE),"")'
    ws["B28"].alignment = Alignment(wrap_text=True)
    ws.row_dimensions[28].height = 48

    ws["A30"] = "INSTRUCTIONS"
    ws["A31"] = "1) Yellow cells are inputs — pick System from dropdown"
    ws["A32"] = "2) Material $ is MATERIAL ONLY from vendor quotes (not a bid)"
    ws["A33"] = "3) Does not include labor, travel, mob, tax, freight"
    ws["A34"] = "4) TDS on company Mac: knowledge/sales/systems-pricer/library/tds/"
    ws["A35"] = "5) Or open ACCI_Systems_Pricer.html in Safari/Chrome (works offline)"
    ws.column_dimensions["A"].width = 44
    ws.column_dimensions["B"].width = 72

    # Systems
    ws2 = wb.create_sheet("Systems")
    headers = [
        "ID",
        "Name",
        "Manufacturer",
        "Family",
        "Vendor rep",
        "Mat low",
        "Mat median",
        "Mat high",
        "Sell default",
        "Summary",
        "Use cases",
        "Proof / basis",
        "Typical stack",
        "TDS files",
        "Not ideal for",
    ]
    ws2.append(headers)
    style_header(ws2, 1, len(headers))
    for i, s in enumerate(LIB["systems"]):
        docs = "; ".join(d.get("title", "") for d in (s.get("docs") or []))
        proof = (s.get("material_per_sf") or {}).get("basis", "")
        if s.get("proof_jobs"):
            bits = []
            for p in s["proof_jobs"][:3]:
                bits.append(f"{p.get('job')} mat {p.get('mat_per_sf')}/sf")
            proof = (proof + " | " if proof else "") + "; ".join(bits)
        row = [
            s.get("id"),
            s.get("name"),
            s.get("manufacturer"),
            s.get("family"),
            s.get("vendor_rep"),
            (s.get("material_per_sf") or {}).get("low"),
            (s.get("material_per_sf") or {}).get("median"),
            (s.get("material_per_sf") or {}).get("high"),
            (s.get("sell_per_sf") or {}).get("default"),
            s.get("summary"),
            ", ".join(s.get("use_cases") or []),
            proof,
            " > ".join(s.get("typical_stack") or []),
            docs,
            ", ".join(s.get("not_ideal_for") or []),
        ]
        ws2.append(row)
        if i % 2:
            for c in range(1, len(headers) + 1):
                ws2.cell(i + 2, c).fill = alt
        for c in (6, 7, 8, 9):
            ws2.cell(i + 2, c).number_format = '"$"#,##0.00'

    for col in range(1, len(headers) + 1):
        ws2.column_dimensions[get_column_letter(col)].width = 28 if col < 10 else 40
    ws2.auto_filter.ref = f"A1:{get_column_letter(len(headers))}{len(LIB['systems']) + 1}"
    ws2.freeze_panes = "A2"

    names = [s["name"] for s in LIB["systems"]]
    # Excel list validation — keep under limit by using range
    # Write names to hidden area on Systems col Z
    for idx, name in enumerate(names, 2):
        ws2.cell(idx, 26, name)
    dv = DataValidation(type="list", formula1=f"Systems!$Z$2:$Z${len(names)+1}", allow_blank=False)
    ws.add_data_validation(dv)
    dv.add(ws["B4"])

    # Adders
    ws3 = wb.create_sheet("Polish and Adders")
    ws3["A1"] = "Tyler sell adders (2026-08)"
    ws3["A1"].font = title_font
    ws3.append([])
    ws3.append(["Adder", "Sell / unit", "Unit", "SF or qty", "Total sell"])
    style_header(ws3, 3, 5)
    r = 4
    for a in LIB["global_adders"]:
        ws3.cell(r, 1, a["name"])
        ws3.cell(r, 2, a["sell_per_sf"]).number_format = '"$"#,##0.00'
        ws3.cell(r, 3, a["unit"])
        ws3.cell(r, 4, 10000).fill = input_fill
        ws3.cell(r, 5, f"=B{r}*D{r}").number_format = '"$"#,##0.00'
        r += 1
    ws3.column_dimensions["A"].width = 40
    ws3.column_dimensions["B"].width = 14
    ws3.column_dimensions["D"].width = 12
    ws3.column_dimensions["E"].width = 14

    ws4 = wb.create_sheet("How to use")
    ws4["A1"] = "How to use this workbook"
    ws4["A1"].font = title_font
    lines = [
        "",
        "Phone/desktop friendly version of the ACCI Systems Pricer.",
        "",
        "PRICE A JOB",
        "1. Go to Price Calculator",
        "2. Choose a system in the yellow dropdown (B4)",
        "3. Enter SF, waste %, optional cove",
        "4. Read material vs sell totals (green rows)",
        "",
        "FIND A SYSTEM",
        "1. Go to Systems sheet",
        "2. Filter Family column (flake, ESD, quartz, sealed, troweled)",
        "3. Compare mat median and sell default",
        "",
        "SPEC / TAKEOFF",
        "Send Elaine the PDF or paste finish schedule language in Telegram.",
        "She will scan it and reply with ranked systems + dollars.",
        "",
        "TDS / SUBMITTALS",
        "On company Mac: knowledge/sales/systems-pricer/library/tds/",
        "Or ask Elaine to send the TDS pack for a system.",
        "",
        "STANDALONE WEB",
        "Open ACCI_Systems_Pricer.html in Safari or Chrome (works offline).",
        "",
        "IMPORTANT",
        "Material $ is not a full bid. Add labor, travel, mob, tax, freight.",
        "Federal / UFGS / fuel-resistive: confirm TDS + manufacturer before locking.",
    ]
    for i, line in enumerate(lines, 2):
        ws4[f"A{i}"] = line
        if line.isupper() and len(line) < 40:
            ws4[f"A{i}"].font = Font(bold=True, color="1F4E79")
    ws4.column_dimensions["A"].width = 90

    outs = [
        ROOT / "i-poxy.xlsx",
        Path("/Users/acci/Desktop/Tyler/i-poxy.xlsx"),
        Path("/Users/acci/ACCI-Arkansas/knowledge/output/sales/i-poxy.xlsx"),
        ROOT / "ACCI_Systems_Pricer.xlsx",
        Path("/Users/acci/ACCI-Arkansas/knowledge/output/sales/ACCI_Systems_Pricer.xlsx"),
    ]
    for p in outs:
        p.parent.mkdir(parents=True, exist_ok=True)
        wb.save(p)
        print(f"XLSX {p} ({p.stat().st_size} bytes)")
    return outs[0]


if __name__ == "__main__":
    build_html()
    build_xlsx()
    print("DONE")