-- =====================================================
-- Comprehensive Watermark System Database Schema
-- =====================================================
-- Version: 3.0 Enterprise Edition
-- Date: 2025-01-09
-- Description: Complete enterprise watermark system with:
--              - Site-wide watermark management
--              - User customization and overrides
--              - Multi-service processing with fallback
--              - Template system and sharing
--              - Analytics and monitoring
--              - DMCA protection and leak detection
-- =====================================================

START TRANSACTION;

-- =====================================================
-- ADMIN SETTINGS UPDATES FOR SITE WATERMARKS
-- =====================================================

-- Add comprehensive watermark columns to admin_settings table
ALTER TABLE `admin_settings` 
ADD COLUMN IF NOT EXISTS `watermark_enabled` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Global watermark enable/disable',
ADD COLUMN IF NOT EXISTS `watermark_on_videos` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Video watermark enable/disable',
ADD COLUMN IF NOT EXISTS `watermark_on_images` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Image watermark enable/disable',
ADD COLUMN IF NOT EXISTS `watermark_on_audio` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Audio watermark enable/disable',
ADD COLUMN IF NOT EXISTS `site_watermark_video` varchar(255) DEFAULT NULL COMMENT 'Site video watermark filename',
ADD COLUMN IF NOT EXISTS `site_watermark_image` varchar(255) DEFAULT NULL COMMENT 'Site image watermark filename',
ADD COLUMN IF NOT EXISTS `site_watermark_audio` varchar(255) DEFAULT NULL COMMENT 'Site audio watermark filename',
ADD COLUMN IF NOT EXISTS `watermark_position` varchar(50) DEFAULT 'bottom-right' COMMENT 'Default watermark position',
ADD COLUMN IF NOT EXISTS `watermark_opacity` decimal(3,2) DEFAULT 0.70 COMMENT 'Default watermark opacity (0.0-1.0)',
ADD COLUMN IF NOT EXISTS `watermark_font_size` int(11) DEFAULT 24 COMMENT 'Default font size',
ADD COLUMN IF NOT EXISTS `watermark_text_color` varchar(7) DEFAULT '#ffffff' COMMENT 'Default text color (#hex)',
ADD COLUMN IF NOT EXISTS `watermark_text` varchar(255) DEFAULT NULL COMMENT 'Default watermark text',
ADD COLUMN IF NOT EXISTS `watermark_policy` enum('required','optional','disabled') DEFAULT 'optional' COMMENT 'Site watermark policy',
ADD COLUMN IF NOT EXISTS `allow_custom_watermarks` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Allow users to upload custom watermarks',
ADD COLUMN IF NOT EXISTS `ffmpeg_path` varchar(255) DEFAULT '/usr/bin/ffmpeg' COMMENT 'FFmpeg binary path',
ADD COLUMN IF NOT EXISTS `coconut_api_key` varchar(255) DEFAULT NULL COMMENT 'Coconut.co API key',
ADD COLUMN IF NOT EXISTS `coconut_region` varchar(50) DEFAULT 'Virginia' COMMENT 'Coconut.co processing region',
ADD COLUMN IF NOT EXISTS `coconut_webhook_url` varchar(255) DEFAULT NULL COMMENT 'Coconut.io webhook URL',
ADD COLUMN IF NOT EXISTS `aws_mediaconvert_role` varchar(255) DEFAULT NULL COMMENT 'AWS MediaConvert IAM role ARN',
ADD COLUMN IF NOT EXISTS `azure_media_account` varchar(255) DEFAULT NULL COMMENT 'Azure Media Services account name',
ADD COLUMN IF NOT EXISTS `google_cloud_project` varchar(255) DEFAULT NULL COMMENT 'Google Cloud project ID';

-- =====================================================
-- SITE WATERMARK SETTINGS TABLE
-- =====================================================

CREATE TABLE IF NOT EXISTS `site_watermark_settings` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL DEFAULT 1 COMMENT 'Site identifier for multi-site support',
  `watermarking_enabled` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Enable watermarking site-wide',
  `default_watermark_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Default site watermark template ID',
  `video_watermark_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Video-specific watermark template ID',
  `image_watermark_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Image-specific watermark template ID',
  `audio_watermark_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Audio-specific watermark template ID',
  `watermark_policy` enum('required','optional','disabled') NOT NULL DEFAULT 'optional' COMMENT 'Site watermark enforcement policy',
  `enforcement_level` enum('strict','moderate','lenient') NOT NULL DEFAULT 'moderate' COMMENT 'Policy enforcement level',
  `allow_user_override` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Allow users to override site watermarks',
  `created_by` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Admin user who created settings',
  `updated_by` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Admin user who last updated settings',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `site_watermark_settings_site_id_unique` (`site_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Site-wide watermark configuration and policies';

-- =====================================================
-- WATERMARK SETTINGS TABLE (USER CUSTOMIZATION)
-- =====================================================

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','both') NOT NULL DEFAULT 'text' COMMENT 'Type of watermark',
  `watermark_file` varchar(500) DEFAULT NULL COMMENT 'Custom watermark image path',
  `text_content` varchar(255) DEFAULT NULL COMMENT 'Watermark text 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' COMMENT 'Watermark position',
  `opacity` tinyint(3) UNSIGNED NOT NULL DEFAULT 70 COMMENT 'Watermark opacity (0-100)',
  `color` varchar(7) NOT NULL DEFAULT '#ffffff' COMMENT 'Text color in hex format',
  `font_family` varchar(100) DEFAULT 'Arial' COMMENT 'Font family name',
  `font_size` tinyint(3) UNSIGNED NOT NULL DEFAULT 24 COMMENT 'Font size in pixels',
  `rotation` decimal(5,2) NOT NULL DEFAULT 0.00 COMMENT 'Rotation angle in degrees',
  `is_active` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Enable watermark for user',
  `processing_service` enum('local','coconut','aws','azure','google') NOT NULL DEFAULT 'local' COMMENT 'Preferred processing service',
  `overrides_site_default` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Override site default watermark',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `watermark_settings_user_id_unique` (`user_id`),
  KEY `watermark_settings_user_id_index` (`user_id`),
  KEY `watermark_settings_active_index` (`is_active`),
  KEY `watermark_settings_override_index` (`overrides_site_default`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='User watermark settings and customization';

-- =====================================================
-- WATERMARK TEMPLATES TABLE
-- =====================================================

CREATE TABLE IF NOT EXISTS `watermark_templates` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL COMMENT 'Template creator user ID',
  `name` varchar(100) NOT NULL COMMENT 'Template name',
  `description` text DEFAULT NULL COMMENT 'Template description',
  `watermark_config` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'Watermark configuration JSON' CHECK (json_valid(`watermark_config`)),
  `is_default` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Default template for user',
  `is_public` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Available to all users',
  `usage_count` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Number of times used',
  `schedule_config` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Scheduled application config JSON' CHECK (json_valid(`schedule_config`)),
  `category` varchar(50) DEFAULT 'custom' COMMENT 'Template category',
  `tags` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Template tags JSON array' CHECK (json_valid(`tags`)),
  `rating` decimal(3,2) UNSIGNED DEFAULT 0.00 COMMENT 'Average user rating (0.00-5.00)',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `watermark_templates_user_id_index` (`user_id`),
  KEY `watermark_templates_public_index` (`is_public`),
  KEY `watermark_templates_category_index` (`category`),
  KEY `watermark_templates_usage_index` (`usage_count`),
  KEY `watermark_templates_rating_index` (`rating`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Reusable watermark templates and configurations';

-- =====================================================
-- WATERMARK PROCESSING JOBS TABLE
-- =====================================================

CREATE TABLE IF NOT EXISTS `watermark_processing_jobs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL COMMENT 'User who initiated the job',
  `media_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Media file being processed',
  `processing_service` enum('local','coconut','aws','azure','google') NOT NULL DEFAULT 'local' COMMENT 'Service used for processing',
  `job_status` enum('queued','processing','completed','failed','cancelled','retrying') NOT NULL DEFAULT 'queued' COMMENT 'Current job status',
  `started_at` timestamp NULL DEFAULT NULL COMMENT 'Job start timestamp',
  `completed_at` timestamp NULL DEFAULT NULL COMMENT 'Job completion timestamp',
  `error_message` text DEFAULT NULL COMMENT 'Error message if job failed',
  `input_file_path` varchar(500) DEFAULT NULL COMMENT 'Input file path',
  `output_file_path` varchar(500) DEFAULT NULL COMMENT 'Output file path',
  `watermark_config` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Watermark configuration used' CHECK (json_valid(`watermark_config`)),
  `fallback_attempts` tinyint(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Number of fallback attempts',
  `final_processor_used` varchar(50) DEFAULT NULL COMMENT 'Final processor that completed the job',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `watermark_processing_jobs_user_id_index` (`user_id`),
  KEY `watermark_processing_jobs_media_id_index` (`media_id`),
  KEY `watermark_processing_jobs_status_index` (`job_status`),
  KEY `watermark_processing_jobs_service_index` (`processing_service`),
  KEY `watermark_processing_jobs_created_index` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Watermark processing job tracking and monitoring';

-- =====================================================
-- DOWNLOAD LOGS TABLE (FOR LEAK TRACING)
-- =====================================================

CREATE TABLE IF NOT EXISTS `download_logs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL COMMENT 'User who downloaded',
  `creator_id` bigint(20) UNSIGNED NOT NULL COMMENT 'Content creator',
  `media_id` bigint(20) UNSIGNED NOT NULL COMMENT 'Downloaded media',
  `watermark_id` varchar(100) DEFAULT NULL COMMENT 'Unique watermark identifier',
  `ip_address` varchar(45) DEFAULT NULL COMMENT 'Downloader IP address',
  `user_agent` text DEFAULT NULL COMMENT 'Browser user agent',
  `download_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Download timestamp',
  `file_size` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Downloaded file size',
  `processing_time` decimal(8,3) DEFAULT NULL COMMENT 'Processing time in seconds',
  `watermark_type` enum('text','image','steganographic','qr','blockchain') DEFAULT NULL COMMENT 'Type of watermark applied',
  `permission_level` enum('free','subscriber','buyer','premium') DEFAULT NULL COMMENT 'User permission level',
  `subscription_tier` varchar(50) DEFAULT NULL COMMENT 'User subscription tier',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `download_logs_user_id_index` (`user_id`),
  KEY `download_logs_creator_id_index` (`creator_id`),
  KEY `download_logs_media_id_index` (`media_id`),
  KEY `download_logs_watermark_id_index` (`watermark_id`),
  KEY `download_logs_timestamp_index` (`download_timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Download tracking for leak detection and analytics';

-- =====================================================
-- LEAK DETECTION TABLE
-- =====================================================

CREATE TABLE IF NOT EXISTS `leak_detections` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `media_id` bigint(20) UNSIGNED NOT NULL COMMENT 'Original media file',
  `original_watermark_id` varchar(100) DEFAULT NULL COMMENT 'Original watermark identifier',
  `detected_url` varchar(1000) NOT NULL COMMENT 'URL where leak was detected',
  `detection_confidence` decimal(5,2) NOT NULL DEFAULT 0.00 COMMENT 'Detection confidence percentage',
  `leak_source` varchar(255) DEFAULT NULL COMMENT 'Suspected leak source',
  `status` enum('detected','investigating','confirmed','false_positive','dmca_sent','resolved') NOT NULL DEFAULT 'detected' COMMENT 'Investigation status',
  `dmca_notice_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Related DMCA notice ID',
  `resolution_date` timestamp NULL DEFAULT NULL COMMENT 'Date leak was resolved',
  `watermark_signature` text DEFAULT NULL COMMENT 'Extracted watermark signature',
  `detection_method` enum('automated','manual','user_report') NOT NULL DEFAULT 'automated' COMMENT 'How leak was detected',
  `scan_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Date of detection scan',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `leak_detections_media_id_index` (`media_id`),
  KEY `leak_detections_watermark_id_index` (`original_watermark_id`),
  KEY `leak_detections_status_index` (`status`),
  KEY `leak_detections_confidence_index` (`detection_confidence`),
  KEY `leak_detections_scan_date_index` (`scan_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Automated leak detection and tracking';

-- =====================================================
-- DMCA NOTICES TABLE
-- =====================================================

CREATE TABLE IF NOT EXISTS `dmca_notices` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `creator_id` bigint(20) UNSIGNED NOT NULL COMMENT 'Content creator filing notice',
  `leak_detection_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Related leak detection',
  `platform` varchar(100) NOT NULL COMMENT 'Platform where notice was sent',
  `notice_content` longtext NOT NULL COMMENT 'DMCA notice content',
  `status` enum('draft','sent','acknowledged','complied','rejected','appealed') NOT NULL DEFAULT 'draft' COMMENT 'Notice status',
  `sent_date` timestamp NULL DEFAULT NULL COMMENT 'Date notice was sent',
  `response_date` timestamp NULL DEFAULT NULL COMMENT 'Date platform responded',
  `platform_response` text DEFAULT NULL COMMENT 'Platform response content',
  `takedown_url` varchar(1000) DEFAULT NULL COMMENT 'URL of content to be taken down',
  `reference_number` varchar(100) DEFAULT NULL COMMENT 'Platform reference number',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `dmca_notices_creator_id_index` (`creator_id`),
  KEY `dmca_notices_leak_detection_id_index` (`leak_detection_id`),
  KEY `dmca_notices_platform_index` (`platform`),
  KEY `dmca_notices_status_index` (`status`),
  KEY `dmca_notices_sent_date_index` (`sent_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='DMCA takedown notice tracking';

-- =====================================================
-- WATERMARK ANALYTICS TABLE
-- =====================================================

CREATE TABLE IF NOT EXISTS `watermark_analytics` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL COMMENT 'Content creator',
  `media_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Media file',
  `metric_type` enum('download','view','leak','effectiveness','performance') NOT NULL COMMENT 'Type of metric',
  `metric_value` decimal(10,2) NOT NULL COMMENT 'Metric value',
  `metric_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Additional metric data JSON' CHECK (json_valid(`metric_data`)),
  `date_recorded` date NOT NULL COMMENT 'Date metric was recorded',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `watermark_analytics_user_id_index` (`user_id`),
  KEY `watermark_analytics_media_id_index` (`media_id`),
  KEY `watermark_analytics_metric_type_index` (`metric_type`),
  KEY `watermark_analytics_date_index` (`date_recorded`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Watermark effectiveness and usage analytics';

-- =====================================================
-- BLOCKCHAIN WATERMARKS TABLE (FUTURE FEATURE)
-- =====================================================

CREATE TABLE IF NOT EXISTS `blockchain_watermarks` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `watermark_hash` varchar(64) NOT NULL COMMENT 'SHA-256 hash of watermark data',
  `blockchain_tx_id` varchar(100) DEFAULT NULL COMMENT 'Blockchain transaction ID',
  `media_id` bigint(20) UNSIGNED NOT NULL COMMENT 'Associated media file',
  `user_id` bigint(20) UNSIGNED NOT NULL COMMENT 'Content owner',
  `proof_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Blockchain proof data JSON' CHECK (json_valid(`proof_data`)),
  `verification_status` enum('pending','verified','failed') NOT NULL DEFAULT 'pending' COMMENT 'Blockchain verification status',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `blockchain_watermarks_hash_unique` (`watermark_hash`),
  KEY `blockchain_watermarks_media_id_index` (`media_id`),
  KEY `blockchain_watermarks_user_id_index` (`user_id`),
  KEY `blockchain_watermarks_status_index` (`verification_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Blockchain-based watermark verification';

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

-- Insert default site watermark settings
INSERT IGNORE INTO `site_watermark_settings` (`site_id`, `watermarking_enabled`, `watermark_policy`, `enforcement_level`, `allow_user_override`) 
VALUES (1, 1, 'optional', 'moderate', 1);

-- Insert default watermark templates
INSERT IGNORE INTO `watermark_templates` (`user_id`, `name`, `description`, `watermark_config`, `is_public`, `category`) VALUES
(1, 'Simple Text Watermark', 'Basic text watermark with username', '{"watermark_type":"text","text_content":"@username","position":"bottom-right","opacity":70,"color":"#ffffff","font_family":"Arial","font_size":24}', 1, 'text'),
(1, 'Copyright Notice', 'Copyright watermark for professional content', '{"watermark_type":"text","text_content":"© Copyright","position":"bottom-center","opacity":80,"color":"#000000","font_family":"Arial","font_size":18}', 1, 'copyright'),
(1, 'Brand Watermark', 'Branded watermark with logo placement', '{"watermark_type":"image","position":"top-right","opacity":60}', 1, 'brand');

-- =====================================================
-- UPDATE EXISTING TABLES
-- =====================================================

-- Add watermark-related columns to media table if they don't exist
ALTER TABLE `media` 
ADD COLUMN IF NOT EXISTS `watermark_applied` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Whether watermark has been applied',
ADD COLUMN IF NOT EXISTS `watermark_type` enum('none','site','custom','both') DEFAULT 'none' COMMENT 'Type of watermark applied',
ADD COLUMN IF NOT EXISTS `watermark_id` varchar(100) DEFAULT NULL COMMENT 'Unique watermark identifier for tracking',
ADD COLUMN IF NOT EXISTS `download_permission` enum('none','all','subscribers','buyers','premium') NOT NULL DEFAULT 'subscribers' COMMENT 'Download permission level';

-- Add download notification preference to users table
ALTER TABLE `users` 
ADD COLUMN IF NOT EXISTS `notify_downloads` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Enable download notifications',
ADD COLUMN IF NOT EXISTS `watermark_required` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Require watermarks on user content';

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

-- Additional indexes for better query performance
CREATE INDEX IF NOT EXISTS `idx_watermark_settings_active_override` ON `watermark_settings` (`is_active`, `overrides_site_default`);
CREATE INDEX IF NOT EXISTS `idx_download_logs_creator_timestamp` ON `download_logs` (`creator_id`, `download_timestamp`);
CREATE INDEX IF NOT EXISTS `idx_leak_detections_status_confidence` ON `leak_detections` (`status`, `detection_confidence`);
CREATE INDEX IF NOT EXISTS `idx_watermark_analytics_user_date` ON `watermark_analytics` (`user_id`, `date_recorded`);

COMMIT;

-- =====================================================
-- VERIFICATION QUERIES
-- =====================================================

-- Verify tables were created successfully
SELECT 
    TABLE_NAME,
    TABLE_COMMENT,
    TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME IN (
    'site_watermark_settings',
    'watermark_settings', 
    'watermark_templates',
    'watermark_processing_jobs',
    'download_logs',
    'leak_detections',
    'dmca_notices',
    'watermark_analytics',
    'blockchain_watermarks'
)
ORDER BY TABLE_NAME;
-- =====================================================
-- LIVE STREAM PROCESSES TABLE
-- =====================================================

-- Live stream watermark processes tracking
CREATE TABLE IF NOT EXISTS `live_stream_processes` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `media_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `stream_id` varchar(255) NOT NULL,
  `process_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `output_url` varchar(500) DEFAULT NULL,
  `watermark_config` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `status` enum('initializing','active','paused','stopped','failed','completed') DEFAULT 'initializing',
  `started_at` timestamp NULL DEFAULT NULL,
  `stopped_at` timestamp NULL DEFAULT NULL,
  `error_message` text DEFAULT NULL,
  `performance_metrics` 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`),
  KEY `idx_media_id` (`media_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_stream_id` (`stream_id`),
  KEY `idx_started_at` (`started_at`),
  CONSTRAINT `live_stream_processes_chk_1` CHECK (json_valid(`process_data`)),
  CONSTRAINT `live_stream_processes_chk_2` CHECK (json_valid(`watermark_config`)),
  CONSTRAINT `live_stream_processes_chk_3` CHECK (json_valid(`performance_metrics`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Live stream watermark process tracking';

-- =====================================================
-- WATERMARK PERFORMANCE METRICS TABLE
-- =====================================================

-- Performance tracking for watermark operations
CREATE TABLE IF NOT EXISTS `watermark_performance_metrics` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `media_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `operation_type` enum('apply','remove','detect','analyze','stream') NOT NULL,
  `processor_used` varchar(100) NOT NULL,
  `file_size_bytes` bigint(20) unsigned DEFAULT NULL,
  `processing_time_ms` int(11) unsigned DEFAULT NULL,
  `cpu_usage_percent` decimal(5,2) DEFAULT NULL,
  `memory_usage_mb` decimal(10,2) DEFAULT NULL,
  `success` tinyint(1) DEFAULT 1,
  `error_message` text DEFAULT NULL,
  `quality_metrics` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `watermark_config` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_media_id` (`media_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_operation_type` (`operation_type`),
  KEY `idx_processor_used` (`processor_used`),
  KEY `idx_success` (`success`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `watermark_performance_metrics_chk_1` CHECK (json_valid(`quality_metrics`)),
  CONSTRAINT `watermark_performance_metrics_chk_2` CHECK (json_valid(`watermark_config`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Watermark operation performance tracking';

-- =====================================================
-- WATERMARK LEAK DETECTION TABLE
-- =====================================================

-- Leak detection and monitoring
CREATE TABLE IF NOT EXISTS `watermark_leak_detections` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `media_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `detection_source` varchar(100) NOT NULL COMMENT 'web_scan, user_report, automated_check',
  `leak_url` varchar(1000) DEFAULT NULL,
  `leak_platform` varchar(100) DEFAULT NULL,
  `confidence_score` decimal(5,2) DEFAULT NULL COMMENT 'Detection confidence 0-100',
  `watermark_data_extracted` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `detection_method` varchar(100) DEFAULT NULL COMMENT 'visual_match, metadata_match, hash_match',
  `status` enum('detected','investigating','confirmed','false_positive','resolved') DEFAULT 'detected',
  `investigation_notes` text DEFAULT NULL,
  `action_taken` text DEFAULT NULL,
  `dmca_notice_sent` tinyint(1) DEFAULT 0,
  `dmca_notice_date` timestamp NULL DEFAULT NULL,
  `resolution_date` 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_media_id` (`media_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_detection_source` (`detection_source`),
  KEY `idx_status` (`status`),
  KEY `idx_confidence_score` (`confidence_score`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `watermark_leak_detections_chk_1` CHECK (json_valid(`watermark_data_extracted`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Watermark leak detection and tracking';

COMMIT;