atm-backtesting/scripts/verify_template.py

"""Smoke test for data/backtest.xlsx after regeneration.

Asserts column positions, sample row formula structure, and TOP CANDIDATE
formula sources. Run AFTER `python scripts/generate_template.py`.

Exit codes: 0 = all PASS, 1 = at least one FAIL, 2 = workbook missing.
"""
from __future__ import annotations

import sys
from pathlib import Path

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

# Romanian diacritics in test names — force UTF-8 stdout on Windows cp1252.
try:
    sys.stdout.reconfigure(encoding="utf-8")
except (AttributeError, OSError):
    pass

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

FAILURES: list[str] = []


def _report(name: str, ok: bool, detail: str = "") -> None:
    tag = "[PASS]" if ok else "[FAIL]"
    msg = f"{tag} {name}"
    if detail:
        msg += f" -- {detail}"
    print(msg)
    if not ok:
        FAILURES.append(name)


def _find_row_with(ws, col_letter: str, needle: str) -> int | None:
    """Return 1-based row where ws[col_letter+row].value == needle, else None."""
    for row in range(1, ws.max_row + 1):
        val = ws[f"{col_letter}{row}"].value
        if val == needle:
            return row
    return None


def _find_col_for_header(ws, row: int, needle: str) -> str | None:
    for col_idx in range(1, ws.max_column + 1):
        if ws.cell(row=row, column=col_idx).value == needle:
            return get_column_letter(col_idx)
    return None


def check_window_grid_headers(wb) -> None:
    ws = wb["Dashboard"]
    header_row = _find_row_with(ws, "A", "Fereastra")
    if header_row is None:
        _report("Dashboard window grid header row", False, "no row with A='Fereastra'")
        return
    _report("Dashboard window grid header row", True, f"row {header_row}")

    expected = {
        "A": "Fereastra",
        "D": "Filtru",
        "E": "Strategie",
        "F": "N",
        "S": "Status Edge",
        "T": "Score_Toate",
        "U": "Score_Prima",
    }
    for col, want in expected.items():
        got = ws[f"{col}{header_row}"].value
        if got == want:
            _report(f"Dashboard header {col}={want!r}", True)
        else:
            actual_col = _find_col_for_header(ws, header_row, want)
            detail = (
                f"got {got!r} at {col}; expected {want!r}. "
                f"Header {want!r} actually at column {actual_col}"
            )
            _report(f"Dashboard header {col}={want!r}", False, detail)


def check_hidden_columns(wb) -> None:
    ws = wb["Dashboard"]
    for col in ("T", "U"):
        hidden = ws.column_dimensions[col].hidden is True
        _report(
            f"Dashboard column {col} hidden",
            hidden,
            "" if hidden else f"hidden={ws.column_dimensions[col].hidden!r}",
        )


def check_primawin_headers(wb) -> tuple[str | None, int]:
    """Return (column letter of PrimaWin_0, count of PrimaWin_* headers)."""
    ws = wb["Trades"]
    win0_col: str | None = None
    count = 0
    for col_idx in range(1, ws.max_column + 1):
        v = ws.cell(row=1, column=col_idx).value
        if isinstance(v, str) and v.startswith("PrimaWin_"):
            count += 1
            if v == "PrimaWin_0":
                win0_col = get_column_letter(col_idx)
    if count == 0:
        _report("Trades has PrimaWin_* headers", False, "none found")
    else:
        _report(
            "Trades has PrimaWin_* headers",
            True,
            f"{count} columns; PrimaWin_0 at column {win0_col}",
        )
    return win0_col, count


def check_primawin_formula(wb, win0_col: str | None) -> None:
    if win0_col is None:
        _report("PrimaWin_0 sample row formula", False, "no PrimaWin_0 column")
        return
    ws = wb["Trades"]
    formula = ws[f"{win0_col}2"].value
    if not isinstance(formula, str):
        _report(
            "PrimaWin_0 sample row formula",
            False,
            f"row 2 value is {formula!r}, not a formula",
        )
        return
    must_have = ["TIME(16,30,0)", "Config!$B$17", "COUNTIFS"]
    missing = [token for token in must_have if token not in formula]
    if missing:
        _report(
            "PrimaWin_0 sample row formula",
            False,
            f"missing tokens: {missing}; formula starts: {formula[:120]!r}",
        )
    else:
        _report("PrimaWin_0 sample row formula", True, "TIME(16,30,0) + Config!$B$17 present")


def check_top_candidate_sources(wb) -> None:
    ws = wb["Dashboard"]
    title_toate = "TOP 5 FERESTRE — Toate trade-urile"
    title_prima = "TOP 5 FERESTRE — Prima per Indicator"

    # Fallback: scan loosely if the em-dash doesn't match.
    def _find_title(prefix: str) -> int | None:
        for row in range(1, ws.max_row + 1):
            v = ws.cell(row=row, column=1).value
            if isinstance(v, str) and v.startswith(prefix):
                return row
        return None

    cases = [
        ("TOP 5 Toate", title_toate, "Toate", "LARGE($T$"),
        ("TOP 5 Prima", title_prima, "Prima per Indicator", "LARGE($U$"),
    ]
    for name, exact_title, fallback_prefix, expected_token in cases:
        title_row = _find_row_with(ws, "A", exact_title)
        if title_row is None:
            title_row = _find_title(f"TOP 5 FERESTRE")
            # Narrow: must mention the discriminator
            title_row = None
            for row in range(1, ws.max_row + 1):
                v = ws.cell(row=row, column=1).value
                if isinstance(v, str) and "TOP 5 FERESTRE" in v and fallback_prefix in v:
                    title_row = row
                    break
        if title_row is None:
            _report(f"{name} title found", False, f"no row containing 'TOP 5 FERESTRE' + {fallback_prefix!r}")
            continue
        _report(f"{name} title found", True, f"row {title_row}")

        # Look at the next few rows for a LARGE() formula in any column.
        found = False
        for offset in (1, 2, 3):
            scan_row = title_row + offset
            for col_idx in range(1, ws.max_column + 1):
                cell = ws.cell(row=scan_row, column=col_idx).value
                if isinstance(cell, str) and expected_token in cell:
                    _report(
                        f"{name} uses {expected_token}",
                        True,
                        f"row {scan_row} col {get_column_letter(col_idx)}",
                    )
                    found = True
                    break
            if found:
                break
        if not found:
            # Sample a formula for diagnostics.
            sample = ws.cell(row=title_row + 1, column=1).value
            _report(
                f"{name} uses {expected_token}",
                False,
                f"token not found in rows {title_row+1}..{title_row+3}; sample A: {sample!r}",
            )


def check_config_escape_hatch(wb) -> None:
    ws = wb["Config"]
    a17 = ws["A17"].value
    b17 = ws["B17"].value
    _report(
        "Config!A17 = 'Activează filtru Prima'",
        a17 == "Activează filtru Prima",
        f"got {a17!r}",
    )
    _report("Config!B17 = 'DA'", b17 == "DA", f"got {b17!r}")


def main() -> int:
    if not OUTPUT.exists():
        print(f"Workbook not found: {OUTPUT}")
        print("RUN python scripts/generate_template.py FIRST")
        return 2

    wb = load_workbook(OUTPUT, data_only=False)

    print(f"Verifying {OUTPUT}")
    print("-" * 60)
    check_window_grid_headers(wb)
    check_hidden_columns(wb)
    win0_col, _ = check_primawin_headers(wb)
    check_primawin_formula(wb, win0_col)
    check_top_candidate_sources(wb)
    check_config_escape_hatch(wb)
    print("-" * 60)
    if FAILURES:
        print(f"{len(FAILURES)} FAIL: {FAILURES}")
        return 1
    print("ALL CHECKS PASSED")
    return 0


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