-- Phase 10: Security Hardening & Production
-- Add to your MySQL database

-- Rate limiting (prevent brute force)
CREATE TABLE IF NOT EXISTS `rate_limits` (
  `id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `identifier` varchar(100) NOT NULL COMMENT 'IP address, email, or user ID',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  
  KEY `identifier` (`identifier`),
  KEY `identifier_created` (`identifier`, `created_at`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Rate limiting tracking';

-- Error logs (detailed error tracking)
CREATE TABLE IF NOT EXISTS `error_logs` (
  `id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `message` varchar(500) NOT NULL,
  `context` json DEFAULT NULL COMMENT 'Error context as JSON',
  `ip_address` varchar(50) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `user_id` int unsigned DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  
  KEY `user_id` (`user_id`),
  KEY `created_at` (`created_at`),
  KEY `ip_address` (`ip_address`),
  
  CONSTRAINT `fk_error_logs_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Error logs for debugging';

-- Security events (suspicious activity tracking)
CREATE TABLE IF NOT EXISTS `security_events` (
  `id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `event_type` varchar(50) NOT NULL COMMENT 'login_attempt, failed_auth, suspicious_input, etc',
  `description` text,
  `user_id` int unsigned DEFAULT NULL,
  `ip_address` varchar(50) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `context` json DEFAULT NULL COMMENT 'Event context as JSON',
  `severity` varchar(20) DEFAULT 'info' COMMENT 'info, warning, critical',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  
  KEY `user_id` (`user_id`),
  KEY `event_type` (`event_type`),
  KEY `severity` (`severity`),
  KEY `created_at` (`created_at`),
  KEY `event_created` (`event_type`, `created_at`),
  
  CONSTRAINT `fk_security_events_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Security events for audit and threat detection';

-- Add CSRF tokens table (optional, for stateless CSRF validation)
CREATE TABLE IF NOT EXISTS `csrf_tokens` (
  `id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `token` varchar(255) NOT NULL UNIQUE,
  `user_id` int unsigned NOT NULL,
  `expires_at` datetime NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  
  KEY `user_id` (`user_id`),
  KEY `expires_at` (`expires_at`),
  
  CONSTRAINT `fk_csrf_tokens_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='CSRF tokens for stateless validation';

-- Add security settings
CREATE TABLE IF NOT EXISTS `security_settings` (
  `id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `setting_key` varchar(100) NOT NULL UNIQUE,
  `setting_value` varchar(255),
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Security configuration settings';

-- Insert default security settings
INSERT INTO `security_settings` (`setting_key`, `setting_value`) VALUES
('rate_limit_enabled', '1'),
('rate_limit_window', '3600'),
('rate_limit_max_attempts', '100'),
('csrf_protection_enabled', '1'),
('password_min_length', '8'),
('password_expiry_days', '90'),
('session_timeout', '3600'),
('failed_login_attempts', '5'),
('failed_login_lockout_minutes', '15'),
('suspicious_activity_alert', '1'),
('api_key_rotation_days', '180'),
('encryption_enabled', '1')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);

-- Add indexes for security queries
CREATE INDEX idx_rate_limits_cleanup ON rate_limits(created_at);
CREATE INDEX idx_error_logs_user_date ON error_logs(user_id, created_at DESC);
CREATE INDEX idx_security_events_user_date ON security_events(user_id, created_at DESC);
CREATE INDEX idx_security_events_severity ON security_events(severity, created_at DESC);
CREATE INDEX idx_csrf_tokens_expires ON csrf_tokens(expires_at);

-- Add columns to users table if not exist
ALTER TABLE `users` 
  ADD COLUMN IF NOT EXISTS `last_login` datetime DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `failed_login_attempts` int unsigned DEFAULT 0,
  ADD COLUMN IF NOT EXISTS `locked_until` datetime DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `email_verified` tinyint(1) DEFAULT 0,
  ADD COLUMN IF NOT EXISTS `phone_verified` tinyint(1) DEFAULT 0,
  ADD COLUMN IF NOT EXISTS `two_factor_enabled` tinyint(1) DEFAULT 0,
  ADD COLUMN IF NOT EXISTS `two_factor_secret` varchar(255) DEFAULT NULL;

-- Add security indexes to users
CREATE INDEX IF NOT EXISTS idx_users_locked_until ON users(locked_until);
CREATE INDEX IF NOT EXISTS idx_users_email_verified ON users(email_verified);

-- Add columns to wallet_transactions for security tracking
ALTER TABLE `wallet_transactions`
  ADD COLUMN IF NOT EXISTS `ip_address` varchar(50) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `user_agent` text DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `fraud_flag` tinyint(1) DEFAULT 0;

-- Add columns to orders for security tracking
ALTER TABLE `orders`
  ADD COLUMN IF NOT EXISTS `ip_address` varchar(50) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `user_agent` text DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `fraud_flag` tinyint(1) DEFAULT 0;

-- Add columns to api_logs for security tracking
ALTER TABLE `api_logs`
  ADD COLUMN IF NOT EXISTS `ip_address` varchar(50) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `security_flag` tinyint(1) DEFAULT 0;

-- Create view for security dashboard
CREATE OR REPLACE VIEW `security_dashboard` AS
SELECT 
  'Failed Logins' as metric,
  COUNT(*) as count,
  MAX(created_at) as last_occurrence
FROM security_events
WHERE event_type = 'failed_login' AND created_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)

UNION ALL

SELECT 
  'Suspicious Inputs' as metric,
  COUNT(*) as count,
  MAX(created_at) as last_occurrence
FROM security_events
WHERE event_type = 'suspicious_input' AND created_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)

UNION ALL

SELECT 
  'Critical Alerts' as metric,
  COUNT(*) as count,
  MAX(created_at) as last_occurrence
FROM security_events
WHERE severity = 'critical' AND created_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR);

-- Cleanup procedures (for maintenance)
DELIMITER //

CREATE PROCEDURE IF NOT EXISTS cleanup_old_rate_limits()
BEGIN
  DELETE FROM rate_limits WHERE created_at < DATE_SUB(NOW(), INTERVAL 7 DAY);
END//

CREATE PROCEDURE IF NOT EXISTS cleanup_old_error_logs()
BEGIN
  DELETE FROM error_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
END//

CREATE PROCEDURE IF NOT EXISTS cleanup_old_security_events()
BEGIN
  DELETE FROM security_events WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);
END//

CREATE PROCEDURE IF NOT EXISTS cleanup_expired_csrf_tokens()
BEGIN
  DELETE FROM csrf_tokens WHERE expires_at < NOW();
END//

DELIMITER ;