Turn price and quantity rows into an interactive pricing iterFact.

import csv
import html
import json
import zipfile
import xml.etree.ElementTree as ET

MAIN_NS = "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
REL_NS = "http://schemas.openxmlformats.org/officeDocument/2006/relationships"
PACKAGE_REL_NS = "http://schemas.openxmlformats.org/package/2006/relationships"
CONTENT_TYPES_NS = "http://schemas.openxmlformats.org/package/2006/content-types"


def normalize_name(value):
    return "".join(ch for ch in str(value or "").strip().lower() if ch.isalnum())


def parse_number(value):
    if value is None:
        return None
    if isinstance(value, (int, float)):
        return float(value)
    text = str(value).strip()
    if not text:
        return None
    text = text.replace("$", "").replace(",", "")
    try:
        return float(text)
    except ValueError:
        return None


def rounded(value, places=2):
    return round(float(value), places)


def money(value):
    return "$" + "{:,.2f}".format(float(value))


def number(value):
    return "{:,.2f}".format(float(value))


def column_index(cell_ref):
    letters = "".join(ch for ch in str(cell_ref or "") if ch.isalpha()).upper()
    index = 0
    for letter in letters:
        index = index * 26 + (ord(letter) - ord("A") + 1)
    return max(index - 1, 0)


def read_xlsx_table(path):
    with zipfile.ZipFile(path) as workbook:
        names = set(workbook.namelist())
        shared_strings = []
        if "xl/sharedStrings.xml" in names:
            root = ET.fromstring(workbook.read("xl/sharedStrings.xml"))
            for item in root.findall(f".//{{{MAIN_NS}}}si"):
                text_parts = [
                    node.text or ""
                    for node in item.findall(f".//{{{MAIN_NS}}}t")
                ]
                shared_strings.append("".join(text_parts))

        sheet_path = "xl/worksheets/sheet1.xml"
        if sheet_path not in names:
            sheet_path = next(
                name for name in workbook.namelist()
                if name.startswith("xl/worksheets/sheet") and name.endswith(".xml")
            )
        root = ET.fromstring(workbook.read(sheet_path))

        rows = []
        for row in root.findall(f".//{{{MAIN_NS}}}row"):
            values = []
            for cell in row.findall(f"{{{MAIN_NS}}}c"):
                index = column_index(cell.attrib.get("r"))
                while len(values) <= index:
                    values.append("")
                value = ""
                cell_type = cell.attrib.get("t")
                if cell_type == "inlineStr":
                    inline = cell.find(f".//{{{MAIN_NS}}}t")
                    value = inline.text if inline is not None else ""
                else:
                    raw = cell.find(f"{{{MAIN_NS}}}v")
                    value = raw.text if raw is not None else ""
                    if cell_type == "s" and value != "":
                        value = shared_strings[int(value)]
                values[index] = value
            if any(str(value).strip() for value in values):
                rows.append(values)

    if not rows:
        return []

    headers = [str(value).strip() for value in rows[0]]
    records = []
    for row in rows[1:]:
        record = {}
        for index, header in enumerate(headers):
            if header:
                record[header] = row[index] if index < len(row) else ""
        if any(str(value).strip() for value in record.values()):
            records.append(record)
    return records


def read_csv_table(path):
    with open(path, "r", encoding="utf-8-sig", newline="") as handle:
        return list(csv.DictReader(handle))


def read_source_table(source):
    path = source["path"]
    if path.lower().endswith(".xlsx"):
        return read_xlsx_table(path)
    return read_csv_table(path)


def find_column(headers, candidates):
    normalized_lookup = {normalize_name(header): header for header in headers}
    for candidate in candidates:
        found = normalized_lookup.get(normalize_name(candidate))
        if found:
            return found
    return None


def fit_linear_regression(points):
    prices = [price for price, _qty in points]
    qtys = [qty for _price, qty in points]
    mean_price = sum(prices) / len(prices)
    mean_qty = sum(qtys) / len(qtys)
    denominator = sum((price - mean_price) ** 2 for price in prices)
    if denominator == 0:
        raise ValueError("The regression needs at least two different prices.")

    slope = sum(
        (price - mean_price) * (qty - mean_qty)
        for price, qty in points
    ) / denominator
    intercept = mean_qty - slope * mean_price

    predictions = [intercept + slope * price for price in prices]
    sse = sum((actual - predicted) ** 2 for actual, predicted in zip(qtys, predictions))
    sst = sum((actual - mean_qty) ** 2 for actual in qtys)
    r_squared = 1 if sst == 0 else 1 - (sse / sst)
    return slope, intercept, r_squared


def excel_col_name(index):
    index += 1
    letters = ""
    while index:
        index, remainder = divmod(index - 1, 26)
        letters = chr(65 + remainder) + letters
    return letters


def cell_xml(row_index, col_index, value):
    ref = f"{excel_col_name(col_index)}{row_index}"
    formula = None
    style = None
    cached_value = value
    if isinstance(value, dict):
        formula = value.get("formula")
        cached_value = value.get("value")
        style = value.get("style")

    style_attr = f' s="{int(style)}"' if style is not None else ""
    if formula:
        if isinstance(cached_value, (int, float)):
            return f'<c r="{ref}"{style_attr}><f>{html.escape(str(formula))}</f><v>{cached_value}</v></c>'
        return f'<c r="{ref}"{style_attr}><f>{html.escape(str(formula))}</f></c>'

    if isinstance(cached_value, (int, float)):
        return f'<c r="{ref}"{style_attr}><v>{cached_value}</v></c>'

    escaped = html.escape("" if cached_value is None else str(cached_value))
    return f'<c r="{ref}" t="inlineStr"{style_attr}><is><t xml:space="preserve">{escaped}</t></is></c>'


def sheet_xml(rows, widths=None, freeze_top_row=True):
    cols_xml = ""
    if widths:
        cols = []
        for index, width in enumerate(widths, start=1):
            cols.append(f'<col min="{index}" max="{index}" width="{width}" customWidth="1"/>')
        cols_xml = f'<cols>{"".join(cols)}</cols>'

    views_xml = ""
    if freeze_top_row:
        views_xml = (
            '<sheetViews><sheetView workbookViewId="0">'
            '<pane ySplit="1" topLeftCell="A2" activePane="bottomLeft" state="frozen"/>'
            '<selection pane="bottomLeft"/>'
            '</sheetView></sheetViews>'
        )

    row_parts = []
    for row_number, row in enumerate(rows, start=1):
        height_attr = ' ht="22" customHeight="1"' if row_number == 1 else ""
        cells = "".join(cell_xml(row_number, col_index, value) for col_index, value in enumerate(row))
        row_parts.append(f'<row r="{row_number}"{height_attr}>{cells}</row>')
    return (
        '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
        f'<worksheet xmlns="{MAIN_NS}">{views_xml}{cols_xml}<sheetData>{"".join(row_parts)}</sheetData></worksheet>'
    )


def styles_xml():
    return (
        '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
        f'<styleSheet xmlns="{MAIN_NS}">'
        '<numFmts count="4">'
        '<numFmt numFmtId="164" formatCode="$#,##0.00"/>'
        '<numFmt numFmtId="165" formatCode="#,##0.00"/>'
        '<numFmt numFmtId="166" formatCode="#,##0"/>'
        '<numFmt numFmtId="167" formatCode="0.0%"/>'
        '</numFmts>'
        '<fonts count="3">'
        '<font><sz val="11"/><name val="Calibri"/></font>'
        '<font><b/><sz val="11"/><color rgb="FFFFFFFF"/><name val="Calibri"/></font>'
        '<font><b/><sz val="11"/><color rgb="FF141311"/><name val="Calibri"/></font>'
        '</fonts>'
        '<fills count="5">'
        '<fill><patternFill patternType="none"/></fill>'
        '<fill><patternFill patternType="gray125"/></fill>'
        '<fill><patternFill patternType="solid"><fgColor rgb="FF1A7A4E"/><bgColor indexed="64"/></patternFill></fill>'
        '<fill><patternFill patternType="solid"><fgColor rgb="FFEAF4EF"/><bgColor indexed="64"/></patternFill></fill>'
        '<fill><patternFill patternType="solid"><fgColor rgb="FFFFF4DC"/><bgColor indexed="64"/></patternFill></fill>'
        '</fills>'
        '<borders count="2">'
        '<border><left/><right/><top/><bottom/><diagonal/></border>'
        '<border><left style="thin"><color rgb="FFD9DED8"/></left><right style="thin"><color rgb="FFD9DED8"/></right><top style="thin"><color rgb="FFD9DED8"/></top><bottom style="thin"><color rgb="FFD9DED8"/></bottom><diagonal/></border>'
        '</borders>'
        '<cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>'
        '<cellXfs count="9">'
        '<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>'
        '<xf numFmtId="0" fontId="1" fillId="2" borderId="1" xfId="0" applyFill="1" applyFont="1" applyBorder="1"><alignment horizontal="center"/></xf>'
        '<xf numFmtId="0" fontId="2" fillId="3" borderId="1" xfId="0" applyFill="1" applyFont="1" applyBorder="1"/>'
        '<xf numFmtId="164" fontId="0" fillId="0" borderId="1" xfId="0" applyNumberFormat="1" applyBorder="1"/>'
        '<xf numFmtId="165" fontId="0" fillId="0" borderId="1" xfId="0" applyNumberFormat="1" applyBorder="1"/>'
        '<xf numFmtId="166" fontId="0" fillId="0" borderId="1" xfId="0" applyNumberFormat="1" applyBorder="1"/>'
        '<xf numFmtId="0" fontId="2" fillId="0" borderId="1" xfId="0" applyFont="1" applyBorder="1"/>'
        '<xf numFmtId="167" fontId="0" fillId="0" borderId="1" xfId="0" applyNumberFormat="1" applyBorder="1"/>'
        '<xf numFmtId="0" fontId="2" fillId="4" borderId="1" xfId="0" applyFill="1" applyFont="1" applyBorder="1"/>'
        '</cellXfs>'
        '<cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles>'
        '</styleSheet>'
    )


def normalize_sheet(sheet):
    if isinstance(sheet, dict):
        return sheet
    name, rows = sheet
    return {"name": name, "rows": rows, "widths": None, "freeze_top_row": True}


def write_xlsx(path, sheets):
    normalized_sheets = [normalize_sheet(sheet) for sheet in sheets]
    content_types = [
        '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>',
        f'<Types xmlns="{CONTENT_TYPES_NS}">',
        '<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>',
        '<Default Extension="xml" ContentType="application/xml"/>',
        '<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>',
        '<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>',
    ]
    for index, _sheet in enumerate(normalized_sheets, start=1):
        content_types.append(
            f'<Override PartName="/xl/worksheets/sheet{index}.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>'
        )
    content_types.append("</Types>")

    workbook_sheets = "".join(
        f'<sheet name="{html.escape(sheet["name"])}" sheetId="{index}" r:id="rId{index}"/>'
        for index, sheet in enumerate(normalized_sheets, start=1)
    )
    workbook_xml = (
        '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
        f'<workbook xmlns="{MAIN_NS}" xmlns:r="{REL_NS}"><sheets>{workbook_sheets}</sheets><calcPr calcId="0" fullCalcOnLoad="1"/></workbook>'
    )
    workbook_rels = [
        '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>',
        f'<Relationships xmlns="{PACKAGE_REL_NS}">',
    ]
    for index, _sheet in enumerate(normalized_sheets, start=1):
        workbook_rels.append(
            f'<Relationship Id="rId{index}" Type="{REL_NS}/worksheet" Target="worksheets/sheet{index}.xml"/>'
        )
    workbook_rels.append(
        f'<Relationship Id="rId{len(normalized_sheets) + 1}" Type="{REL_NS}/styles" Target="styles.xml"/>'
    )
    workbook_rels.append("</Relationships>")

    with zipfile.ZipFile(path, "w", zipfile.ZIP_DEFLATED) as archive:
        archive.writestr("[Content_Types].xml", "".join(content_types))
        archive.writestr(
            "_rels/.rels",
            '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            f'<Relationships xmlns="{PACKAGE_REL_NS}">'
            f'<Relationship Id="rId1" Type="{REL_NS}/officeDocument" Target="xl/workbook.xml"/>'
            "</Relationships>",
        )
        archive.writestr("xl/workbook.xml", workbook_xml)
        archive.writestr("xl/_rels/workbook.xml.rels", "".join(workbook_rels))
        archive.writestr("xl/styles.xml", styles_xml())
        for index, sheet in enumerate(normalized_sheets, start=1):
            archive.writestr(
                f"xl/worksheets/sheet{index}.xml",
                sheet_xml(
                    sheet["rows"],
                    widths=sheet.get("widths"),
                    freeze_top_row=sheet.get("freeze_top_row", True),
                ),
            )


def styled(value, style):
    return {"value": value, "style": style}


def formula(expression, value, style):
    return {"formula": expression, "value": value, "style": style}


def build_interactive_html(model):
    payload_json = json.dumps(model, separators=(",", ":"))
    template = """<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>Price Quantity Pricing Model</title>
  <style>
    :root {
      color-scheme: light;
      --ink: #141311;
      --muted: #5f625d;
      --line: rgba(20, 19, 17, 0.1);
      --paper: #fffdf8;
      --soft: #f5f3ed;
      --green: #1a7a4e;
      --blue: #244f7a;
      --amber: #fff4dc;
    }
    * { box-sizing: border-box; }
    body {
      margin: 0;
      background: #f7f5ef;
      color: var(--ink);
      font-family: Inter, ui-sans-serif, system-ui, -apple-system, BlinkMacSystemFont, "Segoe UI", sans-serif;
    }
    .shell {
      width: min(1120px, 100%);
      margin: 0 auto;
      padding: 24px;
    }
    .hero, .card {
      border: 1px solid var(--line);
      border-radius: 8px;
      background: var(--paper);
    }
    .hero {
      display: grid;
      grid-template-columns: minmax(0, 1.25fr) minmax(280px, .75fr);
      gap: 18px;
      padding: 26px;
    }
    .kicker {
      color: var(--green);
      font-size: 12px;
      font-weight: 850;
      letter-spacing: .1em;
      text-transform: uppercase;
    }
    h1 {
      margin: 10px 0 12px;
      font-size: clamp(32px, 7vw, 58px);
      line-height: .98;
      letter-spacing: 0;
    }
    p {
      margin: 0;
      color: var(--muted);
      line-height: 1.5;
    }
    .metrics {
      display: grid;
      grid-template-columns: repeat(2, minmax(0, 1fr));
      gap: 10px;
    }
    .metric {
      display: grid;
      gap: 6px;
      padding: 12px;
      border: 1px solid var(--line);
      border-radius: 8px;
      background: var(--soft);
    }
    .metric span {
      color: var(--muted);
      font-size: 11px;
      font-weight: 800;
      letter-spacing: .06em;
      text-transform: uppercase;
    }
    .metric strong {
      font-size: 19px;
      line-height: 1.1;
    }
    .grid {
      display: grid;
      grid-template-columns: minmax(0, .8fr) minmax(0, 1.2fr);
      gap: 18px;
      margin-top: 18px;
      align-items: start;
    }
    .card {
      display: grid;
      gap: 14px;
      padding: 18px;
    }
    h2 {
      margin: 0;
      font-size: 21px;
      line-height: 1.15;
      letter-spacing: 0;
    }
    label {
      display: grid;
      gap: 10px;
      color: var(--muted);
      font-size: 13px;
      font-weight: 800;
      letter-spacing: .05em;
      text-transform: uppercase;
    }
    input[type="range"] { width: 100%; accent-color: var(--green); }
    input[type="number"] {
      width: 100%;
      min-height: 44px;
      padding: 0 12px;
      border: 1px solid var(--line);
      border-radius: 8px;
      background: white;
      color: var(--ink);
      font-size: 18px;
      font-weight: 850;
    }
    .scenario {
      display: grid;
      grid-template-columns: repeat(3, minmax(0, 1fr));
      gap: 10px;
    }
    .scenario .metric strong { font-size: 22px; }
    .note {
      padding: 12px;
      border: 1px solid rgba(199, 144, 43, .22);
      border-radius: 8px;
      background: var(--amber);
      color: #5e4010;
      font-size: 13px;
      font-weight: 700;
      line-height: 1.45;
    }
    svg {
      width: 100%;
      min-height: 320px;
      border: 1px solid var(--line);
      border-radius: 8px;
      background: linear-gradient(180deg, #ffffff, #f8faf7);
    }
    table {
      width: 100%;
      border-collapse: collapse;
      overflow: hidden;
      border: 1px solid var(--line);
      border-radius: 8px;
      font-size: 13px;
    }
    th, td {
      padding: 10px 12px;
      border-bottom: 1px solid var(--line);
      text-align: right;
    }
    th:first-child, td:first-child { text-align: left; }
    th {
      background: rgba(26, 122, 78, .1);
      color: #165d3a;
      font-size: 11px;
      letter-spacing: .05em;
      text-transform: uppercase;
    }
    tr:last-child td { border-bottom: 0; }
    .recommended td {
      background: rgba(26, 122, 78, .08);
      font-weight: 850;
    }
    @media (max-width: 820px) {
      .shell { padding: 14px; }
      .hero, .grid { grid-template-columns: 1fr; }
      .scenario, .metrics { grid-template-columns: 1fr; }
    }
  </style>
</head>
<body>
  <main class="shell">
    <section class="hero">
      <div>
        <div class="kicker">interactive iterFact</div>
        <h1>Price and quantity model</h1>
        <p>Move the price and watch the regression estimate quantity and revenue. The Excel export keeps the same slope and intercept as formulas.</p>
      </div>
      <div class="metrics">
        <div class="metric"><span>Slope</span><strong id="slope"></strong></div>
        <div class="metric"><span>R squared</span><strong id="r2"></strong></div>
        <div class="metric"><span>Recommended price</span><strong id="recommended"></strong></div>
        <div class="metric"><span>Rows used</span><strong id="rows"></strong></div>
      </div>
    </section>

    <section class="grid">
      <article class="card">
        <div>
          <div class="kicker">scenario control</div>
          <h2>Test a price</h2>
        </div>
        <label>
          Price
          <input id="priceInput" type="number" min="0.01" step="0.01">
          <input id="priceRange" type="range" min="0.01" step="0.01">
        </label>
        <div class="scenario">
          <div class="metric"><span>Predicted qty</span><strong id="scenarioQty"></strong></div>
          <div class="metric"><span>Expected revenue</span><strong id="scenarioRevenue"></strong></div>
          <div class="metric"><span>Revenue delta</span><strong id="scenarioDelta"></strong></div>
        </div>
        <div class="note">This is a proof model. With only two unique observed prices, the line is useful for demonstrating the workflow, not for final pricing science.</div>
      </article>

      <article class="card">
        <div>
          <div class="kicker">demand curve</div>
          <h2>Observed data and fitted line</h2>
        </div>
        <svg id="chart" viewBox="0 0 720 360" role="img" aria-label="Observed points and fitted demand line"></svg>
      </article>
    </section>

    <section class="card" style="margin-top:18px">
      <div>
        <div class="kicker">pricing table</div>
        <h2>Scenario model</h2>
      </div>
      <table>
        <thead><tr><th>Price</th><th>Predicted Qty</th><th>Expected Revenue</th><th>Note</th></tr></thead>
        <tbody id="scenarioRows"></tbody>
      </table>
    </section>
  </main>
  <script>
    const model = __MODEL_JSON__;
    const slope = model.slope;
    const intercept = model.intercept;
    const recommendedRevenue = model.expected_revenue;
    const formatNumber = new Intl.NumberFormat("en-US", { maximumFractionDigits: 2 });
    const formatMoney = new Intl.NumberFormat("en-US", { style: "currency", currency: "USD", maximumFractionDigits: 2 });
    function predictedQty(price) {
      return Math.max(0, intercept + slope * price);
    }
    function revenue(price) {
      return price * predictedQty(price);
    }
    function setText(id, value) {
      const node = document.getElementById(id);
      if (node) node.textContent = value;
    }
    function updateScenario(price) {
      const cleanPrice = Math.max(0.01, Number(price) || 0.01);
      const qty = predictedQty(cleanPrice);
      const rev = revenue(cleanPrice);
      const delta = rev - recommendedRevenue;
      document.getElementById("priceInput").value = cleanPrice.toFixed(2);
      document.getElementById("priceRange").value = cleanPrice.toFixed(2);
      setText("scenarioQty", formatNumber.format(qty));
      setText("scenarioRevenue", formatMoney.format(rev));
      setText("scenarioDelta", (delta >= 0 ? "+" : "") + formatMoney.format(delta));
      drawChart(cleanPrice);
    }
    function drawChart(activePrice) {
      const svg = document.getElementById("chart");
      const allPrices = model.model_rows.map((row) => row.price).concat(model.points.map((point) => point.price));
      const allQtys = model.model_rows.map((row) => row.predicted_qty).concat(model.points.map((point) => point.qty));
      const minPrice = Math.min.apply(Math, allPrices);
      const maxPrice = Math.max.apply(Math, allPrices);
      const maxQty = Math.max.apply(Math, allQtys) * 1.08;
      const pad = { left: 58, top: 28, right: 22, bottom: 48 };
      const width = 720 - pad.left - pad.right;
      const height = 360 - pad.top - pad.bottom;
      function x(price) { return pad.left + ((price - minPrice) / Math.max(maxPrice - minPrice, 0.01)) * width; }
      function y(qty) { return pad.top + height - (qty / Math.max(maxQty, 1)) * height; }
      const linePoints = model.model_rows.map((row) => x(row.price).toFixed(1) + "," + y(row.predicted_qty).toFixed(1)).join(" ");
      const pointDots = model.points.map((point) => '<circle cx="' + x(point.price).toFixed(1) + '" cy="' + y(point.qty).toFixed(1) + '" r="4.5" fill="#244f7a" opacity=".72"><title>Price ' + point.price + ", Qty " + point.qty + '</title></circle>').join("");
      const activeQty = predictedQty(activePrice);
      svg.innerHTML =
        '<line x1="' + pad.left + '" y1="' + (pad.top + height) + '" x2="' + (pad.left + width) + '" y2="' + (pad.top + height) + '" stroke="#cfd6cf"/>' +
        '<line x1="' + pad.left + '" y1="' + pad.top + '" x2="' + pad.left + '" y2="' + (pad.top + height) + '" stroke="#cfd6cf"/>' +
        '<polyline points="' + linePoints + '" fill="none" stroke="#1a7a4e" stroke-width="4" stroke-linecap="round" stroke-linejoin="round"/>' +
        pointDots +
        '<line x1="' + x(activePrice).toFixed(1) + '" y1="' + y(activeQty).toFixed(1) + '" x2="' + x(activePrice).toFixed(1) + '" y2="' + (pad.top + height) + '" stroke="#9f6a10" stroke-dasharray="5 5"/>' +
        '<circle cx="' + x(activePrice).toFixed(1) + '" cy="' + y(activeQty).toFixed(1) + '" r="8" fill="#9f6a10"/>' +
        '<text x="' + pad.left + '" y="342" fill="#5f625d" font-size="12">Price</text>' +
        '<text x="8" y="' + pad.top + '" fill="#5f625d" font-size="12">Qty</text>';
    }
    function renderTable() {
      const rows = document.getElementById("scenarioRows");
      rows.innerHTML = model.model_rows.map((row) => {
        const cls = row.note ? ' class="recommended"' : "";
        return '<tr' + cls + '><td>' + formatMoney.format(row.price) + '</td><td>' + formatNumber.format(row.predicted_qty) + '</td><td>' + formatMoney.format(row.expected_revenue) + '</td><td>' + (row.note || "") + '</td></tr>';
      }).join("");
    }
    document.getElementById("priceInput").addEventListener("input", (event) => updateScenario(event.target.value));
    document.getElementById("priceRange").addEventListener("input", (event) => updateScenario(event.target.value));
    document.getElementById("priceRange").min = model.slider_min.toFixed(2);
    document.getElementById("priceRange").max = model.slider_max.toFixed(2);
    setText("slope", formatNumber.format(model.slope));
    setText("r2", formatNumber.format(model.r_squared));
    setText("recommended", formatMoney.format(model.recommended_price));
    setText("rows", String(model.rows_used));
    renderTable();
    updateScenario(model.recommended_price);
  </script>
</body>
</html>"""
    return template.replace("__MODEL_JSON__", payload_json)


def transform(inputs, output_dir):
    source = inputs.get("price_qty") or next(iter(inputs.values()))
    rows = read_source_table(source)
    if not rows:
        raise ValueError("The uploaded file did not contain any data rows.")

    headers = list(rows[0].keys())
    price_col = find_column(headers, ["price", "unit price", "unit_price"])
    qty_col = find_column(headers, ["qty", "quantity", "units", "demand"])
    if not price_col or not qty_col:
        raise ValueError("Expected columns named Price and Qty.")

    points = []
    cleaned_rows = []
    for row in rows:
        price = parse_number(row.get(price_col))
        qty = parse_number(row.get(qty_col))
        if price is None or qty is None:
            continue
        points.append((price, qty))
        cleaned_rows.append({"Price": price, "Qty": qty})

    if len(points) < 2:
        raise ValueError("The regression needs at least two usable Price and Qty rows.")

    slope, intercept, r_squared = fit_linear_regression(points)
    observed_prices = sorted(set(price for price, _qty in points))
    min_price = min(observed_prices)
    max_price = max(observed_prices)
    price_span = max(max_price - min_price, 1)
    lower_price = max(0.01, min_price - price_span)
    upper_price = max_price + price_span

    if slope < 0:
        unconstrained_best_price = -intercept / (2 * slope)
        recommended_price = min(max(unconstrained_best_price, 0.01), upper_price)
    else:
        recommended_price = max_price

    scenario_prices = [
        lower_price + (upper_price - lower_price) * index / 12
        for index in range(13)
    ]
    scenario_prices.append(recommended_price)
    scenario_prices = sorted({round(price, 4) for price in scenario_prices})

    model_rows = []
    for price in scenario_prices:
        predicted_qty = max(0, intercept + slope * price)
        expected_revenue = price * predicted_qty
        note = "recommended" if abs(price - recommended_price) < 0.0001 else ""
        model_rows.append({
            "price": rounded(price, 2),
            "predicted_qty": rounded(predicted_qty, 2),
            "expected_revenue": rounded(expected_revenue, 2),
            "note": note,
        })

    expected_qty = max(0, intercept + slope * recommended_price)
    expected_revenue = recommended_price * expected_qty

    regression_rows = [
        [styled("Metric", 1), styled("Value", 1)],
        [styled("Slope: Qty change for each $1 price increase", 2), styled(rounded(slope, 4), 4)],
        [styled("Intercept", 2), styled(rounded(intercept, 4), 4)],
        [styled("R squared", 2), styled(rounded(r_squared, 4), 7)],
        [styled("Recommended price", 2), styled(rounded(recommended_price, 2), 3)],
        [styled("Expected qty at recommended price", 2), styled(rounded(expected_qty, 2), 4)],
        [styled("Expected revenue at recommended price", 2), styled(rounded(expected_revenue, 2), 3)],
        [styled("Formula basis", 8), styled("Predicted Qty = MAX(0, Intercept + Slope * Price)", 8)],
    ]

    pricing_sheet_rows = [
        [styled("Scenario Price", 1), styled("Predicted Qty", 1), styled("Expected Revenue", 1), styled("Notes", 1)]
    ]
    for excel_row, model_row in enumerate(model_rows, start=2):
        pricing_sheet_rows.append([
            styled(model_row["price"], 3),
            formula(f"MAX(0,Regression!$B$3+Regression!$B$2*A{excel_row})", model_row["predicted_qty"], 4),
            formula(f"A{excel_row}*B{excel_row}", model_row["expected_revenue"], 3),
            styled(model_row["note"], 8 if model_row["note"] else 0),
        ])

    source_sheet_rows = [[styled("Price", 1), styled("Qty", 1)]] + [
        [styled(rounded(row["Price"], 4), 3), styled(rounded(row["Qty"], 4), 5)]
        for row in cleaned_rows
    ]

    workbook_path = f"{output_dir}/price-quantity-pricing-model.xlsx"
    write_xlsx(
        workbook_path,
        [
            {
                "name": "Source_Data",
                "rows": source_sheet_rows,
                "widths": [16, 16],
                "freeze_top_row": True,
            },
            {
                "name": "Regression",
                "rows": regression_rows,
                "widths": [46, 22],
                "freeze_top_row": True,
            },
            {
                "name": "Pricing_Model",
                "rows": pricing_sheet_rows,
                "widths": [18, 18, 20, 18],
                "freeze_top_row": True,
            },
        ],
    )

    summary_path = f"{output_dir}/pricing-regression-summary.csv"
    with open(summary_path, "w", encoding="utf-8", newline="") as handle:
        writer = csv.writer(handle)
        writer.writerow(["metric", "value"])
        writer.writerow(["rows_used", len(points)])
        writer.writerow(["unique_prices", len(observed_prices)])
        writer.writerow(["slope", rounded(slope, 4)])
        writer.writerow(["intercept", rounded(intercept, 4)])
        writer.writerow(["r_squared", rounded(r_squared, 4)])
        writer.writerow(["recommended_price", rounded(recommended_price, 2)])
        writer.writerow(["expected_qty", rounded(expected_qty, 2)])
        writer.writerow(["expected_revenue", rounded(expected_revenue, 2)])

    html_model = {
        "rows_used": len(points),
        "unique_prices": len(observed_prices),
        "slope": rounded(slope, 4),
        "intercept": rounded(intercept, 4),
        "r_squared": rounded(r_squared, 4),
        "recommended_price": rounded(recommended_price, 2),
        "expected_qty": rounded(expected_qty, 2),
        "expected_revenue": rounded(expected_revenue, 2),
        "slider_min": rounded(lower_price, 2),
        "slider_max": rounded(upper_price, 2),
        "points": [
            {"price": rounded(price, 2), "qty": rounded(qty, 2)}
            for price, qty in points
        ],
        "model_rows": model_rows,
    }
    iterfact_path = f"{output_dir}/price-quantity-interactive-iterfact.html"
    with open(iterfact_path, "w", encoding="utf-8") as handle:
        handle.write(build_interactive_html(html_model))

    warnings = []
    if len(observed_prices) < 3:
        warnings.append("This proof uses only two unique price points, so treat the line as directional, not final pricing science.")

    return {
        "summary": {
            "rows_used": len(points),
            "unique_prices": len(observed_prices),
            "slope_qty_per_dollar": rounded(slope, 4),
            "intercept": rounded(intercept, 4),
            "r_squared": rounded(r_squared, 4),
            "recommended_price": rounded(recommended_price, 2),
            "expected_revenue": rounded(expected_revenue, 2),
        },
        "tables": {
            "regression": [
                {"metric": "Slope: Qty change for each $1 price increase", "value": rounded(slope, 4)},
                {"metric": "Intercept", "value": rounded(intercept, 4)},
                {"metric": "R squared", "value": rounded(r_squared, 4)},
                {"metric": "Recommended price", "value": money(recommended_price)},
                {"metric": "Expected qty at recommended price", "value": number(expected_qty)},
                {"metric": "Expected revenue at recommended price", "value": money(expected_revenue)},
            ],
            "pricing_model": [
                {
                    "scenario_price": money(row["price"]),
                    "predicted_qty": number(row["predicted_qty"]),
                    "expected_revenue": money(row["expected_revenue"]),
                    "notes": row["note"],
                }
                for row in model_rows
            ],
            "source_preview": [
                {"Price": money(row["Price"]), "Qty": number(row["Qty"])}
                for row in cleaned_rows[:20]
            ],
        },
        "files": {
            "interactive_iterfact": {
                "path": iterfact_path,
                "filename": "price-quantity-interactive-iterfact.html",
                "mimeType": "text/html",
            },
            "pricing_model_xlsx": {
                "path": workbook_path,
                "filename": "price-quantity-pricing-model.xlsx",
                "mimeType": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            },
            "summary_csv": {
                "path": summary_path,
                "filename": "pricing-regression-summary.csv",
                "mimeType": "text/csv",
            },
        },
        "warnings": warnings,
    }