-- Run this in the Supabase SQL editor to set up the database.

create table if not exists bookings (
  id uuid primary key default gen_random_uuid(),
  guest_name text not null,
  guest_email text not null,
  check_in date not null,
  check_out date not null,
  nights int not null,
  total_usd numeric(10,2) not null,
  status text not null default 'pending', -- pending | confirmed | cancelled
  stripe_session_id text,
  stripe_payment_intent text,
  created_at timestamptz not null default now()
);

-- Prevent overlapping confirmed bookings at the DB level as a safety net
-- (primary overlap check happens in the availability API before checkout).
create index if not exists bookings_dates_idx on bookings (check_in, check_out) where status = 'confirmed';

-- Row Level Security: public can INSERT a pending booking (via the server
-- using the service role key in practice), but only the service role can
-- read/update. Adjust if you want guests to look up their own booking.
alter table bookings enable row level security;

create policy "service role full access"
  on bookings for all
  using (auth.role() = 'service_role')
  with check (auth.role() = 'service_role');