-- ALADIN B2B Plus - Quick Database Fix
-- Run this SQL script to add missing columns immediately

-- Fix products table
ALTER TABLE `products` 
ADD COLUMN IF NOT EXISTS `is_featured` BOOLEAN DEFAULT FALSE AFTER `is_active`,
ADD COLUMN IF NOT EXISTS `is_digital` BOOLEAN DEFAULT FALSE AFTER `is_featured`,
ADD COLUMN IF NOT EXISTS `published_at` TIMESTAMP NULL AFTER `sales_count`,
ADD COLUMN IF NOT EXISTS `average_rating` DECIMAL(3,2) DEFAULT 0 AFTER `metadata`,
ADD COLUMN IF NOT EXISTS `reviews_count` INT DEFAULT 0 AFTER `average_rating`,
ADD COLUMN IF NOT EXISTS `views_count` INT DEFAULT 0 AFTER `reviews_count`,
ADD COLUMN IF NOT EXISTS `sales_count` INT DEFAULT 0 AFTER `views_count`,
ADD COLUMN IF NOT EXISTS `min_order_quantity` INT DEFAULT 1 AFTER `published_at`,
ADD COLUMN IF NOT EXISTS `max_order_quantity` INT NULL AFTER `min_order_quantity`,
ADD COLUMN IF NOT EXISTS `tax_class` VARCHAR(255) NULL AFTER `max_order_quantity`,
ADD COLUMN IF NOT EXISTS `shipping_class` VARCHAR(255) NULL AFTER `tax_class`,
ADD COLUMN IF NOT EXISTS `warranty_period` INT NULL AFTER `shipping_class`,
ADD COLUMN IF NOT EXISTS `return_policy` TEXT NULL AFTER `warranty_period`;

-- Fix companies table
ALTER TABLE `companies` 
ADD COLUMN IF NOT EXISTS `is_featured` BOOLEAN DEFAULT FALSE AFTER `is_verified`,
ADD COLUMN IF NOT EXISTS `is_premium` BOOLEAN DEFAULT FALSE AFTER `is_featured`,
ADD COLUMN IF NOT EXISTS `verified_at` TIMESTAMP NULL AFTER `is_premium`,
ADD COLUMN IF NOT EXISTS `employees_count` INT NULL AFTER `verified_at`,
ADD COLUMN IF NOT EXISTS `annual_revenue` DECIMAL(15,2) NULL AFTER `employees_count`,
ADD COLUMN IF NOT EXISTS `founded_year` INT NULL AFTER `annual_revenue`,
ADD COLUMN IF NOT EXISTS `business_hours` JSON NULL AFTER `founded_year`,
ADD COLUMN IF NOT EXISTS `languages_spoken` JSON NULL AFTER `business_hours`,
ADD COLUMN IF NOT EXISTS `certifications` JSON NULL AFTER `languages_spoken`,
ADD COLUMN IF NOT EXISTS `awards` JSON NULL AFTER `certifications`,
ADD COLUMN IF NOT EXISTS `payment_terms` JSON NULL AFTER `awards`,
ADD COLUMN IF NOT EXISTS `shipping_methods` JSON NULL AFTER `payment_terms`,
ADD COLUMN IF NOT EXISTS `return_policy` TEXT NULL AFTER `shipping_methods`,
ADD COLUMN IF NOT EXISTS `warranty_policy` TEXT NULL AFTER `return_policy`,
ADD COLUMN IF NOT EXISTS `minimum_order_value` DECIMAL(10,2) NULL AFTER `warranty_policy`,
ADD COLUMN IF NOT EXISTS `lead_time` VARCHAR(255) NULL AFTER `minimum_order_value`,
ADD COLUMN IF NOT EXISTS `production_capacity` VARCHAR(255) NULL AFTER `lead_time`,
ADD COLUMN IF NOT EXISTS `quality_standards` JSON NULL AFTER `production_capacity`,
ADD COLUMN IF NOT EXISTS `export_markets` JSON NULL AFTER `quality_standards`,
ADD COLUMN IF NOT EXISTS `main_products` JSON NULL AFTER `export_markets`;

-- Add indexes for better performance
CREATE INDEX IF NOT EXISTS `products_featured_published_index` ON `products` (`is_featured`, `published_at`);
CREATE INDEX IF NOT EXISTS `products_status_active_index` ON `products` (`status`, `is_active`);
CREATE INDEX IF NOT EXISTS `companies_featured_verified_index` ON `companies` (`is_featured`, `is_verified`);
CREATE INDEX IF NOT EXISTS `companies_status_verified_index` ON `companies` (`status`, `is_verified`);

-- Create categories table if it doesn't exist
CREATE TABLE IF NOT EXISTS `categories` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` text,
  `image` varchar(255) DEFAULT NULL,
  `sort_order` int DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `categories_slug_unique` (`slug`),
  KEY `categories_is_active_index` (`is_active`),
  KEY `categories_sort_order_index` (`sort_order`)
);

-- Insert sample categories if they don't exist
INSERT IGNORE INTO `categories` (`id`, `name`, `slug`, `description`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 'Electronics', 'electronics', 'Electronic products and devices', 1, NOW(), NOW()),
(2, 'Clothing', 'clothing', 'Fashion and apparel', 1, NOW(), NOW()),
(3, 'Home & Garden', 'home-garden', 'Home improvement and garden supplies', 1, NOW(), NOW()),
(4, 'Automotive', 'automotive', 'Car parts and accessories', 1, NOW(), NOW()),
(5, 'Sports & Outdoors', 'sports-outdoors', 'Sports equipment and outdoor gear', 1, NOW(), NOW());

-- Insert sample company if it doesn't exist
INSERT IGNORE INTO `companies` (`id`, `name`, `slug`, `legal_name`, `email`, `phone`, `business_type`, `industry`, `country`, `city`, `status`, `is_verified`, `is_featured`, `is_premium`, `verified_at`, `created_at`, `updated_at`) VALUES
(1, 'Sample Company', 'sample-company', 'Sample Company Ltd.', 'company@aladin.com', '+1234567890', 'manufacturer', 'electronics', 'United States', 'New York', 'active', 1, 0, 0, NOW(), NOW(), NOW());

-- Show completion message
SELECT 'Database fix completed successfully!' as message;
