CREATE TABLE public.profiles (
  id UUID NOT NULL PRIMARY KEY REFERENCES auth.users ON DELETE CASCADE,
  telegram_id TEXT UNIQUE,
  telegram_username TEXT UNIQUE,
  display_name TEXT,
  balance NUMERIC(12,2) NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE ON public.profiles TO authenticated;
GRANT ALL ON public.profiles TO service_role;
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
CREATE POLICY "profiles_select_own" ON public.profiles FOR SELECT TO authenticated USING (auth.uid() = id);
CREATE POLICY "profiles_insert_own" ON public.profiles FOR INSERT TO authenticated WITH CHECK (auth.uid() = id);
CREATE POLICY "profiles_update_own" ON public.profiles FOR UPDATE TO authenticated USING (auth.uid() = id) WITH CHECK (auth.uid() = id);

CREATE TABLE public.daily_bonus_claims (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES auth.users ON DELETE CASCADE,
  claim_date DATE NOT NULL,
  reward_amount NUMERIC(12,2) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (user_id, claim_date)
);
GRANT SELECT ON public.daily_bonus_claims TO authenticated;
GRANT ALL ON public.daily_bonus_claims TO service_role;
ALTER TABLE public.daily_bonus_claims ENABLE ROW LEVEL SECURITY;
CREATE POLICY "bonus_select_own" ON public.daily_bonus_claims FOR SELECT TO authenticated USING (auth.uid() = user_id);

CREATE TABLE public.balance_transactions (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES auth.users ON DELETE CASCADE,
  type TEXT NOT NULL,
  amount NUMERIC(12,2) NOT NULL,
  balance_before NUMERIC(12,2) NOT NULL,
  balance_after NUMERIC(12,2) NOT NULL,
  reference_id TEXT,
  description TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
GRANT SELECT ON public.balance_transactions TO authenticated;
GRANT ALL ON public.balance_transactions TO service_role;
ALTER TABLE public.balance_transactions ENABLE ROW LEVEL SECURITY;
CREATE POLICY "tx_select_own" ON public.balance_transactions FOR SELECT TO authenticated USING (auth.uid() = user_id);

CREATE TABLE public.orders (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES auth.users ON DELETE CASCADE,
  order_code TEXT NOT NULL UNIQUE,
  package_id INTEGER NOT NULL,
  diamond_amount INTEGER NOT NULL,
  price NUMERIC(12,2) NOT NULL,
  free_fire_uid TEXT NOT NULL,
  payment_method TEXT NOT NULL,
  transaction_id TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'PENDING',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX orders_user_created_idx ON public.orders (user_id, created_at DESC);
GRANT SELECT, INSERT ON public.orders TO authenticated;
GRANT ALL ON public.orders TO service_role;
ALTER TABLE public.orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY "orders_select_own" ON public.orders FOR SELECT TO authenticated USING (auth.uid() = user_id);
CREATE POLICY "orders_insert_own" ON public.orders FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);

CREATE OR REPLACE FUNCTION public.set_updated_at()
RETURNS TRIGGER
LANGUAGE plpgsql
SET search_path = public
AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$;

CREATE TRIGGER profiles_set_updated_at BEFORE UPDATE ON public.profiles
FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();
CREATE TRIGGER orders_set_updated_at BEFORE UPDATE ON public.orders
FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

CREATE OR REPLACE FUNCTION public.guard_profile_balance()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  IF NEW.balance IS DISTINCT FROM OLD.balance THEN
    RAISE EXCEPTION 'balance cannot be changed directly';
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER profiles_guard_balance BEFORE UPDATE ON public.profiles
FOR EACH ROW WHEN (current_setting('role', true) <> 'service_role' AND pg_trigger_depth() = 1)
EXECUTE FUNCTION public.guard_profile_balance();

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)
  VALUES (NEW.id, COALESCE(NEW.raw_user_meta_data->>'display_name', split_part(NEW.email, '@', 1)))
  ON CONFLICT (id) DO NOTHING;
  RETURN NEW;
END;
$$;

CREATE TRIGGER on_auth_user_created AFTER INSERT ON auth.users
FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

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, 10, 20, 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.claim_daily_bonus() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.claim_daily_bonus() TO authenticated;

CREATE OR REPLACE FUNCTION public.has_claimed_today()
RETURNS BOOLEAN
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.daily_bonus_claims
    WHERE user_id = auth.uid() AND claim_date = (now() AT TIME ZONE 'UTC')::date
  );
$$;

REVOKE ALL ON FUNCTION public.has_claimed_today() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.has_claimed_today() TO authenticated;