-- ============================================================
-- Migration: Create TTM_CLIENT_TASK_MAPPING table
-- + Add TASK_ASSIGNED flag to TTM_CLIENT_FL_MAPPING
-- ============================================================

-- 1) New table to track admin-assigned task templates per client
CREATE TABLE IF NOT EXISTS 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',
  CLIENT_DEADLINE         date         DEFAULT NULL        COMMENT 'Admin-set deadline for this task',
  STATUS                  varchar(20)  NOT NULL DEFAULT 'DRAFT' COMMENT 'DRAFT | SUBMITTED',
  TASK_ID                 bigint       DEFAULT NULL        COMMENT 'FK: TTM_TASK.ID — populated on submit',
  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;

-- 2) Add TASK_ASSIGNED flag to FL mapping (already used in queries but missing from schema)
ALTER TABLE TTM_CLIENT_FL_MAPPING
  ADD COLUMN IF NOT EXISTS TASK_ASSIGNED tinyint(1) NOT NULL DEFAULT 0
    COMMENT '1 = tasks have been mapped/submitted for this client-document'
  AFTER ACTIVE;
