ALTER TABLE public.profiles ADD COLUMN IF NOT EXISTS account_code TEXT;

CREATE OR REPLACE FUNCTION public.gen_account_code()
RETURNS TEXT
LANGUAGE plpgsql
SET search_path = public
AS $$
DECLARE
  code TEXT;
BEGIN
  LOOP
    code := 'CV-' || lpad((floor(random() * 90000) + 10000)::int::text, 5, '0');
    EXIT WHEN NOT EXISTS (SELECT 1 FROM public.profiles WHERE account_code = code);
  END LOOP;
  RETURN code;
END;
$$;

UPDATE public.profiles SET account_code = public.gen_account_code() WHERE account_code IS NULL;

CREATE UNIQUE INDEX IF NOT EXISTS profiles_account_code_key ON public.profiles (account_code);

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  INSERT INTO public.profiles (id, display_name, account_code)
  VALUES (
    NEW.id,
    COALESCE(NEW.raw_user_meta_data->>'display_name', NULLIF(split_part(COALESCE(NEW.email, ''), '@', 1), '')),
    public.gen_account_code()
  )
  ON CONFLICT (id) DO NOTHING;
  RETURN NEW;
END;
$$;

CREATE OR REPLACE FUNCTION public.claim_daily_bonus()
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  uid UUID := auth.uid();
  rewards NUMERIC[] := ARRAY[3, 5, 6, 8, 10, 12, 20, 23, 25, 28, 35, 50, 60, 75, 80, 100];
  reward NUMERIC;
  today DATE := (now() AT TIME ZONE 'UTC')::date;
  before_balance NUMERIC;
  after_balance NUMERIC;
BEGIN
  IF uid IS NULL THEN
    RAISE EXCEPTION 'not authenticated';
  END IF;

  SELECT balance INTO before_balance FROM public.profiles WHERE id = uid FOR UPDATE;
  IF before_balance IS NULL THEN
    RAISE EXCEPTION 'profile not found';
  END IF;

  reward := rewards[1 + floor(random() * array_length(rewards, 1))::int];

  BEGIN
    INSERT INTO public.daily_bonus_claims (user_id, claim_date, reward_amount)
    VALUES (uid, today, reward);
  EXCEPTION WHEN unique_violation THEN
    RETURN jsonb_build_object('claimed', false, 'reason', 'already_claimed', 'balance', before_balance);
  END;

  after_balance := before_balance + reward;
  UPDATE public.profiles SET balance = after_balance WHERE id = uid;

  INSERT INTO public.balance_transactions (user_id, type, amount, balance_before, balance_after, reference_id, description)
  VALUES (uid, 'DAILY_BONUS', reward, before_balance, after_balance, today::text, 'Daily butterfly bonus');

  RETURN jsonb_build_object('claimed', true, 'reward', reward, 'balance', after_balance);
END;
$$;

REVOKE ALL ON FUNCTION public.gen_account_code() FROM PUBLIC, anon, authenticated;