from sqlalchemy import inspect, text

from services.shared.db import engine
from services.shared.sql_init import init_sql_schema


def test_init_sql_schema_backfills_voice_events_source_event_id():
    with engine.begin() as conn:
        conn.execute(text("DROP TABLE IF EXISTS voice_events"))
        conn.execute(
            text(
                """
                CREATE TABLE voice_events (
                  id INTEGER PRIMARY KEY AUTOINCREMENT,
                  event_id VARCHAR(64) NOT NULL UNIQUE,
                  event_type VARCHAR(64) NOT NULL,
                  call_id VARCHAR(128) NOT NULL,
                  interaction_id VARCHAR(64) NULL,
                  payload_json TEXT NOT NULL,
                  created_at VARCHAR(64) NOT NULL
                )
                """
            )
        )

    init_sql_schema()

    inspector = inspect(engine)
    columns = {item["name"] for item in inspector.get_columns("voice_events")}
    indexes = {item["name"] for item in inspector.get_indexes("voice_events")}

    assert "source_event_id" in columns
    assert "ix_voice_events_source_event_id" in indexes


def test_init_sql_schema_backfills_whatsapp_message_runtime_columns():
    with engine.begin() as conn:
        conn.execute(text("DROP TABLE IF EXISTS whatsapp_messages"))
        conn.execute(
            text(
                """
                CREATE TABLE whatsapp_messages (
                  id INTEGER PRIMARY KEY AUTOINCREMENT,
                  message_id VARCHAR(64) NOT NULL UNIQUE,
                  text TEXT NOT NULL,
                  payload_json TEXT NOT NULL,
                  created_at VARCHAR(64) NOT NULL
                )
                """
            )
        )

    init_sql_schema()

    inspector = inspect(engine)
    columns = {item["name"] for item in inspector.get_columns("whatsapp_messages")}
    indexes = {item["name"] for item in inspector.get_indexes("whatsapp_messages")}

    assert "thread_id" in columns
    assert "chat_id" in columns
    assert "interaction_id" in columns
    assert "customer_id" in columns
    assert "delivery_status" in columns
    assert "author_type" in columns
    assert "ix_whatsapp_messages_chat_external_unique" in indexes
    assert "idx_whatsapp_messages_next_delivery_attempt_at" in indexes


def test_init_sql_schema_backfills_ai_session_context_summary_columns():
    with engine.begin() as conn:
        conn.execute(text("DROP TABLE IF EXISTS ai_sessions"))
        conn.execute(
            text(
                """
                CREATE TABLE ai_sessions (
                  id INTEGER PRIMARY KEY AUTOINCREMENT,
                  session_id VARCHAR(64) NOT NULL UNIQUE,
                  channel VARCHAR(32) NOT NULL,
                  thread_id VARCHAR(64) NULL,
                  interaction_id VARCHAR(64) NULL,
                  customer_id VARCHAR(64) NULL,
                  agent_profile VARCHAR(64) NOT NULL,
                  language VARCHAR(16) NULL,
                  status VARCHAR(32) NOT NULL,
                  summary_text TEXT NOT NULL,
                  last_user_message_id VARCHAR(64) NULL,
                  last_ai_message_id VARCHAR(64) NULL,
                  handoff_reason TEXT NULL,
                  created_at VARCHAR(64) NOT NULL,
                  updated_at VARCHAR(64) NOT NULL,
                  closed_at VARCHAR(64) NULL
                )
                """
            )
        )

    init_sql_schema()

    inspector = inspect(engine)
    columns = {item["name"] for item in inspector.get_columns("ai_sessions")}
    indexes = {item["name"] for item in inspector.get_indexes("ai_sessions")}

    assert "context_summary_json" in columns
    assert "context_summary_updated_at" in columns
    assert "idx_ai_sessions_context_summary_updated_at" in indexes