#!/usr/bin/env python3
"""
Tests for Story 1.01: royal_conversations schema
AIVA RLM Nexus — Track A
"""
import sys
sys.path.insert(0, '/mnt/e/genesis-system/data/genesis-memory')


def test_migration():
    """Run the migration against Elestio PostgreSQL and verify."""
    import psycopg2
    import psycopg2.errors

    PG_HOST = "postgresql-genesis-u50607.vm.elestio.app"
    PG_PORT = 25432
    PG_USER = "postgres"
    PG_PASS = "etY0eog17tD-dDuj--IRH"
    PG_DB = "postgres"

    conn = psycopg2.connect(
        host=PG_HOST,
        port=PG_PORT,
        user=PG_USER,
        password=PG_PASS,
        database=PG_DB
    )
    conn.autocommit = False
    cur = conn.cursor()

    # Run migration (idempotent — CREATE TABLE IF NOT EXISTS)
    with open('/mnt/e/genesis-system/data/migrations/001_royal_conversations.sql', 'r') as f:
        cur.execute(f.read())
    conn.commit()

    # BB1: Table exists with correct schema (14 columns)
    cur.execute("""
        SELECT column_name, data_type
        FROM information_schema.columns
        WHERE table_name = 'royal_conversations'
        ORDER BY ordinal_position
    """)
    columns = cur.fetchall()
    assert len(columns) == 14, f"Expected 14 columns, got {len(columns)}: {columns}"
    print(f"BB1 PASS: {len(columns)} columns found")

    # BB2: INSERT valid row — conversation_id auto-generated
    cur.execute("""
        INSERT INTO royal_conversations (started_at, participants)
        VALUES (NOW(), '{"kinan": true, "aiva": true}'::jsonb)
        RETURNING conversation_id
    """)
    row_id = cur.fetchone()[0]
    assert row_id is not None, "conversation_id should be auto-generated"
    conn.commit()
    print(f"BB2 PASS: auto-generated conversation_id = {row_id}")

    # BB3: Invalid emotional_signal — CHECK constraint must fire
    try:
        cur.execute("""
            INSERT INTO royal_conversations (started_at, emotional_signal)
            VALUES (NOW(), 'invalid_value')
        """)
        conn.commit()
        assert False, "Should have raised CheckViolation for invalid emotional_signal"
    except psycopg2.errors.CheckViolation:
        conn.rollback()
        print("BB3 PASS: CHECK constraint correctly rejected 'invalid_value'")

    # WB1: Re-run migration — must be idempotent (no error if table already exists)
    with open('/mnt/e/genesis-system/data/migrations/001_royal_conversations.sql', 'r') as f:
        cur.execute(f.read())
    conn.commit()
    print("WB1 PASS: migration is idempotent (re-ran without error)")

    # WB3: NULL transcript_raw must be allowed (nullable field)
    cur.execute("""
        INSERT INTO royal_conversations (started_at, transcript_raw)
        VALUES (NOW(), NULL)
        RETURNING conversation_id
    """)
    assert cur.fetchone()[0] is not None, "Row with NULL transcript_raw should be inserted"
    conn.commit()
    print("WB3 PASS: NULL transcript_raw accepted (nullable field confirmed)")

    # Cleanup — remove only test rows inserted in last minute
    cur.execute(
        "DELETE FROM royal_conversations WHERE started_at > NOW() - INTERVAL '1 minute'"
    )
    conn.commit()
    print("Cleanup: test rows removed")

    cur.close()
    conn.close()

    print("\nALL TESTS PASSED — Story 1.01")
    print("Tests: 5/5 PASS (BB1, BB2, BB3, WB1, WB3)")


if __name__ == "__main__":
    test_migration()