-- BEGIN CHANGE: Temporary Imbuement Scrolls (unified shop)
-- Run on ot_new (schema with catalog + group/option/option_item).
-- Catalog: donate (premium_points) only.
--
-- IMPORTANT:
--   Do NOT hardcode z_shop_offer.id = 12742..12748.
--   Those offer PKs are already used by other products on live DB.
--   Scroll client itemids (12742..12748) go ONLY in z_shop_offer_option_item.itemid.
--   Portal Imbuements tab detects those itemids via offer groups.
--
-- Safe to re-run: skips each scroll if that itemid is already sold as offer_type=item.
-- Adjust points/sort_order as needed.

START TRANSACTION;

-- ---------------------------------------------------------------------------
-- helper pattern repeated per scroll
-- ---------------------------------------------------------------------------

-- Critical Scroll (itemid 12745) - 20 PP
SET @oid := (
  SELECT o.`id`
  FROM `z_shop_offer` o
  INNER JOIN `z_shop_offer_group` g ON g.`offer_id` = o.`id`
  INNER JOIN `z_shop_offer_option` op ON op.`group_id` = g.`id`
  INNER JOIN `z_shop_offer_option_item` oi ON oi.`option_id` = op.`id`
  WHERE o.`catalog` = 'donate' AND o.`offer_type` = 'item' AND oi.`itemid` = 12745
  LIMIT 1
);
INSERT INTO `z_shop_offer`
  (`catalog`, `points`, `offer_type`, `offer_name`, `offer_description`, `sort_order`, `pacc_days`, `active`)
SELECT 'donate', 20, 'item', 'Critical Imbuement Scroll',
  'Temporary +critical for 1 hour. Use the scroll in-game.',
  50, 0, 1
FROM DUAL WHERE @oid IS NULL;
SET @oid := IFNULL(@oid, LAST_INSERT_ID());
INSERT INTO `z_shop_offer_group` (`offer_id`, `label`, `sort_order`, `min_select`, `max_select`)
SELECT @oid, 'Scroll', 0, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `z_shop_offer_group` WHERE `offer_id` = @oid);
SET @gid := (SELECT `id` FROM `z_shop_offer_group` WHERE `offer_id` = @oid ORDER BY `sort_order`, `id` LIMIT 1);
INSERT INTO `z_shop_offer_option` (`group_id`, `label`, `sort_order`)
SELECT @gid, 'Critical', 0 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `z_shop_offer_option` WHERE `group_id` = @gid);
SET @opid := (SELECT `id` FROM `z_shop_offer_option` WHERE `group_id` = @gid ORDER BY `sort_order`, `id` LIMIT 1);
INSERT INTO `z_shop_offer_option_item` (`option_id`, `itemid`, `count`, `sort_order`, `slot_role`)
SELECT @opid, 12745, 1, 0, '' FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `z_shop_offer_option_item` WHERE `option_id` = @opid AND `itemid` = 12745);

-- Dodge Scroll (itemid 12743) - 20 PP
SET @oid := (
  SELECT o.`id`
  FROM `z_shop_offer` o
  INNER JOIN `z_shop_offer_group` g ON g.`offer_id` = o.`id`
  INNER JOIN `z_shop_offer_option` op ON op.`group_id` = g.`id`
  INNER JOIN `z_shop_offer_option_item` oi ON oi.`option_id` = op.`id`
  WHERE o.`catalog` = 'donate' AND o.`offer_type` = 'item' AND oi.`itemid` = 12743
  LIMIT 1
);
INSERT INTO `z_shop_offer`
  (`catalog`, `points`, `offer_type`, `offer_name`, `offer_description`, `sort_order`, `pacc_days`, `active`)
SELECT 'donate', 20, 'item', 'Dodge Imbuement Scroll',
  'Temporary +dodge for 1 hour. Use the scroll in-game.',
  51, 0, 1
FROM DUAL WHERE @oid IS NULL;
SET @oid := IFNULL(@oid, LAST_INSERT_ID());
INSERT INTO `z_shop_offer_group` (`offer_id`, `label`, `sort_order`, `min_select`, `max_select`)
SELECT @oid, 'Scroll', 0, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `z_shop_offer_group` WHERE `offer_id` = @oid);
SET @gid := (SELECT `id` FROM `z_shop_offer_group` WHERE `offer_id` = @oid ORDER BY `sort_order`, `id` LIMIT 1);
INSERT INTO `z_shop_offer_option` (`group_id`, `label`, `sort_order`)
SELECT @gid, 'Dodge', 0 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `z_shop_offer_option` WHERE `group_id` = @gid);
SET @opid := (SELECT `id` FROM `z_shop_offer_option` WHERE `group_id` = @gid ORDER BY `sort_order`, `id` LIMIT 1);
INSERT INTO `z_shop_offer_option_item` (`option_id`, `itemid`, `count`, `sort_order`, `slot_role`)
SELECT @opid, 12743, 1, 0, '' FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `z_shop_offer_option_item` WHERE `option_id` = @opid AND `itemid` = 12743);

-- Shielding Scroll (itemid 12744) - 15 PP
SET @oid := (
  SELECT o.`id`
  FROM `z_shop_offer` o
  INNER JOIN `z_shop_offer_group` g ON g.`offer_id` = o.`id`
  INNER JOIN `z_shop_offer_option` op ON op.`group_id` = g.`id`
  INNER JOIN `z_shop_offer_option_item` oi ON oi.`option_id` = op.`id`
  WHERE o.`catalog` = 'donate' AND o.`offer_type` = 'item' AND oi.`itemid` = 12744
  LIMIT 1
);
INSERT INTO `z_shop_offer`
  (`catalog`, `points`, `offer_type`, `offer_name`, `offer_description`, `sort_order`, `pacc_days`, `active`)
SELECT 'donate', 15, 'item', 'Shielding Imbuement Scroll',
  'Temporary +30 shielding for 1 hour. Use the scroll in-game.',
  52, 0, 1
FROM DUAL WHERE @oid IS NULL;
SET @oid := IFNULL(@oid, LAST_INSERT_ID());
INSERT INTO `z_shop_offer_group` (`offer_id`, `label`, `sort_order`, `min_select`, `max_select`)
SELECT @oid, 'Scroll', 0, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `z_shop_offer_group` WHERE `offer_id` = @oid);
SET @gid := (SELECT `id` FROM `z_shop_offer_group` WHERE `offer_id` = @oid ORDER BY `sort_order`, `id` LIMIT 1);
INSERT INTO `z_shop_offer_option` (`group_id`, `label`, `sort_order`)
SELECT @gid, 'Shielding', 0 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `z_shop_offer_option` WHERE `group_id` = @gid);
SET @opid := (SELECT `id` FROM `z_shop_offer_option` WHERE `group_id` = @gid ORDER BY `sort_order`, `id` LIMIT 1);
INSERT INTO `z_shop_offer_option_item` (`option_id`, `itemid`, `count`, `sort_order`, `slot_role`)
SELECT @opid, 12744, 1, 0, '' FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `z_shop_offer_option_item` WHERE `option_id` = @opid AND `itemid` = 12744);

-- Melee Scroll (itemid 12742) - 15 PP
SET @oid := (
  SELECT o.`id`
  FROM `z_shop_offer` o
  INNER JOIN `z_shop_offer_group` g ON g.`offer_id` = o.`id`
  INNER JOIN `z_shop_offer_option` op ON op.`group_id` = g.`id`
  INNER JOIN `z_shop_offer_option_item` oi ON oi.`option_id` = op.`id`
  WHERE o.`catalog` = 'donate' AND o.`offer_type` = 'item' AND oi.`itemid` = 12742
  LIMIT 1
);
INSERT INTO `z_shop_offer`
  (`catalog`, `points`, `offer_type`, `offer_name`, `offer_description`, `sort_order`, `pacc_days`, `active`)
SELECT 'donate', 15, 'item', 'Melee Imbuement Scroll',
  'Temporary +30 sword/axe/club for 1 hour. Use the scroll in-game.',
  53, 0, 1
FROM DUAL WHERE @oid IS NULL;
SET @oid := IFNULL(@oid, LAST_INSERT_ID());
INSERT INTO `z_shop_offer_group` (`offer_id`, `label`, `sort_order`, `min_select`, `max_select`)
SELECT @oid, 'Scroll', 0, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `z_shop_offer_group` WHERE `offer_id` = @oid);
SET @gid := (SELECT `id` FROM `z_shop_offer_group` WHERE `offer_id` = @oid ORDER BY `sort_order`, `id` LIMIT 1);
INSERT INTO `z_shop_offer_option` (`group_id`, `label`, `sort_order`)
SELECT @gid, 'Melee', 0 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `z_shop_offer_option` WHERE `group_id` = @gid);
SET @opid := (SELECT `id` FROM `z_shop_offer_option` WHERE `group_id` = @gid ORDER BY `sort_order`, `id` LIMIT 1);
INSERT INTO `z_shop_offer_option_item` (`option_id`, `itemid`, `count`, `sort_order`, `slot_role`)
SELECT @opid, 12742, 1, 0, '' FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `z_shop_offer_option_item` WHERE `option_id` = @opid AND `itemid` = 12742);

-- Distance Scroll (itemid 12747) - 15 PP
SET @oid := (
  SELECT o.`id`
  FROM `z_shop_offer` o
  INNER JOIN `z_shop_offer_group` g ON g.`offer_id` = o.`id`
  INNER JOIN `z_shop_offer_option` op ON op.`group_id` = g.`id`
  INNER JOIN `z_shop_offer_option_item` oi ON oi.`option_id` = op.`id`
  WHERE o.`catalog` = 'donate' AND o.`offer_type` = 'item' AND oi.`itemid` = 12747
  LIMIT 1
);
INSERT INTO `z_shop_offer`
  (`catalog`, `points`, `offer_type`, `offer_name`, `offer_description`, `sort_order`, `pacc_days`, `active`)
SELECT 'donate', 15, 'item', 'Distance Imbuement Scroll',
  'Temporary +30 distance for 1 hour. Use the scroll in-game.',
  54, 0, 1
FROM DUAL WHERE @oid IS NULL;
SET @oid := IFNULL(@oid, LAST_INSERT_ID());
INSERT INTO `z_shop_offer_group` (`offer_id`, `label`, `sort_order`, `min_select`, `max_select`)
SELECT @oid, 'Scroll', 0, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `z_shop_offer_group` WHERE `offer_id` = @oid);
SET @gid := (SELECT `id` FROM `z_shop_offer_group` WHERE `offer_id` = @oid ORDER BY `sort_order`, `id` LIMIT 1);
INSERT INTO `z_shop_offer_option` (`group_id`, `label`, `sort_order`)
SELECT @gid, 'Distance', 0 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `z_shop_offer_option` WHERE `group_id` = @gid);
SET @opid := (SELECT `id` FROM `z_shop_offer_option` WHERE `group_id` = @gid ORDER BY `sort_order`, `id` LIMIT 1);
INSERT INTO `z_shop_offer_option_item` (`option_id`, `itemid`, `count`, `sort_order`, `slot_role`)
SELECT @opid, 12747, 1, 0, '' FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `z_shop_offer_option_item` WHERE `option_id` = @opid AND `itemid` = 12747);

-- Magic Scroll (itemid 12746) - 25 PP
SET @oid := (
  SELECT o.`id`
  FROM `z_shop_offer` o
  INNER JOIN `z_shop_offer_group` g ON g.`offer_id` = o.`id`
  INNER JOIN `z_shop_offer_option` op ON op.`group_id` = g.`id`
  INNER JOIN `z_shop_offer_option_item` oi ON oi.`option_id` = op.`id`
  WHERE o.`catalog` = 'donate' AND o.`offer_type` = 'item' AND oi.`itemid` = 12746
  LIMIT 1
);
INSERT INTO `z_shop_offer`
  (`catalog`, `points`, `offer_type`, `offer_name`, `offer_description`, `sort_order`, `pacc_days`, `active`)
SELECT 'donate', 25, 'item', 'Magic Imbuement Scroll',
  'Temporary +30 magic level for 1 hour. Use the scroll in-game.',
  55, 0, 1
FROM DUAL WHERE @oid IS NULL;
SET @oid := IFNULL(@oid, LAST_INSERT_ID());
INSERT INTO `z_shop_offer_group` (`offer_id`, `label`, `sort_order`, `min_select`, `max_select`)
SELECT @oid, 'Scroll', 0, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `z_shop_offer_group` WHERE `offer_id` = @oid);
SET @gid := (SELECT `id` FROM `z_shop_offer_group` WHERE `offer_id` = @oid ORDER BY `sort_order`, `id` LIMIT 1);
INSERT INTO `z_shop_offer_option` (`group_id`, `label`, `sort_order`)
SELECT @gid, 'Magic', 0 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `z_shop_offer_option` WHERE `group_id` = @gid);
SET @opid := (SELECT `id` FROM `z_shop_offer_option` WHERE `group_id` = @gid ORDER BY `sort_order`, `id` LIMIT 1);
INSERT INTO `z_shop_offer_option_item` (`option_id`, `itemid`, `count`, `sort_order`, `slot_role`)
SELECT @opid, 12746, 1, 0, '' FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `z_shop_offer_option_item` WHERE `option_id` = @opid AND `itemid` = 12746);

-- Haste Scroll (itemid 12748) - 15 PP
SET @oid := (
  SELECT o.`id`
  FROM `z_shop_offer` o
  INNER JOIN `z_shop_offer_group` g ON g.`offer_id` = o.`id`
  INNER JOIN `z_shop_offer_option` op ON op.`group_id` = g.`id`
  INNER JOIN `z_shop_offer_option_item` oi ON oi.`option_id` = op.`id`
  WHERE o.`catalog` = 'donate' AND o.`offer_type` = 'item' AND oi.`itemid` = 12748
  LIMIT 1
);
INSERT INTO `z_shop_offer`
  (`catalog`, `points`, `offer_type`, `offer_name`, `offer_description`, `sort_order`, `pacc_days`, `active`)
SELECT 'donate', 15, 'item', 'Haste Imbuement Scroll',
  'Temporary strong haste for 1 hour. Use the scroll in-game.',
  56, 0, 1
FROM DUAL WHERE @oid IS NULL;
SET @oid := IFNULL(@oid, LAST_INSERT_ID());
INSERT INTO `z_shop_offer_group` (`offer_id`, `label`, `sort_order`, `min_select`, `max_select`)
SELECT @oid, 'Scroll', 0, 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `z_shop_offer_group` WHERE `offer_id` = @oid);
SET @gid := (SELECT `id` FROM `z_shop_offer_group` WHERE `offer_id` = @oid ORDER BY `sort_order`, `id` LIMIT 1);
INSERT INTO `z_shop_offer_option` (`group_id`, `label`, `sort_order`)
SELECT @gid, 'Haste', 0 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `z_shop_offer_option` WHERE `group_id` = @gid);
SET @opid := (SELECT `id` FROM `z_shop_offer_option` WHERE `group_id` = @gid ORDER BY `sort_order`, `id` LIMIT 1);
INSERT INTO `z_shop_offer_option_item` (`option_id`, `itemid`, `count`, `sort_order`, `slot_role`)
SELECT @opid, 12748, 1, 0, '' FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `z_shop_offer_option_item` WHERE `option_id` = @opid AND `itemid` = 12748);

-- BEGIN CHANGE: enable optional quantity for imbuement scrolls (requires z_shop_offer_quantity.sql)
UPDATE `z_shop_offer` o
INNER JOIN `z_shop_offer_group` g ON g.`offer_id` = o.`id`
INNER JOIN `z_shop_offer_option` op ON op.`group_id` = g.`id`
INNER JOIN `z_shop_offer_option_item` oi ON oi.`option_id` = op.`id`
SET o.`allow_quantity` = 1,
    o.`max_quantity` = 100
WHERE o.`catalog` = 'donate'
  AND o.`offer_type` = 'item'
  AND oi.`itemid` IN (12742, 12743, 12744, 12745, 12746, 12747, 12748);
-- END CHANGE

COMMIT;

-- Verify:
-- SELECT o.id, o.offer_name, o.points, oi.itemid
-- FROM z_shop_offer o
-- JOIN z_shop_offer_group g ON g.offer_id = o.id
-- JOIN z_shop_offer_option op ON op.group_id = g.id
-- JOIN z_shop_offer_option_item oi ON oi.option_id = op.id
-- WHERE o.catalog = 'donate' AND oi.itemid BETWEEN 12742 AND 12748
-- ORDER BY o.sort_order, o.id;

-- END CHANGE
