CREATE TABLE public.daily_bonus_reward_settings (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  amount NUMERIC NOT NULL,
  tier TEXT NOT NULL,
  weight INTEGER NOT NULL DEFAULT 1,
  active BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (amount, tier)
);

GRANT SELECT ON public.daily_bonus_reward_settings TO authenticated;
GRANT SELECT ON public.daily_bonus_reward_settings TO anon;
GRANT ALL ON public.daily_bonus_reward_settings TO service_role;

ALTER TABLE public.daily_bonus_reward_settings ENABLE ROW LEVEL SECURITY;

CREATE POLICY "bonus_settings_read" ON public.daily_bonus_reward_settings
  FOR SELECT USING (active);

CREATE TRIGGER bonus_settings_set_updated_at
  BEFORE UPDATE ON public.daily_bonus_reward_settings
  FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

INSERT INTO public.daily_bonus_reward_settings (amount, tier, weight) VALUES
  (3,'LOW',100),(4,'LOW',95),(5,'LOW',95),(6,'LOW',90),(8,'LOW',85),(9,'LOW',80),
  (10,'LOW',80),(12,'LOW',70),(16,'LOW',60),(18,'LOW',55),(22,'LOW',50),(25,'LOW',45),
  (28,'LOW',40),(29,'LOW',38),(32,'LOW',32),(35,'LOW',28),(40,'LOW',22),
  (60,'HIGH',20),(70,'HIGH',18),(72,'HIGH',16),(75,'HIGH',15),(78,'HIGH',13),(80,'HIGH',12),
  (82,'HIGH',10),(84,'HIGH',9),(88,'HIGH',8),(90,'HIGH',7),(93,'HIGH',5),(94,'HIGH',4),
  (98,'HIGH',3),(100,'HIGH',2);

CREATE OR REPLACE FUNCTION public.pick_bonus_reward()
RETURNS NUMERIC
LANGUAGE plpgsql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  chosen_tier TEXT;
  total INTEGER;
  target INTEGER;
  running INTEGER := 0;
  rec RECORD;
  fallback NUMERIC;
BEGIN
  chosen_tier := CASE WHEN random() < 0.7 THEN 'LOW' ELSE 'HIGH' END;

  SELECT COALESCE(sum(weight), 0) INTO total
  FROM public.daily_bonus_reward_settings
  WHERE active AND tier = chosen_tier;

  IF total <= 0 THEN
    SELECT amount INTO fallback FROM public.daily_bonus_reward_settings
    WHERE active ORDER BY random() LIMIT 1;
    RETURN COALESCE(fallback, 3);
  END IF;

  target := 1 + floor(random() * total)::int;

  FOR rec IN
    SELECT amount, weight FROM public.daily_bonus_reward_settings
    WHERE active AND tier = chosen_tier
    ORDER BY amount
  LOOP
    running := running + rec.weight;
    IF running >= target THEN
      RETURN rec.amount;
    END IF;
  END LOOP;

  RETURN 3;
END;
$$;

REVOKE EXECUTE ON FUNCTION public.pick_bonus_reward() FROM anon, authenticated;

CREATE OR REPLACE FUNCTION public.claim_daily_bonus()
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  uid UUID := auth.uid();
  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 := public.pick_bonus_reward();

  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;
$$;

CREATE OR REPLACE FUNCTION public.admin_adjust_balance(
  _user_id UUID,
  _action TEXT,
  _amount NUMERIC,
  _reason TEXT
)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  before_balance NUMERIC;
  after_balance NUMERIC;
  act TEXT := upper(coalesce(_action, ''));
BEGIN
  IF _reason IS NULL OR btrim(_reason) = '' THEN
    RAISE EXCEPTION 'reason is required';
  END IF;
  IF _amount IS NULL OR _amount < 0 THEN
    RAISE EXCEPTION 'amount must be zero or positive';
  END IF;
  IF act NOT IN ('ADD', 'DEDUCT', 'ADJUST') THEN
    RAISE EXCEPTION 'action must be ADD, DEDUCT or ADJUST';
  END IF;

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

  after_balance := CASE act
    WHEN 'ADD' THEN before_balance + _amount
    WHEN 'DEDUCT' THEN GREATEST(0, before_balance - _amount)
    ELSE _amount
  END;

  UPDATE public.profiles SET balance = after_balance WHERE id = _user_id;

  INSERT INTO public.balance_transactions (user_id, type, amount, balance_before, balance_after, description)
  VALUES (_user_id, 'ADMIN_' || act, after_balance - before_balance, before_balance, after_balance, btrim(_reason));

  RETURN jsonb_build_object('user_id', _user_id, 'action', act, 'balance_before', before_balance, 'balance_after', after_balance, 'reason', btrim(_reason));
END;
$$;

REVOKE EXECUTE ON FUNCTION public.admin_adjust_balance(UUID, TEXT, NUMERIC, TEXT) FROM anon, authenticated;