-- OYS Borrow a Boat — Supabase Schema

-- ============================================================
-- TABLES
-- ============================================================

create table public.boats (
  id                uuid        primary key default gen_random_uuid(),
  name              text        not null,
  display_name      text,
  class             text,
  year              integer,
  img_src           text,
  icon_src          text,
  booking_available boolean     not null default true,
  required_certs    text[]      not null default '{}',
  max_passengers    integer     not null default 6,
  defects           jsonb       not null default '[]',
  -- defects shape: [{ type: string, severity: string, description: string, detail?: string }]
  created_at        timestamptz not null default now()
);

create table public.members (
  id              uuid        primary key default gen_random_uuid(),
  user_id         uuid        not null references auth.users(id) on delete cascade,
  first_name      text        not null default '',
  last_name       text        not null default '',
  email           text        not null,
  slack_id        text,
  certifications  text[]      not null default '{}',
  -- cert codes match boats.required_certs values (e.g. 'j27', 'capri25')
  role            text        not null default 'member'
                              check (role in ('member', 'skipper', 'admin', 'boatswain', 'volunteer', 'instructor')),
  created_at      timestamptz not null default now(),
  unique(user_id)
);

create table public.interval_templates (
  id          uuid        primary key default gen_random_uuid(),
  name        text        not null,
  time_tuples jsonb       not null default '[]',
  -- shape: [[startHHMM, endHHMM], ...] e.g. [["08:00","12:00"],["13:00","17:00"]]
  created_at  timestamptz not null default now()
);

create table public.intervals (
  id         uuid        primary key default gen_random_uuid(),
  boat_id    uuid        not null references public.boats(id) on delete cascade,
  start_time timestamptz not null,
  end_time   timestamptz not null,
  user_id    uuid        references auth.users(id) on delete set null,
  created_at timestamptz not null default now()
);

create index intervals_boat_id_idx on public.intervals(boat_id);
create index intervals_time_range_idx on public.intervals(start_time, end_time);

create table public.reservations (
  id          uuid        primary key default gen_random_uuid(),
  boat_id     uuid        not null references public.boats(id) on delete cascade,
  user_id     uuid        not null references auth.users(id) on delete cascade,
  start_time  timestamptz not null,
  end_time    timestamptz not null,
  status      text        not null default 'pending'
              check (status in ('pending', 'tentative', 'confirmed')),
  reason      text        not null default '',
  comment     text        not null default '',
  member_ids  text[]      not null default '{}',
  guest_ids   text[]      not null default '{}',
  created_at  timestamptz not null default now()
);

create index reservations_boat_id_idx on public.reservations(boat_id);
create index reservations_user_id_idx on public.reservations(user_id);
create index reservations_time_range_idx on public.reservations(start_time, end_time);

create table public.reference_docs (
  id         uuid        primary key default gen_random_uuid(),
  title      text        not null,
  category   text        not null,
  tags       text[]      not null default '{}',
  subtitle   text,
  content    text        not null,
  created_at timestamptz not null default now()
);

-- ============================================================
-- RLS
-- ============================================================

alter table public.boats enable row level security;
alter table public.members enable row level security;
alter table public.interval_templates enable row level security;
alter table public.intervals enable row level security;
alter table public.reservations enable row level security;
alter table public.reference_docs enable row level security;

-- boats
create policy "Authenticated users can read boats" on public.boats
  for select using (auth.role() = 'authenticated');
create policy "Admins can manage boats" on public.boats
  for all using (
    exists (
      select 1 from public.members
      where user_id = auth.uid() and role in ('admin', 'boatswain')
    )
  );

-- members
create policy "Users can read own member record" on public.members
  for select using (user_id = auth.uid());
create policy "Admins can read all members" on public.members
  for select using (
    exists (
      select 1 from public.members m2
      where m2.user_id = auth.uid() and m2.role in ('admin', 'boatswain', 'instructor')
    )
  );
create policy "Users can update own member record" on public.members
  for update using (user_id = auth.uid());
create policy "Admins can manage all members" on public.members
  for all using (
    exists (
      select 1 from public.members m2
      where m2.user_id = auth.uid() and m2.role = 'admin'
    )
  );

-- interval_templates
create policy "Authenticated users can read interval templates" on public.interval_templates
  for select using (auth.role() = 'authenticated');
create policy "Admins can manage interval templates" on public.interval_templates
  for all using (
    exists (
      select 1 from public.members
      where user_id = auth.uid() and role in ('admin', 'boatswain')
    )
  );

-- intervals
create policy "Authenticated users can read intervals" on public.intervals
  for select using (auth.role() = 'authenticated');
create policy "Admins can manage intervals" on public.intervals
  for all using (
    exists (
      select 1 from public.members
      where user_id = auth.uid() and role in ('admin', 'boatswain')
    )
  );

-- reservations
create policy "Users can read own reservations" on public.reservations
  for select using (user_id = auth.uid());
create policy "Admins can read all reservations" on public.reservations
  for select using (
    exists (
      select 1 from public.members
      where user_id = auth.uid() and role in ('admin', 'boatswain')
    )
  );
create policy "Authenticated users can read non-private reservation slots" on public.reservations
  for select using (auth.role() = 'authenticated');
create policy "Users can create own reservations" on public.reservations
  for insert with check (user_id = auth.uid());
create policy "Users can update own reservations" on public.reservations
  for update using (user_id = auth.uid());
create policy "Admins can manage all reservations" on public.reservations
  for all using (
    exists (
      select 1 from public.members
      where user_id = auth.uid() and role in ('admin', 'boatswain')
    )
  );

-- reference_docs
create policy "Authenticated users can read reference docs" on public.reference_docs
  for select using (auth.role() = 'authenticated');
create policy "Admins can manage reference docs" on public.reference_docs
  for all using (
    exists (
      select 1 from public.members
      where user_id = auth.uid() and role = 'admin'
    )
  );

-- ============================================================
-- TRIGGER: create member record on first sign-in
-- ============================================================

create or replace function public.handle_new_user()
returns trigger language plpgsql security definer as $$
begin
  insert into public.members (user_id, email)
  values (new.id, new.email)
  on conflict (user_id) do nothing;
  return new;
end;
$$;

create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();