-- Admin -> Orders: when each order was confirmed (orders.confirmed_at).
-- Same as migration 2026_10_09_000001_add_confirmed_at_to_orders; run one or the other, once.
--
-- confirmed_at = the first time delivery_status reached confirmed or later. From now on it is
-- stamped by the Order model whichever screen changes the status. Earlier orders get it from the
-- audit log (kept since 2026-09-19), else the staff "ready" decision on Confirm Orders made by
-- someone (rows backfilled without a person carry that screen's deploy time); older
-- orders stay NULL and the list shows "date not recorded". Orders back to pending stay NULL.
--
-- After running: php artisan cache:clear

ALTER TABLE `orders` ADD COLUMN `confirmed_at` TIMESTAMP NULL DEFAULT NULL;

UPDATE `orders` o
JOIN (
    SELECT a.auditable_id AS order_id, MIN(a.created_at) AS at
    FROM `audits` a
    WHERE a.auditable_type = 'App\\Models\\Order'
      AND JSON_VALID(a.new_values)
      AND JSON_UNQUOTE(JSON_EXTRACT(a.new_values, '$.delivery_status')) IN ('confirmed', 'picked_up', 'on_the_way', 'delivered')
    GROUP BY a.auditable_id
) first_confirmed ON first_confirmed.order_id = o.id
SET o.confirmed_at = first_confirmed.at
WHERE o.confirmed_at IS NULL AND o.delivery_status != 'pending';

UPDATE `orders` o
JOIN `order_follow_ups` f ON f.order_id = o.id AND f.status = 'ready' AND f.decided_at IS NOT NULL AND f.decided_by_id IS NOT NULL
SET o.confirmed_at = f.decided_at
WHERE o.confirmed_at IS NULL AND o.delivery_status != 'pending';