#!/usr/bin/env python3
"""
Test script to simulate what happens when the API tries to insert a species
This will help us see the ACTUAL error from Supabase
"""

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_insert_with_validation():
    """Test inserting a species with proper response validation"""
    
    if not SUPABASE_URL or not SUPABASE_KEY:
        print("❌ ERROR: SUPABASE_URL or SUPABASE_KEY not found in .env file")
        return
    
    try:
        # Initialize Supabase client
        supabase: Client = create_client(SUPABASE_URL, SUPABASE_KEY)
        
        # Test data - similar to what the API would send
        species_data = {
            "sno": 88888,
            "species_name": "Test Plant Species",
            "species_type": "plant",
            "location": "QSC",
            "latitude": 12.92000,
            "longitude": 80.12100,
            "added_by": "test_user",
            "notes": "Testing insert validation"
        }
        
        print("=" * 60)
        print("TESTING SPECIES INSERT WITH VALIDATION")
        print("=" * 60)
        print(f"\n[DEBUG] Attempting to insert species data:")
        print(f"  Species: {species_data['species_name']}")
        print(f"  Location: {species_data['location']}")
        print(f"  Type: {species_data['species_type']}")
        print()
        
        # This is what the API does
        response = supabase.table("species").insert(species_data).execute()
        
        print(f"[DEBUG] Response received:")
        print(f"  response.data: {response.data}")
        print(f"  response.count: {response.count}")
        print(f"  Type: {type(response.data)}")
        print(f"  Length: {len(response.data) if response.data else 'None'}")
        print()
        
        # Check if the insert was successful (NEW VALIDATION)
        if not response.data or len(response.data) == 0:
            print("❌ [ERROR] Insert failed - no data returned!")
            print(f"   Full response: {response}")
            print()
            print("🔍 DIAGNOSIS:")
            print("   The insert was called but Supabase returned no data.")
            print("   This usually means:")
            print("   1. RLS policy blocked the insert (permission denied)")
            print("   2. A constraint was violated (duplicate key)")
            print("   3. The insert was rolled back due to an error")
            return False
        
        print(f"✅ [INFO] Successfully saved {species_data['species_name']} to Supabase")
        print(f"[DEBUG] Insert response: {response.data}")
        print()
        
        # Verify by reading it back
        inserted_id = response.data[0].get('id')
        print(f"[VERIFY] Reading back inserted record (ID: {inserted_id})...")
        verify = supabase.table("species").select("*").eq("id", inserted_id).execute()
        
        if verify.data and len(verify.data) > 0:
            print("✅ Verification successful - data exists in database!")
            print(f"   Data: {verify.data[0]}")
        else:
            print("❌ Verification FAILED - data not found!")
            return False
        
        # Clean up
        print()
        print("[CLEANUP] Deleting test record...")
        supabase.table("species").delete().eq("id", inserted_id).execute()
        print("✅ Test record deleted")
        print()
        
        print("=" * 60)
        print("✅ TEST PASSED - Insert validation is working!")
        print("=" * 60)
        return True
        
    except Exception as e:
        print()
        print("=" * 60)
        print("❌ EXCEPTION CAUGHT")
        print("=" * 60)
        print(f"Error type: {type(e).__name__}")
        print(f"Error message: {str(e)}")
        print()
        
        error_str = str(e).lower()
        
        if "duplicate key" in error_str or "unique constraint" in error_str:
            print("🔍 DIAGNOSIS: Duplicate Key Violation")
            print("   A species with this name already exists at this location.")
            print("   Solution: The API should return HTTP 409 with a clear message.")
        elif "permission denied" in error_str or "policy" in error_str or "rls" in error_str:
            print("🔍 DIAGNOSIS: Row Level Security (RLS) Policy Issue")
            print("   The RLS policy is blocking the insert.")
            print("   Solution: Run fix_species_insert.sql in Supabase SQL Editor")
        elif "violates not-null constraint" in error_str:
            print("🔍 DIAGNOSIS: Missing Required Field")
            print("   A required field is missing from the insert data.")
        else:
            print("🔍 DIAGNOSIS: Unknown Error")
            print("   Check the error message above for details.")
        
        print()
        return False

if __name__ == "__main__":
    test_insert_with_validation()