-- Create the tables directly for use in phpMyAdmin

-- Create contents table
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',
  `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;

-- Insert sample content
INSERT INTO `contents` (`title`, `content`, `category`, `subcategory`, `status`, `created_at`, `updated_at`)
VALUES 
('Platform Rules', 'These are the rules for using our platform.', 'General', 'Usage', 'published', NOW(), NOW()),
('პლატფორმის წესები', 'ეს არის ჩვენი პლატფორმის გამოყენების წესები.', 'საერთო წესები', 'AIRDROP წესები', 'published', NOW(), NOW());

-- Create rules table
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,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `rules_content_id_foreign` (`content_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add foreign key constraint 
ALTER TABLE `rules` 
ADD CONSTRAINT `rules_content_id_foreign` 
FOREIGN KEY (`content_id`) REFERENCES `contents` (`id`) ON DELETE SET NULL;

-- Insert sample rule
INSERT INTO `rules` (`name`, `condition`, `priority`, `content_id`, `response_template`, `created_at`, `updated_at`)
VALUES 
('General Help', 'help, assistance, support', 10, 1, 'Here is information about {{title}}:\n\n{{content}}', NOW(), NOW()),
('ქართული დახმარება', 'დახმარება, მხარდაჭერა', 15, 2, 'აი ინფორმაცია {{title}}:\n\n{{content}}', NOW(), NOW());
