create extension if not exists pgcrypto;

create table if not exists public.profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  email text unique,
  display_name text,
  avatar_seed text default 'p1',
  pts numeric(14,2) default 10.00,
  lifetime_wager numeric(14,2) default 0,
  month_wager numeric(14,2) default 0,
  month_key text default to_char(now(),'YYYY-MM'),
  level int default 1,
  last_seen timestamptz,
  last_case_open date,
  created_at timestamptz default now()
);

alter table public.profiles
  add column if not exists last_seen timestamptz;

create table if not exists public.events (
  id bigint generated by default as identity primary key,
  name text not null,
  description text default ''::text,
  status text default 'open'::text,
  created_at timestamptz default now(),
  created_by uuid references auth.users(id),
  winner_option_id bigint null
);

create table if not exists public.event_options (
  id bigint generated by default as identity primary key,
  event_id bigint not null references public.events(id) on delete cascade,
  label text not null,
  coef numeric(10,2) not null default 2.00
);

do $$
begin
  alter table public.events
    add constraint events_winner_fk foreign key (winner_option_id)
    references public.event_options(id) on delete set null;
exception when duplicate_object then null;
end $$;

create table if not exists public.bets (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references auth.users(id) on delete cascade,
  event_id bigint references public.events(id) on delete cascade,
  option_id bigint references public.event_options(id) on delete cascade,
  event_name text,
  opt_name text,
  stake numeric(14,2),
  coef numeric(10,2),
  status text default 'pending',
  payout numeric(14,2) default 0,
  created_at timestamptz default now()
);

create table if not exists public.app_state (
  id int primary key,
  current_month text,
  last_awarded_month text,
  updated_at timestamptz default now()
);
insert into public.app_state (id, current_month, last_awarded_month)
values (1, to_char(now(),'YYYY-MM'), null)
on conflict (id) do nothing;

alter table public.profiles disable row level security;
alter table public.events disable row level security;
alter table public.event_options disable row level security;
alter table public.bets disable row level security;
alter table public.app_state disable row level security;

-- ===== v2.1: chat, tips, custom avatars, profile privacy =====
alter table public.profiles add column if not exists avatar_url text;
alter table public.profiles add column if not exists is_public boolean default true;

create table if not exists public.chat_messages (
  id bigint generated by default as identity primary key,
  user_id uuid references auth.users(id) on delete cascade,
  name text not null default 'Player',
  avatar text default 'p1',
  level int default 1,
  kind text default 'msg',
  text text not null,
  created_at timestamptz default now()
);
alter table public.chat_messages disable row level security;

create table if not exists public.tips (
  id bigint generated by default as identity primary key,
  from_id uuid references auth.users(id) on delete set null,
  to_id uuid references auth.users(id) on delete set null,
  amount numeric(14,2) not null,
  created_at timestamptz default now()
);
alter table public.tips disable row level security;

-- ===== v2.2: shared rain pool + deposits =====

create table if not exists public.rain_pool (
  id int primary key default 1,
  amount numeric(14,2) not null default 0,
  next_at timestamptz not null default now() + interval '30 minutes',
  updated_at timestamptz default now()
);
insert into public.rain_pool (id, amount, next_at)
values (1, 0, now() + interval '30 minutes')
on conflict (id) do nothing;
alter table public.rain_pool disable row level security;

-- atomic top-up, so simultaneous bets can't overwrite each other
create or replace function public.rain_add(delta numeric)
returns void language sql security definer as $$
  update public.rain_pool
     set amount = amount + greatest(delta, 0), updated_at = now()
   where id = 1;
$$;

-- atomic claim: locks the row and only succeeds if the timer has run out, so
-- exactly one client can win the drop. Returns the pot, or null to everyone else.
create or replace function public.rain_claim(mins int)
returns numeric language plpgsql security definer as $$
declare pot numeric;
begin
  select amount into pot
    from public.rain_pool
   where id = 1 and next_at <= now()
   for update;
  if pot is null then return null; end if;
  update public.rain_pool
     set amount = 0,
         next_at = now() + make_interval(mins => mins),
         updated_at = now()
   where id = 1;
  return pot;
end $$;

grant execute on function public.rain_add(numeric)  to anon, authenticated;
grant execute on function public.rain_claim(int)    to anon, authenticated;

create table if not exists public.deposits (
  id bigint generated by default as identity primary key,
  user_id uuid references auth.users(id) on delete cascade,
  amount numeric(14,2) not null,
  method text default 'crypto',
  status text default 'pending',
  created_at timestamptz default now()
);
alter table public.deposits disable row level security;

-- ===== v2.3: lifetime stats on the account, not per device =====
create table if not exists public.user_stats (
  user_id uuid primary key references auth.users(id) on delete cascade,
  data jsonb not null default '{}'::jsonb,
  updated_at timestamptz default now()
);
alter table public.user_stats disable row level security;