#!/usr/bin/env python3
"""
build_walls.py (v2, PDF pipeline) — parses the daily CME QuikStrike
"Create PDF Document" exports in oi_pdfs\\ into walls_data.json for the
Call/Put Walls page.

Daily routine: move yesterday's PDFs into oi_pdfs\\history\\, drop the
five fresh exports into oi_pdfs\\. Filenames don't matter — the
instrument is read from inside each PDF; the newest file per instrument
wins. The dashboard server rebuilds automatically when the folder
changes. oi_data.xlsx (the old paste template) still works as a
fallback for any instrument without a PDF.

Per instrument it:
  1. Parses the OI matrix by word coordinates (per-expiry columns,
     C/P split, commas, multi-page, per-character header fonts).
  2. Reads the FRONT-MONTH futures price from the header (Gold/NQ show
     a different price per expiry — the nearest-DTE one is used).
  3. Builds TWO wall sets: "near" (<= NEAR_DTE_MAX days to expiry —
     what pins price this week) and "all" (total OI — the structural
     walls). Strongest only: >= 25% of the side's max, capped at 6.
  4. Converts futures strikes to CFD levels: basis = futures ref minus
     live MT5 spot. USDJPY: CME's 6J is quoted inverted (USD per JPY) —
     strikes are flipped (1/strike) and call/put semantics swap, so
     call_walls are always CFD-upside and put_walls CFD-downside.

Save to: src\\execution\\build_walls.py
Run:     python src\\execution\\build_walls.py
Requires: pdfplumber + openpyxl (python -m pip install pdfplumber openpyxl)
and the MT5 terminal running.
"""
import json
import os
import re
import sys
from collections import Counter
from datetime import datetime, timezone
from pathlib import Path

try:
    import pdfplumber
except ImportError:
    sys.exit("pdfplumber missing — run: python -m pip install pdfplumber")
try:
    import MetaTrader5 as mt5
except ImportError:
    sys.exit("MetaTrader5 missing — run: python -m pip install MetaTrader5")

BASE_PATH = Path(__file__).resolve().parents[2]
PDF_DIR = BASE_PATH / "oi_pdfs"
XLSX = BASE_PATH / "oi_data.xlsx"
OUT_JSON = BASE_PATH / "walls_data.json"
FORECAST_JSON = BASE_PATH / "forecast_data.json"

NEAR_DTE_MAX = 10      # "near-term" wall window (days to expiry)
WALL_MIN_FRAC = 0.25   # a wall must be >= 25% of its side's biggest wall
WALL_MAX = 6           # at most this many walls per side

# PDF title fragment -> display name
TITLES = [("EUR/USD", "EURUSD"), ("GBP/USD", "GBPUSD"),
          ("JPY/USD", "USDJPY"), ("Gold", "GOLD"), ("NASDAQ", "NQ")]

# display name -> (inverted?, fallback MT5 candidates)
INSTRUMENTS = {
    "GOLD":   (False, ["XAUUSD", "GOLD", "XAUUSD.a", "XAUUSD.r"]),
    "EURUSD": (False, ["EURUSD", "EURUSD.a", "EURUSD.r"]),
    "GBPUSD": (False, ["GBPUSD", "GBPUSD.a", "GBPUSD.r"]),
    "USDJPY": (True,  ["USDJPY", "USDJPY.a", "USDJPY.r"]),
    "NQ":     (False, ["USTEC", "NAS100", "US100", "USTEC.cash",
                       "NAS100.cash", "US100.cash"]),
}


# ── shared helpers ───────────────────────────────────────────────────────
def _fnum(s):
    s = str(s).replace(",", "").replace("\xa0", "").strip()
    if not s:
        return None
    try:
        return float(s)
    except ValueError:
        return None


def sources_signature():
    """Fingerprint of the OI sources — must match the server's watcher."""
    parts = []
    if PDF_DIR.is_dir():
        for p in sorted(PDF_DIR.glob("*.pdf")):
            try:
                parts.append(f"{p.name}:{int(p.stat().st_mtime)}")
            except OSError:
                pass
    if XLSX.exists():
        try:
            parts.append(f"xlsx:{int(XLSX.stat().st_mtime)}")
        except OSError:
            pass
    return "|".join(parts)


def filename_date(name):
    m = re.search(r"(\d{1,2})[._\- ](\d{1,2})[._\- ](\d{4})", name)
    if m:
        d, mo, y = m.groups()
        try:
            return f"{int(y):04d}-{int(mo):02d}-{int(d):02d}"
        except ValueError:
            pass
    return None


# ── PDF parser (coordinate-based; validated on all five real exports) ────
def _cluster_lines(words, ytol=2.5):
    out = []
    for w in sorted(words, key=lambda w: (w['top'], w['x0'])):
        if out and abs(w['top'] - out[-1][0]['top']) <= ytol:
            out[-1].append(w)
        else:
            out.append([w])
    return [sorted(l, key=lambda w: w['x0']) for l in out]


def _parse_pdf_page(page):
    lines = _cluster_lines(page.extract_words())

    # the C/P header row defines the column grid
    cp = None
    for i, l in enumerate(lines):
        only = [w['text'] for w in l if w['text'] in ('C', 'P')]
        if len(only) >= 12 and only.count('C') == only.count('P'):
            cp = i
            break
    if cp is None:
        return None
    cols = [((w['x0'] + w['x1']) / 2, w['text'])
            for w in lines[cp] if w['text'] in ('C', 'P')]
    pairs = []
    j = 0
    while j < len(cols):
        if cols[j][1] == 'C' and j + 1 < len(cols) and cols[j + 1][1] == 'P':
            pairs.append((cols[j][0], cols[j + 1][0]))
            j += 2
        else:
            j += 1
    if not pairs:
        return None
    centers = [c for pr in pairs for c in pr]
    bounds = [(centers[k] + centers[k + 1]) / 2
              for k in range(len(centers) - 1)]
    left_bound = centers[0] - (bounds[0] - centers[0] if bounds else 10)

    def col_of(x):
        if x < left_bound:
            return -1
        k = 0
        while k < len(bounds) and x > bounds[k]:
            k += 1
        return k

    pc = [(a + b) / 2 for a, b in pairs]
    pb = [(pc[k] + pc[k + 1]) / 2 for k in range(len(pc) - 1)]

    def pair_of(x):
        k = 0
        while k < len(pb) and x > pb[k]:
            k += 1
        return k

    def pair_join(line):
        """Header fonts split into characters — rebuild text per column."""
        per = {k: [] for k in range(len(pairs))}
        for w in line:
            per[pair_of((w['x0'] + w['x1']) / 2)].append(w)
        return {k: ''.join(x['text'] for x in
                           sorted(ws, key=lambda w: w['x0']))
                for k, ws in per.items()}

    # DTE row (e.g. "1 DTE" rendered as characters)
    dtes = [None] * len(pairs)
    dte_i = None
    for i in range(cp - 1, max(-1, cp - 7), -1):
        if ''.join(w['text'] for w in lines[i]).count('DTE') >= 3:
            dte_i = i
            for k, t in pair_join(lines[i]).items():
                m = re.match(r'(\d+)DTE', t.replace(' ', ''))
                if m:
                    dtes[k] = int(m.group(1))
            break

    # futures price row: first line above the DTE row whose per-column
    # text yields floats for at least half the columns (handles JPY's
    # split "0.00615|8" tokens). Front month = nearest-DTE column.
    fut = None
    if dte_i is not None:
        for i in range(dte_i - 1, max(-1, cp - 8), -1):
            vals = {}
            for k, t in pair_join(lines[i]).items():
                mm = re.findall(r'\d+\.\d+|\d{3,}', t)
                if mm:
                    v = _fnum(max(mm, key=len))
                    if v is not None and v > 0:
                        vals[k] = v
            if len(vals) >= max(2, len(pairs) // 2):
                valid = [k for k in vals if dtes[k] is not None]
                if valid:
                    fut = vals[min(valid, key=lambda k: dtes[k])]
                else:
                    fut = Counter(vals.values()).most_common(1)[0][0]
                break

    # data rows: leftmost numeric token = strike; values right-aligned
    strikes = {}
    for l in lines[cp + 1:]:
        nums = [(w, _fnum(w['text'])) for w in l]
        nums = [(w, v) for w, v in nums if v is not None]
        if not nums:
            continue
        w0, v0 = nums[0]
        if col_of((w0['x0'] + w0['x1']) / 2) != -1:
            continue
        row = strikes.setdefault(v0, {})
        for w, v in nums[1:]:
            ci = col_of(w['x1'] - 1.0)
            if ci < 0:
                continue
            key = (ci // 2, 'C' if ci % 2 == 0 else 'P')
            row[key] = row.get(key, 0) + int(v)
    return {"pairs": len(pairs), "dtes": dtes, "fut": fut,
            "strikes": strikes}


def parse_pdf(path):
    """Returns dict: inst, fut, dtes, expiries, strikes{stk:{(pair,side):oi}}"""
    with pdfplumber.open(path) as pdf:
        txt = (pdf.pages[0].extract_text() or "")
        inst = next((n for p, n in TITLES if p.lower() in txt.lower()), None)
        agg = {}
        fut = None
        dtes = None
        npairs = 0
        for pg in pdf.pages:
            r = _parse_pdf_page(pg)
            if not r:
                continue
            if fut is None:
                fut, dtes, npairs = r["fut"], r["dtes"], r["pairs"]
            for stk, row in r["strikes"].items():
                d = agg.setdefault(stk, {})
                for k, v in row.items():
                    d[k] = d.get(k, 0) + v
        if inst is None or not agg or dtes is None:
            return {"error": "could not parse matrix", "inst": inst}
        return {"inst": inst, "fut": fut, "dtes": dtes,
                "expiries": npairs, "strikes": agg}


# ── xlsx fallback parser (the old paste template) ────────────────────────
def parse_xlsx_sheet(ws):
    grid = [[c.value for c in row] for row in ws.iter_rows()]
    nrows = len(grid)
    cp_row = None
    for r in range(nrows):
        vals = [str(v).strip().upper() if v is not None else ""
                for v in grid[r]]
        if vals.count("C") >= 4 and all(v in ("", "C", "P") for v in vals):
            cp_row = r
            break
    if cp_row is None:
        return {"error": "no pasted matrix found"}
    vals = [str(v).strip().upper() if v is not None else ""
            for v in grid[cp_row]]
    pairs = []
    for c, v in enumerate(vals):
        if v == "C":
            p = next((k for k in range(c + 1, len(vals))
                      if vals[k] == "P"), None)
            if p is not None:
                pairs.append((c, p))

    def cell(r, c):
        return grid[r][c] if 0 <= r < nrows and 0 <= c < len(grid[r]) else None

    dtes = []
    for cc, _ in pairs:
        m = re.search(r"(\d+)", str(cell(cp_row - 1, cc) or ""))
        dtes.append(int(m.group(1)) if m else None)
    hdr = []
    for r in range(max(0, cp_row - 5), max(0, cp_row - 2)):
        for v in grid[r]:
            f = _fnum(v)
            if f is not None and f > 0:
                hdr.append(round(f, 6))
    fut = Counter(hdr).most_common(1)[0][0] if hdr else None
    strikes = {}
    for r in range(cp_row + 1, nrows):
        stk = _fnum(cell(r, 0))
        if stk is None:
            continue
        row = {}
        for k, (cc, pc) in enumerate(pairs):
            cv = _fnum(cell(r, cc)) or 0
            pv = _fnum(cell(r, pc)) or 0
            if cv:
                row[(k, 'C')] = int(cv)
            if pv:
                row[(k, 'P')] = int(pv)
        if row:
            strikes[stk] = row
    if not strikes:
        return {"error": "matrix found but no OI rows parsed"}
    return {"fut": fut, "dtes": dtes, "expiries": len(pairs),
            "strikes": strikes}


# ── walls ────────────────────────────────────────────────────────────────
def side_totals(parsed, side):
    """[(strike, total_oi, near_oi)] for one side."""
    dtes = parsed["dtes"]
    out = []
    for stk, row in parsed["strikes"].items():
        tot = near = 0
        for (pi, sd), v in row.items():
            if sd != side:
                continue
            tot += v
            if pi < len(dtes) and dtes[pi] is not None \
                    and dtes[pi] <= NEAR_DTE_MAX:
                near += v
        if tot:
            out.append((stk, tot, near))
    return out


def pick(items, metric):
    """metric: index 1 (total) or 2 (near). Strongest-only rule."""
    pool = [t for t in items if t[metric] > 0]
    if not pool:
        return []
    mx = max(t[metric] for t in pool)
    keep = [t for t in pool if t[metric] >= mx * WALL_MIN_FRAC]
    keep.sort(key=lambda t: -t[metric])
    return [(t, round(100 * t[metric] / mx, 1)) for t in keep[:WALL_MAX]]


def spot_and_digits(display, candidates):
    sym = None
    try:
        with open(FORECAST_JSON, encoding="utf-8") as f:
            for i in json.load(f).get("instruments", []):
                if i.get("display") == display and i.get("ok"):
                    sym = i.get("symbol")
                    break
    except Exception:
        pass
    for cand in ([sym] if sym else []) + candidates:
        if cand and mt5.symbol_select(cand, True):
            info = mt5.symbol_info(cand)
            tick = mt5.symbol_info_tick(cand)
            px = None
            if tick:
                px = float(tick.last) if getattr(tick, "last", 0) else \
                     (float(tick.bid) if getattr(tick, "bid", 0) else None)
            if px:
                return cand, px, (int(info.digits) if info else 5)
    return None, None, None


def build_instrument(display, parsed, src_label, oi_date):
    inverted, candidates = INSTRUMENTS[display]
    entry = {"display": display, "inverted": inverted,
             "source": src_label, "oi_date": oi_date,
             "near_dte_max": NEAR_DTE_MAX}
    fut_ref = parsed.get("fut")
    if fut_ref is None:
        entry.update({"ok": False, "error": "no futures reference price"})
        return entry
    sym, spot, digits = spot_and_digits(display, candidates)
    if spot is None:
        entry.update({"ok": False, "error": "no MT5 spot price"})
        return entry
    if inverted:
        implied = 1.0 / fut_ref
        basis = implied - spot
        conv = lambda k: (1.0 / k) - basis
    else:
        basis = fut_ref - spot
        conv = lambda k: k - basis

    c_items = side_totals(parsed, 'C')
    p_items = side_totals(parsed, 'P')
    if inverted:                # 6J calls = CFD downside; puts = upside
        c_items, p_items = p_items, c_items

    def walls(items, metric):
        out = []
        for (stk, tot, near), pct in pick(items, metric):
            out.append({"strike": stk, "level": round(conv(stk), digits),
                        "oi": tot, "near": near, "pct": pct})
        return out

    entry.update({
        "ok": True, "symbol": sym, "digits": digits,
        "fut_ref": fut_ref, "spot_at_build": round(spot, digits),
        "basis": round(basis, digits),
        "expiries": parsed.get("expiries"),
        "dte_range": [min((d for d in parsed["dtes"] if d is not None),
                          default=None),
                      max((d for d in parsed["dtes"] if d is not None),
                          default=None)],
        "walls": {
            "all":  {"calls": walls(c_items, 1), "puts": walls(p_items, 1)},
            "near": {"calls": walls(c_items, 2), "puts": walls(p_items, 2)},
        },
    })
    return entry


def main():
    # gather PDF sources: newest file per instrument, top level only
    pdf_for = {}
    if PDF_DIR.is_dir():
        for p in sorted(PDF_DIR.glob("*.pdf"),
                        key=lambda p: p.stat().st_mtime):
            try:
                r = parse_pdf(p)
            except Exception as e:
                print(f"  {p.name}: unreadable ({e})")
                continue
            if r.get("inst") in INSTRUMENTS and "error" not in r:
                pdf_for[r["inst"]] = (p, r)
            else:
                print(f"  {p.name}: skipped "
                      f"({r.get('error', 'unknown instrument')})")

    xlsx_wb = None
    if XLSX.exists():
        try:
            import openpyxl
            xlsx_wb = openpyxl.load_workbook(XLSX, data_only=True)
        except Exception as e:
            print(f"  oi_data.xlsx unreadable: {e}")

    if not pdf_for and xlsx_wb is None:
        sys.exit("no OI sources — drop PDFs into oi_pdfs\\ "
                 "(or fill oi_data.xlsx)")

    if not mt5.initialize():
        sys.exit(f"MT5 initialize failed: {mt5.last_error()}")
    try:
        out = {
            "generated_utc":
                datetime.now(timezone.utc).strftime("%Y-%m-%d %H:%M:%S"),
            "source_sig": sources_signature(),
            "source_mtime": XLSX.stat().st_mtime if XLSX.exists() else 0,
            "instruments": [],
        }
        print("CALL/PUT WALLS build (PDF pipeline)")
        for display in INSTRUMENTS:
            if display in pdf_for:
                path, parsed = pdf_for[display]
                oi_date = filename_date(path.name) or \
                    datetime.fromtimestamp(path.stat().st_mtime,
                                           timezone.utc).strftime("%Y-%m-%d")
                entry = build_instrument(display, parsed,
                                         f"PDF {path.name}", oi_date)
            elif xlsx_wb is not None and display in xlsx_wb.sheetnames:
                parsed = parse_xlsx_sheet(xlsx_wb[display])
                if "error" in parsed:
                    entry = {"display": display, "ok": False,
                             "error": parsed["error"],
                             "source": "oi_data.xlsx"}
                else:
                    b2 = _fnum(xlsx_wb[display]["B2"].value)
                    if b2:
                        parsed["fut"] = b2
                    b3 = xlsx_wb[display]["B3"].value
                    entry = build_instrument(
                        display, parsed, "xlsx paste",
                        str(b3).strip() if b3 else None)
            else:
                entry = {"display": display, "ok": False,
                         "error": "no PDF and no xlsx tab", "source": None}
            out["instruments"].append(entry)
            if entry.get("ok"):
                nc = entry["walls"]["near"]["calls"]
                np_ = entry["walls"]["near"]["puts"]
                ac = entry["walls"]["all"]["calls"]
                ap = entry["walls"]["all"]["puts"]
                print(f"  {display:<8} OK    {entry['source']:<26} "
                      f"nearCW {nc[0]['level'] if nc else '-'} · "
                      f"nearPW {np_[0]['level'] if np_ else '-'} · "
                      f"allCW {ac[0]['level'] if ac else '-'} · "
                      f"allPW {ap[0]['level'] if ap else '-'}")
            else:
                print(f"  {display:<8} SKIP  {entry.get('error')}")
        tmp = OUT_JSON.with_name(OUT_JSON.name + ".tmp")
        with open(tmp, "w", encoding="utf-8") as f:
            json.dump(out, f)
        os.replace(tmp, OUT_JSON)
        print(f"  wrote {OUT_JSON}")
    finally:
        mt5.shutdown()


if __name__ == "__main__":
    main()