DO $$ BEGIN
  CREATE TYPE public.booking_status AS ENUM ('requested','accepted','declined','confirmed','completed','cancelled');
EXCEPTION WHEN duplicate_object THEN null; END $$;

CREATE TABLE public.bookings (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  school_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  trainer_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  trade text NOT NULL,
  scheduled_at timestamptz NOT NULL,
  duration_minutes integer NOT NULL DEFAULT 60 CHECK (duration_minutes BETWEEN 15 AND 600),
  location text,
  notes text,
  hourly_rate numeric(10,2),
  status public.booking_status NOT NULL DEFAULT 'requested',
  accepted_at timestamptz,
  confirmed_at timestamptz,
  completed_at timestamptz,
  cancelled_at timestamptz,
  cancel_reason text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

GRANT SELECT, INSERT, UPDATE, DELETE ON public.bookings TO authenticated;
GRANT ALL ON public.bookings TO service_role;

ALTER TABLE public.bookings ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Participants and oversight can view bookings"
ON public.bookings FOR SELECT TO authenticated
USING (
  auth.uid() = school_id
  OR auth.uid() = trainer_id
  OR public.has_role(auth.uid(), 'admin')
  OR public.has_role(auth.uid(), 'government')
);

CREATE POLICY "Schools create their own bookings"
ON public.bookings FOR INSERT TO authenticated
WITH CHECK (
  auth.uid() = school_id AND public.has_role(auth.uid(), 'school')
);

CREATE POLICY "School or trainer can update own bookings"
ON public.bookings FOR UPDATE TO authenticated
USING (auth.uid() = school_id OR auth.uid() = trainer_id OR public.has_role(auth.uid(), 'admin'))
WITH CHECK (auth.uid() = school_id OR auth.uid() = trainer_id OR public.has_role(auth.uid(), 'admin'));

CREATE INDEX bookings_trainer_idx ON public.bookings(trainer_id, scheduled_at);
CREATE INDEX bookings_school_idx ON public.bookings(school_id, scheduled_at);

CREATE TRIGGER bookings_updated_at
BEFORE UPDATE ON public.bookings
FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();