"""The evidence pack's access-evidence section.

Two things go wrong quietly here. The pack is a ZIP an EXTERNAL auditor
opens in Excel, so a display name arriving from HRIS is untrusted input
on its way to a formula engine. And the section selects which bundles
belong to the pack's month — pick the wrong column and the artefact is
empty while the evidence sits in the table.
"""
from __future__ import annotations

import uuid
from datetime import UTC, datetime

import pytest

from app.domains.auditor.models import AuditorEvidenceBundle
from app.domains.auditor.services import _csv_row

# ── formula injection ─────────────────────────────────────────────────


@pytest.mark.parametrize("payload", [
    "=cmd|'/c calc'!A1",
    "+1+1",
    "-2+3",
    "@SUM(A1:A9)",
    "\tlead-tab",
])
def test_a_formula_in_a_display_name_is_neutralised(payload):
    """Quoting alone does NOT stop this — Excel evaluates a leading
    ``=`` inside a quoted field just the same. Five other exporters in
    this repo already route cells through ``to_cell``; the evidence pack
    was the one that never adopted it."""
    import csv as _csv
    import io as _io

    row = _csv_row(["bundle", payload, 1])
    # Parse the row back the way Excel would, and assert on the CELL —
    # not on the raw text, where quoting makes a weak assertion easy to
    # write by accident.
    cell = next(_csv.reader(_io.StringIO(row)))[1]
    assert not cell.startswith(("=", "+", "-", "@", "\t", "\r")), cell
    assert cell == "'" + payload


def test_ordinary_names_are_not_mangled():
    """The guard must not corrupt the 99.9% case, including the commas
    and quotes the original ``",".join`` fix was written for."""
    row = _csv_row(["b", 'Zhang, "San"', "ok"])
    assert "Zhang" in row and "San" in row
    assert row.startswith("b,")


# ── which bundles belong to the pack's month ──────────────────────────


class _Pack:
    def __init__(self, start: datetime, end: datetime) -> None:
        self.period_start, self.period_end = start, end


async def _bundle(
    db, *, query_key, created_at, period_start=None, period_end=None,
    generated_via="scheduled", params=None,
):
    row = AuditorEvidenceBundle(
        query_key=query_key,
        query_version="v1",
        params=params or {},
        row_count=0,
        content_hash=uuid.uuid4().hex,
        generated_via=generated_via,
        created_at=created_at,
        period_start=period_start,
        period_end=period_end or period_start,
    )
    db.add(row)
    await db.flush()
    return row


@pytest.mark.asyncio
async def test_the_month_a_bundle_covers_beats_the_day_it_was_written(
    db_session,
):
    """The pre-compile runs at 03:20 on the 2nd, so May's evidence is
    created in June.

    Filtering on ``created_at`` therefore excluded every pre-compiled
    bundle for the pack's own month and pulled in the previous month's —
    a May pack rendering "0 frozen evidence bundle(s)" while May's
    evidence sat in the table, which defeats the entire point of
    pre-compiling.
    """
    from app.domains.auditor.services import EvidencePackService

    may = await _bundle(
        db_session,
        query_key="sso_grant_roster",
        created_at=datetime(2026, 6, 2, 3, 20, tzinfo=UTC),
        period_start=datetime(2026, 5, 1, tzinfo=UTC),
    )
    april = await _bundle(
        db_session,
        query_key="sso_grant_roster",
        created_at=datetime(2026, 5, 2, 3, 20, tzinfo=UTC),
        period_start=datetime(2026, 4, 1, tzinfo=UTC),
    )

    section = await EvidencePackService._render_access_evidence(
        db_session,
        pack=_Pack(
            datetime(2026, 5, 1, tzinfo=UTC),
            datetime(2026, 5, 31, 23, 59, 59, tzinfo=UTC),
        ),
    )
    rendered = str(section)
    assert str(may.id)[:8] in rendered
    assert str(april.id)[:8] not in rendered


@pytest.mark.asyncio
async def test_a_wide_manual_footprint_does_not_land_in_every_pack(
    db_session,
):
    """Interval overlap put no upper bound on the BUNDLE's own window.

    One auditor running ``person_footprint`` over 2020-to-now — a named
    individual's complete access history, display names in the clear —
    would then appear in every monthly pack from 2020 onward. "When
    someone went looking" is the right axis for a hand-asked question;
    "what period it covers" is the right axis for routine collection.
    """
    from app.domains.auditor.services import EvidencePackService

    wide = await _bundle(
        db_session,
        query_key="person_footprint",
        created_at=datetime(2026, 8, 1, tzinfo=UTC),
        period_start=datetime(2020, 1, 1, tzinfo=UTC),
        period_end=datetime(2026, 8, 1, tzinfo=UTC),
        generated_via="manual",
        params={"person_id": str(uuid.uuid4())},
    )

    for year, month in ((2021, 3), (2024, 11), (2026, 5)):
        section = await EvidencePackService._render_access_evidence(
            db_session,
            pack=_Pack(
                datetime(year, month, 1, tzinfo=UTC),
                datetime(year, month, 28, 23, 59, 59, tzinfo=UTC),
            ),
        )
        assert str(wide.id)[:8] not in str(section), f"{year}-{month:02d}"

    # It DOES belong to the month it was actually asked in.
    section = await EvidencePackService._render_access_evidence(
        db_session,
        pack=_Pack(
            datetime(2026, 8, 1, tzinfo=UTC),
            datetime(2026, 8, 31, 23, 59, 59, tzinfo=UTC),
        ),
    )
    assert str(wide.id)[:8] in str(section)


@pytest.mark.asyncio
async def test_a_pack_that_does_not_start_on_the_first_still_gets_evidence(
    db_session,
):
    """A lower bound on the pack's own start demanded month alignment.

    The pack range is a free datetime pair from a bare RangePicker, so
    "the last 90 days", a fiscal quarter, or a mis-click on the 2nd are
    all normal. Requiring ``period_start >= pack.period_start`` made
    every one of them render zero scheduled evidence — the same empty
    section this predicate exists to cure. Nothing needed that bound:
    a scheduled window IS one calendar month, so the upper comparison
    already keeps April out of a May pack.
    """
    from app.domains.auditor.services import EvidencePackService

    may = await _bundle(
        db_session,
        query_key="sso_grant_roster",
        created_at=datetime(2026, 6, 2, 3, 20, tzinfo=UTC),
        period_start=datetime(2026, 5, 1, tzinfo=UTC),
        period_end=datetime(2026, 5, 31, tzinfo=UTC),
    )
    april = await _bundle(
        db_session,
        query_key="sso_grant_roster",
        created_at=datetime(2026, 5, 2, 3, 20, tzinfo=UTC),
        period_start=datetime(2026, 4, 1, tzinfo=UTC),
        period_end=datetime(2026, 4, 30, tzinfo=UTC),
    )

    for start, end in (
        (datetime(2026, 5, 2, tzinfo=UTC), datetime(2026, 5, 31, tzinfo=UTC)),
        (datetime(2026, 5, 15, tzinfo=UTC), datetime(2026, 6, 15, tzinfo=UTC)),
    ):
        section = str(await EvidencePackService._render_access_evidence(
            db_session, pack=_Pack(start, end),
        ))
        assert str(may.id)[:8] in section, f"{start:%Y-%m-%d}..{end:%Y-%m-%d}"
        assert str(april.id)[:8] not in section


@pytest.mark.asyncio
async def test_month_end_point_in_time_evidence_survives_a_cst_date_picker(
    db_session,
):
    """A UTC+8 auditor's "May", as the picker now serialises it.

    ``toISOString()`` on a raw RangePicker value sent
    04-30T16:00Z..05-30T16:00Z, so a point-in-time bundle stamped
    05-31T00:00Z fell outside the pack entirely — silently dropping the
    month-end privileged-role evidence CC6.1/CC6.2 rest on, for every
    auditor in Shanghai. The picker now sends whole days
    (``startOf('day')`` / ``endOf('day')``), which in CST is
    04-30T16:00Z..05-31T15:59:59Z.

    Fixing it in the SELECT instead — padding the window by a day —
    was the wrong layer: it pulled both neighbouring months in, since
    their period bounds land exactly on the padded edges.
    """
    from app.domains.auditor.services import EvidencePackService

    anchor = await _bundle(
        db_session,
        query_key="privileged_role_roster",
        created_at=datetime(2026, 6, 2, 3, 20, tzinfo=UTC),
        period_start=datetime(2026, 5, 31, tzinfo=UTC),
        period_end=datetime(2026, 5, 31, tzinfo=UTC),
    )

    section = str(await EvidencePackService._render_access_evidence(
        db_session,
        pack=_Pack(
            datetime(2026, 4, 30, 16, 0, tzinfo=UTC),
            datetime(2026, 5, 31, 15, 59, 59, tzinfo=UTC),
        ),
    ))
    assert str(anchor.id)[:8] in section


@pytest.mark.asyncio
async def test_a_scheduled_bundle_with_no_period_is_not_lost(db_session):
    """The quadrant that fell through both branches.

    One arm required a period, the other required a non-scheduled
    source, so a scheduled bundle with neither belonged to no pack at
    all — invisible rather than misplaced.
    """
    from app.domains.auditor.services import EvidencePackService

    orphan = await _bundle(
        db_session,
        query_key="approval_chain",
        created_at=datetime(2026, 5, 10, tzinfo=UTC),
        period_start=None,
    )

    section = str(await EvidencePackService._render_access_evidence(
        db_session,
        pack=_Pack(
            datetime(2026, 5, 1, tzinfo=UTC),
            datetime(2026, 5, 31, 23, 59, 59, tzinfo=UTC),
        ),
    ))
    assert str(orphan.id)[:8] in section


@pytest.mark.asyncio
async def test_a_canonical_month_pack_excludes_both_neighbours(db_session):
    """The shape every monthly pack has, and the one the padding broke.

    Widening the window by a day to rescue a UTC+8 picker put April's
    period_end and June's period_start each exactly on a padded edge, so
    a May pack shipped three months of evidence with two thirds of it
    wrong. The earlier tests used ranges starting on the 2nd and the
    15th — the only shapes where the leak does NOT fire.
    """
    from app.domains.auditor.services import EvidencePackService

    april = await _bundle(
        db_session, query_key="sso_grant_roster",
        created_at=datetime(2026, 5, 2, tzinfo=UTC),
        period_start=datetime(2026, 4, 1, tzinfo=UTC),
        period_end=datetime(2026, 4, 30, tzinfo=UTC),
    )
    may = await _bundle(
        db_session, query_key="sso_grant_roster",
        created_at=datetime(2026, 6, 2, tzinfo=UTC),
        period_start=datetime(2026, 5, 1, tzinfo=UTC),
        period_end=datetime(2026, 5, 31, tzinfo=UTC),
    )
    june = await _bundle(
        db_session, query_key="sso_grant_roster",
        created_at=datetime(2026, 7, 2, tzinfo=UTC),
        period_start=datetime(2026, 6, 1, tzinfo=UTC),
        period_end=datetime(2026, 6, 30, tzinfo=UTC),
    )

    section = str(await EvidencePackService._render_access_evidence(
        db_session,
        pack=_Pack(
            datetime(2026, 5, 1, tzinfo=UTC),
            datetime(2026, 5, 31, 23, 59, 59, tzinfo=UTC),
        ),
    ))
    assert str(may.id)[:8] in section
    assert str(april.id)[:8] not in section, "April leaked into a May pack"
    assert str(june.id)[:8] not in section, "June leaked into a May pack"