migrate_csv_to_supabase.py

"""
Migrate CSV data to Supabase
Run this once to import your existing mushrooms.csv and plants.csv into Supabase
"""
import pandas as pd
from supabase import create_client
from dotenv import load_dotenv
import os

# Load environment variables
load_dotenv()

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

if not SUPABASE_URL or not SUPABASE_KEY:
    print("ERROR: Missing SUPABASE_URL or SUPABASE_KEY in .env file")
    print("Please add your Supabase credentials to .env:")
    print("  SUPABASE_URL=your-supabase-project-url")
    print("  SUPABASE_KEY=your-supabase-anon-key")
    exit(1)

# Initialize Supabase client
supabase = create_client(SUPABASE_URL, SUPABASE_KEY)

def migrate_csv_to_supabase():
    """Migrate mushrooms and plants CSV files to Supabase"""
    
    print("=" * 60)
    print("CSV to Supabase Migration")
    print("=" * 60)
    
    # Read CSV files
    try:
        mushroom_df = pd.read_csv("data/mushrooms.csv")
        plant_df = pd.read_csv("data/plants.csv")
        print(f"✓ Loaded {len(mushroom_df)} mushrooms and {len(plant_df)} plants from CSV")
    except Exception as e:
        print(f"✗ Error reading CSV files: {e}")
        return
    
    # Clear existing data (optional - comment out if you want to keep existing data)
    print("\nClearing existing species data...")
    try:
        supabase.table("species").delete().neq("id", "00000000-0000-0000-0000-000000000000").execute()
        print("✓ Cleared existing species data")
    except Exception as e:
        print(f"Note: Could not clear existing data: {e}")
    
    # Migrate mushrooms
    print(f"\nMigrating {len(mushroom_df)} mushrooms...")
    mushroom_count = 0
    for _, row in mushroom_df.iterrows():
        try:
            species_data = {
                "sno": int(row['sno']),
                "species_name": row['species'],
                "species_type": "mushroom",
                "location": row['location'],
                "latitude": float(row['lat']),
                "longitude": float(row['lon']),
                "added_by": "csv_migration"
            }
            supabase.table("species").insert(species_data).execute()
            mushroom_count += 1
            print(f"  ✓ {row['species']}", end="\r")
        except Exception as e:
            print(f"\n  ✗ Error inserting {row['species']}: {e}")
    
    print(f"\n✓ Migrated {mushroom_count}/{len(mushroom_df)} mushrooms")
    
    # Migrate plants
    print(f"\nMigrating {len(plant_df)} plants...")
    plant_count = 0
    for _, row in plant_df.iterrows():
        try:
            species_data = {
                "sno": int(row['sno']),
                "species_name": row['species'],
                "species_type": "plant",
                "location": row['location'],
                "latitude": float(row['lat']),
                "longitude": float(row['lon']),
                "added_by": "csv_migration"
            }
            supabase.table("species").insert(species_data).execute()
            plant_count += 1
            print(f"  ✓ {row['species']}", end="\r")
        except Exception as e:
            print(f"\n  ✗ Error inserting {row['species']}: {e}")
    
    print(f"\n✓ Migrated {plant_count}/{len(plant_df)} plants")
    
    # Verify migration
    print("\nVerifying migration...")
    try:
        result = supabase.table("species").select("species_type, count").execute()
        mushrooms_in_db = supabase.table("species").select("*").eq("species_type", "mushroom").execute()
        plants_in_db = supabase.table("species").select("*").eq("species_type", "plant").execute()
        
        print(f"✓ Database now contains:")
        print(f"  - {len(mushrooms_in_db.data)} mushrooms")
        print(f"  - {len(plants_in_db.data)} plants")
        print(f"  - Total: {len(mushrooms_in_db.data) + len(plants_in_db.data)} species")
    except Exception as e:
        print(f"Could not verify: {e}")
    
    print("\n" + "=" * 60)
    print("Migration completed!")
    print("=" * 60)
    print("\nNext steps:")
    print("1. Restart your API server")
    print("2. The API will now use Supabase for data persistence")
    print("3. New species will be saved to Supabase automatically")

if __name__ == "__main__":
    migrate_csv_to_supabase()