#!/usr/bin/env python3
"""
Test UUID research with a completely fresh database by dropping existing tables.
"""

import json
import os
import random
import string
from pathlib import Path

import requests

# Allow unencrypted databases for testing
os.environ["LDR_BOOTSTRAP_ALLOW_UNENCRYPTED"] = "true"

# Import after setting environment
import sys

sys.path.insert(
    0,
    str(Path(__file__).parent.parent.parent.resolve()),
)

from sqlalchemy import inspect, text

from local_deep_research.database.auth_db import get_auth_db_session
from local_deep_research.database.encrypted_db import db_manager
from local_deep_research.database.models import ResearchHistory
from local_deep_research.database.models.auth import User

# Base URL for the application
BASE_URL = "http://127.0.0.1:5000"


def drop_and_recreate_research_tables(username):
    """Drop and recreate research tables to ensure UUID schema"""
    print(f"\nDropping and recreating tables for user {username}...")

    engine = db_manager.connections.get(username)
    if not engine:
        print("No engine found for user")
        return False

    try:
        # Drop the research_history table if it exists
        with engine.connect() as conn:
            # Drop dependent tables first
            conn.execute(text("DROP TABLE IF EXISTS research_resources"))
            conn.execute(text("DROP TABLE IF EXISTS research_history"))
            conn.commit()

        print("Dropped existing research tables")

        # Recreate tables with correct schema
        ResearchHistory.__table__.create(engine)
        print("Created research_history table with UUID schema")

        # Verify the schema
        inspector = inspect(engine)
        columns = inspector.get_columns("research_history")
        for col in columns:
            if col["name"] == "id":
                print(f"ID column type: {col['type']}")

        return True

    except Exception as e:
        print(f"Error recreating tables: {e}")
        return False


def test_uuid_with_fresh_schema():
    """Test research creation with fresh UUID schema"""

    # Create a session for maintaining cookies
    session = requests.Session()

    # Generate random user credentials
    username = f"test_uuid_fresh_{''.join(random.choices(string.ascii_lowercase + string.digits, k=8))}"
    password = "T3st!Secure#2024$LDR"

    print(f"Testing with new user: {username}")

    # Step 1: Register new user
    print("\n1. Registering new user...")

    # Get registration page for CSRF token
    reg_page = session.get(f"{BASE_URL}/auth/register")
    csrf_token = None

    # Extract CSRF token
    for line in reg_page.text.split("\n"):
        if 'name="csrf_token"' in line and "value=" in line:
            start = line.find('value="') + 7
            end = line.find('"', start)
            csrf_token = line[start:end]
            break

    # Register the user
    reg_data = {
        "username": username,
        "password": password,
        "confirm_password": password,
        "acknowledge": "true",
        "csrf_token": csrf_token,
    }

    reg_response = session.post(f"{BASE_URL}/auth/register", data=reg_data)
    print(f"Registration status: {reg_response.status_code}")

    if reg_response.status_code != 200:
        print("Registration failed")
        return False

    # Step 2: Drop and recreate tables with UUID schema
    # First need to get the database connection
    auth_db = get_auth_db_session()
    user = auth_db.query(User).filter_by(username=username).first()
    auth_db.close()

    if user:
        # Open the database to establish connection
        engine = db_manager.open_user_database(username, password)
        if engine:
            # Now drop and recreate tables
            drop_and_recreate_research_tables(username)

    # Step 3: Submit a research request
    print("\n3. Submitting research request...")

    # Get fresh CSRF token
    home_page = session.get(f"{BASE_URL}/")
    csrf_token = None

    for line in home_page.text.split("\n"):
        if 'name="csrf_token"' in line and "value=" in line:
            start = line.find('value="') + 7
            end = line.find('"', start)
            csrf_token = line[start:end]
            break

    # Prepare research request
    research_data = {
        "query": f"Test UUID research for {username}",
        "mode": "quick",
        "model_provider": "OLLAMA",
        "model": "llama3.2:3b",
        "search_engine": "searxng",
        "iterations": 1,
        "questions_per_iteration": 2,
    }

    headers = {"Content-Type": "application/json", "X-CSRF-Token": csrf_token}

    research_response = session.post(
        f"{BASE_URL}/api/start_research", json=research_data, headers=headers
    )

    print(f"Research submission status: {research_response.status_code}")

    if research_response.status_code == 200:
        result = research_response.json()
        print(f"Research response: {json.dumps(result, indent=2)}")

        research_id = result.get("research_id")
        print(f"\nResearch ID: {research_id}")
        print(f"Research ID type: {type(research_id)}")

        # Check if it's a UUID format
        if (
            isinstance(research_id, str)
            and len(research_id) == 36
            and research_id.count("-") == 4
        ):
            print("✅ Research ID is in UUID format!")
        else:
            print("❌ Research ID is NOT in UUID format!")

        # Step 4: Verify in database
        print("\n4. Verifying in database...")

        db_session = db_manager.get_session(username)
        if db_session:
            research = (
                db_session.query(ResearchHistory)
                .filter_by(id=research_id)
                .first()
            )
            if research:
                print("Found research in DB:")
                print(
                    f"  ID: {research.id} (type: {type(research.id).__name__})"
                )
                print(f"  Query: {research.query}")
                print(f"  Status: {research.status}")
            else:
                print("Research not found in database")
            db_session.close()

        # Clean up
        db_manager.close_user_database(username)

        return True

    print(f"Research submission failed: {research_response.text}")
    return False


if __name__ == "__main__":
    print("=" * 60)
    print("UUID Fresh Database Test")
    print("=" * 60)

    success = test_uuid_with_fresh_schema()

    print("\n" + "=" * 60)
    print("✅ Test completed" if success else "❌ Test failed")
    print("=" * 60)
