-- Watermark Security System Tables
-- Implements comprehensive security measures for watermark operations

-- Watermark Security Logs Table
CREATE TABLE `watermark_security_logs` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` bigint(20) UNSIGNED NOT NULL,
    `event_type` varchar(100) NOT NULL,
    `severity` enum('low', 'medium', 'high', 'critical') NOT NULL DEFAULT 'medium',
    `ip_address` varchar(45) NULL,
    `user_agent` text NULL,
    `session_id` varchar(255) NULL,
    `metadata` json NULL,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_event_type` (`event_type`),
    KEY `idx_severity` (`severity`),
    KEY `idx_created_at` (`created_at`),
    KEY `idx_ip_address` (`ip_address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Watermark Audit Logs Table
CREATE TABLE `watermark_audit_logs` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` bigint(20) UNSIGNED NOT NULL,
    `operation` varchar(100) NOT NULL,
    `resource_type` varchar(100) NOT NULL DEFAULT 'watermark',
    `resource_id` bigint(20) UNSIGNED NULL,
    `old_values` json NULL,
    `new_values` json NULL,
    `ip_address` varchar(45) NULL,
    `user_agent` text NULL,
    `session_id` varchar(255) NULL,
    `success` tinyint(1) NOT NULL DEFAULT 1,
    `error_message` text NULL,
    `processing_time` int(11) NULL,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_operation` (`operation`),
    KEY `idx_resource_type` (`resource_type`),
    KEY `idx_resource_id` (`resource_id`),
    KEY `idx_success` (`success`),
    KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- File Upload Security Scans Table
CREATE TABLE `watermark_file_scans` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` bigint(20) UNSIGNED NOT NULL,
    `scan_id` varchar(36) NOT NULL,
    `filename` varchar(255) NOT NULL,
    `file_hash` varchar(64) NOT NULL,
    `file_size` bigint(20) NOT NULL,
    `mime_type` varchar(100) NOT NULL,
    `scan_result` enum('safe', 'threat_detected', 'scan_failed') NOT NULL,
    `threats_detected` json NULL,
    `scan_duration` int(11) NULL,
    `quarantined` tinyint(1) NOT NULL DEFAULT 0,
    `quarantine_reason` text NULL,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `scan_id` (`scan_id`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_file_hash` (`file_hash`),
    KEY `idx_scan_result` (`scan_result`),
    KEY `idx_quarantined` (`quarantined`),
    KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Suspicious Activity Patterns Table
CREATE TABLE `watermark_suspicious_activity` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` bigint(20) UNSIGNED NOT NULL,
    `pattern_type` varchar(100) NOT NULL,
    `severity` enum('low', 'medium', 'high', 'critical') NOT NULL,
    `description` text NOT NULL,
    `detection_count` int(11) NOT NULL DEFAULT 1,
    `first_detected_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_detected_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `status` enum('active', 'resolved', 'false_positive') NOT NULL DEFAULT 'active',
    `resolved_by` bigint(20) UNSIGNED NULL,
    `resolution_notes` text NULL,
    `metadata` json NULL,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_pattern_type` (`pattern_type`),
    KEY `idx_severity` (`severity`),
    KEY `idx_status` (`status`),
    KEY `idx_first_detected_at` (`first_detected_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Security Incident Response Table
CREATE TABLE `watermark_security_incidents` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `incident_type` varchar(100) NOT NULL,
    `severity` enum('low', 'medium', 'high', 'critical') NOT NULL,
    `user_id` bigint(20) UNSIGNED NULL,
    `affected_users` json NULL,
    `description` text NOT NULL,
    `detection_method` varchar(255) NULL,
    `detected_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `status` enum('open', 'investigating', 'contained', 'resolved') NOT NULL DEFAULT 'open',
    `assigned_to` bigint(20) UNSIGNED NULL,
    `response_actions` json NULL,
    `resolution_summary` text NULL,
    `resolved_at` timestamp NULL DEFAULT NULL,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_incident_type` (`incident_type`),
    KEY `idx_severity` (`severity`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_status` (`status`),
    KEY `idx_detected_at` (`detected_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Threat Intelligence Table
CREATE TABLE `watermark_threat_intelligence` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `threat_type` varchar(100) NOT NULL,
    `threat_signature` text NOT NULL,
    `threat_description` text NOT NULL,
    `severity` enum('low', 'medium', 'high', 'critical') NOT NULL,
    `detection_pattern` text NOT NULL,
    `mitigation_action` varchar(255) NOT NULL,
    `source` varchar(255) NULL,
    `is_active` tinyint(1) NOT NULL DEFAULT 1,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_threat_type` (`threat_type`),
    KEY `idx_severity` (`severity`),
    KEY `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Rate Limiting Table
CREATE TABLE `watermark_rate_limits` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` bigint(20) UNSIGNED NULL,
    `ip_address` varchar(45) NULL,
    `action_type` varchar(100) NOT NULL,
    `request_count` int(11) NOT NULL DEFAULT 1,
    `window_start` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `window_end` timestamp NOT NULL,
    `limit_exceeded` tinyint(1) NOT NULL DEFAULT 0,
    `blocked_until` timestamp NULL DEFAULT NULL,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_ip_address` (`ip_address`),
    KEY `idx_action_type` (`action_type`),
    KEY `idx_window_end` (`window_end`),
    KEY `idx_blocked_until` (`blocked_until`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Security Configuration Table
CREATE TABLE `watermark_security_config` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `config_key` varchar(100) NOT NULL,
    `config_value` text NOT NULL,
    `config_type` enum('string', 'integer', 'boolean', 'json') NOT NULL DEFAULT 'string',
    `description` text NULL,
    `is_sensitive` tinyint(1) NOT NULL DEFAULT 0,
    `updated_by` bigint(20) UNSIGNED NULL,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `config_key` (`config_key`),
    KEY `idx_is_sensitive` (`is_sensitive`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default security configuration
INSERT INTO `watermark_security_config` (`config_key`, `config_value`, `config_type`, `description`) VALUES
('max_file_size', '5242880', 'integer', 'Maximum file size for watermark uploads in bytes'),
('allowed_mime_types', '["image/jpeg","image/png","image/gif","image/webp","image/svg+xml"]', 'json', 'Allowed MIME types for watermark files'),
('scan_timeout', '30', 'integer', 'File scan timeout in seconds'),
('quarantine_enabled', 'true', 'boolean', 'Enable automatic quarantine of suspicious files'),
('rate_limit_uploads', '10', 'integer', 'Maximum uploads per user per minute'),
('rate_limit_validations', '20', 'integer', 'Maximum validation attempts per user per hour'),
('security_logging_enabled', 'true', 'boolean', 'Enable security event logging'),
('threat_detection_enabled', 'true', 'boolean', 'Enable threat detection scanning'),
('metadata_scanning_enabled', 'true', 'boolean', 'Enable image metadata scanning'),
('suspicious_activity_threshold', '5', 'integer', 'Threshold for suspicious activity detection');

-- Insert default threat signatures
INSERT INTO `watermark_threat_intelligence` (`threat_type`, `threat_signature`, `threat_description`, `severity`, `detection_pattern`, `mitigation_action`, `source`) VALUES
('php_code_injection', '<?php', 'PHP code embedded in image file', 'critical', 'regex:/\\<\\?php/i', 'quarantine', 'internal'),
('javascript_injection', '<script', 'JavaScript code embedded in image file', 'high', 'regex:/\\<script/i', 'quarantine', 'internal'),
('server_side_include', '<%', 'Server-side include code in image file', 'high', 'regex:/\\<%/i', 'quarantine', 'internal'),
('eval_function', 'eval(', 'Eval function call detected', 'critical', 'regex:/eval\\s*\\(/i', 'quarantine', 'internal'),
('system_command', 'system(', 'System command execution detected', 'critical', 'regex:/system\\s*\\(/i', 'quarantine', 'internal'),
('shell_execution', 'shell_exec(', 'Shell execution function detected', 'critical', 'regex:/shell_exec\\s*\\(/i', 'quarantine', 'internal'),
('base64_decode', 'base64_decode(', 'Base64 decode function (potential obfuscation)', 'medium', 'regex:/base64_decode\\s*\\(/i', 'flag', 'internal'),
('data_uri_html', 'data:text/html', 'Data URI with HTML content', 'high', 'string:data:text/html', 'quarantine', 'internal');

-- Enterprise Standard: NO FOREIGN KEYS - Pure SQL Implementation
-- Foreign key constraints removed for maximum performance and flexibility
-- Data integrity maintained through application logic

-- Create indexes for performance optimization
CREATE INDEX `idx_security_logs_user_event` ON `watermark_security_logs` (`user_id`, `event_type`);
CREATE INDEX `idx_security_logs_severity_date` ON `watermark_security_logs` (`severity`, `created_at`);
CREATE INDEX `idx_audit_logs_user_operation` ON `watermark_audit_logs` (`user_id`, `operation`);
CREATE INDEX `idx_audit_logs_success_date` ON `watermark_audit_logs` (`success`, `created_at`);
CREATE INDEX `idx_file_scans_user_result` ON `watermark_file_scans` (`user_id`, `scan_result`);
CREATE INDEX `idx_suspicious_activity_user_status` ON `watermark_suspicious_activity` (`user_id`, `status`);
CREATE INDEX `idx_incidents_severity_status` ON `watermark_security_incidents` (`severity`, `status`);
CREATE INDEX `idx_rate_limits_user_action` ON `watermark_rate_limits` (`user_id`, `action_type`);
CREATE INDEX `idx_rate_limits_ip_action` ON `watermark_rate_limits` (`ip_address`, `action_type`);

-- Add security-related columns to existing watermark tables if they don't exist
ALTER TABLE `watermark_settings` 
ADD COLUMN `security_scan_passed` tinyint(1) NOT NULL DEFAULT 0,
ADD COLUMN `last_security_scan` timestamp NULL DEFAULT NULL,
ADD COLUMN `scan_id` varchar(36) NULL DEFAULT NULL;

-- Create security scan index
CREATE INDEX `idx_watermark_settings_security` ON `watermark_settings` (`security_scan_passed`, `last_security_scan`);