"""
INDL-45 — Reports & Analytics.

Integration + unit tests for the 8 report data endpoints, the PDF/Excel/bulk-CSV
export endpoints, and their security controls (RBAC per report type, tenant
isolation / IDOR, report_type allowlist, date-range bounds, formula-injection
sanitisation, export audit logging, rate limiting).

Maps to PRD acceptance criteria AC-01..AC-12 and security criteria SEC-01..SEC-12.
"""
import uuid
from datetime import date, datetime, timedelta, timezone

import openpyxl
import pytest
import pytest_asyncio
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

from src.apps.auth.models.user import User
from src.apps.billing.models.invoice import Invoice
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.scheduling.models.service_event import ServiceEvent
from src.apps.sections.models.section import Section
from src.apps.site_admin.models.audit_log import AuditLog
from src.apps.tenants.models.account import Account
from src.apps.reports.services.data import ReportDataService, ReportResult
from src.apps.reports.services.export import ReportExportService
from src.apps.reports.security import (
    check_export_rate_limit,
    reset_export_rate_limit,
    safe_filename,
    validate_date_range,
    validate_report_type,
)
from src.core.exceptions import RateLimitError, ValidationError
from src.core.security import build_token_payload, create_access_token, hash_password

pytestmark = pytest.mark.asyncio

ALL_REPORTS = [
    "monthly-sales", "burial-register", "capacity", "revenue-by-section",
    "outstanding-balances", "service-schedule", "audit-log", "memorial-status",
]
STAFF_REPORTS = [
    "monthly-sales", "burial-register", "capacity", "service-schedule",
    "memorial-status",
]
MANAGER_REPORTS = ["revenue-by-section", "outstanding-balances", "audit-log"]


# --------------------------------------------------------------------------- #
# Helpers
# --------------------------------------------------------------------------- #
async def _make_account(db: AsyncSession, subdomain: str) -> Account:
    account = Account(
        organization_name=f"Cemetery {subdomain}",
        subdomain=subdomain,
        contact_email=f"admin@{subdomain}.com",
        plan="starter",
        status="active",
    )
    db.add(account)
    await db.flush()
    return account


async def _make_user(db: AsyncSession, account: Account, role: str) -> User:
    user = User(
        tenant_id=account.id,
        email=f"{role}-{uuid.uuid4().hex[:8]}@{account.subdomain}.com",
        password_hash=hash_password("TestPassword123"),
        first_name=role.capitalize(),
        last_name="User",
        role=role,
        status="active",
    )
    db.add(user)
    await db.flush()
    return user


def _headers(user: User, account: Account) -> dict:
    payload = build_token_payload(user, account)
    return {
        "Authorization": f"Bearer {create_access_token(payload)}",
        "X-Tenant-ID": str(account.id),
    }


async def _seed_domain(db: AsyncSession, account: Account) -> dict:
    """Seed a section, plot type, plots, record + burial, invoices, service, memorial."""
    section = Section(tenant_id=account.id, code="A", name="Section A")
    db.add(section)
    await db.flush()

    ptype = PlotType(tenant_id=account.id, name="Single", default_price=1500, capacity=1)
    db.add(ptype)
    await db.flush()

    plot = Plot(
        tenant_id=account.id, plot_ref="A-001", section_id=section.id,
        plot_type_id=ptype.id, status="occupied",
        reserved_at=datetime.now(timezone.utc) - timedelta(days=30),
    )
    vacant_plot = Plot(
        tenant_id=account.id, plot_ref="A-002", section_id=section.id,
        plot_type_id=ptype.id, status="vacant",
    )
    db.add_all([plot, vacant_plot])
    await db.flush()

    record = Record(
        tenant_id=account.id, plot_id=plot.id, first_name="Patricia",
        last_name="O'Brien", date_of_birth=date(1940, 1, 1),
        date_of_death=date(2024, 6, 1), is_veteran=True, status="active",
    )
    db.add(record)
    await db.flush()

    burial = BurialInfo(
        tenant_id=account.id, record_id=record.id,
        interment_date=date(2024, 6, 10), interment_type="Burial",
        officiant="Rev. Smith",
    )
    db.add(burial)

    # Two open invoices in different aging buckets.
    inv1 = Invoice(
        tenant_id=account.id, invoice_number="INV-001", status="partial",
        total_amount=2000, paid_amount=1000, balance_due=1000,
        due_date=date.today() - timedelta(days=45), purchaser_name="Jane Smith",
    )
    inv2 = Invoice(
        tenant_id=account.id, invoice_number="INV-002", status="overdue",
        total_amount=500, paid_amount=0, balance_due=500,
        due_date=date.today() - timedelta(days=120), purchaser_name="John Doe",
    )
    db.add_all([inv1, inv2])

    service = ServiceEvent(
        tenant_id=account.id, plot_id=plot.id, section_id=section.id,
        service_type="Interment", scheduled_date=date.today() + timedelta(days=3),
        status="scheduled", family_contact_name="Mary Smith",
        family_contact_phone="555-0100", officiant="Rev. Smith",
    )
    db.add(service)

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

    return {"section": section, "plot": plot, "record": record}


@pytest_asyncio.fixture
async def seeded(db_session: AsyncSession):
    account = await _make_account(db_session, "greenhills")
    admin = await _make_user(db_session, account, "administrator")
    manager = await _make_user(db_session, account, "manager")
    staff = await _make_user(db_session, account, "staff")
    domain = await _seed_domain(db_session, account)
    return {
        "account": account, "admin": admin, "manager": manager,
        "staff": staff, **domain,
    }


@pytest_asyncio.fixture(autouse=True)
def _clear_rate_limit():
    reset_export_rate_limit()
    yield
    reset_export_rate_limit()


# --------------------------------------------------------------------------- #
# AC-09 — all data endpoints return well-structured data
# --------------------------------------------------------------------------- #
@pytest.mark.parametrize("report_type", ALL_REPORTS)
async def test_data_endpoint_returns_structure(client, seeded, report_type):
    resp = await client.get(
        f"/api/v1/reports/{report_type}",
        headers=_headers(seeded["manager"], seeded["account"]),
    )
    assert resp.status_code == 200, resp.text
    data = resp.json()["data"]
    assert "title" in data and "columns" in data and "rows" in data
    assert isinstance(data["columns"], list) and isinstance(data["rows"], list)


# --------------------------------------------------------------------------- #
# AC-10 / SEC-01 / SEC-02 / SEC-09 — per-report-type RBAC
# --------------------------------------------------------------------------- #
@pytest.mark.parametrize("report_type", MANAGER_REPORTS)
async def test_staff_forbidden_on_manager_reports(client, seeded, report_type):
    resp = await client.get(
        f"/api/v1/reports/{report_type}",
        headers=_headers(seeded["staff"], seeded["account"]),
    )
    assert resp.status_code == 403


@pytest.mark.parametrize("report_type", STAFF_REPORTS)
async def test_staff_allowed_on_staff_reports(client, seeded, report_type):
    resp = await client.get(
        f"/api/v1/reports/{report_type}",
        headers=_headers(seeded["staff"], seeded["account"]),
    )
    assert resp.status_code == 200


@pytest.mark.parametrize("report_type", MANAGER_REPORTS)
async def test_staff_forbidden_on_manager_report_exports(client, seeded, report_type):
    """SEC-01 — export variants of manager reports also 403 for staff."""
    resp = await client.get(
        f"/api/v1/reports/{report_type}/export/pdf",
        headers=_headers(seeded["staff"], seeded["account"]),
    )
    assert resp.status_code == 403


# --------------------------------------------------------------------------- #
# AC-04 / SEC-06 / SEC-08 — PDF export
# --------------------------------------------------------------------------- #
async def test_export_pdf(client, seeded):
    resp = await client.get(
        "/api/v1/reports/monthly-sales/export/pdf",
        headers=_headers(seeded["manager"], seeded["account"]),
    )
    assert resp.status_code == 200
    assert resp.headers["content-type"] == "application/pdf"
    assert resp.content.startswith(b"%PDF")
    assert resp.headers["content-disposition"].startswith("attachment")
    assert ".pdf" in resp.headers["content-disposition"]
    # SEC-06 — export security headers
    assert "no-store" in resp.headers["cache-control"]
    assert resp.headers["x-content-type-options"] == "nosniff"


# --------------------------------------------------------------------------- #
# AC-05 — Excel export
# --------------------------------------------------------------------------- #
async def test_export_excel(client, seeded):
    resp = await client.get(
        "/api/v1/reports/burial-register/export/excel",
        headers=_headers(seeded["manager"], seeded["account"]),
    )
    assert resp.status_code == 200
    assert "spreadsheetml" in resp.headers["content-type"]
    assert resp.content.startswith(b"PK")  # .xlsx is a zip
    assert ".xlsx" in resp.headers["content-disposition"]


# --------------------------------------------------------------------------- #
# AC-06 / SEC-10 — bulk CSV export
# --------------------------------------------------------------------------- #
async def test_bulk_csv_requires_dates(client, seeded):
    resp = await client.get(
        "/api/v1/reports/export/bulk-csv",
        headers=_headers(seeded["manager"], seeded["account"]),
    )
    assert resp.status_code == 422


async def test_bulk_csv_success(client, seeded):
    resp = await client.get(
        "/api/v1/reports/export/bulk-csv?from_date=2024-01-01&to_date=2024-12-31",
        headers=_headers(seeded["manager"], seeded["account"]),
    )
    assert resp.status_code == 200
    assert "text/csv" in resp.headers["content-type"]
    assert ".csv" in resp.headers["content-disposition"]


async def test_bulk_csv_forbidden_for_staff(client, seeded):
    """SEC-11 — bulk CSV requires manager minimum."""
    resp = await client.get(
        "/api/v1/reports/export/bulk-csv?from_date=2024-01-01&to_date=2024-12-31",
        headers=_headers(seeded["staff"], seeded["account"]),
    )
    assert resp.status_code == 403


# --------------------------------------------------------------------------- #
# SEC-05 — report_type allowlist + path regex
# --------------------------------------------------------------------------- #
async def test_invalid_report_type_422(client, seeded):
    resp = await client.get(
        "/api/v1/reports/not-a-real-report/export/pdf",
        headers=_headers(seeded["manager"], seeded["account"]),
    )
    assert resp.status_code == 422


@pytest.mark.parametrize("bad", ["Monthly", "audit_log", "reports123"])
async def test_report_type_path_regex_rejects(client, seeded, bad):
    resp = await client.get(
        f"/api/v1/reports/{bad}/export/pdf",
        headers=_headers(seeded["manager"], seeded["account"]),
    )
    assert resp.status_code == 422


# --------------------------------------------------------------------------- #
# SEC-03 / SEC-04 — date range bound
# --------------------------------------------------------------------------- #
async def test_date_range_exceeds_max_422(client, seeded):
    resp = await client.get(
        "/api/v1/reports/monthly-sales/export/pdf"
        "?from_date=2020-01-01&to_date=2025-12-31",
        headers=_headers(seeded["manager"], seeded["account"]),
    )
    assert resp.status_code == 422


# --------------------------------------------------------------------------- #
# A07 — auth required
# --------------------------------------------------------------------------- #
async def test_export_requires_auth(client):
    resp = await client.get("/api/v1/reports/monthly-sales/export/pdf")
    assert resp.status_code == 401


# --------------------------------------------------------------------------- #
# SEC-02 / API1 — cross-tenant section IDOR
# --------------------------------------------------------------------------- #
async def test_cross_tenant_section_404(client, db_session, seeded):
    other = await _make_account(db_session, "rivalcem")
    other_section = Section(tenant_id=other.id, code="X", name="Rival Section")
    db_session.add(other_section)
    await db_session.flush()

    resp = await client.get(
        f"/api/v1/reports/capacity?section_id={other_section.id}",
        headers=_headers(seeded["manager"], seeded["account"]),
    )
    assert resp.status_code == 404


# --------------------------------------------------------------------------- #
# SEC-08 — export writes an audit log entry
# --------------------------------------------------------------------------- #
async def test_export_writes_audit_log(client, db_session, seeded):
    await client.get(
        "/api/v1/reports/monthly-sales/export/pdf",
        headers=_headers(seeded["manager"], seeded["account"]),
    )
    result = await db_session.execute(
        select(AuditLog).where(
            AuditLog.tenant_id == seeded["account"].id,
            AuditLog.entity_type == "report_export",
        )
    )
    entries = result.scalars().all()
    assert len(entries) >= 1
    entry = entries[-1]
    assert entry.action == "export"
    assert entry.user_id == seeded["manager"].id
    assert entry.new_value["report_type"] == "monthly-sales"
    assert entry.new_value["format"] == "pdf"


# --------------------------------------------------------------------------- #
# AC-07 / AC-08 — outstanding balances shape (aging + paid/due split)
# --------------------------------------------------------------------------- #
async def test_outstanding_balances_shape(client, seeded):
    resp = await client.get(
        "/api/v1/reports/outstanding-balances",
        headers=_headers(seeded["manager"], seeded["account"]),
    )
    assert resp.status_code == 200
    data = resp.json()["data"]
    assert data["summary"]["total_invoices"] == 2
    assert data["summary"]["total_due"] == 1500.0
    buckets = data["aging_buckets"]
    assert buckets["31_60_days"]["count"] == 1     # INV-001, 45 days
    assert buckets["over_90_days"]["count"] == 1    # INV-002, 120 days
    inv = next(i for i in data["invoices"] if i["invoice_number"] == "INV-001")
    assert inv["paid_pct"] == 50.0
    assert inv["due_pct"] == 50.0
    assert inv["aging_bucket"] == "31_60_days"


# --------------------------------------------------------------------------- #
# AC-11 — empty state still produces a valid export
# --------------------------------------------------------------------------- #
async def test_empty_report_still_exports(client, db_session):
    account = await _make_account(db_session, "emptycem")
    manager = await _make_user(db_session, account, "manager")
    resp = await client.get(
        "/api/v1/reports/outstanding-balances/export/excel",
        headers=_headers(manager, account),
    )
    assert resp.status_code == 200
    assert resp.content.startswith(b"PK")


# --------------------------------------------------------------------------- #
# INDL-45-01 — legacy /revenue endpoint field-name bug fix (no 500)
# --------------------------------------------------------------------------- #
async def test_legacy_revenue_endpoint_works(client, seeded):
    resp = await client.get(
        "/api/v1/reports/revenue",
        headers=_headers(seeded["manager"], seeded["account"]),
    )
    assert resp.status_code == 200
    assert resp.json()["success"] is True


# --------------------------------------------------------------------------- #
# Unit tests — export sanitisation & security helpers
# --------------------------------------------------------------------------- #
def test_excel_formula_injection_sanitised():
    """SEC-04 / SEC-07 — a formula-like cell is stored as text, not a formula."""
    result = ReportResult(
        title="T", columns=["Name"],
        rows=[['=HYPERLINK("http://evil.com","x")'], ["+1+1"], ["@SUM(A1)"]],
    )
    buf = ReportExportService().generate_excel(result)
    wb = openpyxl.load_workbook(buf)
    ws = wb.active
    for row_idx in (2, 3, 4):
        val = ws.cell(row=row_idx, column=1).value
        assert val.startswith("\t")
        assert not val.startswith(("=", "+", "@"))


def test_csv_formula_injection_sanitised():
    result = ReportResult(
        title="T", columns=["Name"], rows=[["=cmd|'/c calc'!A1"]],
    )
    out = "".join(ReportExportService().generate_csv(result))
    # The dangerous value is tab-prefixed so it no longer starts with '='.
    assert "\t=cmd" in out


def test_pdf_generation_produces_valid_bytes():
    result = ReportResult(title="Empty", columns=["A", "B"], rows=[])
    buf = ReportExportService().generate_pdf(result)
    assert buf.getvalue().startswith(b"%PDF")


def test_safe_filename_strips_unsafe_chars():
    fn = safe_filename("../../etc/passwd", "pdf", date(2026, 7, 3))
    assert "/" not in fn and ".." not in fn.replace(".pdf", "")
    assert fn.endswith(".pdf")


def test_validate_report_type_rejects_unknown():
    with pytest.raises(ValidationError):
        validate_report_type("nope")
    assert validate_report_type("monthly-sales") == "monthly-sales"


def test_validate_date_range_bounds():
    with pytest.raises(ValidationError):
        validate_date_range(date(2020, 1, 1), date(2025, 1, 1))  # > 366 days
    with pytest.raises(ValidationError):
        validate_date_range(date(2024, 6, 1), date(2024, 1, 1))  # from > to
    with pytest.raises(ValidationError):
        validate_date_range(None, None, require=True)  # required but absent
    # Valid range does not raise.
    validate_date_range(date(2024, 1, 1), date(2024, 6, 1))


def test_rate_limit_raises_after_limit():
    """SEC-11 — the sliding-window limiter blocks the (limit+1)th request."""
    reset_export_rate_limit()
    key = "tenant:user"
    for _ in range(3):
        check_export_rate_limit(key, limit=3, window=60)
    with pytest.raises(RateLimitError):
        check_export_rate_limit(key, limit=3, window=60)