

-- 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_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 INDEX idx_login_logs_deviceid_time ON login_logs(deviceid, login_time);
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);
    
    -- Calculate change amount
    SET change_amount = new_credit - old_credit;
    
    -- 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 ;
    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);