-- Migration: Add FTZ Files to general file manager
-- This migration adds an FTZ Files folder in the file manager system

-- First, add 'ftz' to the entity_type ENUM
ALTER TABLE file_folders 
MODIFY COLUMN entity_type ENUM(
    'system','crm','user','customer','vendor','project',
    'accounting','hr','inventory','manufacturing','purchases','sales','activity','ftz'
) DEFAULT 'system';

-- Add FTZ folder context to file_folders table
-- Get a valid created_by user ID (first admin user)
SET @admin_user_id = (SELECT id FROM users WHERE id = 1 LIMIT 1);

INSERT INTO file_folders (name, entity_type, parent_id, owner_id, access_level, path, created_by, created_at, updated_at)
SELECT 
    'FTZ Files',
    'ftz',
    NULL,
    NULL,
    'public',
    '/FTZ Files',
    @admin_user_id,
    NOW(),
    NOW()
WHERE NOT EXISTS (
    SELECT 1 FROM file_folders WHERE name = 'FTZ Files' AND entity_type = 'ftz'
);

-- Add permission for FTZ context in file manager
INSERT INTO permissions (name, module, description)
SELECT 'file_manager.access_ftz', 'Files', 'Access FTZ Files in File Manager'
WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE name = 'file_manager.access_ftz');

-- Grant permission to admin role
INSERT INTO role_permissions (role_id, permission_id)
SELECT 1, id FROM permissions 
WHERE name = 'file_manager.access_ftz'
AND NOT EXISTS (
    SELECT 1 FROM role_permissions rp 
    WHERE rp.role_id = 1 AND rp.permission_id = permissions.id
);