-- Aviator Sonic User Authentication Database Structure
-- Database: kfgwkeab_app
-- Username: kfgwkeab_app
-- Password: kfgwkeab_app

-- Create users table
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deviceid` varchar(255) NOT NULL UNIQUE,
  `password` varchar(255) NOT NULL,
  `country` varchar(100) NOT NULL,
  `level` enum('basic','premium','admin') NOT NULL DEFAULT 'basic',
  `credit` decimal(10,2) NOT NULL DEFAULT 0.00,
  `expiretime` datetime NOT NULL,
  `exx1` varchar(255) DEFAULT NULL,
  `exx2` varchar(255) DEFAULT NULL,
  `exx3` varchar(255) DEFAULT NULL,
  `exx4` varchar(255) DEFAULT NULL,
  `exx5` varchar(255) 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','suspended') NOT NULL DEFAULT 'active',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_deviceid` (`deviceid`),
  KEY `idx_level` (`level`),
  KEY `idx_country` (`country`),
  KEY `idx_status` (`status`),
  KEY `idx_expiretime` (`expiretime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert sample users for testing
INSERT INTO `users` (`deviceid`, `password`, `country`, `level`, `credit`, `expiretime`, `exx1`, `exx2`, `exx3`, `exx4`, `exx5`) VALUES
('sample_device_001', 'test123', 'USA', 'premium', 100.00, '2024-12-31 23:59:59', 'extra1', 'extra2', 'extra3', 'extra4', 'extra5'),
('sample_device_002', 'demo456', 'UK', 'basic', 50.00, '2024-06-30 23:59:59', NULL, NULL, NULL, NULL, NULL),
('admin_device_001', 'admin123', 'Global', 'admin', 999.99, '2025-12-31 23:59:59', 'admin_extra', NULL, NULL, NULL, NULL);

-- Create login_logs table for tracking authentication attempts
CREATE TABLE IF NOT EXISTS `login_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deviceid` varchar(255) NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `login_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` enum('success','failed','blocked') NOT NULL,
  `failure_reason` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_deviceid` (`deviceid`),
  KEY `idx_login_time` (`login_time`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create user_sessions table for managing active sessions
CREATE TABLE IF NOT EXISTS `user_sessions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deviceid` varchar(255) NOT NULL,
  `session_token` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `expires_at` timestamp NOT NULL,
  `last_activity` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `status` enum('active','expired','revoked') NOT NULL DEFAULT 'active',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_session_token` (`session_token`),
  KEY `idx_deviceid` (`deviceid`),
  KEY `idx_expires_at` (`expires_at`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create user_credits_history table for tracking credit changes
CREATE TABLE IF NOT EXISTS `user_credits_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deviceid` varchar(255) NOT NULL,
  `previous_credit` decimal(10,2) NOT NULL,
  `new_credit` decimal(10,2) NOT NULL,
  `change_amount` decimal(10,2) NOT NULL,
  `change_type` enum('add','subtract','set') NOT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `admin_deviceid` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_deviceid` (`deviceid`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_change_type` (`change_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create app_settings table for application configuration
CREATE TABLE IF NOT EXISTS `app_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL UNIQUE,
  `setting_value` text NOT NULL,
  `description` varchar(255) 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`),
  UNIQUE KEY `unique_setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default app settings
INSERT INTO `app_settings` (`setting_key`, `setting_value`, `description`) VALUES
('app_version', '1.0.0', 'Current application version'),
('maintenance_mode', 'false', 'Enable/disable maintenance mode'),
('max_login_attempts', '5', 'Maximum login attempts before blocking'),
('session_timeout', '3600', 'Session timeout in seconds'),
('default_credit', '0', 'Default credit for new users'),
('registration_enabled', 'false', 'Enable/disable new user registration');

-- Create indexes for better performance
CREATE INDEX idx_users_level_status ON users(level, status);
CREATE INDEX idx_users_expiretime_status ON users(expiretime, status);
CREATE INDEX idx_login_logs_deviceid_time ON login_logs(deviceid, login_time);
CREATE INDEX idx_sessions_deviceid_status ON user_sessions(deviceid, status);

-- Create views for common queries
CREATE VIEW active_users AS
SELECT 
    deviceid,
    country,
    level,
    credit,
    expiretime,
    created_at,
    CASE 
        WHEN expiretime > NOW() THEN 'active'
        ELSE 'expired'
    END as account_status
FROM users 
WHERE status = 'active';

CREATE VIEW user_stats AS
SELECT 
    level,
    country,
    COUNT(*) as user_count,
    AVG(credit) as avg_credit,
    SUM(credit) as total_credit
FROM users 
WHERE status = 'active'
GROUP BY level, country;

-- Create stored procedures for common operations
DELIMITER //

CREATE PROCEDURE GetUserByDeviceId(IN device_id VARCHAR(255))
BEGIN
    SELECT * FROM users WHERE deviceid = device_id AND status = 'active';
END //

CREATE PROCEDURE UpdateUserCredit(IN device_id VARCHAR(255), IN new_credit DECIMAL(10,2), IN change_reason VARCHAR(255))
BEGIN
    DECLARE old_credit DECIMAL(10,2);
    DECLARE change_amount DECIMAL(10,2);
    
    -- Get current credit
    SELECT credit INTO old_credit FROM users WHERE deviceid = device_id;
    
    -- Calculate change amount
    SET change_amount = new_credit - old_credit;
    
    -- Update user credit
    UPDATE users SET credit = new_credit WHERE deviceid = device_id;
    
    -- Log the change
    INSERT INTO user_credits_history (deviceid, previous_credit, new_credit, change_amount, change_type, reason)
    VALUES (device_id, old_credit, new_credit, change_amount, 'set', change_reason);
END //

CREATE PROCEDURE LogLoginAttempt(IN device_id VARCHAR(255), IN ip VARCHAR(45), IN agent TEXT, IN attempt_status VARCHAR(20), IN fail_reason VARCHAR(255))
BEGIN
    INSERT INTO login_logs (deviceid, ip_address, user_agent, status, failure_reason)
    VALUES (device_id, ip, agent, attempt_status, fail_reason);
END //

DELIMITER ;

-- Create triggers for automatic logging
DELIMITER //

CREATE TRIGGER user_credit_change_trigger
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    IF OLD.credit != NEW.credit THEN
        INSERT INTO user_credits_history (deviceid, previous_credit, new_credit, change_amount, change_type, reason)
        VALUES (NEW.deviceid, OLD.credit, NEW.credit, (NEW.credit - OLD.credit), 'set', 'Manual update');
    END IF;
END //

DELIMITER ;

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

-- Sample queries for testing
-- SELECT * FROM users WHERE deviceid = 'sample_device_001';
-- SELECT * FROM active_users;
-- SELECT * FROM user_stats;
-- CALL GetUserByDeviceId('sample_device_001');
-- CALL UpdateUserCredit('sample_device_001', 150.00, 'Bonus credit added');
-- CALL LogLoginAttempt('sample_device_001', '192.168.1.1', 'Android App', 'success', NULL);