-- ============================================================
-- Migration: TTM_ENGAGEMENT_FUNCTION_MAPPING
-- Purpose:   Maps engagement types → departments (functions).
--            Admin can assign multiple functions per engagement type.
-- Run:       Execute once on the Oremus database.
-- ============================================================

CREATE TABLE IF NOT EXISTS `TTM_ENGAGEMENT_FUNCTION_MAPPING` (
  `ID`                  bigint       NOT NULL AUTO_INCREMENT,
  `ENGAGEMENT_TYPE_ID`  bigint       NOT NULL COMMENT 'FK → ENGAGEMENT_TYPE_MASTER.ID',
  `DEPT_REF_ID`         int          NOT NULL COMMENT 'FK → departments.ID',
  `SORT_ORDER`          int          NOT NULL DEFAULT 0,
  `IS_ACTIVE`           tinyint(1)   NOT NULL DEFAULT 1,
  `CREATED_AT`          datetime     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `CREATED_BY`          bigint       NOT NULL COMMENT 'FK → users.ID',
  `UPDATED_AT`          datetime              DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `DELETED`             tinyint(1)   NOT NULL DEFAULT 0,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `uq_eng_dept` (`ENGAGEMENT_TYPE_ID`, `DEPT_REF_ID`, `DELETED`),
  KEY `idx_engagement`   (`ENGAGEMENT_TYPE_ID`),
  KEY `idx_dept`          (`DEPT_REF_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
