"""Unit tests for PgSessionStore (sequence_number + archive)."""

import pytest

from navi.core.pg_session_store import PgSessionStore
from navi.core.session import Session
from navi.llm.base import Message
from tests.conftest_factory import FakeConnection, FakePool


@pytest.mark.asyncio
async def test_save_assigns_sequence_numbers_to_new_messages():
    conn = FakeConnection()
    conn.enqueue("OK")  # upsert sessions row (INSERT ... ON CONFLICT)
    conn.enqueue(None)  # executemany UPDATE (empty)
    conn.enqueue("OK")  # INSERT executemany
    conn.enqueue("OK")  # UPDATE next_sequence

    pool = FakePool(conn)
    store = PgSessionStore(pool)
    store._initialized = True

    session = Session(profile_id="test")
    session.messages = [
        Message(role="user", content="hi"),
        Message(role="assistant", content="hello"),
    ]
    for m in session.messages:
        assert m.sequence_number == -1

    await store.save(session)

    assert session.messages[0].sequence_number == 0
    assert session.messages[1].sequence_number == 1
    assert session.db_next_sequence == 2


@pytest.mark.asyncio
async def test_save_updates_existing_rows_by_sequence_number():
    conn = FakeConnection()
    conn.enqueue("OK")  # upsert sessions row (INSERT ... ON CONFLICT)
    conn.enqueue(None)  # executemany UPDATE
    conn.enqueue("OK")  # INSERT (empty)
    conn.enqueue("OK")  # UPDATE next_sequence

    pool = FakePool(conn)
    store = PgSessionStore(pool)
    store._initialized = True

    session = Session(profile_id="test")
    m1 = Message(role="user", content="hi")
    m1.sequence_number = 0
    m2 = Message(role="assistant", content="hello")
    m2.sequence_number = 1
    session.messages = [m1, m2]
    session.db_next_sequence = 2

    await store.save(session)

    executemany_calls = [c for c in conn.calls if c[0] == "executemany"]
    assert len(executemany_calls) == 1
    assert "UPDATE session_messages" in executemany_calls[0][1]


@pytest.mark.asyncio
async def test_archive_old_messages_sends_correct_sql():
    conn = FakeConnection()
    conn.enqueue("INSERT 0 3")  # copy to archive
    conn.enqueue("OK")  # DELETE
    conn.enqueue("OK")  # UPDATE archive_threshold

    pool = FakePool(conn)
    store = PgSessionStore(pool)
    store._initialized = True

    n = await store.archive_old_messages("sid-1", keep_seq_threshold=100)
    assert n == 3

    execute_calls = [c for c in conn.calls if c[0] == "execute"]
    assert any("DELETE FROM session_messages" in c[1] for c in execute_calls)
    assert any("UPDATE sessions SET archive_threshold" in c[1] for c in execute_calls)


@pytest.mark.asyncio
async def test_create_registers_pending_without_db():
    """create() must NOT touch the DB — the session lives only in _pending."""
    conn = FakeConnection()
    pool = FakePool(conn)
    store = PgSessionStore(pool)
    store._initialized = True

    session = await store.create(profile_id="test", user_id="u1")

    assert session.profile_id == "test"
    assert session.user_id == "u1"
    # Registered as pending, no DB row yet.
    assert session.id in store._pending
    assert store._pending[session.id] is session
    # No execute/fetch happened on the connection.
    assert conn.calls == []


@pytest.mark.asyncio
async def test_get_returns_pending_session_without_db():
    """get() returns the in-memory pending session without hitting the DB."""
    conn = FakeConnection()
    pool = FakePool(conn)
    store = PgSessionStore(pool)
    store._initialized = True

    created = await store.create(profile_id="test", user_id="u1")
    fetched = await store.get(created.id)

    assert fetched is created
    assert conn.calls == []  # no DB lookup for a pending session


@pytest.mark.asyncio
async def test_get_falls_back_to_db_for_unknown_session():
    """get() returns None for an id that is neither pending nor in the DB."""
    conn = FakeConnection()
    conn.enqueue(None)  # fetchrow for the sessions row -> not found
    pool = FakePool(conn)
    store = PgSessionStore(pool)
    store._initialized = True

    fetched = await store.get("does-not-exist")
    assert fetched is None


@pytest.mark.asyncio
async def test_save_upserts_and_pops_pending():
    """First save() upserts the sessions row and removes it from _pending."""
    conn = FakeConnection()
    conn.enqueue("INSERT 0 1")  # upsert sessions row
    conn.enqueue("OK")  # INSERT executemany (one new message)
    conn.enqueue("OK")  # UPDATE next_sequence
    pool = FakePool(conn)
    store = PgSessionStore(pool)
    store._initialized = True

    session = await store.create(profile_id="test", user_id="u1")
    assert session.id in store._pending

    session.messages.append(Message(role="user", content="hi"))
    await store.save(session)

    # The pending entry is gone after the first save.
    assert session.id not in store._pending
    # The first execute is the upsert (INSERT ... ON CONFLICT), not a plain UPDATE.
    execute_calls = [c for c in conn.calls if c[0] == "execute"]
    assert "INSERT INTO sessions" in execute_calls[0][1]
    assert "ON CONFLICT" in execute_calls[0][1]
    # Message got a real sequence number.
    assert session.messages[0].sequence_number == 0


@pytest.mark.asyncio
async def test_list_all_excludes_pending():
    """Pending sessions must not appear in list_all (DB query only)."""
    conn = FakeConnection()
    conn.enqueue([])  # fetch sessions -> DB has no rows
    pool = FakePool(conn)
    store = PgSessionStore(pool)
    store._initialized = True

    await store.create(profile_id="test", user_id="u1")
    sessions = await store.list_all(user_id="u1")

    assert sessions == []
    # The pending session is still in memory, just not listed.
    assert len(store._pending) == 1


@pytest.mark.asyncio
async def test_sweep_pending_removes_old_entries():
    """sweep_pending drops entries older than the age threshold."""
    from datetime import datetime, timedelta, timezone

    pool = FakePool(FakeConnection())
    store = PgSessionStore(pool)
    store._initialized = True

    session = await store.create(profile_id="test")
    # Backdate creation so it's older than a 1s threshold.
    session.created_at = datetime.now(timezone.utc) - timedelta(hours=2)

    removed = await store.sweep_pending(max_age_seconds=1)
    assert removed == 1
    assert session.id not in store._pending


@pytest.mark.asyncio
async def test_sweep_pending_keeps_recent_entries():
    """sweep_pending leaves recently-created entries alone."""
    pool = FakePool(FakeConnection())
    store = PgSessionStore(pool)
    store._initialized = True

    session = await store.create(profile_id="test")
    removed = await store.sweep_pending(max_age_seconds=3600)
    assert removed == 0
    assert session.id in store._pending
