#!/usr/bin/env python3
"""
Tests for Story 5.05 (Track B): SessionStore — Session Lifecycle Manager

Black Box tests (BB): verify the public contract from the caller's perspective —
    correct UUIDs returned, active-session filtering, close semantics, None on miss.
White Box tests (WB): verify internals — connection pool getconn/putconn pattern,
    UUID4 generation, parameterised SQL (no f-strings), no SQLite import,
    cleanup_orphaned_sessions WHERE clause shape.

ALL tests use mocks — NO real Postgres connection is required.

Story: 5.05
File under test: core/storage/session_store.py
"""

from __future__ import annotations

import sys
sys.path.insert(0, "/mnt/e/genesis-system")

import pathlib
import re
import uuid
from datetime import datetime
from unittest.mock import MagicMock, patch

import pytest

# ---------------------------------------------------------------------------
# Module under test
# ---------------------------------------------------------------------------

from core.storage.session_store import SessionStore
from core.storage import SessionStore as SessionStoreFromPackage


# ---------------------------------------------------------------------------
# Mock-pool / mock-connection factory helpers
# ---------------------------------------------------------------------------

def _make_pool_and_conn(fetchone_return=None, fetchall_return=None):
    """
    Returns (mock_pool, mock_conn, mock_cursor) wired so that:
      - pool.getconn()  → conn
      - pool.putconn()  is tracked
      - conn.cursor()   supports the context-manager protocol
      - cursor.fetchone() → fetchone_return
      - cursor.fetchall() → fetchall_return (default [])
      - cursor.rowcount   → 0  (overridden per-test where needed)
    """
    mock_cursor = MagicMock()
    mock_cursor.fetchone.return_value = fetchone_return
    mock_cursor.fetchall.return_value = (
        fetchall_return if fetchall_return is not None else []
    )
    mock_cursor.rowcount = 0  # default; override in individual tests

    mock_conn = MagicMock()
    mock_conn.cursor.return_value.__enter__ = MagicMock(return_value=mock_cursor)
    mock_conn.cursor.return_value.__exit__ = MagicMock(return_value=False)

    mock_pool = MagicMock()
    mock_pool.getconn.return_value = mock_conn

    return mock_pool, mock_conn, mock_cursor


def _make_store(fetchone=None, fetchall=None):
    """Return a SessionStore wired to a fully-mocked pool."""
    mock_pool, mock_conn, mock_cursor = _make_pool_and_conn(fetchone, fetchall)
    with patch("psycopg2.pool.ThreadedConnectionPool", return_value=mock_pool):
        store = SessionStore(
            {"host": "localhost", "port": 5432,
             "user": "u", "password": "p", "dbname": "genesis"}
        )
    # Expose mocks for assertion convenience
    store._mock_pool = mock_pool
    store._mock_conn = mock_conn
    store._mock_cursor = mock_cursor
    return store


# ---------------------------------------------------------------------------
# Helper: build a fake session row as RealDictCursor would return
# ---------------------------------------------------------------------------

def _session_row(
    session_id: str = None,
    agent_id: str = "test-agent",
    started_at: datetime = None,
    ended_at: datetime = None,
    metadata: dict = None,
) -> dict:
    return {
        "id": session_id or str(uuid.uuid4()),
        "started_at": started_at or datetime(2026, 2, 25, 10, 0, 0),
        "ended_at": ended_at,
        "agent_id": agent_id,
        "metadata": metadata,
    }


# ===========================================================================
# Black Box tests
# ===========================================================================


class TestBB1OpenSessionReturnsUUID:
    """BB1: open_session returns a valid UUID4 string."""

    def test_returns_string(self):
        store = _make_store()
        result = store.open_session("forge-agent")
        assert isinstance(result, str)

    def test_returned_value_is_valid_uuid(self):
        store = _make_store()
        result = store.open_session("forge-agent")
        parsed = uuid.UUID(result)
        assert str(parsed) == result

    def test_two_calls_return_different_uuids(self):
        store = _make_store()
        id1 = store.open_session("agent-a")
        id2 = store.open_session("agent-b")
        assert id1 != id2


class TestBB2GetActiveSessionsIncludesNewSession:
    """BB2: open_session → get_active_sessions() includes the new session."""

    def test_active_sessions_contains_open_session(self):
        session_id = str(uuid.uuid4())
        row = _session_row(session_id=session_id, ended_at=None)
        store = _make_store(fetchall=[row])
        sessions = store.get_active_sessions()
        assert len(sessions) == 1
        assert sessions[0]["id"] == session_id
        assert sessions[0]["ended_at"] is None

    def test_returns_empty_when_no_active_sessions(self):
        store = _make_store(fetchall=[])
        sessions = store.get_active_sessions()
        assert sessions == []


class TestBB3CloseSessionRemovesFromActive:
    """BB3: close_session → session no longer appears in get_active_sessions()."""

    def test_closed_session_not_in_active_list(self):
        # After close, fetchall returns empty (simulates DB filter working)
        store_after_close = _make_store(fetchall=[])
        active = store_after_close.get_active_sessions()
        assert active == []

    def test_close_session_does_not_raise(self):
        store = _make_store()
        # Should complete without exception
        store.close_session(str(uuid.uuid4()))


class TestBB4GetSessionUnknownIdReturnsNone:
    """BB4: get_session(unknown_id) → returns None."""

    def test_unknown_id_returns_none(self):
        store = _make_store(fetchone=None)
        result = store.get_session("00000000-0000-0000-0000-000000000000")
        assert result is None

    def test_does_not_raise_on_missing_session(self):
        store = _make_store(fetchone=None)
        try:
            result = store.get_session(str(uuid.uuid4()))
            assert result is None
        except Exception as exc:
            pytest.fail(f"get_session raised unexpectedly: {exc}")

    def test_known_id_returns_dict(self):
        session_id = str(uuid.uuid4())
        row = _session_row(session_id=session_id)
        store = _make_store(fetchone=row)
        result = store.get_session(session_id)
        assert result is not None
        assert isinstance(result, dict)
        assert result["id"] == session_id


class TestBB5CleanupOrphanedSessionsReturnsCount:
    """BB5: cleanup_orphaned_sessions() returns the number of rows updated."""

    def test_returns_integer(self):
        store = _make_store()
        # rowcount defaults to 0 in mock
        count = store.cleanup_orphaned_sessions()
        assert isinstance(count, int)

    def test_returns_nonzero_when_rows_updated(self):
        store = _make_store()
        store._mock_cursor.rowcount = 3
        count = store.cleanup_orphaned_sessions()
        assert count == 3

    def test_returns_zero_when_no_rows_updated(self):
        store = _make_store()
        store._mock_cursor.rowcount = 0
        count = store.cleanup_orphaned_sessions()
        assert count == 0


# ===========================================================================
# White Box tests
# ===========================================================================


class TestWB1CleanupOrphanedSQLShape:
    """WB1: cleanup_orphaned_sessions SQL uses WHERE ended_at IS NULL AND
    started_at < NOW() - INTERVAL '24 hours'."""

    def test_sql_contains_ended_at_is_null(self):
        source = pathlib.Path(
            "/mnt/e/genesis-system/core/storage/session_store.py"
        ).read_text()
        assert "ended_at IS NULL" in source, (
            "cleanup_orphaned_sessions must filter on ended_at IS NULL"
        )

    def test_sql_contains_24_hours_interval(self):
        source = pathlib.Path(
            "/mnt/e/genesis-system/core/storage/session_store.py"
        ).read_text()
        assert "24 hours" in source.lower(), (
            "cleanup_orphaned_sessions must use a 24-hour interval"
        )

    def test_sql_contains_started_at_comparison(self):
        source = pathlib.Path(
            "/mnt/e/genesis-system/core/storage/session_store.py"
        ).read_text()
        assert "started_at" in source, (
            "cleanup_orphaned_sessions must compare started_at to detect old sessions"
        )


class TestWB2OpenSessionGeneratesUUID4:
    """WB2: open_session generates UUID4 (not sequential int or uuid1)."""

    def test_uses_uuid4(self):
        store = _make_store()
        with patch("core.storage.session_store.uuid.uuid4") as mock_uuid4:
            fixed = uuid.UUID("aaaaaaaa-bbbb-4bbb-8bbb-cccccccccccc")
            mock_uuid4.return_value = fixed
            returned = store.open_session("agent-x")
        assert returned == str(fixed)
        mock_uuid4.assert_called()

    def test_source_uses_uuid4_not_uuid1(self):
        source = pathlib.Path(
            "/mnt/e/genesis-system/core/storage/session_store.py"
        ).read_text()
        assert "uuid.uuid4()" in source, "open_session must use uuid.uuid4()"
        assert "uuid.uuid1()" not in source, "Must not use uuid1 — use uuid4"


class TestWB3ConnectionPoolGetconnPutconn:
    """WB3: every public method uses getconn/putconn in try/finally."""

    def test_open_session_getconn_putconn(self):
        store = _make_store()
        store.open_session("agent")
        store._mock_pool.getconn.assert_called_once()
        store._mock_pool.putconn.assert_called_once_with(store._mock_conn)

    def test_close_session_getconn_putconn(self):
        store = _make_store()
        store.close_session(str(uuid.uuid4()))
        store._mock_pool.getconn.assert_called_once()
        store._mock_pool.putconn.assert_called_once_with(store._mock_conn)

    def test_get_active_sessions_getconn_putconn(self):
        store = _make_store(fetchall=[])
        store.get_active_sessions()
        store._mock_pool.getconn.assert_called_once()
        store._mock_pool.putconn.assert_called_once_with(store._mock_conn)

    def test_get_session_getconn_putconn(self):
        store = _make_store(fetchone=None)
        store.get_session(str(uuid.uuid4()))
        store._mock_pool.getconn.assert_called_once()
        store._mock_pool.putconn.assert_called_once_with(store._mock_conn)

    def test_cleanup_getconn_putconn(self):
        store = _make_store()
        store.cleanup_orphaned_sessions()
        store._mock_pool.getconn.assert_called_once()
        store._mock_pool.putconn.assert_called_once_with(store._mock_conn)

    def test_putconn_called_even_when_execute_raises(self):
        """putconn must run in finally even if cursor.execute raises."""
        store = _make_store()
        store._mock_cursor.execute.side_effect = RuntimeError("DB error")
        with pytest.raises(RuntimeError):
            store.open_session("agent")
        store._mock_pool.putconn.assert_called_once_with(store._mock_conn)


class TestWB4NoSQLiteImport:
    """WB4: session_store.py must not import sqlite3."""

    def test_no_sqlite3_in_source(self):
        source = pathlib.Path(
            "/mnt/e/genesis-system/core/storage/session_store.py"
        ).read_text()
        assert "import sqlite3" not in source, (
            "session_store.py must NOT import sqlite3 — Genesis Rule 7 (no SQLite)"
        )

    def test_sqlite3_not_in_module_namespace(self):
        import core.storage.session_store as mod
        assert not hasattr(mod, "sqlite3"), (
            "sqlite3 must not appear in session_store module namespace"
        )


# ===========================================================================
# Package export tests
# ===========================================================================


class TestPackageExports:
    """SessionStore must be importable directly from core.storage."""

    def test_session_store_importable_from_package(self):
        assert SessionStoreFromPackage is SessionStore

    def test_all_includes_session_store(self):
        from core.storage import __all__
        assert "SessionStore" in __all__


# ===========================================================================
# ThreadedConnectionPool initialisation test
# ===========================================================================


class TestConnectionPoolInit:
    """SessionStore must create ThreadedConnectionPool(minconn=2, maxconn=10)."""

    def test_pool_created_with_correct_min_max(self):
        with patch("psycopg2.pool.ThreadedConnectionPool") as mock_cls:
            mock_cls.return_value = MagicMock()
            params = {"host": "h", "port": 5432,
                      "user": "u", "password": "p", "dbname": "db"}
            SessionStore(params)

        positional = mock_cls.call_args[0]
        assert positional[0] == 2, f"minconn must be 2, got {positional[0]}"
        assert positional[1] == 10, f"maxconn must be 10, got {positional[1]}"

    def test_pool_receives_connection_params(self):
        with patch("psycopg2.pool.ThreadedConnectionPool") as mock_cls:
            mock_cls.return_value = MagicMock()
            params = {"host": "myhost", "port": 5433,
                      "user": "admin", "password": "secret",
                      "dbname": "genesis_test"}
            SessionStore(params)

        kwargs = mock_cls.call_args[1]
        assert kwargs["host"] == "myhost"
        assert kwargs["dbname"] == "genesis_test"


# ===========================================================================
# SessionStore.close() test
# ===========================================================================


class TestClose:
    """close() must call pool.closeall()."""

    def test_close_calls_closeall(self):
        store = _make_store()
        store.close()
        store._mock_pool.closeall.assert_called_once()

    def test_close_twice_does_not_raise(self):
        store = _make_store()
        store.close()
        store.close()  # second call must not raise
        assert store._mock_pool.closeall.call_count == 2


# ===========================================================================
# Parametrised SQL safety test
# ===========================================================================


class TestParameterisedQueries:
    """All SQL in session_store.py must use %s placeholders, not f-strings."""

    def test_no_fstring_sql_in_source(self):
        source = pathlib.Path(
            "/mnt/e/genesis-system/core/storage/session_store.py"
        ).read_text()
        # Heuristic: f-strings containing SQL keywords are forbidden
        fstring_sql = re.findall(
            r'f["\'].*?(SELECT|INSERT|UPDATE|DELETE|WHERE).*?["\']', source
        )
        assert not fstring_sql, (
            f"Found f-string SQL (injection risk): {fstring_sql}"
        )

    def test_open_session_execute_called_with_params_tuple(self):
        """open_session must call cur.execute(sql, params) — not bare execute(sql)."""
        store = _make_store()
        store.open_session("forge-agent", {"key": "val"})
        cur = store._mock_cursor
        assert cur.execute.call_count >= 1
        for call_args in cur.execute.call_args_list:
            args = call_args[0]
            assert len(args) >= 2, (
                "cur.execute() must be called with (sql, params) — not just (sql,)"
            )


# ===========================================================================
# Standalone runner (mirrors pattern from test_story_5_02.py)
# ===========================================================================

if __name__ == "__main__":
    import traceback

    tests = [
        # BB
        ("BB1a: open_session returns string",
         TestBB1OpenSessionReturnsUUID().test_returns_string),
        ("BB1b: returned value is valid UUID",
         TestBB1OpenSessionReturnsUUID().test_returned_value_is_valid_uuid),
        ("BB1c: two calls → different UUIDs",
         TestBB1OpenSessionReturnsUUID().test_two_calls_return_different_uuids),
        ("BB2a: active sessions contains open session",
         TestBB2GetActiveSessionsIncludesNewSession().test_active_sessions_contains_open_session),
        ("BB2b: empty when no active sessions",
         TestBB2GetActiveSessionsIncludesNewSession().test_returns_empty_when_no_active_sessions),
        ("BB3a: closed session not in active list",
         TestBB3CloseSessionRemovesFromActive().test_closed_session_not_in_active_list),
        ("BB3b: close_session does not raise",
         TestBB3CloseSessionRemovesFromActive().test_close_session_does_not_raise),
        ("BB4a: unknown id returns None",
         TestBB4GetSessionUnknownIdReturnsNone().test_unknown_id_returns_none),
        ("BB4b: no exception on missing session",
         TestBB4GetSessionUnknownIdReturnsNone().test_does_not_raise_on_missing_session),
        ("BB4c: known id returns dict",
         TestBB4GetSessionUnknownIdReturnsNone().test_known_id_returns_dict),
        ("BB5a: cleanup returns integer",
         TestBB5CleanupOrphanedSessionsReturnsCount().test_returns_integer),
        ("BB5b: cleanup returns nonzero count",
         TestBB5CleanupOrphanedSessionsReturnsCount().test_returns_nonzero_when_rows_updated),
        ("BB5c: cleanup returns zero",
         TestBB5CleanupOrphanedSessionsReturnsCount().test_returns_zero_when_no_rows_updated),
        # WB
        ("WB1a: SQL has ended_at IS NULL",
         TestWB1CleanupOrphanedSQLShape().test_sql_contains_ended_at_is_null),
        ("WB1b: SQL has 24 hours interval",
         TestWB1CleanupOrphanedSQLShape().test_sql_contains_24_hours_interval),
        ("WB1c: SQL has started_at comparison",
         TestWB1CleanupOrphanedSQLShape().test_sql_contains_started_at_comparison),
        ("WB2a: uses uuid4",
         TestWB2OpenSessionGeneratesUUID4().test_uses_uuid4),
        ("WB2b: source uses uuid4 not uuid1",
         TestWB2OpenSessionGeneratesUUID4().test_source_uses_uuid4_not_uuid1),
        ("WB3a: open_session getconn/putconn",
         TestWB3ConnectionPoolGetconnPutconn().test_open_session_getconn_putconn),
        ("WB3b: close_session getconn/putconn",
         TestWB3ConnectionPoolGetconnPutconn().test_close_session_getconn_putconn),
        ("WB3c: get_active_sessions getconn/putconn",
         TestWB3ConnectionPoolGetconnPutconn().test_get_active_sessions_getconn_putconn),
        ("WB3d: get_session getconn/putconn",
         TestWB3ConnectionPoolGetconnPutconn().test_get_session_getconn_putconn),
        ("WB3e: cleanup getconn/putconn",
         TestWB3ConnectionPoolGetconnPutconn().test_cleanup_getconn_putconn),
        ("WB3f: putconn called even on execute error",
         TestWB3ConnectionPoolGetconnPutconn().test_putconn_called_even_when_execute_raises),
        ("WB4a: no sqlite3 in source",
         TestWB4NoSQLiteImport().test_no_sqlite3_in_source),
        ("WB4b: sqlite3 not in module namespace",
         TestWB4NoSQLiteImport().test_sqlite3_not_in_module_namespace),
        # Package
        ("PKG: SessionStore importable from package",
         TestPackageExports().test_session_store_importable_from_package),
        ("PKG: __all__ includes SessionStore",
         TestPackageExports().test_all_includes_session_store),
        # Pool init
        ("POOL: minconn=2, maxconn=10",
         TestConnectionPoolInit().test_pool_created_with_correct_min_max),
        ("POOL: params forwarded",
         TestConnectionPoolInit().test_pool_receives_connection_params),
        # Close
        ("CLOSE: closeall called",
         TestClose().test_close_calls_closeall),
        ("CLOSE: close twice does not raise",
         TestClose().test_close_twice_does_not_raise),
        # SQL safety
        ("SQL: no f-string SQL",
         TestParameterisedQueries().test_no_fstring_sql_in_source),
        ("SQL: open_session execute uses params tuple",
         TestParameterisedQueries().test_open_session_execute_called_with_params_tuple),
    ]

    passed = 0
    failed = 0
    for name, fn in tests:
        try:
            fn()
            print(f"  [PASS] {name}")
            passed += 1
        except Exception as exc:
            print(f"  [FAIL] {name}: {exc}")
            traceback.print_exc()
            failed += 1

    print(f"\n{passed}/{passed + failed} tests passed")
    if failed == 0:
        print("ALL TESTS PASSED -- Story 5.05 (Track B): SessionStore")
    else:
        sys.exit(1)