-- fill in created_on column with created_at values if created_on is null
UPDATE orders
SET created_on = created_at
WHERE created_on IS NULL;
-- drop is_escalated column
ALTER TABLE orders DROP COLUMN is_escalated;
-- drop device_id column
ALTER TABLE orders DROP COLUMN device_id;
-- rename oracle_status to integration_status
ALTER TABLE orders CHANGE oracle_status integration_status VARCHAR(255);
-- rename oracle_message to integration_message
ALTER TABLE orders CHANGE oracle_message integration_message VARCHAR(6000);
-- add time_to_confirm column
ALTER TABLE orders ADD COLUMN time_to_confirm INT;
-- add room_number column
ALTER TABLE orders ADD COLUMN room_number VARCHAR(255);
-- fill in time_to_confirm column with difference between created_on and confirmed_at
UPDATE orders 
SET time_to_confirm = TIMESTAMPDIFF(SECOND, created_on, confirmed_at)
WHERE confirmed_at IS NOT NULL;
-- rename restaurant_id to venue_id
ALTER TABLE orders CHANGE restaurant_id venue_id VARCHAR(255);
-- add time_to_done column
ALTER TABLE orders ADD COLUMN time_to_done INT;
-- fill in time_to_done column with difference between confirmed_at and done_at
UPDATE orders
SET time_to_done = TIMESTAMPDIFF(SECOND, confirmed_at, done_at)
WHERE done_at IS NOT NULL;
-- add time_after_escalation column
ALTER TABLE orders ADD COLUMN time_after_escalation INT;
-- fill in time_after_escalation column with difference between escalated_at and confirmed_at
UPDATE orders
SET time_after_escalation = TIMESTAMPDIFF(SECOND, escalated_at, confirmed_at)
WHERE escalated_at IS NOT NULL;
-- rename restaurant_id to venue_id
ALTER TABLE items CHANGE restaurant_id venue_id VARCHAR(255);
-- add addons table
CREATE TABLE addons (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    item_id INT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    
);



