-- ============================================================================
-- transfer_shop_ownership RPC (UX-09)
--
-- Atomic role swap for owner handoff. Without this, an owner who wants
-- to step down would have to:
--   1. update center_memberships demoting themselves to trainer
--   2. update the new owner's membership to owner
--   3. update centers.owner_id
-- ...and any failure between steps leaves the shop in a half-transferred
-- state. The RPC wraps all three in a single transaction with caller +
-- target validation.
-- ============================================================================

CREATE OR REPLACE FUNCTION transfer_shop_ownership(
  _center_id uuid,
  _new_owner_user_id uuid
) RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  _caller_id uuid := auth.uid();
BEGIN
  IF _caller_id IS NULL THEN
    RAISE EXCEPTION 'unauthenticated' USING ERRCODE = 'insufficient_privilege';
  END IF;

  -- Caller must be the current owner of the center.
  IF NOT EXISTS (
    SELECT 1 FROM centers
    WHERE id = _center_id AND owner_id = _caller_id
  ) THEN
    RAISE EXCEPTION 'only current owner can transfer ownership'
      USING ERRCODE = 'insufficient_privilege';
  END IF;

  IF _new_owner_user_id = _caller_id THEN
    RAISE EXCEPTION 'cannot transfer ownership to self'
      USING ERRCODE = 'invalid_parameter_value';
  END IF;

  -- New owner must be active staff (trainer) of the same center.
  IF NOT EXISTS (
    SELECT 1 FROM center_memberships
    WHERE center_id = _center_id
      AND user_id = _new_owner_user_id
      AND role IN ('owner', 'trainer')
      AND left_at IS NULL
  ) THEN
    RAISE EXCEPTION 'new owner must be active staff of the center'
      USING ERRCODE = 'invalid_parameter_value';
  END IF;

  -- Atomic 3-step swap.
  UPDATE center_memberships
     SET role = 'trainer'
   WHERE center_id = _center_id AND user_id = _caller_id;

  UPDATE center_memberships
     SET role = 'owner'
   WHERE center_id = _center_id AND user_id = _new_owner_user_id;

  UPDATE centers
     SET owner_id = _new_owner_user_id
   WHERE id = _center_id;
END;
$$;

REVOKE ALL ON FUNCTION transfer_shop_ownership(uuid, uuid) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION transfer_shop_ownership(uuid, uuid) TO authenticated;

COMMENT ON FUNCTION transfer_shop_ownership(uuid, uuid) IS
  'UX-09: atomic owner handoff. Caller must be current owner. Target must be active staff (not self).';
