-- Add filesystem support fields to files table
ALTER TABLE files 
ADD COLUMN filesystem_path TEXT NULL AFTER path,
ADD COLUMN source_type ENUM('uploads', 'local_filesystem') DEFAULT 'uploads' AFTER filesystem_path;

-- Add filesystem support fields to folders table
ALTER TABLE folders 
ADD COLUMN filesystem_path TEXT NULL AFTER full_path,
ADD COLUMN source_type ENUM('uploads', 'local_filesystem') DEFAULT 'uploads' AFTER filesystem_path;

-- Add index for better performance
CREATE INDEX idx_files_source_type ON files(source_type);
CREATE INDEX idx_folders_source_type ON folders(source_type);

-- Comment to document the changes
-- This migration adds support for local filesystem access by:
-- 1. Adding filesystem_path field to store absolute paths for local files/folders
-- 2. Adding source_type field to distinguish between uploads and local filesystem items
-- 3. Adding indexes for efficient querying by source type