-- Fixes: staff have no way to reach "My Appraisals" from their ESS
-- portal. The page (ess/my_appraisals.php) existed and, as of this
-- session, correctly shows an employee their own appraisal history --
-- but it was never registered in nav_modules, so it never appeared in
-- the ESS sidebar submenu (My Portal), and wasn't in the dashboard's
-- quick-access tiles either (that part is a code fix, already applied
-- directly to ess/dashboard.php, not something a migration can do).
--
-- Safe to run whether or not the original nav migration
-- (2026_09_14_add_nav_module_access.sql) already ran -- only adds rows
-- that don't already exist.

INSERT INTO `nav_modules` (`module_key`, `label`, `url`, `parent_id`, `display_order`)
SELECT 'ess_appraisals', 'My Appraisals', 'ess/my_appraisals.php', id, 90
FROM `nav_modules` nm
WHERE nm.`module_key` = 'my_portal'
AND NOT EXISTS (SELECT 1 FROM `nav_modules` WHERE `module_key` = 'ess_appraisals');

INSERT IGNORE INTO `nav_module_roles` (`module_id`, `role`)
SELECT nm.id, r.role
FROM `nav_modules` nm
CROSS JOIN (
    SELECT 'admin' AS role UNION SELECT 'superadmin' UNION SELECT 'manager'
    UNION SELECT 'supervisor' UNION SELECT 'hr' UNION SELECT 'gm' UNION SELECT 'staff'
) r
WHERE nm.`module_key` = 'ess_appraisals';