-- Processing Pipeline Controls Table
-- Manages video processing pipeline configuration, budget tracking, and maintenance

CREATE TABLE IF NOT EXISTS `processing_pipeline_controls` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `processor_type` enum('ffmpeg','coconut','aws_mediaconvert') COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_enabled` tinyint(1) NOT NULL DEFAULT '1',
  `priority_order` int(11) NOT NULL DEFAULT '1',
  `max_concurrent_jobs` int(11) unsigned NOT NULL DEFAULT '5',
  `max_queue_size` int(11) unsigned NOT NULL DEFAULT '100',
  `timeout_seconds` int(11) unsigned NOT NULL DEFAULT '3600',
  `retry_attempts` int(11) unsigned NOT NULL DEFAULT '3',
  `quality_settings` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `processing_options` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `cost_per_minute` decimal(8,4) DEFAULT NULL,
  `monthly_budget` decimal(10,2) DEFAULT NULL,
  `current_month_usage` decimal(10,2) NOT NULL DEFAULT '0.00',
  `is_maintenance_mode` tinyint(1) NOT NULL DEFAULT '0',
  `maintenance_message` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_modified_by` bigint(20) unsigned 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_is_enabled` (`is_enabled`),
  KEY `idx_priority_order` (`priority_order`),
  KEY `idx_is_maintenance_mode` (`is_maintenance_mode`),
  KEY `idx_last_modified_by` (`last_modified_by`),
  CONSTRAINT `processing_pipeline_controls_chk_1` CHECK (json_valid(`quality_settings`)),
  CONSTRAINT `processing_pipeline_controls_chk_2` CHECK (json_valid(`processing_options`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Processing pipeline configuration and budget controls';

-- Insert default processing pipeline controls if they don't exist
INSERT IGNORE INTO `processing_pipeline_controls` (
  `processor_type`, 
  `is_enabled`, 
  `priority_order`, 
  `max_concurrent_jobs`, 
  `max_queue_size`, 
  `timeout_seconds`, 
  `retry_attempts`,
  `quality_settings`,
  `processing_options`,
  `cost_per_minute`,
  `monthly_budget`,
  `current_month_usage`,
  `is_maintenance_mode`
) VALUES 
(
  'ffmpeg', 
  1, 
  1, 
  5, 
  100, 
  3600, 
  3,
  '{"resolution": "1080p", "bitrate": "5000k", "codec": "h264", "audio_bitrate": "128k"}',
  '{"watermark": true, "thumbnail": true, "formats": ["mp4", "webm"]}',
  NULL,
  NULL,
  0.00,
  0
),
(
  'coconut', 
  1, 
  2, 
  3, 
  50, 
  7200, 
  2,
  '{"resolution": "1080p", "bitrate": "4000k", "codec": "h264", "audio_bitrate": "128k"}',
  '{"watermark": true, "thumbnail": true, "formats": ["mp4", "hls"]}',
  0.0050,
  100.00,
  0.00,
  0
),
(
  'aws_mediaconvert', 
  1, 
  3, 
  2, 
  25, 
  10800, 
  1,
  '{"resolution": "4K", "bitrate": "15000k", "codec": "h265", "audio_bitrate": "192k"}',
  '{"watermark": true, "thumbnail": true, "formats": ["mp4", "hls", "dash"]}',
  0.0240,
  500.00,
  0.00,
  0
);

-- Create indexes for performance optimization
CREATE INDEX IF NOT EXISTS `idx_enabled_priority` ON `processing_pipeline_controls` (`is_enabled`, `priority_order`);
CREATE INDEX IF NOT EXISTS `idx_maintenance_enabled` ON `processing_pipeline_controls` (`is_maintenance_mode`, `is_enabled`);
CREATE INDEX IF NOT EXISTS `idx_budget_usage` ON `processing_pipeline_controls` (`monthly_budget`, `current_month_usage`);

-- Create view for active processors
CREATE OR REPLACE VIEW `active_processors_view` AS
SELECT 
    `processor_type`,
    `priority_order`,
    `max_concurrent_jobs`,
    `max_queue_size`,
    `timeout_seconds`,
    `retry_attempts`,
    `quality_settings`,
    `processing_options`,
    `cost_per_minute`,
    `monthly_budget`,
    `current_month_usage`,
    CASE 
        WHEN `monthly_budget` IS NULL OR `monthly_budget` = 0 THEN 0
        ELSE (`current_month_usage` / `monthly_budget`) * 100
    END as `budget_usage_percentage`,
    CASE 
        WHEN `monthly_budget` IS NOT NULL AND `current_month_usage` > `monthly_budget` THEN 1
        ELSE 0
    END as `is_over_budget`,
    CASE 
        WHEN `is_maintenance_mode` = 1 THEN 'maintenance'
        WHEN `is_enabled` = 0 THEN 'disabled'
        WHEN `monthly_budget` IS NOT NULL AND `current_month_usage` > `monthly_budget` THEN 'over_budget'
        ELSE 'active'
    END as `status`,
    `created_at`,
    `updated_at`
FROM `processing_pipeline_controls`
WHERE `is_enabled` = 1 
  AND `is_maintenance_mode` = 0
ORDER BY `priority_order`;

-- Create stored procedure for getting next available processor
DELIMITER $$

CREATE PROCEDURE IF NOT EXISTS `GetNextAvailableProcessor`(
    IN preferred_type VARCHAR(50),
    OUT processor_type VARCHAR(50),
    OUT processor_config JSON
)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE current_type VARCHAR(50);
    DECLARE current_config JSON;
    
    -- Cursor for processors in priority order
    DECLARE processor_cursor CURSOR FOR 
        SELECT 
            `processor_type`,
            JSON_OBJECT(
                'max_concurrent_jobs', `max_concurrent_jobs`,
                'timeout_seconds', `timeout_seconds`,
                'retry_attempts', `retry_attempts`,
                'quality_settings', `quality_settings`,
                'processing_options', `processing_options`
            ) as config
        FROM `processing_pipeline_controls`
        WHERE `is_enabled` = 1 
          AND `is_maintenance_mode` = 0
          AND (
              `monthly_budget` IS NULL 
              OR `current_month_usage` <= `monthly_budget`
          )
        ORDER BY 
            CASE WHEN `processor_type` = preferred_type THEN 0 ELSE 1 END,
            `priority_order`;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    SET processor_type = NULL;
    SET processor_config = NULL;
    
    OPEN processor_cursor;
    
    read_loop: LOOP
        FETCH processor_cursor INTO current_type, current_config;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        SET processor_type = current_type;
        SET processor_config = current_config;
        LEAVE read_loop;
    END LOOP;
    
    CLOSE processor_cursor;
END$$

DELIMITER ;

-- Create stored procedure for recording usage
DELIMITER $$

CREATE PROCEDURE IF NOT EXISTS `RecordProcessingUsage`(
    IN proc_type VARCHAR(50),
    IN processing_minutes DECIMAL(10,4),
    OUT success BOOLEAN,
    OUT new_usage DECIMAL(10,2)
)
BEGIN
    DECLARE current_cost_per_minute DECIMAL(8,4);
    DECLARE calculated_cost DECIMAL(10,2);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET success = FALSE;
        ROLLBACK;
    END;
    
    START TRANSACTION;
    
    -- Get cost per minute
    SELECT `cost_per_minute` INTO current_cost_per_minute
    FROM `processing_pipeline_controls`
    WHERE `processor_type` = proc_type;
    
    -- Calculate cost
    IF current_cost_per_minute IS NOT NULL AND current_cost_per_minute > 0 THEN
        SET calculated_cost = current_cost_per_minute * processing_minutes;
    ELSE
        SET calculated_cost = 0;
    END IF;
    
    -- Update usage
    UPDATE `processing_pipeline_controls`
    SET `current_month_usage` = `current_month_usage` + calculated_cost,
        `updated_at` = CURRENT_TIMESTAMP
    WHERE `processor_type` = proc_type;
    
    -- Get new usage amount
    SELECT `current_month_usage` INTO new_usage
    FROM `processing_pipeline_controls`
    WHERE `processor_type` = proc_type;
    
    SET success = TRUE;
    COMMIT;
END$$

DELIMITER ;

-- Create trigger to log configuration changes
DELIMITER $$

CREATE TRIGGER IF NOT EXISTS `processing_pipeline_controls_audit` 
AFTER UPDATE ON `processing_pipeline_controls`
FOR EACH ROW
BEGIN
    INSERT INTO `audit_logs` (
        `table_name`,
        `record_id`,
        `action`,
        `old_values`,
        `new_values`,
        `user_id`,
        `created_at`
    ) VALUES (
        'processing_pipeline_controls',
        NEW.id,
        'UPDATE',
        JSON_OBJECT(
            'is_enabled', OLD.is_enabled,
            'priority_order', OLD.priority_order,
            'max_concurrent_jobs', OLD.max_concurrent_jobs,
            'is_maintenance_mode', OLD.is_maintenance_mode,
            'current_month_usage', OLD.current_month_usage
        ),
        JSON_OBJECT(
            'is_enabled', NEW.is_enabled,
            'priority_order', NEW.priority_order,
            'max_concurrent_jobs', NEW.max_concurrent_jobs,
            'is_maintenance_mode', NEW.is_maintenance_mode,
            'current_month_usage', NEW.current_month_usage
        ),
        NEW.last_modified_by,
        CURRENT_TIMESTAMP
    );
END$$

DELIMITER ;