-- Profile sync RPC + insert policy (safe if 006 already applied)

DO $$ BEGIN
  CREATE TYPE portal_role AS ENUM ('league_master', 'manager');
EXCEPTION
  WHEN duplicate_object THEN NULL;
END $$;

ALTER TABLE profiles
  ADD COLUMN IF NOT EXISTS portal_role portal_role NOT NULL DEFAULT 'manager';

-- Backfill portal_role from signup metadata
UPDATE profiles p
SET portal_role = (u.raw_user_meta_data->>'portal_role')::portal_role
FROM auth.users u
WHERE u.id = p.id
  AND u.raw_user_meta_data->>'portal_role' IN ('league_master', 'manager');

CREATE OR REPLACE FUNCTION ensure_user_profile(
  p_display_name TEXT DEFAULT NULL,
  p_portal_role portal_role DEFAULT 'manager'
)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  IF auth.uid() IS NULL THEN
    RAISE EXCEPTION 'Not authenticated';
  END IF;

  INSERT INTO public.profiles (id, display_name, portal_role)
  VALUES (
    auth.uid(),
    COALESCE(
      p_display_name,
      (SELECT COALESCE(raw_user_meta_data->>'display_name', email) FROM auth.users WHERE id = auth.uid())
    ),
    p_portal_role
  )
  ON CONFLICT (id) DO UPDATE SET
    display_name = COALESCE(EXCLUDED.display_name, profiles.display_name),
    portal_role = COALESCE(EXCLUDED.portal_role, profiles.portal_role);
END;
$$;

GRANT EXECUTE ON FUNCTION ensure_user_profile(TEXT, portal_role) TO authenticated;

DROP POLICY IF EXISTS profiles_insert_own ON profiles;
CREATE POLICY profiles_insert_own ON profiles
  FOR INSERT TO authenticated
  WITH CHECK (id = auth.uid());