-- =====================================================
-- Menu Category Table Migration Script
-- =====================================================
-- This script adds missing fields to existing menu_categories table
-- and updates existing data with appropriate values

-- =====================================================
-- Step 1: Add missing columns to menu_categories table
-- =====================================================

-- Add slug column
ALTER TABLE menu_categories 
ADD COLUMN slug VARCHAR(120) NULL AFTER name;

-- Add color_code column  
ALTER TABLE menu_categories 
ADD COLUMN color_code VARCHAR(7) DEFAULT '#fd3f25' AFTER description;

-- Add ion_icon column (rename existing icon to ion_icon for consistency)
ALTER TABLE menu_categories 
ADD COLUMN ion_icon VARCHAR(50) NULL AFTER color_code;

-- =====================================================
-- Step 2: Update existing data with appropriate values
-- =====================================================

-- Update Beverages category
UPDATE menu_categories 
SET slug = 'beverages',
    color_code = '#3498db',
    ion_icon = 'cafe-outline'
WHERE id = 1 AND name = 'Beverages';

-- Update Bread category  
UPDATE menu_categories 
SET slug = 'bread',
    color_code = '#d4a574', 
    ion_icon = 'restaurant-outline'
WHERE id = 2 AND name = 'Bread';

-- Update Desserts category
UPDATE menu_categories 
SET slug = 'desserts',
    color_code = '#ff1493',
    ion_icon = 'ice-cream-outline' 
WHERE id = 3 AND name = 'Desserts';

-- Update Main Course category
UPDATE menu_categories 
SET slug = 'main-course',
    color_code = '#fd3f25',
    ion_icon = 'nutrition-outline'
WHERE id = 4 AND name = 'Main Course';

-- Update Starter category
UPDATE menu_categories 
SET slug = 'starter', 
    color_code = '#fd3f25',
    ion_icon = 'restaurant-outline'
WHERE id = 5 AND name = 'Starter';

-- =====================================================
-- Step 3: Copy existing icon values to ion_icon for all records
-- =====================================================

-- Update ion_icon based on existing icon field values
UPDATE menu_categories 
SET ion_icon = CASE 
    WHEN icon = 'local_cafe' THEN 'cafe-outline'
    WHEN icon = 'bakery_dining' THEN 'restaurant-outline' 
    WHEN icon = 'icecream' THEN 'ice-cream-outline'
    WHEN icon = 'dinner_dining' THEN 'nutrition-outline'
    WHEN icon = 'restaurant' THEN 'restaurant-outline'
    ELSE 'restaurant-outline'
END
WHERE ion_icon IS NULL OR ion_icon = '';

-- =====================================================
-- Step 4: Generate slugs for any categories without them
-- =====================================================

-- Update slug for categories that don't have slug set
UPDATE menu_categories 
SET slug = LOWER(TRIM(REPLACE(REPLACE(REPLACE(name, ' ', '-'), '&', 'and'), '--', '-')))
WHERE slug IS NULL OR slug = '';

-- =====================================================
-- Step 5: Set default color codes for categories without them
-- =====================================================

-- Update color_code for categories that don't have color set
UPDATE menu_categories 
SET color_code = CASE 
    WHEN LOWER(name) LIKE '%beverage%' OR LOWER(name) LIKE '%drink%' OR LOWER(name) LIKE '%juice%' THEN '#3498db'
    WHEN LOWER(name) LIKE '%dessert%' OR LOWER(name) LIKE '%sweet%' OR LOWER(name) LIKE '%ice%' THEN '#ff1493'
    WHEN LOWER(name) LIKE '%bread%' OR LOWER(name) LIKE '%roti%' OR LOWER(name) LIKE '%naan%' THEN '#d4a574'
    WHEN LOWER(name) LIKE '%starter%' OR LOWER(name) LIKE '%appetizer%' THEN '#20861d'
    WHEN LOWER(name) LIKE '%main%' OR LOWER(name) LIKE '%course%' THEN '#fd3f25'
    WHEN LOWER(name) LIKE '%chicken%' THEN '#ff6b35'
    WHEN LOWER(name) LIKE '%vegetarian%' OR LOWER(name) LIKE '%veg%' THEN '#27ae60'
    WHEN LOWER(name) LIKE '%pizza%' THEN '#ff6b35'
    WHEN LOWER(name) LIKE '%chinese%' THEN '#dc143c'
    WHEN LOWER(name) LIKE '%healthy%' THEN '#32cd32'
    ELSE '#fd3f25'
END
WHERE color_code IS NULL OR color_code = '' OR color_code = '#fd3f25';

-- =====================================================
-- Step 6: Add indexes for better performance
-- =====================================================

-- Add index for slug column
CREATE INDEX idx_menu_categories_slug ON menu_categories (slug);

-- Add index for color_code column  
CREATE INDEX idx_menu_categories_color ON menu_categories (color_code);

-- Add index for ion_icon column
CREATE INDEX idx_menu_categories_ion_icon ON menu_categories (ion_icon);

-- Composite index for active categories with sorting
CREATE INDEX idx_menu_categories_active_sorted ON menu_categories (restaurant_id, is_active, sort_order);

-- =====================================================
-- Step 7: Make slug column required after data population
-- =====================================================

-- Make slug column NOT NULL after all data is populated
ALTER TABLE menu_categories 
MODIFY COLUMN slug VARCHAR(120) NOT NULL;

-- Add unique constraint on slug per restaurant
ALTER TABLE menu_categories 
ADD CONSTRAINT uk_menu_categories_restaurant_slug UNIQUE (restaurant_id, slug);

-- =====================================================
-- Step 8: Validation queries
-- =====================================================

-- Check updated data
SELECT 
    id,
    restaurant_id,
    name,
    slug,
    color_code,
    icon as old_icon,
    ion_icon as new_ion_icon,
    sort_order,
    is_active
FROM menu_categories 
ORDER BY restaurant_id, sort_order;

-- Count total categories updated
SELECT COUNT(*) as 'Total Categories Updated' FROM menu_categories;

-- Check for any missing values
SELECT 
    'Missing Slugs' as issue_type,
    COUNT(*) as count
FROM menu_categories 
WHERE slug IS NULL OR slug = ''
UNION ALL
SELECT 
    'Missing Ion Icons' as issue_type,
    COUNT(*) as count  
FROM menu_categories
WHERE ion_icon IS NULL OR ion_icon = ''
UNION ALL
SELECT 
    'Missing Color Codes' as issue_type,
    COUNT(*) as count
FROM menu_categories 
WHERE color_code IS NULL OR color_code = '';

-- =====================================================
-- Step 9: Optional - Remove old icon column (uncomment if needed)
-- =====================================================

-- WARNING: Only run this after confirming ion_icon column is working properly
-- ALTER TABLE menu_categories DROP COLUMN icon;

-- =====================================================
-- Step 10: Sample insert for new categories using master_categories
-- =====================================================

-- Example of how to insert new categories from master_categories table
/*
INSERT INTO menu_categories (restaurant_id, name, slug, description, color_code, ion_icon, sort_order, is_active, show_in_restaurant, show_in_online, created_at, updated_at)
SELECT 
    1 as restaurant_id,  -- Replace with actual restaurant_id
    mc.name,
    mc.slug, 
    mc.description,
    mc.color_code,
    mc.ion_icon,
    mc.sort_order,
    1 as is_active,
    1 as show_in_restaurant, 
    1 as show_in_online,
    NOW() as created_at,
    NOW() as updated_at
FROM master_categories mc
WHERE mc.name IN ('Pizza', 'Chinese', 'Healthy') -- Add desired categories
AND NOT EXISTS (
    SELECT 1 FROM menu_categories 
    WHERE restaurant_id = 1 AND slug = mc.slug
);
*/
