File size: 5,526 Bytes
8deea46 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 |
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() |