-- Lets HR/admin invite additional, named reviewers to a SPECIFIC
-- appraisal, beyond the standard Self -> Manager -> HR -> GM chain --
-- e.g. a department head from a related team, a project sponsor, or
-- anyone else whose sign-off matters for that particular employee's
-- cycle. Each invited reviewer has their own approval_level: reviewers
-- at level 1 must all approve before level 2 can act, and so on,
-- mirroring how the standard chain itself is sequential.
--
-- This sits BETWEEN HR review and GM review in the sequence: GM's final
-- sign-off is blocked (see ajax_gm_review.php) until every invited
-- reviewer, at every level, has approved.
--
-- Safe to run more than once.

CREATE TABLE IF NOT EXISTS `appraisal_reviewers` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `appraisal_id` INT(11) NOT NULL,
    `employee_id` INT(11) NOT NULL,
    `approval_level` INT(11) NOT NULL DEFAULT 1,
    `status` ENUM('Pending','Approved','Rejected') NOT NULL DEFAULT 'Pending',
    `comment` TEXT DEFAULT NULL,
    `invited_by` INT(11) DEFAULT NULL,
    `invited_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `reviewed_at` DATETIME DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_appraisal` (`appraisal_id`),
    KEY `idx_employee` (`employee_id`),
    CONSTRAINT `fk_ar_appraisal` FOREIGN KEY (`appraisal_id`) REFERENCES `employee_appraisals` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Registers the "My Review Requests" page (where an invited reviewer
-- finds appraisals awaiting their sign-off) in the sidebar, under My
-- Portal since it's a self-service, "things waiting on me" page like the
-- rest of that section -- available to every role, since anybody can be
-- invited to review regardless of their normal role.
INSERT INTO `nav_modules` (`module_key`, `label`, `url`, `parent_id`, `display_order`)
SELECT 'ess_extra_reviews', 'My Review Requests', 'performance/my_extra_reviews.php', id, 95
FROM `nav_modules` nm
WHERE nm.`module_key` = 'my_portal'
AND NOT EXISTS (SELECT 1 FROM `nav_modules` WHERE `module_key` = 'ess_extra_reviews');

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_extra_reviews';