-- BEGIN CHANGE: reconcile shop history stuck on wait after in-game delivery
-- Cause: shop.lua called Result:free() on empty SELECT (queue id already gone),
-- which error()'d before UPDATE trans_state='realized'.
-- Safe: only touches rows with no matching z_ots_comunication left.
-- Run once on ot_new after deploying fixed shop.lua (or anytime to clean orphans).

-- Preview (optional):
-- SELECT h.id, h.to_name, h.item_name, h.queue_ids, h.trans_state, h.trans_start
-- FROM z_shop_history_item h
-- WHERE (h.trans_state = 'wait' OR h.trans_real = 0 OR h.trans_real IS NULL)
--   AND (
--     ((h.queue_ids IS NULL OR h.queue_ids = '')
--       AND NOT EXISTS (SELECT 1 FROM z_ots_comunication c WHERE c.id = h.id))
--     OR
--     (h.queue_ids IS NOT NULL AND h.queue_ids <> ''
--       AND NOT EXISTS (
--         SELECT 1 FROM z_ots_comunication c
--         WHERE FIND_IN_SET(c.id, REPLACE(h.queue_ids, ' ', '')) > 0
--       ))
--   );

UPDATE `z_shop_history_item` h
SET
  h.`trans_state` = 'realized',
  h.`trans_real` = UNIX_TIMESTAMP()
WHERE (h.`trans_state` = 'wait' OR h.`trans_real` = 0 OR h.`trans_real` IS NULL)
  AND (
    (
      (h.`queue_ids` IS NULL OR h.`queue_ids` = '')
      AND NOT EXISTS (SELECT 1 FROM `z_ots_comunication` c WHERE c.`id` = h.`id`)
    )
    OR
    (
      h.`queue_ids` IS NOT NULL AND h.`queue_ids` <> ''
      AND NOT EXISTS (
        SELECT 1 FROM `z_ots_comunication` c
        WHERE FIND_IN_SET(c.`id`, REPLACE(h.`queue_ids`, ' ', '')) > 0
      )
    )
  );

-- PACC is instant on buy; any wait/trans_real=0 row is anomalous.
UPDATE `z_shop_history_pacc`
SET
  `trans_state` = 'realized',
  `trans_real` = IFNULL(NULLIF(`trans_real`, 0), UNIX_TIMESTAMP())
WHERE `trans_state` = 'wait' OR `trans_real` = 0 OR `trans_real` IS NULL;

-- END CHANGE
