CREATE TABLE public.booking_cost_rules (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  cost_type text NOT NULL DEFAULT 'other',
  cost_account_id uuid NOT NULL REFERENCES public.accounts(id),
  basis text NOT NULL DEFAULT 'percent' CHECK (basis IN ('percent','fixed')),
  rate numeric(18,4) NOT NULL DEFAULT 0 CHECK (rate >= 0),
  service_id uuid REFERENCES public.services(id) ON DELETE CASCADE,
  room_id uuid REFERENCES public.rooms(id) ON DELETE CASCADE,
  is_paid boolean NOT NULL DEFAULT false,
  paid_from_account_id uuid REFERENCES public.accounts(id),
  vendor_id uuid REFERENCES public.vendors(id) ON DELETE SET NULL,
  description text,
  is_active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

GRANT SELECT, INSERT, UPDATE, DELETE ON public.booking_cost_rules TO authenticated;
GRANT ALL ON public.booking_cost_rules TO service_role;
ALTER TABLE public.booking_cost_rules ENABLE ROW LEVEL SECURITY;

CREATE POLICY "booking cost rules viewable with bookings access"
ON public.booking_cost_rules FOR SELECT TO authenticated
USING (public.can_access('bookings'));

CREATE POLICY "booking cost rules insertable with bookings edit"
ON public.booking_cost_rules FOR INSERT TO authenticated
WITH CHECK (public.can_edit('bookings'));

CREATE POLICY "booking cost rules updatable with bookings edit"
ON public.booking_cost_rules FOR UPDATE TO authenticated
USING (public.can_edit('bookings')) WITH CHECK (public.can_edit('bookings'));

CREATE POLICY "booking cost rules deletable by admins"
ON public.booking_cost_rules FOR DELETE TO authenticated
USING (public.is_admin());

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

-- Automatically post the configured direct costs when a booking is approved.
CREATE OR REPLACE FUNCTION public.apply_booking_cost_rules(_booking_id uuid)
RETURNS integer
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE _b public.bookings; _r record; _amt numeric; _net numeric; _made int := 0; _ref text;
BEGIN
  SELECT * INTO _b FROM public.bookings WHERE id = _booking_id;
  IF _b.id IS NULL THEN RAISE EXCEPTION 'Booking not found'; END IF;
  _net := COALESCE(_b.price,0) - COALESCE(_b.discount,0);

  FOR _r IN
    SELECT * FROM public.booking_cost_rules
     WHERE is_active
       AND (service_id IS NULL OR service_id = _b.service_id)
       AND (room_id IS NULL OR room_id = _b.room_id)
     ORDER BY created_at
  LOOP
    _ref := 'AUTO:' || _r.id::text;
    IF EXISTS (SELECT 1 FROM public.booking_costs WHERE booking_id = _booking_id AND reference = _ref) THEN
      CONTINUE;
    END IF;
    _amt := CASE WHEN _r.basis = 'percent' THEN round(_net * _r.rate / 100, 2) ELSE round(_r.rate, 2) END;
    IF _amt <= 0 THEN CONTINUE; END IF;
    PERFORM public.record_booking_cost(
      _booking_id, _b.booking_date, _r.cost_account_id, _amt, _r.cost_type,
      _r.is_paid, _r.paid_from_account_id, _r.vendor_id, 'cash', _ref,
      COALESCE(_r.description, _r.name || ' (auto on approval)')
    );
    _made := _made + 1;
  END LOOP;
  RETURN _made;
END; $$;

REVOKE ALL ON FUNCTION public.apply_booking_cost_rules(uuid) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.apply_booking_cost_rules(uuid) TO authenticated;

CREATE OR REPLACE FUNCTION public.approve_booking(_booking_id uuid, _note text DEFAULT NULL::text, _confirm boolean DEFAULT true)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE _b public.bookings; _costs int := 0;
BEGIN
  IF NOT public.is_admin() THEN RAISE EXCEPTION 'Only an administrator can approve bookings'; END IF;
  SELECT * INTO _b FROM public.bookings WHERE id = _booking_id FOR UPDATE;
  IF _b.id IS NULL THEN RAISE EXCEPTION 'Booking not found'; END IF;
  IF _b.approval_status NOT IN ('pending','rejected') THEN RAISE EXCEPTION 'Booking is not awaiting approval'; END IF;

  UPDATE public.bookings SET approval_status = 'approved', approved_by = auth.uid(), approved_at = now(),
    rejection_reason = NULL,
    status = CASE WHEN _confirm AND status = 'tentative' THEN 'confirmed' ELSE status END,
    updated_at = now()
  WHERE id = _booking_id;

  INSERT INTO public.booking_approvals (booking_id, action, actor_id, note)
  VALUES (_booking_id, 'approved', auth.uid(), _note);

  _costs := public.apply_booking_cost_rules(_booking_id);

  INSERT INTO public.audit_logs (user_id, action, entity_type, entity_id, description)
  VALUES (auth.uid(), 'approve', 'booking', _booking_id,
    'Approved booking ' || _b.booking_number ||
    CASE WHEN _costs > 0 THEN ' · ' || _costs || ' automatic cost entries posted' ELSE '' END);
END; $$;

INSERT INTO public.booking_cost_rules (name, cost_type, cost_account_id, basis, rate, is_paid, description)
VALUES
 ('Crew fee on approval', 'crew', (SELECT id FROM public.accounts WHERE code = '5010'), 'percent', 10, false, 'Crew / operator cost accrued when a booking is approved'),
 ('Props & consumables', 'props', (SELECT id FROM public.accounts WHERE code = '5020'), 'percent', 3, false, 'Props and consumables accrued when a booking is approved'),
 ('Makeup & styling', 'other', (SELECT id FROM public.accounts WHERE code = '5060'), 'fixed', 0, false, 'Set a fixed amount to accrue makeup and styling per approved booking');