

CREATE TABLE IF NOT EXISTS public.booking_costs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  cost_number text NOT NULL UNIQUE,
  booking_id uuid NOT NULL REFERENCES public.bookings(id) ON DELETE CASCADE,
  cost_date date NOT NULL DEFAULT CURRENT_DATE,
  cost_type text NOT NULL DEFAULT 'other',
  cost_account_id uuid NOT NULL REFERENCES public.accounts(id),
  paid_from_account_id uuid REFERENCES public.accounts(id),
  vendor_id uuid REFERENCES public.vendors(id),
  amount numeric NOT NULL DEFAULT 0,
  is_paid boolean NOT NULL DEFAULT true,
  method text NOT NULL DEFAULT 'cash',
  reference text,
  description text,
  journal_entry_id uuid REFERENCES public.journal_entries(id),
  created_by uuid,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

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

ALTER TABLE public.booking_costs ENABLE ROW LEVEL SECURITY;

CREATE POLICY "booking_costs_select" ON public.booking_costs
  FOR SELECT TO authenticated USING (public.can_access('bookings'));

CREATE POLICY "booking_costs_insert" ON public.booking_costs
  FOR INSERT TO authenticated WITH CHECK (public.can_edit('bookings'));

CREATE POLICY "booking_costs_update" ON public.booking_costs
  FOR UPDATE TO authenticated USING (public.can_edit('bookings')) WITH CHECK (public.can_edit('bookings'));

CREATE POLICY "booking_costs_delete" ON public.booking_costs
  FOR DELETE TO authenticated USING (public.is_admin());

CREATE TRIGGER booking_costs_set_updated_at
  BEFORE UPDATE ON public.booking_costs
  FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

CREATE INDEX IF NOT EXISTS booking_costs_booking_idx ON public.booking_costs(booking_id);
CREATE INDEX IF NOT EXISTS booking_costs_date_idx ON public.booking_costs(cost_date);

REVOKE ALL ON FUNCTION public.record_booking_cost(uuid, date, uuid, numeric, text, boolean, uuid, uuid, text, text, text) FROM PUBLIC, anon;
GRANT EXECUTE ON FUNCTION public.record_booking_cost(uuid, date, uuid, numeric, text, boolean, uuid, uuid, text, text, text) TO authenticated;

REVOKE ALL ON FUNCTION public.calculate_gross_profit(date, date) FROM PUBLIC, anon;
GRANT EXECUTE ON FUNCTION public.calculate_gross_profit(date, date) TO authenticated;