DELIMITER $$
CREATE PROCEDURE cloneRestCatItems4(INOUT residold INT, INOUT residnew INT)
BEGIN
DECLARE
v_userid,v_useridold INT DEFAULT 0;
SET
v_userid =(
SELECT
user_id
FROM
restaurant_user s
join users u on s.user_id =u.id
WHERE
s.restaurant_id = ResidNew
and u.delivery_guy_detail_id is null
ORDER BY
user_id
ASC
LIMIT 1
);
SET
v_useridold =(
SELECT
s. user_id
FROM
restaurant_user s
join users u on s.user_id =u.id
WHERE
s.restaurant_id = Residold
and u.delivery_guy_detail_id is null
and exists (select 1 from item_categories g
where g.user_id=s.user_id)
ORDER BY
s.user_id
ASC
LIMIT 1
);
INSERT INTO item_categories(
NAME,
is_enabled,
created_at,
updated_at,
user_id
)
SELECT distinct
cat.name AS NAME,
cat.is_enabled AS is_enabled,
cat.created_at AS created_at,
cat.updated_at AS updated_at,
v_userid AS user_id
FROM
item_categories cat
INNER JOIN restaurant_user ruser ON
cat.user_id = ruser.user_id
INNER JOIN restaurants res ON
res.id = ruser.restaurant_id
WHERE
res.id = ResidOld
and ruser.user_id = v_useridold;
INSERT INTO items(
restaurant_id,
item_category_id,
NAME,
price,
old_price,
image,
is_recommended,
is_popular,
is_new,
created_at,
updated_at,
DESC,
placeholder_image,
is_active,
is_veg
)
SELECT
ResidNew AS restaurant_id,
cat2.id AS item_category_id,
it.name AS Item_name,
it.price AS price,
it.old_price AS old_price,
it.image AS image,
it.is_recommended AS is_recommended,
it.is_popular AS is_popular,
it.is_new AS is_new,
NOW() AS created_at, NOW() AS updated_at, it.desc AS description, it.placeholder_image AS placeholder_image, it.is_active AS is_active, it.is_veg AS is_veg
FROM
item_categories cat
JOIN restaurant_user ruser ON
cat.user_id = ruser.user_id
JOIN users u ON ruser.user_id =u.id
JOIN restaurants res ON
res.id = ruser.restaurant_id
JOIN items it ON
it.item_category_id = cat.id AND it.restaurant_id = res.id
JOIN item_categories cat2 ON
cat.name = cat2.name AND cat2.user_id = v_userid
WHERE
res.id = ResidOld AND cat.user_id = v_useridold
and u.delivery_guy_detail_id is null;
END$$
DELIMITER ;
