-- Aviator Sonic App Information Database Structure
-- Database: kfgwkeab_app
-- Username: kfgwkeab_app
-- Password: kfgwkeab_app

-- Create appinfo table for application configuration
CREATE TABLE IF NOT EXISTS `appinfo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `app_name` varchar(255) NOT NULL,
  `app_packagename` varchar(255) NOT NULL,
  `latest_version` varchar(50) NOT NULL,
  `latest_version_apk_url` text NOT NULL,
  `maintenance_mode` tinyint(1) NOT NULL DEFAULT 0,
  `admin_telegram_url` varchar(500) DEFAULT NULL,
  `admin_whatsapp_url` varchar(500) DEFAULT NULL,
  `admin_url_1` varchar(500) DEFAULT NULL,
  `admin_url_2` varchar(500) DEFAULT NULL,
  `admin_url_3` varchar(500) DEFAULT NULL,
  `admin_url_4` varchar(500) DEFAULT NULL,
  `admin_url_5` varchar(500) DEFAULT NULL,
  `free_register_mode` tinyint(1) NOT NULL DEFAULT 0,
  `free_register_time_minutes` int(11) NOT NULL DEFAULT 60,
  `free_register_credit` decimal(10,2) NOT NULL DEFAULT 0.00,
  `free_user_level` enum('basic','premium','admin') NOT NULL DEFAULT 'basic',
  `extra_1` text DEFAULT NULL,
  `extra_2` text DEFAULT NULL,
  `extra_3` text DEFAULT NULL,
  `extra_4` text DEFAULT NULL,
  `extra_5` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_package` (`app_packagename`),
  KEY `idx_app_name` (`app_name`),
  KEY `idx_maintenance` (`maintenance_mode`),
  KEY `idx_free_register` (`free_register_mode`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default app configuration
INSERT INTO `appinfo` (
  `app_name`,
  `app_packagename`,
  `latest_version`,
  `latest_version_apk_url`,
  `maintenance_mode`,
  `admin_telegram_url`,
  `admin_whatsapp_url`,
  `admin_url_1`,
  `admin_url_2`,
  `admin_url_3`,
  `admin_url_4`,
  `admin_url_5`,
  `free_register_mode`,
  `free_register_time_minutes`,
  `free_register_credit`,
  `free_user_level`,
  `extra_1`,
  `extra_2`,
  `extra_3`,
  `extra_4`,
  `extra_5`
) VALUES (
  'AVIATOR SONIC',
  'aviator.sonic.user',
  '1.0.0',
  'https://app.obsidianff.com/downloads/aviator-sonic-latest.apk',
  0,
  'https://t.me/aviatorsonic_admin',
  'https://wa.me/1234567890',
  'https://app.obsidianff.com/support',
  'https://app.obsidianff.com/help',
  'https://app.obsidianff.com/faq',
  'https://app.obsidianff.com/contact',
  'https://app.obsidianff.com/terms',
  1,
  1440,
  100.00,
  'basic',
  'Welcome bonus enabled',
  'Auto registration active',
  'Version check enabled',
  'Maintenance notifications on',
  'Security bypass available'
);

-- Create app_versions table for version history tracking
CREATE TABLE IF NOT EXISTS `app_versions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `version_name` varchar(50) NOT NULL,
  `version_code` int(11) NOT NULL,
  `apk_url` text NOT NULL,
  `release_notes` text DEFAULT NULL,
  `is_critical_update` tinyint(1) NOT NULL DEFAULT 0,
  `min_supported_version` varchar(50) DEFAULT NULL,
  `release_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` enum('active','deprecated','beta') NOT NULL DEFAULT 'active',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_version_name` (`version_name`),
  UNIQUE KEY `unique_version_code` (`version_code`),
  KEY `idx_release_date` (`release_date`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert sample version data
INSERT INTO `app_versions` (
  `version_name`,
  `version_code`,
  `apk_url`,
  `release_notes`,
  `is_critical_update`,
  `min_supported_version`
) VALUES 
('1.0.0', 1, 'https://app.obsidianff.com/downloads/aviator-sonic-v1.0.0.apk', 'Initial release with basic features', 0, '1.0.0'),
('1.0.1', 2, 'https://app.obsidianff.com/downloads/aviator-sonic-v1.0.1.apk', 'Bug fixes and performance improvements', 0, '1.0.0'),
('1.1.0', 3, 'https://app.obsidianff.com/downloads/aviator-sonic-v1.1.0.apk', 'New features and UI improvements', 1, '1.0.0');

-- Create maintenance_messages table for custom maintenance messages
CREATE TABLE IF NOT EXISTS `maintenance_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `start_time` timestamp NULL DEFAULT NULL,
  `end_time` timestamp NULL DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_active` (`is_active`),
  KEY `idx_time_range` (`start_time`, `end_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default maintenance message
INSERT INTO `maintenance_messages` (
  `title`,
  `message`,
  `start_time`,
  `end_time`,
  `is_active`
) VALUES (
  'Scheduled Maintenance',
  'We are currently performing scheduled maintenance to improve your experience. Please try again later. Thank you for your patience!',
  NULL,
  NULL,
  1
);

-- Create stored procedures for app info management
DELIMITER //

CREATE PROCEDURE GetAppInfo(IN package_name VARCHAR(255))
BEGIN
    SELECT * FROM appinfo WHERE app_packagename = package_name AND status = 'active' LIMIT 1;
END //

CREATE PROCEDURE CheckAppVersion(IN package_name VARCHAR(255), IN current_version VARCHAR(50))
BEGIN
    DECLARE latest_ver VARCHAR(50);
    DECLARE is_outdated BOOLEAN DEFAULT FALSE;
    
    SELECT latest_version INTO latest_ver 
    FROM appinfo 
    WHERE app_packagename = package_name AND status = 'active' 
    LIMIT 1;
    
    IF latest_ver != current_version THEN
        SET is_outdated = TRUE;
    END IF;
    
    SELECT latest_ver as latest_version, is_outdated as needs_update;
END //

CREATE PROCEDURE GetMaintenanceMessage()
BEGIN
    SELECT title, message 
    FROM maintenance_messages 
    WHERE is_active = 1 
    AND (start_time IS NULL OR start_time <= NOW()) 
    AND (end_time IS NULL OR end_time >= NOW()) 
    ORDER BY id DESC 
    LIMIT 1;
END //

CREATE PROCEDURE UpdateAppInfo(
    IN p_app_name VARCHAR(255),
    IN p_latest_version VARCHAR(50),
    IN p_apk_url TEXT,
    IN p_maintenance_mode TINYINT,
    IN p_free_register_mode TINYINT,
    IN p_package_name VARCHAR(255)
)
BEGIN
    UPDATE appinfo SET 
        app_name = p_app_name,
        latest_version = p_latest_version,
        latest_version_apk_url = p_apk_url,
        maintenance_mode = p_maintenance_mode,
        free_register_mode = p_free_register_mode,
        updated_at = CURRENT_TIMESTAMP
    WHERE app_packagename = p_package_name;
END //

DELIMITER ;

-- Create views for common queries
CREATE VIEW active_app_config AS
SELECT 
    app_name,
    app_packagename,
    latest_version,
    latest_version_apk_url,
    maintenance_mode,
    free_register_mode,
    free_register_time_minutes,
    free_register_credit,
    free_user_level,
    admin_telegram_url,
    admin_whatsapp_url
FROM appinfo 
WHERE status = 'active';

CREATE VIEW maintenance_status AS
SELECT 
    a.app_name,
    a.app_packagename,
    a.maintenance_mode,
    m.title as maintenance_title,
    m.message as maintenance_message,
    m.start_time,
    m.end_time
FROM appinfo a
LEFT JOIN maintenance_messages m ON m.is_active = 1
WHERE a.status = 'active';

-- Create indexes for better performance
CREATE INDEX idx_appinfo_package_status ON appinfo(app_packagename, status);
CREATE INDEX idx_versions_name_status ON app_versions(version_name, status);
CREATE INDEX idx_maintenance_active_time ON maintenance_messages(is_active, start_time, end_time);

-- Sample queries for testing
-- SELECT * FROM appinfo WHERE app_packagename = 'aviator.sonic.user';
-- CALL GetAppInfo('aviator.sonic.user');
-- CALL CheckAppVersion('aviator.sonic.user', '1.0.0');
-- CALL GetMaintenanceMessage();
-- SELECT * FROM active_app_config;
-- SELECT * FROM maintenance_status;

-- Grant necessary permissions (run as database administrator)
-- GRANT SELECT, INSERT, UPDATE, DELETE ON kfgwkeab_app.appinfo TO 'kfgwkeab_app'@'localhost';
-- GRANT SELECT, INSERT, UPDATE, DELETE ON kfgwkeab_app.app_versions TO 'kfgwkeab_app'@'localhost';
-- GRANT SELECT, INSERT, UPDATE, DELETE ON kfgwkeab_app.maintenance_messages TO 'kfgwkeab_app'@'localhost';
-- FLUSH PRIVILEGES;