-- ============================================
-- MULTI-VENDOR PRODUCTION DATABASE UPDATE
-- Date: 2025-09-01
-- Description: Convert single restaurant to multi-vendor system
-- ============================================

-- STEP 1: Create restaurants table
CREATE TABLE `restaurants` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `slug` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `description` text COLLATE utf8mb4_unicode_ci,
    `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `phone` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
    `address` text COLLATE utf8mb4_unicode_ci NOT NULL,
    `city` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
    `state` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
    `pincode` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
    `latitude` decimal(10,8) DEFAULT NULL,
    `longitude` decimal(11,8) DEFAULT NULL,
    `logo_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `cover_image_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `cuisine_types` json NOT NULL,
    `business_hours` json DEFAULT NULL,
    `fssai_license` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `gst_number` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `pan_number` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `bank_account_details` json DEFAULT NULL,
    `commission_rate` decimal(5,2) NOT NULL DEFAULT '5.00',
    `status` enum('active','inactive','pending','suspended') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
    `is_verified` tinyint(1) NOT NULL DEFAULT '0',
    `subscription_plan` enum('basic','premium','enterprise') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'basic',
    `subscription_expires_at` timestamp NULL DEFAULT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `restaurants_slug_unique` (`slug`),
    UNIQUE KEY `restaurants_email_unique` (`email`),
    KEY `restaurants_status_index` (`status`),
    KEY `restaurants_is_verified_index` (`is_verified`),
    KEY `restaurants_city_index` (`city`),
    KEY `restaurants_status_is_verified_index` (`status`,`is_verified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- STEP 2: Update users table
ALTER TABLE `users` 
ADD COLUMN `restaurant_id` bigint unsigned NULL AFTER `role`;

-- Add foreign key constraint
ALTER TABLE `users` 
ADD CONSTRAINT `users_restaurant_id_foreign` 
FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE;

-- Add index for performance
ALTER TABLE `users` ADD INDEX `users_restaurant_id_index` (`restaurant_id`);

-- Update role enum to include super_admin
ALTER TABLE `users` 
MODIFY COLUMN `role` enum('super_admin','admin','staff','manager') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'staff';

-- STEP 3: Insert default restaurant (Chahat Hotel)
INSERT INTO `restaurants` (
    `name`, `slug`, `description`, `email`, `phone`, `address`, 
    `city`, `state`, `pincode`, `latitude`, `longitude`,
    `cuisine_types`, `business_hours`, `fssai_license`,
    `status`, `is_verified`, `subscription_plan`, `created_at`, `updated_at`
) VALUES (
    'Chahat Hotel',
    'chahat-hotel',
    'Taste में Best - Authentic Indian cuisine in Dhanbad',
    'chahathotel0828@gmail.com',
    '+91-8002264900',
    'Domanpur, Rajganj, Dhanbad - 828113',
    'Dhanbad',
    'Jharkhand',
    '828113',
    23.7957,
    86.4304,
    '["Indian", "Chinese", "Continental"]',
    '{"monday": {"open": "08:00", "close": "23:00"}, "tuesday": {"open": "08:00", "close": "23:00"}, "wednesday": {"open": "08:00", "close": "23:00"}, "thursday": {"open": "08:00", "close": "23:00"}, "friday": {"open": "08:00", "close": "23:00"}, "saturday": {"open": "08:00", "close": "23:00"}, "sunday": {"open": "08:00", "close": "23:00"}}',
    '21125172000153',
    'active',
    1,
    'premium',
    NOW(),
    NOW()
);

-- STEP 4: Get the restaurant ID for future updates
SET @restaurant_id = LAST_INSERT_ID();

-- STEP 5: Update existing users to belong to the default restaurant
UPDATE `users` SET 
    `restaurant_id` = @restaurant_id;

-- STEP 6: Add restaurant_id to existing tables
-- Menu Items
ALTER TABLE `menu_items` 
ADD COLUMN `restaurant_id` bigint unsigned NOT NULL DEFAULT 1 AFTER `id`;

UPDATE `menu_items` SET `restaurant_id` = @restaurant_id;

ALTER TABLE `menu_items` 
ADD CONSTRAINT `menu_items_restaurant_id_foreign` 
FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE;

-- Orders
ALTER TABLE `orders` 
ADD COLUMN `restaurant_id` bigint unsigned NOT NULL DEFAULT 1 AFTER `id`;

UPDATE `orders` SET `restaurant_id` = @restaurant_id;

ALTER TABLE `orders` 
ADD CONSTRAINT `orders_restaurant_id_foreign` 
FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE;

-- Staff
ALTER TABLE `staff` 
ADD COLUMN `restaurant_id` bigint unsigned NOT NULL DEFAULT 1 AFTER `id`;

UPDATE `staff` SET `restaurant_id` = @restaurant_id;

ALTER TABLE `staff` 
ADD CONSTRAINT `staff_restaurant_id_foreign` 
FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE;

-- Inventories
ALTER TABLE `inventories` 
ADD COLUMN `restaurant_id` bigint unsigned NOT NULL DEFAULT 1 AFTER `id`;

UPDATE `inventories` SET `restaurant_id` = @restaurant_id;

ALTER TABLE `inventories` 
ADD CONSTRAINT `inventories_restaurant_id_foreign` 
FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE;

-- Coupons
ALTER TABLE `coupons` 
ADD COLUMN `restaurant_id` bigint unsigned NOT NULL DEFAULT 1 AFTER `id`;

UPDATE `coupons` SET `restaurant_id` = @restaurant_id;

ALTER TABLE `coupons` 
ADD CONSTRAINT `coupons_restaurant_id_foreign` 
FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE;

-- Menu Categories
ALTER TABLE `menu_categories` 
ADD COLUMN `restaurant_id` bigint unsigned NOT NULL DEFAULT 1 AFTER `id`;

UPDATE `menu_categories` SET `restaurant_id` = @restaurant_id;

ALTER TABLE `menu_categories` 
ADD CONSTRAINT `menu_categories_restaurant_id_foreign` 
FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE;

-- Restaurant Settings (Make per-restaurant)
ALTER TABLE `restaurant_settings` 
ADD COLUMN `restaurant_id` bigint unsigned NOT NULL DEFAULT 1 AFTER `id`;

UPDATE `restaurant_settings` SET `restaurant_id` = @restaurant_id;

ALTER TABLE `restaurant_settings` 
ADD CONSTRAINT `restaurant_settings_restaurant_id_foreign` 
FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE;

-- Add unique constraint for restaurant settings
ALTER TABLE `restaurant_settings` 
ADD CONSTRAINT `restaurant_settings_restaurant_unique` UNIQUE (`restaurant_id`);

-- STEP 7: Add indexes for performance optimization
ALTER TABLE `menu_items` ADD INDEX `menu_items_restaurant_id_index` (`restaurant_id`);
ALTER TABLE `orders` ADD INDEX `orders_restaurant_id_index` (`restaurant_id`);
ALTER TABLE `staff` ADD INDEX `staff_restaurant_id_index` (`restaurant_id`);
ALTER TABLE `inventories` ADD INDEX `inventories_restaurant_id_index` (`restaurant_id`);
ALTER TABLE `coupons` ADD INDEX `coupons_restaurant_id_index` (`restaurant_id`);
ALTER TABLE `menu_categories` ADD INDEX `menu_categories_restaurant_id_index` (`restaurant_id`);
ALTER TABLE `restaurant_settings` ADD INDEX `restaurant_settings_restaurant_id_index` (`restaurant_id`);

-- STEP 8: Create a super admin user (optional - uncomment if needed)
-- INSERT INTO `users` (
--     `name`, `email`, `password`, `role`, `restaurant_id`, `created_at`, `updated_at`
-- ) VALUES (
--     'Super Admin',
--     'superadmin@chahathotel.com',
--     '$2y$12$your_hashed_password_here', -- Change this to actual hashed password
--     'super_admin',
--     NULL, -- Super admin doesn't belong to any specific restaurant
--     NOW(),
--     NOW()
-- );

-- STEP 9: Verification queries (Run these to verify the changes)
-- SELECT 'Restaurants created:', COUNT(*) FROM restaurants;
-- SELECT 'Users updated:', COUNT(*) FROM users WHERE restaurant_id IS NOT NULL;
-- SELECT 'Menu items updated:', COUNT(*) FROM menu_items WHERE restaurant_id IS NOT NULL;
-- SELECT 'Orders updated:', COUNT(*) FROM orders WHERE restaurant_id IS NOT NULL;

-- STEP 10: Cleanup - Remove default values after data migration
-- ALTER TABLE `menu_items` ALTER COLUMN `restaurant_id` DROP DEFAULT;
-- ALTER TABLE `orders` ALTER COLUMN `restaurant_id` DROP DEFAULT;
-- ALTER TABLE `staff` ALTER COLUMN `restaurant_id` DROP DEFAULT;
-- ALTER TABLE `inventories` ALTER COLUMN `restaurant_id` DROP DEFAULT;
-- ALTER TABLE `coupons` ALTER COLUMN `restaurant_id` DROP DEFAULT;
-- ALTER TABLE `menu_categories` ALTER COLUMN `restaurant_id` DROP DEFAULT;
-- ALTER TABLE `restaurant_settings` ALTER COLUMN `restaurant_id` DROP DEFAULT;

-- ============================================
-- END OF MIGRATION SCRIPT
-- ============================================

-- IMPORTANT NOTES:
-- 1. Take a full database backup before running this script
-- 2. Test on a staging environment first
-- 3. Run during maintenance window to avoid conflicts
-- 4. Monitor application logs after deployment
-- 5. Update application code to use restaurant_id in queries
