from io import BytesIO

from openpyxl import load_workbook

from grantflow.exporters.excel_builder import build_xlsx_from_logframe
from grantflow.tests.exporters_helpers import (
    _sample_citations,
    _sample_critic_findings,
    _sample_quality_summary,
    _sample_review_comments,
)


def test_excel_export_includes_citations_sheet():
    logframe_draft = {
        "indicators": [
            {
                "indicator_id": "IND_001",
                "name": "Indicator A",
                "justification": "Test",
                "citation": "USAID ADS 201 p.12",
                "baseline": "0",
                "target": "100",
            }
        ]
    }
    content = build_xlsx_from_logframe(
        logframe_draft,
        "usaid",
        citations=_sample_citations(),
        critic_findings=_sample_critic_findings(),
        review_comments=_sample_review_comments(),
        quality_summary=_sample_quality_summary(),
    )
    wb = load_workbook(BytesIO(content))
    assert "LogFrame" in wb.sheetnames
    assert "Quality Summary" in wb.sheetnames
    assert "Review Readiness" in wb.sheetnames
    assert "Citations" in wb.sheetnames
    assert "Critic Findings" in wb.sheetnames
    assert "Review Comments" in wb.sheetnames

    ws = wb["Citations"]
    rows = list(ws.iter_rows(values_only=True))
    assert rows[0][:7] == ("Stage", "Type", "Used For", "Label", "Confidence", "Result Level", "Statement Path")
    headers = [str(cell or "") for cell in rows[0]]
    assert "Evidence Signal" in headers
    assert "Review Hint" in headers
    assert any(row[3] == "USAID ADS 201 p.12" for row in rows[1:])
    assert any(abs(float(row[4]) - 0.82) < 1e-9 for row in rows[1:] if row[4] is not None)
    assert any(row[5] == "outcome" for row in rows[1:])
    assert any(row[6] == "toc.development_objectives[0].description" for row in rows[1:])
    evidence_idx = headers.index("Evidence Signal")
    review_hint_idx = headers.index("Review Hint")
    assert any(row[evidence_idx] == "PMP and indicator reference evidence" for row in rows[1:])
    assert any(
        row[review_hint_idx] == "Use this citation to support the indicator row in the USAID monitoring package."
        for row in rows[1:]
    )

    findings_rows = list(wb["Critic Findings"].iter_rows(values_only=True))
    assert findings_rows[0][:4] == ("Status", "Severity", "Section", "Code")
    assert any(row[3] == "TOC_SCHEMA_INVALID" for row in findings_rows[1:])
    findings_headers = [str(cell or "") for cell in findings_rows[0]]
    assert "Review Title" in findings_headers
    assert "Recommended Action" in findings_headers
    assert "Reviewer Next Step" in findings_headers

    comments_rows = list(wb["Review Comments"].iter_rows(values_only=True))
    assert comments_rows[0][:4] == ("Status", "Section", "Author", "Message")
    assert any(row[6] == "2026-02-25T10:00:00Z" for row in comments_rows[1:])

    quality_rows = list(wb["Quality Summary"].iter_rows(values_only=True))
    quality_map = {str(row[0]): row[1] for row in quality_rows[1:] if row and row[0]}
    assert quality_map["Quality score"] == 8.5
    assert quality_map["Critic engine"] == "rules+llm"
    assert quality_map["Fatal flaw count"] == 1

    readiness_rows = list(wb["Review Readiness"].iter_rows(values_only=True))
    readiness_map = {str(row[0]): row[1] for row in readiness_rows[1:] if row and row[0]}
    assert readiness_map["Open critic findings"] == 1
    assert readiness_map["Acknowledged critic findings"] == 1
    assert readiness_map["Resolved critic findings"] == 0
    assert readiness_map["High-severity open findings"] == 1
    assert readiness_map["Open review comments"] == 0
    assert readiness_map["Acknowledged review comments"] == 0
    assert readiness_map["Resolved review comments"] == 1
    assert readiness_map["Fallback/strategy citations"] == 1
    assert readiness_map["Next primary review action"] == "resolve_finding"
    assert readiness_map["Finding ack queue"] == 0
    assert readiness_map["Finding resolve queue"] == 1
    assert readiness_map["Comment ack queue"] == 0
    assert readiness_map["Comment resolve queue"] == 0
    assert readiness_map["Comment reopen queue"] == 1
    assert "Critic finding resolution rate" in readiness_map
    assert "Critic finding acknowledgment rate" in readiness_map
    assert "Reviewer workflow resolution rate" in readiness_map
    assert "Reviewer workflow acknowledgment rate" in readiness_map
    assert "Comment threads aged 3-7d" in readiness_map
    assert "Top reviewer action 1" in readiness_map


def test_excel_export_includes_export_contract_sheet():
    usaid_toc = {
        "toc": {
            "project_goal": "Improve civic services",
            "development_objectives": [
                {
                    "do_id": "DO1",
                    "description": "Improved digital delivery",
                    "intermediate_results": [],
                }
            ],
        }
    }
    content = build_xlsx_from_logframe({"indicators": []}, "usaid", toc_draft=usaid_toc)
    wb = load_workbook(BytesIO(content))
    assert "Export Contract" in wb.sheetnames
    rows = list(wb["Export Contract"].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["Status"] == "pass"
    assert row_map["Missing Required ToC Sections"] == "-"
    assert "LogFrame" in str(row_map["Required Workbook Sheets"])
    assert "USAID_RF" in str(row_map["Required Workbook Sheets"])
    assert row_map["Expected Primary Sheet"] == "USAID_RF"
    assert "DO ID" in str(row_map["Expected Primary Sheet Headers"])
    assert "IR Description" in str(row_map["Actual Primary Sheet Headers"])
    assert row_map["Missing Required Primary Sheet Headers"] == "-"
