from openpyxl.formula.translate import Translator

def normalize_formula(formula: str, cell_ref: str, origin_row: int = 1) -> str:
    """Translate formula back to row-1 to create a canonical form."""
    col = ''.join(c for c in cell_ref if c.isalpha())
    origin = f"{col}{origin_row}"
    try:
        return Translator(formula, cell_ref).translate_formula(origin)
    except Exception:
        return formula  # absolute refs won't translate

def detect_row_patterns(formulas: list[dict]) -> dict:
    """Group formulas by normalized pattern.

    Returns: {normalized_formula: [{"row": N, "cell_ref": "X", ...}, ...]}
    Each group with len > 1 is a loop candidate.
    """
    from collections import defaultdict
    patterns = defaultdict(list)
    for f in formulas:
        norm = normalize_formula(f["formula"], f["cell_ref"])
        patterns[norm].append(f)
    return dict(patterns)

def pattern_to_python(pattern_key: str, cells: list[dict], var_map: dict) -> str:
    """Generate Python code from a formula pattern group."""
    expr = formula_to_python(f"={pattern_key}", var_map)
    if expr is None:
        return f"# MANUAL: {pattern_key} ({len(cells)} cells)"

    if len(cells) == 1:
        return f"result = {expr}"

    # Detect row range
    rows = sorted(int(c["cell_ref"].lstrip("ABCDEFGHIJKLMNOPQRSTUVWXYZ")) for c in cells)
    start, end = rows[0], rows[-1]

    # Identify column variables used in the formula
    return (
        f"# Pattern: {pattern_key} ({len(cells)} rows)\n"
        f"for i in range({start}, {end + 1}):\n"
        f"    result[i] = {expr}  # row-indexed"
    )

1. Dual-pass load (openpyxl)           → formula strings + cached values
2. Normalize formulas (Translator)     → canonical row-1 forms
3. Group by pattern                    → {pattern: [rows]}
4. For each pattern:
   a. Translate formula → Python expr  → formula_to_python()
   b. Determine loop/single/vectorize  → based on group size
   c. Generate function + test         → with Excel values as assertions
5. Assemble module                     → one .py file per sheet