-- First, check if tables exist and create them if they don't

-- Create contents table if not exists
CREATE TABLE IF NOT EXISTS `contents` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `content` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `category` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `subcategory` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` enum('published','draft') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'published',
  `language` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT 'en',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `contents_category_subcategory_index` (`category`,`subcategory`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create rules table if not exists
CREATE TABLE IF NOT EXISTS `rules` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `condition` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `priority` int(11) NOT NULL DEFAULT 0,
  `content_id` bigint(20) UNSIGNED DEFAULT NULL,
  `response_template` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `language` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT 'en',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `tags` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `rules_content_id_foreign` (`content_id`),
  KEY `rules_is_active_priority_index` (`is_active`, `priority`),
  KEY `rules_language_is_active_index` (`language`, `is_active`),
  CONSTRAINT `rules_content_id_foreign` FOREIGN KEY (`content_id`) REFERENCES `contents` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create rule_categories table if not exists
CREATE TABLE IF NOT EXISTS `rule_categories` (
  `id` bigint(20) 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 DEFAULT NULL,
  `language` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT 'en',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `rule_categories_slug_unique` (`slug`),
  KEY `rule_categories_language_is_active_index` (`language`,`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create rule_subcategories table if not exists
CREATE TABLE IF NOT EXISTS `rule_subcategories` (
  `id` bigint(20) 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 DEFAULT NULL,
  `category_id` bigint(20) UNSIGNED NOT NULL,
  `language` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT 'en',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `rule_subcategories_slug_unique` (`slug`),
  KEY `rule_subcategories_category_id_language_is_active_index` (`category_id`,`language`,`is_active`),
  CONSTRAINT `rule_subcategories_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `rule_categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Now, add foreign key columns to existing tables if they don't exist
-- Check if category_id column exists in contents table, add if it doesn't
SET @exists = (SELECT COUNT(*)
               FROM information_schema.COLUMNS 
               WHERE TABLE_NAME = 'contents' AND COLUMN_NAME = 'category_id');
               
SET @sqlstmt = IF(@exists = 0, 'ALTER TABLE `contents`
                 ADD COLUMN `category_id` bigint(20) UNSIGNED DEFAULT NULL AFTER `subcategory`,
                 ADD CONSTRAINT `contents_category_id_foreign` FOREIGN KEY (`category_id`) 
                 REFERENCES `rule_categories` (`id`) ON DELETE SET NULL;', 'SELECT "column exists" status');

PREPARE stmt FROM @sqlstmt;
EXECUTE stmt;

-- Check if subcategory_id column exists in contents table, add if it doesn't
SET @exists = (SELECT COUNT(*)
               FROM information_schema.COLUMNS 
               WHERE TABLE_NAME = 'contents' AND COLUMN_NAME = 'subcategory_id');
               
SET @sqlstmt = IF(@exists = 0, 'ALTER TABLE `contents`
                 ADD COLUMN `subcategory_id` bigint(20) UNSIGNED DEFAULT NULL AFTER `category_id`,
                 ADD CONSTRAINT `contents_subcategory_id_foreign` FOREIGN KEY (`subcategory_id`) 
                 REFERENCES `rule_subcategories` (`id`) ON DELETE SET NULL;', 'SELECT "column exists" status');

PREPARE stmt FROM @sqlstmt;
EXECUTE stmt;

-- Check if category_id column exists in rules table, add if it doesn't
SET @exists = (SELECT COUNT(*)
               FROM information_schema.COLUMNS 
               WHERE TABLE_NAME = 'rules' AND COLUMN_NAME = 'category_id');
               
SET @sqlstmt = IF(@exists = 0, 'ALTER TABLE `rules`
                 ADD COLUMN `category_id` bigint(20) UNSIGNED DEFAULT NULL AFTER `content_id`,
                 ADD CONSTRAINT `rules_category_id_foreign` FOREIGN KEY (`category_id`) 
                 REFERENCES `rule_categories` (`id`) ON DELETE SET NULL;', 'SELECT "column exists" status');

PREPARE stmt FROM @sqlstmt;
EXECUTE stmt;

-- Check if subcategory_id column exists in rules table, add if it doesn't
SET @exists = (SELECT COUNT(*)
               FROM information_schema.COLUMNS 
               WHERE TABLE_NAME = 'rules' AND COLUMN_NAME = 'subcategory_id');
               
SET @sqlstmt = IF(@exists = 0, 'ALTER TABLE `rules`
                 ADD COLUMN `subcategory_id` bigint(20) UNSIGNED DEFAULT NULL AFTER `category_id`,
                 ADD CONSTRAINT `rules_subcategory_id_foreign` FOREIGN KEY (`subcategory_id`) 
                 REFERENCES `rule_subcategories` (`id`) ON DELETE SET NULL;', 'SELECT "column exists" status');

PREPARE stmt FROM @sqlstmt;
EXECUTE stmt;
