-- =====================================================
-- COMPREHENSIVE WATERMARK SYSTEM DATABASE TABLES
-- Enterprise-level watermark management with Vue/Inertia integration
-- Pure SQL implementation - NO FOREIGN KEY CONSTRAINTS
-- =====================================================

-- Watermark Settings Table - User watermark configurations
CREATE TABLE IF NOT EXISTS `watermark_settings` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `watermark_type` enum('text','image') NOT NULL DEFAULT 'text',
  `watermark_file` varchar(500) DEFAULT NULL COMMENT 'Path to watermark image file',
  `text_content` varchar(255) DEFAULT NULL COMMENT 'Text watermark content',
  `position` enum('top-left','top-center','top-right','middle-left','middle-center','middle-right','bottom-left','bottom-center','bottom-right') NOT NULL DEFAULT 'bottom-right',
  `opacity` int(11) NOT NULL DEFAULT 70 COMMENT 'Opacity percentage (0-100)',
  `color` varchar(7) NOT NULL DEFAULT '#ffffff' COMMENT 'Text color in hex format',
  `font_family` varchar(100) NOT NULL DEFAULT 'Arial',
  `font_size` int(11) NOT NULL DEFAULT 24 COMMENT 'Font size in pixels',
  `rotation` int(11) NOT NULL DEFAULT 0 COMMENT 'Rotation in degrees (-360 to 360)',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `overrides_site_default` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Override site watermark with custom',
  `processing_service` enum('local','coconut','aws','azure','google') NOT NULL DEFAULT 'local',
  `template_name` varchar(255) DEFAULT NULL COMMENT 'Name if saved as template',
  `is_template` tinyint(1) NOT NULL DEFAULT 0,
  `is_public_template` tinyint(1) NOT NULL DEFAULT 0,
  `usage_count` int(11) NOT NULL DEFAULT 0,
  `last_used_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_user_id` (`user_id`),
  KEY `idx_watermark_type` (`watermark_type`),
  KEY `idx_is_active` (`is_active`),
  KEY `idx_is_template` (`is_template`),
  KEY `idx_is_public_template` (`is_public_template`),
  KEY `idx_usage_count` (`usage_count`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='User watermark settings and configurations';

-- Watermark Templates Table - Reusable watermark configurations
CREATE TABLE IF NOT EXISTS `watermark_templates` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `watermark_type` enum('text','image') NOT NULL DEFAULT 'text',
  `watermark_file` varchar(500) DEFAULT NULL,
  `text_content` varchar(255) DEFAULT NULL,
  `position` enum('top-left','top-center','top-right','middle-left','middle-center','middle-right','bottom-left','bottom-center','bottom-right') NOT NULL DEFAULT 'bottom-right',
  `opacity` int(11) NOT NULL DEFAULT 70,
  `color` varchar(7) NOT NULL DEFAULT '#ffffff',
  `font_family` varchar(100) NOT NULL DEFAULT 'Arial',
  `font_size` int(11) NOT NULL DEFAULT 24,
  `rotation` int(11) NOT NULL DEFAULT 0,
  `processing_service` enum('local','coconut','aws','azure','google') NOT NULL DEFAULT 'local',
  `preview_url` varchar(500) DEFAULT NULL COMMENT 'Generated preview image',
  `is_public` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Available to all users',
  `is_featured` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Featured template',
  `is_default` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Default template for user',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `usage_count` int(11) NOT NULL DEFAULT 0,
  `rating` decimal(3,2) NOT NULL DEFAULT 0.00 COMMENT 'Average rating (0-5)',
  `rating_count` int(11) NOT NULL DEFAULT 0,
  `tags` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'JSON array of tags',
  `category` varchar(100) 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_name` (`name`),
  KEY `idx_watermark_type` (`watermark_type`),
  KEY `idx_is_public` (`is_public`),
  KEY `idx_is_featured` (`is_featured`),
  KEY `idx_is_active` (`is_active`),
  KEY `idx_usage_count` (`usage_count`),
  KEY `idx_rating` (`rating`),
  KEY `idx_category` (`category`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `watermark_templates_chk_1` CHECK (json_valid(`tags`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Reusable watermark templates';

-- Template Ratings Table - User ratings for public templates
CREATE TABLE IF NOT EXISTS `template_ratings` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `template_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `rating` tinyint(1) NOT NULL COMMENT 'Rating from 1-5',
  `comment` text DEFAULT NULL,
  `is_helpful` tinyint(1) DEFAULT NULL COMMENT 'Helpful rating from other users',
  `helpful_count` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_template_rating` (`template_id`, `user_id`),
  KEY `idx_template_id` (`template_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_rating` (`rating`),
  KEY `idx_helpful_count` (`helpful_count`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `template_ratings_chk_1` CHECK ((`rating` >= 1 AND `rating` <= 5))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='User ratings for watermark templates';

-- Watermark Processing Jobs Table - Track processing status
CREATE TABLE IF NOT EXISTS `watermark_processing_jobs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `media_id` bigint(20) unsigned DEFAULT NULL COMMENT 'Reference to media being processed',
  `vault_file_id` bigint(20) unsigned DEFAULT NULL COMMENT 'Reference to vault file',
  `watermark_settings_id` bigint(20) unsigned NOT NULL,
  `processor_type` enum('local','coconut','aws','azure','google') NOT NULL,
  `job_id` varchar(255) DEFAULT NULL COMMENT 'External processor job ID',
  `status` enum('pending','processing','completed','failed','cancelled') NOT NULL DEFAULT 'pending',
  `progress` int(11) NOT NULL DEFAULT 0 COMMENT 'Progress percentage (0-100)',
  `input_file_path` varchar(1000) NOT NULL,
  `output_file_path` varchar(1000) DEFAULT NULL,
  `processing_options` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'JSON processing configuration',
  `error_message` text DEFAULT NULL,
  `processing_time_seconds` int(11) DEFAULT NULL,
  `file_size_before` bigint(20) DEFAULT NULL,
  `file_size_after` bigint(20) DEFAULT NULL,
  `started_at` timestamp NULL DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `failed_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_user_id` (`user_id`),
  KEY `idx_media_id` (`media_id`),
  KEY `idx_vault_file_id` (`vault_file_id`),
  KEY `idx_watermark_settings_id` (`watermark_settings_id`),
  KEY `idx_processor_type` (`processor_type`),
  KEY `idx_status` (`status`),
  KEY `idx_progress` (`progress`),
  KEY `idx_started_at` (`started_at`),
  KEY `idx_completed_at` (`completed_at`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `watermark_processing_jobs_chk_1` CHECK (json_valid(`processing_options`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Watermark processing job tracking';

-- Watermark Analytics Table - Usage and performance analytics
CREATE TABLE IF NOT EXISTS `watermark_analytics` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `date` date NOT NULL,
  `watermark_type` enum('text','image') NOT NULL,
  `processor_used` enum('local','coconut','aws','azure','google') NOT NULL,
  `files_processed` int(11) NOT NULL DEFAULT 0,
  `processing_time_total` int(11) NOT NULL DEFAULT 0 COMMENT 'Total processing time in seconds',
  `processing_time_avg` decimal(8,2) NOT NULL DEFAULT 0.00 COMMENT 'Average processing time',
  `success_count` int(11) NOT NULL DEFAULT 0,
  `failure_count` int(11) NOT NULL DEFAULT 0,
  `success_rate` decimal(5,2) NOT NULL DEFAULT 0.00 COMMENT 'Success rate percentage',
  `data_processed_mb` decimal(12,2) NOT NULL DEFAULT 0.00 COMMENT 'Total data processed in MB',
  `cost_estimate` decimal(10,4) NOT NULL DEFAULT 0.0000 COMMENT 'Estimated processing cost',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_date_type_processor` (`user_id`, `date`, `watermark_type`, `processor_used`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_date` (`date`),
  KEY `idx_watermark_type` (`watermark_type`),
  KEY `idx_processor_used` (`processor_used`),
  KEY `idx_success_rate` (`success_rate`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Watermark processing analytics';

-- Site Watermark Settings Table - Global watermark configuration
CREATE TABLE IF NOT EXISTS `site_watermark_settings` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text NOT NULL,
  `setting_type` enum('string','integer','boolean','json','file') NOT NULL DEFAULT 'string',
  `content_type` enum('video','image','audio','all') NOT NULL DEFAULT 'all',
  `description` text DEFAULT 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`),
  UNIQUE KEY `unique_setting_content_type` (`setting_key`, `content_type`),
  KEY `idx_setting_key` (`setting_key`),
  KEY `idx_content_type` (`content_type`),
  KEY `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Site-wide watermark settings';

-- Watermark Detection Logs Table - Track watermark detection and leaks
CREATE TABLE IF NOT EXISTS `watermark_detection_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `original_file_id` bigint(20) unsigned DEFAULT NULL COMMENT 'Original watermarked file',
  `detected_url` varchar(1000) DEFAULT NULL COMMENT 'URL where content was found',
  `detection_method` enum('manual','automated','dmca','user_report') NOT NULL,
  `confidence_score` decimal(5,2) DEFAULT NULL COMMENT 'Detection confidence (0-100)',
  `watermark_visible` tinyint(1) DEFAULT NULL COMMENT 'Is watermark still visible',
  `watermark_removed` tinyint(1) DEFAULT NULL COMMENT 'Was watermark removed/cropped',
  `file_hash` varchar(64) DEFAULT NULL COMMENT 'Hash of detected file',
  `detection_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'JSON detection metadata',
  `status` enum('pending','confirmed','false_positive','resolved') NOT NULL DEFAULT 'pending',
  `action_taken` enum('none','dmca_sent','legal_notice','content_removed') DEFAULT 'none',
  `notes` text DEFAULT NULL,
  `detected_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `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_user_id` (`user_id`),
  KEY `idx_original_file_id` (`original_file_id`),
  KEY `idx_detection_method` (`detection_method`),
  KEY `idx_confidence_score` (`confidence_score`),
  KEY `idx_status` (`status`),
  KEY `idx_action_taken` (`action_taken`),
  KEY `idx_detected_at` (`detected_at`),
  KEY `idx_file_hash` (`file_hash`),
  CONSTRAINT `watermark_detection_logs_chk_1` CHECK (json_valid(`detection_data`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Watermark detection and leak tracking';

-- =====================================================
-- INSERT DEFAULT DATA
-- =====================================================

-- Insert default site watermark settings
INSERT IGNORE INTO `site_watermark_settings` (`setting_key`, `setting_value`, `setting_type`, `content_type`, `description`) VALUES
('watermark_enabled', 'true', 'boolean', 'all', 'Enable site-wide watermarking'),
('default_text', 'Protected Content', 'string', 'all', 'Default watermark text'),
('default_position', 'bottom-right', 'string', 'all', 'Default watermark position'),
('default_opacity', '70', 'integer', 'all', 'Default watermark opacity (0-100)'),
('default_color', '#ffffff', 'string', 'all', 'Default text color'),
('default_font_family', 'Arial', 'string', 'all', 'Default font family'),
('default_font_size', '24', 'integer', 'all', 'Default font size in pixels'),
('processing_service', 'local', 'string', 'all', 'Default processing service'),
('auto_watermark_uploads', 'true', 'boolean', 'all', 'Automatically watermark new uploads'),
('allow_user_override', 'true', 'boolean', 'all', 'Allow users to override site watermark'),
('max_file_size_mb', '5', 'integer', 'all', 'Maximum watermark file size in MB'),
('supported_formats', '["jpg","jpeg","png","svg"]', 'json', 'image', 'Supported watermark image formats'),
('video_watermark_enabled', 'true', 'boolean', 'video', 'Enable watermarking for videos'),
('image_watermark_enabled', 'true', 'boolean', 'image', 'Enable watermarking for images'),
('audio_watermark_enabled', 'false', 'boolean', 'audio', 'Enable watermarking for audio files');

-- Insert default watermark templates (public templates)
INSERT IGNORE INTO `watermark_templates` (`user_id`, `name`, `description`, `watermark_type`, `text_content`, `position`, `opacity`, `color`, `font_family`, `font_size`, `is_public`, `is_featured`, `category`) VALUES
(1, 'Simple Copyright', 'Basic copyright watermark', 'text', '© Copyright Protected', 'bottom-right', 70, '#ffffff', 'Arial', 18, 1, 1, 'copyright'),
(1, 'Brand Watermark', 'Professional brand watermark', 'text', 'YourBrand.com', 'bottom-center', 60, '#ffffff', 'Arial', 20, 1, 1, 'branding'),
(1, 'Subtle Protection', 'Subtle content protection', 'text', 'Protected Content', 'bottom-left', 50, '#cccccc', 'Arial', 16, 1, 0, 'protection'),
(1, 'Bold Warning', 'Bold piracy warning', 'text', 'UNAUTHORIZED USE PROHIBITED', 'middle-center', 80, '#ff0000', 'Impact', 28, 1, 1, 'warning'),
(1, 'Corner Logo', 'Corner logo placement', 'text', 'LOGO', 'top-right', 75, '#000000', 'Arial', 24, 1, 0, 'logo');

-- =====================================================
-- CREATE INDEXES FOR PERFORMANCE
-- =====================================================

-- Additional performance indexes
ALTER TABLE `watermark_settings` 
ADD INDEX `idx_user_active` (`user_id`, `is_active`),
ADD INDEX `idx_template_public` (`is_template`, `is_public_template`),
ADD INDEX `idx_last_used` (`last_used_at`);

ALTER TABLE `watermark_templates` 
ADD INDEX `idx_public_featured` (`is_public`, `is_featured`),
ADD INDEX `idx_rating_usage` (`rating`, `usage_count`),
ADD INDEX `idx_user_active` (`user_id`, `is_active`);

ALTER TABLE `watermark_processing_jobs` 
ADD INDEX `idx_user_status` (`user_id`, `status`),
ADD INDEX `idx_processor_status` (`processor_type`, `status`),
ADD INDEX `idx_processing_time` (`processing_time_seconds`);

ALTER TABLE `watermark_analytics` 
ADD INDEX `idx_user_date_range` (`user_id`, `date`),
ADD INDEX `idx_processor_performance` (`processor_used`, `success_rate`),
ADD INDEX `idx_monthly_stats` (`date`, `processor_used`);

-- =====================================================
-- CREATE VIEWS FOR COMMON QUERIES
-- =====================================================

-- User watermark summary view
CREATE OR REPLACE VIEW `user_watermark_summary` AS
SELECT 
    u.id as user_id,
    u.username,
    COUNT(DISTINCT ws.id) as watermark_configs,
    COUNT(DISTINCT wt.id) as templates_created,
    COUNT(DISTINCT wt2.id) as public_templates,
    COALESCE(SUM(wt.usage_count), 0) as total_template_usage,
    COALESCE(AVG(wt.rating), 0) as avg_template_rating,
    MAX(ws.last_used_at) as last_watermark_used
FROM users u
LEFT JOIN watermark_settings ws ON u.id = ws.user_id AND ws.is_active = 1
LEFT JOIN watermark_templates wt ON u.id = wt.user_id AND wt.is_active = 1
LEFT JOIN watermark_templates wt2 ON u.id = wt2.user_id AND wt2.is_public = 1 AND wt2.is_active = 1
GROUP BY u.id, u.username;

-- Popular templates view
CREATE OR REPLACE VIEW `popular_watermark_templates` AS
SELECT 
    wt.*,
    u.username as creator_name,
    COALESCE(wt.rating, 0) as template_rating,
    COALESCE(wt.usage_count, 0) as times_used
FROM watermark_templates wt
JOIN users u ON wt.user_id = u.id
WHERE wt.is_public = 1 AND wt.is_active = 1
ORDER BY wt.usage_count DESC, wt.rating DESC;

-- Processing performance view
CREATE OR REPLACE VIEW `watermark_processing_performance` AS
SELECT 
    processor_used,
    DATE(created_at) as date,
    COUNT(*) as total_jobs,
    SUM(success_count) as successful_jobs,
    SUM(failure_count) as failed_jobs,
    AVG(success_rate) as avg_success_rate,
    AVG(processing_time_avg) as avg_processing_time,
    SUM(data_processed_mb) as total_data_processed,
    SUM(cost_estimate) as total_estimated_cost
FROM watermark_analytics
GROUP BY processor_used, DATE(created_at)
ORDER BY date DESC, avg_success_rate DESC;

-- =====================================================
-- STORED PROCEDURES FOR COMMON OPERATIONS
-- =====================================================

DELIMITER $$

-- Procedure to apply watermark template to user settings
CREATE PROCEDURE IF NOT EXISTS ApplyWatermarkTemplate(
    IN p_user_id BIGINT UNSIGNED,
    IN p_template_id BIGINT UNSIGNED
)
BEGIN
    DECLARE template_exists INT DEFAULT 0;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- Check if template exists and is accessible
    SELECT COUNT(*) INTO template_exists
    FROM watermark_templates 
    WHERE id = p_template_id 
    AND (user_id = p_user_id OR is_public = 1) 
    AND is_active = 1;
    
    IF template_exists > 0 THEN
        -- Update or create user watermark settings from template
        INSERT INTO watermark_settings (
            user_id, watermark_type, watermark_file, text_content, position,
            opacity, color, font_family, font_size, rotation, processing_service,
            is_active, last_used_at
        )
        SELECT 
            p_user_id, watermark_type, watermark_file, text_content, position,
            opacity, color, font_family, font_size, rotation, processing_service,
            1, NOW()
        FROM watermark_templates 
        WHERE id = p_template_id
        ON DUPLICATE KEY UPDATE
            watermark_type = VALUES(watermark_type),
            watermark_file = VALUES(watermark_file),
            text_content = VALUES(text_content),
            position = VALUES(position),
            opacity = VALUES(opacity),
            color = VALUES(color),
            font_family = VALUES(font_family),
            font_size = VALUES(font_size),
            rotation = VALUES(rotation),
            processing_service = VALUES(processing_service),
            is_active = 1,
            last_used_at = NOW();
        
        -- Increment template usage count
        UPDATE watermark_templates 
        SET usage_count = usage_count + 1,
            updated_at = NOW()
        WHERE id = p_template_id;
    END IF;
    
    COMMIT;
END$$

-- Procedure to update watermark analytics
CREATE PROCEDURE IF NOT EXISTS UpdateWatermarkAnalytics(
    IN p_user_id BIGINT UNSIGNED,
    IN p_watermark_type ENUM('text','image'),
    IN p_processor_used ENUM('local','coconut','aws','azure','google'),
    IN p_processing_time INT,
    IN p_success BOOLEAN,
    IN p_data_size_mb DECIMAL(12,2),
    IN p_cost_estimate DECIMAL(10,4)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    INSERT INTO watermark_analytics (
        user_id, date, watermark_type, processor_used,
        files_processed, processing_time_total, success_count, failure_count,
        data_processed_mb, cost_estimate
    ) VALUES (
        p_user_id, CURDATE(), p_watermark_type, p_processor_used,
        1, p_processing_time, IF(p_success, 1, 0), IF(p_success, 0, 1),
        p_data_size_mb, p_cost_estimate
    )
    ON DUPLICATE KEY UPDATE
        files_processed = files_processed + 1,
        processing_time_total = processing_time_total + p_processing_time,
        processing_time_avg = processing_time_total / files_processed,
        success_count = success_count + IF(p_success, 1, 0),
        failure_count = failure_count + IF(p_success, 0, 1),
        success_rate = (success_count / files_processed) * 100,
        data_processed_mb = data_processed_mb + p_data_size_mb,
        cost_estimate = cost_estimate + p_cost_estimate,
        updated_at = NOW();
    
    COMMIT;
END$$

DELIMITER ;

-- =====================================================
-- SUMMARY
-- =====================================================

SELECT 'COMPREHENSIVE WATERMARK SYSTEM SETUP COMPLETE' as status;
SELECT 'Tables created: watermark_settings, watermark_templates, template_ratings, watermark_processing_jobs, watermark_analytics, site_watermark_settings, watermark_detection_logs' as tables_created;
SELECT 'Views created: user_watermark_summary, popular_watermark_templates, watermark_processing_performance' as views_created;
SELECT 'Procedures created: ApplyWatermarkTemplate, UpdateWatermarkAnalytics' as procedures_created;
SELECT 'Default data inserted: site settings and public templates' as default_data;
SELECT 'Performance indexes added for optimal query performance' as performance_optimization;