-- ============================================================
-- OREMUS - GOAL SHEET MODULE TABLES
-- Database: Oremus
-- Dependencies: Shared (users)
-- ============================================================

-- --------------------------------------------------------
-- goal_sheets
-- Top-level goal sheet per submitter per cycle
-- SUBMITTER_ROLE: who submitted (REPORTING_MANAGER or FUNCTIONAL_LEAD)
-- STATUS lifecycle: DRAFT -> SUBMITTED -> FUNC_APPROVED | FUNC_REJECTED
--                           -> CEO_APPROVED | CEO_REJECTED
-- FUNC_STATUS: PENDING -> APPROVED | REJECTED | APPRAISED | REVIEWED
-- CEO_STATUS: PENDING -> APPROVED | REJECTED | REVIEWED
-- --------------------------------------------------------
CREATE TABLE `goal_sheets` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `SUBMITTER_USER_ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'FK -> users.USER_ID (submitting RM or FL)',
  `SUBMITTER_ROLE` enum('REPORTING_MANAGER','FUNCTIONAL_LEAD') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `SHEET_NAME` varchar(255) NOT NULL,
  `REVIEW_OPEN` tinyint(1) DEFAULT '0' COMMENT '1 = mid/year-end review window is open',
  `STATUS` enum('DRAFT','SUBMITTED','FUNC_APPROVED','CEO_APPROVED','FUNC_REJECTED','CEO_REJECTED') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'DRAFT',
  `FUNCTIONAL_LEAD_USER_ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'FK -> users.USER_ID',
  `FUNC_STATUS` enum('PENDING','APPROVED','REJECTED','APPRAISED','REVIEWED') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'PENDING',
  `FUNC_APPROVED_AT` datetime DEFAULT NULL,
  `FUNC_APPROVED_BY` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'FK -> users.USER_ID',
  `CEO_USER_ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'FK -> users.USER_ID',
  `CEO_STATUS` enum('PENDING','APPROVED','REJECTED','REVIEWED') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'PENDING',
  `CEO_APPROVED_AT` datetime DEFAULT NULL,
  `CEO_APPROVED_BY` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'FK -> users.USER_ID',
  `REJECT_REASON` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `REJECTED_BY` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'FK -> users.USER_ID',
  `REJECTED_AT` datetime DEFAULT NULL,
  `DELETED` tinyint(1) DEFAULT '0',
  `CREATED_AT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `IDX_GOAL_SUBMITTER` (`SUBMITTER_USER_ID`),
  KEY `IDX_GOAL_STATUS` (`STATUS`),
  KEY `IDX_GOAL_FUNC` (`FUNCTIONAL_LEAD_USER_ID`,`FUNC_STATUS`),
  KEY `IDX_GOAL_CEO` (`CEO_USER_ID`,`CEO_STATUS`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- goals
-- Individual goals inside a goal_sheet
-- Each goal belongs to a specific objective (BSC objective)
-- STATUS: Tracks overall goal lifecycle
-- YEAR_END_APPRAISAL_STATUS: Final appraisal outcome
-- Mid-year and Year-end remarks from: submitter, appraiser, reviewer
-- Self/Appraiser/Reviewer appraisal scores recorded here
-- FK: GOAL_SHEET_ID -> goal_sheets.ID (stored as varchar uuid-style)
-- FK: OBJECTIVE_ID -> objectives.ID [BSC module]
-- --------------------------------------------------------
CREATE TABLE `goals` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `GOAL_SHEET_ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'FK -> goal_sheets.ID (stored as string ref)',
  `OBJECTIVE_ID` int NOT NULL COMMENT 'FK -> objectives.ID (BSC module)',
  `GOAL_TITLE` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `DETAILS` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `ACTION_POINTS` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `MEASURABLE` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'How success is measured',
  `TIMELINE` date DEFAULT NULL COMMENT 'Original target date',
  `PROGRESS_NOTES` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `STATUS` text COMMENT 'e.g. On Track, At Risk, Completed',
  `SUBMITTER_REMARK` text,
  `YEAR_END_APPRAISAL_STATUS` text,
  `MID_YEAR_APPRAISER_REMARK` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `MID_YEAR_REVIEWER_REMARK` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `YEAR_END_APPRAISER_REMARK` text,
  `YEAR_END_REVIEWER_REMARK` text,
  `NEW_TIMELINE` date DEFAULT NULL COMMENT 'Revised target date if extended',
  `SELF_APPRAISAL` decimal(5,2) DEFAULT NULL,
  `APPRAISER_APPRAISAL` decimal(10,0) DEFAULT NULL,
  `REVIEWER_APPRAISAL` decimal(5,2) DEFAULT NULL,
  `DELETED` tinyint(1) DEFAULT '0',
  `CREATED_AT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ============================================================
-- GOAL SHEET MODULE - TABLE RELATIONSHIPS
--
-- goal_sheets (1) -> (*) goals
-- goal_sheets.SUBMITTER_USER_ID -> users.USER_ID
-- goal_sheets.FUNCTIONAL_LEAD_USER_ID -> users.USER_ID
-- goal_sheets.CEO_USER_ID -> users.USER_ID
-- goals.OBJECTIVE_ID -> objectives.ID  [BSC Module]
--
-- WORKFLOW:
--   RM/FL creates goal_sheet (DRAFT)
--   Submits -> Functional Lead reviews -> Approves/Rejects
--   CEO reviews -> Approves/Rejects
--   Mid-year review: appraiser fills mid-year remarks
--   Year-end review: self + appraiser + reviewer scores
-- ============================================================
