-- Watermark Monitoring and Alerting System Tables
-- Implements comprehensive monitoring, alerting, and incident detection

-- System Health Monitoring Logs Table
CREATE TABLE `watermark_system_health_logs` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `overall_status` enum('healthy', 'warning', 'critical', 'error') NOT NULL,
    `components_status` json NOT NULL,
    `metrics` json NOT NULL,
    `alerts_count` int(11) NOT NULL DEFAULT 0,
    `alerts` json NULL,
    `response_time_ms` decimal(10,2) NULL,
    `memory_usage_mb` decimal(10,2) NULL,
    `disk_usage_percent` decimal(5,2) NULL,
    `cpu_usage_percent` decimal(5,2) NULL,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_overall_status` (`overall_status`),
    KEY `idx_created_at` (`created_at`),
    KEY `idx_alerts_count` (`alerts_count`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Performance Metrics Table
CREATE TABLE `watermark_performance_metrics` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `metric_type` varchar(100) NOT NULL,
    `metric_name` varchar(255) NOT NULL,
    `metric_value` decimal(15,4) NOT NULL,
    `metric_unit` varchar(50) NULL,
    `threshold_warning` decimal(15,4) NULL,
    `threshold_critical` decimal(15,4) NULL,
    `status` enum('normal', 'warning', 'critical') NOT NULL DEFAULT 'normal',
    `metadata` json NULL,
    `recorded_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_metric_type` (`metric_type`),
    KEY `idx_metric_name` (`metric_name`),
    KEY `idx_status` (`status`),
    KEY `idx_recorded_at` (`recorded_at`),
    KEY `idx_metric_type_name` (`metric_type`, `metric_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Alerts Management Table
CREATE TABLE `watermark_alerts` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `alert_type` varchar(100) NOT NULL,
    `severity` enum('low', 'medium', 'high', 'critical') NOT NULL,
    `message` text NOT NULL,
    `metadata` json NULL,
    `status` enum('active', 'acknowledged', 'resolved', 'suppressed') NOT NULL DEFAULT 'active',
    `acknowledged_by` bigint(20) UNSIGNED NULL,
    `acknowledged_at` timestamp NULL DEFAULT NULL,
    `acknowledgment_notes` text NULL,
    `resolved_by` bigint(20) UNSIGNED NULL,
    `resolved_at` timestamp NULL DEFAULT NULL,
    `resolution_notes` text NULL,
    `escalation_count` int(11) NOT NULL DEFAULT 0,
    `last_escalated_at` timestamp NULL DEFAULT NULL,
    `suppressed_until` timestamp NULL DEFAULT NULL,
    `notification_sent` tinyint(1) NOT NULL DEFAULT 0,
    `notification_channels` 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_alert_type` (`alert_type`),
    KEY `idx_severity` (`severity`),
    KEY `idx_status` (`status`),
    KEY `idx_created_at` (`created_at`),
    KEY `idx_acknowledged_by` (`acknowledged_by`),
    KEY `idx_resolved_by` (`resolved_by`),
    KEY `idx_alert_type_status` (`alert_type`, `status`),
    KEY `idx_severity_status` (`severity`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Alert Notifications Log Table
CREATE TABLE `watermark_alert_notifications` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `alert_id` bigint(20) UNSIGNED NOT NULL,
    `notification_channel` varchar(100) NOT NULL,
    `recipient` varchar(255) NOT NULL,
    `status` enum('pending', 'sent', 'failed', 'delivered') NOT NULL DEFAULT 'pending',
    `sent_at` timestamp NULL DEFAULT NULL,
    `delivered_at` timestamp NULL DEFAULT NULL,
    `failure_reason` text NULL,
    `retry_count` int(11) NOT NULL DEFAULT 0,
    `max_retries` int(11) NOT NULL DEFAULT 3,
    `next_retry_at` timestamp NULL DEFAULT NULL,
    `notification_data` 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_alert_id` (`alert_id`),
    KEY `idx_notification_channel` (`notification_channel`),
    KEY `idx_status` (`status`),
    KEY `idx_sent_at` (`sent_at`),
    KEY `idx_next_retry_at` (`next_retry_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Security Analysis Logs Table
CREATE TABLE `watermark_security_analysis_logs` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `analysis_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `threats_analyzed` int(11) NOT NULL DEFAULT 0,
    `incidents_detected` int(11) NOT NULL DEFAULT 0,
    `patterns_matched` int(11) NOT NULL DEFAULT 0,
    `analysis_duration_ms` int(11) NULL,
    `analysis_results` json NULL,
    `recommendations` json NULL,
    `actions_taken` json NULL,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_analysis_timestamp` (`analysis_timestamp`),
    KEY `idx_threats_analyzed` (`threats_analyzed`),
    KEY `idx_incidents_detected` (`incidents_detected`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- System Resource Monitoring Table
CREATE TABLE `watermark_resource_monitoring` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `resource_type` varchar(100) NOT NULL,
    `resource_name` varchar(255) NOT NULL,
    `current_usage` decimal(15,4) NOT NULL,
    `maximum_capacity` decimal(15,4) NULL,
    `usage_percentage` decimal(5,2) NULL,
    `status` enum('normal', 'warning', 'critical') NOT NULL DEFAULT 'normal',
    `threshold_warning` decimal(5,2) NULL DEFAULT 80.00,
    `threshold_critical` decimal(5,2) NULL DEFAULT 95.00,
    `additional_metrics` json NULL,
    `monitored_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_resource_type` (`resource_type`),
    KEY `idx_resource_name` (`resource_name`),
    KEY `idx_status` (`status`),
    KEY `idx_monitored_at` (`monitored_at`),
    KEY `idx_resource_type_name` (`resource_type`, `resource_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Service Health Monitoring Table
CREATE TABLE `watermark_service_health` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `service_name` varchar(100) NOT NULL,
    `service_type` varchar(100) NOT NULL,
    `status` enum('healthy', 'warning', 'critical', 'error', 'disabled') NOT NULL,
    `response_time_ms` decimal(10,2) NULL,
    `availability_percentage` decimal(5,2) NULL DEFAULT 100.00,
    `last_check_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_success_at` timestamp NULL DEFAULT NULL,
    `last_failure_at` timestamp NULL DEFAULT NULL,
    `failure_count` int(11) NOT NULL DEFAULT 0,
    `consecutive_failures` int(11) NOT NULL DEFAULT 0,
    `health_details` json NULL,
    `configuration` json NULL,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `service_name` (`service_name`),
    KEY `idx_service_type` (`service_type`),
    KEY `idx_status` (`status`),
    KEY `idx_last_check_at` (`last_check_at`),
    KEY `idx_availability_percentage` (`availability_percentage`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Alert Rules Configuration Table
CREATE TABLE `watermark_alert_rules` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `rule_name` varchar(255) NOT NULL,
    `rule_type` varchar(100) NOT NULL,
    `metric_name` varchar(255) NOT NULL,
    `condition_operator` enum('>', '<', '>=', '<=', '=', '!=') NOT NULL,
    `threshold_value` decimal(15,4) NOT NULL,
    `severity` enum('low', 'medium', 'high', 'critical') NOT NULL,
    `evaluation_window_seconds` int(11) NOT NULL DEFAULT 300,
    `cooldown_seconds` int(11) NOT NULL DEFAULT 300,
    `is_enabled` tinyint(1) NOT NULL DEFAULT 1,
    `notification_channels` json NOT NULL,
    `rule_description` text NULL,
    `created_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 `rule_name` (`rule_name`),
    KEY `idx_rule_type` (`rule_type`),
    KEY `idx_metric_name` (`metric_name`),
    KEY `idx_is_enabled` (`is_enabled`),
    KEY `idx_created_by` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Monitoring Dashboard Configuration Table
CREATE TABLE `watermark_monitoring_dashboards` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `dashboard_name` varchar(255) NOT NULL,
    `dashboard_type` varchar(100) NOT NULL,
    `user_id` bigint(20) UNSIGNED NULL,
    `is_public` tinyint(1) NOT NULL DEFAULT 0,
    `is_default` tinyint(1) NOT NULL DEFAULT 0,
    `layout_config` json NOT NULL,
    `widget_config` json NOT NULL,
    `refresh_interval_seconds` int(11) NOT NULL DEFAULT 60,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_dashboard_type` (`dashboard_type`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_is_public` (`is_public`),
    KEY `idx_is_default` (`is_default`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default service health records
INSERT INTO `watermark_service_health` (`service_name`, `service_type`, `status`, `configuration`) VALUES
('ffmpeg', 'local_processor', 'healthy', '{"path": "/usr/bin/ffmpeg", "timeout": 300}'),
('coconut', 'cloud_processor', 'disabled', '{"api_key": null, "webhook_url": null}'),
('aws_mediaconvert', 'cloud_processor', 'disabled', '{"region": null, "role_arn": null}'),
('azure_media', 'cloud_processor', 'disabled', '{"account_name": null, "account_key": null}'),
('google_cloud', 'cloud_processor', 'disabled', '{"project_id": null, "credentials_path": null}'),
('database', 'infrastructure', 'healthy', '{"connection_timeout": 30, "max_connections": 100}'),
('redis', 'infrastructure', 'healthy', '{"connection_timeout": 5, "max_memory": "256mb"}'),
('storage', 'infrastructure', 'healthy', '{"disk_warning_threshold": 85, "disk_critical_threshold": 95}');

-- Insert default alert rules
INSERT INTO `watermark_alert_rules` (`rule_name`, `rule_type`, `metric_name`, `condition_operator`, `threshold_value`, `severity`, `evaluation_window_seconds`, `cooldown_seconds`, `notification_channels`, `rule_description`) VALUES
('High Processing Time', 'performance', 'avg_processing_time_ms', '>', 30000, 'warning', 300, 300, '["email", "log"]', 'Alert when average processing time exceeds 30 seconds'),
('Critical Processing Time', 'performance', 'avg_processing_time_ms', '>', 60000, 'critical', 300, 300, '["email", "log", "sms"]', 'Alert when average processing time exceeds 60 seconds'),
('High Queue Size', 'performance', 'queue_size', '>', 100, 'warning', 180, 180, '["email", "log"]', 'Alert when queue size exceeds 100 jobs'),
('Critical Queue Size', 'performance', 'queue_size', '>', 500, 'critical', 180, 180, '["email", "log", "sms"]', 'Alert when queue size exceeds 500 jobs'),
('High Error Rate', 'performance', 'error_rate_percent', '>', 5, 'warning', 600, 300, '["email", "log"]', 'Alert when error rate exceeds 5%'),
('Critical Error Rate', 'performance', 'error_rate_percent', '>', 15, 'critical', 600, 300, '["email", "log", "sms"]', 'Alert when error rate exceeds 15%'),
('High Memory Usage', 'system', 'memory_usage_percent', '>', 80, 'warning', 300, 600, '["email", "log"]', 'Alert when memory usage exceeds 80%'),
('Critical Memory Usage', 'system', 'memory_usage_percent', '>', 95, 'critical', 300, 600, '["email", "log", "sms"]', 'Alert when memory usage exceeds 95%'),
('High Disk Usage', 'system', 'disk_usage_percent', '>', 85, 'warning', 600, 1800, '["email", "log"]', 'Alert when disk usage exceeds 85%'),
('Critical Disk Usage', 'system', 'disk_usage_percent', '>', 95, 'critical', 600, 1800, '["email", "log", "sms"]', 'Alert when disk usage exceeds 95%'),
('Security Incidents', 'security', 'active_incidents_count', '>', 0, 'high', 60, 60, '["email", "log", "sms"]', 'Alert when security incidents are detected'),
('Failed Uploads Spike', 'security', 'failed_uploads_per_hour', '>', 50, 'medium', 300, 300, '["email", "log"]', 'Alert when failed uploads exceed 50 per hour');

-- Insert default dashboard configuration
INSERT INTO `watermark_monitoring_dashboards` (`dashboard_name`, `dashboard_type`, `is_public`, `is_default`, `layout_config`, `widget_config`, `refresh_interval_seconds`) VALUES
('System Overview', 'system', 1, 1, 
'{"layout": "grid", "columns": 3, "rows": 4}', 
'{"widgets": [
    {"type": "system_health", "position": {"row": 1, "col": 1}, "size": {"width": 1, "height": 1}},
    {"type": "performance_metrics", "position": {"row": 1, "col": 2}, "size": {"width": 2, "height": 1}},
    {"type": "service_status", "position": {"row": 2, "col": 1}, "size": {"width": 1, "height": 1}},
    {"type": "alert_summary", "position": {"row": 2, "col": 2}, "size": {"width": 2, "height": 1}},
    {"type": "processing_queue", "position": {"row": 3, "col": 1}, "size": {"width": 1, "height": 1}},
    {"type": "error_rate_chart", "position": {"row": 3, "col": 2}, "size": {"width": 2, "height": 1}},
    {"type": "resource_usage", "position": {"row": 4, "col": 1}, "size": {"width": 3, "height": 1}}
]}', 
60),
('Security Dashboard', 'security', 1, 0, 
'{"layout": "grid", "columns": 2, "rows": 3}', 
'{"widgets": [
    {"type": "security_incidents", "position": {"row": 1, "col": 1}, "size": {"width": 1, "height": 1}},
    {"type": "threat_analysis", "position": {"row": 1, "col": 2}, "size": {"width": 1, "height": 1}},
    {"type": "failed_uploads", "position": {"row": 2, "col": 1}, "size": {"width": 1, "height": 1}},
    {"type": "blocked_ips", "position": {"row": 2, "col": 2}, "size": {"width": 1, "height": 1}},
    {"type": "security_timeline", "position": {"row": 3, "col": 1}, "size": {"width": 2, "height": 1}}
]}', 
30);

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

-- Create performance optimization indexes
CREATE INDEX `idx_health_logs_status_date` ON `watermark_system_health_logs` (`overall_status`, `created_at`);
CREATE INDEX `idx_performance_metrics_type_date` ON `watermark_performance_metrics` (`metric_type`, `recorded_at`);
CREATE INDEX `idx_alerts_type_severity_status` ON `watermark_alerts` (`alert_type`, `severity`, `status`);
CREATE INDEX `idx_alert_notifications_status_retry` ON `watermark_alert_notifications` (`status`, `next_retry_at`);
CREATE INDEX `idx_security_analysis_timestamp` ON `watermark_security_analysis_logs` (`analysis_timestamp`);
CREATE INDEX `idx_resource_monitoring_type_status` ON `watermark_resource_monitoring` (`resource_type`, `status`);
CREATE INDEX `idx_service_health_type_status` ON `watermark_service_health` (`service_type`, `status`);

-- Add monitoring columns to existing watermark tables if they don't exist
ALTER TABLE `watermark_processing_jobs` 
ADD COLUMN `monitoring_enabled` tinyint(1) NOT NULL DEFAULT 1,
ADD COLUMN `performance_metrics` json NULL,
ADD COLUMN `resource_usage` json NULL,
ADD COLUMN `health_check_passed` tinyint(1) NOT NULL DEFAULT 1;

-- Create monitoring index on processing jobs
CREATE INDEX `idx_watermark_jobs_monitoring` ON `watermark_processing_jobs` (`monitoring_enabled`, `health_check_passed`, `created_at`);

-- Create view for real-time monitoring dashboard
CREATE VIEW `watermark_monitoring_dashboard_view` AS
SELECT 
    'system_health' as widget_type,
    JSON_OBJECT(
        'overall_status', whl.overall_status,
        'last_check', whl.created_at,
        'alerts_count', whl.alerts_count,
        'response_time', whl.response_time_ms
    ) as widget_data
FROM `watermark_system_health_logs` whl
WHERE whl.created_at = (SELECT MAX(created_at) FROM `watermark_system_health_logs`)

UNION ALL

SELECT 
    'service_status' as widget_type,
    JSON_OBJECT(
        'services', JSON_ARRAYAGG(
            JSON_OBJECT(
                'name', wsh.service_name,
                'type', wsh.service_type,
                'status', wsh.status,
                'response_time', wsh.response_time_ms,
                'availability', wsh.availability_percentage
            )
        )
    ) as widget_data
FROM `watermark_service_health` wsh

UNION ALL

SELECT 
    'active_alerts' as widget_type,
    JSON_OBJECT(
        'total_alerts', COUNT(*),
        'critical_alerts', SUM(CASE WHEN severity = 'critical' THEN 1 ELSE 0 END),
        'high_alerts', SUM(CASE WHEN severity = 'high' THEN 1 ELSE 0 END),
        'medium_alerts', SUM(CASE WHEN severity = 'medium' THEN 1 ELSE 0 END),
        'low_alerts', SUM(CASE WHEN severity = 'low' THEN 1 ELSE 0 END)
    ) as widget_data
FROM `watermark_alerts` wa
WHERE wa.status = 'active';