-- =====================================================
-- Master Categories Table Creation and Data Population
-- =====================================================
-- This table contains all possible food categories that restaurants can inherit during onboarding
-- Each category includes Ionic icons and image references for consistent UI

-- Drop table if exists to ensure clean creation
DROP TABLE IF EXISTS master_categories;

-- Create master_categories table
CREATE TABLE master_categories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    slug VARCHAR(120) NOT NULL UNIQUE,
    description TEXT,
    ion_icon VARCHAR(50) NOT NULL,
    image VARCHAR(255),
    color_code VARCHAR(7) DEFAULT '#fd3f25',
    sort_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_name (name),
    INDEX idx_slug (slug),
    INDEX idx_sort_order (sort_order),
    INDEX idx_is_active (is_active)
);

-- =====================================================
-- Insert comprehensive food categories data
-- =====================================================

INSERT INTO master_categories (name, slug, description, ion_icon, image, color_code, sort_order) VALUES
-- Main Course Categories
('Appetizers & Starters', 'appetizers-starters', 'Light bites and starters to begin your meal', 'restaurant-outline', 'assets/categories/appetizers.jpg', '#fd3f25', 1),
('Soups & Salads', 'soups-salads', 'Fresh soups and healthy salads', 'leaf-outline', 'assets/categories/soups.jpg', '#20861d', 2),
('Main Course', 'main-course', 'Hearty main dishes and entrees', 'nutrition-outline', 'assets/categories/main-course.jpg', '#fd3f25', 3),
('Rice & Biryani', 'rice-biryani', 'Aromatic rice dishes and biryanis', 'globe-outline', 'assets/categories/biryani.jpg', '#ff8c00', 4),
('Noodles & Pasta', 'noodles-pasta', 'Asian noodles and Italian pasta dishes', 'fast-food-outline', 'assets/categories/noodles.jpg', '#8e44ad', 5),
('Breads & Rotis', 'breads-rotis', 'Fresh breads, rotis, and naans', 'bread-outline', 'assets/categories/breads.jpg', '#d4a574', 6),

-- Protein Categories
('Chicken', 'chicken', 'Delicious chicken preparations', 'chicken-outline', 'assets/categories/chicken.jpg', '#ff6b35', 7),
('Mutton & Lamb', 'mutton-lamb', 'Tender mutton and lamb dishes', 'meat-outline', 'assets/categories/mutton.jpg', '#c0392b', 8),
('Seafood', 'seafood', 'Fresh fish and seafood delicacies', 'fish-outline', 'assets/categories/seafood.jpg', '#3498db', 9),
('Vegetarian', 'vegetarian', 'Pure vegetarian dishes', 'leaf-outline', 'assets/categories/vegetarian.jpg', '#27ae60', 10),
('Vegan', 'vegan', 'Plant-based vegan options', 'flower-outline', 'assets/categories/vegan.jpg', '#2ecc71', 11),
('Eggs', 'eggs', 'Various egg preparations', 'egg-outline', 'assets/categories/eggs.jpg', '#f1c40f', 12),

-- Cuisine Types
('Indian', 'indian', 'Traditional Indian cuisine', 'flag-outline', 'assets/categories/indian.jpg', '#ff9933', 13),
('Chinese', 'chinese', 'Authentic Chinese dishes', 'restaurant-outline', 'assets/categories/chinese.jpg', '#dc143c', 14),
('Continental', 'continental', 'European and Continental dishes', 'wine-outline', 'assets/categories/continental.jpg', '#4a5568', 15),
('Italian', 'italian', 'Classic Italian cuisine', 'pizza-outline', 'assets/categories/italian.jpg', '#228b22', 16),
('Mexican', 'mexican', 'Spicy Mexican favorites', 'flame-outline', 'assets/categories/mexican.jpg', '#ff4500', 17),
('Thai', 'thai', 'Exotic Thai cuisine', 'leaf-outline', 'assets/categories/thai.jpg', '#32cd32', 18),
('Japanese', 'japanese', 'Authentic Japanese dishes', 'fish-outline', 'assets/categories/japanese.jpg', '#ff1493', 19),
('Korean', 'korean', 'Spicy Korean specialties', 'flame-outline', 'assets/categories/korean.jpg', '#ff6347', 20),

-- Fast Food & Street Food
('Pizza', 'pizza', 'Freshly baked pizzas', 'pizza-outline', 'assets/categories/pizza.jpg', '#ff6b35', 21),
('Burgers', 'burgers', 'Juicy burgers and sandwiches', 'fast-food-outline', 'assets/categories/burgers.jpg', '#e74c3c', 22),
('Sandwiches', 'sandwiches', 'Fresh sandwiches and wraps', 'fast-food-outline', 'assets/categories/sandwiches.jpg', '#34495e', 23),
('Wraps & Rolls', 'wraps-rolls', 'Delicious wraps and rolls', 'document-outline', 'assets/categories/wraps.jpg', '#9b59b6', 24),
('Street Food', 'street-food', 'Popular street food items', 'storefront-outline', 'assets/categories/street-food.jpg', '#f39c12', 25),
('Chaat', 'chaat', 'Tangy Indian street food', 'storefront-outline', 'assets/categories/chaat.jpg', '#e67e22', 26),
('Momos', 'momos', 'Steamed and fried dumplings', 'ellipse-outline', 'assets/categories/momos.jpg', '#1abc9c', 27),

-- Snacks & Sides
('Snacks', 'snacks', 'Quick bites and snacks', 'bag-outline', 'assets/categories/snacks.jpg', '#95a5a6', 28),
('Sides', 'sides', 'Side dishes and accompaniments', 'grid-outline', 'assets/categories/sides.jpg', '#7f8c8d', 29),
('Pickles & Papad', 'pickles-papad', 'Traditional pickles and crispy papads', 'jar-outline', 'assets/categories/pickles.jpg', '#d35400', 30),

-- Beverages
('Beverages', 'beverages', 'Refreshing drinks and beverages', 'water-outline', 'assets/categories/beverages.jpg', '#3498db', 31),
('Hot Drinks', 'hot-drinks', 'Tea, coffee, and hot beverages', 'cafe-outline', 'assets/categories/hot-drinks.jpg', '#8b4513', 32),
('Cold Drinks', 'cold-drinks', 'Refreshing cold beverages', 'snow-outline', 'assets/categories/cold-drinks.jpg', '#00bfff', 33),
('Fresh Juices', 'fresh-juices', 'Freshly squeezed fruit juices', 'nutrition-outline', 'assets/categories/juices.jpg', '#ff8c00', 34),
('Smoothies', 'smoothies', 'Healthy fruit and vegetable smoothies', 'fitness-outline', 'assets/categories/smoothies.jpg', '#ff69b4', 35),
('Shakes & Lassi', 'shakes-lassi', 'Thick shakes and traditional lassi', 'ice-cream-outline', 'assets/categories/shakes.jpg', '#dda0dd', 36),

-- Desserts & Sweets
('Desserts', 'desserts', 'Sweet treats and desserts', 'ice-cream-outline', 'assets/categories/desserts.jpg', '#ff1493', 37),
('Ice Cream', 'ice-cream', 'Cool ice creams and frozen treats', 'snow-outline', 'assets/categories/ice-cream.jpg', '#87ceeb', 38),
('Cakes & Pastries', 'cakes-pastries', 'Fresh cakes and pastries', 'gift-outline', 'assets/categories/cakes.jpg', '#ffb6c1', 39),
('Indian Sweets', 'indian-sweets', 'Traditional Indian mithai and sweets', 'heart-outline', 'assets/categories/sweets.jpg', '#ffd700', 40),
('Cookies & Biscuits', 'cookies-biscuits', 'Crunchy cookies and biscuits', 'ellipse-outline', 'assets/categories/cookies.jpg', '#daa520', 41),

-- Healthy & Special Diet
('Healthy', 'healthy', 'Nutritious and healthy options', 'fitness-outline', 'assets/categories/healthy.jpg', '#32cd32', 42),
('Low Calorie', 'low-calorie', 'Diet-friendly low calorie meals', 'leaf-outline', 'assets/categories/low-calorie.jpg', '#90ee90', 43),
('Keto', 'keto', 'Ketogenic diet friendly dishes', 'fitness-outline', 'assets/categories/keto.jpg', '#006400', 44),
('Gluten Free', 'gluten-free', 'Gluten-free food options', 'checkmark-outline', 'assets/categories/gluten-free.jpg', '#228b22', 45),
('Protein Rich', 'protein-rich', 'High protein meals for fitness', 'barbell-outline', 'assets/categories/protein.jpg', '#ff4500', 46),

-- Breakfast & Brunch
('Breakfast', 'breakfast', 'Morning breakfast items', 'sunny-outline', 'assets/categories/breakfast.jpg', '#ffa500', 47),
('South Indian', 'south-indian', 'Authentic South Indian breakfast', 'leaf-outline', 'assets/categories/south-indian.jpg', '#ff6347', 48),
('North Indian', 'north-indian', 'Traditional North Indian dishes', 'restaurant-outline', 'assets/categories/north-indian.jpg', '#dc143c', 49),
('Parathas', 'parathas', 'Stuffed and plain parathas', 'ellipse-outline', 'assets/categories/parathas.jpg', '#daa520', 50),

-- Special Categories
('Chef Special', 'chef-special', 'Chef recommended special dishes', 'star-outline', 'assets/categories/chef-special.jpg', '#ffd700', 51),
('Combo Meals', 'combo-meals', 'Value combo meal packages', 'gift-outline', 'assets/categories/combo.jpg', '#ff6b35', 52),
('Family Pack', 'family-pack', 'Large portions for family sharing', 'people-outline', 'assets/categories/family-pack.jpg', '#9370db', 53),
('Kids Menu', 'kids-menu', 'Special menu items for children', 'happy-outline', 'assets/categories/kids.jpg', '#ff69b4', 54),
('Lunch Box', 'lunch-box', 'Complete lunch box meals', 'briefcase-outline', 'assets/categories/lunch-box.jpg', '#4682b4', 55),

-- Regional Specialties
('Gujarati', 'gujarati', 'Traditional Gujarati cuisine', 'leaf-outline', 'assets/categories/gujarati.jpg', '#ff8c00', 56),
('Punjabi', 'punjabi', 'Rich Punjabi dishes', 'restaurant-outline', 'assets/categories/punjabi.jpg', '#ff4500', 57),
('Bengali', 'bengali', 'Sweet and savory Bengali cuisine', 'fish-outline', 'assets/categories/bengali.jpg', '#ff1493', 58),
('Maharashtrian', 'maharashtrian', 'Authentic Maharashtrian food', 'storefront-outline', 'assets/categories/maharashtrian.jpg', '#ff6347', 59),
('Rajasthani', 'rajasthani', 'Royal Rajasthani delicacies', 'diamond-outline', 'assets/categories/rajasthani.jpg', '#ffd700', 60);

-- =====================================================
-- Create indexes for better performance
-- =====================================================

CREATE INDEX idx_master_categories_active_sorted ON master_categories (is_active, sort_order);
CREATE INDEX idx_master_categories_color ON master_categories (color_code);

-- =====================================================
-- Display inserted data count
-- =====================================================

SELECT COUNT(*) as 'Total Categories Inserted' FROM master_categories;

-- =====================================================
-- Sample query to view all categories
-- =====================================================

-- SELECT id, name, slug, ion_icon, color_code, sort_order 
-- FROM master_categories 
-- WHERE is_active = 1 
-- ORDER BY sort_order ASC;
