-- =====================================================================
-- The checkout's coupon lives on the combined order only (2026-10-20)
--
-- Same as migration 2026_10_20_000002_move_coupon_to_combined_orders (run one
-- or the other, not both - running both changes nothing more: the second
-- finds no share left).
--
-- Before: each seller order carried a share of the coupon
--   orders.coupon_discount = the share
--   orders.grand_total     = products + tax (+ line shipping) - the share
-- After: the share goes back to the seller order, the whole coupon is on the
-- combined order:
--   orders.coupon_discount = 0
--   orders.grand_total     = products + tax (+ line shipping)
--   combined_orders.coupon_discount = the whole coupon
-- What the customer pays does not change (combined_orders.grand_total is
-- already orders + shipping - coupon and is not touched).
--
-- Every share is copied to coupon_allocation_backups first; to undo, run the
-- block at the end. Only orders of a combined order are touched.
-- After running: php artisan cache:clear.
-- =====================================================================

-- 0. check before: the sum of what customers pay (must be the same after step 4)
SELECT COUNT(*) AS orders_with_a_share, ROUND(SUM(coupon_discount), 2) AS shares
FROM orders WHERE combined_order_id IS NOT NULL AND coupon_discount <> 0;
SELECT ROUND(SUM(grand_total), 2) AS combined_grand_total FROM combined_orders;

-- 1. backup
CREATE TABLE IF NOT EXISTS `coupon_allocation_backups` (
    `order_id` int(10) UNSIGNED NOT NULL,
    `combined_order_id` int(10) UNSIGNED DEFAULT NULL,
    `coupon_discount` double(20,2) NOT NULL,
    `grand_total` double(20,2) NOT NULL,
    `moved_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`order_id`),
    KEY `coupon_allocation_backups_combined_order_id_index` (`combined_order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `coupon_allocation_backups` (`order_id`, `combined_order_id`, `coupon_discount`, `grand_total`, `moved_at`)
SELECT `id`, `combined_order_id`, `coupon_discount`, `grand_total`, NOW()
FROM `orders`
WHERE `combined_order_id` IS NOT NULL AND `coupon_discount` <> 0;

START TRANSACTION;

-- 2. the coupon the customer was given, on the combined order: the whole one - or, when
--    some seller orders are cancelled / returned, the shares of the orders still standing
UPDATE `combined_orders` co
JOIN (
    SELECT `combined_order_id`,
           SUM(`coupon_discount`) AS shares,
           SUM(CASE WHEN `delivery_status` IN ('cancelled', 'returned') THEN 0 ELSE `coupon_discount` END) AS live_shares,
           SUM(CASE WHEN `delivery_status` IN ('cancelled', 'returned') THEN 1 ELSE 0 END) AS voids,
           SUM(CASE WHEN `delivery_status` IN ('cancelled', 'returned') THEN 0 ELSE 1 END) AS lives
    FROM `orders`
    WHERE `combined_order_id` IS NOT NULL
    GROUP BY `combined_order_id`
    HAVING SUM(CASE WHEN `coupon_discount` <> 0 THEN 1 ELSE 0 END) > 0
) s ON s.`combined_order_id` = co.`id`
SET co.`coupon_discount` = ROUND(CASE WHEN s.voids > 0 AND s.lives > 0 THEN s.live_shares
                                      ELSE GREATEST(co.`coupon_discount`, s.shares) END, 2);

-- 3. the coupon reference on combined orders that have none
UPDATE `combined_orders` co
JOIN (
    SELECT `combined_order_id`, MIN(`id`) AS first_id
    FROM `orders`
    WHERE `combined_order_id` IS NOT NULL AND `coupon_discount` <> 0 AND (`coupon_id` IS NOT NULL OR `coupon_code` IS NOT NULL)
    GROUP BY `combined_order_id`
) f ON f.`combined_order_id` = co.`id`
JOIN `orders` o ON o.`id` = f.first_id
SET co.`coupon_id` = o.`coupon_id`, co.`coupon_code` = o.`coupon_code`
WHERE co.`coupon_id` IS NULL AND (co.`coupon_code` IS NULL OR co.`coupon_code` = '');

-- 4. each seller order gets its share back
UPDATE `orders`
SET `grand_total` = ROUND(`grand_total` + `coupon_discount`, 2), `coupon_discount` = 0
WHERE `combined_order_id` IS NOT NULL AND `coupon_discount` <> 0;

COMMIT;

-- 5. check after: no share left, combined_grand_total the same as in step 0
SELECT COUNT(*) AS orders_with_a_share FROM orders WHERE combined_order_id IS NOT NULL AND coupon_discount <> 0;
SELECT ROUND(SUM(grand_total), 2) AS combined_grand_total FROM combined_orders;

-- To undo (puts every share back; the combined orders keep the whole coupon,
-- which they also held before):
-- UPDATE `orders` o JOIN `coupon_allocation_backups` b ON b.`order_id` = o.`id`
-- SET o.`coupon_discount` = b.`coupon_discount`, o.`grand_total` = b.`grand_total`;