-- ============================================================
-- OREMUS - BALANCE SCORE CARD (BSC) MODULE TABLES
-- Database: Oremus
-- Dependencies: Shared (users, departments)
-- ============================================================

-- --------------------------------------------------------
-- evaluation_periods
-- Defines appraisal cycles (half-year / annual)
-- Each user has an active period at a time
-- FK: USER_ID -> users.ID
-- --------------------------------------------------------
CREATE TABLE `evaluation_periods` (
  `PERIOD_ID` int NOT NULL AUTO_INCREMENT,
  `USER_ID` int DEFAULT NULL COMMENT 'FK -> users.ID',
  `DEPARTMENT_ID` int DEFAULT NULL COMMENT 'FK -> departments.ID',
  `DESIGNATION` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `DEPARTMENT_NAME` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `USER_ROLE` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `START_DATE` date DEFAULT NULL,
  `END_DATE` date DEFAULT NULL,
  `CYCLE` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'e.g. H1, H2, ANNUAL',
  `ACTIVE` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`PERIOD_ID`),
  KEY `USER_ID` (`USER_ID`),
  CONSTRAINT `evaluation_periods_ibfk_1` FOREIGN KEY (`USER_ID`) REFERENCES `users` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- objectives  (BSC strategic objectives)
-- Master list of company-level BSC objectives
-- --------------------------------------------------------
CREATE TABLE `objectives` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `OBJECTIVE` text NOT NULL,
  `CREATED_AT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `DELETED` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- objectives_performance
-- User-specific BSC objective lines per evaluation period
-- BSC_REF: links to a BSC record (if applicable)
-- FK: USER_ID -> users.ID
-- FK: PERIOD_REF_ID -> evaluation_periods.PERIOD_ID
-- --------------------------------------------------------
CREATE TABLE `objectives_performance` (
  `OBJECTIVE_PERFORMANCE_ID` int NOT NULL AUTO_INCREMENT,
  `USER_ID` int DEFAULT NULL COMMENT 'FK -> users.ID',
  `BSC_REF` int DEFAULT NULL,
  `OBJECTIVE_PERFORMANCE_NAME` text COLLATE utf8mb4_general_ci,
  `OBJECTIVE_PERFORMANCE_WEIGHTAGE` decimal(5,2) DEFAULT NULL,
  `PERIOD_REF_ID` int DEFAULT NULL COMMENT 'FK -> evaluation_periods.PERIOD_ID',
  PRIMARY KEY (`OBJECTIVE_PERFORMANCE_ID`),
  KEY `USER_ID` (`USER_ID`),
  KEY `fk_objectives_period` (`PERIOD_REF_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- kras  (Key Result Areas)
-- KRAs belong to an objectives_performance record
-- FK: OBJECTIVE_PERFORMANCE_ID -> objectives_performance.OBJECTIVE_PERFORMANCE_ID
-- --------------------------------------------------------
CREATE TABLE `kras` (
  `KRA_ID` int NOT NULL AUTO_INCREMENT,
  `OBJECTIVE_PERFORMANCE_ID` int DEFAULT NULL COMMENT 'FK -> objectives_performance.OBJECTIVE_PERFORMANCE_ID',
  `KRA_NUMBER` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `KRA_WEIGHTAGE` decimal(5,2) DEFAULT NULL,
  `KRA_DESCRIPTION` text COLLATE utf8mb4_general_ci,
  PRIMARY KEY (`KRA_ID`),
  KEY `OBJECTIVE_PERFORMANCE_ID` (`OBJECTIVE_PERFORMANCE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- kpis  (Key Performance Indicators)
-- KPIs belong to a KRA
-- FK: KRA_ID -> kras.KRA_ID
-- --------------------------------------------------------
CREATE TABLE `kpis` (
  `KPI_ID` int NOT NULL AUTO_INCREMENT,
  `KRA_ID` int DEFAULT NULL COMMENT 'FK -> kras.KRA_ID',
  `KPI_NUMBER` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `KPI_PERCENTAGE` decimal(5,2) DEFAULT NULL,
  `KPI_DESCRIPTION` text COLLATE utf8mb4_general_ci,
  `BENCHMARK` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `WEIGHTAGE` decimal(6,2) DEFAULT NULL,
  PRIMARY KEY (`KPI_ID`),
  KEY `KRA_ID` (`KRA_ID`),
  CONSTRAINT `kpis_ibfk_1` FOREIGN KEY (`KRA_ID`) REFERENCES `kras` (`KRA_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- ratings
-- Self + Reviewer ratings per KPI per user per period
-- Roles: SELF, RM (Reporting Manager), FL (Functional Lead), CEO
-- FK: USER_ID -> users.ID
-- FK: PERIOD_REF_ID -> evaluation_periods.PERIOD_ID
-- FK: KPI_REF_ID -> kpis.KPI_ID (logical)
-- --------------------------------------------------------
CREATE TABLE `ratings` (
  `RATING_ID` int NOT NULL AUTO_INCREMENT,
  `USER_ID` int DEFAULT NULL COMMENT 'FK -> users.ID (employee being rated)',
  `PERIOD_REF_ID` int DEFAULT NULL COMMENT 'FK -> evaluation_periods.PERIOD_ID',
  `KPI_REF_ID` int DEFAULT NULL COMMENT 'FK -> kpis.KPI_ID',
  `SELF_RATING` int DEFAULT '0',
  `SELF_SCORING` float DEFAULT '0',
  `SELF_COMMENTS` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `RM_RATING` int DEFAULT '0',
  `RM_SCORING` float DEFAULT '0',
  `RM_COMMENTS` varchar(255) COLLATE utf8mb4_general_ci DEFAULT '',
  `RM_REMARKS` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `FL_RATING` int DEFAULT '0',
  `FL_SCORING` float DEFAULT '0',
  `FL_COMMENTS` varchar(255) COLLATE utf8mb4_general_ci DEFAULT '',
  `FL_REMARKS` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `CEO_RATING` int DEFAULT '0',
  `CEO_SCORING` float DEFAULT '0',
  `CEO_COMMENTS` varchar(255) COLLATE utf8mb4_general_ci DEFAULT '',
  `CEO_REMARKS` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`RATING_ID`),
  KEY `USER_ID` (`USER_ID`),
  KEY `fk_ratings_period` (`PERIOD_REF_ID`),
  CONSTRAINT `fk_ratings_period` FOREIGN KEY (`PERIOD_REF_ID`) REFERENCES `evaluation_periods` (`PERIOD_ID`),
  CONSTRAINT `ratings_ibfk_1` FOREIGN KEY (`USER_ID`) REFERENCES `users` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- rating_descriptor
-- Descriptive labels for each rating value (e.g., 5=Excellent)
-- FK: RATING_REF_ID -> rating_period.ID (logical)
-- --------------------------------------------------------
CREATE TABLE `rating_descriptor` (
  `RATING_DESCRIPTOR_ID` int NOT NULL AUTO_INCREMENT,
  `RATING_REF_ID` int NOT NULL COMMENT 'FK -> rating_period.ID',
  `RATING_KEY` varchar(50) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'e.g. "5", "4", "3"',
  `RATING_VALUE` text COLLATE utf8mb4_general_ci COMMENT 'e.g. "Excellent", "Good"',
  PRIMARY KEY (`RATING_DESCRIPTOR_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- rating_period
-- Defines BSC rating windows (start/end dates + rating scale)
-- --------------------------------------------------------
CREATE TABLE `rating_period` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `STARTED_DATE` date NOT NULL,
  `END_DATE` date NOT NULL,
  `RATING` int DEFAULT NULL COMMENT 'Max rating scale (e.g. 5)',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- rating_ratio
-- Weightage distribution across role-based ratings per user per period
-- UPDATED_BY: last edited by HR or CEO
-- STATUS: 0=draft, 1=finalized
-- FK: USER_ID -> users.ID
-- FK: PERIOD_ID -> evaluation_periods.PERIOD_ID
-- --------------------------------------------------------
CREATE TABLE `rating_ratio` (
  `RATING_RATIO_ID` int NOT NULL AUTO_INCREMENT,
  `USER_ID` int NOT NULL COMMENT 'FK -> users.ID',
  `PERIOD_ID` int NOT NULL COMMENT 'FK -> evaluation_periods.PERIOD_ID',
  `SELF_RATING` float NOT NULL COMMENT 'Weightage % for self rating',
  `RM_RATING` float NOT NULL,
  `FL_RATING` float NOT NULL,
  `CEO_RATING` float NOT NULL,
  `UPDATED_BY` enum('HR','CEO') COLLATE utf8mb4_general_ci DEFAULT 'CEO',
  `RATIONALISE_RATING` float NOT NULL DEFAULT '0',
  `STATUS` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`RATING_RATIO_ID`),
  UNIQUE KEY `unique_user_period` (`USER_ID`,`PERIOD_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- rationalise_rating
-- Final rationalized score after all reviews for a user+period
-- CEO_APPROVED + HR_APPROVED must both be 1 for finalization
-- FK: USER_ID -> users.ID
-- FK: PERIOD_REF_ID -> evaluation_periods.PERIOD_ID
-- --------------------------------------------------------
CREATE TABLE `rationalise_rating` (
  `RATIONALISE_RATING_ID` int NOT NULL AUTO_INCREMENT,
  `AVG_RATIONALISE_RATING` int DEFAULT NULL,
  `RATIONALISE_SCORE` int NOT NULL,
  `USER_ID` int DEFAULT NULL COMMENT 'FK -> users.ID',
  `PERIOD_REF_ID` int DEFAULT NULL COMMENT 'FK -> evaluation_periods.PERIOD_ID',
  `CEO_APPROVED` tinyint(1) DEFAULT '0',
  `HR_APPROVED` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`RATIONALISE_RATING_ID`),
  UNIQUE KEY `unique_user_period` (`USER_ID`,`PERIOD_REF_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- performance_summary
-- Aggregate counts of objectives/KRAs/KPIs per period
-- FK: PERIOD_REF_ID -> evaluation_periods.PERIOD_ID
-- --------------------------------------------------------
CREATE TABLE `performance_summary` (
  `SUMMARY_ID` int NOT NULL AUTO_INCREMENT,
  `TOTAL_OBJECTIVES` int DEFAULT NULL,
  `TOTAL_KRAS` int DEFAULT NULL,
  `TOTAL_KPIS` int DEFAULT NULL,
  `TOTAL_WEIGHTAGE` decimal(6,2) DEFAULT NULL,
  `PERIOD_REF_ID` int DEFAULT NULL COMMENT 'FK -> evaluation_periods.PERIOD_ID',
  PRIMARY KEY (`SUMMARY_ID`),
  KEY `fk_performance_summary_period` (`PERIOD_REF_ID`),
  CONSTRAINT `fk_performance_summary_period` FOREIGN KEY (`PERIOD_REF_ID`) REFERENCES `evaluation_periods` (`PERIOD_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- status  (BSC Workflow Status per user per period)
-- Tracks entire BSC review workflow for each user
-- CURRENT_STEP: draft -> submit_for_review -> rm_review -> fl_review -> ceo_review -> complete
-- FK: USER_ID -> users.ID
-- FK: PERIOD_REF_ID -> evaluation_periods.PERIOD_ID
-- --------------------------------------------------------
CREATE TABLE `status` (
  `STATUS_ID` int NOT NULL AUTO_INCREMENT,
  `USER_ID` int DEFAULT NULL COMMENT 'FK -> users.ID',
  `DATA_COMPLETE` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `READY_FOR_REVIEW` tinyint(1) DEFAULT NULL,
  `USER_ROLE` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'employee',
  `SUBMIT_FOR_REVIEW` tinyint(1) NOT NULL DEFAULT '0',
  `SUBMIT_FOR_REVIEW_DATE` datetime DEFAULT NULL,
  `REPORTING_MANAGER_REVIEWED` tinyint(1) NOT NULL DEFAULT '0',
  `RM_AUTO_APPROVED` tinyint(1) NOT NULL DEFAULT '0',
  `REPORTING_MANAGER_REVIEW_DATE` datetime DEFAULT NULL,
  `REPORTING_MANAGER_ID` int DEFAULT NULL COMMENT 'FK -> users.ID',
  `FUNCTIONAL_HEAD_REVIEWED` tinyint(1) NOT NULL DEFAULT '0',
  `FL_AUTO_APPROVED` tinyint(1) NOT NULL DEFAULT '0',
  `FUNCTIONAL_HEAD_REVIEW_DATE` datetime DEFAULT NULL,
  `FUNCTIONAL_HEAD_ID` int DEFAULT NULL COMMENT 'FK -> users.ID',
  `CEO_REVIEWED` tinyint(1) NOT NULL DEFAULT '0',
  `CEO_REVIEW_DATE` datetime DEFAULT NULL,
  `CEO_ID` int DEFAULT NULL COMMENT 'FK -> users.ID',
  `CURRENT_STEP` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'draft',
  `WORKFLOW_STATUS` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'Draft',
  `CREATED_AT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `PERIOD_REF_ID` int NOT NULL COMMENT 'FK -> evaluation_periods.PERIOD_ID',
  PRIMARY KEY (`STATUS_ID`),
  KEY `USER_ID` (`USER_ID`),
  KEY `fk_status_period` (`PERIOD_REF_ID`),
  CONSTRAINT `fk_status_period` FOREIGN KEY (`PERIOD_REF_ID`) REFERENCES `evaluation_periods` (`PERIOD_ID`),
  CONSTRAINT `status_ibfk_1` FOREIGN KEY (`USER_ID`) REFERENCES `users` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- feedback  (BSC 360-degree feedback)
-- Multi-reviewer feedback per user per period
-- FK: USER_ID -> users.ID
-- FK: PERIOD_REF_ID -> evaluation_periods.PERIOD_ID
-- --------------------------------------------------------
CREATE TABLE `feedback` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `USER_ID` int DEFAULT NULL COMMENT 'FK -> users.ID (employee being reviewed)',
  `QUESTION1` varchar(500) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `RESPONSE1` varchar(500) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `QUESTION2` varchar(500) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `RESPONSE2` varchar(500) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `LEADER_RESPONSE` text COLLATE utf8mb4_general_ci NOT NULL,
  `RM_RESPONSE` text COLLATE utf8mb4_general_ci NOT NULL,
  `FL_RESPONSE` text COLLATE utf8mb4_general_ci NOT NULL,
  `CEO_RESPONSE` text COLLATE utf8mb4_general_ci NOT NULL,
  `PERIOD_REF_ID` int DEFAULT NULL COMMENT 'FK -> evaluation_periods.PERIOD_ID',
  PRIMARY KEY (`ID`),
  KEY `USER_ID` (`USER_ID`),
  CONSTRAINT `feedback_ibfk_1` FOREIGN KEY (`USER_ID`) REFERENCES `users` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================================
-- BSC MODULE - TABLE RELATIONSHIPS
--
-- evaluation_periods (1) -> (*) objectives_performance
-- evaluation_periods (1) -> (*) ratings
-- evaluation_periods (1) ->  (1) performance_summary
-- evaluation_periods (1) ->  (1) status (per user)
-- evaluation_periods (1) ->  (1) rationalise_rating (per user)
-- evaluation_periods (1) -> (*) feedback
-- objectives_performance (1) -> (*) kras
-- kras (1) -> (*) kpis
-- kpis (1) -> (*) ratings
--
-- WORKFLOW:
--   HR creates evaluation_period
--   Employees fill BSC (objectives -> KRAs -> KPIs)
--   Employee self-rates -> submits for review
--   RM reviews -> FL reviews -> CEO reviews
--   Ratings rationalized -> finalized
-- ============================================================
