-- =============================================
-- V4 Aviator Sonic - Database Migration Script
-- Signal Activation & Enhanced Features
-- Run this AFTER database_structure.sql
-- Date: 2026-01-27
-- =============================================

-- =============================================
-- NEW TABLE: Activation Codes
-- =============================================
CREATE TABLE IF NOT EXISTS `activation_codes` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(20) NOT NULL UNIQUE COMMENT 'Unique activation code like PREDICTOR-XXXX',
  `device_id` VARCHAR(255) NOT NULL,
  `user_id` INT(11) DEFAULT NULL,
  `status` ENUM('pending', 'used', 'expired') DEFAULT 'pending',
  `generated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `used_at` TIMESTAMP NULL,
  `expires_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_code` (`code`),
  KEY `idx_device_id` (`device_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- NEW TABLE: Signal Requests
-- =============================================
CREATE TABLE IF NOT EXISTS `signal_requests` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `device_id` VARCHAR(255) NOT NULL,
  `activation_code` VARCHAR(20) NOT NULL,
  `signal_duration_minutes` INT(11) NOT NULL COMMENT 'Requested signal duration in minutes',
  `time_deducted_seconds` INT(11) NOT NULL COMMENT 'Actual time deducted from user account',
  `status` ENUM('pending', 'active', 'completed', 'cancelled') DEFAULT 'pending',
  `notification_enabled` TINYINT(1) DEFAULT 0 COMMENT 'Enable notification alerts for signals',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `started_at` TIMESTAMP NULL,
  `completed_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  KEY `idx_device_id` (`device_id`),
  KEY `idx_activation_code` (`activation_code`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- NEW TABLE: Translation Cache
-- =============================================
CREATE TABLE IF NOT EXISTS `translation_cache` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `source_text` TEXT NOT NULL,
  `source_text_hash` VARCHAR(64) NOT NULL COMMENT 'MD5 hash for quick lookup',
  `source_lang` VARCHAR(10) NOT NULL,
  `target_lang` VARCHAR(10) NOT NULL,
  `translated_text` TEXT NOT NULL,
  `translation_service` ENUM('google', 'libretranslate', 'manual') DEFAULT 'libretranslate',
  `quality_score` DECIMAL(3,2) DEFAULT NULL COMMENT 'Translation quality 0.00-1.00',
  `used_count` INT(11) DEFAULT 1 COMMENT 'How many times this translation was reused',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `last_used_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_translation` (`source_text_hash`, `source_lang`, `target_lang`),
  KEY `idx_langs` (`source_lang`, `target_lang`),
  KEY `idx_hash` (`source_text_hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- NEW TABLE: Permission Logs
-- =============================================
CREATE TABLE IF NOT EXISTS `permission_logs` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `device_id` VARCHAR(255) NOT NULL,
  `permission_type` ENUM('notification', 'notification_listener', 'battery_optimization', 'boot_receiver', 'overlay') NOT NULL,
  `status` ENUM('granted', 'denied', 'revoked') NOT NULL,
  `requested_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `granted_at` TIMESTAMP NULL,
  `app_version` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_device_id` (`device_id`),
  KEY `idx_permission_type` (`permission_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- UPDATE: Admin Users - Add Role System
-- =============================================
ALTER TABLE `admin_users` 
  ADD COLUMN IF NOT EXISTS `role` ENUM('super_admin', 'admin', 'support', 'viewer') DEFAULT 'support' AFTER `username`,
  ADD COLUMN IF NOT EXISTS `can_manual_reply` TINYINT(1) DEFAULT 1 AFTER `role`,
  ADD COLUMN IF NOT EXISTS `permissions` TEXT COMMENT 'JSON array of permissions' AFTER `can_manual_reply`,
  ADD COLUMN IF NOT EXISTS `last_login` TIMESTAMP NULL AFTER `permissions`,
  ADD COLUMN IF NOT EXISTS `login_count` INT(11) DEFAULT 0 AFTER `last_login`;

-- =============================================
-- UPDATE: Auto Reply Templates - Enhanced AI Training
-- =============================================
ALTER TABLE `auto_reply_templates`
  ADD COLUMN IF NOT EXISTS `category` ENUM('activation', 'signal', 'support', 'general', 'permission', 'error') DEFAULT 'general' AFTER `template_name`,
  ADD COLUMN IF NOT EXISTS `requires_permission_check` TINYINT(1) DEFAULT 0 COMMENT 'Check if user has all permissions before sending' AFTER `category`,
  ADD COLUMN IF NOT EXISTS `context_aware` TINYINT(1) DEFAULT 0 COMMENT 'Use conversation context for better matching' AFTER `requires_permission_check`,
  ADD COLUMN IF NOT EXISTS `ai_training_data` TEXT COMMENT 'Training data for future AI improvements' AFTER `context_aware`,
  ADD COLUMN IF NOT EXISTS `next_step_action` VARCHAR(100) DEFAULT NULL COMMENT 'Action to trigger after reply: show_permission_dialog, show_duration_selector, etc.' AFTER `ai_training_data`;

-- =============================================
-- UPDATE: App Config - Add Translation Settings
-- =============================================
ALTER TABLE `appconfig`
  ADD COLUMN IF NOT EXISTS `google_translate_api_key` VARCHAR(255) DEFAULT NULL COMMENT 'Google Cloud Translation API key',
  ADD COLUMN IF NOT EXISTS `libretranslate_url` VARCHAR(255) DEFAULT 'https://libretranslate.com/translate' COMMENT 'LibreTranslate API endpoint',
  ADD COLUMN IF NOT EXISTS `translation_cache_enabled` TINYINT(1) DEFAULT 1 COMMENT 'Use cached translations',
  ADD COLUMN IF NOT EXISTS `translation_cache_ttl_days` INT(11) DEFAULT 30 COMMENT 'Cache expiry in days',
  ADD COLUMN IF NOT EXISTS `force_permissions` TINYINT(1) DEFAULT 1 COMMENT 'Force users to grant all permissions',
  ADD COLUMN IF NOT EXISTS `notification_sync_interval_minutes` INT(11) DEFAULT 5 COMMENT 'Notification sync interval';

-- Update existing config
UPDATE `appconfig` SET 
  `force_permissions` = 1,
  `notification_sync_interval_minutes` = 5,
  `translation_cache_enabled` = 1,
  `translation_cache_ttl_days` = 30
WHERE `id` = 1;

-- =============================================
-- SAMPLE: Activation Auto-Reply Templates
-- =============================================

-- Template 1: Activation Code - No Permissions
INSERT INTO `auto_reply_templates` 
(template_name, category, keywords, reply_message, reply_message_bn, reply_message_hi, requires_permission_check, next_step_action, priority, enabled)
VALUES 
('Activation - No Permission', 'activation', 'PREDICTOR-,ACTIVATOR-', 
'⚠️ Please accept all permissions before getting signal.\n\nRequired Permissions:\n✓ Notification Access\n✓ Background Running\n✓ Notification Reading\n\nTap "Allow Permissions" button below to continue.',
'⚠️ সিগন্যাল পাওয়ার আগে সকল permission allow করুন।\n\nপ্রয়োজনীয় Permissions:\n✓ Notification Access\n✓ Background Running\n✓ Notification Reading\n\n"Allow Permissions" বাটনে ক্লিক করুন।',
'⚠️ सिग्नल प्राप्त करने से पहले कृपया सभी permissions स्वीकार करें।\n\nआवश्यक Permissions:\n✓ Notification Access\n✓ Background Running\n✓ Notification Reading\n\n"Allow Permissions" बटन पर क्लिक करें।',
1, 'show_permission_dialog', 100, 1);

-- Template 2: Activation Code - Valid (With Permissions)
INSERT INTO `auto_reply_templates`
(template_name, category, keywords, reply_message, reply_message_bn, reply_message_hi, next_step_action, priority, enabled)
VALUES 
('Activation - Valid Code', 'activation', 'PREDICTOR-,ACTIVATOR-',
'✅ Success! You want signal for how many time?\n\nPlease select duration:\n⏱️ 15 Minutes\n⏱️ 30 Minutes\n⏱️ 1 Hour\n⏱️ 2 Hours\n⏱️ 3 Hours\n\nNote: Selected time will be deducted from your remaining time.',
'✅ সফল! আপনি কত সময়ের জন্য signal চান?\n\nদয়া করে সময় নির্বাচন করুন:\n⏱️ ১৫ মিনিট\n⏱️ ৩০ মিনিট\n⏱️ ১ ঘন্টা\n⏱️ ২ ঘন্টা\n⏱️ ৩ ঘন্টা\n\nমনে রাখবেন: নির্বাচিত সময় আপনার অবশিষ্ট সময় থেকে কাটা হবে।',
'✅ सफल! आप कितने समय के लिए signal चाहते हैं?\n\nकृपया अवधि चुनें:\n⏱️ 15 मिनट\n⏱️ 30 मिनट\n⏱️ 1 घंटा\n⏱️ 2 घंटे\n⏱️ 3 घंटे\n\nनोट: चयनित समय आपके शेष समय से काट लिया जाएगा।',
'show_duration_selector', 99, 1);

-- Template 3: Admin Busy Message
INSERT INTO `auto_reply_templates`
(template_name, category, keywords, reply_message, reply_message_bn, reply_message_hi, priority, enabled)
VALUES
('Admin Busy - Auto Reply', 'support', '',
'Dear user, your message is very important to us. 💬\n\nCurrently all admins are busy. Please wait a few moments, one of our admins will reply you soon.\n\nThank you for your patience! 🙏',
'প্রিয় ব্যবহারকারী, আপনার বার্তা আমাদের জন্য অত্যন্ত গুরুত্বপূর্ণ। 💬\n\nবর্তমানে সকল admin ব্যস্ত আছেন। অনুগ্রহ করে কিছুক্ষণ অপেক্ষা করুন, আমাদের একজন admin শীঘ্রই আপনাকে উত্তর দেবে।\n\nআপনার ধৈর্যের জন্য ধন্যবাদ! 🙏',
'प्रिय उपयोगकर्ता, आपका संदेश हमारे लिए बहुत महत्वपूर्ण है। 💬\n\nवर्तमान में सभी admins व्यस्त हैं। कृपया कुछ क्षण प्रतीक्षा करें, हमारे एक admin जल्द ही आपको जवाब देंगे।\n\nआपके धैर्य के लिए धन्यवाद! 🙏',
5, 1);

-- Template 4: Insufficient Time
INSERT INTO `auto_reply_templates`
(template_name, category, keywords, reply_message, reply_message_bn, reply_message_hi, priority, enabled)
VALUES
('Error - Insufficient Time', 'error', '',
'❌ Sorry! You don\'t have enough time remaining.\n\nYour current time: {remaining_time}\nRequired time: {requested_time}\n\nPlease purchase a duration package to continue.\n\n[View Packages]',
'❌ দুঃখিত! আপনার পর্যাপ্ত সময় নেই।\n\nআপনার বর্তমান সময়: {remaining_time}\nপ্রয়োজনীয় সময়: {requested_time}\n\nচালিয়ে যেতে একটি duration package কিনুন।\n\n[Packages দেখুন]',
'❌ क्षमा करें! आपके पास पर्याप्त समय नहीं है।\n\nआपका वर्तमान समय: {remaining_time}\nआवश्यक समय: {requested_time}\n\nजारी रखने के लिए एक duration package खरीदें।\n\n[Packages देखें]',
98, 1);

-- =============================================
-- Migration Complete!
-- Total new tables: 4
-- Total table updates: 3
-- =============================================
