-- ============================================================
-- TTM_CLIENT_FL_MAPPING — Add ENGAGEMENT_TYPE_IDS column
-- Stored as comma-separated IDs e.g. "1,3,5"  (TEXT column).
-- Supports multi-select engagement types per mapping.
-- Safe / idempotent — run any time.
--
-- Usage (MySQL CLI):
--   mysql -u <user> -p <database_name> < ttm_engagement_type_mapping_migration.sql
-- ============================================================

-- ── STEP 1: Remove old single-value column if it exists ───────────────
ALTER TABLE `TTM_CLIENT_FL_MAPPING`
  DROP COLUMN IF EXISTS `ENGAGEMENT_TYPE_ID`;

-- ── STEP 2: Add TEXT column for comma-separated multi-select IDs ───────
ALTER TABLE `TTM_CLIENT_FL_MAPPING`
  ADD COLUMN IF NOT EXISTS `ENGAGEMENT_TYPE_IDS` text DEFAULT NULL
    COMMENT 'Comma-separated ENGAGEMENT_TYPE_MASTER IDs; supports multi-select e.g. "1,3,5"';

-- ── Done ─────────────────────────────────────────────────────────────
-- After running this, the TTM client-FL mapping form will allow
-- selecting multiple natures of engagement (from ENGAGEMENT_TYPE_MASTER)
-- for each mapping. Existing rows will have ENGAGEMENT_TYPE_IDS = NULL.
