-- Migration: Change template_key from enum to varchar(255)
-- Date: 2026-06-10
-- Reason: template_key_enum required an ALTER TYPE for every new program type (TAT, ENT, etc.)
--         causing PT_TAT and future type templates to silently fail to clone.
--         VARCHAR(255) lets any template key be stored without enum maintenance.

-- hdb_communication_templates
ALTER TABLE hdb_communication_templates
    ALTER COLUMN template_key TYPE VARCHAR(255) USING template_key::text;

-- communication_templates_master (entity declared it as enum but DB column was already varchar;
-- this is a no-op if the column is already varchar, safe to run)
DO $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name = 'communication_templates_master'
          AND column_name = 'template_key'
          AND data_type = 'USER-DEFINED'
    ) THEN
        ALTER TABLE communication_templates_master
            ALTER COLUMN template_key TYPE VARCHAR(255) USING template_key::text;
    END IF;
END $$;

-- Verification
SELECT
    table_name,
    column_name,
    data_type,
    character_maximum_length
FROM information_schema.columns
WHERE column_name = 'template_key'
  AND table_name IN ('hdb_communication_templates', 'communication_templates_master')
ORDER BY table_name;
