"""Import command for the official RUFORUM MEL Framework workbook.

Builds a synthetic workbook in the same shape as ``RUFORUM MEL FRAMEWORK.xlsm``
(visible sheet ``RUFORUM MEL FRAME``, merged-style hierarchy columns left blank
on continuation rows) and drives ``import_ruforum_mel_framework`` against it.

Do NOT run under a parallel pytest — the test_iilmp DB collides.
"""
from __future__ import annotations

import pytest
from django.core.management import call_command

from apps.mel.indicators.models import (
    Indicator,
    IndicatorState,
    LogFrame,
    LogFrameLevel,
    LogFrameRow,
)

pytestmark = pytest.mark.django_db

HEADER = [
    "No",
    "AHESTI 10-Year Objective (2018-2030)",
    "AHESTI 10-Year Outcome (2018-2030)",
    "AHESTI Intermediate Outcome (2024-2028)",
    "RUFORUM High-Level Output",
    "Indicator",
    "Disaggregation",
]

ROWS = [
    ["", "Objective A", "Outcome A", "Intermediate A", "1. Output One",
     "Share of graduates employed", "sex, job type, age"],
    ["", "", "", "", "",
     "Employer satisfaction", "sex, business type (SME, corporate)"],
    ["", "", "", "", "2. Output Two",
     "Policies revised", "Type of policies, level (institutional, national)"],
    ["", "Objective B", "Outcome B", "Intermediate B", "3. Output Three",
     "Resources mobilised", "Types of resource, year"],
]


@pytest.fixture
def workbook(tmp_path):
    openpyxl = pytest.importorskip("openpyxl")
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "RUFORUM MEL FRAME"
    ws.append(HEADER)
    for row in ROWS:
        ws.append(row)
    path = tmp_path / "framework.xlsx"
    wb.save(path)
    return str(path)


def test_import_builds_one_framework_per_objective(workbook):
    call_command("import_ruforum_mel_framework", workbook, slug="test-ruf-fw")

    # Two objectives → two Strategic Objectives (each its own LogFrame).
    lf_a = LogFrame.objects.get(slug="test-ruf-fw-1")
    lf_b = LogFrame.objects.get(slug="test-ruf-fw-2")
    assert lf_a.name == "Objective A"
    assert lf_b.name == "Objective B"

    def counts(lf):
        return {
            level: lf.rows.filter(level=level).count()
            for level in (
                LogFrameLevel.IMPACT,
                LogFrameLevel.OUTCOME,
                LogFrameLevel.OUTPUT,
            )
        }

    assert counts(lf_a) == {
        LogFrameLevel.IMPACT: 1,
        LogFrameLevel.OUTCOME: 1,
        LogFrameLevel.OUTPUT: 2,  # Output One + Output Two
    }
    assert counts(lf_b) == {
        LogFrameLevel.IMPACT: 1,
        LogFrameLevel.OUTCOME: 1,
        LogFrameLevel.OUTPUT: 1,
    }
    # No Strategic-Objective rows exist any more — the SO is the framework itself.
    assert not LogFrameRow.objects.filter(level="strategic_objective").exists()

    # Forward-fill: the second indicator hangs off Output One like the first.
    out_one = lf_a.rows.get(title="1. Output One")
    assert out_one.indicators.count() == 2
    # Parent chain intact — Impact is the root (no parent row).
    assert out_one.parent.level == LogFrameLevel.OUTCOME
    assert out_one.parent.parent.level == LogFrameLevel.IMPACT
    assert out_one.parent.parent.parent is None

    ind = Indicator.objects.get(name="Employer satisfaction")
    assert ind.workflow_status == IndicatorState.DRAFT
    # Paren-aware category split: "(SME, corporate)" stays one category.
    names = set(ind.disaggregation_categories.values_list("name", flat=True))
    assert names == {"Sex", "Business type (SME, corporate)"}


def test_import_is_idempotent(workbook):
    def snapshot():
        return (
            LogFrameRow.objects.filter(
                logframe__slug__startswith="test-ruf-idem"
            ).count(),
            Indicator.objects.filter(
                logframe_row__logframe__slug__startswith="test-ruf-idem"
            ).count(),
        )

    call_command("import_ruforum_mel_framework", workbook, slug="test-ruf-idem")
    before = snapshot()
    call_command("import_ruforum_mel_framework", workbook, slug="test-ruf-idem")
    after = snapshot()
    assert before == after