-- BEGIN CHANGE: Unify Premium + Guild shop into one catalog/history + normalized groups
-- Run on ot_new (backup first). Keeps *_legacy tables for manual drop after validation.
-- Safe to re-run only if _legacy already exists? NO - run once.

-- ---------------------------------------------------------------------------
-- 0) Prep: id map for guild offers that collide with donate ids
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `z_shop_id_map` (
  `old_guild_id` INT NOT NULL,
  `new_id` INT NOT NULL,
  PRIMARY KEY (`old_guild_id`),
  UNIQUE KEY `uniq_new` (`new_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ---------------------------------------------------------------------------
-- 1) New offer catalog
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `z_shop_offer_v2` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `catalog` VARCHAR(16) NOT NULL DEFAULT 'donate',
  `points` INT NOT NULL DEFAULT 0,
  `offer_type` VARCHAR(32) NOT NULL DEFAULT 'item',
  `offer_name` VARCHAR(255) NOT NULL DEFAULT '',
  `offer_description` TEXT NOT NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  `pacc_days` INT NOT NULL DEFAULT 0,
  `active` TINYINT UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `idx_catalog_type` (`catalog`, `offer_type`),
  KEY `idx_catalog_sort` (`catalog`, `sort_order`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `z_shop_offer_group` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `offer_id` INT UNSIGNED NOT NULL,
  `label` VARCHAR(64) NOT NULL DEFAULT '',
  `sort_order` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `min_select` TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `max_select` TINYINT UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `idx_offer_sort` (`offer_id`, `sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `z_shop_offer_option` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `group_id` INT UNSIGNED NOT NULL,
  `label` VARCHAR(64) NOT NULL DEFAULT '',
  `sort_order` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_group_sort` (`group_id`, `sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `z_shop_offer_option_item` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `option_id` INT UNSIGNED NOT NULL,
  `itemid` INT NOT NULL DEFAULT 0,
  `count` INT NOT NULL DEFAULT 1,
  `sort_order` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `slot_role` VARCHAR(16) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_option_sort` (`option_id`, `sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `z_shop_history_item_v2` (
  `id` INT NOT NULL,
  `catalog` VARCHAR(16) NOT NULL DEFAULT 'donate',
  `to_name` VARCHAR(255) NOT NULL DEFAULT '',
  `to_account` INT NOT NULL DEFAULT 0,
  `from_nick` VARCHAR(255) NOT NULL DEFAULT '',
  `from_account` INT NOT NULL DEFAULT 0,
  `price` INT NOT NULL DEFAULT 0,
  `offer_id` INT NOT NULL DEFAULT 0,
  `trans_state` VARCHAR(32) NOT NULL DEFAULT 'wait',
  `trans_start` INT NOT NULL DEFAULT 0,
  `trans_real` INT NOT NULL DEFAULT 0,
  `item_name` VARCHAR(255) NOT NULL DEFAULT '',
  `offer_type` INT NOT NULL DEFAULT 0,
  `serial_item` VARCHAR(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_from_account` (`from_account`),
  KEY `idx_catalog` (`catalog`),
  KEY `idx_offer` (`offer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `z_shop_history_pacc_v2` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `catalog` VARCHAR(16) NOT NULL DEFAULT 'donate',
  `to_name` VARCHAR(255) NOT NULL DEFAULT '',
  `to_account` INT NOT NULL DEFAULT 0,
  `from_nick` VARCHAR(255) NOT NULL DEFAULT '',
  `from_account` INT NOT NULL DEFAULT 0,
  `price` INT NOT NULL DEFAULT 0,
  `offer_id` INT NOT NULL DEFAULT 0,
  `pacc_days` INT NOT NULL DEFAULT 0,
  `trans_state` VARCHAR(32) NOT NULL DEFAULT 'wait',
  `trans_start` INT NOT NULL DEFAULT 0,
  `trans_real` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_from_account` (`from_account`),
  KEY `idx_catalog` (`catalog`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ---------------------------------------------------------------------------
-- 2) Migrate donate offers (keep same ids)
-- ---------------------------------------------------------------------------
INSERT INTO `z_shop_offer_v2`
  (`id`, `catalog`, `points`, `offer_type`, `offer_name`, `offer_description`, `sort_order`, `pacc_days`, `active`)
SELECT
  `id`,
  'donate',
  `points`,
  `offer_type`,
  `offer_name`,
  IFNULL(`offer_description`, ''),
  CAST(`type` AS SIGNED),
  CASE WHEN `offer_type` = 'pacc' THEN IFNULL(`count1`, 0) ELSE 0 END,
  1
FROM `z_shop_offer`
ON DUPLICATE KEY UPDATE
  `points` = VALUES(`points`),
  `offer_type` = VALUES(`offer_type`),
  `offer_name` = VALUES(`offer_name`);

-- ---------------------------------------------------------------------------
-- 3) Migrate guild offers (remap ids that collide)
-- ---------------------------------------------------------------------------
INSERT INTO `z_shop_id_map` (`old_guild_id`, `new_id`)
SELECT g.`id`,
  CASE
    WHEN EXISTS (SELECT 1 FROM `z_shop_offer_v2` v WHERE v.`id` = g.`id`)
      THEN (SELECT IFNULL(MAX(v2.`id`), 0) FROM `z_shop_offer_v2` v2) + g.`id` + 100000
    ELSE g.`id`
  END
FROM `z_shopguild_offer` g
ON DUPLICATE KEY UPDATE `new_id` = VALUES(`new_id`);

-- Fix collisions more carefully: assign sequential ids from max+1
-- Rebuild map for colliding rows only
DELETE FROM `z_shop_id_map`;

SET @next_id := (SELECT IFNULL(MAX(`id`), 0) FROM `z_shop_offer_v2`);

INSERT INTO `z_shop_id_map` (`old_guild_id`, `new_id`)
SELECT g.`id`,
  CASE
    WHEN EXISTS (SELECT 1 FROM `z_shop_offer_v2` v WHERE v.`id` = g.`id`)
      THEN (@next_id := @next_id + 1)
    ELSE g.`id`
  END
FROM `z_shopguild_offer` g
ORDER BY g.`id`;

INSERT INTO `z_shop_offer_v2`
  (`id`, `catalog`, `points`, `offer_type`, `offer_name`, `offer_description`, `sort_order`, `pacc_days`, `active`)
SELECT
  m.`new_id`,
  'guild',
  g.`points`,
  g.`offer_type`,
  g.`offer_name`,
  IFNULL(g.`offer_description`, ''),
  CAST(g.`type` AS SIGNED),
  CASE WHEN g.`offer_type` = 'pacc' THEN IFNULL(g.`count1`, 0) ELSE 0 END,
  1
FROM `z_shopguild_offer` g
INNER JOIN `z_shop_id_map` m ON m.`old_guild_id` = g.`id`
ON DUPLICATE KEY UPDATE
  `points` = VALUES(`points`),
  `offer_type` = VALUES(`offer_type`);

-- ---------------------------------------------------------------------------
-- 4) Helper: create fixed group+option+items from legacy slots (donate)
--     Skip pacc and bundle (bundle migrated separately)
-- ---------------------------------------------------------------------------

-- Donate fixed offers
INSERT INTO `z_shop_offer_group` (`offer_id`, `label`, `sort_order`, `min_select`, `max_select`)
SELECT o.`id`, 'Items', 0, 1, 1
FROM `z_shop_offer` o
WHERE o.`offer_type` NOT IN ('pacc', 'bundle')
  AND NOT EXISTS (
    SELECT 1 FROM `z_shop_offer_group` g WHERE g.`offer_id` = o.`id`
  );

INSERT INTO `z_shop_offer_option` (`group_id`, `label`, `sort_order`)
SELECT g.`id`, 'Default', 0
FROM `z_shop_offer_group` g
INNER JOIN `z_shop_offer` o ON o.`id` = g.`offer_id` AND o.`offer_type` NOT IN ('pacc', 'bundle')
WHERE g.`label` = 'Items'
  AND NOT EXISTS (SELECT 1 FROM `z_shop_offer_option` op WHERE op.`group_id` = g.`id`);

-- Slot 1
INSERT INTO `z_shop_offer_option_item` (`option_id`, `itemid`, `count`, `sort_order`, `slot_role`)
SELECT op.`id`, o.`itemid1`, GREATEST(IFNULL(o.`count1`, 1), 1), 0,
  CASE WHEN o.`offer_type` = 'container' THEN 'content' ELSE '' END
FROM `z_shop_offer` o
INNER JOIN `z_shop_offer_group` g ON g.`offer_id` = o.`id` AND g.`label` = 'Items'
INNER JOIN `z_shop_offer_option` op ON op.`group_id` = g.`id`
WHERE o.`offer_type` NOT IN ('pacc', 'bundle') AND IFNULL(o.`itemid1`, 0) > 0;

-- Slot 2
INSERT INTO `z_shop_offer_option_item` (`option_id`, `itemid`, `count`, `sort_order`, `slot_role`)
SELECT op.`id`, o.`itemid2`, GREATEST(IFNULL(o.`count2`, 1), 1), 1,
  CASE WHEN o.`offer_type` = 'container' THEN 'container' ELSE '' END
FROM `z_shop_offer` o
INNER JOIN `z_shop_offer_group` g ON g.`offer_id` = o.`id` AND g.`label` = 'Items'
INNER JOIN `z_shop_offer_option` op ON op.`group_id` = g.`id`
WHERE o.`offer_type` NOT IN ('pacc', 'bundle') AND IFNULL(o.`itemid2`, 0) > 0;

-- Slots 3-7
INSERT INTO `z_shop_offer_option_item` (`option_id`, `itemid`, `count`, `sort_order`, `slot_role`)
SELECT op.`id`, o.`itemid3`, GREATEST(IFNULL(o.`count3`, 1), 1), 2, ''
FROM `z_shop_offer` o
INNER JOIN `z_shop_offer_group` g ON g.`offer_id` = o.`id` AND g.`label` = 'Items'
INNER JOIN `z_shop_offer_option` op ON op.`group_id` = g.`id`
WHERE o.`offer_type` NOT IN ('pacc', 'bundle') AND IFNULL(o.`itemid3`, 0) > 0;

INSERT INTO `z_shop_offer_option_item` (`option_id`, `itemid`, `count`, `sort_order`, `slot_role`)
SELECT op.`id`, o.`itemid4`, GREATEST(IFNULL(o.`count4`, 1), 1), 3, ''
FROM `z_shop_offer` o
INNER JOIN `z_shop_offer_group` g ON g.`offer_id` = o.`id` AND g.`label` = 'Items'
INNER JOIN `z_shop_offer_option` op ON op.`group_id` = g.`id`
WHERE o.`offer_type` NOT IN ('pacc', 'bundle') AND IFNULL(o.`itemid4`, 0) > 0;

INSERT INTO `z_shop_offer_option_item` (`option_id`, `itemid`, `count`, `sort_order`, `slot_role`)
SELECT op.`id`, o.`itemid5`, GREATEST(IFNULL(o.`count5`, 1), 1), 4, ''
FROM `z_shop_offer` o
INNER JOIN `z_shop_offer_group` g ON g.`offer_id` = o.`id` AND g.`label` = 'Items'
INNER JOIN `z_shop_offer_option` op ON op.`group_id` = g.`id`
WHERE o.`offer_type` NOT IN ('pacc', 'bundle') AND IFNULL(o.`itemid5`, 0) > 0;

INSERT INTO `z_shop_offer_option_item` (`option_id`, `itemid`, `count`, `sort_order`, `slot_role`)
SELECT op.`id`, o.`itemid6`, GREATEST(IFNULL(o.`count6`, 1), 1), 5, ''
FROM `z_shop_offer` o
INNER JOIN `z_shop_offer_group` g ON g.`offer_id` = o.`id` AND g.`label` = 'Items'
INNER JOIN `z_shop_offer_option` op ON op.`group_id` = g.`id`
WHERE o.`offer_type` NOT IN ('pacc', 'bundle') AND IFNULL(o.`itemid6`, 0) > 0;

INSERT INTO `z_shop_offer_option_item` (`option_id`, `itemid`, `count`, `sort_order`, `slot_role`)
SELECT op.`id`, o.`itemid7`, GREATEST(IFNULL(o.`count7`, 1), 1), 6, ''
FROM `z_shop_offer` o
INNER JOIN `z_shop_offer_group` g ON g.`offer_id` = o.`id` AND g.`label` = 'Items'
INNER JOIN `z_shop_offer_option` op ON op.`group_id` = g.`id`
WHERE o.`offer_type` NOT IN ('pacc', 'bundle') AND IFNULL(o.`itemid7`, 0) > 0;

-- Guild fixed offers (same pattern via id map)
INSERT INTO `z_shop_offer_group` (`offer_id`, `label`, `sort_order`, `min_select`, `max_select`)
SELECT m.`new_id`, 'Items', 0, 1, 1
FROM `z_shopguild_offer` o
INNER JOIN `z_shop_id_map` m ON m.`old_guild_id` = o.`id`
WHERE o.`offer_type` NOT IN ('pacc', 'bundle')
  AND NOT EXISTS (SELECT 1 FROM `z_shop_offer_group` g WHERE g.`offer_id` = m.`new_id`);

INSERT INTO `z_shop_offer_option` (`group_id`, `label`, `sort_order`)
SELECT g.`id`, 'Default', 0
FROM `z_shop_offer_group` g
INNER JOIN `z_shop_id_map` m ON m.`new_id` = g.`offer_id`
INNER JOIN `z_shopguild_offer` o ON o.`id` = m.`old_guild_id` AND o.`offer_type` NOT IN ('pacc', 'bundle')
WHERE g.`label` = 'Items'
  AND NOT EXISTS (SELECT 1 FROM `z_shop_offer_option` op WHERE op.`group_id` = g.`id`);

INSERT INTO `z_shop_offer_option_item` (`option_id`, `itemid`, `count`, `sort_order`, `slot_role`)
SELECT op.`id`, o.`itemid1`, GREATEST(IFNULL(o.`count1`, 1), 1), 0,
  CASE WHEN o.`offer_type` = 'container' THEN 'content' ELSE '' END
FROM `z_shopguild_offer` o
INNER JOIN `z_shop_id_map` m ON m.`old_guild_id` = o.`id`
INNER JOIN `z_shop_offer_group` g ON g.`offer_id` = m.`new_id` AND g.`label` = 'Items'
INNER JOIN `z_shop_offer_option` op ON op.`group_id` = g.`id`
WHERE o.`offer_type` NOT IN ('pacc', 'bundle') AND IFNULL(o.`itemid1`, 0) > 0;

INSERT INTO `z_shop_offer_option_item` (`option_id`, `itemid`, `count`, `sort_order`, `slot_role`)
SELECT op.`id`, o.`itemid2`, GREATEST(IFNULL(o.`count2`, 1), 1), 1,
  CASE WHEN o.`offer_type` = 'container' THEN 'container' ELSE '' END
FROM `z_shopguild_offer` o
INNER JOIN `z_shop_id_map` m ON m.`old_guild_id` = o.`id`
INNER JOIN `z_shop_offer_group` g ON g.`offer_id` = m.`new_id` AND g.`label` = 'Items'
INNER JOIN `z_shop_offer_option` op ON op.`group_id` = g.`id`
WHERE o.`offer_type` NOT IN ('pacc', 'bundle') AND IFNULL(o.`itemid2`, 0) > 0;

INSERT INTO `z_shop_offer_option_item` (`option_id`, `itemid`, `count`, `sort_order`, `slot_role`)
SELECT op.`id`, o.`itemid3`, GREATEST(IFNULL(o.`count3`, 1), 1), 2, ''
FROM `z_shopguild_offer` o
INNER JOIN `z_shop_id_map` m ON m.`old_guild_id` = o.`id`
INNER JOIN `z_shop_offer_group` g ON g.`offer_id` = m.`new_id` AND g.`label` = 'Items'
INNER JOIN `z_shop_offer_option` op ON op.`group_id` = g.`id`
WHERE o.`offer_type` NOT IN ('pacc', 'bundle') AND IFNULL(o.`itemid3`, 0) > 0;

INSERT INTO `z_shop_offer_option_item` (`option_id`, `itemid`, `count`, `sort_order`, `slot_role`)
SELECT op.`id`, o.`itemid4`, GREATEST(IFNULL(o.`count4`, 1), 1), 3, ''
FROM `z_shopguild_offer` o
INNER JOIN `z_shop_id_map` m ON m.`old_guild_id` = o.`id`
INNER JOIN `z_shop_offer_group` g ON g.`offer_id` = m.`new_id` AND g.`label` = 'Items'
INNER JOIN `z_shop_offer_option` op ON op.`group_id` = g.`id`
WHERE o.`offer_type` NOT IN ('pacc', 'bundle') AND IFNULL(o.`itemid4`, 0) > 0;

INSERT INTO `z_shop_offer_option_item` (`option_id`, `itemid`, `count`, `sort_order`, `slot_role`)
SELECT op.`id`, o.`itemid5`, GREATEST(IFNULL(o.`count5`, 1), 1), 4, ''
FROM `z_shopguild_offer` o
INNER JOIN `z_shop_id_map` m ON m.`old_guild_id` = o.`id`
INNER JOIN `z_shop_offer_group` g ON g.`offer_id` = m.`new_id` AND g.`label` = 'Items'
INNER JOIN `z_shop_offer_option` op ON op.`group_id` = g.`id`
WHERE o.`offer_type` NOT IN ('pacc', 'bundle') AND IFNULL(o.`itemid5`, 0) > 0;

INSERT INTO `z_shop_offer_option_item` (`option_id`, `itemid`, `count`, `sort_order`, `slot_role`)
SELECT op.`id`, o.`itemid6`, GREATEST(IFNULL(o.`count6`, 1), 1), 5, ''
FROM `z_shopguild_offer` o
INNER JOIN `z_shop_id_map` m ON m.`old_guild_id` = o.`id`
INNER JOIN `z_shop_offer_group` g ON g.`offer_id` = m.`new_id` AND g.`label` = 'Items'
INNER JOIN `z_shop_offer_option` op ON op.`group_id` = g.`id`
WHERE o.`offer_type` NOT IN ('pacc', 'bundle') AND IFNULL(o.`itemid6`, 0) > 0;

INSERT INTO `z_shop_offer_option_item` (`option_id`, `itemid`, `count`, `sort_order`, `slot_role`)
SELECT op.`id`, o.`itemid7`, GREATEST(IFNULL(o.`count7`, 1), 1), 6, ''
FROM `z_shopguild_offer` o
INNER JOIN `z_shop_id_map` m ON m.`old_guild_id` = o.`id`
INNER JOIN `z_shop_offer_group` g ON g.`offer_id` = m.`new_id` AND g.`label` = 'Items'
INNER JOIN `z_shop_offer_option` op ON op.`group_id` = g.`id`
WHERE o.`offer_type` NOT IN ('pacc', 'bundle') AND IFNULL(o.`itemid7`, 0) > 0;

-- ---------------------------------------------------------------------------
-- 5) Migrate existing z_shop_bundle_* (if present) into offer groups
-- ---------------------------------------------------------------------------
-- Donate bundles
INSERT INTO `z_shop_offer_group` (`offer_id`, `label`, `sort_order`, `min_select`, `max_select`)
SELECT bg.`offer_id`, bg.`label`, bg.`sort_order`, bg.`min_select`, bg.`max_select`
FROM `z_shop_bundle_group` bg
WHERE bg.`catalog` = 'donate'
  AND EXISTS (SELECT 1 FROM `z_shop_offer_v2` v WHERE v.`id` = bg.`offer_id`)
  AND NOT EXISTS (
    SELECT 1 FROM `z_shop_offer_group` g
    WHERE g.`offer_id` = bg.`offer_id` AND g.`label` = bg.`label` AND g.`sort_order` = bg.`sort_order`
  );

-- Map old bundle group id -> new group id for donate (by offer+label+sort)
-- Options/items via join
INSERT INTO `z_shop_offer_option` (`group_id`, `label`, `sort_order`)
SELECT ng.`id`, bo.`label`, bo.`sort_order`
FROM `z_shop_bundle_option` bo
INNER JOIN `z_shop_bundle_group` bg ON bg.`id` = bo.`group_id` AND bg.`catalog` = 'donate'
INNER JOIN `z_shop_offer_group` ng
  ON ng.`offer_id` = bg.`offer_id` AND ng.`label` = bg.`label` AND ng.`sort_order` = bg.`sort_order`
WHERE NOT EXISTS (
  SELECT 1 FROM `z_shop_offer_option` x WHERE x.`group_id` = ng.`id` AND x.`label` = bo.`label` AND x.`sort_order` = bo.`sort_order`
);

INSERT INTO `z_shop_offer_option_item` (`option_id`, `itemid`, `count`, `sort_order`, `slot_role`)
SELECT nop.`id`, bi.`itemid`, GREATEST(IFNULL(bi.`count`, 1), 1), bi.`sort_order`, ''
FROM `z_shop_bundle_option_item` bi
INNER JOIN `z_shop_bundle_option` bo ON bo.`id` = bi.`option_id`
INNER JOIN `z_shop_bundle_group` bg ON bg.`id` = bo.`group_id` AND bg.`catalog` = 'donate'
INNER JOIN `z_shop_offer_group` ng
  ON ng.`offer_id` = bg.`offer_id` AND ng.`label` = bg.`label` AND ng.`sort_order` = bg.`sort_order`
INNER JOIN `z_shop_offer_option` nop
  ON nop.`group_id` = ng.`id` AND nop.`label` = bo.`label` AND nop.`sort_order` = bo.`sort_order`
WHERE IFNULL(bi.`itemid`, 0) > 0;

-- Guild bundles (remap offer_id)
INSERT INTO `z_shop_offer_group` (`offer_id`, `label`, `sort_order`, `min_select`, `max_select`)
SELECT m.`new_id`, bg.`label`, bg.`sort_order`, bg.`min_select`, bg.`max_select`
FROM `z_shop_bundle_group` bg
INNER JOIN `z_shop_id_map` m ON m.`old_guild_id` = bg.`offer_id`
WHERE bg.`catalog` = 'guild'
  AND NOT EXISTS (
    SELECT 1 FROM `z_shop_offer_group` g
    WHERE g.`offer_id` = m.`new_id` AND g.`label` = bg.`label` AND g.`sort_order` = bg.`sort_order`
  );

INSERT INTO `z_shop_offer_option` (`group_id`, `label`, `sort_order`)
SELECT ng.`id`, bo.`label`, bo.`sort_order`
FROM `z_shop_bundle_option` bo
INNER JOIN `z_shop_bundle_group` bg ON bg.`id` = bo.`group_id` AND bg.`catalog` = 'guild'
INNER JOIN `z_shop_id_map` m ON m.`old_guild_id` = bg.`offer_id`
INNER JOIN `z_shop_offer_group` ng
  ON ng.`offer_id` = m.`new_id` AND ng.`label` = bg.`label` AND ng.`sort_order` = bg.`sort_order`
WHERE NOT EXISTS (
  SELECT 1 FROM `z_shop_offer_option` x WHERE x.`group_id` = ng.`id` AND x.`label` = bo.`label` AND x.`sort_order` = bo.`sort_order`
);

INSERT INTO `z_shop_offer_option_item` (`option_id`, `itemid`, `count`, `sort_order`, `slot_role`)
SELECT nop.`id`, bi.`itemid`, GREATEST(IFNULL(bi.`count`, 1), 1), bi.`sort_order`, ''
FROM `z_shop_bundle_option_item` bi
INNER JOIN `z_shop_bundle_option` bo ON bo.`id` = bi.`option_id`
INNER JOIN `z_shop_bundle_group` bg ON bg.`id` = bo.`group_id` AND bg.`catalog` = 'guild'
INNER JOIN `z_shop_id_map` m ON m.`old_guild_id` = bg.`offer_id`
INNER JOIN `z_shop_offer_group` ng
  ON ng.`offer_id` = m.`new_id` AND ng.`label` = bg.`label` AND ng.`sort_order` = bg.`sort_order`
INNER JOIN `z_shop_offer_option` nop
  ON nop.`group_id` = ng.`id` AND nop.`label` = bo.`label` AND nop.`sort_order` = bo.`sort_order`
WHERE IFNULL(bi.`itemid`, 0) > 0;

-- ---------------------------------------------------------------------------
-- 6) History unify
-- ---------------------------------------------------------------------------
INSERT INTO `z_shop_history_item_v2`
  (`id`, `catalog`, `to_name`, `to_account`, `from_nick`, `from_account`, `price`, `offer_id`,
   `trans_state`, `trans_start`, `trans_real`, `item_name`, `offer_type`, `serial_item`)
SELECT
  h.`id`, 'donate', h.`to_name`, h.`to_account`, h.`from_nick`, h.`from_account`, h.`price`, h.`offer_id`,
  h.`trans_state`, h.`trans_start`, h.`trans_real`,
  IFNULL(h.`item_name`, ''), IFNULL(h.`offer_type`, 0), IFNULL(h.`serial_item`, '')
FROM `z_shop_history_item` h
ON DUPLICATE KEY UPDATE `catalog` = 'donate';

INSERT INTO `z_shop_history_item_v2`
  (`id`, `catalog`, `to_name`, `to_account`, `from_nick`, `from_account`, `price`, `offer_id`,
   `trans_state`, `trans_start`, `trans_real`, `item_name`, `offer_type`, `serial_item`)
SELECT
  h.`id`, 'guild', h.`to_name`, h.`to_account`, h.`from_nick`, h.`from_account`, h.`price`,
  IFNULL(m.`new_id`, h.`offer_id`),
  h.`trans_state`, h.`trans_start`, h.`trans_real`,
  IFNULL(h.`item_name`, ''), IFNULL(h.`offer_type`, 0), ''
FROM `z_shopguild_history_item` h
LEFT JOIN `z_shop_id_map` m ON m.`old_guild_id` = h.`offer_id`
ON DUPLICATE KEY UPDATE `catalog` = VALUES(`catalog`);

INSERT INTO `z_shop_history_pacc_v2`
  (`catalog`, `to_name`, `to_account`, `from_nick`, `from_account`, `price`, `offer_id`,
   `pacc_days`, `trans_state`, `trans_start`, `trans_real`)
SELECT
  'donate', h.`to_name`, h.`to_account`, h.`from_nick`, h.`from_account`, h.`price`, 0,
  h.`pacc_days`, h.`trans_state`, h.`trans_start`, h.`trans_real`
FROM `z_shop_history_pacc` h;

INSERT INTO `z_shop_history_pacc_v2`
  (`catalog`, `to_name`, `to_account`, `from_nick`, `from_account`, `price`, `offer_id`,
   `pacc_days`, `trans_state`, `trans_start`, `trans_real`)
SELECT
  'guild', h.`to_name`, h.`to_account`, h.`from_nick`, h.`from_account`, h.`price`, 0,
  h.`pacc_days`, h.`trans_state`, h.`trans_start`, h.`trans_real`
FROM `z_shopguild_history_pacc` h;

-- ---------------------------------------------------------------------------
-- 7) Swap names (legacy keep)
-- ---------------------------------------------------------------------------
RENAME TABLE
  `z_shop_offer` TO `z_shop_offer_legacy`,
  `z_shopguild_offer` TO `z_shopguild_offer_legacy`,
  `z_shop_offer_v2` TO `z_shop_offer`,
  `z_shop_history_item` TO `z_shop_history_item_legacy`,
  `z_shopguild_history_item` TO `z_shopguild_history_item_legacy`,
  `z_shop_history_item_v2` TO `z_shop_history_item`,
  `z_shop_history_pacc` TO `z_shop_history_pacc_legacy`,
  `z_shopguild_history_pacc` TO `z_shopguild_history_pacc_legacy`,
  `z_shop_history_pacc_v2` TO `z_shop_history_pacc`;

-- Bundle tables -> legacy (if exist)
-- Ignore errors if already renamed / missing: run manually if needed:
-- RENAME TABLE z_shop_bundle_group TO z_shop_bundle_group_legacy;
-- RENAME TABLE z_shop_bundle_option TO z_shop_bundle_option_legacy;
-- RENAME TABLE z_shop_bundle_option_item TO z_shop_bundle_option_item_legacy;

-- Sync AUTO_INCREMENT
SET @ai := (SELECT IFNULL(MAX(`id`), 0) + 1 FROM `z_shop_offer`);
SET @sql := CONCAT('ALTER TABLE `z_shop_offer` AUTO_INCREMENT = ', @ai);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- END CHANGE
