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;