-- Deployment 25: Add review_score + review history to both KPI tables
-- Safe to re-run (ADD COLUMN IF NOT EXISTS)

ALTER TABLE `kpi_scheduling_entries`
  ADD COLUMN IF NOT EXISTS `review_score` tinyint(2) DEFAULT NULL AFTER `review_comment`,
  ADD COLUMN IF NOT EXISTS `score_revised` tinyint(1) DEFAULT 0 AFTER `review_score`;

ALTER TABLE `cesar_kpi_entries`
  ADD COLUMN IF NOT EXISTS `review_score` tinyint(2) DEFAULT NULL AFTER `review_comment`,
  ADD COLUMN IF NOT EXISTS `score_revised` tinyint(1) DEFAULT 0 AFTER `review_score`;

-- Review score revision history (shared for both KPI types)
CREATE TABLE IF NOT EXISTS `kpi_review_history` (
  `id`              int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `kpi_type`        enum('scheduling','cesar') NOT NULL,
  `kpi_entry_id`    int(11) UNSIGNED NOT NULL,
  `previous_score`  tinyint(2) DEFAULT NULL,
  `previous_comment` text DEFAULT NULL,
  `new_score`       tinyint(2) DEFAULT NULL,
  `new_comment`     text DEFAULT NULL,
  `revised_reason`  text NOT NULL,
  `revised_by_type` varchar(20) DEFAULT NULL,
  `revised_by_id`   int(11) DEFAULT NULL,
  `revised_by_name` varchar(200) DEFAULT NULL,
  `created_at`      timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_kpi` (`kpi_type`, `kpi_entry_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Expand kpi_entry_audit action ENUM to include score_revised
ALTER TABLE `kpi_entry_audit`
  MODIFY COLUMN `action` enum('draft_saved','submitted','reviewed','approved','returned','locked','unlocked','score_revised') NOT NULL;