from io import BytesIO

from openpyxl import load_workbook

from grantflow.exporters.excel_builder import build_xlsx_from_logframe


def test_excel_donor_sheets_include_suggested_monitoring_focus_columns():
    donor_logframe = {
        "indicators": [
            {
                "indicator_id": "IND_001",
                "name": "Institutional service adoption score",
                "result_level": "outcome",
                "means_of_verification": "Verification annexes and partner review notes",
                "owner": "Project M&E manager",
            },
            {
                "indicator_id": "IND_002",
                "name": "Civil service modernization learning uptake",
                "result_level": "impact",
                "means_of_verification": "CLA evidence and review memos",
                "owner": "MEL lead",
            },
        ]
    }
    usaid_toc = {
        "toc": {
            "project_goal": "Improve civic services",
            "development_objectives": [
                {
                    "do_id": "DO1",
                    "description": "Improved digital delivery",
                    "intermediate_results": [
                        {
                            "ir_id": "IR1.1",
                            "description": "Capacity strengthened",
                            "outputs": [
                                {
                                    "output_id": "O1.1.1",
                                    "description": "Training delivered",
                                    "indicators": [],
                                }
                            ],
                        }
                    ],
                }
            ],
        }
    }
    eu_toc = {
        "toc": {
            "overall_objective": {"objective_id": "OO1", "title": "Digital governance", "rationale": "EU fit"},
            "specific_objectives": [
                {"objective_id": "SO1", "title": "Service quality", "rationale": "Improve delivery standards"}
            ],
            "expected_outcomes": [
                {"outcome_id": "OUT1", "title": "Citizen trust", "expected_change": "Higher service satisfaction"}
            ],
        }
    }

    usaid_wb = load_workbook(BytesIO(build_xlsx_from_logframe(donor_logframe, "usaid", toc_draft=usaid_toc)))
    eu_wb = load_workbook(BytesIO(build_xlsx_from_logframe(donor_logframe, "eu", toc_draft=eu_toc)))

    usaid_rows = list(usaid_wb["USAID_RF"].iter_rows(values_only=True))
    eu_rows = list(eu_wb["EU_Intervention"].iter_rows(values_only=True))

    assert "Suggested Monitoring Focus" in usaid_rows[0]
    assert "Suggested Means of Verification" in usaid_rows[0]
    assert "Suggested Owner" in usaid_rows[0]
    assert "Suggested Result Focus" in usaid_rows[0]
    assert "Suggested Measurement Intent" in usaid_rows[0]
    assert "Institutional service adoption score" in str(usaid_rows[1])
    assert "Project M&E manager" in str(usaid_rows[1])

    assert "Suggested Monitoring Focus" in eu_rows[0]
    assert "Suggested Means of Verification" in eu_rows[0]
    assert "Suggested Owner" in eu_rows[0]
    assert "Suggested Result Focus" in eu_rows[0]
    assert "Suggested Measurement Intent" in eu_rows[0]
    assert "Institutional service adoption score" in str(eu_rows[1])
    assert "Verification annexes and partner review notes" in str(eu_rows[1])


def test_excel_export_includes_donor_specific_sheets():
    usaid_toc = {
        "toc": {
            "development_objectives": [
                {
                    "do_id": "DO1",
                    "description": "Improved digital delivery",
                    "intermediate_results": [
                        {
                            "ir_id": "IR1.1",
                            "description": "Civil service capacity strengthened",
                            "outputs": [
                                {
                                    "output_id": "O1.1.1",
                                    "description": "Training delivered",
                                    "indicators": [
                                        {
                                            "indicator_code": "EG.1-1",
                                            "name": "Officials trained",
                                            "target": "300",
                                            "justification": "Capacity milestone",
                                            "citation": "USAID ADS 201 p.12",
                                        }
                                    ],
                                }
                            ],
                        }
                    ],
                }
            ]
        }
    }
    eu_toc = {
        "toc": {
            "overall_objective": {"objective_id": "OO1", "title": "Digital governance", "rationale": "EU fit"},
            "specific_objectives": [
                {"objective_id": "SO1", "title": "Service quality", "rationale": "Improve delivery standards"}
            ],
            "expected_outcomes": [
                {"outcome_id": "OUT1", "title": "Citizen trust", "expected_change": "Higher service satisfaction"}
            ],
            "assumptions": ["Policy continuity"],
            "risks": ["Election-year disruption"],
        }
    }
    wb_toc = {
        "toc": {
            "project_development_objective": "Improve municipal service delivery",
            "objectives": [
                {"objective_id": "PDO1", "title": "Service delivery quality", "description": "Better outcomes"}
            ],
            "results_chain": [
                {
                    "result_id": "R1",
                    "title": "Digital workflows adopted",
                    "description": "Local agencies use digital case-management",
                    "indicator_focus": "Processing time",
                }
            ],
        }
    }
    giz_toc = {
        "toc": {
            "programme_objective": "Strengthen SME resilience",
            "outputs": ["Coaching delivered"],
            "outcomes": [
                {
                    "title": "SMEs improve recovery planning",
                    "description": "Participating SMEs institutionalize continuity plans",
                    "partner_role": "Chamber of Commerce",
                }
            ],
            "sustainability_factors": ["Partner co-financing"],
            "assumptions_risks": ["Macroeconomic volatility"],
        }
    }
    state_toc = {
        "toc": {
            "strategic_context": "Independent media under pressure",
            "program_goal": "Improve media ecosystem resilience",
            "objectives": [
                {
                    "objective": "Increase legal preparedness",
                    "line_of_effort": "rights",
                    "expected_change": "Media organizations handle pressure better",
                }
            ],
            "stakeholder_map": ["Local media outlets"],
            "risk_mitigation": ["Anonymous reporting channels"],
        }
    }

    usaid_wb = load_workbook(BytesIO(build_xlsx_from_logframe({"indicators": []}, "usaid", toc_draft=usaid_toc)))
    eu_wb = load_workbook(BytesIO(build_xlsx_from_logframe({"indicators": []}, "eu", toc_draft=eu_toc)))
    donor_focus_logframe = {
        "indicators": [
            {
                "indicator_id": "IND_WB_1",
                "name": "Agency performance monitoring score",
                "result_level": "outcome",
                "baseline": "0 agencies",
                "target": "8 agencies",
                "frequency": "semi-annual",
                "formula": "Count of agencies meeting performance score threshold",
                "means_of_verification": "ISR aide-memoires and results framework updates",
                "owner": "PIU results lead",
            },
            {
                "indicator_id": "IND_STATE_1",
                "name": "Media resilience score",
                "result_level": "impact",
                "baseline": "0 organizations",
                "target": "6 organizations",
                "frequency": "annual",
                "means_of_verification": "Editorial risk logs and resilience reviews",
                "owner": "Program manager and media partner leads",
            },
        ]
    }

    wb_wb = load_workbook(BytesIO(build_xlsx_from_logframe(donor_focus_logframe, "worldbank", toc_draft=wb_toc)))
    giz_focus_logframe = {
        "indicators": [
            {
                "indicator_id": "IND_GIZ_1",
                "name": "SME continuity adoption score",
                "result_level": "outcome",
                "baseline": "0 SMEs",
                "target": "40 SMEs",
                "frequency": "semiannual",
                "formula": "Count of SMEs meeting continuity adoption criteria",
                "means_of_verification": "Partner coaching records and SME verification visits",
                "owner": "Programme M&E lead and chamber focal points",
            },
            {
                "indicator_id": "IND_GIZ_2",
                "name": "Coaching completion rate",
                "result_level": "output",
                "baseline": "0 firms",
                "target": "60 firms",
                "frequency": "quarterly",
                "formula": "Count of firms completing coaching package",
                "means_of_verification": "Attendance registers and delivery logs",
                "owner": "Technical assistance team lead",
            },
        ]
    }
    un_toc = {
        "toc": {
            "brief": "Inclusive education access remains uneven across target districts.",
            "objectives": [
                {
                    "title": "Improve inclusive education system readiness",
                    "description": "District education actors adopt inclusive planning and referral practices.",
                }
            ],
            "outcomes": [
                {
                    "title": "More children with disabilities access adapted services",
                    "description": "Referral and classroom support pathways operate consistently.",
                }
            ],
        }
    }
    un_focus_logframe = {
        "indicators": [
            {
                "indicator_id": "IND_UN_1",
                "name": "Inclusive service readiness score",
                "result_level": "outcome",
                "baseline": "0 districts",
                "target": "12 districts",
                "frequency": "annual",
                "formula": "Count of districts meeting inclusive service readiness criteria",
                "means_of_verification": "Partner monitoring records and school support checklists",
                "owner": "Programme manager and inclusion focal points",
            }
        ]
    }
    giz_wb = load_workbook(BytesIO(build_xlsx_from_logframe(giz_focus_logframe, "giz", toc_draft=giz_toc)))
    un_wb = load_workbook(BytesIO(build_xlsx_from_logframe(un_focus_logframe, "un_agencies", toc_draft=un_toc)))
    state_wb = load_workbook(
        BytesIO(build_xlsx_from_logframe(donor_focus_logframe, "state_department", toc_draft=state_toc))
    )

    assert "USAID_RF" in usaid_wb.sheetnames
    assert "EU_Intervention" in eu_wb.sheetnames
    assert "EU_Assumptions_Risks" in eu_wb.sheetnames
    assert "WB_Results" in wb_wb.sheetnames
    assert "GIZ_Results" in giz_wb.sheetnames
    assert "UN_Results" in un_wb.sheetnames
    assert "StateDept_Results" in state_wb.sheetnames

    usaid_rows = list(usaid_wb["USAID_RF"].iter_rows(values_only=True))
    assert usaid_rows[0][0] == "DO ID"
    assert any(row[0] == "DO1" and row[6] == "EG.1-1" for row in usaid_rows[1:])

    eu_rows = list(eu_wb["EU_Intervention"].iter_rows(values_only=True))
    assert eu_rows[0][:2] == ("Level", "ID")
    assert any(row[0] == "Specific Objective" and row[1] == "SO1" for row in eu_rows[1:])
    assert any(row[0] == "Outcome" and row[1] == "OUT1" for row in eu_rows[1:])
    eu_aux_rows = list(eu_wb["EU_Assumptions_Risks"].iter_rows(values_only=True))
    assert any(row[0] == "Assumption" for row in eu_aux_rows[1:])
    assert any(row[0] == "Risk" for row in eu_aux_rows[1:])

    wb_rows = list(wb_wb["WB_Results"].iter_rows(values_only=True))
    assert wb_rows[0][0] == "Level"
    assert any(row[0] == "PDO" for row in wb_rows[1:])
    assert any(row[0] == "Result" and row[1] == "R1" for row in wb_rows[1:])
    assert "Suggested Monitoring Focus" in wb_rows[0]
    assert "Suggested Means of Verification" in wb_rows[0]
    assert "Suggested Owner" in wb_rows[0]
    assert "Suggested Baseline -> Target" in wb_rows[0]
    assert "Suggested Frequency" in wb_rows[0]
    assert "Suggested Formula" in wb_rows[0]
    assert any("ISR aide-memoires and results framework updates" in str(row) for row in wb_rows[1:])
    assert any("0 agencies -> 8 agencies" in str(row) for row in wb_rows[1:])

    giz_rows = list(giz_wb["GIZ_Results"].iter_rows(values_only=True))
    assert any(row[0] == "Programme Objective" for row in giz_rows[1:])
    assert any(row[0] == "Outcome" for row in giz_rows[1:])
    assert "Suggested Monitoring Focus" in giz_rows[0]
    assert "Suggested Means of Verification" in giz_rows[0]
    assert "Suggested Owner" in giz_rows[0]
    assert "Suggested Baseline -> Target" in giz_rows[0]
    assert "Suggested Frequency" in giz_rows[0]
    assert "Suggested Formula" in giz_rows[0]
    assert "Suggested Result Focus" in giz_rows[0]
    assert "Suggested Measurement Intent" in giz_rows[0]
    assert any("Attendance registers and delivery logs" in str(row) for row in giz_rows[1:])
    assert any("0 firms -> 60 firms" in str(row) for row in giz_rows[1:])

    un_rows = list(un_wb["UN_Results"].iter_rows(values_only=True))
    assert un_rows[0][:4] == ("Level", "Title", "Description", "Suggested Monitoring Focus")
    assert "Suggested Baseline -> Target" in un_rows[0]
    assert "Suggested Frequency" in un_rows[0]
    assert "Suggested Formula" in un_rows[0]
    assert "Suggested Result Focus" in un_rows[0]
    assert "Suggested Measurement Intent" in un_rows[0]
    assert any(row[0] == "Overview" for row in un_rows[1:])
    assert any(
        row[0] == "Objective" and row[1] == "Improve inclusive education system readiness" for row in un_rows[1:]
    )
    assert any("Partner monitoring records and school support checklists" in str(row) for row in un_rows[1:])
    assert any("0 districts -> 12 districts" in str(row) for row in un_rows[1:])

    state_rows = list(state_wb["StateDept_Results"].iter_rows(values_only=True))
    assert any(row[0] == "Strategic Context" for row in state_rows[1:])
    assert any(row[0] == "Objective" for row in state_rows[1:])
    assert "Suggested Monitoring Focus" in state_rows[0]
    assert "Suggested Means of Verification" in state_rows[0]
    assert "Suggested Owner" in state_rows[0]
    assert "Suggested Result Focus" in state_rows[0]
    assert "Suggested Measurement Intent" in state_rows[0]
    assert any("Editorial risk logs and resilience reviews" in str(row) for row in state_rows[1:])
