ALTER TABLE public.bookings
  ADD COLUMN IF NOT EXISTS approval_status text NOT NULL DEFAULT 'not_required',
  ADD COLUMN IF NOT EXISTS submitted_by uuid,
  ADD COLUMN IF NOT EXISTS submitted_at timestamptz,
  ADD COLUMN IF NOT EXISTS approved_by uuid,
  ADD COLUMN IF NOT EXISTS approved_at timestamptz,
  ADD COLUMN IF NOT EXISTS rejection_reason text;

CREATE TABLE IF NOT EXISTS public.booking_approvals (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  booking_id uuid NOT NULL REFERENCES public.bookings(id) ON DELETE CASCADE,
  action text NOT NULL,
  actor_id uuid,
  note text,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS booking_approvals_booking_idx ON public.booking_approvals(booking_id);

GRANT SELECT ON public.booking_approvals TO authenticated;
GRANT ALL ON public.booking_approvals TO service_role;
ALTER TABLE public.booking_approvals ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "booking_approvals_read" ON public.booking_approvals;
CREATE POLICY "booking_approvals_read" ON public.booking_approvals
  FOR SELECT TO authenticated USING (public.can_access('bookings'));

CREATE OR REPLACE FUNCTION public.submit_booking_for_approval(_booking_id uuid, _note text DEFAULT NULL)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE _b public.bookings;
BEGIN
  IF NOT public.can_edit('bookings') THEN RAISE EXCEPTION 'You do not have permission to submit 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 = 'pending' THEN RAISE EXCEPTION 'Booking is already awaiting approval'; END IF;
  IF _b.approval_status = 'approved' THEN RAISE EXCEPTION 'Booking is already approved'; END IF;
  IF _b.status = 'cancelled' THEN RAISE EXCEPTION 'Cancelled bookings cannot be submitted'; END IF;
  IF _b.customer_id IS NULL THEN RAISE EXCEPTION 'Add a customer to the booking before submitting it'; END IF;
  IF _b.total <= 0 THEN RAISE EXCEPTION 'Booking total must be greater than zero'; END IF;

  UPDATE public.bookings SET approval_status = 'pending', submitted_by = auth.uid(), submitted_at = now(),
    approved_by = NULL, approved_at = NULL, rejection_reason = NULL
  WHERE id = _booking_id;

  INSERT INTO public.booking_approvals (booking_id, action, actor_id, note)
  VALUES (_booking_id, 'submitted', auth.uid(), _note);
  INSERT INTO public.audit_logs (user_id, action, entity_type, entity_id, description)
  VALUES (auth.uid(), 'submit', 'booking', _booking_id, 'Submitted booking ' || _b.booking_number || ' for approval');
END;
$$;

CREATE OR REPLACE FUNCTION public.approve_booking(_booking_id uuid, _note text DEFAULT NULL, _confirm boolean DEFAULT true)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE _b public.bookings;
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);
  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);
END;
$$;

CREATE OR REPLACE FUNCTION public.reject_booking(_booking_id uuid, _reason text)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE _b public.bookings;
BEGIN
  IF NOT public.is_admin() THEN RAISE EXCEPTION 'Only an administrator can reject bookings'; END IF;
  IF coalesce(btrim(_reason),'') = '' THEN RAISE EXCEPTION 'A rejection reason is required'; 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 <> 'pending' THEN RAISE EXCEPTION 'Booking is not awaiting approval'; END IF;

  UPDATE public.bookings SET approval_status = 'rejected', rejection_reason = _reason,
    approved_by = NULL, approved_at = NULL, updated_at = now() WHERE id = _booking_id;

  INSERT INTO public.booking_approvals (booking_id, action, actor_id, note)
  VALUES (_booking_id, 'rejected', auth.uid(), _reason);
  INSERT INTO public.audit_logs (user_id, action, entity_type, entity_id, description)
  VALUES (auth.uid(), 'reject', 'booking', _booking_id, 'Rejected booking ' || _b.booking_number);
END;
$$;

-- Invoicing a booking now needs admin approval for non-admin users.
CREATE OR REPLACE FUNCTION public.create_invoice_from_booking(_booking_id uuid)
RETURNS uuid
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE _b public.bookings; _inv uuid; _num text; _net numeric; _acc uuid; _status text;
BEGIN
  IF NOT public.can_edit('bookings') THEN RAISE EXCEPTION 'You do not have permission to invoice 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.customer_id IS NULL THEN RAISE EXCEPTION 'Booking has no customer'; END IF;
  IF NOT public.is_admin() AND _b.approval_status <> 'approved' THEN
    RAISE EXCEPTION 'This booking must be approved by an administrator before it can be invoiced';
  END IF;

  SELECT r.revenue_account_id INTO _acc FROM public.rooms r WHERE r.id = _b.room_id;
  IF _acc IS NULL THEN
    SELECT s.revenue_account_id INTO _acc FROM public.services s WHERE s.id = _b.service_id;
  END IF;
  IF _acc IS NULL THEN
    SELECT NULLIF(value->>'default_revenue_account_id','')::uuid INTO _acc FROM public.settings WHERE key = 'sales';
  END IF;
  IF _acc IS NULL THEN
    _acc := public.account_id_by_code(CASE WHEN _b.start_time IS NOT NULL AND _b.end_time IS NOT NULL THEN '4010' ELSE '4020' END);
  END IF;

  _net := _b.price - _b.discount;

  IF _b.invoice_id IS NOT NULL THEN
    SELECT status INTO _status FROM public.invoices WHERE id = _b.invoice_id;
    IF _status IS DISTINCT FROM 'draft' THEN
      RAISE EXCEPTION 'This booking''s invoice is already posted — reverse it before changing the booking';
    END IF;
    UPDATE public.invoices SET
      customer_id = _b.customer_id,
      invoice_date = _b.booking_date,
      due_date = _b.booking_date + 15,
      subtotal = _b.price,
      discount_total = _b.discount,
      tax_total = _b.tax,
      total = _net + _b.tax,
      balance = _net + _b.tax - amount_paid,
      notes = 'Booking ' || _b.booking_number,
      updated_at = now()
    WHERE id = _b.invoice_id;
    DELETE FROM public.invoice_items WHERE invoice_id = _b.invoice_id;
    INSERT INTO public.invoice_items (invoice_id, item_type, service_id, description, quantity, unit_price, discount, tax_amount, line_total, revenue_account_id)
    VALUES (_b.invoice_id, 'service', _b.service_id, 'Studio booking ' || _b.booking_number, 1, _b.price, _b.discount, _b.tax, _net + _b.tax, _acc);
    RETURN _b.invoice_id;
  END IF;

  _num := public.next_number('invoice');
  INSERT INTO public.invoices (invoice_number, customer_id, invoice_date, due_date, subtotal, discount_total, tax_total, total, balance, status, booking_id, notes)
  VALUES (_num, _b.customer_id, _b.booking_date, _b.booking_date + 15, _b.price, _b.discount, _b.tax, _net + _b.tax, _net + _b.tax, 'draft', _b.id, 'Booking ' || _b.booking_number)
  RETURNING id INTO _inv;
  INSERT INTO public.invoice_items (invoice_id, item_type, service_id, description, quantity, unit_price, discount, tax_amount, line_total, revenue_account_id)
  VALUES (_inv, 'service', _b.service_id, 'Studio booking ' || _b.booking_number, 1, _b.price, _b.discount, _b.tax, _net + _b.tax, _acc);
  UPDATE public.bookings SET invoice_id = _inv WHERE id = _booking_id;
  RETURN _inv;
END;
$$;

REVOKE ALL ON FUNCTION public.submit_booking_for_approval(uuid, text) FROM PUBLIC, anon;
REVOKE ALL ON FUNCTION public.approve_booking(uuid, text, boolean) FROM PUBLIC, anon;
REVOKE ALL ON FUNCTION public.reject_booking(uuid, text) FROM PUBLIC, anon;
GRANT EXECUTE ON FUNCTION public.submit_booking_for_approval(uuid, text) TO authenticated;
GRANT EXECUTE ON FUNCTION public.approve_booking(uuid, text, boolean) TO authenticated;
GRANT EXECUTE ON FUNCTION public.reject_booking(uuid, text) TO authenticated;