"""
INDL-60 — Reports & Analytics: corrected columns, real section revenue,
multi-table exports, and the Audit Activity Log UI removal.

Extends test_reports.py's fixtures/style. Each test seeds its own minimal,
purpose-built domain data (rather than reusing the shared `_seed_domain`
fixture) so assertions on exact aggregated values aren't polluted by
unrelated seeded rows.

Maps to the PRD's Test Cases T-01..T-12 (docs/60_INDL-60_Reports_and_Analytics.md)
plus two extra regression checks (tenant isolation, agent-identity grouping)
called out by the tester brief.
"""
import io
from datetime import date, datetime, timedelta, timezone

import pytest
from sqlalchemy.ext.asyncio import AsyncSession

from src.apps.billing.models.invoice import Invoice
from src.apps.billing.models.invoice_payment import InvoicePayment
from src.apps.crew_members.models.crew_member import CrewMember
from src.apps.memorials.models.memorial import Memorial
from src.apps.plots.models.plot import Plot
from src.apps.plots.models.plot_type import PlotType
from src.apps.records.models.burial_info import BurialInfo
from src.apps.records.models.record import Record
from src.apps.sales.models.contract import Contract
from src.apps.scheduling.models.service_crew_assignment import ServiceCrewAssignment
from src.apps.scheduling.models.service_event import ServiceEvent
from src.apps.sections.models.section import Section

from tests.apps.reports.test_reports import _headers, _make_account, _make_user

pytestmark = pytest.mark.asyncio


def _today() -> date:
    return datetime.now(timezone.utc).date()


# --------------------------------------------------------------------------- #
# T-01 / T-02 — Monthly Sales Summary multi-table structure
# --------------------------------------------------------------------------- #
async def test_monthly_sales_multi_table_structure(client, db_session: AsyncSession):
    account = await _make_account(db_session, "salesreport")
    manager = await _make_user(db_session, account, "manager")
    agent1 = await _make_user(db_session, account, "administrator")  # "Administrator User"
    agent2 = await _make_user(db_session, account, "staff")  # "Staff User"

    section = Section(tenant_id=account.id, code="A", name="Section A")
    db_session.add(section)
    await db_session.flush()

    ptype_single = PlotType(
        tenant_id=account.id, name="Single", default_price=1000, default_gap_m=0.3
    )
    ptype_double = PlotType(
        tenant_id=account.id, name="Double", default_price=2000, default_gap_m=0.5
    )
    db_session.add_all([ptype_single, ptype_double])
    await db_session.flush()

    now = datetime.now(timezone.utc)
    plot_single = Plot(
        tenant_id=account.id, plot_ref="A-001", section_id=section.id,
        plot_type_id=ptype_single.id, status="occupied", reserved_at=now,
    )
    plot_double = Plot(
        tenant_id=account.id, plot_ref="A-002", section_id=section.id,
        plot_type_id=ptype_double.id, status="reserved", reserved_at=now,
    )
    db_session.add_all([plot_single, plot_double])
    await db_session.flush()

    contract1 = Contract(
        tenant_id=account.id, contract_number="C-AGENT-1", status="signed",
        total_amount=1000, created_by=agent1.id, plot_id=plot_single.id,
    )
    contract2 = Contract(
        tenant_id=account.id, contract_number="C-AGENT-2", status="signed",
        total_amount=2000, created_by=agent2.id, plot_id=plot_double.id,
    )
    db_session.add_all([contract1, contract2])
    await db_session.flush()

    invoice1 = Invoice(
        tenant_id=account.id, contract_id=contract1.id, invoice_number="INV-A1",
        status="paid", total_amount=1000, paid_amount=1000, balance_due=0,
        purchaser_name="Buyer One",
    )
    invoice2 = Invoice(
        tenant_id=account.id, contract_id=contract2.id, invoice_number="INV-A2",
        status="paid", total_amount=2000, paid_amount=2000, balance_due=0,
        purchaser_name="Buyer Two",
    )
    db_session.add_all([invoice1, invoice2])
    await db_session.flush()

    payment1 = InvoicePayment(
        tenant_id=account.id, invoice_id=invoice1.id, amount=1000,
        method="credit_card", received_on=_today(),
    )
    payment2 = InvoicePayment(
        tenant_id=account.id, invoice_id=invoice2.id, amount=2000,
        method="cash", received_on=_today(),
    )
    db_session.add_all([payment1, payment2])
    await db_session.flush()

    resp = await client.get(
        "/api/v1/reports/monthly-sales", headers=_headers(manager, account)
    )
    assert resp.status_code == 200, resp.text
    data = resp.json()["data"]

    # Primary monthly table still present and correct.
    assert data["columns"] == ["Month", "Invoices", "Total Invoiced", "Paid", "Balance Due"]
    assert len(data["rows"]) == 1
    month_row = data["rows"][0]
    assert month_row[1] == 2  # invoice count
    assert month_row[2] == 3000.0  # total invoiced
    assert month_row[3] == 3000.0  # paid
    assert month_row[4] == 0.0  # balance due

    tables = data["tables"]
    assert len(tables) == 3
    titles = [t["title"] for t in tables]
    assert titles == ["Revenue by Plot Type", "Agent Performance", "Payment Method Breakdown"]

    # Revenue by Plot Type
    pt_table = tables[0]
    assert pt_table["columns"] == ["Plot Type", "Plots Sold", "Revenue"]
    pt_by_name = {r[0]: r for r in pt_table["rows"]}
    assert pt_by_name["Single"][1:] == [1, 1000.0]
    assert pt_by_name["Double"][1:] == [1, 2000.0]

    # Agent Performance
    agent_table = tables[1]
    assert agent_table["columns"] == ["Agent", "Contracts", "Total Revenue", "Plots Sold"]
    agent_by_name = {r[0]: r for r in agent_table["rows"]}
    assert agent_by_name["Administrator User"][1:] == [1, 1000.0, 1]
    assert agent_by_name["Staff User"][1:] == [1, 2000.0, 1]

    # Payment Method Breakdown
    pm_table = tables[2]
    assert pm_table["columns"] == ["Payment Method", "Payments", "Amount Collected"]
    pm_by_method = {r[0]: r for r in pm_table["rows"]}
    assert pm_by_method["cash"][1:] == [1, 2000.0]
    assert pm_by_method["credit_card"][1:] == [1, 1000.0]


async def test_monthly_sales_excel_export_has_four_sheets(client, db_session: AsyncSession):
    """T-02 — Excel export contains one sheet per table (4 total)."""
    import openpyxl

    account = await _make_account(db_session, "salesxlsx")
    manager = await _make_user(db_session, account, "manager")
    ptype = PlotType(tenant_id=account.id, name="Single", default_price=1000, default_gap_m=0.3)
    db_session.add(ptype)
    await db_session.flush()
    plot = Plot(
        tenant_id=account.id, plot_ref="X-001", plot_type_id=ptype.id,
        status="occupied", reserved_at=datetime.now(timezone.utc),
    )
    db_session.add(plot)
    await db_session.flush()
    invoice = Invoice(
        tenant_id=account.id, invoice_number="INV-X1", status="paid",
        total_amount=500, paid_amount=500, balance_due=0, purchaser_name="Buyer X",
    )
    db_session.add(invoice)
    await db_session.flush()

    resp = await client.get(
        "/api/v1/reports/monthly-sales/export/excel", headers=_headers(manager, account)
    )
    assert resp.status_code == 200
    wb = openpyxl.load_workbook(io.BytesIO(resp.content))
    assert len(wb.sheetnames) == 4
    assert wb.sheetnames[1:] == ["Revenue by Plot Type", "Agent Performance", "Payment Method Breakdown"]


# --------------------------------------------------------------------------- #
# T-03 — Burial Register new columns (Gender, Interment Time)
# --------------------------------------------------------------------------- #
async def test_burial_register_gender_and_interment_time(client, db_session: AsyncSession):
    account = await _make_account(db_session, "burialreg")
    staff = await _make_user(db_session, account, "staff")

    section = Section(tenant_id=account.id, code="B", name="Section B")
    db_session.add(section)
    await db_session.flush()

    ptype = PlotType(tenant_id=account.id, name="Single", default_price=1000, default_gap_m=0.3)
    db_session.add(ptype)
    await db_session.flush()

    plot = Plot(
        tenant_id=account.id, plot_ref="B-001", section_id=section.id,
        plot_type_id=ptype.id, status="occupied",
    )
    db_session.add(plot)
    await db_session.flush()

    record = Record(
        tenant_id=account.id, plot_id=plot.id, first_name="Patricia",
        last_name="OBrien", date_of_birth=date(1940, 1, 1),
        date_of_death=date(2024, 6, 1), gender="male", status="active",
    )
    db_session.add(record)
    await db_session.flush()

    burial = BurialInfo(
        tenant_id=account.id, record_id=record.id,
        interment_date=date(2024, 6, 10), interment_type="Burial",
        officiant="Rev. Smith", interment_time=datetime.strptime("14:30", "%H:%M").time(),
    )
    db_session.add(burial)
    await db_session.flush()

    resp = await client.get(
        "/api/v1/reports/burial-register", headers=_headers(staff, account)
    )
    assert resp.status_code == 200, resp.text
    data = resp.json()["data"]

    assert data["columns"] == [
        "Full Name", "Date of Birth", "Date of Death", "Gender", "Plot",
        "Section", "Interment Type", "Interment Date", "Interment Time",
        "Officiant",
    ]
    assert len(data["rows"]) == 1
    row = data["rows"][0]
    assert row[0] == "Patricia OBrien"
    assert row[1] == "1940-01-01"
    assert row[2] == "2024-06-01"
    assert row[3] == "male"
    assert row[4] == "B-001"
    assert row[5] == "Section B"
    assert row[6] == "Burial"
    assert row[7] == "2024-06-10"
    assert row[8] == "14:30"
    assert row[9] == "Rev. Smith"


# --------------------------------------------------------------------------- #
# T-04 — Capacity Report Plot Detail table
# --------------------------------------------------------------------------- #
async def test_capacity_plot_detail_table(client, db_session: AsyncSession):
    account = await _make_account(db_session, "capdetail")
    staff = await _make_user(db_session, account, "staff")

    section = Section(tenant_id=account.id, code="C", name="Section C")
    db_session.add(section)
    await db_session.flush()

    ptype = PlotType(tenant_id=account.id, name="Single", default_price=4000, default_gap_m=0.3)
    db_session.add(ptype)
    await db_session.flush()

    occupied_plot = Plot(
        tenant_id=account.id, plot_ref="C-OCC", section_id=section.id,
        plot_type_id=ptype.id, status="occupied",
    )
    reserved_plot = Plot(
        tenant_id=account.id, plot_ref="C-RES", section_id=section.id,
        plot_type_id=ptype.id, status="reserved", reserved_by="Jane Buyer",
    )
    vacant_plot = Plot(
        tenant_id=account.id, plot_ref="C-VAC", section_id=section.id,
        plot_type_id=ptype.id, status="vacant",
    )
    db_session.add_all([occupied_plot, reserved_plot, vacant_plot])
    await db_session.flush()

    record = Record(
        tenant_id=account.id, plot_id=occupied_plot.id, first_name="Occupant",
        last_name="Alive", status="active",
    )
    db_session.add(record)
    await db_session.flush()

    resp = await client.get(
        f"/api/v1/reports/capacity?section_id={section.id}", headers=_headers(staff, account)
    )
    assert resp.status_code == 200, resp.text
    data = resp.json()["data"]

    # Existing Section Summary table (primary columns/rows) unaffected.
    assert data["columns"] == [
        "Section", "Total", "Vacant", "Reserved", "Occupied", "Hold",
        "Utilisation %", "Projected Full",
    ]
    assert len(data["rows"]) == 1
    summary_row = data["rows"][0]
    assert summary_row[0] == "Section C"
    assert summary_row[1] == 3  # total
    assert summary_row[2] == 1  # vacant
    assert summary_row[3] == 1  # reserved
    assert summary_row[4] == 1  # occupied

    detail_table = data["tables"][0]
    assert detail_table["title"] == "Plot Detail"
    assert detail_table["columns"] == [
        "Plot ID", "Section", "Status", "Type", "Price", "Reserved / Interred By",
    ]
    assert len(detail_table["rows"]) == 3
    by_plot_id = {r[0]: r for r in detail_table["rows"]}
    assert by_plot_id["C-OCC"][-1] == "Occupant Alive"
    assert by_plot_id["C-RES"][-1] == "Jane Buyer"
    assert by_plot_id["C-VAC"][-1] == "—"


async def test_capacity_plot_detail_excludes_soft_deleted_record(
    client, db_session: AsyncSession
):
    """The security-relevant deleted_at filter: a plot whose only linked Record
    is soft-deleted must NOT show that record's name in the report."""
    account = await _make_account(db_session, "capdeleted")
    staff = await _make_user(db_session, account, "staff")

    section = Section(tenant_id=account.id, code="D", name="Section D")
    db_session.add(section)
    await db_session.flush()

    ptype = PlotType(tenant_id=account.id, name="Single", default_price=4000, default_gap_m=0.3)
    db_session.add(ptype)
    await db_session.flush()

    plot = Plot(
        tenant_id=account.id, plot_ref="D-DEL", section_id=section.id,
        plot_type_id=ptype.id, status="occupied",
    )
    db_session.add(plot)
    await db_session.flush()

    deleted_record = Record(
        tenant_id=account.id, plot_id=plot.id, first_name="Ghost",
        last_name="Deleted", status="active",
    )
    db_session.add(deleted_record)
    await db_session.flush()
    deleted_record.deleted_at = datetime.now(timezone.utc)
    await db_session.flush()

    resp = await client.get(
        f"/api/v1/reports/capacity?section_id={section.id}", headers=_headers(staff, account)
    )
    assert resp.status_code == 200, resp.text
    data = resp.json()["data"]
    detail_rows = data["tables"][0]["rows"]
    assert len(detail_rows) == 1
    row = detail_rows[0]
    assert row[0] == "D-DEL"
    assert "Ghost" not in row[-1]
    assert "Deleted" not in row[-1]
    assert row[-1] == "—"


# --------------------------------------------------------------------------- #
# T-05 — Revenue by Section, invoice-based rewrite + security-fix regression
# --------------------------------------------------------------------------- #
async def test_revenue_by_section_invoice_based(client, db_session: AsyncSession):
    account = await _make_account(db_session, "revsection")
    manager = await _make_user(db_session, account, "manager")

    section = Section(tenant_id=account.id, code="E", name="Section E")
    db_session.add(section)
    await db_session.flush()

    contract_with_section = Contract(
        tenant_id=account.id, contract_number="C-SEC-1", status="signed",
        total_amount=1000, section_id=section.id,
    )
    contract_no_section = Contract(
        tenant_id=account.id, contract_number="C-SEC-2", status="signed",
        total_amount=300, section_id=None,
    )
    contract_deleted_with_section = Contract(
        tenant_id=account.id, contract_number="C-SEC-3", status="signed",
        total_amount=400, section_id=section.id,
    )
    db_session.add_all(
        [contract_with_section, contract_no_section, contract_deleted_with_section]
    )
    await db_session.flush()
    contract_deleted_with_section.deleted_at = datetime.now(timezone.utc)
    await db_session.flush()

    inv_with_section = Invoice(
        tenant_id=account.id, contract_id=contract_with_section.id,
        invoice_number="INV-SEC-1", status="paid", total_amount=1000,
        paid_amount=1000, balance_due=0, due_date=_today(),
        purchaser_name="Buyer Section",
    )
    inv_no_contract = Invoice(
        tenant_id=account.id, contract_id=None,
        invoice_number="INV-SEC-2", status="outstanding", total_amount=200,
        paid_amount=0, balance_due=200, due_date=_today(),
        purchaser_name="Buyer NoContract",
    )
    inv_contract_no_section = Invoice(
        tenant_id=account.id, contract_id=contract_no_section.id,
        invoice_number="INV-SEC-3", status="paid", total_amount=300,
        paid_amount=300, balance_due=0, due_date=_today(),
        purchaser_name="Buyer NoSection",
    )
    inv_deleted_contract_section = Invoice(
        tenant_id=account.id, contract_id=contract_deleted_with_section.id,
        invoice_number="INV-SEC-4", status="outstanding", total_amount=400,
        paid_amount=0, balance_due=400, due_date=_today(),
        purchaser_name="Buyer DeletedContract",
    )
    db_session.add_all(
        [inv_with_section, inv_no_contract, inv_contract_no_section,
         inv_deleted_contract_section]
    )
    await db_session.flush()

    resp = await client.get(
        "/api/v1/reports/revenue-by-section", headers=_headers(manager, account)
    )
    assert resp.status_code == 200, resp.text
    data = resp.json()["data"]

    assert data["columns"] == [
        "Invoice #", "Purchaser", "Total", "Paid", "Due Amount",
        "Due Date", "Status", "Section",
    ]
    by_invoice = {r[0]: r for r in data["rows"]}

    assert by_invoice["INV-SEC-1"][-1] == "Section E"
    assert by_invoice["INV-SEC-2"][-1] == "Unassigned"
    assert by_invoice["INV-SEC-3"][-1] == "Unassigned"
    # Security-fix regression: an invoice linked to a SOFT-DELETED contract
    # that has a real section_id must fall through to "Unassigned", not leak
    # the deleted contract's section.
    assert by_invoice["INV-SEC-4"][-1] == "Unassigned"


async def test_revenue_by_section_resolves_section_via_plot_when_contract_section_is_null(
    client, db_session: AsyncSession
):
    """Regression test for the live bug: real cemetery data never populates
    Contract.section_id directly (data entry always goes through picking a
    plot), so revenue-by-section must fall back to the contract's linked
    plot's section — otherwise every invoice reports as "Unassigned" even
    though the Cemetery Map clearly shows the plot's real section.
    """
    account = await _make_account(db_session, "revsectionplot")
    manager = await _make_user(db_session, account, "manager")

    section = Section(tenant_id=account.id, code="A", name="Section A")
    db_session.add(section)
    await db_session.flush()

    plot = Plot(
        tenant_id=account.id, plot_ref="A-1", section_id=section.id, status="occupied",
    )
    db_session.add(plot)
    await db_session.flush()

    # Mirrors real-world data: plot_id is set, section_id is NOT.
    contract = Contract(
        tenant_id=account.id, contract_number="C-PLOT-1", status="signed",
        total_amount=5650, plot_id=plot.id, section_id=None,
    )
    db_session.add(contract)
    await db_session.flush()

    invoice = Invoice(
        tenant_id=account.id, contract_id=contract.id,
        invoice_number="INV-PLOT-1", status="outstanding", total_amount=5650,
        paid_amount=0, balance_due=5650, due_date=_today(),
        purchaser_name="Sign Ature",
    )
    db_session.add(invoice)
    await db_session.flush()

    resp = await client.get(
        "/api/v1/reports/revenue-by-section", headers=_headers(manager, account)
    )
    assert resp.status_code == 200, resp.text
    data = resp.json()["data"]
    by_invoice = {r[0]: r for r in data["rows"]}
    assert by_invoice["INV-PLOT-1"][-1] == "Section A"


# --------------------------------------------------------------------------- #
# T-06 — Service Schedule: Decedent + Assigned Crew (+ deleted-crew filter)
# --------------------------------------------------------------------------- #
async def test_service_schedule_decedent_and_assigned_crew(
    client, db_session: AsyncSession
):
    account = await _make_account(db_session, "svcschedule")
    staff = await _make_user(db_session, account, "staff")

    crew_alice = CrewMember(
        tenant_id=account.id, name="Alice Crew", email="alice@svcschedule.com",
        phone="555-0001", address="1 Crew St",
    )
    crew_bob = CrewMember(
        tenant_id=account.id, name="Bob Crew", email="bob@svcschedule.com",
        phone="555-0002", address="2 Crew St",
    )
    crew_charlie = CrewMember(
        tenant_id=account.id, name="Charlie Crew", email="charlie@svcschedule.com",
        phone="555-0003", address="3 Crew St",
    )
    db_session.add_all([crew_alice, crew_bob, crew_charlie])
    await db_session.flush()

    service1 = ServiceEvent(
        tenant_id=account.id, service_type="Interment",
        scheduled_date=_today() + timedelta(days=3), status="confirmed",
        decedent_name="John Decedent",
    )
    service2 = ServiceEvent(
        tenant_id=account.id, service_type="Interment",
        scheduled_date=_today() + timedelta(days=4), status="confirmed",
        decedent_name="Jane Decedent",
    )
    db_session.add_all([service1, service2])
    await db_session.flush()

    db_session.add_all([
        ServiceCrewAssignment(
            tenant_id=account.id, service_id=service1.id, crew_member_id=crew_alice.id,
        ),
        ServiceCrewAssignment(
            tenant_id=account.id, service_id=service1.id, crew_member_id=crew_bob.id,
        ),
        ServiceCrewAssignment(
            tenant_id=account.id, service_id=service2.id, crew_member_id=crew_alice.id,
        ),
        ServiceCrewAssignment(
            tenant_id=account.id, service_id=service2.id, crew_member_id=crew_charlie.id,
        ),
    ])
    await db_session.flush()
    crew_charlie.deleted_at = datetime.now(timezone.utc)
    await db_session.flush()

    resp = await client.get(
        "/api/v1/reports/service-schedule", headers=_headers(staff, account)
    )
    assert resp.status_code == 200, resp.text
    data = resp.json()["data"]

    assert "Decedent" in data["columns"]
    assert "Assigned Crew" in data["columns"]
    decedent_idx = data["columns"].index("Decedent")
    crew_idx = data["columns"].index("Assigned Crew")

    row1 = next(r for r in data["rows"] if r[decedent_idx] == "John Decedent")
    crew1_names = {n.strip() for n in row1[crew_idx].split(",")}
    assert crew1_names == {"Alice Crew", "Bob Crew"}

    row2 = next(r for r in data["rows"] if r[decedent_idx] == "Jane Decedent")
    crew2_names = {n.strip() for n in row2[crew_idx].split(",") if n.strip()}
    assert crew2_names == {"Alice Crew"}
    assert "Charlie Crew" not in row2[crew_idx]


# --------------------------------------------------------------------------- #
# T-06 (Memorial Status) — relabeled columns, values/logic unchanged
# --------------------------------------------------------------------------- #
async def test_memorial_status_relabeled_columns(client, db_session: AsyncSession):
    account = await _make_account(db_session, "memstatus")
    staff = await _make_user(db_session, account, "staff")

    record = Record(
        tenant_id=account.id, first_name="Pat", last_name="Doe", status="active",
    )
    db_session.add(record)
    await db_session.flush()

    memorial = Memorial(
        tenant_id=account.id, record_id=record.id, slug="pat-doe",
        is_published=True, published_at=datetime.now(timezone.utc),
    )
    db_session.add(memorial)
    await db_session.flush()

    resp = await client.get(
        "/api/v1/reports/memorial-status", headers=_headers(staff, account)
    )
    assert resp.status_code == 200, resp.text
    data = resp.json()["data"]
    assert data["columns"] == ["Memorial", "Status", "Section", "Approved At", "Submitted At"]


async def test_memorial_status_includes_memorial_with_soft_deleted_record(
    client, db_session: AsyncSession
):
    """Regression test: a memorial must still be reported even when its linked
    Record has since been soft-deleted, matching the Memorials moderation page
    (memorial_service.py), which never filters on Record.deleted_at either.
    Memorial.display_name is denormalized, so the row still shows a real name
    without touching the deleted Record's fields.
    """
    account = await _make_account(db_session, "memstatusdel")
    staff = await _make_user(db_session, account, "staff")

    record = Record(
        tenant_id=account.id, first_name="Ptricia", last_name="OBrien", status="active",
    )
    db_session.add(record)
    await db_session.flush()

    memorial = Memorial(
        tenant_id=account.id, record_id=record.id, slug="ptricia-obrien",
        display_name="Ptricia O'Brien", is_published=True,
        published_at=datetime.now(timezone.utc),
    )
    db_session.add(memorial)
    await db_session.flush()

    # Soft-delete the record AFTER the memorial exists — same order of events
    # that produced the live bug (memorial created/published, record deleted
    # later by staff cleanup).
    record.deleted_at = datetime.now(timezone.utc)
    await db_session.flush()

    resp = await client.get(
        "/api/v1/reports/memorial-status", headers=_headers(staff, account)
    )
    assert resp.status_code == 200, resp.text
    data = resp.json()["data"]
    name_idx = data["columns"].index("Memorial")
    status_idx = data["columns"].index("Status")
    names = [r[name_idx] for r in data["rows"]]
    assert "Ptricia O'Brien" in names
    row = next(r for r in data["rows"] if r[name_idx] == "Ptricia O'Brien")
    assert row[status_idx] == "Published"


# --------------------------------------------------------------------------- #
# T-08 / T-09 — Audit Activity Log removed from bulk CSV, endpoint still live
# --------------------------------------------------------------------------- #
async def test_audit_log_endpoint_still_live_for_manager(client, db_session: AsyncSession):
    account = await _make_account(db_session, "auditlive")
    manager = await _make_user(db_session, account, "manager")

    resp = await client.get(
        "/api/v1/reports/audit-log", headers=_headers(manager, account)
    )
    assert resp.status_code == 200, resp.text


async def test_bulk_csv_excludes_audit_log_section(client, db_session: AsyncSession):
    account = await _make_account(db_session, "auditbulk")
    manager = await _make_user(db_session, account, "manager")

    resp = await client.get(
        "/api/v1/reports/export/bulk-csv?from_date=2020-01-01&to_date=2020-12-31",
        headers=_headers(manager, account),
    )
    assert resp.status_code == 200, resp.text
    content = resp.text
    assert "Audit Activity Log" not in content
    assert "## Audit" not in content


# --------------------------------------------------------------------------- #
# Tenant isolation regression — revenue-by-section and capacity Plot Detail
# --------------------------------------------------------------------------- #
async def test_revenue_by_section_tenant_isolation(client, db_session: AsyncSession):
    account_a = await _make_account(db_session, "tenanta")
    account_b = await _make_account(db_session, "tenantb")
    manager_a = await _make_user(db_session, account_a, "manager")

    section_a = Section(tenant_id=account_a.id, code="TA", name="Tenant A Section")
    section_b = Section(tenant_id=account_b.id, code="TB", name="Tenant B Section")
    db_session.add_all([section_a, section_b])
    await db_session.flush()

    contract_a = Contract(
        tenant_id=account_a.id, contract_number="C-TA-1", status="signed",
        total_amount=100, section_id=section_a.id,
    )
    contract_b = Contract(
        tenant_id=account_b.id, contract_number="C-TB-1", status="signed",
        total_amount=999, section_id=section_b.id,
    )
    db_session.add_all([contract_a, contract_b])
    await db_session.flush()

    invoice_a = Invoice(
        tenant_id=account_a.id, contract_id=contract_a.id, invoice_number="INV-TA-1",
        status="paid", total_amount=100, paid_amount=100, balance_due=0,
        purchaser_name="Tenant A Buyer",
    )
    invoice_b = Invoice(
        tenant_id=account_b.id, contract_id=contract_b.id, invoice_number="INV-TB-1",
        status="paid", total_amount=999, paid_amount=999, balance_due=0,
        purchaser_name="Tenant B Buyer",
    )
    db_session.add_all([invoice_a, invoice_b])
    await db_session.flush()

    resp = await client.get(
        "/api/v1/reports/revenue-by-section", headers=_headers(manager_a, account_a)
    )
    assert resp.status_code == 200, resp.text
    data = resp.json()["data"]
    invoice_numbers = {r[0] for r in data["rows"]}
    assert "INV-TA-1" in invoice_numbers
    assert "INV-TB-1" not in invoice_numbers
    section_names = {r[-1] for r in data["rows"]}
    assert "Tenant B Section" not in section_names


async def test_capacity_plot_detail_tenant_isolation(client, db_session: AsyncSession):
    account_a = await _make_account(db_session, "tenantc")
    account_b = await _make_account(db_session, "tenantd")
    staff_a = await _make_user(db_session, account_a, "staff")

    plot_a = Plot(tenant_id=account_a.id, plot_ref="SHARED-REF", status="vacant")
    plot_b = Plot(tenant_id=account_b.id, plot_ref="SHARED-REF", status="occupied")
    db_session.add_all([plot_a, plot_b])
    await db_session.flush()

    record_b = Record(
        tenant_id=account_b.id, plot_id=plot_b.id, first_name="Other",
        last_name="TenantRecord", status="active",
    )
    db_session.add(record_b)
    await db_session.flush()

    resp = await client.get(
        "/api/v1/reports/capacity", headers=_headers(staff_a, account_a)
    )
    assert resp.status_code == 200, resp.text
    data = resp.json()["data"]
    detail_rows = data["tables"][0]["rows"]
    assert len(detail_rows) == 1
    assert detail_rows[0][0] == "SHARED-REF"
    assert detail_rows[0][2] == "Vacant"
    assert "Other TenantRecord" not in detail_rows[0][-1]


# --------------------------------------------------------------------------- #
# Agent Performance groups by user identity, not display-name string
# --------------------------------------------------------------------------- #
async def test_agent_performance_groups_by_user_id_not_name(
    client, db_session: AsyncSession
):
    account = await _make_account(db_session, "sameagentname")
    manager = await _make_user(db_session, account, "manager")

    # Two distinct users sharing the exact same first+last name.
    agent1 = await _make_user(db_session, account, "staff")
    agent1.first_name, agent1.last_name = "Jordan", "Lee"
    agent2 = await _make_user(db_session, account, "staff")
    agent2.first_name, agent2.last_name = "Jordan", "Lee"
    await db_session.flush()

    contract1 = Contract(
        tenant_id=account.id, contract_number="C-DUP-1", status="signed",
        total_amount=500, created_by=agent1.id,
    )
    contract2 = Contract(
        tenant_id=account.id, contract_number="C-DUP-2", status="signed",
        total_amount=700, created_by=agent2.id,
    )
    db_session.add_all([contract1, contract2])
    await db_session.flush()

    invoice1 = Invoice(
        tenant_id=account.id, contract_id=contract1.id, invoice_number="INV-DUP-1",
        status="paid", total_amount=500, paid_amount=500, balance_due=0,
    )
    invoice2 = Invoice(
        tenant_id=account.id, contract_id=contract2.id, invoice_number="INV-DUP-2",
        status="paid", total_amount=700, paid_amount=700, balance_due=0,
    )
    db_session.add_all([invoice1, invoice2])
    await db_session.flush()

    resp = await client.get(
        "/api/v1/reports/monthly-sales", headers=_headers(manager, account)
    )
    assert resp.status_code == 200, resp.text
    data = resp.json()["data"]
    agent_table = next(t for t in data["tables"] if t["title"] == "Agent Performance")

    jordan_rows = [r for r in agent_table["rows"] if r[0] == "Jordan Lee"]
    # Proves the group-by-user-id fix: two distinct users with the same display
    # name must appear as 2 separate rows, not merged into one.
    assert len(jordan_rows) == 2
    revenues = sorted(r[2] for r in jordan_rows)
    assert revenues == [500.0, 700.0]
    contracts = sorted(r[1] for r in jordan_rows)
    assert contracts == [1, 1]