#!/usr/bin/env python3
"""
Tests for Story 5.01 (Track B): Postgres Schema — sessions, events, swarm_sagas

Black Box tests (BB): verify the public contract from the outside —
    correct DDL structure, idempotency, and correct drop order.
White Box tests (WB): verify internal DDL content — field names, FK constraints,
    CHECK constraints, indexes, and CASCADE behaviour.

Story: 5.01
File under test: core/storage/postgres_schema.py
"""

from __future__ import annotations

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

import pytest
from unittest.mock import MagicMock, call, patch


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

from core.storage.postgres_schema import (
    create_all_tables,
    drop_all_tables,
    SESSIONS_DDL,
    EVENTS_DDL,
    SWARM_SAGAS_DDL,
)


# ---------------------------------------------------------------------------
# Helpers / fixtures
# ---------------------------------------------------------------------------

def _make_mock_conn() -> MagicMock:
    """Return a mock psycopg2 connection with a working context-manager cursor."""
    conn = MagicMock()
    cursor = MagicMock()
    # Support 'with conn.cursor() as cur:' pattern
    conn.cursor.return_value.__enter__ = MagicMock(return_value=cursor)
    conn.cursor.return_value.__exit__ = MagicMock(return_value=False)
    conn._cursor = cursor  # expose for assertions
    return conn


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


def test_bb1_create_all_tables_runs_without_error():
    """BB1: create_all_tables() runs without raising any exception."""
    conn = _make_mock_conn()
    create_all_tables(conn)  # Must not raise


def test_bb2_create_all_tables_is_idempotent():
    """BB2: Calling create_all_tables() twice must not raise any exception."""
    conn = _make_mock_conn()
    create_all_tables(conn)
    create_all_tables(conn)  # Second call must also be safe


def test_bb3_drop_all_tables_drops_in_reverse_fk_order():
    """BB3: drop_all_tables() drops sagas first, then events, then sessions (reverse FK order)."""
    conn = _make_mock_conn()
    cur = conn._cursor

    drop_all_tables(conn)

    execute_calls = [c[0][0].strip() for c in cur.execute.call_args_list]
    assert len(execute_calls) == 3, f"Expected 3 DROP statements, got {len(execute_calls)}"

    # Verify ordering: sagas → events → sessions
    assert "swarm_sagas" in execute_calls[0], f"First drop must target swarm_sagas, got: {execute_calls[0]}"
    assert "events" in execute_calls[1], f"Second drop must target events, got: {execute_calls[1]}"
    assert "sessions" in execute_calls[2], f"Third drop must target sessions, got: {execute_calls[2]}"


def test_bb4_sessions_ddl_contains_if_not_exists():
    """BB4: SESSIONS_DDL uses 'IF NOT EXISTS' to ensure idempotency."""
    assert "IF NOT EXISTS" in SESSIONS_DDL, "SESSIONS_DDL must contain 'IF NOT EXISTS'"


def test_bb5_events_ddl_contains_if_not_exists():
    """BB5: EVENTS_DDL uses 'IF NOT EXISTS' on every CREATE statement."""
    assert "IF NOT EXISTS" in EVENTS_DDL, "EVENTS_DDL must contain 'IF NOT EXISTS'"


def test_bb6_swarm_sagas_ddl_contains_if_not_exists():
    """BB6: SWARM_SAGAS_DDL uses 'IF NOT EXISTS' to ensure idempotency."""
    assert "IF NOT EXISTS" in SWARM_SAGAS_DDL, "SWARM_SAGAS_DDL must contain 'IF NOT EXISTS'"


def test_bb7_create_all_tables_commits_the_transaction():
    """BB7: create_all_tables() must call conn.commit() exactly once."""
    conn = _make_mock_conn()
    create_all_tables(conn)
    conn.commit.assert_called_once()


def test_bb8_drop_all_tables_commits_the_transaction():
    """BB8: drop_all_tables() must call conn.commit() exactly once."""
    conn = _make_mock_conn()
    drop_all_tables(conn)
    conn.commit.assert_called_once()


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


def test_wb1_sessions_table_has_required_columns():
    """WB1: SESSIONS_DDL contains UUID PK, started_at, ended_at, agent_id, metadata columns."""
    ddl = SESSIONS_DDL
    assert "id" in ddl and "UUID PRIMARY KEY" in ddl, "sessions must have UUID PRIMARY KEY column 'id'"
    assert "started_at" in ddl, "sessions must have 'started_at' column"
    assert "ended_at" in ddl, "sessions must have 'ended_at' column"
    assert "agent_id" in ddl, "sessions must have 'agent_id' column"
    assert "metadata" in ddl, "sessions must have 'metadata' column"
    assert "JSONB" in ddl, "metadata column must be JSONB type"


def test_wb2_events_table_has_fk_to_sessions():
    """WB2: EVENTS_DDL defines a foreign key referencing sessions(id)."""
    ddl = EVENTS_DDL
    assert "REFERENCES sessions(id)" in ddl, (
        "events.session_id must have a FK constraint: REFERENCES sessions(id)"
    )


def test_wb3_swarm_sagas_has_status_check_constraint():
    """WB3: SWARM_SAGAS_DDL includes a CHECK constraint on the status column with all four valid values."""
    ddl = SWARM_SAGAS_DDL
    assert "CHECK" in ddl, "swarm_sagas must have a CHECK constraint on status"
    for expected_status in ("RUNNING", "COMPLETED", "PARTIAL_FAIL", "FAILED"):
        assert expected_status in ddl, (
            f"SWARM_SAGAS_DDL CHECK constraint must include status value '{expected_status}'"
        )


def test_wb4_drop_all_tables_uses_cascade():
    """WB4: drop_all_tables() uses CASCADE so FK constraints are automatically dropped."""
    conn = _make_mock_conn()
    cur = conn._cursor

    drop_all_tables(conn)

    executed = " ".join(c[0][0] for c in cur.execute.call_args_list)
    assert "CASCADE" in executed, "drop_all_tables() must use CASCADE in all DROP statements"


def test_wb5_events_and_sagas_have_indexes():
    """WB5: EVENTS_DDL and SWARM_SAGAS_DDL both define at least one CREATE INDEX statement."""
    assert "CREATE INDEX" in EVENTS_DDL, "EVENTS_DDL must define indexes"
    assert "CREATE INDEX" in SWARM_SAGAS_DDL, "SWARM_SAGAS_DDL must define indexes"

    # Specific index names from the spec
    assert "idx_events_session_id" in EVENTS_DDL
    assert "idx_events_type" in EVENTS_DDL
    assert "idx_events_created" in EVENTS_DDL
    assert "idx_sagas_session_id" in SWARM_SAGAS_DDL
    assert "idx_sagas_status" in SWARM_SAGAS_DDL


def test_wb6_create_all_tables_executes_three_ddl_statements():
    """WB6: create_all_tables() calls cur.execute() exactly 3 times (once per table/block)."""
    conn = _make_mock_conn()
    cur = conn._cursor

    create_all_tables(conn)

    assert cur.execute.call_count == 3, (
        f"Expected 3 cur.execute() calls (one per DDL block), got {cur.execute.call_count}"
    )


def test_wb7_create_all_tables_execution_order():
    """WB7: create_all_tables() executes sessions DDL first, events second, sagas third (FK order)."""
    conn = _make_mock_conn()
    cur = conn._cursor

    create_all_tables(conn)

    calls = cur.execute.call_args_list
    assert len(calls) == 3

    first_stmt = calls[0][0][0]
    second_stmt = calls[1][0][0]
    third_stmt = calls[2][0][0]

    assert "sessions" in first_stmt, f"First DDL must create sessions, got: {first_stmt[:80]}"
    assert "events" in second_stmt, f"Second DDL must create events, got: {second_stmt[:80]}"
    assert "swarm_sagas" in third_stmt, f"Third DDL must create swarm_sagas, got: {third_stmt[:80]}"


# ===========================================================================
# Package import / export tests
# ===========================================================================


def test_package_exports_create_all_tables():
    """Package level: create_all_tables importable from core.storage."""
    from core.storage import create_all_tables as _fn
    assert _fn is create_all_tables


def test_package_exports_drop_all_tables():
    """Package level: drop_all_tables importable from core.storage."""
    from core.storage import drop_all_tables as _fn
    assert _fn is drop_all_tables


def test_package_exports_all_ddl_constants():
    """Package level: all three DDL constants importable from core.storage."""
    from core.storage import SESSIONS_DDL as s, EVENTS_DDL as e, SWARM_SAGAS_DDL as g
    assert s is SESSIONS_DDL
    assert e is EVENTS_DDL
    assert g is SWARM_SAGAS_DDL


# ===========================================================================
# Standalone runner (pytest preferred, but fallback to direct execution)
# ===========================================================================

if __name__ == "__main__":
    import traceback

    tests_run = 0
    tests_passed = 0

    def _run(name: str, fn):
        global tests_run, tests_passed
        tests_run += 1
        try:
            fn()
            print(f"  [PASS] {name}")
            tests_passed += 1
        except Exception as exc:
            print(f"  [FAIL] {name}: {exc}")
            traceback.print_exc()

    _run("BB1: create_all_tables no error", test_bb1_create_all_tables_runs_without_error)
    _run("BB2: create_all_tables idempotent", test_bb2_create_all_tables_is_idempotent)
    _run("BB3: drop order reversed", test_bb3_drop_all_tables_drops_in_reverse_fk_order)
    _run("BB4: SESSIONS_DDL IF NOT EXISTS", test_bb4_sessions_ddl_contains_if_not_exists)
    _run("BB5: EVENTS_DDL IF NOT EXISTS", test_bb5_events_ddl_contains_if_not_exists)
    _run("BB6: SWARM_SAGAS_DDL IF NOT EXISTS", test_bb6_swarm_sagas_ddl_contains_if_not_exists)
    _run("BB7: create commits", test_bb7_create_all_tables_commits_the_transaction)
    _run("BB8: drop commits", test_bb8_drop_all_tables_commits_the_transaction)
    _run("WB1: sessions columns", test_wb1_sessions_table_has_required_columns)
    _run("WB2: events FK to sessions", test_wb2_events_table_has_fk_to_sessions)
    _run("WB3: sagas CHECK constraint", test_wb3_swarm_sagas_has_status_check_constraint)
    _run("WB4: drop uses CASCADE", test_wb4_drop_all_tables_uses_cascade)
    _run("WB5: indexes defined", test_wb5_events_and_sagas_have_indexes)
    _run("WB6: 3 execute calls", test_wb6_create_all_tables_executes_three_ddl_statements)
    _run("WB7: create DDL order", test_wb7_create_all_tables_execution_order)
    _run("PKG: create_all_tables export", test_package_exports_create_all_tables)
    _run("PKG: drop_all_tables export", test_package_exports_drop_all_tables)
    _run("PKG: DDL constants export", test_package_exports_all_ddl_constants)

    print(f"\n{tests_passed}/{tests_run} tests passed")
    if tests_passed == tests_run:
        print("ALL TESTS PASSED -- Story 5.01 (Track B)")
    else:
        sys.exit(1)