-- =====================================================
-- VAULT SYSTEM DATABASE SCHEMA - Netflix/OnlyFans Level
-- Multi-Role Media Storage with Enterprise Features
-- Pure SQL with IF NOT EXISTS - No Foreign Key Constraints
-- =====================================================

-- Upload Sessions Table - Chunked Upload Management
CREATE TABLE IF NOT EXISTS `upload_sessions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `upload_id` varchar(255) NOT NULL UNIQUE,
  `filename` varchar(255) NOT NULL,
  `original_filename` varchar(255) NOT NULL,
  `total_size` bigint(20) unsigned NOT NULL,
  `chunk_size` int(11) unsigned NOT NULL DEFAULT 5242880,
  `total_chunks` int(11) unsigned NOT NULL,
  `uploaded_chunks` int(11) unsigned NOT NULL DEFAULT 0,
  `file_type` enum('video','image','audio','document') NOT NULL,
  `mime_type` varchar(100) NOT NULL,
  `temp_path` varchar(500) NOT NULL,
  `final_path` varchar(500) DEFAULT NULL,
  `status` enum('uploading','completed','failed','cancelled','expired') NOT NULL DEFAULT 'uploading',
  `progress_percentage` decimal(5,2) NOT NULL DEFAULT 0.00,
  `error_message` text DEFAULT NULL,
  `metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `expires_at` timestamp NOT 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_upload_id` (`upload_id`),
  KEY `idx_status` (`status`),
  KEY `idx_expires_at` (`expires_at`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Vault Files Table - Main File Storage Registry
CREATE TABLE IF NOT EXISTS `vault_files` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `upload_session_id` bigint(20) unsigned DEFAULT NULL,
  `creator_folder` varchar(50) NOT NULL DEFAULT 'creator',
  `title` varchar(500) NOT NULL,
  `name` varchar(255) NOT NULL,
  `original_name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `tags` text DEFAULT NULL,
  `file_path` varchar(500) DEFAULT NULL,
  `bucket_path` varchar(500) DEFAULT NULL,
  `storage_provider` varchar(50) NOT NULL DEFAULT 'local',
  `file_type` enum('video','image','audio','document') NOT NULL,
  `file_size` bigint(20) unsigned NOT NULL,
  `mime_type` varchar(100) NOT NULL,
  `file_extension` varchar(20) NOT NULL,
  `category` varchar(100) NOT NULL,
  `status` enum('uploading','processing','ready','failed','deleted') NOT NULL DEFAULT 'uploading',
  `thumbnail_path` varchar(500) DEFAULT NULL,
  `preview_path` varchar(500) DEFAULT NULL,
  `watermark_applied` tinyint(1) NOT NULL DEFAULT 0,
  `processing_config` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `quality_variants` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `hls_playlist_path` varchar(500) DEFAULT NULL,
  `download_count` int(11) unsigned NOT NULL DEFAULT 0,
  `view_count` int(11) unsigned NOT NULL DEFAULT 0,
  `published_at` timestamp NULL DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_upload_session_id` (`upload_session_id`),
  KEY `idx_creator_folder` (`creator_folder`),
  KEY `idx_file_type` (`file_type`),
  KEY `idx_category` (`category`),
  KEY `idx_status` (`status`),
  KEY `idx_published_at` (`published_at`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Processing Jobs Table - Three-Tier Processing System
CREATE TABLE IF NOT EXISTS `processing_jobs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `vault_file_id` bigint(20) unsigned NOT NULL,
  `upload_session_id` bigint(20) unsigned DEFAULT NULL,
  `job_id` varchar(255) NOT NULL UNIQUE,
  `provider` enum('ffmpeg','coconut','aws_mediaconvert','azure','google') NOT NULL DEFAULT 'ffmpeg',
  `provider_job_id` varchar(255) DEFAULT NULL,
  `input_path` varchar(500) NOT NULL,
  `output_path` varchar(500) DEFAULT NULL,
  `input_size` bigint(20) unsigned NOT NULL,
  `output_size` bigint(20) unsigned DEFAULT NULL,
  `processing_config` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `processing_type` enum('encoding','transcoding','thumbnail','watermark','hls','preview') NOT NULL DEFAULT 'encoding',
  `status` enum('queued','processing','completed','failed','cancelled','retrying') NOT NULL DEFAULT 'queued',
  `progress_percentage` decimal(5,2) NOT NULL DEFAULT 0.00,
  `priority` int(11) NOT NULL DEFAULT 5,
  `started_at` timestamp NULL DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `failed_at` timestamp NULL DEFAULT NULL,
  `error_message` text DEFAULT NULL,
  `error_details` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `retry_count` int(11) NOT NULL DEFAULT 0,
  `max_retries` int(11) NOT NULL DEFAULT 3,
  `webhook_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `processing_time` int(11) unsigned DEFAULT NULL,
  `cost_estimate` decimal(10,4) DEFAULT NULL,
  `metadata` 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_vault_file_id` (`vault_file_id`),
  KEY `idx_upload_session_id` (`upload_session_id`),
  KEY `idx_job_id` (`job_id`),
  KEY `idx_provider` (`provider`),
  KEY `idx_status` (`status`),
  KEY `idx_priority` (`priority`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Vault Timeline Posts Table - Vault to Timeline Publishing
CREATE TABLE IF NOT EXISTS `vault_timeline_posts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `vault_file_id` bigint(20) unsigned NOT NULL,
  `timeline_post_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `published_by` bigint(20) unsigned DEFAULT NULL,
  `publish_config` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `scheduled_at` timestamp NULL DEFAULT NULL,
  `published_at` timestamp NULL DEFAULT NULL,
  `status` enum('scheduled','published','failed','cancelled') NOT NULL DEFAULT 'published',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_vault_file_id` (`vault_file_id`),
  KEY `idx_timeline_post_id` (`timeline_post_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_published_at` (`published_at`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Vault Analytics Table - Storage and Usage Analytics
CREATE TABLE IF NOT EXISTS `vault_analytics` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `date` date NOT NULL,
  `total_files` int(11) unsigned NOT NULL DEFAULT 0,
  `total_size` bigint(20) unsigned NOT NULL DEFAULT 0,
  `files_uploaded` int(11) unsigned NOT NULL DEFAULT 0,
  `files_processed` int(11) unsigned NOT NULL DEFAULT 0,
  `files_published` int(11) unsigned NOT NULL DEFAULT 0,
  `files_downloaded` int(11) unsigned NOT NULL DEFAULT 0,
  `storage_used` bigint(20) unsigned NOT NULL DEFAULT 0,
  `bandwidth_used` bigint(20) unsigned NOT NULL DEFAULT 0,
  `processing_time` int(11) unsigned NOT NULL DEFAULT 0,
  `processing_cost` decimal(10,4) NOT NULL DEFAULT 0.0000,
  `video_files` int(11) unsigned NOT NULL DEFAULT 0,
  `image_files` int(11) unsigned NOT NULL DEFAULT 0,
  `audio_files` int(11) unsigned NOT NULL DEFAULT 0,
  `document_files` int(11) unsigned 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_date` (`user_id`, `date`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Vault Access Logs Table - Security and Audit Trail
CREATE TABLE IF NOT EXISTS `vault_access_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `vault_file_id` bigint(20) unsigned DEFAULT NULL,
  `action` varchar(100) NOT NULL,
  `resource` varchar(255) DEFAULT NULL,
  `ip_address` varchar(45) NOT NULL,
  `user_agent` text DEFAULT NULL,
  `request_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `response_status` int(11) DEFAULT NULL,
  `response_time` int(11) unsigned DEFAULT NULL,
  `file_size` bigint(20) unsigned DEFAULT NULL,
  `bandwidth_used` bigint(20) unsigned DEFAULT NULL,
  `security_flags` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_vault_file_id` (`vault_file_id`),
  KEY `idx_action` (`action`),
  KEY `idx_ip_address` (`ip_address`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Vault Security Events Table - Security Monitoring
CREATE TABLE IF NOT EXISTS `vault_security_events` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `event_type` varchar(100) NOT NULL,
  `severity` enum('low','medium','high','critical') NOT NULL DEFAULT 'medium',
  `description` text NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `request_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `risk_score` int(11) unsigned DEFAULT NULL,
  `automated_response` varchar(255) DEFAULT NULL,
  `resolved` tinyint(1) NOT NULL DEFAULT 0,
  `resolved_at` timestamp NULL DEFAULT NULL,
  `resolved_by` bigint(20) unsigned DEFAULT NULL,
  `metadata` 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_user_id` (`user_id`),
  KEY `idx_event_type` (`event_type`),
  KEY `idx_severity` (`severity`),
  KEY `idx_resolved` (`resolved`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Vault Settings Table - User-Specific Vault Configuration
CREATE TABLE IF NOT EXISTS `vault_settings` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `storage_provider` varchar(50) NOT NULL DEFAULT 'local',
  `processing_provider` varchar(50) NOT NULL DEFAULT 'ffmpeg',
  `auto_watermark` tinyint(1) NOT NULL DEFAULT 1,
  `auto_thumbnail` tinyint(1) NOT NULL DEFAULT 1,
  `auto_hls` tinyint(1) NOT NULL DEFAULT 1,
  `quality_settings` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `notification_settings` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `privacy_settings` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `custom_categories` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `upload_preferences` 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_user_settings` (`user_id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Vault Shares Table - Cross-Role Content Sharing
CREATE TABLE IF NOT EXISTS `vault_shares` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `vault_file_id` bigint(20) unsigned NOT NULL,
  `shared_by` bigint(20) unsigned NOT NULL,
  `shared_with` bigint(20) unsigned DEFAULT NULL,
  `share_type` enum('user','role','public','link') NOT NULL DEFAULT 'user',
  `share_token` varchar(255) DEFAULT NULL,
  `permissions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `access_count` int(11) unsigned NOT NULL DEFAULT 0,
  `last_accessed` timestamp NULL DEFAULT NULL,
  `status` enum('active','expired','revoked') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_vault_file_id` (`vault_file_id`),
  KEY `idx_shared_by` (`shared_by`),
  KEY `idx_shared_with` (`shared_with`),
  KEY `idx_share_token` (`share_token`),
  KEY `idx_status` (`status`),
  KEY `idx_expires_at` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Vault Collections Table - Content Organization
CREATE TABLE IF NOT EXISTS `vault_collections` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `slug` varchar(255) NOT NULL,
  `cover_image` varchar(500) DEFAULT NULL,
  `is_public` tinyint(1) NOT NULL DEFAULT 0,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `file_count` int(11) unsigned NOT NULL DEFAULT 0,
  `total_size` bigint(20) unsigned 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`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_slug` (`slug`),
  KEY `idx_is_public` (`is_public`),
  KEY `idx_sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Vault Collection Files Table - Files in Collections
CREATE TABLE IF NOT EXISTS `vault_collection_files` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `collection_id` bigint(20) unsigned NOT NULL,
  `vault_file_id` bigint(20) unsigned NOT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `added_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_collection_file` (`collection_id`, `vault_file_id`),
  KEY `idx_collection_id` (`collection_id`),
  KEY `idx_vault_file_id` (`vault_file_id`),
  KEY `idx_sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Vault Processing Queue Table - Redis Fallback Queue
CREATE TABLE IF NOT EXISTS `vault_processing_queue` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `job_id` varchar(255) NOT NULL UNIQUE,
  `vault_file_id` bigint(20) unsigned NOT NULL,
  `provider` varchar(50) NOT NULL,
  `priority` int(11) NOT NULL DEFAULT 5,
  `payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `attempts` int(11) NOT NULL DEFAULT 0,
  `reserved_at` timestamp NULL DEFAULT NULL,
  `available_at` timestamp NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_job_id` (`job_id`),
  KEY `idx_vault_file_id` (`vault_file_id`),
  KEY `idx_provider` (`provider`),
  KEY `idx_priority` (`priority`),
  KEY `idx_available_at` (`available_at`),
  KEY `idx_reserved_at` (`reserved_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- INDEXES FOR PERFORMANCE OPTIMIZATION
-- =====================================================

-- Composite indexes for common queries
CREATE INDEX IF NOT EXISTS `idx_vault_files_user_status` ON `vault_files` (`user_id`, `status`);
CREATE INDEX IF NOT EXISTS `idx_vault_files_user_type` ON `vault_files` (`user_id`, `file_type`);
CREATE INDEX IF NOT EXISTS `idx_vault_files_user_category` ON `vault_files` (`user_id`, `category`);
CREATE INDEX IF NOT EXISTS `idx_vault_files_status_created` ON `vault_files` (`status`, `created_at`);

CREATE INDEX IF NOT EXISTS `idx_processing_jobs_status_priority` ON `processing_jobs` (`status`, `priority`);
CREATE INDEX IF NOT EXISTS `idx_processing_jobs_provider_status` ON `processing_jobs` (`provider`, `status`);

CREATE INDEX IF NOT EXISTS `idx_upload_sessions_user_status` ON `upload_sessions` (`user_id`, `status`);
CREATE INDEX IF NOT EXISTS `idx_upload_sessions_status_expires` ON `upload_sessions` (`status`, `expires_at`);

-- =====================================================
-- INITIAL DATA INSERTION
-- =====================================================

-- Insert default vault settings for existing users (if any)
INSERT IGNORE INTO `vault_settings` (`user_id`, `storage_provider`, `processing_provider`, `auto_watermark`, `auto_thumbnail`, `auto_hls`)
SELECT `id`, 'local', 'ffmpeg', 1, 1, 1 FROM `users` WHERE `id` NOT IN (SELECT `user_id` FROM `vault_settings`);

-- =====================================================
-- CLEANUP PROCEDURES (Optional)
-- =====================================================

-- Procedure to cleanup expired upload sessions
DELIMITER $$
CREATE PROCEDURE IF NOT EXISTS CleanupExpiredUploadSessions()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE session_id BIGINT;
    DECLARE temp_path VARCHAR(500);
    
    DECLARE session_cursor CURSOR FOR 
        SELECT id, temp_path FROM upload_sessions 
        WHERE status = 'uploading' AND expires_at < NOW();
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN session_cursor;
    
    cleanup_loop: LOOP
        FETCH session_cursor INTO session_id, temp_path;
        IF done THEN
            LEAVE cleanup_loop;
        END IF;
        
        -- Update session status
        UPDATE upload_sessions 
        SET status = 'expired', updated_at = NOW() 
        WHERE id = session_id;
        
        -- Note: File cleanup should be handled by application layer
        
    END LOOP;
    
    CLOSE session_cursor;
END$$
DELIMITER ;

-- Procedure to update vault analytics
DELIMITER $$
CREATE PROCEDURE IF NOT EXISTS UpdateVaultAnalytics(IN target_date DATE)
BEGIN
    INSERT INTO vault_analytics (
        user_id, date, total_files, total_size, files_uploaded, 
        files_processed, files_published, storage_used,
        video_files, image_files, audio_files, document_files
    )
    SELECT 
        vf.user_id,
        target_date,
        COUNT(*) as total_files,
        SUM(vf.file_size) as total_size,
        SUM(CASE WHEN DATE(vf.created_at) = target_date THEN 1 ELSE 0 END) as files_uploaded,
        SUM(CASE WHEN vf.status = 'ready' AND DATE(vf.updated_at) = target_date THEN 1 ELSE 0 END) as files_processed,
        SUM(CASE WHEN vf.published_at IS NOT NULL AND DATE(vf.published_at) = target_date THEN 1 ELSE 0 END) as files_published,
        SUM(vf.file_size) as storage_used,
        SUM(CASE WHEN vf.file_type = 'video' THEN 1 ELSE 0 END) as video_files,
        SUM(CASE WHEN vf.file_type = 'image' THEN 1 ELSE 0 END) as image_files,
        SUM(CASE WHEN vf.file_type = 'audio' THEN 1 ELSE 0 END) as audio_files,
        SUM(CASE WHEN vf.file_type = 'document' THEN 1 ELSE 0 END) as document_files
    FROM vault_files vf
    WHERE vf.deleted_at IS NULL
    GROUP BY vf.user_id
    ON DUPLICATE KEY UPDATE
        total_files = VALUES(total_files),
        total_size = VALUES(total_size),
        files_uploaded = VALUES(files_uploaded),
        files_processed = VALUES(files_processed),
        files_published = VALUES(files_published),
        storage_used = VALUES(storage_used),
        video_files = VALUES(video_files),
        image_files = VALUES(image_files),
        audio_files = VALUES(audio_files),
        document_files = VALUES(document_files),
        updated_at = NOW();
END$$
DELIMITER ;

-- =====================================================
-- COMPLETION MESSAGE
-- =====================================================

SELECT 'Vault System Database Schema Created Successfully!' as Status,
       'Netflix/OnlyFans-Level Multi-Role Media Storage Ready' as Message,
       NOW() as Timestamp;