#!/usr/bin/env python3
"""
Test script to diagnose Supabase species insert issues
This will help identify if the problem is with RLS policies, data validation, or something else
"""

import os
from dotenv import load_dotenv
from supabase import create_client, Client

# Load environment variables
load_dotenv()

SUPABASE_URL = os.getenv("SUPABASE_URL", "")
SUPABASE_KEY = os.getenv("SUPABASE_KEY", "")

def test_supabase_insert():
    """Test inserting a species into Supabase"""
    
    if not SUPABASE_URL or not SUPABASE_KEY:
        print("❌ ERROR: SUPABASE_URL or SUPABASE_KEY not found in .env file")
        return False
    
    print(f"✓ Supabase URL: {SUPABASE_URL[:30]}...")
    print(f"✓ Supabase Key: {SUPABASE_KEY[:20]}...")
    print()
    
    try:
        # Initialize Supabase client
        print("1. Initializing Supabase client...")
        supabase: Client = create_client(SUPABASE_URL, SUPABASE_KEY)
        print("   ✓ Client initialized")
        print()
        
        # Test connection by reading species
        print("2. Testing SELECT operation...")
        response = supabase.table("species").select("*").limit(5).execute()
        print(f"   ✓ SELECT successful - found {len(response.data)} species")
        if response.data:
            print(f"   Sample: {response.data[0].get('species_name', 'N/A')}")
        print()
        
        # Get current count
        print("3. Getting current species count...")
        count_response = supabase.table("species").select("sno").execute()
        current_count = len(count_response.data)
        print(f"   ✓ Current species count: {current_count}")
        print()
        
        # Try to insert a test species
        print("4. Testing INSERT operation...")
        test_species_data = {
            "sno": 99999,  # Use a high number to avoid conflicts
            "species_name": "TEST_SPECIES_DELETE_ME",
            "species_type": "plant",
            "location": "QSC",
            "latitude": 12.92000,
            "longitude": 80.12100,
            "added_by": "test_script",
            "notes": "This is a test insert - should be deleted"
        }
        
        print(f"   Inserting: {test_species_data}")
        insert_response = supabase.table("species").insert(test_species_data).execute()
        
        # Check response
        print(f"   Response data: {insert_response.data}")
        print(f"   Response count: {insert_response.count}")
        
        if not insert_response.data or len(insert_response.data) == 0:
            print("   ❌ INSERT FAILED - No data returned!")
            print(f"   Full response: {insert_response}")
            return False
        
        print("   ✓ INSERT successful!")
        inserted_id = insert_response.data[0].get('id')
        print(f"   Inserted ID: {inserted_id}")
        print()
        
        # Verify the insert by reading it back
        print("5. Verifying insert by reading back...")
        verify_response = supabase.table("species").select("*").eq("id", inserted_id).execute()
        if verify_response.data and len(verify_response.data) > 0:
            print("   ✓ Verification successful - data exists in database")
            print(f"   Data: {verify_response.data[0]}")
        else:
            print("   ❌ Verification FAILED - data not found in database!")
            return False
        print()
        
        # Clean up - delete the test record
        print("6. Cleaning up test data...")
        delete_response = supabase.table("species").delete().eq("id", inserted_id).execute()
        print("   ✓ Test data deleted")
        print()
        
        print("=" * 60)
        print("✓ ALL TESTS PASSED!")
        print("=" * 60)
        print()
        print("Your Supabase configuration is working correctly.")
        print("The issue might be:")
        print("  1. Duplicate species_name + location (UNIQUE constraint)")
        print("  2. The API server not using the correct credentials")
        print("  3. Network/firewall issues between API and Supabase")
        print()
        return True
        
    except Exception as e:
        print(f"\n❌ ERROR: {e}")
        print(f"\nFull error details: {type(e).__name__}: {str(e)}")
        
        # Check for common issues
        if "duplicate key" in str(e).lower():
            print("\n⚠️  DIAGNOSIS: Duplicate key violation")
            print("   The species already exists with this name and location.")
            print("   Solution: Check if the species is already in the database.")
        elif "permission denied" in str(e).lower() or "policy" in str(e).lower():
            print("\n⚠️  DIAGNOSIS: Row Level Security (RLS) policy issue")
            print("   Solution: Run fix_species_insert.sql in Supabase SQL Editor")
        elif "connection" in str(e).lower():
            print("\n⚠️  DIAGNOSIS: Connection issue")
            print("   Solution: Check your SUPABASE_URL and network connection")
        elif "authentication" in str(e).lower() or "api key" in str(e).lower():
            print("\n⚠️  DIAGNOSIS: Authentication issue")
            print("   Solution: Check your SUPABASE_KEY is correct")
        
        return False

if __name__ == "__main__":
    print("=" * 60)
    print("SUPABASE SPECIES INSERT DIAGNOSTIC TEST")
    print("=" * 60)
    print()
    
    success = test_supabase_insert()
    
    if not success:
        print("\n❌ Tests failed. Please fix the issues above and try again.")
        exit(1)
    else:
        exit(0)