-- Processing Pipeline Health Monitoring Table
-- Tracks real-time health, performance metrics, and system status for each processor

CREATE TABLE IF NOT EXISTS `processing_pipeline_health` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `processor_type` enum('ffmpeg','coconut','aws_mediaconvert') COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` enum('online','offline','degraded','maintenance','idle') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'offline',
  `queue_length` int(11) unsigned NOT NULL DEFAULT '0',
  `active_jobs` int(11) unsigned NOT NULL DEFAULT '0',
  `completed_jobs_last_hour` int(11) unsigned NOT NULL DEFAULT '0',
  `failed_jobs_last_hour` int(11) unsigned NOT NULL DEFAULT '0',
  `average_processing_time_seconds` int(11) unsigned NOT NULL DEFAULT '0',
  `success_rate_percentage` decimal(5,2) NOT NULL DEFAULT '100.00',
  `cpu_usage_percentage` decimal(5,2) NOT NULL DEFAULT '0.00',
  `memory_usage_percentage` decimal(5,2) NOT NULL DEFAULT '0.00',
  `disk_usage_percentage` decimal(5,2) NOT NULL DEFAULT '0.00',
  `network_usage_percentage` decimal(5,2) NOT NULL DEFAULT '0.00',
  `performance_score` decimal(5,2) NOT NULL DEFAULT '100.00',
  `throughput_per_hour` int(11) unsigned NOT NULL DEFAULT '0',
  `cost_per_job_average` decimal(8,4) NOT NULL DEFAULT '0.0000',
  `queue_wait_time_average` int(11) unsigned NOT NULL DEFAULT '0',
  `failover_count_24h` int(11) unsigned NOT NULL DEFAULT '0',
  `recovery_count_24h` int(11) unsigned NOT NULL DEFAULT '0',
  `last_heartbeat` timestamp NULL DEFAULT NULL,
  `error_message` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `resource_efficiency_score` decimal(5,2) NOT NULL DEFAULT '100.00',
  `cost_efficiency_score` decimal(5,2) NOT NULL DEFAULT '100.00',
  `stability_score` decimal(5,2) NOT NULL DEFAULT '100.00',
  `uptime_percentage` decimal(5,2) NOT NULL DEFAULT '100.00',
  `mtbf_minutes` int(11) unsigned NOT NULL DEFAULT '10080',
  `peak_hour_performance` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `error_patterns` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `weekly_trends` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `job_size_distribution` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `health_trends_24h` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_processor_type` (`processor_type`),
  KEY `idx_processor_type` (`processor_type`),
  KEY `idx_status` (`status`),
  KEY `idx_performance_score` (`performance_score`),
  KEY `idx_last_heartbeat` (`last_heartbeat`),
  KEY `idx_success_rate` (`success_rate_percentage`),
  KEY `idx_queue_length` (`queue_length`),
  KEY `idx_active_jobs` (`active_jobs`),
  KEY `idx_throughput` (`throughput_per_hour`),
  KEY `idx_failover_count` (`failover_count_24h`),
  KEY `idx_uptime` (`uptime_percentage`),
  CONSTRAINT `processing_pipeline_health_chk_1` CHECK (json_valid(`peak_hour_performance`)),
  CONSTRAINT `processing_pipeline_health_chk_2` CHECK (json_valid(`error_patterns`)),
  CONSTRAINT `processing_pipeline_health_chk_3` CHECK (json_valid(`weekly_trends`)),
  CONSTRAINT `processing_pipeline_health_chk_4` CHECK (json_valid(`job_size_distribution`)),
  CONSTRAINT `processing_pipeline_health_chk_5` CHECK (json_valid(`health_trends_24h`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Real-time health monitoring for processing pipeline processors';

-- Insert default health records for each processor type
INSERT IGNORE INTO `processing_pipeline_health` (
  `processor_type`, 
  `status`, 
  `queue_length`, 
  `active_jobs`, 
  `completed_jobs_last_hour`, 
  `failed_jobs_last_hour`,
  `average_processing_time_seconds`,
  `success_rate_percentage`,
  `cpu_usage_percentage`,
  `memory_usage_percentage`,
  `disk_usage_percentage`,
  `network_usage_percentage`,
  `performance_score`,
  `throughput_per_hour`,
  `cost_per_job_average`,
  `queue_wait_time_average`,
  `failover_count_24h`,
  `recovery_count_24h`,
  `last_heartbeat`,
  `resource_efficiency_score`,
  `cost_efficiency_score`,
  `stability_score`,
  `uptime_percentage`,
  `mtbf_minutes`
) VALUES 
(
  'ffmpeg', 
  'online', 
  0, 
  0, 
  0, 
  0,
  180,
  100.00,
  25.50,
  35.20,
  15.80,
  10.30,
  95.75,
  12,
  0.0000,
  45,
  0,
  0,
  CURRENT_TIMESTAMP,
  88.50,
  100.00,
  98.20,
  99.85,
  8640
),
(
  'coconut', 
  'online', 
  0, 
  0, 
  0, 
  0,
  120,
  99.50,
  0.00,
  0.00,
  0.00,
  85.60,
  18,
  0.0050,
  30,
  0,
  0,
  CURRENT_TIMESTAMP,
  92.30,
  85.40,
  99.10,
  99.95,
  10080
),
(
  'aws_mediaconvert', 
  'online', 
  0, 
  0, 
  0, 
  0,
  90,
  99.80,
  0.00,
  0.00,
  0.00,
  45.20,
  25,
  0.0240,
  15,
  0,
  0,
  CURRENT_TIMESTAMP,
  95.80,
  78.60,
  99.90,
  99.99,
  14400
);

-- Create performance optimization indexes
CREATE INDEX IF NOT EXISTS `idx_health_composite` ON `processing_pipeline_health` (`status`, `performance_score`, `last_heartbeat`);
CREATE INDEX IF NOT EXISTS `idx_efficiency_composite` ON `processing_pipeline_health` (`resource_efficiency_score`, `cost_efficiency_score`, `stability_score`);
CREATE INDEX IF NOT EXISTS `idx_monitoring_composite` ON `processing_pipeline_health` (`queue_length`, `active_jobs`, `throughput_per_hour`);
CREATE INDEX IF NOT EXISTS `idx_reliability_composite` ON `processing_pipeline_health` (`uptime_percentage`, `failover_count_24h`, `mtbf_minutes`);

-- Create view for healthy processors
CREATE OR REPLACE VIEW `healthy_processors_view` AS
SELECT 
    `processor_type`,
    `status`,
    `performance_score`,
    `success_rate_percentage`,
    `queue_length`,
    `active_jobs`,
    `throughput_per_hour`,
    `resource_efficiency_score`,
    `cost_efficiency_score`,
    `stability_score`,
    `uptime_percentage`,
    `last_heartbeat`,
    CASE 
        WHEN `status` = 'online' AND `performance_score` >= 75 AND `success_rate_percentage` >= 95 THEN 'healthy'
        WHEN `status` = 'online' AND `performance_score` >= 50 AND `success_rate_percentage` >= 90 THEN 'degraded'
        ELSE 'unhealthy'
    END as `health_status`,
    CASE 
        WHEN `last_heartbeat` IS NULL OR `last_heartbeat` < DATE_SUB(NOW(), INTERVAL 5 MINUTE) THEN 1
        ELSE 0
    END as `is_stale`,
    TIMESTAMPDIFF(MINUTE, `last_heartbeat`, NOW()) as `minutes_since_heartbeat`,
    `created_at`,
    `updated_at`
FROM `processing_pipeline_health`
ORDER BY `performance_score` DESC, `uptime_percentage` DESC;

-- Create view for system health overview
CREATE OR REPLACE VIEW `system_health_overview` AS
SELECT 
    COUNT(*) as `total_processors`,
    SUM(CASE WHEN `status` = 'online' THEN 1 ELSE 0 END) as `online_processors`,
    SUM(CASE WHEN `status` = 'offline' THEN 1 ELSE 0 END) as `offline_processors`,
    SUM(CASE WHEN `status` = 'degraded' THEN 1 ELSE 0 END) as `degraded_processors`,
    SUM(CASE WHEN `status` = 'maintenance' THEN 1 ELSE 0 END) as `maintenance_processors`,
    AVG(`performance_score`) as `average_performance_score`,
    AVG(`success_rate_percentage`) as `average_success_rate`,
    SUM(`queue_length`) as `total_queue_length`,
    SUM(`active_jobs`) as `total_active_jobs`,
    SUM(`throughput_per_hour`) as `total_throughput_per_hour`,
    AVG(`resource_efficiency_score`) as `average_resource_efficiency`,
    AVG(`cost_efficiency_score`) as `average_cost_efficiency`,
    AVG(`stability_score`) as `average_stability_score`,
    AVG(`uptime_percentage`) as `average_uptime`,
    SUM(`failover_count_24h`) as `total_failovers_24h`,
    SUM(`recovery_count_24h`) as `total_recoveries_24h`,
    CASE 
        WHEN AVG(`performance_score`) >= 80 AND SUM(CASE WHEN `status` = 'online' THEN 1 ELSE 0 END) >= COUNT(*) * 0.8 THEN 'healthy'
        WHEN AVG(`performance_score`) >= 60 AND SUM(CASE WHEN `status` = 'online' THEN 1 ELSE 0 END) >= COUNT(*) * 0.6 THEN 'degraded'
        ELSE 'critical'
    END as `system_status`,
    NOW() as `calculated_at`
FROM `processing_pipeline_health`;

-- Create stored procedure for updating processor health with enhanced metrics
DELIMITER $

CREATE PROCEDURE IF NOT EXISTS `UpdateProcessorHealthEnhanced`(
    IN proc_type VARCHAR(50),
    IN new_status VARCHAR(20),
    IN queue_len INT,
    IN active_job_count INT,
    IN completed_last_hour INT,
    IN failed_last_hour INT,
    IN avg_processing_time INT,
    IN success_rate DECIMAL(5,2),
    IN cpu_usage DECIMAL(5,2),
    IN memory_usage DECIMAL(5,2),
    IN disk_usage DECIMAL(5,2),
    IN network_usage DECIMAL(5,2),
    IN throughput INT,
    IN cost_per_job DECIMAL(8,4),
    IN queue_wait_time INT,
    IN error_msg TEXT,
    OUT success BOOLEAN,
    OUT performance_score DECIMAL(5,2)
)
BEGIN
    DECLARE resource_efficiency DECIMAL(5,2);
    DECLARE cost_efficiency DECIMAL(5,2);
    DECLARE stability DECIMAL(5,2);
    DECLARE calculated_performance DECIMAL(5,2);
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET success = FALSE;
        ROLLBACK;
    END;
    
    START TRANSACTION;
    
    -- Calculate efficiency scores
    SET resource_efficiency = GREATEST(0, 100 - ((cpu_usage + memory_usage + disk_usage) / 3 - 60));
    SET cost_efficiency = CASE 
        WHEN cost_per_job = 0 THEN 100
        ELSE GREATEST(0, 100 - (cost_per_job * 1000))
    END;
    SET stability = GREATEST(0, success_rate);
    
    -- Calculate overall performance score
    SET calculated_performance = (
        (success_rate * 0.4) + 
        (resource_efficiency * 0.3) + 
        (cost_efficiency * 0.2) + 
        (stability * 0.1)
    );
    
    -- Update or insert health record
    INSERT INTO `processing_pipeline_health` (
        `processor_type`,
        `status`,
        `queue_length`,
        `active_jobs`,
        `completed_jobs_last_hour`,
        `failed_jobs_last_hour`,
        `average_processing_time_seconds`,
        `success_rate_percentage`,
        `cpu_usage_percentage`,
        `memory_usage_percentage`,
        `disk_usage_percentage`,
        `network_usage_percentage`,
        `performance_score`,
        `throughput_per_hour`,
        `cost_per_job_average`,
        `queue_wait_time_average`,
        `last_heartbeat`,
        `error_message`,
        `resource_efficiency_score`,
        `cost_efficiency_score`,
        `stability_score`,
        `updated_at`
    ) VALUES (
        proc_type,
        new_status,
        queue_len,
        active_job_count,
        completed_last_hour,
        failed_last_hour,
        avg_processing_time,
        success_rate,
        cpu_usage,
        memory_usage,
        disk_usage,
        network_usage,
        calculated_performance,
        throughput,
        cost_per_job,
        queue_wait_time,
        CURRENT_TIMESTAMP,
        error_msg,
        resource_efficiency,
        cost_efficiency,
        stability,
        CURRENT_TIMESTAMP
    ) ON DUPLICATE KEY UPDATE
        `status` = new_status,
        `queue_length` = queue_len,
        `active_jobs` = active_job_count,
        `completed_jobs_last_hour` = completed_last_hour,
        `failed_jobs_last_hour` = failed_last_hour,
        `average_processing_time_seconds` = avg_processing_time,
        `success_rate_percentage` = success_rate,
        `cpu_usage_percentage` = cpu_usage,
        `memory_usage_percentage` = memory_usage,
        `disk_usage_percentage` = disk_usage,
        `network_usage_percentage` = network_usage,
        `performance_score` = calculated_performance,
        `throughput_per_hour` = throughput,
        `cost_per_job_average` = cost_per_job,
        `queue_wait_time_average` = queue_wait_time,
        `last_heartbeat` = CURRENT_TIMESTAMP,
        `error_message` = error_msg,
        `resource_efficiency_score` = resource_efficiency,
        `cost_efficiency_score` = cost_efficiency,
        `stability_score` = stability,
        `updated_at` = CURRENT_TIMESTAMP;
    
    SET performance_score = calculated_performance;
    SET success = TRUE;
    COMMIT;
END$

DELIMITER ;

-- Create stored procedure for recording failover events
DELIMITER $

CREATE PROCEDURE IF NOT EXISTS `RecordFailoverEvent`(
    IN proc_type VARCHAR(50),
    IN failover_reason TEXT,
    OUT success BOOLEAN
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET success = FALSE;
        ROLLBACK;
    END;
    
    START TRANSACTION;
    
    -- Increment failover count and update status
    UPDATE `processing_pipeline_health`
    SET `failover_count_24h` = `failover_count_24h` + 1,
        `status` = 'degraded',
        `error_message` = failover_reason,
        `last_heartbeat` = CURRENT_TIMESTAMP,
        `updated_at` = CURRENT_TIMESTAMP
    WHERE `processor_type` = proc_type;
    
    SET success = TRUE;
    COMMIT;
END$

DELIMITER ;

-- Create stored procedure for recording recovery events
DELIMITER $

CREATE PROCEDURE IF NOT EXISTS `RecordRecoveryEvent`(
    IN proc_type VARCHAR(50),
    OUT success BOOLEAN
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET success = FALSE;
        ROLLBACK;
    END;
    
    START TRANSACTION;
    
    -- Increment recovery count and update status
    UPDATE `processing_pipeline_health`
    SET `recovery_count_24h` = `recovery_count_24h` + 1,
        `status` = 'online',
        `error_message` = NULL,
        `last_heartbeat` = CURRENT_TIMESTAMP,
        `updated_at` = CURRENT_TIMESTAMP
    WHERE `processor_type` = proc_type;
    
    SET success = TRUE;
    COMMIT;
END$

DELIMITER ;

-- Create stored procedure for daily health metrics reset
DELIMITER $

CREATE PROCEDURE IF NOT EXISTS `ResetDailyHealthMetrics`()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
    END;
    
    START TRANSACTION;
    
    -- Reset daily counters
    UPDATE `processing_pipeline_health`
    SET `failover_count_24h` = 0,
        `recovery_count_24h` = 0,
        `completed_jobs_last_hour` = 0,
        `failed_jobs_last_hour` = 0,
        `updated_at` = CURRENT_TIMESTAMP;
    
    COMMIT;
END$

DELIMITER ;

-- Create stored procedure for getting system health alerts
DELIMITER $

CREATE PROCEDURE IF NOT EXISTS `GetSystemHealthAlerts`()
BEGIN
    SELECT 
        `processor_type`,
        CASE 
            WHEN `status` = 'offline' THEN 'critical'
            WHEN `performance_score` < 50 THEN 'critical'
            WHEN `queue_length` > 100 THEN 'warning'
            WHEN `success_rate_percentage` < 95 THEN 'warning'
            WHEN `last_heartbeat` < DATE_SUB(NOW(), INTERVAL 5 MINUTE) THEN 'warning'
            WHEN `failover_count_24h` > 3 THEN 'info'
            ELSE 'none'
        END as `alert_level`,
        CASE 
            WHEN `status` = 'offline' THEN CONCAT('Processor ', `processor_type`, ' is offline')
            WHEN `performance_score` < 50 THEN CONCAT('Processor ', `processor_type`, ' has critical performance issues (Score: ', `performance_score`, '%)')
            WHEN `queue_length` > 100 THEN CONCAT('Processor ', `processor_type`, ' has high queue backlog: ', `queue_length`, ' jobs')
            WHEN `success_rate_percentage` < 95 THEN CONCAT('Processor ', `processor_type`, ' has low success rate: ', `success_rate_percentage`, '%')
            WHEN `last_heartbeat` < DATE_SUB(NOW(), INTERVAL 5 MINUTE) THEN CONCAT('Processor ', `processor_type`, ' hasn\'t reported status recently')
            WHEN `failover_count_24h` > 3 THEN CONCAT('Processor ', `processor_type`, ' has experienced ', `failover_count_24h`, ' failovers in the last 24 hours')
            ELSE 'No alerts'
        END as `alert_message`,
        `last_heartbeat` as `alert_timestamp`
    FROM `processing_pipeline_health`
    WHERE `status` = 'offline' 
       OR `performance_score` < 50 
       OR `queue_length` > 100 
       OR `success_rate_percentage` < 95 
       OR `last_heartbeat` < DATE_SUB(NOW(), INTERVAL 5 MINUTE)
       OR `failover_count_24h` > 3
    ORDER BY 
        CASE 
            WHEN `status` = 'offline' OR `performance_score` < 50 THEN 3
            WHEN `queue_length` > 100 OR `success_rate_percentage` < 95 OR `last_heartbeat` < DATE_SUB(NOW(), INTERVAL 5 MINUTE) THEN 2
            ELSE 1
        END DESC,
        `last_heartbeat` DESC;
END$

DELIMITER ;

-- Create trigger to automatically update uptime percentage
DELIMITER $

CREATE TRIGGER IF NOT EXISTS `update_uptime_percentage` 
BEFORE UPDATE ON `processing_pipeline_health`
FOR EACH ROW
BEGIN
    -- Calculate uptime based on status changes and failover frequency
    IF NEW.status = 'online' AND OLD.status != 'online' THEN
        SET NEW.uptime_percentage = GREATEST(90, NEW.uptime_percentage);
    ELSEIF NEW.status = 'offline' THEN
        SET NEW.uptime_percentage = GREATEST(0, NEW.uptime_percentage - 10);
    END IF;
    
    -- Adjust uptime based on failover frequency
    IF NEW.failover_count_24h > OLD.failover_count_24h THEN
        SET NEW.uptime_percentage = GREATEST(0, NEW.uptime_percentage - 2);
    END IF;
    
    -- Update MTBF (Mean Time Between Failures) in minutes
    IF NEW.failover_count_24h > 0 THEN
        SET NEW.mtbf_minutes = GREATEST(60, 1440 / NEW.failover_count_24h); -- 1440 minutes in a day
    ELSE
        SET NEW.mtbf_minutes = 10080; -- 1 week in minutes
    END IF;
END$

DELIMITER ;

-- Create event scheduler for daily metrics reset (runs at midnight)
-- Note: Requires event_scheduler to be enabled in MySQL configuration
SET GLOBAL event_scheduler = ON;

CREATE EVENT IF NOT EXISTS `daily_health_metrics_reset`
ON SCHEDULE EVERY 1 DAY
STARTS TIMESTAMP(CURDATE() + INTERVAL 1 DAY)
DO
  CALL ResetDailyHealthMetrics();

-- Create event scheduler for stale processor detection (runs every 5 minutes)
CREATE EVENT IF NOT EXISTS `stale_processor_detection`
ON SCHEDULE EVERY 5 MINUTE
DO
  UPDATE `processing_pipeline_health`
  SET `status` = 'offline',
      `error_message` = 'Processor not responding - marked as stale',
      `updated_at` = CURRENT_TIMESTAMP
  WHERE `last_heartbeat` < DATE_SUB(NOW(), INTERVAL 10 MINUTE)
    AND `status` != 'offline'
    AND `status` != 'maintenance';