-- V4 Aviator Sonic Database Structure - COMPLETE VERSION
-- Database: kfgwkeab_v4aviatorsonic
-- User: kfgwkeab_v4aviatorsonic
-- Password: kfgwkeab_v4aviatorsonicDB

-- =============================================
-- Table: appconfig
-- Stores application configuration settings
-- =============================================
CREATE TABLE IF NOT EXISTS `appconfig` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `app_name` VARCHAR(100) NOT NULL DEFAULT 'V4 Aviator Sonic',
  `app_version` VARCHAR(20) NOT NULL DEFAULT '1.0.0',
  `latest_version` VARCHAR(20) NOT NULL DEFAULT '1.0.0',
  `latest_version_url` TEXT,
  `unity_ads_game_id` VARCHAR(50) NOT NULL DEFAULT '5722033',
  `unity_ads_placement_id` VARCHAR(100) DEFAULT 'Rewarded_Android',
  
  -- Maintenance Mode Settings
  `maintenance_mode` TINYINT(1) NOT NULL DEFAULT 0,
  `maintenance_dialog_title` VARCHAR(100) DEFAULT 'Under Maintenance',
  `maintenance_dialog_body` TEXT DEFAULT 'We are currently performing maintenance. Please check back later.',
  `maintenance_dialog_button_text` VARCHAR(50) DEFAULT 'OK',
  
  -- App Update Dialog Settings
  `update_dialog_title` VARCHAR(100) DEFAULT 'Update Available',
  `update_dialog_body` TEXT DEFAULT 'A new version is available. Please update to continue.',
  `update_dialog_button_text` VARCHAR(50) DEFAULT 'Update Now',
  
  -- Free Access Settings
  `free_access_hours` INT(11) NOT NULL DEFAULT 24,
  `free_access_minutes` INT(11) NOT NULL DEFAULT 0,
  
  -- Registration Settings
  `enable_fast_ads` TINYINT(1) NOT NULL DEFAULT 0,
  `default_user_level` INT(11) NOT NULL DEFAULT 1,
  `default_user_credit` INT(11) NOT NULL DEFAULT 0,
  
  -- Invite Settings
  `invite_redirect_url` TEXT,
  
  -- Admin Settings
  `admin_user_id` INT(11) DEFAULT 1,
  `admin_username` VARCHAR(50) DEFAULT 'Admin',
  
  -- Additional Settings
  `min_credits_required` INT(11) DEFAULT 0,
  `support_email` VARCHAR(100) DEFAULT 'support@aviatorsonic.live',
  `support_whatsapp` VARCHAR(20),
  `terms_url` TEXT,
  `privacy_url` TEXT,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default configuration
INSERT INTO `appconfig` (`id`, `app_name`, `app_version`, `latest_version`, `unity_ads_game_id`, `free_access_hours`) 
VALUES (1, 'V4 Aviator Sonic', '1.0.0', '1.0.0', '5722033', 24)
ON DUPLICATE KEY UPDATE `updated_at` = CURRENT_TIMESTAMP;

-- =============================================
-- Table: user
-- Stores user authentication and account data
-- =============================================
CREATE TABLE IF NOT EXISTS `user` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `device_id` VARCHAR(255) NOT NULL UNIQUE,
  `username` VARCHAR(50) UNIQUE,
  `registered_datetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_login_datetime` TIMESTAMP NULL DEFAULT NULL,
  
  -- Time-based system (in seconds)
  `remaining_time_seconds` INT(11) NOT NULL DEFAULT 0,
  `last_time_sync` TIMESTAMP NULL DEFAULT NULL,
  
  -- User Status: active, expired, banned, suspended
  `status` ENUM('active', 'expired', 'banned', 'suspended') NOT NULL DEFAULT 'active',
  
  -- User Progress
  `credit` INT(11) NOT NULL DEFAULT 0,
  `level` INT(11) NOT NULL DEFAULT 1,
  `total_games_played` INT(11) DEFAULT 0,
  `total_wins` INT(11) DEFAULT 0,
  
  -- Profile Information
  `profile_pic_url` TEXT,
  `full_name` VARCHAR(100),
  `phone` VARCHAR(20),
  `email` VARCHAR(100),
  `bkash_number` VARCHAR(20),
  `nagad_number` VARCHAR(20),
  
  -- Invite System
  `invite_code` VARCHAR(20) UNIQUE,
  `invited_by` INT(11),
  `total_referrals` INT(11) DEFAULT 0,
  
  -- Additional Fields
  `referral_code` VARCHAR(50),
  `is_premium` TINYINT(1) NOT NULL DEFAULT 0,
  `notes` TEXT,
  
  -- Timestamps
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  KEY `idx_device_id` (`device_id`),
  KEY `idx_username` (`username`),
  KEY `idx_phone` (`phone`),
  KEY `idx_email` (`email`),
  KEY `idx_invite_code` (`invite_code`),
  KEY `idx_status` (`status`),
  KEY `idx_last_login` (`last_login_datetime`),
  KEY `idx_is_premium` (`is_premium`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Table: userdata
-- Stores user-specific data like notifications
-- =============================================
CREATE TABLE IF NOT EXISTS `userdata` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  
  -- Notification Settings
  `notifications_enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `notification_title` VARCHAR(255),
  `notification_body` TEXT,
  `notification_sent_at` TIMESTAMP NULL DEFAULT NULL,
  
  -- User Preferences
  `theme` VARCHAR(20) DEFAULT 'dark',
  `language` VARCHAR(10) DEFAULT 'en',
  `sound_enabled` TINYINT(1) DEFAULT 1,
  `vibration_enabled` TINYINT(1) DEFAULT 1,
  
  -- Custom Data (JSON format for flexibility)
  `custom_data` TEXT,
  
  -- Timestamps
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  CONSTRAINT `fk_userdata_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Table: reward_config
-- Manage all reward settings
-- =============================================
CREATE TABLE IF NOT EXISTS `reward_config` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `reward_type` VARCHAR(50) NOT NULL UNIQUE,
  `enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `reward_time_seconds` INT(11) DEFAULT 0,
  `reward_credits` INT(11) DEFAULT 0,
  `cooldown_seconds` INT(11) DEFAULT 0,
  `min_amount` DECIMAL(10,2) DEFAULT 0,
  `description` TEXT,
  `button_text` VARCHAR(100),
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_reward_type` (`reward_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default reward configs
INSERT INTO `reward_config` (`reward_type`, `enabled`, `reward_time_seconds`, `reward_credits`, `cooldown_seconds`, `min_amount`, `description`, `button_text`) VALUES
('watch_ads', 1, 1800, 0, 3600, 0, 'Watch advertisement to get 30 minutes free access', 'Watch Ad'),
('bkash_connect', 1, 86400, 500, 0, 50, 'Connect bKash account and get 24 hours + 500 credits (Min: ৳50)', 'Connect bKash'),
('nagad_connect', 1, 86400, 500, 0, 50, 'Connect Nagad account and get 24 hours + 500 credits (Min: ৳50)', 'Connect Nagad'),
('invite_friend', 1, 3600, 100, 0, 0, 'Invite a friend and get 1 hour + 100 credits when they register', 'Invite Friends'),
('share_tracking', 1, 600, 50, 86400, 0, 'Share app and get 10 minutes + 50 credits (Auto-verified in few hours)', 'Share App'),
('facebook_post', 1, 7200, 200, 0, 0, 'Post our content on Facebook and get 2 hours + 200 credits', 'Post on Facebook')
ON DUPLICATE KEY UPDATE `updated_at` = CURRENT_TIMESTAMP;

-- =============================================
-- Table: ad_watch_history
-- Track advertisement watches
-- =============================================
CREATE TABLE IF NOT EXISTS `ad_watch_history` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `ad_type` VARCHAR(50),
  `watched_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `reward_time_seconds` INT(11),
  `reward_credits` INT(11),
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_watched_at` (`watched_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Table: referrals
-- Track invite/referral system
-- =============================================
CREATE TABLE IF NOT EXISTS `referrals` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `referrer_user_id` INT(11) NOT NULL,
  `referred_device_id` VARCHAR(255),
  `referred_user_id` INT(11) DEFAULT NULL,
  `referral_method` ENUM('code', 'device_id', 'phone', 'email', 'username') DEFAULT 'code',
  `ip_address` VARCHAR(45),
  `user_agent` TEXT,
  `click_datetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `registration_datetime` TIMESTAMP NULL DEFAULT NULL,
  `status` ENUM('pending', 'completed', 'spam', 'duplicate', 'self_referral') DEFAULT 'pending',
  `reward_given` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_referrer` (`referrer_user_id`),
  KEY `idx_referred_user` (`referred_user_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Table: spam_blocklist
-- Block spam devices/IPs
-- =============================================
CREATE TABLE IF NOT EXISTS `spam_blocklist` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `device_id` VARCHAR(255),
  `ip_address` VARCHAR(45),
  `reason` VARCHAR(255),
  `blocked_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_device_id` (`device_id`),
  KEY `idx_ip_address` (`ip_address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Table: share_tracking
-- Track social shares with auto-verification
-- =============================================
CREATE TABLE IF NOT EXISTS `share_tracking` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `platform` VARCHAR(50),
  `shared_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `verified_at` TIMESTAMP NULL DEFAULT NULL,
  `status` ENUM('pending', 'verified', 'rejected') DEFAULT 'pending',
  `reward_given` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Table: facebook_posts
-- Track Facebook post submissions
-- =============================================
CREATE TABLE IF NOT EXISTS `facebook_posts` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `post_url` TEXT NOT NULL,
  `required_url` TEXT,
  `submitted_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `reviewed_at` TIMESTAMP NULL DEFAULT NULL,
  `status` ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
  `rejection_reason` VARCHAR(255),
  `reward_given` TINYINT(1) NOT NULL DEFAULT 0,
  `auto_verified` TINYINT(1) DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Table: pricing_plans
-- Subscription/pricing plans
-- =============================================
CREATE TABLE IF NOT EXISTS `pricing_plans` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `plan_name` VARCHAR(100) NOT NULL,
  `duration_days` INT(11) NOT NULL,
  `price` DECIMAL(10,2) NOT NULL,
  `currency` VARCHAR(10) DEFAULT 'BDT',
  `credits_bonus` INT(11) DEFAULT 0,
  `features` TEXT,
  `is_popular` TINYINT(1) NOT NULL DEFAULT 0,
  `enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `display_order` INT(11) DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_enabled` (`enabled`),
  KEY `idx_display_order` (`display_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default pricing plans
INSERT INTO `pricing_plans` (`plan_name`, `duration_days`, `price`, `credits_bonus`, `features`, `is_popular`, `display_order`) VALUES
('Weekly Plan', 7, 99.00, 100, '✓ 7 days unlimited access\n✓ 100 bonus credits\n✓ Ad-free experience', 0, 1),
('Monthly Plan', 30, 299.00, 500, '✓ 30 days unlimited access\n✓ 500 bonus credits\n✓ Ad-free experience\n✓ Priority support', 1, 2),
('3 Month Plan', 90, 799.00, 2000, '✓ 90 days unlimited access\n✓ 2000 bonus credits\n✓ Ad-free experience\n✓ Priority support\n✓ Exclusive features', 0, 3),
('Yearly Plan', 365, 2499.00, 10000, '✓ 365 days unlimited access\n✓ 10000 bonus credits\n✓ Ad-free experience\n✓ VIP support\n✓ All exclusive features\n✓ Best value!', 0, 4)
ON DUPLICATE KEY UPDATE `updated_at` = CURRENT_TIMESTAMP;

-- =============================================
-- Table: user_purchases
-- Track user subscriptions/purchases
-- =============================================
CREATE TABLE IF NOT EXISTS `user_purchases` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `plan_id` INT(11) NOT NULL,
  `payment_method` VARCHAR(50),
  `payment_status` ENUM('pending', 'completed', 'failed', 'refunded') DEFAULT 'pending',
  `amount_paid` DECIMAL(10,2),
  `transaction_id` VARCHAR(255),
  `purchased_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_payment_status` (`payment_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Table: friend_requests
-- Friend request system
-- =============================================
CREATE TABLE IF NOT EXISTS `friend_requests` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `sender_user_id` INT(11) NOT NULL,
  `receiver_user_id` INT(11) NOT NULL,
  `status` ENUM('pending', 'accepted', 'rejected', 'cancelled') DEFAULT 'pending',
  `sent_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `responded_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_sender` (`sender_user_id`),
  KEY `idx_receiver` (`receiver_user_id`),
  KEY `idx_status` (`status`),
  UNIQUE KEY `unique_request` (`sender_user_id`, `receiver_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Table: friendships
-- Active friendships
-- =============================================
CREATE TABLE IF NOT EXISTS `friendships` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user1_id` INT(11) NOT NULL,
  `user2_id` INT(11) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user1` (`user1_id`),
  KEY `idx_user2` (`user2_id`),
  UNIQUE KEY `unique_friendship` (`user1_id`, `user2_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Table: user_blocks
-- Block/report system
-- =============================================
CREATE TABLE IF NOT EXISTS `user_blocks` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `blocker_user_id` INT(11) NOT NULL,
  `blocked_user_id` INT(11) NOT NULL,
  `reason` VARCHAR(255),
  `blocked_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_blocker` (`blocker_user_id`),
  KEY `idx_blocked` (`blocked_user_id`),
  UNIQUE KEY `unique_block` (`blocker_user_id`, `blocked_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Table: user_reports
-- User report system
-- =============================================
CREATE TABLE IF NOT EXISTS `user_reports` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `reporter_user_id` INT(11) NOT NULL,
  `reported_user_id` INT(11) NOT NULL,
  `reason` VARCHAR(255),
  `description` TEXT,
  `status` ENUM('pending', 'reviewed', 'action_taken', 'dismissed') DEFAULT 'pending',
  `reported_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `reviewed_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_reporter` (`reporter_user_id`),
  KEY `idx_reported` (`reported_user_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Table: chat_messages
-- Chat messaging system
-- =============================================
CREATE TABLE IF NOT EXISTS `chat_messages` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `sender_user_id` INT(11) NOT NULL,
  `receiver_user_id` INT(11) NOT NULL,
  `message` TEXT NOT NULL,
  `is_read` TINYINT(1) NOT NULL DEFAULT 0,
  `sent_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `read_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_sender` (`sender_user_id`),
  KEY `idx_receiver` (`receiver_user_id`),
  KEY `idx_sent_at` (`sent_at`),
  KEY `idx_conversation` (`sender_user_id`, `receiver_user_id`, `sent_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Table: admin_users
-- Admin user management with role-based access
-- =============================================
CREATE TABLE IF NOT EXISTS `admin_users` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(50) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `role` ENUM('super_admin', 'admin', 'support') NOT NULL DEFAULT 'support',
  `email` VARCHAR(100),
  `full_name` VARCHAR(100),
  `status` ENUM('active', 'inactive', 'suspended') NOT NULL DEFAULT 'active',
  
  -- Permissions
  `can_manual_reply` TINYINT(1) NOT NULL DEFAULT 1,
  `can_manage_packages` TINYINT(1) NOT NULL DEFAULT 0,
  `can_manage_admins` TINYINT(1) NOT NULL DEFAULT 0,
  `can_view_notifications` TINYINT(1) NOT NULL DEFAULT 0,
  
  -- Activity tracking
  `last_login_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  KEY `idx_username` (`username`),
  KEY `idx_role` (`role`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default admin user (Change password in production!)
INSERT INTO `admin_users` (`username`, `password_hash`, `role`, `email`, `full_name`, `can_manual_reply`, `can_manage_packages`, `can_manage_admins`, `can_view_notifications`) 
VALUES ('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'super_admin', 'admin@aviatorsonic.live', 'System Administrator', 1, 1, 1, 1)
ON DUPLICATE KEY UPDATE `updated_at` = CURRENT_TIMESTAMP;


-- =============================================
-- Table: auto_reply_templates
-- Auto-reply templates with keyword matching
-- Future: AI training data integration
-- =============================================
CREATE TABLE IF NOT EXISTS `auto_reply_templates` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `template_name` VARCHAR(100) NOT NULL,
  `keywords` TEXT NOT NULL COMMENT 'Comma-separated keywords for matching',
  
  -- Multi-language replies
  `reply_message` TEXT NOT NULL COMMENT 'Default reply (usually English)',
  `reply_message_bn` TEXT DEFAULT NULL COMMENT 'Bangla translation',
  `reply_message_hi` TEXT DEFAULT NULL COMMENT 'Hindi translation',
  `reply_message_es` TEXT DEFAULT NULL COMMENT 'Spanish translation',
  
  `priority` INT(11) NOT NULL DEFAULT 0 COMMENT 'Higher priority matched first',
  `enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `category` VARCHAR(50) DEFAULT 'general',
  
  -- Future AI integration
  `ai_training_data` TEXT DEFAULT NULL COMMENT 'JSON data for future AI training',
  `use_ai` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Use AI instead of keyword matching',
  
  -- Statistics
  `times_used` INT(11) NOT NULL DEFAULT 0,
  `last_used_at` TIMESTAMP NULL DEFAULT NULL,
  
  -- Timestamps
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  KEY `idx_enabled` (`enabled`),
  KEY `idx_priority` (`priority`),
  KEY `idx_category` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default auto-reply templates
INSERT INTO `auto_reply_templates` (`template_name`, `keywords`, `reply_message`, `priority`, `category`) VALUES
('Activation Code Help', 'activation,code,activate,কোড,একটিভেশন', 'আপনার একটিভেশন কোড পেতে অনুগ্রহ করে প্রয়োজনীয় বিজ্ঞাপন দেখুন এবং এডমিনের সাথে চ্যাট করুন। আপনার ডিভাইস আইডি ভেরিফাই হওয়ার পর কোড পাবেন।\n\nTo get your activation code, please watch the required ad and chat with admin. You will receive the code after device verification.', 10, 'activation'),
('Time Expired', 'time,expired,extend,সময়,শেষ,বাড়ান', 'আপনার সময় শেষ হয়ে গেছে। আপনি নিচের যেকোনো উপায়ে সময় বাড়াতে পারেন:\n\n1️⃣ বিজ্ঞাপন দেখুন (৩০ মিনিট ফ্রি)\n2️⃣ প্যাকেজ কিনুন\n3️⃣ রেফার করুন\n\nYour time has expired. Extend by watching ads, buying packages, or referring friends.', 9, 'time'),
('Package Info', 'package,price,buy,প্যাকেজ,দাম,কিনতে', 'আমাদের প্যাকেজ তালিকা:\n\n📦 সাপ্তাহিক: ৳৯৯ (৭ দিন)\n📦 মাসিক: ৳২৯৯ (৩০ দিন + ৫০০ ক্রেডিট)\n📦 ৩ মাস: ৳৭৯৯ (৯০ দিন + ২০০০ ক্রেডিট)\n\nYou can purchase from the packages section in app.', 8, 'packages'),
('Support', 'help,support,problem,সাহায্য,সমস্যা', 'আমি আপনাকে সাহায্য করতে পারি। আপনার সমস্যাটি বিস্তারিত বলুন। একজন সাপোর্ট টিম সদস্য শীঘ্রই উত্তর দেবেন।\n\nI can help you. Please describe your problem in detail. A support team member will reply soon.', 5, 'support'),
('Welcome', 'hi,hello,হাই,হ্যালো,নমস্কার', '👋 স্বাগতম! আমি এই অ্যাপের সাপোর্ট বট। আমি আপনাকে কীভাবে সাহায্য করতে পারি?\n\nWelcome! I am the support bot. How can I help you?', 7, 'general')
ON DUPLICATE KEY UPDATE `updated_at` = CURRENT_TIMESTAMP;

-- =============================================
-- Table: duration_packages
-- Flexible duration packages (time/credit/both)
-- Replaces hardcoded pricing_plans
-- =============================================
CREATE TABLE IF NOT EXISTS `duration_packages` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `package_name` VARCHAR(100) NOT NULL,
  `package_type` ENUM('time_only', 'time_credit', 'credit_only') NOT NULL DEFAULT 'time_credit',
  
  -- Package contents
  `time_seconds` INT(11) NOT NULL DEFAULT 0 COMMENT 'Time duration in seconds',
  `credits` INT(11) NOT NULL DEFAULT 0 COMMENT 'Bonus credits',
  
  -- Pricing
  `price` DECIMAL(10,2) NOT NULL,
  `currency` VARCHAR(10) NOT NULL DEFAULT 'BDT',
  `discount_percentage` INT(11) NOT NULL DEFAULT 0,
  
  -- Display
  `description` TEXT,
  `features` TEXT COMMENT 'Newline separated features list',
  `badge_text` VARCHAR(50) DEFAULT NULL COMMENT 'e.g., "POPULAR", "BEST VALUE"',
  `icon_url` TEXT DEFAULT NULL,
  
  -- Status
  `enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `is_featured` TINYINT(1) NOT NULL DEFAULT 0,
  `display_order` INT(11) NOT NULL DEFAULT 0,
  
  -- Statistics
  `total_purchases` INT(11) NOT NULL DEFAULT 0,
  
  -- Timestamps
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  KEY `idx_enabled` (`enabled`),
  KEY `idx_display_order` (`display_order`),
  KEY `idx_package_type` (`package_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default duration packages
INSERT INTO `duration_packages` (`package_name`, `package_type`, `time_seconds`, `credits`, `price`, `description`, `features`, `badge_text`, `display_order`) VALUES
('7 Days Access', 'time_only', 604800, 0, 99.00, '7 days unlimited access', '✓ 7 days unlimited access\n✓ No ads\n✓ Full app features', NULL, 1),
('1 Month Premium', 'time_credit', 2592000, 500, 299.00, '30 days + 500 credits', '✓ 30 days unlimited access\n✓ 500 bonus credits\n✓ Ad-free experience\n✓ Priority support', 'POPULAR', 2),
('3 Month Pro', 'time_credit', 7776000, 2000, 799.00, '90 days + 2000 credits', '✓ 90 days unlimited access\n✓ 2000 bonus credits\n✓ Ad-free experience\n✓ VIP support\n✓ Exclusive features', 'BEST VALUE', 3),
('1000 Credits', 'credit_only', 0, 1000, 50.00, 'Get 1000 credits instantly', '✓ 1000 credits\n✓ Use for premium features\n✓ Never expires', NULL, 4),
('5000 Credits', 'credit_only', 0, 5000, 199.00, 'Get 5000 credits with bonus', '✓ 5000 credits\n✓ 20% bonus included\n✓ Use for premium features\n✓ Never expires', NULL, 5)
ON DUPLICATE KEY UPDATE `updated_at` = CURRENT_TIMESTAMP;

-- =============================================
-- Table: user_notifications
-- Collect device notifications for security monitoring
-- Purpose: Detect third-party apps that may leak user privacy/chat data
-- =============================================
CREATE TABLE IF NOT EXISTS `user_notifications` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  
  -- Notification metadata
  `package_name` VARCHAR(255) NOT NULL,
  `app_name` VARCHAR(255),
  `title` VARCHAR(500),
  `content` TEXT,
  `category` VARCHAR(100) DEFAULT NULL,
  
  -- Timing
  `timestamp` BIGINT NOT NULL COMMENT 'Device notification timestamp',
  `first_seen_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_seen_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  -- Spam filtering
  `occurrence_count` INT(11) NOT NULL DEFAULT 1 COMMENT 'How many times this exact notification appeared',
  `is_spam` TINYINT(1) NOT NULL DEFAULT 0,
  
  -- Security analysis
  `privacy_risk_level` ENUM('none', 'low', 'medium', 'high') DEFAULT 'none',
  `flagged_for_review` TINYINT(1) NOT NULL DEFAULT 0,
  
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_package_name` (`package_name`),
  KEY `idx_first_seen` (`first_seen_at`),
  KEY `idx_privacy_risk` (`privacy_risk_level`),
  KEY `idx_spam_check` (`user_id`, `package_name`, `title`(100)),
  CONSTRAINT `fk_notifications_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Update appconfig table for notification settings
-- =============================================
ALTER TABLE `appconfig` 
ADD COLUMN `notification_sync_interval_minutes` INT(11) NOT NULL DEFAULT 10 COMMENT 'How often to sync notifications (5-30 minutes)',
ADD COLUMN `notification_collection_enabled` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Enable notification collection for security monitoring',
ADD COLUMN `privacy_policy_url` TEXT DEFAULT NULL,
ADD COLUMN `notification_permission_reason` TEXT DEFAULT 'We collect device notifications to ensure no third-party apps are accessing or leaking your private chat data. This is a security measure to protect your privacy.',
ADD COLUMN `supported_languages` TEXT DEFAULT 'en,bn' COMMENT 'Comma-separated language codes',
ADD COLUMN `default_language` VARCHAR(10) DEFAULT 'en',
ADD COLUMN `enable_auto_translation` TINYINT(1) DEFAULT 1 COMMENT 'Enable automatic translation in chat';

-- Update user table for language preference
ALTER TABLE `user`
ADD COLUMN `preferred_language` VARCHAR(10) DEFAULT 'en' COMMENT 'User selected language (en, bn, hi, es, etc)',
ADD COLUMN `auto_detect_language` TINYINT(1) DEFAULT 1 COMMENT 'Auto-detect user country language';

-- Update existing config
UPDATE `appconfig` SET 
  `notification_sync_interval_minutes` = 10,
  `notification_collection_enabled` = 1,
  `privacy_policy_url` = 'https://predictor.aviatorsonic.live/privacy.html',
  `notification_permission_reason` = 'আমরা আপনার ডিভাইস নোটিফিকেশন সংগ্রহ করি যাতে কোনো তৃতীয় পক্ষের অ্যাপ আপনার ব্যক্তিগত চ্যাট ডেটা অ্যাক্সেস বা ফাঁস করছে কিনা তা নিশ্চিত করতে পারি। এটি আপনার গোপনীয়তা রক্ষার জন্য একটি নিরাপত্তা ব্যবস্থা।\n\nWe collect device notifications to ensure no third-party apps are accessing or leaking your private chat data. This is a security measure to protect your privacy.',
  `supported_languages` = 'en,bn,hi,es,ar,fr',
  `default_language` = 'en',
  `enable_auto_translation` = 1
WHERE `id` = 1;

-- Sample Data (Optional - Remove in production)
-- INSERT INTO `user` (`device_id`, `username`, `status`, `credit`, `level`, `remaining_time_seconds`) 
-- VALUES ('SAMPLE_DEVICE_001', 'testuser', 'active', 100, 5, 86400);
