-- ============================================================
-- OREMUS - TTM (TASK TEMPLATE MANAGEMENT) MODULE
-- Complete Schema v2.0
-- Database: Oremus
-- Based on: TTM2.0_for_Oremus_Updated requirement document
--
-- Cross-module dependencies:
--   users         (Shared)       - all employee references
--   departments   (Shared)       - function department owners
--   clients       (Timesheet)    - CLIENT_ID in TTM_TASK
--   CONTRACT      (CRM)          - CONTRACT_ID in TTM_TASK
--
-- TABLE COUNT: 26 tables
--   Section 1:  8  Master / Lookup tables
--   Section 2:  2  Template tables  (CRM creates blueprints)
--   Section 3:  1  Client-FL mapping  (CEO maps)
--   Section 4:  5  Task execution tables
--   Section 5:  3  Document management
--   Section 6:  2  Comments & external email
--   Section 7:  2  Delay management
--   Section 8:  3  Transfer management
--   Section 9:  2  Change request workflows
--   Section 10: 1  Client portal session
--   Section 11: 1  Notification log
--   Section 12: 1  Immutable audit log  (SOC 2)
-- ============================================================


-- ============================================================
-- SECTION 1: MASTER / LOOKUP TABLES
-- ============================================================

-- --------------------------------------------------------
-- 1.1  TTM_TASK_TYPE_MASTER
-- Four task types per business requirement:
--   FIXED           Auto-generated on a recurring schedule (e.g. monthly payroll)
--   VARIABLE        One-off, triggered manually by FL when needed
--   FIXED_VARIABLE  Recurring but each instance date is event-driven (e.g. board meetings)
--   GENERAL         Simple task, no sequential multi-function workflow
-- --------------------------------------------------------
CREATE TABLE `TTM_TASK_TYPE_MASTER` (
  `ID`          int          NOT NULL AUTO_INCREMENT,
  `TYPE_NAME`   varchar(50)  NOT NULL COMMENT 'Fixed | Variable | Fixed Variable | General',
  `TYPE_CODE`   varchar(50)  NOT NULL,
  `DESCRIPTION` varchar(255) DEFAULT NULL,
  `SORT_ORDER`  int          DEFAULT '0',
  `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`),
  UNIQUE KEY `UQ_TYPE_CODE` (`TYPE_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- 1.2  TTM_RECURRENCE_RULE_MASTER
-- Frequency options for FIXED and FIXED_VARIABLE tasks.
-- INTERVAL_MONTHS: NULL for weekly/biweekly; otherwise number of months between instances.
-- RECURRENCE_DAY_OF_MONTH: which calendar day triggers auto-generation (1-28).
-- --------------------------------------------------------
CREATE TABLE `TTM_RECURRENCE_RULE_MASTER` (
  `ID`                     int          NOT NULL AUTO_INCREMENT,
  `RULE_NAME`              varchar(50)  NOT NULL COMMENT 'Weekly | Fortnightly | Monthly | Quarterly | Half-Yearly | Yearly',
  `RULE_CODE`              varchar(50)  NOT NULL,
  `DESCRIPTION`            varchar(255) DEFAULT NULL,
  `INTERVAL_MONTHS`        int          DEFAULT NULL COMMENT 'NULL=weekly; 1=monthly; 3=quarterly; 6=half-yearly; 12=yearly',
  `RECURRENCE_DAY_OF_MONTH`int          DEFAULT NULL COMMENT 'Auto-generate on this day (1-28). NULL for weekly.',
  `SORT_ORDER`             int          DEFAULT '0',
  `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`),
  UNIQUE KEY `UQ_RULE_CODE` (`RULE_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- 1.3  TTM_PRIORITY_MASTER
-- Auto-escalated by cron based on days to CLIENT_DEADLINE:
--   LOW      15+ days  (Green)
--   MEDIUM   7-14 days (Blue)
--   HIGH     3-6 days  (Orange)
--   CRITICAL 1-2 days or already delayed (Red)
-- --------------------------------------------------------
CREATE TABLE `TTM_PRIORITY_MASTER` (
  `ID`                  int          NOT NULL AUTO_INCREMENT,
  `PRIORITY_NAME`       varchar(50)  NOT NULL,
  `PRIORITY_CODE`       varchar(50)  NOT NULL,
  `DAYS_THRESHOLD_FROM` int          DEFAULT NULL COMMENT 'Lower bound days to deadline (inclusive)',
  `DAYS_THRESHOLD_TO`   int          DEFAULT NULL COMMENT 'Upper bound days to deadline (inclusive); NULL = delayed',
  `COLOR_HEX`           varchar(10)  DEFAULT NULL COMMENT 'UI color e.g. #28a745',
  `DESCRIPTION`         varchar(255) DEFAULT NULL,
  `SORT_ORDER`          int          DEFAULT '0',
  `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`),
  UNIQUE KEY `UQ_PRIORITY_CODE` (`PRIORITY_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- 1.4  TTM_TASK_STATUS_MASTER
-- Overall task lifecycle status (task-level, not function-level).
--   PENDING_TEAM   Task created by CRM/cron, FL has not assigned team yet
--   ACTIVE         FL assigned team, work is in progress
--   COMPLETED      All functions completed, deliverables sent to client
--   DEACTIVATED    Service cancelled, no new instances will generate
--   ARCHIVED       Past retention period, moved to read-only archive
--   OVERDUE        Client deadline has passed and task not yet completed
-- --------------------------------------------------------
CREATE TABLE `TTM_TASK_STATUS_MASTER` (
  `ID`            int          NOT NULL AUTO_INCREMENT,
  `STATUS_CODE`   varchar(50)  NOT NULL,
  `STATUS_NAME`   varchar(100) NOT NULL,
  `DESCRIPTION`   varchar(255) DEFAULT NULL,
  `IS_TERMINAL`   tinyint(1)   NOT NULL DEFAULT '0' COMMENT '1 = no further transitions allowed from this status',
  `DISPLAY_ORDER` int          DEFAULT '0',
  `CREATED_AT`    timestamp    NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT`    timestamp    NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UQ_TASK_STATUS_CODE` (`STATUS_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- 1.5  TTM_FUNCTION_STATUS_MASTER
-- Per-function workflow status (function-level, not task-level).
-- Valid progression:
--   NOT_STARTED -> IN_PROGRESS -> SUBMITTED_FOR_REVIEW
--   SUBMITTED_FOR_REVIEW -> UNDER_REVIEW
--   UNDER_REVIEW -> APPROVED (all reviewers approved) -> COMPLETED
--   UNDER_REVIEW -> REJECTED -> REWORK_REQUIRED -> PA resubmits
--   UNDER_REVIEW -> CLARIFICATION_PENDING -> PA answers -> UNDER_REVIEW again
-- --------------------------------------------------------
CREATE TABLE `TTM_FUNCTION_STATUS_MASTER` (
  `ID`            int          NOT NULL AUTO_INCREMENT,
  `STATUS_CODE`   varchar(60)  NOT NULL,
  `STATUS_NAME`   varchar(100) NOT NULL,
  `DESCRIPTION`   varchar(255) DEFAULT NULL,
  `IS_TERMINAL`   tinyint(1)   NOT NULL DEFAULT '0' COMMENT '1 = COMPLETED / terminal state',
  `DISPLAY_ORDER` int          DEFAULT '0',
  `CREATED_AT`    timestamp    NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT`    timestamp    NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UQ_FUNC_STATUS_CODE` (`STATUS_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- 1.6  TTM_REVIEW_STATUS_MASTER
-- Reviewer-level decision status (per TTM_FUNCTION_REVIEWER row).
--   PENDING                  Reviewer assigned, has not acted yet
--   APPROVED                 Reviewer approved the work
--   REJECTED                 Reviewer rejected - rework required
--   CLARIFICATION_REQUESTED  Reviewer needs more info before deciding
-- --------------------------------------------------------
CREATE TABLE `TTM_REVIEW_STATUS` (
  `ID`            int          NOT NULL AUTO_INCREMENT,
  `STATUS_CODE`   varchar(50)  NOT NULL,
  `STATUS_NAME`   varchar(100) NOT NULL,
  `DESCRIPTION`   varchar(255) DEFAULT NULL,
  `DISPLAY_ORDER` int          DEFAULT '0',
  `ACTIVE`        tinyint(1)   DEFAULT '1',
  `CREATED_AT`    timestamp    NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT`    timestamp    NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UQ_REVIEW_STATUS_CODE` (`STATUS_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- 1.7  TTM_TEMPLATE_STATUS_MASTER
-- Lifecycle status of a task template itself.
--   DRAFT     Being designed by CRM, not yet usable
--   ACTIVE    Published, CRM/cron can create tasks from it
--   INACTIVE  Temporarily disabled (no new tasks, existing tasks continue)
--   ARCHIVED  Permanently retired
-- --------------------------------------------------------
CREATE TABLE `TTM_TEMPLATE_STATUS_MASTER` (
  `ID`            int          NOT NULL AUTO_INCREMENT,
  `STATUS_CODE`   varchar(50)  NOT NULL,
  `STATUS_NAME`   varchar(50)  NOT NULL,
  `DESCRIPTION`   varchar(255) 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`),
  UNIQUE KEY `UQ_TMPL_STATUS_CODE` (`STATUS_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- 1.8  TTM_DELAY_CATEGORY_MASTER
-- Selectable categories when logging a delay reason.
-- ALLOWS_MANUAL_TEXT = 1 means free-text mandatory alongside selection.
-- --------------------------------------------------------
CREATE TABLE `TTM_DELAY_CATEGORY_MASTER` (
  `ID`                  int          NOT NULL AUTO_INCREMENT,
  `CATEGORY_CODE`       varchar(60)  NOT NULL,
  `CATEGORY_NAME`       varchar(150) NOT NULL COMMENT 'e.g. Awaiting client input, Technical issues, Complexity underestimated, Reviewer delay, Other',
  `ALLOWS_MANUAL_TEXT`  tinyint(1)   NOT NULL DEFAULT '1' COMMENT '1 = user must also type free-text explanation',
  `SORT_ORDER`          int          DEFAULT '0',
  `DELETED`             tinyint(1)   DEFAULT '0',
  `CREATED_AT`          timestamp    NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UQ_DELAY_CAT_CODE` (`CATEGORY_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ============================================================
-- SECTION 2: TEMPLATE TABLES  (CRM / Admin creates these)
-- Generic, reusable blueprints — not client-specific.
-- One template serves ALL clients who need that service.
-- ============================================================

-- --------------------------------------------------------
-- 2.1  TTM_TASK_TEMPLATE
-- Top-level blueprint for a service-based recurring or one-off task.
-- TEMPLATE_CODE: CRM-assigned human-readable code e.g. TPL-PAY-001
-- SERVICE_NAME:  Maps to engagement type (e.g. "Payroll Services")
-- TASK_TYPE_ID:  Fixed / Variable / Fixed Variable / General
-- RECURRENCE_RULE_ID: NULL for Variable & General; set for Fixed / Fixed Variable
-- DEFAULT_DEADLINE_OFFSET_DAYS: for Fixed tasks, client deadline =
--   generation date + this value (can be overridden per task instance)
-- ACTIVE:  0 = template inactive (no new tasks generated)
-- --------------------------------------------------------
CREATE TABLE `TTM_TASK_TEMPLATE` (
  `ID`                          bigint       NOT NULL AUTO_INCREMENT,
  `TEMPLATE_CODE`               varchar(50)  NOT NULL COMMENT 'e.g. TPL-PAY-001',
  `TEMPLATE_NAME`               varchar(100) NOT NULL COMMENT 'e.g. Monthly Payroll',
  `SERVICE_NAME`                varchar(255) NOT NULL COMMENT 'Maps to CRM engagement type e.g. Payroll Services',
  `ENGAGEMENT_TYPE_ID`          int          DEFAULT NULL COMMENT 'FK -> ENGAGEMENT_TYPE_MASTER.ID — links task to CRM engagement type',
  `TASK_TYPE_ID`                int          DEFAULT NULL COMMENT 'FK -> TTM_TASK_TYPE_MASTER.ID',
  `STATUS_ID`                   int          DEFAULT NULL COMMENT 'FK -> TTM_TEMPLATE_STATUS_MASTER.ID',
  `ACTIVE`                      tinyint(1)   DEFAULT '1',
  `DELETED`                     tinyint(1)   NOT NULL DEFAULT '0',
  `CREATED_BY_EMP_REF_ID`       bigint       NOT NULL COMMENT 'FK -> users.ID (CRM/Admin)',
  `CREATED_AT`                  datetime     DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT`                  datetime     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UQ_TEMPLATE_CODE` (`TEMPLATE_CODE`),
  KEY `IDX_TASK_TYPE` (`TASK_TYPE_ID`),
  KEY `idx_engagement_type` (`ENGAGEMENT_TYPE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- 2.2  TTM_TEMPLATE_FUNCTION
-- Sequential stages (functions) inside a task template.
-- SEQ_NO:              Execution order. Function N+1 starts only after N completes.
-- DEPT_REF_ID:         Department that owns / executes this function.
-- INITIATOR_ROLE:      'PA' or 'TL' — who will do the actual work.
--                      From 2nd function onward, no separate initiator is needed
--                      (previous function's last reviewer auto-triggers next).
-- MIN_REVIEW_COUNT:    Minimum number of reviewers that must approve.
--                      FL can increase but never decrease this at task-assignment time.
-- REVIEW_LEVELS_JSON:  Ordered JSON array of who may review this function.
--                      e.g. ["TL","RM","FL","CEO"]
--                      Used to validate reviewer assignment and ordering.
-- TYPICAL_DURATION_DAYS: Standard days for this function; used to back-calculate
--                      function deadlines from CLIENT_DEADLINE.
-- --------------------------------------------------------
CREATE TABLE `TTM_TEMPLATE_FUNCTION` (
  `ID`                    bigint       NOT NULL AUTO_INCREMENT,
  `TEMPLATE_ID`           bigint       NOT NULL COMMENT 'FK -> TTM_TASK_TEMPLATE.ID',
  `SEQ_NO`                int          NOT NULL COMMENT 'Execution order (1, 2, 3…)',
  `FUNCTION_NAME`         varchar(255) NOT NULL COMMENT 'e.g. Data Collection, Salary Calculation',
  `DEPT_REF_ID`           bigint       DEFAULT NULL COMMENT 'FK -> departments.ID (department that owns this stage)',
  `INITIATOR_ROLE`        varchar(20)  DEFAULT 'PA' COMMENT 'PA | TL | RM (who executes work; null from 2nd function)',
  `MIN_REVIEW_COUNT`      int          DEFAULT '1' COMMENT 'Minimum reviewers needed. FL may raise, not lower.',
  `REVIEW_LEVELS_JSON`    json         DEFAULT NULL COMMENT 'Ordered array e.g. ["TL","RM","FL","CEO"]',
  `TYPICAL_DURATION_DAYS` int          DEFAULT '1' COMMENT 'Used to back-calculate function deadlines from CLIENT_DEADLINE',
  `ACTIVE`                tinyint(1)   DEFAULT '1',
  `DELETED`               tinyint(1)   NOT NULL DEFAULT '0',
  `CREATED_AT`            datetime     DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT`            datetime     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `IDX_TEMPLATE_ID` (`TEMPLATE_ID`),
  KEY `IDX_TEMPLATE_SEQ` (`TEMPLATE_ID`, `SEQ_NO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ============================================================
-- SECTION 3: CLIENT-FL MAPPING  (CEO creates this)
-- CEO maps specific clients to specific Functional Leads.
-- Determines which FL receives auto-generated / manual tasks.
-- ============================================================

-- --------------------------------------------------------
-- 3.1  TTM_CLIENT_FL_MAPPING
-- CEO maps CLIENT → FL (optionally per service/template).
-- One client can have multiple FL mappings for different services:
--   Tech Innovations → FL Suresh for Payroll
--   Tech Innovations → FL John for Accounting
-- TEMPLATE_ID NULL means FL handles ALL templates for this client.
-- All future FIXED tasks for this client auto-route to the mapped FL.
-- --------------------------------------------------------
CREATE TABLE `TTM_CLIENT_FL_MAPPING` (
  `ID`                      bigint       NOT NULL AUTO_INCREMENT,
  `CLIENT_ID`               bigint       NOT NULL COMMENT 'FK -> CONTRACT.CONTRACT_ID (CRM contract)',
  `CONTRACT_DOCUMENT_ID`    bigint       UNSIGNED DEFAULT NULL COMMENT 'FK -> CONTRACT_DOCUMENT.DOCUMENT_ID — the CRM document that triggered this mapping',
  `FL_EMP_REF_ID`           bigint       NOT NULL COMMENT 'FK -> users.ID (Functional Lead)',
  `TEMPLATE_ID`             bigint       DEFAULT NULL COMMENT 'FK -> TTM_TASK_TEMPLATE.ID; NULL = all templates for this client',
  `SERVICE_NAME`            varchar(255) DEFAULT NULL COMMENT 'Denormalized for quick display',
  `ENGAGEMENT_TYPE_IDS`     text         DEFAULT NULL COMMENT 'Comma-separated ENGAGEMENT_TYPE_MASTER IDs, e.g. "1,3,5"',
  `DEPT_REF_ID`             bigint       DEFAULT NULL COMMENT 'FK -> departments.ID (FL home department)',
  `MAPPED_BY_EMP_REF_ID`    bigint       NOT NULL COMMENT 'FK -> users.ID (CEO who created mapping)',
  `ACTIVE`                  tinyint(1)   NOT NULL DEFAULT '1',
  `TASK_ASSIGNED`           tinyint(1)   NOT NULL DEFAULT '0' COMMENT '1 = tasks have been mapped/submitted for this client-document',
  `EFFECTIVE_FROM`          date         DEFAULT NULL COMMENT 'When this mapping becomes active',
  `EFFECTIVE_UNTIL`         date         DEFAULT NULL COMMENT 'NULL = indefinite; set when FL changes',
  `DELETED`                 tinyint(1)   NOT NULL DEFAULT '0',
  `CREATED_AT`              datetime     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT`              datetime     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `IDX_CLIENT_MAPPING` (`CLIENT_ID`, `ACTIVE`),
  KEY `IDX_FL_MAPPING` (`FL_EMP_REF_ID`, `ACTIVE`),
  KEY `IDX_CLIENT_TEMPLATE` (`CLIENT_ID`, `TEMPLATE_ID`),
  KEY `IDX_CONTRACT_DOC` (`CONTRACT_DOCUMENT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- 3.2  TTM_CLIENT_TASK_MAPPING
-- Admin maps task templates to a client after all FL mappings
-- for a contract document are active.
-- Flow: DRAFT → SUBMITTED (creates real TTM_TASK instances)
-- --------------------------------------------------------
CREATE TABLE `TTM_CLIENT_TASK_MAPPING` (
  `ID`                      bigint       NOT NULL AUTO_INCREMENT,
  `CLIENT_ID`               bigint       NOT NULL COMMENT 'FK -> CONTRACT.CONTRACT_ID (CRM contract)',
  `CONTRACT_DOCUMENT_ID`    bigint       DEFAULT NULL COMMENT 'FK -> CONTRACT_DOCUMENT.DOCUMENT_ID',
  `TEMPLATE_ID`             bigint       NOT NULL COMMENT 'FK -> TTM_TASK_TEMPLATE.ID',
  `ENGAGEMENT_TYPE_ID`      int          DEFAULT NULL COMMENT 'FK -> ENGAGEMENT_TYPE_MASTER.ID',
  `RECURRENCE_RULE_ID`      int          DEFAULT NULL COMMENT 'FK -> TTM_RECURRENCE_RULE_MASTER.ID — selected by admin at mapping time',
  `CLIENT_DEADLINE`         date         DEFAULT NULL COMMENT 'Admin-set deadline for this task',
  `STATUS`                  varchar(20)  NOT NULL DEFAULT 'DRAFT' COMMENT 'DRAFT = editable; SUBMITTED = task created',
  `TASK_ID`                 bigint       DEFAULT NULL COMMENT 'FK -> TTM_TASK.ID — populated when submitted',
  `CREATED_BY_EMP_REF_ID`   bigint       NOT NULL COMMENT 'FK -> users.ID (Admin who mapped)',
  `DELETED`                 tinyint(1)   NOT NULL DEFAULT '0',
  `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 `UQ_CLIENT_TEMPLATE` (`CLIENT_ID`, `TEMPLATE_ID`, `DELETED`),
  KEY `IDX_CLIENT` (`CLIENT_ID`),
  KEY `IDX_STATUS` (`STATUS`),
  KEY `IDX_TEMPLATE` (`TEMPLATE_ID`),
  KEY `IDX_CONTRACT_DOC` (`CONTRACT_DOCUMENT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ============================================================
-- SECTION 4: TASK EXECUTION TABLES
-- The actual work objects created from templates per client.
-- ============================================================

-- --------------------------------------------------------
-- 4.1  TTM_TASK
-- A task instance: one template + one client + one period.
-- Generated from TTM_TASK_TEMPLATE:
--   FIXED         → auto-generated by cron on RECURRENCE_DAY_OF_MONTH
--   VARIABLE      → FL requests; CRM approves and creates instance
--   FIXED_VARIABLE→ FL manually triggers when event occurs
--   GENERAL       → CRM creates directly
-- TASK_CODE:       Unique code, format TSK-YYYYMMDD-XXXXX
-- TASK_PERIOD:     Human label for the period e.g. "April 2025"
-- CLIENT_DEADLINE: IMMOVABLE unless CRM formally approves a change request.
--                  Mandatory 1-day buffer: last function deadline ≤ CLIENT_DEADLINE - 1
-- STATUS_CODE:     Tracks overall task lifecycle (mirrors TTM_TASK_STATUS_MASTER codes)
-- PRIORITY_CODE:   Auto-escalated daily by cron from TTM_PRIORITY_MASTER rules
-- PROGRESS_PERCENT:Denormalized; updated when functions complete
--                  = (completed_functions / total_functions) × 100
-- --------------------------------------------------------
CREATE TABLE `TTM_TASK` (
  `ID`                      bigint        NOT NULL AUTO_INCREMENT,
  `TASK_CODE`               varchar(60)   NOT NULL COMMENT 'Unique code e.g. TSK-20250401-00001',
  `TEMPLATE_ID`             bigint        NOT NULL COMMENT 'FK -> TTM_TASK_TEMPLATE.ID',
  `CLIENT_ID`               bigint        NOT NULL COMMENT 'FK -> clients.ID (Timesheet clients table)',
  `CONTRACT_ID`             bigint        DEFAULT NULL COMMENT 'FK -> CONTRACT.CONTRACT_ID (CRM contract)',
  `RECURRENCE_RULE_ID`      int           DEFAULT NULL COMMENT 'FK -> TTM_RECURRENCE_RULE_MASTER.ID — from client-task mapping',
  `TASK_PERIOD`             varchar(50)   DEFAULT NULL COMMENT 'e.g. "April 2025", "Q1 2025" — for display',
  `ASSIGNED_FL_EMP_REF_ID`  bigint        DEFAULT NULL COMMENT 'FK -> users.ID (Functional Lead who manages this task)',
  `CLIENT_DEADLINE`         date          NOT NULL COMMENT 'Immovable client-facing deadline. Change requires CRM approval.',
  `STATUS_CODE`             varchar(60)   NOT NULL DEFAULT 'PENDING_TEAM' COMMENT 'PENDING_TEAM|ACTIVE|COMPLETED|DEACTIVATED|ARCHIVED|OVERDUE',
  `PRIORITY_CODE`           varchar(50)   DEFAULT 'LOW' COMMENT 'AUTO-ESCALATED by cron: LOW|MEDIUM|HIGH|CRITICAL',
  `PROGRESS_PERCENT`        decimal(5,2)  DEFAULT '0.00' COMMENT 'Updated by system when functions complete (0-100)',
  `IS_DELAYED`              tinyint(1)    NOT NULL DEFAULT '0' COMMENT '1 = any function is past its deadline',
  `AUTO_GENERATED`          tinyint(1)    NOT NULL DEFAULT '0' COMMENT '1 = created by cron (Fixed); 0 = created by CRM/FL',
  `DELETED`                 tinyint(1)    NOT NULL DEFAULT '0',
  `CREATED_BY_EMP_REF_ID`   bigint        NOT NULL COMMENT 'FK -> users.ID (CRM/Admin or system)',
  `CREATED_AT`              datetime      DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT`              datetime      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UQ_TASK_CODE` (`TASK_CODE`),
  KEY `IDX_CLIENT_TASK` (`CLIENT_ID`, `STATUS_CODE`),
  KEY `IDX_FL_TASK` (`ASSIGNED_FL_EMP_REF_ID`, `STATUS_CODE`),
  KEY `IDX_TEMPLATE_TASK` (`TEMPLATE_ID`),
  KEY `IDX_DEADLINE` (`CLIENT_DEADLINE`),
  KEY `IDX_STATUS` (`STATUS_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- 4.2  TTM_TASK_FUNCTION
-- An instance of each TTM_TEMPLATE_FUNCTION for a specific task.
-- Created in batch when FL assigns team to a task.
--
-- Lifecycle:
--   ACTIVE=0 → waiting for previous function to complete
--   ACTIVE=1 → this function is the current working stage
--
-- STATUS_CODE mirrors TTM_FUNCTION_STATUS_MASTER.STATUS_CODE:
--   NOT_STARTED → IN_PROGRESS → SUBMITTED_FOR_REVIEW → UNDER_REVIEW
--   UNDER_REVIEW → COMPLETED  (all reviewers approved)
--   UNDER_REVIEW → REWORK_REQUIRED (any reviewer rejected)
--   UNDER_REVIEW → CLARIFICATION_PENDING
--
-- SUBMISSION_COUNT: how many times initiator submitted (tracks rework cycles)
-- REJECTION_COUNT:  how many times work was rejected
-- CLARIFICATION_COUNT: how many clarification rounds occurred
-- DELAY_DAYS:       incremented daily by cron when past DEADLINE and not COMPLETED
-- --------------------------------------------------------
CREATE TABLE `TTM_TASK_FUNCTION` (
  `ID`                      bigint        NOT NULL AUTO_INCREMENT,
  `TASK_ID`                 bigint        NOT NULL COMMENT 'FK -> TTM_TASK.ID',
  `TEMPLATE_FUNCTION_ID`    bigint        NOT NULL COMMENT 'FK -> TTM_TEMPLATE_FUNCTION.ID',
  `SEQ_NO`                  int           NOT NULL COMMENT 'Mirrors TTM_TEMPLATE_FUNCTION.SEQ_NO',
  `FUNCTION_NAME`           varchar(255)  NOT NULL COMMENT 'Copied from template at task creation time',
  `DEPT_REF_ID`             bigint        DEFAULT NULL COMMENT 'FK -> departments.ID',
  `INITIATOR_EMP_REF_ID`    bigint        DEFAULT NULL COMMENT 'FK -> users.ID (PA or TL assigned by FL to do the work)',
  `DEADLINE`                date          NOT NULL COMMENT 'Function deadline (sequential; must have 1-day buffer before CLIENT_DEADLINE on last function)',
  `STATUS_CODE`             varchar(60)   NOT NULL DEFAULT 'NOT_STARTED' COMMENT 'NOT_STARTED|IN_PROGRESS|SUBMITTED_FOR_REVIEW|UNDER_REVIEW|CLARIFICATION_PENDING|REWORK_REQUIRED|COMPLETED',
  `ACTIVE`                  tinyint(1)    NOT NULL DEFAULT '0' COMMENT '0=waiting for prev function; 1=current active stage',
  `STARTED_AT`              datetime      DEFAULT NULL COMMENT 'When initiator clicked Start Task',
  `SUBMITTED_AT`            datetime      DEFAULT NULL COMMENT 'Most recent submission timestamp',
  `COMPLETED_AT`            datetime      DEFAULT NULL COMMENT 'When all reviewers approved',
  `SUBMISSION_COUNT`        int           NOT NULL DEFAULT '0' COMMENT 'Total times initiator submitted (tracks rework cycles)',
  `REJECTION_COUNT`         int           NOT NULL DEFAULT '0' COMMENT 'Total times work was rejected by any reviewer',
  `CLARIFICATION_COUNT`     int           NOT NULL DEFAULT '0' COMMENT 'Total clarification rounds',
  `DELAY_DAYS`              int           NOT NULL DEFAULT '0' COMMENT 'Days past DEADLINE (incremented daily by cron)',
  `EXTENDED_DAY_COUNT`      int           NOT NULL DEFAULT '0' COMMENT 'Total days extension granted',
  `EXTENDED_TIMES_COUNT`    int           NOT NULL DEFAULT '0' COMMENT 'Number of extension events',
  `DELAY_REASON_RECORDED`   tinyint(1)    NOT NULL DEFAULT '0' COMMENT '1 = mandatory delay reason has been filed before proceeding',
  `DELETED`                 tinyint(1)    NOT NULL DEFAULT '0',
  `CREATED_AT`              datetime      DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT`              datetime      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `IDX_TASK_FUNC` (`TASK_ID`, `SEQ_NO`),
  KEY `IDX_INITIATOR` (`INITIATOR_EMP_REF_ID`),
  KEY `IDX_STATUS_CODE` (`STATUS_CODE`),
  KEY `IDX_DEADLINE` (`DEADLINE`),
  KEY `IDX_ACTIVE` (`ACTIVE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- 4.3  TTM_FUNCTION_REVIEWER
-- Reviewers assigned by FL to a specific task function instance.
-- Minimum count from template; FL may add more reviewers.
-- REVIEW_ORDER: determines sequence (1 reviews first, then 2, etc.)
-- Rule: Initiator cannot be a reviewer of the same function.
--       If TL becomes initiator (upward delegation), RM/FL must review.
-- KEY constraint: one active reviewer per (TASK_FUNCTION_ID, REVIEWER_EMP_REF_ID)
-- --------------------------------------------------------
CREATE TABLE `TTM_FUNCTION_REVIEWER` (
  `ID`                    bigint        NOT NULL AUTO_INCREMENT,
  `TASK_FUNCTION_ID`      bigint        NOT NULL COMMENT 'FK -> TTM_TASK_FUNCTION.ID',
  `REVIEW_ORDER`          int           NOT NULL DEFAULT '1' COMMENT 'Sequential review order within this function',
  `REVIEW_LEVEL`          varchar(20)   NOT NULL COMMENT 'TL | RM | FL | CEO',
  `REVIEWER_EMP_REF_ID`   bigint        NOT NULL COMMENT 'FK -> users.ID',
  `IS_REQUIRED`           tinyint(1)    DEFAULT '1' COMMENT '1 = mandatory (counts toward MIN_REVIEW_COUNT)',
  `REVIEW_STATUS_CODE`    varchar(50)   NOT NULL DEFAULT 'PENDING' COMMENT 'PENDING|APPROVED|REJECTED|CLARIFICATION_REQUESTED',
  `COMMENT`               text          DEFAULT NULL COMMENT 'Mandatory on REJECTED; optional on APPROVED/CLARIFICATION_REQUESTED',
  `CLARIFICATION_TEXT`    text          DEFAULT NULL COMMENT 'Populated when REVIEW_STATUS_CODE = CLARIFICATION_REQUESTED',
  `CLARIFICATION_REPLY`   text          DEFAULT NULL COMMENT 'Initiator reply to clarification request',
  `CLARIFICATION_AT`      datetime      DEFAULT NULL,
  `CLARIFICATION_REPLIED_AT` datetime   DEFAULT NULL,
  `ACTION_AT`             datetime      DEFAULT NULL COMMENT 'When final action (approve/reject) was taken',
  `EXTENDED_DEADLINE`     date          DEFAULT NULL COMMENT 'If this reviewer approved an extension to a new date',
  `DELETED`               tinyint(1)    NOT NULL DEFAULT '0',
  `CREATED_AT`            datetime      DEFAULT CURRENT_TIMESTAMP,
  KEY `IDX_TASK_FUNCTION_REVIEWER` (`TASK_FUNCTION_ID`, `REVIEW_ORDER`),
  KEY `IDX_REVIEWER_TASKS` (`REVIEWER_EMP_REF_ID`),
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- 4.4  TTM_SUBMISSION_LOG
-- Records each submit → review → decision cycle for a task function.
-- Critical for:
--   - Quality metrics (how many rounds did a function take?)
--   - Performance reviews (PA quality score over time)
--   - Audit trail (complete work history)
--   - Identifying patterns in rejections
-- CYCLE_NUMBER: 1 = first submission, 2 = after first rework, etc.
-- SUBMITTED_BY_EMP_REF_ID: always the initiator (PA/TL)
-- DECISION: final decision for this cycle (APPROVED / REJECTED)
-- DECISION_BY_EMP_REF_ID: reviewer who made final decision for this cycle
-- --------------------------------------------------------
CREATE TABLE `TTM_SUBMISSION_LOG` (
  `ID`                         bigint        NOT NULL AUTO_INCREMENT,
  `TASK_FUNCTION_ID`           bigint        NOT NULL COMMENT 'FK -> TTM_TASK_FUNCTION.ID',
  `CYCLE_NUMBER`               int           NOT NULL COMMENT '1=first submission, 2=after rework, etc.',
  `SUBMITTED_BY_EMP_REF_ID`    bigint        NOT NULL COMMENT 'FK -> users.ID (initiator)',
  `SUBMITTED_AT`               datetime      NOT NULL,
  `SUBMISSION_NOTE`            text          DEFAULT NULL COMMENT 'Initiator note on submission',
  `DOCUMENT_COUNT`             int           DEFAULT '0' COMMENT 'Number of files uploaded with this submission',
  `DECISION`                   enum('PENDING','APPROVED','REJECTED','CLARIFICATION') DEFAULT 'PENDING',
  `DECISION_BY_EMP_REF_ID`     bigint        DEFAULT NULL COMMENT 'FK -> users.ID (reviewer who closed this cycle)',
  `DECISION_AT`                datetime      DEFAULT NULL,
  `REJECTION_REASON`           text          DEFAULT NULL COMMENT 'Populated if DECISION = REJECTED',
  `CREATED_AT`                 datetime      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `IDX_SUB_LOG_FUNC` (`TASK_FUNCTION_ID`),
  KEY `IDX_SUB_LOG_SUBMITTER` (`SUBMITTED_BY_EMP_REF_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- 4.5  TTM_REVIEW_LOG
-- Granular log of every individual reviewer action.
-- Captures who reviewed, what they decided, and when —
-- even across multiple cycles of the same function.
-- Links to TTM_SUBMISSION_LOG.CYCLE_NUMBER for full context.
-- --------------------------------------------------------
CREATE TABLE `TTM_REVIEW_LOG` (
  `ID`                       bigint       NOT NULL AUTO_INCREMENT,
  `TASK_FUNCTION_REVIEWER_ID`bigint       NOT NULL COMMENT 'FK -> TTM_FUNCTION_REVIEWER.ID',
  `SUBMISSION_LOG_ID`        bigint       NOT NULL COMMENT 'FK -> TTM_SUBMISSION_LOG.ID (which cycle this belongs to)',
  `REVIEWER_EMP_REF_ID`      bigint       NOT NULL COMMENT 'FK -> users.ID',
  `DECISION`                 enum('APPROVED','REJECTED','CLARIFICATION_REQUESTED') NOT NULL,
  `COMMENT`                  text         DEFAULT NULL,
  `DECIDED_AT`               datetime     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `IDX_REVIEW_LOG_FUNC` (`TASK_FUNCTION_REVIEWER_ID`),
  KEY `IDX_REVIEW_LOG_SUB` (`SUBMISSION_LOG_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;






-- --------------------------------------------------------
-- 5.3  TTM_DOCUMENT_DOWNLOAD_LOG
-- Every document download recorded (internal team + client portal).
-- Complete audit trail: who, when, from where.
-- ENTITY_TYPE: USER = internal employee; CLIENT = portal client.
-- --------------------------------------------------------
CREATE TABLE `TTM_DOCUMENT_DOWNLOAD_LOG` (
  `ID`              bigint        NOT NULL AUTO_INCREMENT,
  `DOCUMENT_ID`     bigint        NOT NULL COMMENT 'FK -> TTM_TASK_DOCUMENT.ID',
  `ENTITY_TYPE`     enum('USER','CLIENT') NOT NULL COMMENT 'Who downloaded',
  `ENTITY_REF_ID`   bigint        NOT NULL COMMENT 'FK -> users.ID (if USER) or clients.ID (if CLIENT)',
  `DOWNLOADED_AT`   datetime      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `IP_ADDRESS`      varchar(64)   DEFAULT NULL,
  `DEVICE_INFO`     varchar(255)  DEFAULT NULL COMMENT 'Browser / OS / device string',
  PRIMARY KEY (`ID`),
  KEY `IDX_DOWNLOG_DOC` (`DOCUMENT_ID`),
  KEY `IDX_DOWNLOG_ENTITY` (`ENTITY_TYPE`, `ENTITY_REF_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ============================================================
-- SECTION 6: COMMENTS & EXTERNAL EMAIL
-- Two comment visibility scopes:
--   INTERNAL → Oremus team only (sensitive operational notes)
--   PUBLIC   → Client-visible in portal; only FL can post
-- External emails: FL/RM/CRM can send client emails from within TTM.
-- ============================================================

-- --------------------------------------------------------
-- 6.1  TTM_TASK_COMMENT
-- Comments on tasks or specific functions.
-- COMMENT_TYPE:
--   INTERNAL  Visible to Oremus team only (PA, TL, RM, FL, CRM, CEO)
--   PUBLIC    Visible to client in portal; ONLY FL can create these
-- TASK_FUNCTION_ID: null = task-level comment; set = function-level comment
-- PARENT_COMMENT_ID: null = root comment; set = threaded reply
-- --------------------------------------------------------
CREATE TABLE `TTM_TASK_COMMENT` (
  `ID`                       bigint        NOT NULL AUTO_INCREMENT,
  `TASK_ID`                  bigint        NOT NULL COMMENT 'FK -> TTM_TASK.ID',
  `TASK_FUNCTION_ID`         bigint        DEFAULT NULL COMMENT 'FK -> TTM_TASK_FUNCTION.ID (null = task-level)',
  `COMMENT_TYPE`             enum('INTERNAL','PUBLIC') NOT NULL DEFAULT 'INTERNAL',
  `TEXT`                     text          NOT NULL,
  `ATTACHMENT_URL`           varchar(500)  DEFAULT NULL COMMENT 'Optional file attached to comment',
  `PARENT_COMMENT_ID`        bigint        DEFAULT NULL COMMENT 'FK -> TTM_TASK_COMMENT.ID (for threaded replies)',
  `CREATED_BY_EMP_REF_ID`    bigint        NOT NULL COMMENT 'FK -> users.ID',
  `DELETED`                  tinyint(1)    NOT NULL DEFAULT '0',
  `CREATED_AT`               datetime      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT`               datetime      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `IDX_COMMENT_TASK` (`TASK_ID`, `COMMENT_TYPE`),
  KEY `IDX_COMMENT_FUNC` (`TASK_FUNCTION_ID`),
  KEY `IDX_COMMENT_PARENT` (`PARENT_COMMENT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- 6.2  TTM_EMAIL_LOG
-- External emails sent to clients from within TTM.
-- Who can send: FL (full), RM (with FL delegation), CRM (admin emails).
-- All emails are attached to a task for full audit trail.
-- RECIPIENT_EMAILS: JSON array (CFO, Accounts Manager, etc.)
-- --------------------------------------------------------
CREATE TABLE `TTM_EMAIL_LOG` (
  `ID`                    bigint        NOT NULL AUTO_INCREMENT,
  `TASK_ID`               bigint        NOT NULL COMMENT 'FK -> TTM_TASK.ID',
  `SENT_BY_EMP_REF_ID`    bigint        NOT NULL COMMENT 'FK -> users.ID (FL/RM/CRM sender)',
  `RECIPIENT_EMAILS`      json          NOT NULL COMMENT 'JSON array of recipient email strings',
  `SUBJECT`               varchar(500)  NOT NULL,
  `BODY`                  text          NOT NULL,
  `EMAIL_TYPE`            enum(
                            'CLIENT_UPDATE',
                            'CLARIFICATION_REQUEST',
                            'DEADLINE_EXTENSION_REQUEST',
                            'TASK_COMPLETION',
                            'GENERAL'
                          ) NOT NULL DEFAULT 'GENERAL',
  `STATUS`                enum('QUEUED','SENT','FAILED') NOT NULL DEFAULT 'QUEUED',
  `ERROR_MESSAGE`         text          DEFAULT NULL COMMENT 'Populated when STATUS = FAILED',
  `SENT_AT`               datetime      DEFAULT NULL,
  `CREATED_AT`            datetime      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `IDX_EMAIL_LOG_TASK` (`TASK_ID`),
  KEY `IDX_EMAIL_LOG_SENDER` (`SENT_BY_EMP_REF_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ============================================================
-- SECTION 7: DELAY MANAGEMENT
-- System detects daily: DEADLINE < TODAY AND STATUS != COMPLETED
-- Initiator must file delay reason before proceeding with work.
-- Escalation matrix: 1-2d (FL monitor), 3-5d (fast-track),
--   6-9d (urgent intervention), 10+ (crisis mode / CEO involved)
-- ============================================================

-- --------------------------------------------------------
-- 7.1  TTM_DELAY_REASON
-- Mandatory record filed by initiator when function is past deadline.
-- System BLOCKS submission until this record exists for the function.
-- Immutable once submitted (cannot edit/delete for audit integrity).
-- DETAILS: minimum 100 characters (configurable; client wants 200).
-- --------------------------------------------------------
CREATE TABLE `TTM_DELAY_REASON` (
  `ID`                         bigint        NOT NULL AUTO_INCREMENT,
  `TASK_FUNCTION_ID`           bigint        NOT NULL COMMENT 'FK -> TTM_TASK_FUNCTION.ID',
  `DELAY_CATEGORY_ID`          int           NOT NULL COMMENT 'FK -> TTM_DELAY_CATEGORY_MASTER.ID',
  `DETAILS`                    text          NOT NULL COMMENT 'Min 200 chars. Immutable once saved.',
  `ATTACHMENT_URL`             varchar(500)  DEFAULT NULL COMMENT 'Optional supporting document',
  `DELAY_DAYS_AT_FILING`       int           DEFAULT NULL COMMENT 'How many days delayed when this record was filed',
  `CREATED_BY_EMP_REF_ID`      bigint        NOT NULL COMMENT 'FK -> users.ID (initiator)',
  `CREATED_AT`                 datetime      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UQ_DELAY_PER_FUNC` (`TASK_FUNCTION_ID`) COMMENT 'One delay reason per function',
  KEY `IDX_DELAY_FUNC` (`TASK_FUNCTION_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- 7.2  TTM_DELAY_ESCALATION_LOG
-- System logs each escalation event as delay days accumulate.
-- Used to track which notifications were sent and to whom.
-- ESCALATION_LEVEL: 1=Minor(1-2d), 2=Moderate(3-5d), 3=Significant(6-9d), 4=Critical(10+d)
-- RISK_LEVEL: impact assessment on client deadline: LOW|MEDIUM|HIGH|CRITICAL
-- CLIENT_DEADLINE_RISK_DAYS: how many days slack remain before CLIENT_DEADLINE
-- --------------------------------------------------------
CREATE TABLE `TTM_DELAY_ESCALATION_LOG` (
  `ID`                          bigint        NOT NULL AUTO_INCREMENT,
  `TASK_FUNCTION_ID`            bigint        NOT NULL COMMENT 'FK -> TTM_TASK_FUNCTION.ID',
  `ESCALATION_LEVEL`            tinyint(1)    NOT NULL COMMENT '1=Minor|2=Moderate|3=Significant|4=Critical',
  `DELAY_DAYS`                  int           NOT NULL COMMENT 'Days past function deadline at time of this escalation',
  `RISK_LEVEL`                  enum('LOW','MEDIUM','HIGH','CRITICAL') NOT NULL,
  `CLIENT_DEADLINE_RISK_DAYS`   int           DEFAULT NULL COMMENT 'Days slack remaining before CLIENT_DEADLINE',
  `NOTIFIED_EMP_IDS`            json          DEFAULT NULL COMMENT 'JSON array of users.ID who were notified',
  `CLIENT_NOTIFIED`             tinyint(1)    NOT NULL DEFAULT '0',
  `DETECTED_AT`                 datetime      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `IDX_ESC_FUNC` (`TASK_FUNCTION_ID`),
  KEY `IDX_ESC_DETECTED` (`DETECTED_AT`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ============================================================
-- SECTION 8: TRANSFER MANAGEMENT
-- Handles: planned leave, emergency sick leave, resignation, role changes.
-- Transfer levels: PA, TL, RM, FL (each with different approval chains).
-- FL-level transfers have 4 distinct scenarios (A/B/C/D).
-- ============================================================

-- --------------------------------------------------------
-- 8.1  TTM_TASK_TRANSFER
-- Transfer request for PA / TL / RM level (not FL — see TTM_FL_TRANSFER).
-- TRANSFER_TYPE:
--   TEMPORARY_FUTURE    Planned leave (start in future; reverts at END_DATE)
--   TEMPORARY_IMMEDIATE Emergency today; approval by FL (immediate authority)
--   PERMANENT           Resignation / role change (no revert; END_DATE = NULL)
-- TRANSFER_SCOPE:
--   ALL_TASKS           All active assignments of FROM_EMP are transferred
--   SPECIFIC_FUNCTION   Only one task function is transferred (upward delegation)
-- Upward delegation rule: If TL transferred into PA role (as initiator),
--   TL cannot also be reviewer → system auto-escalates reviewers to RM/FL.
-- --------------------------------------------------------
CREATE TABLE `TTM_TASK_TRANSFER` (
  `ID`                        bigint        NOT NULL AUTO_INCREMENT,
  `TRANSFER_TYPE`             enum('TEMPORARY_FUTURE','TEMPORARY_IMMEDIATE','PERMANENT') NOT NULL,
  `TRANSFER_SCOPE`            enum('ALL_TASKS','SPECIFIC_FUNCTION') NOT NULL DEFAULT 'ALL_TASKS',
  `LEVEL`                     varchar(10)   NOT NULL COMMENT 'PA | TL | RM',
  `FROM_EMP_REF_ID`           bigint        NOT NULL COMMENT 'FK -> users.ID (employee being transferred)',
  `TO_EMP_REF_ID`             bigint        NOT NULL COMMENT 'FK -> users.ID (replacement employee)',
  `TASK_FUNCTION_ID`          bigint        DEFAULT NULL COMMENT 'FK -> TTM_TASK_FUNCTION.ID (set when SCOPE=SPECIFIC_FUNCTION)',
  `START_DATE`                date          NOT NULL COMMENT 'Transfer effective date',
  `END_DATE`                  date          DEFAULT NULL COMMENT 'NULL for PERMANENT; set for TEMPORARY',
  `REASON`                    text          NOT NULL,
  `PRIORITY`                  enum('NORMAL','URGENT') NOT NULL DEFAULT 'NORMAL' COMMENT 'URGENT for same-day emergency transfers',
  `STATUS`                    enum('PENDING','APPROVED','REJECTED','EXECUTED','REVERTED','COMPLETED') NOT NULL DEFAULT 'PENDING',
  `IS_UPWARD_DELEGATION`      tinyint(1)    NOT NULL DEFAULT '0' COMMENT '1 = PA task transferred to TL; reviewer chain auto-escalated',
  `INITIATED_BY_EMP_REF_ID`   bigint        NOT NULL COMMENT 'FK -> users.ID (FL/HR who initiated)',
  `APPROVED_BY_EMP_REF_ID`    bigint        DEFAULT NULL COMMENT 'FK -> users.ID',
  `APPROVED_AT`               datetime      DEFAULT NULL,
  `EXECUTED_AT`               datetime      DEFAULT NULL COMMENT 'When cron executed the transfer',
  `REVERTED_AT`               datetime      DEFAULT NULL COMMENT 'When cron reverted at END_DATE (temp only)',
  `HANDOVER_NOTES`            text          DEFAULT NULL,
  `CREATED_AT`                datetime      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT`                datetime      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `IDX_TRANSFER_FROM` (`FROM_EMP_REF_ID`, `STATUS`),
  KEY `IDX_TRANSFER_TO` (`TO_EMP_REF_ID`),
  KEY `IDX_TRANSFER_DATES` (`START_DATE`, `END_DATE`, `STATUS`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- 8.2  TTM_TRANSFER_TASK_MAP
-- Which task functions were actually moved in a transfer event.
-- One transfer can affect many functions (recorded individually).
-- REVIEWER_ESCALATED: 1 = upward delegation caused reviewer chain to change.
-- --------------------------------------------------------
CREATE TABLE `TTM_TRANSFER_TASK_MAP` (
  `ID`                              bigint    NOT NULL AUTO_INCREMENT,
  `TRANSFER_ID`                     bigint    NOT NULL COMMENT 'FK -> TTM_TASK_TRANSFER.ID',
  `TASK_FUNCTION_ID`                bigint    NOT NULL COMMENT 'FK -> TTM_TASK_FUNCTION.ID',
  `ORIGINAL_INITIATOR_EMP_REF_ID`   bigint    NOT NULL COMMENT 'FK -> users.ID (before transfer)',
  `NEW_INITIATOR_EMP_REF_ID`        bigint    NOT NULL COMMENT 'FK -> users.ID (after transfer)',
  `REVIEWER_ESCALATED`              tinyint(1) NOT NULL DEFAULT '0' COMMENT '1 = reviewer chain changed due to upward delegation',
  `ORIGINAL_REVIEWERS_JSON`         json      DEFAULT NULL COMMENT 'Snapshot of reviewers before transfer',
  `NEW_REVIEWERS_JSON`              json      DEFAULT NULL COMMENT 'Snapshot of reviewers after transfer',
  `CREATED_AT`                      datetime  NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `IDX_TMAP_TRANSFER` (`TRANSFER_ID`),
  KEY `IDX_TMAP_FUNC` (`TASK_FUNCTION_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- 8.3  TTM_FL_TRANSFER
-- Functional Lead-level transfers (4 scenarios per requirement doc):
--   Scenario A: All clients WITH  their team  → full department handover
--   Scenario B: All clients WITHOUT their team → clients move, team stays
--   Scenario C: Few clients WITHOUT their team → selected clients only
--   Scenario D: Few clients WITH  their team  → selected clients + dedicated team
--
-- CLIENT_SELECTION:
--   ALL  = Scenarios A & B (all clients of the FL)
--   FEW  = Scenarios C & D (specific clients only)
-- TEAM_TRANSFER:
--   1    = Scenarios A & D (team moves with clients)
--   0    = Scenarios B & C (team stays with original FL)
--
-- Approval: FL cannot self-approve; requires CEO or CRM.
-- --------------------------------------------------------
CREATE TABLE `TTM_FL_TRANSFER` (
  `ID`                        bigint        NOT NULL AUTO_INCREMENT,
  `SCENARIO`                  enum('A','B','C','D') NOT NULL COMMENT 'A=All+Team; B=All-Team; C=Few-Team; D=Few+Team',
  `FROM_FL_EMP_REF_ID`        bigint        NOT NULL COMMENT 'FK -> users.ID (original FL)',
  `TO_FL_EMP_REF_ID`          bigint        NOT NULL COMMENT 'FK -> users.ID (new FL)',
  `CLIENT_SELECTION`          enum('ALL','FEW') NOT NULL,
  `TEAM_TRANSFER`             tinyint(1)    NOT NULL DEFAULT '0' COMMENT '1 = team moves with clients',
  `TRANSFER_TYPE`             enum('TEMPORARY','PERMANENT') NOT NULL DEFAULT 'PERMANENT',
  `START_DATE`                date          NOT NULL,
  `END_DATE`                  date          DEFAULT NULL COMMENT 'Set for TEMPORARY; null for PERMANENT',
  `REASON`                    text          NOT NULL,
  `STATUS`                    enum('PENDING','APPROVED','REJECTED','EXECUTED','REVERTED','COMPLETED') NOT NULL DEFAULT 'PENDING',
  `INITIATED_BY_EMP_REF_ID`   bigint        NOT NULL COMMENT 'FK -> users.ID (HR/CEO who initiated)',
  `APPROVED_BY_EMP_REF_ID`    bigint        DEFAULT NULL COMMENT 'FK -> users.ID (CEO/CRM approver)',
  `APPROVED_AT`               datetime      DEFAULT NULL,
  `EXECUTED_AT`               datetime      DEFAULT NULL,
  `NOTES`                     text          DEFAULT NULL,
  `CREATED_AT`                datetime      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT`                datetime      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `IDX_FL_TRANSFER_FROM` (`FROM_FL_EMP_REF_ID`),
  KEY `IDX_FL_TRANSFER_STATUS` (`STATUS`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- 8.4  TTM_FL_TRANSFER_CLIENT_MAP
-- Which clients (and optionally their teams) are included in an FL transfer.
-- TEAM_MEMBERS_JSON: populated when TEAM_TRANSFER=1; lists users.ID of team members moved.
-- Note: if team member has other clients, system throws an error (per requirement).
-- --------------------------------------------------------
CREATE TABLE `TTM_FL_TRANSFER_CLIENT_MAP` (
  `ID`                    bigint    NOT NULL AUTO_INCREMENT,
  `FL_TRANSFER_ID`        bigint    NOT NULL COMMENT 'FK -> TTM_FL_TRANSFER.ID',
  `CLIENT_ID`             bigint    NOT NULL COMMENT 'FK -> clients.ID',
  `MAPPING_ID`            bigint    DEFAULT NULL COMMENT 'FK -> TTM_CLIENT_FL_MAPPING.ID (original mapping)',
  `TEAM_MEMBERS_JSON`     json      DEFAULT NULL COMMENT 'JSON array of users.ID moved with this client (when TEAM_TRANSFER=1)',
  `HAS_OTHER_CLIENTS_CONFLICT` tinyint(1) DEFAULT '0' COMMENT '1 = team member also has other clients; error state',
  `CREATED_AT`            datetime  NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `IDX_FL_TMAP_TRANSFER` (`FL_TRANSFER_ID`),
  KEY `IDX_FL_TMAP_CLIENT` (`CLIENT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ============================================================
-- SECTION 9: CHANGE REQUEST WORKFLOWS
-- All formal change requests follow approve/reject workflow.
-- Only CRM can approve; FL can request.
-- ============================================================

-- --------------------------------------------------------
-- 9.1  TTM_DEADLINE_CHANGE_REQUEST
-- Formal request to extend CLIENT_DEADLINE of a task.
-- ONLY CRM can approve.
-- On approval:
--   1. TTM_TASK.CLIENT_DEADLINE updated
--   2. All function deadlines proportionally recalculated
--   3. 1-day buffer rule re-validated
--   4. Audit log entry created
-- --------------------------------------------------------
CREATE TABLE `TTM_DEADLINE_CHANGE_REQUEST` (
  `ID`                          bigint        NOT NULL AUTO_INCREMENT,
  `TASK_ID`                     bigint        NOT NULL COMMENT 'FK -> TTM_TASK.ID',
  `CURRENT_CLIENT_DEADLINE`     date          NOT NULL COMMENT 'Deadline before change',
  `REQUESTED_CLIENT_DEADLINE`   date          NOT NULL COMMENT 'New requested deadline',
  `REASON`                      text          NOT NULL COMMENT 'Why extension is needed',
  `SUPPORTING_DOC_URL`          varchar(500)  DEFAULT NULL COMMENT 'Client agreement email / approval doc',
  `REQUESTED_BY_EMP_REF_ID`     bigint        NOT NULL COMMENT 'FK -> users.ID (FL who requested)',
  `STATUS`                      enum('PENDING','APPROVED','REJECTED') NOT NULL DEFAULT 'PENDING',
  `REVIEWED_BY_EMP_REF_ID`      bigint        DEFAULT NULL COMMENT 'FK -> users.ID (CRM reviewer)',
  `REVIEWED_AT`                 datetime      DEFAULT NULL,
  `REVIEW_COMMENT`              text          DEFAULT NULL,
  `CREATED_AT`                  datetime      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT`                  datetime      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `IDX_DCR_TASK` (`TASK_ID`, `STATUS`),
  KEY `IDX_DCR_REQUESTER` (`REQUESTED_BY_EMP_REF_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- 9.2  TTM_DEACTIVATION_REQUEST
-- Request to cancel a task / service for a client.
-- Key principle: current cycle completes normally first, THEN deactivates.
-- SCOPE determines what gets deactivated:
--   SPECIFIC_TASK     Only this one task instance
--   SERVICE_TASKS     All tasks of a given template for this client (service cancelled)
--   ALL_CLIENT_TASKS  All TTM tasks for this client (full offboarding)
-- RETAIN_UNTIL: data kept read-only until Sep 30 of year after deactivation (SOC audit).
-- --------------------------------------------------------
CREATE TABLE `TTM_DEACTIVATION_REQUEST` (
  `ID`                          bigint        NOT NULL AUTO_INCREMENT,
  `SCOPE`                       enum('SPECIFIC_TASK','SERVICE_TASKS','ALL_CLIENT_TASKS') NOT NULL,
  `TASK_ID`                     bigint        DEFAULT NULL COMMENT 'FK -> TTM_TASK.ID (set when SCOPE=SPECIFIC_TASK)',
  `CLIENT_ID`                   bigint        NOT NULL COMMENT 'FK -> clients.ID',
  `TEMPLATE_ID`                 bigint        DEFAULT NULL COMMENT 'FK -> TTM_TASK_TEMPLATE.ID (set when SCOPE=SERVICE_TASKS)',
  `REASON`                      text          NOT NULL COMMENT 'e.g. Client handling TDS in-house',
  `SUPPORTING_DOC_URL`          varchar(500)  DEFAULT NULL COMMENT 'Client cancellation email / doc',
  `REQUESTED_BY_EMP_REF_ID`     bigint        NOT NULL COMMENT 'FK -> users.ID (FL)',
  `STATUS`                      enum('PENDING','APPROVED','REJECTED','EXECUTED') NOT NULL DEFAULT 'PENDING',
  `REVIEWED_BY_EMP_REF_ID`      bigint        DEFAULT NULL COMMENT 'FK -> users.ID (CRM)',
  `REVIEWED_AT`                 datetime      DEFAULT NULL,
  `REVIEW_COMMENT`              text          DEFAULT NULL,
  `DEACTIVATION_EXECUTE_DATE`   date          DEFAULT NULL COMMENT 'System sets: day after current active tasks complete',
  `RETAIN_UNTIL`                date          DEFAULT NULL COMMENT 'Sep 30 of subsequent year after deactivation',
  `EXECUTED_AT`                 datetime      DEFAULT NULL COMMENT 'When system actually deactivated the tasks',
  `CREATED_AT`                  datetime      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT`                  datetime      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `IDX_DEACT_CLIENT` (`CLIENT_ID`, `STATUS`),
  KEY `IDX_DEACT_TASK` (`TASK_ID`),
  KEY `IDX_DEACT_EXECUTE` (`DEACTIVATION_EXECUTE_DATE`, `STATUS`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;





-- ============================================================
-- SECTION 11: NOTIFICATION LOG
-- 10 internal notification types (per requirement doc).
-- Channels: EMAIL, IN_APP, SMS.
-- ============================================================

-- --------------------------------------------------------
-- 11.1  TTM_NOTIFICATION_LOG
-- System-generated notification record for every triggered event.
-- NOTIFICATION_TYPE maps to one of the 10 defined business events.
-- IS_READ: for IN_APP notifications (user sees bell icon).
-- --------------------------------------------------------
CREATE TABLE `TTM_NOTIFICATION_LOG` (
  `ID`                       bigint        NOT NULL AUTO_INCREMENT,
  `NOTIFICATION_TYPE`        enum(
                               'TASK_ASSIGNMENT',          -- CRM assigns to FL; FL assigns to PA/TL
                               'REVIEW_PENDING',           -- PA submits; reviewer notified
                               'WORK_APPROVED',            -- Reviewer approved; initiator notified
                               'WORK_REJECTED',            -- Reviewer rejected; initiator notified
                               'CLARIFICATION_REQUESTED',  -- Reviewer needs info
                               'FUNCTION_COMPLETE',        -- All reviews approved; all involved + next fn notified
                               'ONE_DAY_BEFORE_DEADLINE',  -- Sent at 6AM day before function deadline
                               'DELAY_ALERT',              -- Function past deadline; escalation matrix
                               'TRANSFER_NOTIFICATION',    -- Task transferred to/from employee
                               'DEACTIVATION_NOTIFICATION' -- Service deactivation approved
                             ) NOT NULL,
  `ENTITY_TYPE`              varchar(50)   NOT NULL COMMENT 'TASK | TASK_FUNCTION | TRANSFER | FL_TRANSFER | DEACTIVATION',
  `ENTITY_REF_ID`            bigint        NOT NULL COMMENT 'ID of the entity that triggered notification',
  `RECIPIENT_EMP_REF_ID`     bigint        NOT NULL COMMENT 'FK -> users.ID',
  `CHANNEL`                  enum('EMAIL','IN_APP','SMS') NOT NULL DEFAULT 'EMAIL',
  `SUBJECT`                  varchar(500)  DEFAULT NULL,
  `MESSAGE`                  text          NOT NULL,
  `IS_READ`                  tinyint(1)    NOT NULL DEFAULT '0' COMMENT '1 = user dismissed in-app notification',
  `READ_AT`                  datetime      DEFAULT NULL,
  `STATUS`                   enum('QUEUED','SENT','FAILED') NOT NULL DEFAULT 'QUEUED',
  `SENT_AT`                  datetime      DEFAULT NULL,
  `ERROR_MESSAGE`            text          DEFAULT NULL COMMENT 'Populated when STATUS = FAILED',
  `CREATED_AT`               datetime      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `IDX_NOTIF_RECIPIENT` (`RECIPIENT_EMP_REF_ID`, `IS_READ`),
  KEY `IDX_NOTIF_TYPE` (`NOTIFICATION_TYPE`),
  KEY `IDX_NOTIF_STATUS` (`STATUS`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ============================================================
-- SECTION 12: IMMUTABLE AUDIT LOG  (SOC 2 Compliance)
-- Every action logged. Logs cannot be edited or deleted.
-- Retained minimum 7 years (RETENTION_UNTIL = CREATED_AT + 7 years).
-- Actions logged: task creation, assignment, submission, review decision,
--   completion, rejection, transfer, deactivation, deadline change,
--   document upload/download, comment, email, login/logout, system actions.
-- ============================================================

-- --------------------------------------------------------
-- 12.1  TTM_AUDIT_LOG
-- ACTOR_EMP_REF_ID: null for system-generated actions (cron jobs).
-- ENTITY_TYPE / ENTITY_REF_ID: what was affected.
-- BEFORE_JSON / AFTER_JSON: full state snapshots for reversal audit.
-- TS_MS: millisecond precision timestamp for exact sequencing.
-- --------------------------------------------------------
CREATE TABLE `TTM_AUDIT_LOG` (
  `ID`                  bigint        NOT NULL AUTO_INCREMENT,
  `TS_MS`               bigint        NOT NULL COMMENT 'Unix timestamp in milliseconds (precise sequencing)',
  `ACTOR_EMP_REF_ID`    bigint        DEFAULT NULL COMMENT 'FK -> users.ID; NULL = system/cron action',
  `ACTOR_CLIENT_ID`     bigint        DEFAULT NULL COMMENT 'FK -> clients.ID; set when action is by portal client',
  `ACTION`              varchar(100)  NOT NULL COMMENT 'e.g. TASK_CREATED | WORK_SUBMITTED | REVIEW_APPROVED | TRANSFER_EXECUTED',
  `ENTITY_TYPE`         varchar(60)   NOT NULL COMMENT 'TASK | TASK_FUNCTION | REVIEWER | TRANSFER | FL_TRANSFER | TEMPLATE | DOCUMENT | COMMENT',
  `ENTITY_REF_ID`       bigint        DEFAULT NULL COMMENT 'PK of the affected record',
  `BEFORE_JSON`         json          DEFAULT NULL COMMENT 'State before action',
  `AFTER_JSON`          json          DEFAULT NULL COMMENT 'State after action',
  `REASON`              text          DEFAULT NULL COMMENT 'Optional: contextual reason',
  `IP_ADDRESS`          varchar(64)   DEFAULT NULL,
  `USER_AGENT`          varchar(255)  DEFAULT NULL,
  `RETENTION_UNTIL`     date          NOT NULL COMMENT 'CREATED_AT + 7 years (legal / regulatory minimum)',
  `CREATED_AT`          datetime      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `IDX_AUDIT_ACTOR` (`ACTOR_EMP_REF_ID`),
  KEY `IDX_AUDIT_ENTITY` (`ENTITY_TYPE`, `ENTITY_REF_ID`),
  KEY `IDX_AUDIT_ACTION` (`ACTION`),
  KEY `IDX_AUDIT_RETENTION` (`RETENTION_UNTIL`),
  KEY `IDX_AUDIT_CREATED` (`CREATED_AT`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ============================================================
-- COMPLETE RELATIONSHIP MAP
-- ============================================================
--
-- MASTER TABLES (lookup only, no FK dependencies)
--   TTM_TASK_TYPE_MASTER
--   TTM_RECURRENCE_RULE_MASTER
--   TTM_PRIORITY_MASTER
--   TTM_TASK_STATUS_MASTER
--   TTM_FUNCTION_STATUS_MASTER
--   TTM_REVIEW_STATUS
--   TTM_TEMPLATE_STATUS_MASTER
--   TTM_DELAY_CATEGORY_MASTER
--
-- TEMPLATES (CRM creates)
--   TTM_TASK_TEMPLATE (1) ---> (*) TTM_TEMPLATE_FUNCTION
--   TTM_TASK_TEMPLATE.TASK_TYPE_ID     --> TTM_TASK_TYPE_MASTER
--   TTM_TASK_TEMPLATE.RECURRENCE_RULE_ID --> TTM_RECURRENCE_RULE_MASTER
--   TTM_TASK_TEMPLATE.STATUS_ID        --> TTM_TEMPLATE_STATUS_MASTER
--   TTM_TEMPLATE_FUNCTION.DEPT_REF_ID  --> departments   [Shared]
--
-- MAPPING (CEO creates)
--   TTM_CLIENT_FL_MAPPING.CLIENT_ID    --> clients        [Timesheet]
--   TTM_CLIENT_FL_MAPPING.FL_EMP_REF_ID--> users          [Shared]
--   TTM_CLIENT_FL_MAPPING.TEMPLATE_ID  --> TTM_TASK_TEMPLATE
--
-- TASK EXECUTION
--   TTM_TASK.TEMPLATE_ID               --> TTM_TASK_TEMPLATE
--   TTM_TASK.CLIENT_ID                 --> clients        [Timesheet]
--   TTM_TASK.CONTRACT_ID               --> CONTRACT       [CRM]
--   TTM_TASK.ASSIGNED_FL_EMP_REF_ID    --> users          [Shared]
--   TTM_TASK (1) ---> (*) TTM_TASK_FUNCTION
--   TTM_TASK_FUNCTION.TEMPLATE_FUNCTION_ID --> TTM_TEMPLATE_FUNCTION
--   TTM_TASK_FUNCTION.INITIATOR_EMP_REF_ID --> users
--   TTM_TASK_FUNCTION.DEPT_REF_ID      --> departments
--   TTM_TASK_FUNCTION (1) ---> (*) TTM_FUNCTION_REVIEWER
--   TTM_TASK_FUNCTION (1) ---> (*) TTM_SUBMISSION_LOG
--   TTM_TASK_FUNCTION (1) ---> (*) TTM_DELAY_REASON
--   TTM_TASK_FUNCTION (1) ---> (*) TTM_DELAY_ESCALATION_LOG
--   TTM_SUBMISSION_LOG (1) ---> (*) TTM_REVIEW_LOG
--   TTM_FUNCTION_REVIEWER (1) ---> (*) TTM_REVIEW_LOG
--
-- DOCUMENTS
--   TTM_TASK_FUNCTION (1) ---> (*) TTM_TASK_DOCUMENT
--   TTM_TASK_DOCUMENT (1) ---> (*) TTM_DOCUMENT_PASSWORD
--   TTM_TASK_DOCUMENT (1) ---> (*) TTM_DOCUMENT_DOWNLOAD_LOG
--
-- COMMUNICATION
--   TTM_TASK (1) ---> (*) TTM_TASK_COMMENT
--   TTM_TASK (1) ---> (*) TTM_EMAIL_LOG
--
-- TRANSFERS
--   TTM_TASK_TRANSFER (1) ---> (*) TTM_TRANSFER_TASK_MAP
--   TTM_FL_TRANSFER (1)   ---> (*) TTM_FL_TRANSFER_CLIENT_MAP
--
-- CHANGE REQUESTS
--   TTM_TASK (1) ---> (*) TTM_DEADLINE_CHANGE_REQUEST
--   TTM_TASK (1) ---> (*) TTM_DEACTIVATION_REQUEST (SPECIFIC_TASK scope)
--
-- CLIENT PORTAL
--   clients (1) ---> (*) TTM_CLIENT_PORTAL_SESSION
--
-- NOTIFICATIONS & AUDIT
--   users (1)   ---> (*) TTM_NOTIFICATION_LOG
--   Everything  ---> (*) TTM_AUDIT_LOG
--
-- ============================================================
-- TTM_TASK_GENERATION_LOG — Deduplication for recurring task auto-generation
-- ============================================================
-- The cron job that auto-generates FIXED tasks inserts a row here for every
-- (template, client, period) tuple it creates. The UNIQUE KEY prevents
-- duplicate generation even if the cron fires twice.
-- ============================================================

CREATE TABLE IF NOT EXISTS `TTM_TASK_GENERATION_LOG` (
  `ID`            bigint       NOT NULL AUTO_INCREMENT,
  `TEMPLATE_ID`   bigint       NOT NULL COMMENT 'FK -> TTM_TASK_TEMPLATE.ID',
  `CLIENT_ID`     bigint       NOT NULL COMMENT 'CONTRACT_ID from TTM_CLIENT_FL_MAPPING',
  `TASK_ID`       bigint       NOT NULL COMMENT 'FK -> TTM_TASK.ID (the generated task)',
  `TASK_PERIOD`   varchar(50)  NOT NULL COMMENT 'Period key e.g. "2026-03", "2026-Q1", "2026-W10"',
  `RULE_CODE`     varchar(50)  NOT NULL COMMENT 'Recurrence rule e.g. MONTHLY, QUARTERLY',
  `GENERATED_AT`  datetime     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UQ_TPL_CLIENT_PERIOD` (`TEMPLATE_ID`, `CLIENT_ID`, `TASK_PERIOD`),
  KEY `IDX_TASK_ID` (`TASK_ID`),
  KEY `IDX_GENERATED_AT` (`GENERATED_AT`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ============================================================
-- CRON JOBS REQUIRED
-- ============================================================
-- 1. FIXED TASK AUTO-GENERATION (daily at midnight)
--      Reads TTM_TASK_TEMPLATE (FIXED, ACTIVE)
--      Reads TTM_RECURRENCE_RULE_MASTER.RECURRENCE_DAY_OF_MONTH
--      Reads TTM_CLIENT_FL_MAPPING for each client
--      Creates TTM_TASK instances; sends TASK_ASSIGNMENT notification
--
-- 2. PRIORITY ESCALATION (daily at 06:00)
--      Updates TTM_TASK.PRIORITY_CODE based on days to CLIENT_DEADLINE
--
-- 3. ONE-DAY-BEFORE REMINDER (daily at 06:00)
--      TTM_TASK_FUNCTION WHERE DEADLINE = TODAY+1 AND STATUS_CODE != COMPLETED
--      Sends ONE_DAY_BEFORE_DEADLINE to INITIATOR_EMP_REF_ID
--
-- 4. DELAY DETECTION + ESCALATION (daily at 07:00)
--      TTM_TASK_FUNCTION WHERE DEADLINE < TODAY AND STATUS_CODE != COMPLETED
--      Increments DELAY_DAYS; updates TTM_TASK.IS_DELAYED = 1
--      Inserts TTM_DELAY_ESCALATION_LOG per escalation level
--      Sends DELAY_ALERT notifications per escalation matrix
--
-- 5. TRANSFER EXECUTION (daily at 00:01)
--      TTM_TASK_TRANSFER WHERE START_DATE = TODAY AND STATUS = PENDING|APPROVED
--      Creates TTM_TRANSFER_TASK_MAP; updates INITIATOR_EMP_REF_ID
--
-- 6. TRANSFER REVERT (daily at 00:01)
--      TTM_TASK_TRANSFER WHERE END_DATE = TODAY AND TRANSFER_TYPE = TEMPORARY_*
--      Reverts new task assignments to original employee (in-progress stay with replacement)
--
-- 7. DEACTIVATION EXECUTION (daily at 00:01)
--      TTM_DEACTIVATION_REQUEST WHERE DEACTIVATION_EXECUTE_DATE = TODAY AND STATUS = APPROVED
--      Sets TTM_TASK.STATUS_CODE = DEACTIVATED; stops future recurring instances
-- ============================================================
