-- Human-friendly order number: YYYYMM + 4-digit monthly sequence (e.g. 2026070001).
-- Kept separate from the auto-increment PK `id`, which stays the internal/FK key.
-- Already applied locally; run on the production DB at deploy time.

ALTER TABLE orders ADD COLUMN order_number VARCHAR(20) NULL UNIQUE AFTER id;

-- Backfill existing orders per creation month, ordered by id
UPDATE orders o
JOIN (
    SELECT id,
           CONCAT(
               DATE_FORMAT(created_at, '%Y%m'),
               LPAD(ROW_NUMBER() OVER (PARTITION BY DATE_FORMAT(created_at, '%Y%m') ORDER BY id), 4, '0')
           ) AS onum
    FROM orders
) x ON x.id = o.id
SET o.order_number = x.onum;