-- Update Umre Tours structure to support optional sub-categories

USE tour_management;

-- Add category_id to umre_tours and make sub_category_id optional
ALTER TABLE umre_tours
ADD COLUMN category_id INT NOT NULL AFTER id,
ADD FOREIGN KEY (category_id) REFERENCES umre_categories(id) ON DELETE CASCADE;

-- Make sub_category_id optional (allow NULL)
ALTER TABLE umre_tours
MODIFY COLUMN sub_category_id INT NULL;

-- Update existing tours to set category_id based on their sub_category
UPDATE umre_tours t
JOIN umre_sub_categories sc ON t.sub_category_id = sc.id
SET t.category_id = sc.category_id;

SELECT 'Umre tours structure updated successfully!' as message;