import json
import uuid

# Configuration
INPUT_FILE = 'master_index.json'
OUTPUT_FILE = 'reseed_200_vendors.sql'

BIG_COUNTRIES = ['Germany', 'France', 'United Kingdom', 'Italy', 'Spain', 'Poland']
TARGET_COUNTRIES = [
    'Austria', 'Belgium', 'Bulgaria', 'Croatia', 'Cyprus', 'Czechia', 'Denmark', 
    'Estonia', 'Finland', 'France', 'Germany', 'Greece', 'Hungary', 'Ireland', 
    'Italy', 'Latvia', 'Lithuania', 'Luxembourg', 'Malta', 'Netherlands', 'Norway', 
    'Poland', 'Portugal', 'Romania', 'Slovakia', 'Slovenia', 'Spain', 'Sweden', 
    'Switzerland', 'United Kingdom'
]

def clean_sql(text):
    if not text: return ""
    return str(text).replace("'", "''")

def generate_sql():
    try:
        with open(INPUT_FILE, 'r', encoding='utf-8') as f:
            data = json.load(f)
    except FileNotFoundError:
        print(f"Error: {INPUT_FILE} not found.")
        return

    vendors_by_country = {country: [] for country in TARGET_COUNTRIES}
    
    # Sort vendors into country buckets
    for v in data.get('vendors', []):
        country = v.get('countries_served', [None])[0]
        if country in vendors_by_country:
            vendors_by_country[country].append(v)

    sql_statements = []
    
    # Boilerplate Setup
    sql_statements.append("-- ============================================")
    sql_statements.append("-- AUTO-GENERATED COMPLETE VENDOR RESEED")
    sql_statements.append("-- ============================================")
    sql_statements.append("ALTER TABLE public.profiles DISABLE ROW LEVEL SECURITY;")
    sql_statements.append("ALTER TABLE public.vendors DISABLE ROW LEVEL SECURITY;")

    # Re-declare the helper if not exists (to ensure the script is self-contained)
    sql_statements.append("""
CREATE OR REPLACE FUNCTION public.create_user(user_id uuid, email text, password text, user_role text) RETURNS void AS $$
BEGIN
    INSERT INTO auth.users (instance_id, id, aud, role, email, encrypted_password, email_confirmed_at, raw_app_meta_data, raw_user_meta_data, created_at, updated_at)
    VALUES ('00000000-0000-0000-0000-000000000000', user_id, 'authenticated', 'authenticated', email, crypt(password, gen_salt('bf')), NOW(), 
    jsonb_build_object('provider', 'email', 'providers', ARRAY['email']), jsonb_build_object('role', user_role), NOW(), NOW());
    INSERT INTO auth.identities (id, provider_id, user_id, identity_data, provider, last_sign_in_at, created_at, updated_at)
    VALUES (gen_random_uuid(), gen_random_uuid(), user_id, format('{"sub":"%s","email":"%s"}', user_id::text, email)::jsonb, 'email', NOW(), NOW(), NOW());
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
""")

    total_count = 0
    for country, vendor_list in vendors_by_country.items():
        limit = 10 if country in BIG_COUNTRIES else 5
        selected_vendors = vendor_list[:limit]
        
        if not selected_vendors: continue

        sql_statements.append(f"\n-- VENDORS FOR {country.upper()}")
        
        for v in selected_vendors:
            u_id = str(uuid.uuid4())
            v_id = str(uuid.uuid4())
            
            name = clean_sql(v['legal_name'])
            email = v['primary_procurement_contact']['email']
            contact_name = clean_sql(v['primary_procurement_contact']['full_name'])
            phone = v['primary_procurement_contact'].get('direct_phone', '+00 000 000')
            reg_num = v.get('registration_number', f'REG-{uuid.uuid4().hex[:6]}')
            addr = clean_sql(v['general_inquiry']['physical_address'])
            city = addr.split(',')[1].strip() if ',' in addr else "City"
            
            # 1. Create Auth User
            sql_statements.append(f"SELECT public.create_user('{u_id}', '{email}', 'Vendor@123', 'vendor');")

            # 2. Insert Profile
            sql_statements.append(
                f"INSERT INTO public.profiles (id, email, full_name, role, organization_name, phone, address, city, country, is_active) "
                f"VALUES ('{u_id}', '{email}', '{contact_name}', 'vendor', '{name}', '{phone}', '{addr}', '{city}', '{country}', true);"
            )

            # 3. Insert Vendor Details
            categories = "{" + ",".join([f'"{c}"' for c in v.get('primary_categories', [])]) + "}"
            markets = "{" + ",".join([f'"{m}"' for m in v.get('countries_served', [])]) + "}"
            rating = round(v.get('confidence_score', 80) / 20.0, 1)
            
            sql_statements.append(
                f"INSERT INTO public.vendors (id, user_id, vendor_name, company_registration, vendor_type, rating, contact_person, contact_email, contact_phone, address, city, country, is_verified, vat_id, target_markets, interested_categories, duns_number, economic_role) "
                f"VALUES ('{v_id}', '{u_id}', '{name}', '{reg_num}', '{v['primary_categories'][0]}', {rating}, '{contact_name}', '{email}', '{phone}', '{addr}', '{city}', '{country}', true, '{v.get('vat_number', '')}', '{markets}', '{categories}', '{v.get('vendor_id', '')}', 'distributor');"
            )
            total_count += 1

    sql_statements.append("\nALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;")
    sql_statements.append("ALTER TABLE public.vendors ENABLE ROW LEVEL SECURITY;")
    sql_statements.append(f"\n-- FINISHED: Created {total_count} Vendors")

    with open(OUTPUT_FILE, 'w', encoding='utf-8') as f:
        f.write("\n".join(sql_statements))
    
    print(f"✅ Generated {OUTPUT_FILE} with {total_count} vendors.")

if __name__ == "__main__":
    generate_sql()