-- Migration: 20240628_elevate_admin_phone.sql
-- Automatically detect and elevate the admin phone number to super_admin on registration or logic updates.

-- 1. Elevate profile trigger handler function
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
DECLARE
  default_role user_role := 'buyer';
BEGIN
  -- Check if phone matches the administrator phone target: 0505294417
  IF new.phone LIKE '%0505294417' OR new.phone LIKE '%505294417' THEN
    default_role := 'super_admin';
  END IF;

  INSERT INTO public.profiles (id, phone_number, role)
  VALUES (new.id, new.phone, default_role)
  ON CONFLICT (id) DO UPDATE SET 
    role = EXCLUDED.role,
    phone_number = EXCLUDED.phone_number,
    updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 2. Retroactively upgrade any profile with the target phone number
UPDATE public.profiles 
SET role = 'super_admin' 
WHERE phone_number LIKE '%0505294417' 
   OR phone_number LIKE '%505294417';