#!/usr/bin/env python3
"""
Quote price history report.

IN:  A folder of saved quote files (.json). Each file is a list of quotes
     (format "ps-quote-v1"). Lines with no "our_part" are skipped.
OUT: One web page, quote-price-history.html, written in the folder you run
     the script from. It shows, for each of our part numbers and each supplier,
     the quotes oldest first, with the change from that supplier's last quote.
RUN: python3 quote_history.py quote-history
     (replace "quote-history" with the folder that holds the .json files)

v2: adds two columns after Unit price: lead time (days) and freight, read from
each quote's "terms" (lead_time_days, freight_cost). If a quote leaves one out
it shows "not stated". Nothing else is changed.

v3: adds a small summary table at the top of the page: each supplier's latest
price for each part, with the cheapest supplier in green. "Cheapest" is only
judged among suppliers whose latest quote uses the same unit and currency (no
unit conversion); if a part has no such pair, nothing is green. Nothing else
is changed.

v4: adds two buttons at the top right of the page: "Print" and "Download for
Excel". The second saves every price history row (one row per quote line) as a
CSV file. Nothing else is changed.

v5: each part gets its own tab. Click a part number (tab) under the summary table
to see only that part. The summary table stays above the tabs. Printing still
prints every part. Nothing else is changed.

Notes: needs only Python 3 (no installs, no internet). Numbers are read and
shown exactly as in the files (prices with at least 2 decimals, never rounded).
The only computed numbers are the change in price and the percent change
(percent is shown to 1 decimal). A change is only worked out when the unit and
currency are the same as that supplier's last quote for that part; otherwise
the page says "unit changed". If any file has bad data, nothing is written
and one message names the file, the field, and the value.
"""

import csv
import io
import json
import os
import sys
from datetime import date
from decimal import Decimal, ROUND_HALF_UP
from html import escape

OUTPUT_NAME = "quote-price-history.html"


class BadData(Exception):
    def __init__(self, filename, field, value, why):
        self.filename, self.field, self.value, self.why = filename, field, value, why
        super().__init__()

    def message(self):
        return (f"Bad data in file {self.filename}: field {self.field} "
                f"has value {self.value!r} ({self.why}). Nothing was written.")


def _no_constants(name):
    raise ValueError(f"{name} is not allowed")


def is_number(v):
    return isinstance(v, (int, Decimal)) and not isinstance(v, bool)


def need_text(fn, path, obj, key):
    v = obj.get(key)
    if not isinstance(v, str) or not v.strip():
        raise BadData(fn, f"{path}.{key}", v, "must be non-empty text")
    return v


def load_file(fn, path):
    """Return (number of quotes, list of price rows) from one file."""
    try:
        with open(path, "r", encoding="utf-8") as f:
            data = json.load(f, parse_float=Decimal, parse_constant=_no_constants)
    except (ValueError, UnicodeDecodeError) as e:
        raise BadData(fn, "(whole file)", "unreadable", f"not valid JSON: {e}")
    if not isinstance(data, list):
        raise BadData(fn, "(top level)", type(data).__name__, "must be a list of quotes")

    rows = []
    for qi, q in enumerate(data):
        qp = f"[{qi}]"
        if not isinstance(q, dict):
            raise BadData(fn, qp, q, "each quote must be an object")
        supplier = q.get("supplier")
        quote = q.get("quote")
        if not isinstance(supplier, dict):
            raise BadData(fn, f"{qp}.supplier", supplier, "must be an object")
        if not isinstance(quote, dict):
            raise BadData(fn, f"{qp}.quote", quote, "must be an object")
        sname = need_text(fn, f"{qp}.supplier", supplier, "name")
        rfq = need_text(fn, f"{qp}.quote", quote, "our_rfq")
        currency = need_text(fn, f"{qp}.quote", quote, "currency")
        qdate = need_text(fn, f"{qp}.quote", quote, "date")
        try:
            if len(qdate) != 10:
                raise ValueError
            date.fromisoformat(qdate)
        except ValueError:
            raise BadData(fn, f"{qp}.quote.date", qdate, "must be a real date written YYYY-MM-DD")
        lines = q.get("lines")
        if not isinstance(lines, list):
            raise BadData(fn, f"{qp}.lines", lines, "must be a list")
        for li, ln in enumerate(lines):
            lp = f"{qp}.lines[{li}]"
            if not isinstance(ln, dict):
                raise BadData(fn, lp, ln, "each line must be an object")
            part = ln.get("our_part")
            if part is None or (isinstance(part, str) and not part.strip()):
                continue  # no part number of ours: skip
            if not isinstance(part, str):
                raise BadData(fn, f"{lp}.our_part", part, "must be text")
            qty, price, uom = ln.get("qty"), ln.get("unit_price"), ln.get("uom")
            if not is_number(qty) or qty <= 0:
                raise BadData(fn, f"{lp}.qty", qty, "must be a number greater than 0")
            if not is_number(price) or price < 0:
                raise BadData(fn, f"{lp}.unit_price", price, "must be a number, 0 or more")
            if not isinstance(uom, str) or not uom.strip():
                raise BadData(fn, f"{lp}.uom", uom, "must be non-empty text")
            terms = q.get("terms")
            if terms is None:
                terms = {}
            if not isinstance(terms, dict):
                raise BadData(fn, f"{qp}.terms", terms, "must be an object")
            lead = terms.get("lead_time_days")
            if lead is not None and (not is_number(lead) or lead < 0):
                raise BadData(fn, f"{qp}.terms.lead_time_days", lead,
                              "must be a number, 0 or more (or left out)")
            freight = terms.get("freight_cost")
            if freight is not None and (not is_number(freight) or freight < 0):
                raise BadData(fn, f"{qp}.terms.freight_cost", freight,
                              "must be a number, 0 or more (or left out)")
            rows.append({
                "part": part.strip(), "supplier": sname, "rfq": rfq,
                "date": qdate, "qty": Decimal(qty), "uom": uom,
                "price": Decimal(price), "currency": currency,
                "file": fn, "order": (qi, li),
                "lead": None if lead is None else Decimal(lead),
                "freight": None if freight is None else Decimal(freight),
            })
    return len(data), rows


def money(d):
    """At least 2 decimals; more only if the file itself has more. Never rounds."""
    d = Decimal(d)
    if d.as_tuple().exponent >= -2:
        return format(d.quantize(Decimal("0.01")), "f")
    return format(d, "f")


def plain(d):
    return format(Decimal(d), "f")


def change_cell(prev, cur):
    if prev is None:
        return "first quote", "note"
    if prev["uom"] != cur["uom"]:
        return "unit changed", "warn"
    if prev["currency"] != cur["currency"]:
        return "currency changed", "warn"
    diff = cur["price"] - prev["price"]
    sign = "+" if diff > 0 else ("-" if diff < 0 else "")
    cls = "up" if diff > 0 else ("down" if diff < 0 else "flat")
    text = f"{sign}{money(abs(diff))}"
    if prev["price"] != 0:
        pct = (diff / prev["price"] * 100).quantize(Decimal("0.1"), rounding=ROUND_HALF_UP)
        psign = "+" if pct > 0 else ""
        text += f" ({psign}{pct}%)"
    return text, cls


def csv_script(csv_rows):
    """Page script for the two buttons. The CSV text is built here from the same
    rows shown on the page. Changes such as "+0.50 (+4.0%)" are wrapped so Excel
    shows them as text instead of trying to read them as a formula."""
    safe = []
    for i, row in enumerate(csv_rows):
        if i > 0 and row[-1][:1] in ("+", "-", "=", "@"):
            row = row[:-1] + ['="' + row[-1].replace('"', '""') + '"']
        safe.append(row)
    buf = io.StringIO()
    csv.writer(buf, lineterminator="\r\n").writerows(safe)
    text = json.dumps("\ufeff" + buf.getvalue()).replace("</", "<\\/")
    return ("<script>function downloadCsv(){var t=" + text + ";"
            "var b=new Blob([t],{type:\"text/csv;charset=utf-8\"});"
            "var a=document.createElement(\"a\");a.href=URL.createObjectURL(b);"
            "a.download=\"quote-price-history.csv\";document.body.appendChild(a);"
            "a.click();a.remove();}</script>")


def build_page(rows, files):
    parts = {}
    for r in rows:
        parts.setdefault(r["part"], {}).setdefault(r["supplier"], []).append(r)

    csv_rows = [["Part", "Supplier", "RFQ number", "Quote date", "Quantity", "Unit",
                 "Unit price", "Lead time (days)", "Freight", "Change from last quote"]]
    out = []
    w = out.append
    w("<!DOCTYPE html>\n<html lang=\"en\"><head><meta charset=\"utf-8\">")
    w("<title>Quote price history</title>")
    w("<style>"
      "body{font-family:system-ui,sans-serif;margin:2em auto;max-width:60em;padding:0 1em;color:#222}"
      "h1{margin-bottom:.2em}h2{margin-top:2em;border-bottom:2px solid #444;padding-bottom:.2em}"
      "h3{margin:1.2em 0 .3em}table{border-collapse:collapse;width:100%}"
      "th,td{border:1px solid #ccc;padding:.35em .6em;text-align:left}"
      "th{background:#f0f0f0}td.n{text-align:right;font-variant-numeric:tabular-nums}"
      ".up{color:#a00}.down{color:#060}.flat{color:#555}.warn{color:#8a5a00;font-weight:bold}"
      ".best td{background:#d4f0d4;font-weight:bold}"
      ".note{color:#555}.small{color:#555;font-size:.9em}"
      ".top{display:flex;justify-content:space-between;align-items:flex-start}"
      ".btns{display:flex;gap:.5em;margin-top:1em}.btns button{font:inherit;padding:.35em .9em;cursor:pointer}"
      ".tabs{display:flex;gap:.3em;margin-top:2em;border-bottom:2px solid #444}"
      ".tabs button{font:inherit;font-weight:bold;padding:.4em 1em;cursor:pointer;background:#f0f0f0;"
      "border:1px solid #ccc;border-bottom:none}"
      ".tabs button.active{background:#444;color:#fff;border-color:#444}"
      ".panel{display:none}.panel.active{display:block}.panel h2{margin-top:1em}"
      "@media print{.btns,.tabs{display:none}.panel{display:block}}"
      "</style></head><body>")
    w("<div class=\"top\"><h1>Quote price history</h1><div class=\"btns\">"
      "<button type=\"button\" onclick=\"window.print()\">Print</button>"
      "<button type=\"button\" onclick=\"downloadCsv()\">Download for Excel</button></div></div>")
    w("<p class=\"small\">Oldest quote first. Prices are shown as written in the quote files. "
      "Change is from the same supplier's previous quote for the same part, and is only "
      "worked out when the unit is the same. Red = price went up, green = went down.</p>")
    if parts:
        w("<h2>Latest price summary</h2>")
        w("<table><tr><th>Part</th><th>Supplier</th><th>Latest RFQ</th><th>Unit</th>"
          "<th>Latest price</th></tr>")
        for part in sorted(parts):
            latest = []
            for sup in sorted(parts[part]):
                latest.append(max(parts[part][sup],
                                  key=lambda r: (r["date"], r["rfq"], r["file"], r["order"])))
            groups = {}
            for r in latest:
                groups.setdefault((r["uom"], r["currency"]), []).append(r)
            best = set()
            for g in groups.values():
                if len(g) > 1:
                    low = min(r["price"] for r in g)
                    best.update(id(r) for r in g if r["price"] == low)
            for r in latest:
                cur = "" if r["currency"] == "USD" else f" {escape(r['currency'])}"
                cls = ' class="best"' if id(r) in best else ""
                w(f"<tr{cls}><td>{escape(part)}</td><td>{escape(r['supplier'])}</td>"
                  f"<td>{escape(r['rfq'])}</td><td>{escape(r['uom'])}</td>"
                  f"<td class=\"n\">{money(r['price'])}{cur}</td></tr>")
        w("</table>")
        w("<p class=\"small\">Green = cheapest latest price among suppliers quoting the same "
          "unit. Prices in different units are not compared.</p>")
    if not parts:
        w("<p>No quote lines with one of our part numbers were found.</p>")
    if parts:
        w("<div class=\"tabs\" role=\"tablist\">")
        for i, part in enumerate(sorted(parts)):
            a = " active" if i == 0 else ""
            w(f"<button type=\"button\" role=\"tab\" class=\"tab{a}\" data-tab=\"{i}\" "
              f"onclick=\"showTab({i})\">{escape(part)}</button>")
        w("</div>")
    for pi, part in enumerate(sorted(parts)):
        w(f"<div class=\"panel{' active' if pi == 0 else ''}\" id=\"panel{pi}\" role=\"tabpanel\">")
        w(f"<h2>{escape(part)}</h2>")
        for sup in sorted(parts[part]):
            hist = sorted(parts[part][sup],
                          key=lambda r: (r["date"], r["rfq"], r["file"], r["order"]))
            w(f"<h3>{escape(sup)}</h3>")
            w("<table><tr><th>RFQ number</th><th>Quote date</th><th>Quantity</th>"
              "<th>Unit</th><th>Unit price</th><th>Lead time (days)</th><th>Freight</th>"
              "<th>Change from last quote</th></tr>")
            prev = None
            for r in hist:
                text, cls = change_cell(prev, r)
                cur = "" if r["currency"] == "USD" else f" {escape(r['currency'])}"
                lead = "not stated" if r["lead"] is None else plain(r["lead"])
                freight = "not stated" if r["freight"] is None else money(r["freight"])
                w(f"<tr><td>{escape(r['rfq'])}</td><td>{escape(r['date'])}</td>"
                  f"<td class=\"n\">{plain(r['qty'])}</td><td>{escape(r['uom'])}</td>"
                  f"<td class=\"n\">{money(r['price'])}{cur}</td>"
                  f"<td class=\"n\">{lead}</td><td class=\"n\">{freight}</td>"
                  f"<td class=\"{cls}\">{escape(text)}</td></tr>")
                csv_rows.append([part, sup, r["rfq"], r["date"], plain(r["qty"]), r["uom"],
                                 money(r["price"]), lead, freight, text])
                prev = r
            w("</table>")
        w("</div>")
    w("<h2>Files read</h2><ul>")
    for fn, n in files:
        w(f"<li>{escape(fn)} ({n} quote{'s' if n != 1 else ''})</li>")
    w("</ul>")
    w("<script>function showTab(n){var t=document.querySelectorAll(\".tab\"),"
      "p=document.querySelectorAll(\".panel\");for(var i=0;i<t.length;i++){"
      "t[i].classList.toggle(\"active\",i===n);p[i].classList.toggle(\"active\",i===n);}}</script>")
    w(csv_script(csv_rows))
    w("</body></html>\n")
    return "".join(out)


def main(argv):
    if len(argv) != 2:
        print("Usage: python3 quote_history.py <folder with .json quote files>")
        return 2
    folder = argv[1]
    if not os.path.isdir(folder):
        print(f"Folder not found: {folder}")
        return 2
    names = sorted(n for n in os.listdir(folder)
                   if n.lower().endswith(".json") and os.path.isfile(os.path.join(folder, n)))
    if not names:
        print(f"No .json files found in folder: {folder}")
        return 2
    rows, files = [], []
    try:
        for n in names:
            count, got = load_file(n, os.path.join(folder, n))
            rows.extend(got)
            files.append((n, count))
    except BadData as e:
        print(e.message())
        return 1
    page = build_page(rows, files)
    with open(OUTPUT_NAME, "w", encoding="utf-8", newline="\n") as f:
        f.write(page)
    print(f"Wrote {OUTPUT_NAME} from {len(files)} files.")
    return 0


if __name__ == "__main__":
    sys.exit(main(sys.argv))
