import os
import uuid
import json
import asyncio
from app.services.infra.database import bootstrap_tenant, upsert_thread_analytics, get_db_connection

def verify_schema_and_upsert():
    tenant_id = f"test_sentiment_{uuid.uuid4().hex[:6]}"
    print(f"--- Testing Schema for Tenant: {tenant_id} ---")
    
    try:
        # 1. Bootstrap (should create tables and add new columns if they exist)
        bootstrap_tenant(tenant_id)
        print("1. Bootstrap successful.")

        # 2. Verify columns exist in Postgres
        conn = get_db_connection()
        with conn.cursor() as cur:
            cur.execute(f"SELECT column_name FROM information_schema.columns WHERE table_schema = '{tenant_id}' AND table_name = 'strategist_thread_analytics'")
            columns = [row[0] for row in cur.fetchall()]
            print(f"Detected columns: {columns}")
            
            assert "positive_points" in columns, "Missing positive_points column"
            assert "key_concerns" in columns, "Missing key_concerns column"
        print("2. Schema verification successful.")

        # 3. Test Upsert with new fields
        test_data = {
            "thread_id": "test_thread_1",
            "sentiment_score": 0.8,
            "intent": "general",
            "is_high_intent": False,
            "is_lead_qualified": True,
            "is_resolved": True,
            "escalation_needed": False,
            "positive_points": "- User was happy with the speed\n- User praised the AI's clarity",
            "key_concerns": None,
            "pain_point": "None",
            "feature_request": None,
            "objection": None,
            "cta_clicked": True,
            "summary": "Positive feedback Session",
            "metadata": {"test": True}
        }
        
        upsert_thread_analytics(tenant_id, test_data)
        print("3. Upsert with positive_points successful.")

        # 4. Verify data retrieval
        with conn.cursor() as cur:
            cur.execute(f"SELECT positive_points, key_concerns FROM {tenant_id}.strategist_thread_analytics WHERE thread_id = %s", ("test_thread_1",))
            row = cur.fetchone()
            print(f"Retrieved row: {row}")
            assert row[0] == test_data["positive_points"]
            assert row[1] == test_data["key_concerns"]
        print("4. Data verification successful.")

        print("--- ALL DB VERIFICATIONS PASSED ---")
        
    except Exception as e:
        print(f"--- VERIFICATION FAILED: {e} ---")
        raise
    finally:
        if 'conn' in locals():
            conn.close()

if __name__ == "__main__":
    verify_schema_and_upsert()