-- QA Viewer role: projects + tasks + profile read only (for RBAC testing)
-- Assign app_role_id = (SELECT id FROM workspace_roles WHERE slug = 'qa-viewer') to test employees

INSERT INTO workspace_roles (name, slug, description, is_system, status)
SELECT 'QA Viewer', 'qa-viewer', 'Read-only projects and tasks for QA testing', 0, 'active'
WHERE NOT EXISTS (
  SELECT 1 FROM workspace_roles WHERE slug = 'qa-viewer' AND deleted_at IS NULL
);

SET @qa_role_id = (SELECT id FROM workspace_roles WHERE slug = 'qa-viewer' AND deleted_at IS NULL LIMIT 1);

DELETE FROM role_permissions WHERE role_id = @qa_role_id;

INSERT INTO role_permissions (role_id, module_key, action_key, allowed)
SELECT @qa_role_id, pm.module_key, pa.action_key,
  CASE
    WHEN pm.module_key = 'dashboard' AND pa.action_key = 'read' THEN 1
    WHEN pm.module_key = 'projects' AND pa.action_key = 'read' THEN 1
    WHEN pm.module_key = 'tasks' AND pa.action_key = 'read' THEN 1
    WHEN pm.module_key = 'profile' AND pa.action_key IN ('read', 'update') THEN 1
    ELSE 0
  END
FROM permission_modules pm
CROSS JOIN permission_actions pa;

-- Test checklist (manual):
-- 1. Assign QA Viewer to an employee; log in as that employee
-- 2. Network tab: no GET /api/departments, /api/designations, /api/employees
-- 3. Projects filter builder: owner/department-related fields disabled if mapped
-- 4. /departments → Permission Denied
-- 5. Export buttons hidden on Projects/Tasks
-- 6. Add Employee hidden without employees.create