CREATE TABLE IF NOT EXISTS collaboration_admin_departments (
 user_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
 department_id BIGINT UNSIGNED NOT NULL,
 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY (department_id) REFERENCES departments(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS collaboration_deleted_projects (
 project_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
 deleted_by BIGINT UNSIGNED NOT NULL,
 deleted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (project_id) REFERENCES collaboration_projects(id) ON DELETE CASCADE,
 FOREIGN KEY (deleted_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Reuse an existing admin department column when the deployed installation has one.
SET @has_admin_department=(SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='admins' AND column_name='department_id');
SET @seed_admin_scope=IF(@has_admin_department>0,'INSERT IGNORE INTO collaboration_admin_departments(user_id,department_id) SELECT a.user_id,a.department_id FROM admins a JOIN departments d ON d.id=a.department_id','SELECT 1');
PREPARE seed_admin_scope FROM @seed_admin_scope;
EXECUTE seed_admin_scope;
DEALLOCATE PREPARE seed_admin_scope;

SET @has_user_department=(SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='users' AND column_name='department_id');
SET @seed_user_scope=IF(@has_user_department>0,'INSERT IGNORE INTO collaboration_admin_departments(user_id,department_id) SELECT u.id,u.department_id FROM users u JOIN departments d ON d.id=u.department_id WHERE u.role="admin"','SELECT 1');
PREPARE seed_user_scope FROM @seed_user_scope;
EXECUTE seed_user_scope;
DEALLOCATE PREPARE seed_user_scope;
