Enable OAuth only if you want users to log in with Google/Facebook instead of guest accounts.

1. Supabase → Authentication → Settings:
   • Keep Allow anonymous sign-ins enabled (guest mode).
   • Set Site URL to your published game URL.
   • Add your game URL(s) to Additional Redirect URLs (staging + localhost if needed).
2. Supabase → Authentication → Providers:
   • Enable Google and paste Client ID + Client Secret.
   • Enable Facebook and paste App ID + App Secret.
3. Google Cloud Console: create OAuth credentials (Web app) and add redirect URI:

   https://<YOUR_SUPABASE_PROJECT_REF>.supabase.co/auth/v1/callback

4. Meta for Developers: create Facebook app + Facebook Login (web) and add the same redirect URI:

   https://<YOUR_SUPABASE_PROJECT_REF>.supabase.co/auth/v1/callback

• Tables: player_stats, friendships, plus view friend_list_view.
• Row creation: the SQL script creates a trigger on auth.users to insert a default player_stats row.
• RLS: set up by the SQL script; you can tighten policies based on your needs.
• RPC used by the game: sync_hero_match_progress, sync_lives, use_life, set_lives, apply_bundle_reward.

Source file: docs/Supabase/SQL. Run once per Supabase project.

-- ================================================================
-- HERO MATCH GAME - FULL BACKEND SETUP
-- ================================================================

-- 1. TABLES
-- ================================================================
CREATE TABLE IF NOT EXISTS public.player_stats (
    id uuid REFERENCES auth.users NOT NULL PRIMARY KEY,
    username text,
    coins int4 DEFAULT 3000,
    stars int4 DEFAULT 0,
    level int4 DEFAULT 1,
    lives int4 DEFAULT 5,
    max_lives int4 DEFAULT 5,
    high_score int4 DEFAULT 0,
    game_data jsonb DEFAULT '{}'::jsonb,
    power_ups jsonb DEFAULT '{"rocketV": 3, "tnt": 3, "disco": 3}'::jsonb,
    boosters jsonb DEFAULT '{"hammer": 3, "laserGun": 3, "gauntlet": 3, "powerCore": 3}'::jsonb,
    last_regen_time timestamptz DEFAULT now(),
    updated_at timestamptz DEFAULT now(),
    purchased_bundles text[] DEFAULT '{}'::text[],
    avatar_id int4 DEFAULT 0,
    country_code text,
    short_id text UNIQUE
);

CREATE TABLE IF NOT EXISTS public.friendships (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    sender_id uuid REFERENCES public.player_stats(id),
    receiver_id uuid REFERENCES public.player_stats(id),
    status text DEFAULT 'pending', -- pending, accepted, rejected
    created_at timestamptz DEFAULT now()
);

-- 2. CORE FUNCTIONS
-- ================================================================

-- ID Generator
CREATE OR REPLACE FUNCTION public.generate_short_id()
 RETURNS text LANGUAGE plpgsql SECURITY DEFINER AS $function$
DECLARE
    new_id text;
BEGIN
    LOOP
        new_id := upper(substring(md5(random()::text) from 1 for 6));
        IF NOT EXISTS (SELECT 1 FROM public.player_stats WHERE short_id = new_id) THEN
            RETURN new_id;
        END IF;
    END LOOP;
END; $function$;

-- Progress & Stats Sync
CREATE OR REPLACE FUNCTION public.sync_hero_match_progress(p_level integer, p_coins integer, p_stars integer, p_high_score integer, p_game_data jsonb, p_power_ups jsonb, p_boosters jsonb)
 RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $function$
BEGIN
  INSERT INTO public.player_stats (id, level, coins, stars, high_score, game_data, power_ups, boosters, updated_at)
  VALUES (auth.uid(), p_level, p_coins, p_stars, p_high_score, p_game_data, p_power_ups, p_boosters, now())
  ON CONFLICT (id) DO UPDATE SET
    level = greatest(public.player_stats.level, excluded.level),
    coins = CASE WHEN excluded.level > public.player_stats.level THEN excluded.coins ELSE public.player_stats.coins END,
    stars = CASE WHEN excluded.level > public.player_stats.level THEN excluded.stars ELSE public.player_stats.stars END,
    game_data = CASE WHEN excluded.level > public.player_stats.level THEN excluded.game_data ELSE public.player_stats.game_data END,
    power_ups = CASE WHEN excluded.level > public.player_stats.level THEN excluded.power_ups ELSE public.player_stats.power_ups END,
    boosters = CASE WHEN excluded.level > public.player_stats.level THEN excluded.boosters ELSE public.player_stats.boosters END,
    updated_at = now();
END; $function$;

-- Lives Management
CREATE OR REPLACE FUNCTION public.sync_lives(p_id uuid, regen_seconds integer)
 RETURNS json LANGUAGE plpgsql SECURITY DEFINER AS $function$
DECLARE
  player_rec record;
  now_time timestamp with time zone := now();
  lives_gained int;
  new_lives int;
  new_regen_time timestamp with time zone;
BEGIN
  SELECT * INTO player_rec FROM public.player_stats WHERE id = p_id;
  IF NOT FOUND THEN RETURN json_build_object('error', 'Player not found'); END IF;
  IF player_rec.last_regen_time IS NULL THEN
    UPDATE public.player_stats SET last_regen_time = now_time WHERE id = p_id;
    player_rec.last_regen_time := now_time;
  END IF;
  IF player_rec.lives >= player_rec.max_lives THEN
    RETURN json_build_object('lives', player_rec.lives, 'max_lives', player_rec.max_lives, 'next_regen_seconds', 0);
  END IF;
  lives_gained := floor(extract(epoch from (now_time - player_rec.last_regen_time)) / regen_seconds);
  new_lives := LEAST(player_rec.max_lives, player_rec.lives + lives_gained);
  IF new_lives >= player_rec.max_lives THEN new_regen_time := now_time;
  ELSE new_regen_time := player_rec.last_regen_time + (lives_gained * interval '1 second' * regen_seconds); END IF;
  UPDATE public.player_stats SET lives = new_lives, last_regen_time = new_regen_time, updated_at = now_time WHERE id = p_id;
  RETURN json_build_object('lives', new_lives, 'max_lives', player_rec.max_lives, 'next_regen_seconds', GREATEST(0, regen_seconds - floor(extract(epoch from (now_time - new_regen_time)))::int));
END; $function$;

CREATE OR REPLACE FUNCTION public.use_life(p_id uuid, regen_seconds integer)
 RETURNS json LANGUAGE plpgsql SECURITY DEFINER AS $function$
DECLARE
  current_rec record;
  sync_res json;
BEGIN
  SELECT public.sync_lives(p_id, regen_seconds) INTO sync_res;
  SELECT * INTO current_rec FROM public.player_stats WHERE id = p_id;
  IF current_rec.lives <= 0 THEN
    RETURN json_build_object('error', 'No lives');
  END IF;
  UPDATE public.player_stats SET lives = current_rec.lives - 1, updated_at = now() WHERE id = p_id;
  RETURN json_build_object('lives', current_rec.lives - 1, 'max_lives', current_rec.max_lives);
END; $function$;

CREATE OR REPLACE FUNCTION public.set_lives(p_id uuid, lives integer, regen_seconds integer, max_lives integer)
 RETURNS json LANGUAGE plpgsql SECURITY DEFINER AS $function$
DECLARE
  safe_lives int := GREATEST(0, lives);
  safe_max int := GREATEST(1, max_lives);
BEGIN
  UPDATE public.player_stats
  SET lives = LEAST(safe_lives, safe_max),
      max_lives = safe_max,
      last_regen_time = now(),
      updated_at = now()
  WHERE id = p_id;
  RETURN json_build_object('lives', LEAST(safe_lives, safe_max), 'max_lives', safe_max, 'next_regen_seconds', 0);
END; $function$;

CREATE OR REPLACE FUNCTION public.add_currency(p_id uuid, coins_add integer, stars_add integer)
 RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $function$
BEGIN
  UPDATE public.player_stats SET coins = COALESCE(coins, 0) + coins_add, stars = COALESCE(stars, 0) + stars_add, updated_at = now() WHERE id = p_id;
END; $function$;

CREATE OR REPLACE FUNCTION public.apply_bundle_reward(p_id uuid, coins_to_add integer, reward_items jsonb, bundle_sku text)
 RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $function$
DECLARE
    item_key text; item_val int; current_boosters jsonb; current_powerups jsonb;
BEGIN
    SELECT COALESCE(boosters, '{}'::jsonb), COALESCE(power_ups, '{}'::jsonb) INTO current_boosters, current_powerups FROM public.player_stats WHERE id = p_id;
    FOR item_key, item_val IN SELECT * FROM jsonb_each_text(reward_items) LOOP
        IF item_key IN ('hammer', 'laserGun', 'gauntlet', 'powerCore') THEN
            current_boosters = jsonb_set(current_boosters, array[item_key], to_jsonb(COALESCE((current_boosters->>item_key)::int, 0) + item_val));
        ELSIF item_key IN ('rocketV', 'tnt', 'disco') THEN
            current_powerups = jsonb_set(current_powerups, array[item_key], to_jsonb(COALESCE((current_powerups->>item_key)::int, 0) + item_val));
        END IF;
    END LOOP;
    UPDATE public.player_stats SET coins = coins + coins_to_add, boosters = current_boosters, power_ups = current_powerups, purchased_bundles = array_append(purchased_bundles, bundle_sku), updated_at = now() WHERE id = p_id;
END; $function$;

-- Leaderboard (Fixed for JS params)
CREATE OR REPLACE FUNCTION public.get_leaderboard_v2(p_type text, p_user_id uuid, p_country_code text DEFAULT NULL)
 RETURNS TABLE(user_id uuid, username text, avatar_id integer, level integer, rank bigint) 
 LANGUAGE plpgsql SECURITY DEFINER AS $function$
BEGIN
    RETURN QUERY
    WITH RankedPlayers AS (
        SELECT 
            p.id,
            COALESCE(p.username, 'Unknown Player') as display_name,
            p.avatar_id,
            p.level,
            ROW_NUMBER() OVER (ORDER BY p.level DESC, p.updated_at ASC) as r_num
        FROM public.player_stats p
        WHERE 
            (p_type = 'global')
            OR
            (p_type = 'local' AND p.country_code = p_country_code)
            OR
            (p_type = 'friends' AND (
                p.id = p_user_id OR 
                EXISTS (
                    SELECT 1 FROM public.friendships f 
                    WHERE f.status = 'accepted' AND (
                        (f.sender_id = p_user_id AND f.receiver_id = p.id) OR
                        (f.receiver_id = p_user_id AND f.sender_id = p.id)
                    )
                )
            ))
    )
    SELECT * FROM RankedPlayers LIMIT 50;
END; $function$;

-- 3. TRIGGER FUNCTIONS
-- ================================================================
CREATE OR REPLACE FUNCTION public.handle_new_player()
 RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER AS $function$
BEGIN
  INSERT INTO public.player_stats (id, username, coins, stars, high_score, power_ups, boosters)
  VALUES (new.id, 'Player ' || substr(new.id::text, 1, 4), 3000, 0, 0,
    jsonb_build_object('rocketV', 3, 'tnt', 3, 'disco', 3),
    jsonb_build_object('hammer', 3, 'laserGun', 3, 'gauntlet', 3, 'powerCore', 3)
  );
  RETURN new;
END; $function$;

CREATE OR REPLACE FUNCTION public.trg_set_short_id()
 RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER AS $function$
BEGIN
    IF NEW.short_id IS NULL THEN
        NEW.short_id := public.generate_short_id();
    END IF;
    RETURN NEW;
END; $function$;

-- 4. APPLY TRIGGERS
-- ================================================================
DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE PROCEDURE public.handle_new_player();

DROP TRIGGER IF EXISTS set_short_id_trigger ON public.player_stats;
CREATE TRIGGER set_short_id_trigger
  BEFORE INSERT ON public.player_stats
  FOR EACH ROW EXECUTE PROCEDURE public.trg_set_short_id();

-- 5. REFRESH API
-- ================================================================
NOTIFY pgrst, 'reload schema';
-- Enable RLS
ALTER TABLE public.player_stats ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.friendships ENABLE ROW LEVEL SECURITY;

-- Player Stats Policies
CREATE POLICY "Public profiles are viewable by everyone" 
ON public.player_stats FOR SELECT 
USING (true);

CREATE POLICY "Users can update own stats" 
ON public.player_stats FOR UPDATE 
USING (auth.uid() = id);

-- Friendships Policies
CREATE POLICY "Users can view their own friendships" 
ON public.friendships FOR SELECT 
USING (auth.uid() = sender_id OR auth.uid() = receiver_id);

CREATE POLICY "Users can create friendship requests" 
ON public.friendships FOR INSERT 
WITH CHECK (auth.uid() = sender_id);

CREATE POLICY "Users can update their own received requests" 
ON public.friendships FOR UPDATE 
USING (auth.uid() = receiver_id);

1. Supabase Dashboard → Edge Functions → open Secrets.
2. Add each key/value:
   • XSOLLA_MERCHANT_ID, XSOLLA_API_KEY, XSOLLA_PROJECT_ID
   • XSOLLA_WEBHOOK_SECRET (optional, from Xsolla webhook settings)
   • XSOLLA_CATALOG_URL (optional, public JSON catalog URL for price validation)
3. Then click save (or batch save if you added multiple variables at the same time).

Do not hardcode secrets inside Edge-Function.ts.

Source file: docs/Supabase/Edge-Function.ts. Paste into Supabase Edge Functions editor (Via Editor) and deploy.

Loading…

• The client calls this.database.client.functions.invoke('xsolla-payment?action=get_token'), so the function name must be xsolla-payment.