prototype.py

from openpyxl import load_workbook
import re
from pathlib import Path
import networkx as nx
from datetime import datetime
from difflib import SequenceMatcher


EXTERNAL_REF_REGEX = r"\[([^\]]+\.xlsx)\]"

def extract_dependencies(xlsx_path):
    wb = load_workbook(xlsx_path, data_only=False)
    deps = []

    for sheet in wb.worksheets:
        for row in sheet.iter_rows():
            for cell in row:
                if isinstance(cell.value, str) and cell.value.startswith("="):
                    matches = re.findall(EXTERNAL_REF_REGEX, cell.value)
                    for match in matches:
                        deps.append({
                            "source_file": xlsx_path.name,
                            "source_sheet": sheet.title,
                            "target_file": match,
                            "formula": cell.value
                        })
    return deps



def build_dependency_graph(folder_path):
    graph = nx.DiGraph()
    folder = Path(folder_path)

    for xlsx in folder.glob("*.xlsx"):
        deps = extract_dependencies(xlsx)
        for dep in deps:
            graph.add_edge(
                dep["source_file"],
                dep["target_file"],
                sheet=dep["source_sheet"],
                formula=dep["formula"]
            )
    return graph


def detect_stale_dependencies(graph, folder_path, days_threshold=30):
    folder = Path(folder_path)
    warnings = []

    for source, target in graph.edges():
        target_path = folder / target
        if target_path.exists():
            modified = datetime.fromtimestamp(target_path.stat().st_mtime)
            age_days = (datetime.now() - modified).days
            if age_days > days_threshold:
                warnings.append({
                    "source": source,
                    "target": target,
                    "issue": "STALE_REFERENCE",
                    "days_since_update": age_days
                })
        else:
            warnings.append({
                "source": source,
                "target": target,
                "issue": "MISSING_FILE"
            })

    return warnings



def similar(a, b):
    return SequenceMatcher(None, a.lower(), b.lower()).ratio()

def suggest_column_merges(column_names, threshold=0.8):
    groups = []
    used = set()

    for col in column_names:
        if col in used:
            continue
        group = [col]
        for other in column_names:
            if col != other and similar(col, other) > threshold:
                group.append(other)
                used.add(other)
        used.add(col)
        if len(group) > 1:
            groups.append(group)

    return groups



if __name__ == "__main__":
    folder = "./spreadsheets"
    graph = build_dependency_graph(folder)

    print("Dependencies:")
    for edge in graph.edges(data=True):
        print(edge)

    warnings = detect_stale_dependencies(graph, folder)
    print("\nWarnings:")
    for w in warnings:
        print(w)