FINAL_DATABASE_UPDATE.py

#!/usr/bin/env python3
"""
FINAL DATABASE UPDATE
Updates database with accurate data from correct source files:
1. 74 cells from villages_by_division.csv
2. 189 mobilizers from CELL MOBILIZERS - Commissioned.xlsx (Team: Sam Kanyamukiza)
3. 72 polling stations from updated polling stations final (72).xlsx
"""
import os
import csv
import pandas as pd
from supabase import create_client
from collections import defaultdict

# Initialize Supabase
SUPABASE_URL = os.environ.get('SUPABASE_URL', '').strip()
SUPABASE_KEY = os.environ.get('SUPABASE_KEY', '').strip()

if not SUPABASE_URL or not SUPABASE_KEY:
    print("[ERROR] Environment variables not set")
    print("Please ensure SUPABASE_URL and SUPABASE_KEY are set")
    exit(1)

supabase = create_client(SUPABASE_URL, SUPABASE_KEY)

print("=" * 80)
print("FINAL DATABASE UPDATE - ACCURATE DATA")
print("=" * 80)
print(f"Supabase URL: {SUPABASE_URL[:40]}...")
print()

# ============================================================================
# STEP 1: UPDATE CELLS (74 from villages_by_division.csv)
# ============================================================================
print("[STEP 1/3] Updating 74 Cells from villages_by_division.csv")
print("-" * 80)

cells_by_division = defaultdict(list)

with open('villages_by_division.csv', 'r', encoding='utf-8') as f:
    reader = csv.DictReader(f)
    for row in reader:
        division = row.get('Division', '').strip()
        village = row.get('Village', '').strip()

        if division and village:
            cells_by_division[division].append(village)

total_cells = sum(len(cells) for cells in cells_by_division.values())
print(f"Found {total_cells} cells across {len(cells_by_division)} divisions")

# Clear existing cells
try:
    result = supabase.table('cells').delete().neq('id', 0).execute()
    print("Cleared existing cells table")
except Exception as e:
    print(f"Note clearing cells: {e}")

# Insert correct 74 cells
cell_count = 0
for division, villages in sorted(cells_by_division.items()):
    print(f"\n{division}: {len(villages)} cells")

    for village in sorted(villages):
        cell_count += 1

        cell_data = {
            'cell_name': village,
            'cell_code': f"CELL-{cell_count:03d}",
            'division': division,
            'ward': None,
            'total_voters': 0
        }

        try:
            supabase.table('cells').insert(cell_data).execute()
            if cell_count % 10 == 0 or cell_count <= 5:
                print(f"  [{cell_count:2}/{total_cells}] {village}")
        except Exception as e:
            print(f"  [ERROR] {village}: {e}")

print(f"\n[OK] Inserted {cell_count}/74 cells")

# ============================================================================
# STEP 2: LOAD MOBILIZERS (189 from CELL MOBILIZERS - Commissioned.xlsx)
# ============================================================================
print("\n[STEP 2/3] Loading Mobilizers (Sam Kanyamukiza's Team)")
print("-" * 80)

# Read Excel file
df = pd.read_excel('CELL MOBILIZERS - Commissioned.xlsx', header=1)
df = df.dropna(how='all')
df = df.iloc[1:]  # Skip the header row

# Clean column names
df.columns = ['index', 'name', 'village', 'ward', 'polling_station', 'phone']

# Remove rows with empty names
df = df[df['name'].notna()]
df = df[df['name'] != 'NAME']

print(f"Found {len(df)} mobilizers in file")

# Clear existing mobilizers
try:
    result = supabase.table('mobilizers').delete().neq('id', 0).execute()
    print("Cleared existing mobilizers table")
except Exception as e:
    print(f"Note: Could not clear mobilizers: {e}")
    print("Table may not exist yet - will create records anyway")

mobilizer_count = 0
for _, row in df.iterrows():
    name = str(row['name']).strip() if pd.notna(row['name']) else ''
    village = str(row['village']).strip() if pd.notna(row['village']) else None
    ward = str(row['ward']).strip() if pd.notna(row['ward']) else None
    polling_station = str(row['polling_station']).strip() if pd.notna(row['polling_station']) else None
    phone = str(row['phone']).strip() if pd.notna(row['phone']) else None

    # Skip invalid entries
    if not name or len(name) < 3 or name.lower() in ['nan', 'name']:
        continue

    # Clean phone number
    if phone and phone.lower() != 'nan':
        phone = phone.replace(' ', '').replace('-', '')
    else:
        phone = None

    # Match village to division
    division = None
    if village and village.lower() != 'nan':
        with open('villages_by_division.csv', 'r') as f:
            reader = csv.DictReader(f)
            for vrow in reader:
                if vrow.get('Village', '').strip().upper() == village.upper():
                    division = vrow.get('Division', '').strip()
                    break

    mobilizer_data = {
        'name': name,
        'phone': phone,
        'village': village if village and village.lower() != 'nan' else None,
        'ward': ward if ward and ward.lower() != 'nan' else None,
        'division': division,
        'polling_station': polling_station if polling_station and polling_station.lower() != 'nan' else None,
        'role': 'Cell Mobilizer',
        'status': 'Active',
        'team_leader': 'Sam Kanyamukiza'
    }

    try:
        supabase.table('mobilizers').insert(mobilizer_data).execute()
        mobilizer_count += 1
        if mobilizer_count <= 10 or mobilizer_count % 20 == 0:
            print(f"  [{mobilizer_count:3}] {name:30} | {village or 'Unassigned':20} | {phone or 'No phone'}")
    except Exception as e:
        print(f"  [ERROR] {name}: {e}")

print(f"\n[OK] Inserted {mobilizer_count} mobilizers under Chief Mobilizer Sam Kanyamukiza")

# ============================================================================
# STEP 3: UPDATE POLLING STATIONS (72 from updated polling stations final (72).xlsx)
# ============================================================================
print("\n[STEP 3/3] Loading 72 Polling Stations")
print("-" * 80)

df_stations = pd.read_excel('updated polling stations final (72).xlsx')

stations = []
current_division = None
current_ward = None

for idx, row in df_stations.iterrows():
    col1 = str(row['Unnamed: 1']).strip() if pd.notna(row['Unnamed: 1']) else ''
    col2 = str(row['Unnamed: 2']).strip() if pd.notna(row['Unnamed: 2']) else ''
    col3 = str(row['Unnamed: 3']).strip() if pd.notna(row['Unnamed: 3']) else ''

    # Check if this is a division header
    if 'DIVISION' in col1.upper():
        current_division = col1.replace('DIVISION', '').strip()
        current_division = f"{current_division.split()[0][:2]} KABALE {current_division.split()[0]}"
        continue

    # Check if this is a ward header
    if col2 and not col2.isdigit() and (col3 == 'nan' or not col3):
        current_ward = col2
        continue

    # Check if this is a polling station (has a number)
    if col2.isdigit():
        stations.append({
            'division': current_division,
            'ward': current_ward,
            'number': int(col2),
            'name': col3,
            'code': f"PS-{len(stations) + 1:03d}"
        })

print(f"Found {len(stations)} polling stations")

# Note: polling_stations table may not exist - showing what would be inserted
print("\nPolling Stations to be inserted:")
for i, station in enumerate(stations[:10], 1):
    print(f"  [{i:2}] {station['name']:50} | {station['ward']:20}")

if len(stations) > 10:
    print(f"  ... and {len(stations) - 10} more")

print(f"\n[INFO] Total of {len(stations)} polling stations ready for import")
if len(stations) == 72:
    print("[OK] Matches expected count of 72!")
else:
    print(f"[WARNING] Expected 72, found {len(stations)}")

# ============================================================================
# FINAL VERIFICATION
# ============================================================================
print("\n" + "=" * 80)
print("DATABASE UPDATE COMPLETE!")
print("=" * 80)

try:
    cells_response = supabase.table('cells').select('*', count='exact').execute()
    mobilizers_response = supabase.table('mobilizers').select('*', count='exact').execute()

    cells_actual = cells_response.count if hasattr(cells_response, 'count') else len(cells_response.data)
    mobilizers_actual = mobilizers_response.count if hasattr(mobilizers_response, 'count') else len(mobilizers_response.data)

    print(f"\nFinal Database Counts:")
    print(f"  Cells:           {cells_actual:3} (expected: 74)")
    print(f"  Mobilizers:      {mobilizers_actual:3} (expected: ~189)")
    print(f"  Polling Stations: {len(stations):3} (expected: 72 - ready for import)")
    print(f"\nMobilizers Team:")
    print(f"  Chief Mobilizer: Sam Kanyamukiza")
    print(f"  Team Size:       {mobilizers_actual} cell mobilizers")

    # Check if targets met
    if cells_actual == 74:
        print("\n[OK] Cells: PERFECT!")
    else:
        print(f"\n[WARNING] Cells: Expected 74, got {cells_actual}")

    if len(stations) == 72:
        print("[OK] Polling Stations: PERFECT!")
    else:
        print(f"[WARNING] Polling Stations: Expected 72, got {len(stations)}")

except Exception as e:
    print(f"\nError during verification: {e}")

print("\n" + "=" * 80)
print("IMPORTANT NEXT STEPS:")
print("=" * 80)
print("1. [URGENT] Add SUPABASE_KEY to Render environment variables")
print("   - Go to: https://dashboard.render.com/web/srv-csu4fbdv2p9s73auo7o0/env")
print("   - Add: SUPABASE_KEY = <your anon key>")
print("   - Wait for auto-redeploy (~2 min)")
print()
print("2. Create polling_stations table if needed")
print()
print("3. Match 1,000 call center contacts with voter records")
print("=" * 80)