atm-backtesting/scripts/strip_dashboard.py

# -*- coding: utf-8 -*-
"""Migrare UNICĂ: scoate sheet-ul Dashboard din data/backtest.xlsx.

Dashboard-ul (cu ~4.200 coloane-helper) era cauza dimensiunii de ~39 MB.
După separare trăiește în data/Dashboard.xlsx (generat de generate_dashboard.py).
Acest script doar curăță fișierul real → ~0.8 MB.

NU folosește openpyxl pentru rescriere (ar ȘTERGE dropdown-urile x14 din Trades).
În schimb face chirurgie pe zip: scoate DOAR partea XML a sheet-ului Dashboard +
drawings/charts asociate + calcChain.xml, lăsând XML-ul Trades (cu dropdown-urile)
byte-cu-byte intact. Excel regenerează calcChain la prima deschidere.

Rulare (cere confirmare; face backup automat):
    python scripts/strip_dashboard.py --yes

Alternativă manuală 100% sigură (dacă preferi): deschide backtest.xlsx în Excel,
click-dreapta pe tab-ul "Dashboard" → Delete → Salvează.
"""

import posixpath
import re
import shutil
import sys
import xml.etree.ElementTree as ET
import zipfile
from datetime import datetime
from pathlib import Path

SRC = Path(__file__).resolve().parent.parent / "data" / "backtest.xlsx"

NS = {
    "main": "http://schemas.openxmlformats.org/spreadsheetml/2006/main",
    "r": "http://schemas.openxmlformats.org/officeDocument/2006/relationships",
    "rel": "http://schemas.openxmlformats.org/package/2006/relationships",
    "ct": "http://schemas.openxmlformats.org/package/2006/content-types",
}


def _norm(part: str) -> str:
    """xl/worksheets/sheet3.xml -> /xl/worksheets/sheet3.xml (cheie content-types)."""
    return "/" + part if not part.startswith("/") else part


def _resolve(owner_part: str, target: str) -> str:
    """Rezolvă un Target de relationship relativ la partea care îl deține.

    ex: owner=xl/worksheets/sheet3.xml, target=../drawings/drawing1.xml
        -> xl/drawings/drawing1.xml
    """
    if target.startswith("/"):
        return target.lstrip("/")
    base = posixpath.dirname(owner_part)
    return posixpath.normpath(posixpath.join(base, target))


def find_dashboard_parts(z: zipfile.ZipFile):
    """Întoarce (set de părți de șters, r:id-ul Dashboard din workbook.xml)."""
    names = set(z.namelist())

    # 1) workbook.xml: găsește <sheet name="Dashboard" r:id=...>
    wb_xml = z.read("xl/workbook.xml").decode("utf-8")
    wb_root = ET.fromstring(wb_xml)
    rid = None
    for sheet in wb_root.iter(f"{{{NS['main']}}}sheet"):
        if sheet.get("name") == "Dashboard":
            rid = sheet.get(f"{{{NS['r']}}}id")
            break
    if rid is None:
        return None, None

    # 2) workbook.xml.rels: r:id -> worksheets/sheetN.xml
    rels_xml = z.read("xl/_rels/workbook.xml.rels").decode("utf-8")
    rels_root = ET.fromstring(rels_xml)
    target = None
    for rel in rels_root.iter(f"{{{NS['rel']}}}Relationship"):
        if rel.get("Id") == rid:
            target = rel.get("Target")  # ex: worksheets/sheet3.xml
            break
    if target is None:
        return None, None
    sheet_part = _resolve("xl/workbook.xml", target)  # xl/worksheets/sheetN.xml

    drop = {sheet_part}

    # Helper recursiv: adaugă o parte + tot ce referă rels-ul ei (drawings->charts->...)
    def add_with_rels(part: str):
        if part not in names:
            return
        drop.add(part)
        rels_path = posixpath.join(
            posixpath.dirname(part), "_rels", posixpath.basename(part) + ".rels"
        )
        if rels_path not in names:
            return
        drop.add(rels_path)
        rr = ET.fromstring(z.read(rels_path).decode("utf-8"))
        for rel in rr.iter(f"{{{NS['rel']}}}Relationship"):
            tgt = rel.get("Target", "")
            if rel.get("TargetMode") == "External" or not tgt:
                continue
            child = _resolve(part, tgt)
            # doar părți interne de tip drawing/chart/style/colors (nu media partajată)
            if any(k in child for k in ("drawing", "chart")):
                add_with_rels(child)

    # 3-4) sheet Dashboard -> drawings -> charts (transitiv, căi rezolvate corect)
    add_with_rels(sheet_part)

    # 5) calcChain — Excel îl regenerează; scoaterea lui elimină bloat-ul
    if "xl/calcChain.xml" in names:
        drop.add("xl/calcChain.xml")

    return drop, rid


def rewrite(z_in: zipfile.ZipFile, drop: set, rid: str, out_path: Path):
    drop_norm = {_norm(p) for p in drop}

    # workbook.xml — scoate <sheet name="Dashboard"> și definedName-urile orfane
    # care referă Dashboard! (ex: _FilterDatabase). Dashboard e ULTIMUL sheet, deci
    # localSheetId-urile Config(0)/Trades(1) nu se reindexează.
    wb_xml = z_in.read("xl/workbook.xml").decode("utf-8")
    wb_xml = re.sub(
        r'<sheet[^>]*name="Dashboard"[^>]*/>', "", wb_xml, count=1
    )
    wb_xml = re.sub(
        r"<definedName\b[^>]*>[^<]*Dashboard![^<]*</definedName>", "", wb_xml
    )
    # dacă blocul <definedNames> a rămas gol, scoate-l
    wb_xml = re.sub(r"<definedNames>\s*</definedNames>", "", wb_xml)

    # workbook.xml.rels — scoate relationship-ul rid
    rels_xml = z_in.read("xl/_rels/workbook.xml.rels").decode("utf-8")
    rels_xml = re.sub(
        r'<Relationship[^>]*Id="' + re.escape(rid) + r'"[^>]*/>',
        "",
        rels_xml,
        count=1,
    )

    # [Content_Types].xml — scoate Override-urile părților șterse
    ct_xml = z_in.read("[Content_Types].xml").decode("utf-8")
    ct_root = ET.fromstring(ct_xml)
    ct_ns = NS["ct"]
    for ov in list(ct_root):
        if ov.tag == f"{{{ct_ns}}}Override" and ov.get("PartName") in drop_norm:
            ct_root.remove(ov)
    ct_out = ET.tostring(ct_root, encoding="unicode")
    if not ct_out.startswith("<?xml"):
        ct_out = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>\n' + ct_out

    replacements = {
        "xl/workbook.xml": wb_xml,
        "xl/_rels/workbook.xml.rels": rels_xml,
        "[Content_Types].xml": ct_out,
    }

    with zipfile.ZipFile(out_path, "w", zipfile.ZIP_DEFLATED) as z_out:
        for item in z_in.infolist():
            if item.filename in drop:
                continue
            if item.filename in replacements:
                z_out.writestr(item, replacements[item.filename])
            else:
                z_out.writestr(item, z_in.read(item.filename))


def main() -> int:
    if "--yes" not in sys.argv:
        print("Operație pe data/backtest.xlsx: scoate sheet-ul Dashboard (chirurgie zip).")
        print("Dropdown-urile x14 din Trades rămân intacte. Backup automat înainte.")
        print("Confirmă cu:  python scripts/strip_dashboard.py --yes")
        return 1

    if not SRC.exists():
        print(f"EROARE: nu găsesc {SRC}")
        return 1

    timestamp = datetime.now().strftime("%Y%m%d-%H%M%S")
    backup = SRC.with_name(f"{SRC.stem}.backup-{timestamp}{SRC.suffix}")
    shutil.copy2(SRC, backup)
    print(f"Backup -> {backup}")

    with zipfile.ZipFile(SRC) as z:
        drop, rid = find_dashboard_parts(z)
        if not drop:
            print("Nimic de făcut: sheet-ul 'Dashboard' nu există în backtest.xlsx.")
            return 0
        print("Părți eliminate:")
        for p in sorted(drop):
            print("   ", p)
        tmp = SRC.with_name(SRC.stem + ".stripping.tmp.xlsx")
        rewrite(z, drop, rid, tmp)

    tmp.replace(SRC)
    print(f"Sheet 'Dashboard' eliminat. Salvat {SRC}")
    print("Pas următor: deschide & SALVEAZĂ backtest.xlsx în Excel (recalcul),")
    print("apoi rulează refresh_dashboard.bat pentru a genera Dashboard.xlsx.")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())