"""Tests for scripts/ingest_splits.py.

File-structure tests (no DB) exercise XLSX loading, header validation, row
parsing and single-vendor checks in isolation.

DB-backed tests use @db.test_schema_default_seed with the in-memory SQLite
test database and exercise the full run() pipeline.

Seed state relevant to these tests (vendor 24601):
  Collaborators : id=1 ("Person"), id=2 ("Another Person")  — both vendor 24601
                  id=3 ("Person")                           — vendor 90210
  Splits (type=SplitTypeId.TRACK):
    identifier="12341234", collaborator_id=2, rate=0.10 NET
    identifier="12341234", collaborator_id=3, rate=0.20 NET
  Snowflake Tracks:
    id/tuid=12341234 → upc="101"
    id/tuid=12341235 → upc="101"
    id/tuid=12345    → upc="102"
  Snowflake Products:
    product_id=1, upc="101", vendor_id=24601  (2 tracks)
    product_id=2, upc="102", vendor_id=24601  (1 track)
"""

from pathlib import Path

import openpyxl
import pytest
from sqlalchemy import select

from collaborator.connectors import mysql
from collaborator.constants.split_type import SplitTypeId
from collaborator.models.rds.split import Split
from scripts.ingest_splits import (
    IngestConfig,
    IngestSummary,
    load_xlsx,
    parse_rows,
    run,
    validate_headers,
    validate_single_vendor,
)
from tests.testutils import db

# ---------------------------------------------------------------------------
# Helpers
# ---------------------------------------------------------------------------

_HEADERS = [
    "Vendor ID",
    "Collaborator ID",
    "Collaborator Name",
    "Collaborator Split",
    "Split Type",
    "tuid",
    "productid",
]


def _write_xlsx(path: Path, headers: list, rows: list) -> None:
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.append(headers)
    for row in rows:
        ws.append(row)
    wb.save(str(path))


def _make_config(file_path: Path, dry_run: bool = True) -> IngestConfig:
    """Build an IngestConfig without touching sys.argv or env vars."""
    # _cli_parse_args is a pydantic-settings private kwarg; mypy can't see it
    return IngestConfig(  # type: ignore[call-arg]
        file_path=file_path,
        ticket_id="TEST-001",
        dry_run=dry_run,
        _cli_parse_args=False,
    )


# ---------------------------------------------------------------------------
# File-structure tests (no DB required)
# ---------------------------------------------------------------------------


def test_parse_valid_rows(tmp_path: Path) -> None:
    """Valid XLSX parses to correct SplitRow objects."""
    file_path = tmp_path / "ingest.xlsx"
    _write_xlsx(
        file_path,
        headers=_HEADERS,
        rows=[
            [24601, 1, "Person", 0.15, "NET", "12341234", None],
            [24601, 2, "Another Person", 0.20, "GROSS", "55555555", None],
        ],
    )

    raw_rows = load_xlsx(file_path)
    validate_headers(raw_rows)
    rows = parse_rows(raw_rows)
    vendor_id = validate_single_vendor(rows)

    assert len(rows) == 2
    assert vendor_id == 24601
    assert rows[0].split_type == "NET"
    assert rows[1].split_type == "GROSS"


def test_missing_required_header(tmp_path: Path) -> None:
    """Dropping a required column raises ValueError listing the missing column."""
    file_path = tmp_path / "bad.xlsx"
    headers_without_vendor = [h for h in _HEADERS if h != "Vendor ID"]
    _write_xlsx(
        file_path,
        headers=headers_without_vendor,
        rows=[[1, "Person", 0.15, "NET", "12341234", None]],
    )

    raw_rows = load_xlsx(file_path)
    with pytest.raises(ValueError, match="Missing required columns"):
        validate_headers(raw_rows)


def test_multiple_vendor_ids(tmp_path: Path) -> None:
    """Rows with different vendor IDs raise ValueError."""
    file_path = tmp_path / "multi_vendor.xlsx"
    _write_xlsx(
        file_path,
        headers=_HEADERS,
        rows=[
            [24601, 1, "Person", 0.15, "NET", "12341234", None],
            [99999, 2, "Other", 0.10, "NET", "55555555", None],
        ],
    )

    raw_rows = load_xlsx(file_path)
    validate_headers(raw_rows)
    rows = parse_rows(raw_rows)
    with pytest.raises(ValueError, match="exactly one vendor ID"):
        validate_single_vendor(rows)


def test_missing_tuid_and_upc(tmp_path: Path) -> None:
    """A row with neither tuid nor release_upc fails row validation."""
    file_path = tmp_path / "no_id.xlsx"
    _write_xlsx(
        file_path,
        headers=_HEADERS,
        rows=[[24601, 1, "Person", 0.15, "NET", None, None]],
    )

    raw_rows = load_xlsx(file_path)
    validate_headers(raw_rows)
    with pytest.raises(ValueError, match="tuid or a Product ID"):
        parse_rows(raw_rows)


def test_invalid_split_type(tmp_path: Path) -> None:
    """An unrecognised split_type (e.g. 'FLAT') fails row validation."""
    file_path = tmp_path / "bad_type.xlsx"
    _write_xlsx(
        file_path,
        headers=_HEADERS,
        rows=[[24601, 1, "Person", 0.15, "FLAT", "12341234", None]],
    )

    raw_rows = load_xlsx(file_path)
    validate_headers(raw_rows)
    with pytest.raises(ValueError, match="Validation failed"):
        parse_rows(raw_rows)


def test_split_rate_out_of_range(tmp_path: Path) -> None:
    """A split_rate that after percentage normalisation is still > 1 fails row validation."""
    file_path = tmp_path / "bad_rate.xlsx"
    _write_xlsx(
        file_path,
        headers=_HEADERS,
        rows=[[24601, 1, "Person", 150, "NET", "12341234", None]],
    )

    raw_rows = load_xlsx(file_path)
    validate_headers(raw_rows)
    with pytest.raises(ValueError, match="Validation failed"):
        parse_rows(raw_rows)


def test_tuid_preserved_as_string(tmp_path: Path) -> None:
    """Integer tuid cells are coerced to strings without float corruption."""
    file_path = tmp_path / "int_tuid.xlsx"
    _write_xlsx(
        file_path,
        headers=_HEADERS,
        rows=[[24601, 1, "Person", 0.15, "NET", 12341234, None]],
    )

    raw_rows = load_xlsx(file_path)
    rows = parse_rows(raw_rows)
    assert rows[0].tuid == "12341234"


def test_product_id_only_row_accepted(tmp_path: Path) -> None:
    """A row with a product ID and no tuid parses without error."""
    file_path = tmp_path / "product_id_only.xlsx"
    _write_xlsx(
        file_path,
        headers=_HEADERS,
        rows=[[24601, 1, "Person", 0.10, "NET", None, 1]],
    )

    raw_rows = load_xlsx(file_path)
    validate_headers(raw_rows)
    rows = parse_rows(raw_rows)
    assert len(rows) == 1
    assert rows[0].tuid is None
    assert rows[0].product_id == 1


def test_all_row_errors_collected(tmp_path: Path) -> None:
    """parse_rows reports errors for all invalid rows, not just the first."""
    file_path = tmp_path / "multi_err.xlsx"
    _write_xlsx(
        file_path,
        headers=_HEADERS,
        rows=[
            [24601, 1, "Person", 150, "NET", "12341234", None],  # bad rate (150% → 1.5)
            [24601, 2, "Other", 0.10, "FLAT", "55555555", None],  # bad type
        ],
    )

    raw_rows = load_xlsx(file_path)
    with pytest.raises(ValueError, match="2 row"):
        parse_rows(raw_rows)


# ---------------------------------------------------------------------------
# DB-backed tests
# ---------------------------------------------------------------------------


@db.test_schema_default_seed
def test_happy_path_dry_run_with_db(tmp_path: Path) -> None:
    """Full dry-run against the seeded test DB returns correct change counts.

    Rows: collaborators 1 and 2 for TUID 12341234.

    Seed splits (type=2) for TUID 12341234:
      collaborator_id=1 (rate 0.20 NET) — will be updated
      collaborator_id=2 (rate 0.10 NET) — will be updated

    Both ingested rows match existing splits, so they update; nothing is new.
    """
    file_path = tmp_path / "ingest.xlsx"
    _write_xlsx(
        file_path,
        headers=_HEADERS,
        rows=[
            [24601, 1, "Person", 0.15, "NET", "12341234", None],
            [24601, 2, "Another Person", 0.30, "NET", "12341234", None],
        ],
    )

    summary = run(_make_config(file_path))

    assert isinstance(summary, IngestSummary)
    assert summary.vendor_id == 24601
    assert summary.input_rows == 2
    assert summary.expanded_rows == 2
    assert summary.dry_run is True
    assert summary.existing_collaborators_matched == 2
    assert summary.new_collaborators == 0
    assert summary.tracks_with_new_splits == 0
    assert summary.tracks_with_existing_splits_updated == 1  # both updates on 1 track
    assert summary.tracks_with_unaffected_splits == 0
    assert summary.new_splits_created == 0
    assert summary.existing_splits_updated == 2


@db.test_schema_default_seed
def test_collaborator_not_found(tmp_path: Path) -> None:
    """A collaborator ID that doesn't exist in the DB raises ValueError."""
    file_path = tmp_path / "bad_collab.xlsx"
    _write_xlsx(
        file_path,
        headers=_HEADERS,
        rows=[[24601, 999, "Ghost", 0.10, "NET", "12341234", None]],
    )

    with pytest.raises(ValueError, match="Collaborator ID 999 not found"):
        run(_make_config(file_path))


@db.test_schema_default_seed
def test_collaborator_wrong_vendor(tmp_path: Path) -> None:
    """A collaborator that belongs to a different vendor raises ValueError."""
    # Collaborator 3 belongs to vendor 90210, not 24601
    file_path = tmp_path / "wrong_vendor.xlsx"
    _write_xlsx(
        file_path,
        headers=_HEADERS,
        rows=[[24601, 3, "Person", 0.10, "NET", "12341234", None]],
    )

    with pytest.raises(ValueError, match="belongs to vendor"):
        run(_make_config(file_path))


@db.test_schema_default_seed
def test_tuid_not_found_in_snowflake(tmp_path: Path) -> None:
    """A TUID not present in Snowflake DIM_TRACK raises ValueError."""
    file_path = tmp_path / "bad_tuid.xlsx"
    _write_xlsx(
        file_path,
        headers=_HEADERS,
        rows=[[24601, 1, "Person", 0.10, "NET", "99999999", None]],
    )

    with pytest.raises(ValueError, match="TUIDs not found in Snowflake"):
        run(_make_config(file_path))


@db.test_schema_default_seed
def test_product_id_expansion_with_db(tmp_path: Path) -> None:
    """Product ID 1 expands to its two seeded tracks (12341234 and 12341235).

    The single product-ID row for collaborator 1 becomes two TUID rows — one per
    track — so total_rows in the summary is 2.
    """
    file_path = tmp_path / "product_id_expand.xlsx"
    _write_xlsx(
        file_path,
        headers=_HEADERS,
        rows=[[24601, 1, "Person", 0.10, "NET", None, 1]],
    )

    summary = run(_make_config(file_path))

    # 1 XLSX row expanded to 2 track rows
    assert summary.input_rows == 1
    assert summary.expanded_rows == 2
    assert summary.existing_collaborators_matched == 1
    # collab 1 has a type-2 split at 12341234 already (update) but none at
    # 12341235 → 1 new + 1 update across 2 tracks
    assert summary.new_splits_created == 1
    assert summary.tracks_with_new_splits == 1
    assert summary.existing_splits_updated == 1
    assert summary.tracks_with_existing_splits_updated == 1


@db.test_schema_default_seed
def test_product_id_not_found_for_vendor(tmp_path: Path) -> None:
    """A product ID not owned by the vendor raises ValueError."""
    file_path = tmp_path / "bad_product_id.xlsx"
    _write_xlsx(
        file_path,
        headers=_HEADERS,
        rows=[[24601, 1, "Person", 0.10, "NET", None, 9999]],
    )

    with pytest.raises(ValueError, match="Product IDs not found for vendor"):
        run(_make_config(file_path))


# ---------------------------------------------------------------------------
# Write tests (dry_run=False)
# ---------------------------------------------------------------------------


@mysql.db_session
def _get_split(tuid: str, collab_id: int, session) -> dict | None:
    """Fetch a single SplitTypeId.TRACK split as a plain dict, or None."""
    row = session.execute(
        select(
            Split.split_rate,
            Split.rate_type,
            Split.created_by,
            Split.updated_by,
        ).where(
            Split.identifier == tuid,
            Split.collaborator_id == collab_id,
            Split.split_type_id == SplitTypeId.TRACK,
        )
    ).first()
    if row is None:
        return None
    split_rate, rate_type, created_by, updated_by = row
    return {
        "split_rate": split_rate,
        "rate_type": rate_type,
        "created_by": created_by,
        "updated_by": updated_by,
    }


@db.test_schema_default_seed
def test_write_creates_and_updates_splits(tmp_path: Path) -> None:
    """dry_run=False writes updates to existing splits.

    Seed splits for TUID 12341234:
      collab 1: rate=0.20 NET  → updated to 0.15
      collab 2: rate=0.10 NET  → updated to 0.30

    Both ingested rows match existing splits and update them.
    """
    file_path = tmp_path / "ingest.xlsx"
    _write_xlsx(
        file_path,
        headers=_HEADERS,
        rows=[
            [24601, 1, "Person", 0.15, "NET", "12341234", None],
            [24601, 2, "Another Person", 0.30, "NET", "12341234", None],
        ],
    )

    summary = run(_make_config(file_path, dry_run=False))

    assert summary.dry_run is False
    assert summary.new_splits_created == 0
    assert summary.existing_splits_updated == 2

    # Updated split for collab 1
    s1 = _get_split("12341234", 1)
    assert s1 is not None
    assert s1["split_rate"] == pytest.approx(0.15)
    assert s1["updated_by"] == "TEST-001"

    # Updated split for collab 2
    s2 = _get_split("12341234", 2)
    assert s2 is not None
    assert s2["split_rate"] == pytest.approx(0.30)
    assert s2["updated_by"] == "TEST-001"


@db.test_schema_default_seed
def test_dry_run_makes_no_db_changes(tmp_path: Path) -> None:
    """dry_run=True (default) leaves the DB completely unchanged."""
    file_path = tmp_path / "ingest.xlsx"
    _write_xlsx(
        file_path,
        headers=_HEADERS,
        rows=[[24601, 1, "Person", 0.99, "NET", "12341234", None]],
    )

    summary = run(_make_config(file_path, dry_run=True))

    assert summary.dry_run is True
    # Collab 1 has a seeded split for 12341234 at rate 0.20 — dry run must
    # leave the rate unchanged.
    s1 = _get_split("12341234", 1)
    assert s1 is not None
    assert s1["split_rate"] == pytest.approx(0.20)


# ---------------------------------------------------------------------------
# Collaborator creation tests (dry_run=False, no collaborator_id in row)
# ---------------------------------------------------------------------------


@mysql.db_session
def _get_collaborator_by_name(name: str, vendor_id: int, session) -> dict | None:
    """Fetch a single collaborator by name+vendor as a plain dict, or None."""
    from collaborator.models.rds.collaborator import Collaborator as _Collab

    row = (
        session.execute(
            select(_Collab).where(
                _Collab.name == name,
                _Collab.vendor_id == vendor_id,
                _Collab.collaborator_type == "COLLABORATOR",
            )
        )
        .scalars()
        .first()
    )
    return row.to_dict() if row else None


@db.test_schema_default_seed
def test_new_collaborator_created_on_write(tmp_path: Path) -> None:
    """A row with no collaborator_id creates a new collaborator and its split.

    Seed has no collaborator named "Brand New" for vendor 24601.
    ACCOUNT_PAYMENT_TERM seed provides currency "USD" for vendor 24601.
    """
    file_path = tmp_path / "ingest.xlsx"
    _write_xlsx(
        file_path,
        headers=_HEADERS,
        rows=[[24601, None, "Brand New", 0.10, "NET", "12341234", None]],
    )

    summary = run(_make_config(file_path, dry_run=False))

    assert summary.dry_run is False
    assert summary.new_collaborators == 1
    assert summary.new_splits_created == 1

    collab = _get_collaborator_by_name("Brand New", 24601)
    assert collab is not None
    assert collab["currency"] == "USD"
    assert collab["vendor_id"] == 24601

    split = _get_split("12341234", collab["id"])
    assert split is not None
    assert split["split_rate"] == pytest.approx(0.10)
    assert split["created_by"] == "TEST-001"


@db.test_schema_default_seed
def test_dry_run_does_not_create_collaborators(tmp_path: Path) -> None:
    """dry_run=True with a new-collaborator row leaves the DB unchanged."""
    file_path = tmp_path / "ingest.xlsx"
    _write_xlsx(
        file_path,
        headers=_HEADERS,
        rows=[[24601, None, "Brand New", 0.10, "NET", "12341234", None]],
    )

    summary = run(_make_config(file_path, dry_run=True))

    assert summary.dry_run is True
    assert summary.new_collaborators == 1  # shown in summary
    assert summary.new_splits_created == 1  # counted even without collaborator ID
    assert _get_collaborator_by_name("Brand New", 24601) is None  # not written


@db.test_schema_default_seed
def test_existing_collaborator_reused_on_write(tmp_path: Path) -> None:
    """A row with no collaborator_id but a name matching an existing collaborator reuses it.

    Seed has collaborator id=1, name="Person", vendor_id=24601.
    """
    file_path = tmp_path / "ingest.xlsx"
    _write_xlsx(
        file_path,
        headers=_HEADERS,
        rows=[[24601, None, "Person", 0.25, "NET", "12341234", None]],
    )

    summary = run(_make_config(file_path, dry_run=False))

    assert summary.new_collaborators == 0
    assert summary.existing_collaborators_matched == 1
    # Only one "Person" collaborator should exist for vendor 24601
    collab = _get_collaborator_by_name("Person", 24601)
    assert collab is not None
    assert collab["id"] == 1  # reused the seeded collaborator