-- AUTO TESTER - File Storage Tables Migration
-- Created: 2026-04-22
-- Description: Create tables for file storage system (images, reports, backups)

-- ============================================
-- 1. File Storage Table
-- ============================================

CREATE TABLE IF NOT EXISTS `file_storage` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `original_name` VARCHAR(255) NOT NULL COMMENT 'Original filename',
    `file_name` VARCHAR(255) NOT NULL UNIQUE COMMENT 'Generated unique filename',
    `file_path` VARCHAR(500) NOT NULL COMMENT 'Relative file path',
    `file_size` BIGINT NOT NULL COMMENT 'File size in bytes',
    `file_type` VARCHAR(100) NOT NULL COMMENT 'MIME type',
    `category` ENUM('image', 'report', 'vehicle', 'inspection', 'backup', 'temp') NOT NULL COMMENT 'File category',
    `reference_id` INT COMMENT 'Reference to related record (inspection_id, vehicle_id, etc.)',
    `description` TEXT COMMENT 'File description',
    `uploaded_by` INT COMMENT 'ID of user who uploaded the file',
    `is_public` BOOLEAN DEFAULT FALSE COMMENT 'Whether file is publicly accessible',
    `download_count` INT DEFAULT 0 COMMENT 'Number of times downloaded',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` DATETIME COMMENT 'Soft delete timestamp',
    `deleted_by` INT COMMENT 'ID of user who deleted the file',
    
    FOREIGN KEY (`uploaded_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`deleted_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    
    INDEX `idx_file_name` (`file_name`),
    INDEX `idx_category` (`category`),
    INDEX `idx_reference_id` (`reference_id`),
    INDEX `idx_uploaded_by` (`uploaded_by`),
    INDEX `idx_created_at` (`created_at`),
    INDEX `idx_category_reference` (`category`, `reference_id`),
    INDEX `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='File storage system';

-- ============================================
-- 2. File Access Log Table
-- ============================================

CREATE TABLE IF NOT EXISTS `file_access_log` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `file_id` INT NOT NULL COMMENT 'File ID',
    `user_id` INT COMMENT 'User ID who accessed the file',
    `action` ENUM('view', 'download', 'upload', 'delete') NOT NULL COMMENT 'Action performed',
    `ip_address` VARCHAR(45) COMMENT 'IP address of the user',
    `user_agent` TEXT COMMENT 'User agent string',
    `access_date` DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`file_id`) REFERENCES `file_storage`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    
    INDEX `idx_file_id` (`file_id`),
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_action` (`action`),
    INDEX `idx_access_date` (`access_date`),
    INDEX `idx_file_action` (`file_id`, `action`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='File access logging';

-- ============================================
-- 3. File Sharing Table
-- ============================================

CREATE TABLE IF NOT EXISTS `file_sharing` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `file_id` INT NOT NULL COMMENT 'File ID',
    `shared_with` INT COMMENT 'User ID who has access',
    `shared_by` INT NOT NULL COMMENT 'User ID who shared the file',
    `permission` ENUM('view', 'download', 'edit') DEFAULT 'view' COMMENT 'Permission level',
    `expires_at` DATETIME COMMENT 'When access expires',
    `access_token` VARCHAR(255) COMMENT 'Unique access token',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `revoked_at` DATETIME COMMENT 'When access was revoked',
    `revoked_by` INT COMMENT 'User ID who revoked access',
    
    FOREIGN KEY (`file_id`) REFERENCES `file_storage`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`shared_with`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`shared_by`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`revoked_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    
    INDEX `idx_file_id` (`file_id`),
    INDEX `idx_shared_with` (`shared_with`),
    INDEX `idx_shared_by` (`shared_by`),
    INDEX `idx_access_token` (`access_token`),
    INDEX `idx_expires_at` (`expires_at`),
    INDEX `idx_active_shares` (`file_id`, `revoked_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='File sharing permissions';

-- ============================================
-- 4. File Versioning Table
-- ============================================

CREATE TABLE IF NOT EXISTS `file_versions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `file_id` INT NOT NULL COMMENT 'Original file ID',
    `version_number` INT NOT NULL COMMENT 'Version number',
    `version_name` VARCHAR(255) NOT NULL COMMENT 'Version filename',
    `version_path` VARCHAR(500) NOT NULL COMMENT 'Version file path',
    `version_size` BIGINT NOT NULL COMMENT 'Version file size',
    `change_description` TEXT COMMENT 'Description of changes',
    `created_by` INT NOT NULL COMMENT 'User who created this version',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`file_id`) REFERENCES `file_storage`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    
    UNIQUE KEY `uk_file_version` (`file_id`, `version_number`),
    INDEX `idx_file_id` (`file_id`),
    INDEX `idx_created_by` (`created_by`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='File versioning system';

-- ============================================
-- 5. File Thumbnails Table
-- ============================================

CREATE TABLE IF NOT EXISTS `file_thumbnails` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `file_id` INT NOT NULL COMMENT 'Original file ID',
    `thumbnail_name` VARCHAR(255) NOT NULL COMMENT 'Thumbnail filename',
    `thumbnail_path` VARCHAR(500) NOT NULL COMMENT 'Thumbnail file path',
    `thumbnail_size` BIGINT NOT NULL COMMENT 'Thumbnail file size',
    `width` INT COMMENT 'Thumbnail width',
    `height` INT COMMENT 'Thumbnail height',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`file_id`) REFERENCES `file_storage`(`id`) ON DELETE CASCADE,
    
    UNIQUE KEY `uk_file_thumbnail` (`file_id`),
    INDEX `idx_file_id` (`file_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='File thumbnails for images';

-- ============================================
-- 6. File Tags Table
-- ============================================

CREATE TABLE IF NOT EXISTS `file_tags` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `file_id` INT NOT NULL COMMENT 'File ID',
    `tag_name` VARCHAR(100) NOT NULL COMMENT 'Tag name',
    `tag_color` VARCHAR(7) DEFAULT '#007bff' COMMENT 'Tag color in hex',
    `created_by` INT COMMENT 'User who added the tag',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`file_id`) REFERENCES `file_storage`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    
    UNIQUE KEY `uk_file_tag` (`file_id`, `tag_name`),
    INDEX `idx_file_id` (`file_id`),
    INDEX `idx_tag_name` (`tag_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='File tagging system';

-- ============================================
-- 7. File Settings Table
-- ============================================

CREATE TABLE IF NOT EXISTS `file_settings` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `setting_key` VARCHAR(100) NOT NULL UNIQUE COMMENT 'Setting key',
    `setting_value` TEXT NOT NULL COMMENT 'Setting value',
    `description` TEXT COMMENT 'Setting description',
    `is_active` BOOLEAN DEFAULT TRUE COMMENT 'Whether setting is active',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX `idx_setting_key` (`setting_key`),
    INDEX `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='File system settings';

-- ============================================
-- 8. Insert Default Settings
-- ============================================

INSERT INTO `file_settings` (`setting_key`, `setting_value`, `description`) VALUES
('max_file_size', '10485760', 'Maximum file size in bytes (10MB)'),
('allowed_image_types', 'jpg,jpeg,png,gif,webp', 'Allowed image file types'),
('allowed_report_types', 'pdf,doc,docx,xls,xlsx', 'Allowed report file types'),
('thumbnail_width', '200', 'Thumbnail width in pixels'),
('thumbnail_height', '200', 'Thumbnail height in pixels'),
('enable_versioning', '1', 'Enable file versioning'),
('max_versions_per_file', '10', 'Maximum versions per file'),
('auto_cleanup_temp_hours', '24', 'Auto cleanup temporary files after hours'),
('enable_public_sharing', '1', 'Enable public file sharing'),
('default_sharing_expires_days', '7', 'Default sharing expiration in days'),
('enable_thumbnails', '1', 'Generate thumbnails for images'),
('compression_quality', '85', 'Image compression quality (0-100)')
ON DUPLICATE KEY UPDATE 
    `setting_value` = VALUES(`setting_value`),
    `description` = VALUES(`description`),
    `updated_at` = CURRENT_TIMESTAMP;

-- ============================================
-- 9. Create Triggers
-- ============================================

-- Trigger to log file access on upload
DELIMITER $$
CREATE TRIGGER IF NOT EXISTS `log_file_upload`
AFTER INSERT ON `file_storage`
FOR EACH ROW
BEGIN
    INSERT INTO file_access_log (file_id, user_id, action, ip_address, user_agent)
    VALUES (NEW.id, NEW.uploaded_by, 'upload', 
            IFNULL(@CURRENT_IP, '127.0.0.1'), 
            IFNULL(@CURRENT_USER_AGENT, 'System'));
END$$
DELIMITER ;

-- Trigger to log file access on delete
DELIMITER $$
CREATE TRIGGER IF NOT EXISTS `log_file_delete`
AFTER UPDATE ON `file_storage`
FOR EACH ROW
BEGIN
    IF NEW.deleted_at IS NOT NULL AND OLD.deleted_at IS NULL THEN
        INSERT INTO file_access_log (file_id, user_id, action, ip_address, user_agent)
        VALUES (NEW.id, NEW.deleted_by, 'delete', 
                IFNULL(@CURRENT_IP, '127.0.0.1'), 
                IFNULL(@CURRENT_USER_AGENT, 'System'));
    END IF;
END$$
DELIMITER ;

-- ============================================
-- 10. Create Views
-- ============================================

-- View for active files with user info
CREATE OR REPLACE VIEW `v_active_files` AS
SELECT 
    fs.*,
    u.name as uploaded_by_name,
    u.email as uploaded_by_email,
    COUNT(fal.id) as access_count,
    MAX(fal.access_date) as last_accessed
FROM file_storage fs
LEFT JOIN users u ON fs.uploaded_by = u.id
LEFT JOIN file_access_log fal ON fs.id = fal.file_id
WHERE fs.deleted_at IS NULL
GROUP BY fs.id
ORDER BY fs.created_at DESC;

-- View for file statistics
CREATE OR REPLACE VIEW `v_file_stats` AS
SELECT 
    category,
    COUNT(*) as total_files,
    SUM(file_size) as total_size,
    AVG(file_size) as avg_size,
    MIN(file_size) as min_size,
    MAX(file_size) as max_size,
    COUNT(DISTINCT uploaded_by) as unique_uploaders,
    DATE(created_at) as upload_date
FROM file_storage
WHERE deleted_at IS NULL
GROUP BY category, DATE(created_at)
ORDER BY upload_date DESC, category;

-- View for popular files
CREATE OR REPLACE VIEW `v_popular_files` AS
SELECT 
    fs.*,
    COUNT(fal.id) as download_count,
    u.name as uploaded_by_name
FROM file_storage fs
LEFT JOIN file_access_log fal ON fs.id = fal.file_id AND fal.action = 'download'
LEFT JOIN users u ON fs.uploaded_by = u.id
WHERE fs.deleted_at IS NULL
GROUP BY fs.id
HAVING download_count > 0
ORDER BY download_count DESC, fs.created_at DESC;

-- ============================================
-- 11. Create Stored Procedures
-- ============================================

DELIMITER $$
-- Procedure to get file with full details
CREATE PROCEDURE IF NOT EXISTS `GetFileDetails`(IN file_id INT)
BEGIN
    SELECT 
        fs.*,
        u.name as uploaded_by_name,
        u.email as uploaded_by_email,
        COUNT(fal.id) as total_access,
        SUM(CASE WHEN fal.action = 'download' THEN 1 ELSE 0 END) as download_count,
        MAX(fal.access_date) as last_accessed,
        GROUP_CONCAT(DISTINCT ft.tag_name) as tags
    FROM file_storage fs
    LEFT JOIN users u ON fs.uploaded_by = u.id
    LEFT JOIN file_access_log fal ON fs.id = fal.file_id
    LEFT JOIN file_tags ft ON fs.id = ft.file_id
    WHERE fs.id = file_id AND fs.deleted_at IS NULL
    GROUP BY fs.id;
END$$
DELIMITER ;

DELIMITER $$
-- Procedure to get files by category with pagination
CREATE PROCEDURE IF NOT EXISTS `GetFilesByCategory`(
    IN p_category VARCHAR(20),
    IN p_limit INT,
    IN p_offset INT
)
BEGIN
    SELECT 
        fs.*,
        u.name as uploaded_by_name,
        COUNT(fal.id) as access_count
    FROM file_storage fs
    LEFT JOIN users u ON fs.uploaded_by = u.id
    LEFT JOIN file_access_log fal ON fs.id = fal.file_id
    WHERE fs.deleted_at IS NULL 
    AND (p_category IS NULL OR fs.category = p_category)
    GROUP BY fs.id
    ORDER BY fs.created_at DESC
    LIMIT p_offset, p_limit;
END$$
DELIMITER ;

DELIMITER $$
-- Procedure to cleanup old temporary files
CREATE PROCEDURE IF NOT EXISTS `CleanupTempFiles`(IN older_than_hours INT)
BEGIN
    DECLARE deleted_count INT DEFAULT 0;
    
    -- Mark temporary files as deleted
    UPDATE file_storage 
    SET deleted_at = NOW(), deleted_by = NULL
    WHERE category = 'temp' 
    AND created_at < DATE_SUB(NOW(), INTERVAL older_than_hours HOUR)
    AND deleted_at IS NULL;
    
    SET deleted_count = ROW_COUNT();
    
    -- Log cleanup
    INSERT INTO system_logs (log_type, message, created_at)
    VALUES ('file_cleanup', CONCAT('Cleaned up ', deleted_count, ' temporary files'), NOW());
    
    SELECT deleted_count as files_deleted;
END$$
DELIMITER ;

DELIMITER $$
-- Procedure to generate storage report
CREATE PROCEDURE IF NOT EXISTS `GenerateStorageReport`()
BEGIN
    SELECT 
        'Total Files' as metric,
        COUNT(*) as value,
        'files' as unit
    FROM file_storage 
    WHERE deleted_at IS NULL
    
    UNION ALL
    
    SELECT 
        'Total Size' as metric,
        ROUND(SUM(file_size) / 1048576, 2) as value,
        'MB' as unit
    FROM file_storage 
    WHERE deleted_at IS NULL
    
    UNION ALL
    
    SELECT 
        'Images' as metric,
        COUNT(*) as value,
        'files' as unit
    FROM file_storage 
    WHERE category = 'image' AND deleted_at IS NULL
    
    UNION ALL
    
    SELECT 
        'Reports' as metric,
        COUNT(*) as value,
        'files' as unit
    FROM file_storage 
    WHERE category = 'report' AND deleted_at IS NULL
    
    UNION ALL
    
    SELECT 
        'Average File Size' as metric,
        ROUND(AVG(file_size) / 1024, 2) as value,
        'KB' as unit
    FROM file_storage 
    WHERE deleted_at IS NULL;
END$$
DELIMITER ;

-- ============================================
-- 12. Add Indexes for Performance
-- ============================================

-- Composite indexes for common queries
ALTER TABLE file_storage 
ADD INDEX `idx_category_created` (`category`, `created_at`),
ADD INDEX `idx_reference_category` (`reference_id`, `category`),
ADD INDEX `idx_public_active` (`is_public`, `deleted_at`);

ALTER TABLE file_access_log 
ADD INDEX `idx_file_date_action` (`file_id`, `access_date`, `action`);

ALTER TABLE file_sharing 
ADD INDEX `idx_shared_active` (`shared_with`, `revoked_at`);

-- ============================================
-- 13. Grant Permissions (if needed)
-- ============================================

-- Note: Add specific user permissions here if required
-- GRANT SELECT, INSERT, UPDATE, DELETE ON auto_tester.file_storage TO 'autotester_app'@'localhost';
-- GRANT SELECT, INSERT, UPDATE, DELETE ON auto_tester.file_access_log TO 'autotester_app'@'localhost';
-- GRANT SELECT, INSERT, UPDATE, DELETE ON auto_tester.file_sharing TO 'autotester_app'@'localhost';
-- GRANT SELECT, INSERT, UPDATE, DELETE ON auto_tester.file_versions TO 'autotester_app'@'localhost';
-- GRANT SELECT, INSERT, UPDATE, DELETE ON auto_tester.file_thumbnails TO 'autotester_app'@'localhost';
-- GRANT SELECT, INSERT, UPDATE, DELETE ON auto_tester.file_tags TO 'autotester_app'@'localhost';
-- GRANT SELECT, INSERT, UPDATE ON auto_tester.file_settings TO 'autotester_app'@'localhost';

-- ============================================
-- Migration Complete
-- ============================================

-- Log migration completion
INSERT INTO system_logs (log_type, message, created_at) 
VALUES ('migration', 'File storage tables migration completed successfully', NOW())
ON DUPLICATE KEY UPDATE message = message;
