-- First, modify the title and content columns to support Georgian language
ALTER TABLE `contents` 
MODIFY `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
MODIFY `content` text COLLATE utf8mb4_unicode_ci NOT NULL;

-- Then add the new columns
ALTER TABLE `contents`
ADD COLUMN `category_id` bigint(20) UNSIGNED NULL AFTER `subcategory`,
ADD COLUMN `subcategory_id` bigint(20) UNSIGNED NULL AFTER `category_id`,
ADD COLUMN `language` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT 'en' AFTER `status`,
ADD CONSTRAINT `contents_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `rule_categories` (`id`) ON DELETE SET NULL,
ADD CONSTRAINT `contents_subcategory_id_foreign` FOREIGN KEY (`subcategory_id`) REFERENCES `rule_subcategories` (`id`) ON DELETE SET NULL,
ADD INDEX `contents_category_id_subcategory_id_index` (`category_id`, `subcategory_id`),
ADD INDEX `contents_language_index` (`language`);
