-- ============================================================
-- OREMUS - TIMESHEET MODULE TABLES
-- Database: Oremus
-- Dependencies: Shared (organizations, users, departments)
-- ============================================================

-- --------------------------------------------------------
-- billing_types  (legacy - older version)
-- Simple billing category lookup
-- --------------------------------------------------------
CREATE TABLE `billing_types` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `NAME` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `DESCRIPTION` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `NAME` (`NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- BILLING_TYPE_MASTER  (current - replaces billing_types)
-- Billing category used for task classification
-- Tracks active/soft-delete lifecycle
-- --------------------------------------------------------
CREATE TABLE `BILLING_TYPE_MASTER` (
  `BILLING_TYPE_ID` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `BILLING_TYPE_NAME` varchar(100) NOT NULL,
  `DESCRIPTION` varchar(255) DEFAULT NULL,
  `IS_ACTIVE` tinyint(1) NOT NULL DEFAULT '1',
  `CREATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `DELETED` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`BILLING_TYPE_ID`),
  UNIQUE KEY `UK_BILLING_TYPE_NAME` (`BILLING_TYPE_NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;



-- --------------------------------------------------------
-- tasks  (Timesheet tasks - work items billed to clients)
-- Billing type categorizes each task
-- FK: BILLING_TYPE -> billing_types.ID
-- FK: CREATED_BY -> organizations.ID
-- --------------------------------------------------------
CREATE TABLE `tasks` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `BILLING_TYPE` int NOT NULL COMMENT 'FK -> billing_types.ID',
  `TITILE` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Note: typo in column name retained for backward compat',
  `DESCRIPTION` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `STATUS` enum('pending','in-progress','completed') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'pending',
  `CREATED_AT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `CREATED_BY` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `DELETED` int DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `fk_clients` (`BILLING_TYPE`),
  KEY `CREATED_BY` (`CREATED_BY`),
  CONSTRAINT `tasks_ibfk_1` FOREIGN KEY (`CREATED_BY`) REFERENCES `organizations` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- timesheets
-- Daily time logs: user + task + client + hours per date
-- FLAG: 0=active, 1=replaced (when corrected via REPLACED_BY_ID)
-- FK: USER_ID -> users.USER_ID
-- FK: TASK_REF_ID -> tasks.ID
-- FK: DEPARTMENT_REF_ID -> departments.ID
-- FK: CLIENT_REF_ID -> clients.ID
-- --------------------------------------------------------
CREATE TABLE `timesheets` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `USER_ID` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'FK -> users.USER_ID',
  `TASK_REF_ID` int NOT NULL COMMENT 'FK -> tasks.ID',
  `DEPARTMENT_REF_ID` int NOT NULL COMMENT 'FK -> departments.ID',
  `CLIENT_REF_ID` int NOT NULL COMMENT 'FK -> clients.ID',
  `HOURS` double NOT NULL,
  `DATE` date NOT NULL,
  `CREATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `CREATED_BY` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `FLAG` int NOT NULL DEFAULT '0' COMMENT '0=active, 1=superseded by REPLACED_BY_ID',
  `REPLACED_BY_ID` int DEFAULT NULL COMMENT 'Points to new corrected timesheet entry',
  `DELETED` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- timesheet_submissions
-- Weekly submission records for approval workflow
-- Unique constraint: one submission per user per week
-- Status lifecycle: pending -> approved | rejected
-- AUTO_APPROVED: 1 when system auto-approved via cron
-- FK: USER_ID -> users.USER_ID
-- FK: REVIEWER_ID -> users.USER_ID
-- --------------------------------------------------------
CREATE TABLE `timesheet_submissions` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `USER_ID` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'FK -> users.USER_ID',
  `WEEK_START_DATE` date NOT NULL,
  `WEEK_END_DATE` date NOT NULL,
  `STATUS` enum('pending','approved','rejected','') DEFAULT 'pending',
  `COMMENTS` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci COMMENT 'Reviewer comment on approve/reject',
  `REVIEWER_ID` char(36) NOT NULL COMMENT 'FK -> users.USER_ID (manager)',
  `SUBMITTED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `AUTO_APPROVED` tinyint NOT NULL DEFAULT '0' COMMENT '1 = auto-approved by cron job',
  `CREATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `USER_ID` (`USER_ID`,`WEEK_START_DATE`,`WEEK_END_DATE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- sheet_names
-- Lookup for timesheet sheet name labels (from Excel uploads)
-- --------------------------------------------------------
CREATE TABLE `sheet_names` (
  `SHEET_NAME` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ============================================================
-- TIMESHEET MODULE - TABLE RELATIONSHIPS
--
-- billing_types (1) -> (*) tasks
-- clients (1) -> (*) timesheets
-- tasks (1) -> (*) timesheets
-- departments (1) -> (*) timesheets
-- users (1) -> (*) timesheets
-- users (1) -> (*) timesheet_submissions
--
-- WORKFLOW:
--   Employee logs time daily in `timesheets`
--   End of week: submits via `timesheet_submissions`
--   Manager reviews: approved / rejected
--   Cron job auto-approves if no action within N days
-- ============================================================
