-- Example insert statements for the chatbot system

-- Insert sample content
INSERT INTO `contents` (`title`, `content`, `category`, `subcategory`, `status`, `created_at`, `updated_at`) 
VALUES 
('General Help', 'This is the general help content for the platform. Users should follow all rules.', 'Help', 'General', 'published', NOW(), NOW()),
('FAQs', 'Frequently Asked Questions about our platform and services.', 'Help', 'FAQ', 'published', NOW(), NOW()),
('Platform Rules', 'These are the rules you must follow when using our platform. Violations may result in account suspension.', 'Rules', 'General', 'published', NOW(), NOW()),
('Privacy Policy', 'Our privacy policy explains how we collect and use your data.', 'Legal', 'Privacy', 'published', NOW(), NOW()),
('Georgian Rules', 'ეს არის წესები ქართველი მომხმარებლებისთვის. გთხოვთ დაიცვათ ყველა წესი.', 'წესები', 'ძირითადი', 'published', NOW(), NOW()),
('Airdrop Rules', 'AIRDROP-ის მონაწილეები უნდა დაიცვან შემდეგი წესები...', 'წესები', 'AIRDROP', 'published', NOW(), NOW());

-- Insert sample rules
INSERT INTO `rules` (`name`, `condition`, `priority`, `content_id`, `response_template`, `created_at`, `updated_at`)
VALUES
('Help Rule', 'help, assistance, support, how to', 10, 1, 'Here is some general help information:\n\n{{content}}', NOW(), NOW()),
('FAQ Rule', 'faq, question, frequently asked', 8, 2, 'Here are our FAQs:\n\n{{content}}', NOW(), NOW()),
('Rules Info', 'rules, terms, guidelines, regulations', 12, 3, 'Please review our platform rules:\n\n{{content}}', NOW(), NOW()),
('Privacy Info', 'privacy, data, personal information, gdpr', 5, 4, 'Our Privacy Policy:\n\n{{content}}', NOW(), NOW()),
('Georgian General Help', 'დახმარება, როგორ, გთხოვთ', 15, 5, 'აი ზოგადი დახმარება ქართულ ენაზე:\n\n{{content}}', NOW(), NOW()),
('Airdrop Help', 'airdrop, ეირდროპი, ჯილდო', 20, 6, 'AIRDROP წესები:\n\n{{content}}', NOW(), NOW());

-- Create rule categories if the table exists
INSERT INTO `rule_categories` (`name`, `slug`, `description`, `language`, `is_active`, `created_at`, `updated_at`)
SELECT 'General Help', 'general-help', 'General help and support topics', 'en', 1, NOW(), NOW() 
WHERE EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'rule_categories');

INSERT INTO `rule_categories` (`name`, `slug`, `description`, `language`, `is_active`, `created_at`, `updated_at`)
SELECT 'Platform Rules', 'platform-rules', 'Rules for platform usage', 'en', 1, NOW(), NOW() 
WHERE EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'rule_categories');

INSERT INTO `rule_categories` (`name`, `slug`, `description`, `language`, `is_active`, `created_at`, `updated_at`)
SELECT 'Legal Information', 'legal-info', 'Legal documents and information', 'en', 1, NOW(), NOW() 
WHERE EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'rule_categories');

INSERT INTO `rule_categories` (`name`, `slug`, `description`, `language`, `is_active`, `created_at`, `updated_at`)
SELECT 'ქართული დახმარება', 'georgian-help', 'დახმარების განყოფილება ქართულ ენაზე', 'ka', 1, NOW(), NOW() 
WHERE EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'rule_categories');

-- Create rule subcategories if the table exists
INSERT INTO `rule_subcategories` (`name`, `slug`, `description`, `category_id`, `language`, `is_active`, `created_at`, `updated_at`)
SELECT 'Getting Started', 'getting-started', 'Help for new users', 1, 'en', 1, NOW(), NOW()
WHERE EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'rule_subcategories');

INSERT INTO `rule_subcategories` (`name`, `slug`, `description`, `category_id`, `language`, `is_active`, `created_at`, `updated_at`)
SELECT 'Account Issues', 'account-issues', 'Help with account problems', 1, 'en', 1, NOW(), NOW()
WHERE EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'rule_subcategories');

INSERT INTO `rule_subcategories` (`name`, `slug`, `description`, `category_id`, `language`, `is_active`, `created_at`, `updated_at`)
SELECT 'General Rules', 'general-rules', 'Basic platform rules', 2, 'en', 1, NOW(), NOW()
WHERE EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'rule_subcategories');

INSERT INTO `rule_subcategories` (`name`, `slug`, `description`, `category_id`, `language`, `is_active`, `created_at`, `updated_at`)
SELECT 'Privacy Policy', 'privacy-policy', 'Privacy rules and regulations', 3, 'en', 1, NOW(), NOW()
WHERE EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'rule_subcategories');

INSERT INTO `rule_subcategories` (`name`, `slug`, `description`, `category_id`, `language`, `is_active`, `created_at`, `updated_at`)
SELECT 'პლატფორმის გამოყენება', 'platform-usage-ka', 'როგორ გამოიყენოთ პლატფორმა', 4, 'ka', 1, NOW(), NOW()
WHERE EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'rule_subcategories');

INSERT INTO `rule_subcategories` (`name`, `slug`, `description`, `category_id`, `language`, `is_active`, `created_at`, `updated_at`)
SELECT 'AIRDROP წესები', 'airdrop-rules-ka', 'წესები AIRDROP-ის მონაწილეებისთვის', 4, 'ka', 1, NOW(), NOW()
WHERE EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'rule_subcategories');

-- Update rules with category and subcategory IDs if the columns exist
-- This is a more complex operation that would ideally be done with PHP after confirming values
-- For the sake of example, we can set some basic values:

-- Update rules with category_id if the column exists
UPDATE `rules` SET `category_id` = 1 
WHERE `id` IN (1, 2) 
AND EXISTS (SELECT 1 FROM information_schema.columns 
            WHERE table_schema = DATABASE() 
            AND table_name = 'rules' 
            AND column_name = 'category_id');

UPDATE `rules` SET `category_id` = 2 
WHERE `id` = 3
AND EXISTS (SELECT 1 FROM information_schema.columns 
            WHERE table_schema = DATABASE() 
            AND table_name = 'rules' 
            AND column_name = 'category_id');

UPDATE `rules` SET `category_id` = 3 
WHERE `id` = 4
AND EXISTS (SELECT 1 FROM information_schema.columns 
            WHERE table_schema = DATABASE() 
            AND table_name = 'rules' 
            AND column_name = 'category_id');

UPDATE `rules` SET `category_id` = 4 
WHERE `id` IN (5, 6)
AND EXISTS (SELECT 1 FROM information_schema.columns 
            WHERE table_schema = DATABASE() 
            AND table_name = 'rules' 
            AND column_name = 'category_id');

-- Update rules with subcategory_id if the column exists
UPDATE `rules` SET `subcategory_id` = 1 
WHERE `id` = 1
AND EXISTS (SELECT 1 FROM information_schema.columns 
            WHERE table_schema = DATABASE() 
            AND table_name = 'rules' 
            AND column_name = 'subcategory_id');

UPDATE `rules` SET `subcategory_id` = 2 
WHERE `id` = 2
AND EXISTS (SELECT 1 FROM information_schema.columns 
            WHERE table_schema = DATABASE() 
            AND table_name = 'rules' 
            AND column_name = 'subcategory_id');

UPDATE `rules` SET `subcategory_id` = 3 
WHERE `id` = 3
AND EXISTS (SELECT 1 FROM information_schema.columns 
            WHERE table_schema = DATABASE() 
            AND table_name = 'rules' 
            AND column_name = 'subcategory_id');

UPDATE `rules` SET `subcategory_id` = 4 
WHERE `id` = 4
AND EXISTS (SELECT 1 FROM information_schema.columns 
            WHERE table_schema = DATABASE() 
            AND table_name = 'rules' 
            AND column_name = 'subcategory_id');

UPDATE `rules` SET `subcategory_id` = 5 
WHERE `id` = 5
AND EXISTS (SELECT 1 FROM information_schema.columns 
            WHERE table_schema = DATABASE() 
            AND table_name = 'rules' 
            AND column_name = 'subcategory_id');

UPDATE `rules` SET `subcategory_id` = 6 
WHERE `id` = 6
AND EXISTS (SELECT 1 FROM information_schema.columns 
            WHERE table_schema = DATABASE() 
            AND table_name = 'rules' 
            AND column_name = 'subcategory_id');
