"""Regression test for issue #3544.

When a research session is deleted, ``ResearchResource`` rows
cascade-delete and their ``PaperAppearance`` rows cascade-delete with
them. ``Paper`` rows have no FK back to research and remain in place.

Before the fix, ``/api/journals/user-research`` queried ``Paper``
directly and so kept showing journals whose only Papers belonged to
deleted research sessions. The fix adds ``.filter(Paper.appearances.any())``
to both the top-200 GROUP BY query and the predatory_blocked DISTINCT
query so orphan Papers are excluded from the dashboard view.

This test asserts the filter behavior at the SQL semantics level:
- one Paper still has appearances → it is included
- one Paper is orphaned (its only appearance was cascade-deleted) → it is excluded
"""

import uuid

import pytest
from sqlalchemy import create_engine, event, func
from sqlalchemy.orm import sessionmaker

from local_deep_research.database.models import (
    Base,
    Paper,
    PaperAppearance,
    ResearchHistory,
    ResearchResource,
)


@pytest.fixture
def session():
    """In-memory SQLite engine with FK enforcement enabled.

    FK enforcement is required because the cascade we are testing
    (``research_resources.research_id`` → ``research_history.id``
    with ``ondelete=CASCADE``) is enforced by the database, not by
    SQLAlchemy ORM. Bare SQLite has FK enforcement off by default.
    """
    engine = create_engine("sqlite:///:memory:")

    @event.listens_for(engine, "connect")
    def _enable_fk(dbapi_connection, _):
        dbapi_connection.execute("PRAGMA foreign_keys = ON")

    Base.metadata.create_all(engine)
    Session = sessionmaker(bind=engine)
    s = Session()
    yield s
    s.close()
    engine.dispose()


def _seed_research_with_paper(session, journal_name, doi):
    """Create a (ResearchHistory, ResearchResource, Paper, PaperAppearance) chain."""
    rid = str(uuid.uuid4())
    research = ResearchHistory(
        id=rid,
        query=f"q for {journal_name}",
        mode="detailed",
        status="completed",
        created_at="2026-05-07T00:00:00",
    )
    session.add(research)
    session.flush()

    resource = ResearchResource(
        research_id=rid,
        title=f"paper in {journal_name}",
        url=f"https://example.com/{doi}",
        source_type="article",
        created_at="2026-05-07T00:00:00",
    )
    session.add(resource)
    session.flush()

    paper = Paper(
        doi=doi,
        container_title=journal_name,
        year=2026,
    )
    session.add(paper)
    session.flush()

    appearance = PaperAppearance(
        paper_id=paper.id,
        resource_id=resource.id,
        source_engine="arxiv",
    )
    session.add(appearance)
    session.commit()
    return research, paper


def _user_research_journals_query(session):
    """Mirror the production query in api_user_research_journals."""
    return (
        session.query(
            Paper.container_title,
            func.count(Paper.id).label("paper_count"),
        )
        .filter(Paper.container_title.isnot(None))
        .filter(Paper.appearances.any())
        .group_by(Paper.container_title)
        .all()
    )


def _distinct_titles_query(session):
    """Mirror the production predatory_blocked DISTINCT query."""
    return [
        name
        for (name,) in session.query(Paper.container_title)
        .filter(Paper.container_title.isnot(None))
        .filter(Paper.appearances.any())
        .distinct()
        .all()
    ]


def test_orphan_paper_excluded_from_user_research_dashboard(session):
    """After deleting one research session, its journal must disappear
    from the dashboard while the surviving session's journal remains."""
    _, kept_paper = _seed_research_with_paper(session, "Journal A", "10.1/jA")
    deleted_research, orphan_paper = _seed_research_with_paper(
        session, "Journal B", "10.1/jB"
    )

    # Sanity: both journals visible before deletion.
    rows_before = _user_research_journals_query(session)
    titles_before = {r.container_title for r in rows_before}
    assert titles_before == {"Journal A", "Journal B"}

    # Delete one research session — its ResearchResource cascade-deletes,
    # which cascade-deletes its PaperAppearance row. The Paper row stays.
    session.delete(deleted_research)
    session.commit()

    # Paper is still in the DB — fix is not deletion-based.
    assert session.query(Paper).filter_by(id=orphan_paper.id).count() == 1, (
        "Fix must not delete orphan Papers — only filter them at query time"
    )
    # The other Paper still has its appearance.
    assert (
        session.query(PaperAppearance).filter_by(paper_id=kept_paper.id).count()
        == 1
    )
    # The orphan Paper's appearance is gone (cascade through ResearchResource).
    assert (
        session.query(PaperAppearance)
        .filter_by(paper_id=orphan_paper.id)
        .count()
        == 0
    )

    # Dashboard query: orphan journal must be excluded.
    rows_after = _user_research_journals_query(session)
    titles_after = {r.container_title for r in rows_after}
    assert titles_after == {"Journal A"}, (
        f"Orphan Paper's journal must be excluded from dashboard; "
        f"got {titles_after}"
    )

    # Predatory_blocked DISTINCT query: same behavior.
    distinct_after = _distinct_titles_query(session)
    assert set(distinct_after) == {"Journal A"}


def test_paper_with_multiple_appearances_kept_when_one_deleted(session):
    """A paper that appears in two research sessions must remain visible
    after one of those sessions is deleted (only orphans are excluded)."""
    research_a_id = str(uuid.uuid4())
    research_b_id = str(uuid.uuid4())
    for rid in (research_a_id, research_b_id):
        session.add(
            ResearchHistory(
                id=rid,
                query="q",
                mode="detailed",
                status="completed",
                created_at="2026-05-07T00:00:00",
            )
        )
    session.flush()

    # One Paper, two ResearchResources (one per session), two PaperAppearances.
    paper = Paper(
        doi="10.1/shared",
        container_title="Shared Journal",
        year=2026,
    )
    session.add(paper)
    session.flush()

    for rid in (research_a_id, research_b_id):
        resource = ResearchResource(
            research_id=rid,
            title="shared paper",
            url=f"https://example.com/{rid}",
            source_type="article",
            created_at="2026-05-07T00:00:00",
        )
        session.add(resource)
        session.flush()
        session.add(
            PaperAppearance(
                paper_id=paper.id,
                resource_id=resource.id,
                source_engine="openalex",
            )
        )
    session.commit()

    # Delete research A — its appearance goes, B's stays.
    session.delete(
        session.query(ResearchHistory).filter_by(id=research_a_id).one()
    )
    session.commit()

    assert (
        session.query(PaperAppearance).filter_by(paper_id=paper.id).count() == 1
    )
    rows = _user_research_journals_query(session)
    titles = {r.container_title for r in rows}
    assert titles == {"Shared Journal"}, (
        f"Paper with surviving appearance must stay visible; got {titles}"
    )
