-- ============================================================
-- Migration: Client Onboarding — CRM to TTM
-- Date: 2026-03-10
-- Description:
--   Adds ENGAGEMENT_TYPE_IDS and CONTRACT_DOCUMENT_ID columns
--   to TTM_CLIENT_FL_MAPPING table to support the CRM → TTM
--   client onboarding flow (FL assignment per contract document).
-- ============================================================

-- 1. Add ENGAGEMENT_TYPE_IDS column (comma-separated IDs from ENGAGEMENT_TYPE_MASTER)
--    Placed AFTER SERVICE_NAME
ALTER TABLE TTM_CLIENT_FL_MAPPING
  ADD COLUMN `ENGAGEMENT_TYPE_IDS` TEXT DEFAULT NULL
  COMMENT 'Comma-separated ENGAGEMENT_TYPE_MASTER IDs, e.g. "1,3,5"'
  AFTER `SERVICE_NAME`;

-- 2. Add CONTRACT_DOCUMENT_ID column (FK to CONTRACT_DOCUMENT.DOCUMENT_ID in CRM)
--    Placed AFTER CLIENT_ID
ALTER TABLE TTM_CLIENT_FL_MAPPING
  ADD COLUMN `CONTRACT_DOCUMENT_ID` BIGINT UNSIGNED DEFAULT NULL
  COMMENT 'FK → CONTRACT_DOCUMENT.DOCUMENT_ID — the CRM document that triggered this mapping'
  AFTER `CLIENT_ID`;

-- 3. Add index for fast lookups by CONTRACT_DOCUMENT_ID
ALTER TABLE TTM_CLIENT_FL_MAPPING
  ADD INDEX `IDX_CONTRACT_DOC` (`CONTRACT_DOCUMENT_ID`);

-- ============================================================
-- Verification: Run after migration to confirm columns exist
-- ============================================================
-- SELECT COLUMN_NAME, COLUMN_TYPE, COLUMN_COMMENT
-- FROM INFORMATION_SCHEMA.COLUMNS
-- WHERE TABLE_NAME = 'TTM_CLIENT_FL_MAPPING'
--   AND COLUMN_NAME IN ('ENGAGEMENT_TYPE_IDS', 'CONTRACT_DOCUMENT_ID');
--
-- Expected: 2 rows returned
-- ============================================================
