-- =====================================================
-- 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 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 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;

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 $$
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;