-- Watermark Combination System Database Tables
-- Comprehensive watermark combination and layering system
-- Requirements: 7.5, 7.6

-- Watermark combination logs table
CREATE TABLE IF NOT EXISTS watermark_combination_logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    media_id BIGINT NOT NULL,
    watermark_types JSON NOT NULL,
    layers_count INT NOT NULL DEFAULT 1,
    processing_time INT DEFAULT 0,
    status ENUM('success', 'failed', 'processing') DEFAULT 'processing',
    output_path VARCHAR(500) NULL,
    error_message TEXT NULL,
    global_strength DECIMAL(3,2) DEFAULT 1.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_media (user_id, media_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
);

-- Watermark compositions table
CREATE TABLE IF NOT EXISTS watermark_compositions (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    composition_id VARCHAR(100) NOT NULL UNIQUE,
    name VARCHAR(255) NULL,
    description TEXT NULL,
    metadata JSON NOT NULL,
    layers JSON NOT NULL,
    is_public BOOLEAN DEFAULT FALSE,
    usage_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_composition_id (composition_id),
    INDEX idx_public (is_public),
    INDEX idx_usage_count (usage_count)
);

-- Watermark effectiveness analysis table
CREATE TABLE IF NOT EXISTS watermark_effectiveness_analysis (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    media_id BIGINT NOT NULL,
    combination_log_id BIGINT NULL,
    overall_coverage DECIMAL(5,2) DEFAULT 0.00,
    visibility_score DECIMAL(5,2) DEFAULT 0.00,
    security_level ENUM('low', 'medium', 'high', 'maximum') DEFAULT 'low',
    removal_difficulty DECIMAL(5,2) DEFAULT 0.00,
    effectiveness_score DECIMAL(5,2) DEFAULT 0.00,
    performance_impact JSON NULL,
    recommendations JSON NULL,
    analysis_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_media (user_id, media_id),
    INDEX idx_effectiveness_score (effectiveness_score),
    INDEX idx_security_level (security_level),
    INDEX idx_analysis_date (analysis_date),
    FOREIGN KEY (combination_log_id) REFERENCES watermark_combination_logs(id) ON DELETE SET NULL
);

-- Update vault_files table to support watermark combinations
ALTER TABLE vault_files 
ADD COLUMN IF NOT EXISTS watermark_combination JSON NULL COMMENT 'Stores applied watermark combination configuration',
ADD COLUMN IF NOT EXISTS combination_effectiveness_score DECIMAL(5,2) NULL COMMENT 'Overall effectiveness score of applied watermarks',
ADD COLUMN IF NOT EXISTS last_effectiveness_analysis TIMESTAMP NULL COMMENT 'Last time effectiveness was analyzed';

-- Add indexes for watermark combination queries
ALTER TABLE vault_files 
ADD INDEX IF NOT EXISTS idx_watermark_combination (is_watermarked, combination_effectiveness_score),
ADD INDEX IF NOT EXISTS idx_effectiveness_analysis (last_effectiveness_analysis);

-- Watermark layer templates table
CREATE TABLE IF NOT EXISTS watermark_layer_templates (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    template_name VARCHAR(255) NOT NULL,
    layer_type ENUM('text', 'image', 'qr', 'steganographic', 'blockchain') NOT NULL,
    layer_config JSON NOT NULL,
    is_default BOOLEAN DEFAULT FALSE,
    is_public BOOLEAN DEFAULT FALSE,
    usage_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_type (user_id, layer_type),
    INDEX idx_public_type (is_public, layer_type),
    INDEX idx_usage_count (usage_count)
);

-- Watermark combination presets table
CREATE TABLE IF NOT EXISTS watermark_combination_presets (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    preset_name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    layers JSON NOT NULL,
    composition_settings JSON NULL,
    is_default BOOLEAN DEFAULT FALSE,
    is_public BOOLEAN DEFAULT FALSE,
    usage_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_public (is_public),
    INDEX idx_usage_count (usage_count)
);

-- Watermark strength profiles table
CREATE TABLE IF NOT EXISTS watermark_strength_profiles (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    profile_name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    global_strength DECIMAL(3,2) NOT NULL DEFAULT 1.00,
    layer_adjustments JSON NULL,
    use_case ENUM('light', 'standard', 'heavy', 'maximum', 'custom') DEFAULT 'standard',
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_use_case (use_case)
);

-- Insert default strength profiles
INSERT IGNORE INTO watermark_strength_profiles (user_id, profile_name, description, global_strength, use_case, is_default) VALUES
(0, 'Light Protection', 'Minimal watermarking for premium content', 0.30, 'light', FALSE),
(0, 'Standard Protection', 'Balanced watermarking for general content', 0.70, 'standard', TRUE),
(0, 'Heavy Protection', 'Strong watermarking for sensitive content', 0.90, 'heavy', FALSE),
(0, 'Maximum Protection', 'Full strength watermarking for high-risk content', 1.00, 'maximum', FALSE);

-- Watermark combination statistics table
CREATE TABLE IF NOT EXISTS watermark_combination_stats (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    date DATE NOT NULL,
    combinations_applied INT DEFAULT 0,
    avg_processing_time DECIMAL(8,2) DEFAULT 0.00,
    avg_effectiveness_score DECIMAL(5,2) DEFAULT 0.00,
    most_used_types JSON NULL,
    success_rate DECIMAL(5,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_user_date (user_id, date),
    INDEX idx_date (date),
    INDEX idx_effectiveness (avg_effectiveness_score)
);