from io import BytesIO

from openpyxl import load_workbook

from grantflow.exporters.excel_builder import build_xlsx_from_logframe


def test_excel_export_logframe_sheet_includes_smart_indicator_columns():
    logframe_draft = {
        "indicators": [
            {
                "indicator_id": "IND_001",
                "name": "Service coverage rate",
                "result_level": "outcome",
                "toc_statement_path": "toc.outcomes[0]",
                "justification": "Tracks outcome-level service adoption.",
                "citation": "USAID ADS 201 p.12",
                "baseline": "0%",
                "target": "30%",
                "frequency": "quarterly",
                "formula": "(Numerator / Denominator) * 100",
                "definition": "Share of target beneficiaries receiving digital service.",
                "data_source": "PMP indicator tracking dataset",
                "disaggregation": ["sex", "age", "location"],
                "means_of_verification": "Verified PMP records and spot-check files",
                "owner": "MEL lead and implementing partner M&E team",
                "evidence_excerpt": "Service uptake evidence extracted from validated implementation records.",
            }
        ]
    }
    wb = load_workbook(BytesIO(build_xlsx_from_logframe(logframe_draft, "usaid")))
    ws = wb["LogFrame"]
    rows = list(ws.iter_rows(values_only=True))

    assert rows[0] == (
        "Indicator ID",
        "Name",
        "Result Level",
        "ToC Statement Path",
        "Justification",
        "Citation",
        "Readiness Hint",
        "Result Focus",
        "Measurement Intent",
        "Baseline",
        "Target",
        "Frequency",
        "Formula",
        "Definition",
        "Data Source",
        "Disaggregation",
        "Means of Verification",
        "Owner",
        "Evidence Excerpt",
    )
    assert rows[1][0] == "IND_001"
    assert rows[1][2] == "outcome"
    assert rows[1][3] == "toc.outcomes[0]"
    assert rows[1][6] == "review-ready"
    assert "Share of target beneficiaries receiving digital service." in str(rows[1][7])
    assert "Tracks outcome-level service adoption." in str(rows[1][8])
    assert rows[1][11] == "quarterly"
    assert rows[1][12] == "(Numerator / Denominator) * 100"
    assert rows[1][14] == "PMP indicator tracking dataset"
    assert rows[1][15] == "sex, age, location"
    assert rows[1][16] == "Verified PMP records and spot-check files"
    assert rows[1][17] == "MEL lead and implementing partner M&E team"
    assert "validated implementation records" in str(rows[1][18])


def test_excel_export_includes_template_meta_sheet():
    eu_toc_incomplete = {
        "toc": {
            "overall_objective": {"objective_id": "OO1", "title": "Digital governance", "rationale": "EU fit"},
        }
    }
    content = build_xlsx_from_logframe({"indicators": []}, "eu", toc_draft=eu_toc_incomplete)
    wb = load_workbook(BytesIO(content))
    assert "Template Meta" in wb.sheetnames

    rows = list(wb["Template Meta"].iter_rows(values_only=True))
    row_map = {str(row[0]): row[1] for row in rows[1:] if row and row[0]}
    assert row_map["Template Key"] == "eu"
    assert row_map["Template Display"] == "EU Intervention Logic"
    assert float(row_map["Coverage Rate"]) < 0.34
    assert float(row_map["Coverage Rate"]) > 0.32
    assert "specific_objectives" in str(row_map["Missing Sections"])
    assert "expected_outcomes" in str(row_map["Missing Sections"])
