#!/usr/bin/env python3
"""Fix Supabase signup: apply missing search_path, sync missing profiles, and test"""
import psycopg2
import json
import urllib.request
import urllib.error
import glob

SUPABASE_URL = "https://mitlbxlqjibfcxswgmbq.supabase.co"

conn = psycopg2.connect(
    host='aws-1-eu-central-1.pooler.supabase.com',
    port=5432,
    dbname='postgres',
    user='postgres.mitlbxlqjibfcxswgmbq',
    password='Veteroner06.,'
)
conn.autocommit = True
cur = conn.cursor()

# ============================================
# FIX 1: Recreate handle_new_user with explicit search_path
# ============================================
print("=== FIX 1: Recreate handle_new_user with SET search_path ===")
cur.execute("""
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.user_profiles (id, display_name, settings)
  VALUES (
    NEW.id,
    COALESCE(NEW.raw_user_meta_data->>'display_name', split_part(NEW.email, '@', 1)),
    jsonb_build_object(
      'theme', 'light',
      'language', 'tr',
      'notifications', true,
      'default_period', '1M'
    )
  )
  ON CONFLICT (id) DO NOTHING;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;
""")
print("  handle_new_user() recreated with SET search_path = public")

# Verify
cur.execute("SELECT proconfig FROM pg_proc WHERE proname = 'handle_new_user'")
print("  New proconfig:", cur.fetchone()[0])

# ============================================
# FIX 2: Also fix is_admin function search_path (already has it but verify)
# ============================================
print("\n=== FIX 2: Verify is_admin function ===")
cur.execute("SELECT proconfig FROM pg_proc WHERE proname = 'is_admin'")
config = cur.fetchone()
print("  is_admin proconfig:", config[0] if config else "NOT FOUND")

# ============================================
# FIX 3: Add missing INSERT policy on user_profiles
# ============================================
print("\n=== FIX 3: Add missing INSERT policy on user_profiles ===")
try:
    cur.execute("""
    CREATE POLICY "Users can insert own profile"
      ON user_profiles FOR INSERT
      WITH CHECK (auth.uid() = id);
    """)
    print("  INSERT policy created!")
except Exception as e:
    if 'already exists' in str(e):
        print("  INSERT policy already exists")
    else:
        print("  Error:", e)

# ============================================
# FIX 4: Sync missing user_profiles for existing users
# ============================================
print("\n=== FIX 4: Sync missing user_profiles ===")
cur.execute("""
SELECT u.id, u.email, u.raw_user_meta_data
FROM auth.users u
LEFT JOIN user_profiles up ON u.id = up.id
WHERE up.id IS NULL
""")
missing = cur.fetchall()
print(f"  Found {len(missing)} users without profiles")

for uid, email, meta in missing:
    display = email.split('@')[0] if email else 'unknown'
    if meta and isinstance(meta, dict) and 'display_name' in meta:
        display = meta['display_name']
    
    try:
        cur.execute("""
        INSERT INTO user_profiles (id, display_name, settings)
        VALUES (%s, %s, %s::jsonb)
        ON CONFLICT (id) DO NOTHING
        """, (uid, display, '{"theme":"light","language":"tr","notifications":true,"default_period":"1M"}'))
        print(f"  Created profile for: {email}")
    except Exception as e:
        print(f"  FAILED for {email}: {e}")

# ============================================
# FIX 5: Ensure trigger is properly attached
# ============================================
print("\n=== FIX 5: Re-attach trigger ===")
cur.execute("DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users")
cur.execute("""
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW
  EXECUTE FUNCTION public.handle_new_user();
""")
print("  Trigger re-created")

# ============================================
# Verify final state
# ============================================
print("\n=== Verification ===")
cur.execute("SELECT count(*) FROM auth.users")
auth_count = cur.fetchone()[0]
cur.execute("SELECT count(*) FROM user_profiles")
prof_count = cur.fetchone()[0]
print(f"  auth.users: {auth_count}, user_profiles: {prof_count}")

# Check policies
cur.execute("""
SELECT policyname, cmd FROM pg_policies WHERE tablename = 'user_profiles'
""")
print("  Policies:")
for row in cur.fetchall():
    print(f"    {row[0]} ({row[1]})")

conn.close()

# ============================================
# TEST: Try signup via REST API
# ============================================
print("\n=== TEST: Trying signup via REST API ===")
anon_key = None
for f in glob.glob('/Volumes/LaCie/borsa_uygulamasi/nextjs-app/.env.local'):
    with open(f) as fh:
        for line in fh:
            if 'SUPABASE_ANON_KEY' in line and '=' in line:
                val = line.split('=', 1)[1].strip()
                if val and val != 'your_supabase_anon_key':
                    anon_key = val

if anon_key:
    import random
    test_email = f"test_{random.randint(1000,9999)}@testfix.com"
    data = json.dumps({
        "email": test_email,
        "password": "TestPass123!"
    }).encode()
    
    req = urllib.request.Request(
        f"{SUPABASE_URL}/auth/v1/signup",
        data=data,
        headers={
            "apikey": anon_key,
            "Content-Type": "application/json"
        }
    )
    try:
        resp = urllib.request.urlopen(req, timeout=15)
        result = json.loads(resp.read())
        print(f"  SUCCESS! User created: {result.get('user', {}).get('email', 'N/A')}")
        print(f"  User ID: {result.get('user', {}).get('id', 'N/A')[:16]}...")
        
        # Check if profile was auto-created
        conn2 = psycopg2.connect(
            host='aws-1-eu-central-1.pooler.supabase.com',
            port=5432,
            dbname='postgres',
            user='postgres.mitlbxlqjibfcxswgmbq',
            password='Veteroner06.,'
        )
        cur2 = conn2.cursor()
        uid = result.get('user', {}).get('id', '')
        cur2.execute("SELECT id, display_name FROM user_profiles WHERE id = %s", (uid,))
        prof = cur2.fetchone()
        if prof:
            print(f"  Profile auto-created: display_name={prof[1]}")
        else:
            print(f"  WARNING: Profile NOT auto-created (trigger may still have issues)")
        
        # Clean up test user
        cur2.execute("DELETE FROM user_profiles WHERE id = %s", (uid,))
        cur2.execute("DELETE FROM auth.identities WHERE user_id = %s", (uid,))
        cur2.execute("DELETE FROM auth.users WHERE id = %s", (uid,))
        conn2.commit()
        print(f"  Test user cleaned up")
        conn2.close()
        
    except urllib.error.HTTPError as e:
        body = e.read().decode()
        print(f"  STILL FAILING - HTTP {e.code}: {body}")
    except Exception as e:
        print(f"  Error: {e}")
else:
    print("  Could not find SUPABASE_ANON_KEY")

print("\n=== All fixes applied! ===")