-- ResellBD Step 10: order lifecycle and history. Apply only to the NEW ResellBD database.
SET NAMES utf8mb4;
ALTER TABLE orders MODIFY status ENUM('pending','confirmed','processing','packed','shipped','delivered','cancelled','returned') NOT NULL DEFAULT 'pending';
CREATE TABLE IF NOT EXISTS order_status_history (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 order_id BIGINT UNSIGNED NOT NULL,
 from_status ENUM('pending','confirmed','processing','packed','shipped','delivered','cancelled','returned') NULL,
 to_status ENUM('pending','confirmed','processing','packed','shipped','delivered','cancelled','returned') NOT NULL,
 reason VARCHAR(1000) NULL,
 actor_user_id BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(id), KEY idx_order_history_order_date(order_id,created_at),
 CONSTRAINT fk_order_history_order FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE RESTRICT ON UPDATE CASCADE,
 CONSTRAINT fk_order_history_actor FOREIGN KEY(actor_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO order_status_history(order_id,from_status,to_status,reason,actor_user_id,created_at)
SELECT id,NULL,status,'Step 10 migration: existing status recorded',NULL,created_at FROM orders
WHERE NOT EXISTS (SELECT 1 FROM order_status_history h WHERE h.order_id=orders.id);