-- Migration: Communication Templates Master System + Template Access Key (Consolidated)
-- Date: 2026-04-02/03 (Original) / 2026-04-10 (Consolidated)
-- Description: Creates master communication templates system, enums, access keys, BCC fields, and is_per_recipient column
--              Includes ALL enum values and schema changes consolidated from files 09-14

-- ============================================================
-- PART 1: Create/Update Communication Enums
-- ============================================================

DO $$
BEGIN
    ALTER TYPE communication_category_enum ADD VALUE IF NOT EXISTS 'hdb_blessed_no_payment';
    ALTER TYPE communication_category_enum ADD VALUE IF NOT EXISTS 'registration_completed';
    ALTER TYPE communication_category_enum ADD VALUE IF NOT EXISTS 'registration_cancelled';
    ALTER TYPE communication_category_enum ADD VALUE IF NOT EXISTS 'registration_refund';
    ALTER TYPE communication_category_enum ADD VALUE IF NOT EXISTS 'payment_acknowledgement';
    ALTER TYPE communication_category_enum ADD VALUE IF NOT EXISTS 'payment_offline';
    ALTER TYPE communication_category_enum ADD VALUE IF NOT EXISTS 'payment_pending_reminder';
    ALTER TYPE communication_category_enum ADD VALUE IF NOT EXISTS 'invoice';
    ALTER TYPE communication_category_enum ADD VALUE IF NOT EXISTS 'travel_plan_change';
    ALTER TYPE communication_category_enum ADD VALUE IF NOT EXISTS 'travel_plan_new';
    ALTER TYPE communication_category_enum ADD VALUE IF NOT EXISTS 'otp';
    ALTER TYPE communication_category_enum ADD VALUE IF NOT EXISTS 'billing_details_edit';
    ALTER TYPE communication_category_enum ADD VALUE IF NOT EXISTS 'billing_details_removed';
    ALTER TYPE communication_category_enum ADD VALUE IF NOT EXISTS 'swap_demand';
    ALTER TYPE communication_category_enum ADD VALUE IF NOT EXISTS 'preference_edited';
    ALTER TYPE communication_category_enum ADD VALUE IF NOT EXISTS 'admin_message_notification';
    ALTER TYPE communication_category_enum ADD VALUE IF NOT EXISTS 'first_timer';
    ALTER TYPE communication_category_enum ADD VALUE IF NOT EXISTS 'checklist';
    ALTER TYPE communication_category_enum ADD VALUE IF NOT EXISTS 'preparatory';
    ALTER TYPE communication_category_enum ADD VALUE IF NOT EXISTS 'general';
    ALTER TYPE communication_type_enum ADD VALUE IF NOT EXISTS 'sms';
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

-- ============================================================
-- PART 2: Create Template Usage Type Enum
-- ============================================================

DO $$
BEGIN
    CREATE TYPE template_usage_type_enum AS ENUM ('general', 'bulk_email', 'portal');
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

-- ============================================================
-- PART 3: Create Template Access Key Enum (Complete with All Variants)
-- ============================================================

DO $$
BEGIN
    CREATE TYPE communication_template_access_key_enum AS ENUM (
        -- Generic Templates
        'GENERIC_EMAIL',
        'OTP',
        'OTP_EMAIL',
        'OTP_SMS',

        -- Registration Completed (Seeker, RM, Finance variants)
        'REGISTRATION_COMPLETED',
        'REGISTRATION_COMPLETED_EMAIL_SEEKER',
        'REGISTRATION_COMPLETED_WATI_SEEKER',
        'REGISTRATION_COMPLETED_WATI_RM',
        'REGISTRATION_COMPLETED_SMS_RM',

        -- Registration Cancelled (Seeker, RM, Finance variants)
        'REGISTRATION_CANCELLED',
        'REGISTRATION_CANCELLED_EMAIL_SEEKER',
        'REGISTRATION_CANCELLED_EMAIL_RM',
        'REGISTRATION_CANCELLED_EMAIL_FINANCE',
        'REGISTRATION_CANCELLED_WATI_SEEKER',
        'REGISTRATION_CANCELLED_WATI_RM',
        'REGISTRATION_CANCELLED_WATI_FINANCE',

        -- Registration Refund
        'REGISTRATION_REFUND',
        'REGISTRATION_REFUND_EMAIL_FINANCE',

        -- Blessed (Seeker, RM variants)
        'BLESSED',
        'BLESSED_EMAIL_SEEKER',
        'BLESSED_WATI_SEEKER',
        'BLESSED_WATI_RM',
        'BLESSED_SMS_RM',

        -- Blessed No Payment
        'BLESSED_NO_PAYMENT',
        'BLESSED_NO_PAYMENT_EMAIL',
        'BLESSED_NO_PAYMENT_WATI',
        'BLESSED_NO_PAYMENT_EMAIL_SEEKER',
        'BLESSED_NO_PAYMENT_WATI_SEEKER',
        'BLESSED_NO_PAYMENT_WATI_RM',

        -- Hold
        'HOLD',
        'HOLD_EMAIL_SEEKER',
        'RM_HOLD',
        'RM_HOLD_EMAIL_RM',

        -- Invoice
        'INVOICE',
        'INVOICE_EMAIL',
        'INVOICE_WATI',
        'INVOICE_EMAIL_SEEKER',
        'INVOICE_WATI_SEEKER',

        -- Payment Acknowledgement
        'PAYMENT_ACKNOWLEDGEMENT_OFFLINE',
        'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL',
        'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WATI',
        'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL_SEEKER',
        'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WATI_SEEKER',

        -- Payment Pending Reminder
        'PAYMENT_PENDING_REMINDER',
        'PAYMENT_PENDING_REMINDER_WATI_RM',

        -- Billing
        'BILLING_DETAILS_EDIT',
        'BILLING_DETAILS_EDIT_EMAIL_SEEKER',
        'BILLING_DETAILS_REMOVED',
        'BILLING_DETAILS_EDIT_REMOVED_EMAIL_SEEKER',

        -- E-Invoice
        'EINVOICE_ERROR',
        'EINVOICE_ERROR_EMAIL_SEEKER',

        -- Travel Plan
        'TRAVEL_PLAN_CHANGE',
        'TRAVEL_PLAN_NEW',
        'TRAVEL_PLAN_RETURN_CHANGE',
        'TRAVEL_PLAN_ONWARD_CHANGE',
        'CO_ORDINATOR_TRAVEL_PLAN_CHANGE_WATI',
        'CO_ORDINATOR_NEW_TRAVEL_PLAN_MADE_WATI',
        'CO_ORDINATOR_RETURN_TRAVEL_PLAN_CHANGE_WATI',
        'CO_ORDINATOR_ONWARD_TRAVEL_PLAN_CHANGE_WATI',

        -- Swap Demand
        'SWAP_DEMAND',
        'SWAP_DEMAND_EMAIL',
        'SWAP_DEMAND_WATI',
        'SWAP_DEMAND_EMAIL_SEEKER',
        'SWAP_DEMAND_WATI_SEEKER',

        -- Preferences
        'PREFERENCE_EDITED',
        'PREFERENCE_EDITED_WATI_RM',

        -- Admin Messages
        'ADMIN_MESSAGE_NOTIFICATION',
        'ADMIN_MESSAGE_NOTIFICATION_WATI',

        -- Program Specific
        'FIRST_TIMER',
        'CHECKLIST',
        'PREPARATORY'
    );
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

-- ============================================================
-- PART 4: Create Communication Templates Master Table
-- ============================================================

CREATE TABLE IF NOT EXISTS communication_templates_master (
    id BIGSERIAL PRIMARY KEY,
    template_key VARCHAR(255) NOT NULL,
    template_name VARCHAR(255) NOT NULL,
    template_type communication_type_enum NOT NULL,
    template_id VARCHAR(500) NOT NULL,
    sandbox_template_id VARCHAR(500),
    category communication_category_enum,
    template_usage_type template_usage_type_enum NOT NULL DEFAULT 'general',
    template_access_key communication_template_access_key_enum NOT NULL,
    program_type_id INT,
    merge_field_map JSONB DEFAULT '[]'::jsonb,
    display_order INT,
    is_active BOOLEAN DEFAULT true,
    created_by BIGINT,
    updated_by BIGINT,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_program_type FOREIGN KEY (program_type_id) REFERENCES program_type_v1(id) ON DELETE SET NULL
);

-- ============================================================
-- PART 5: Add BCC fields to program_v1, program_session, program_type_v1
-- ============================================================

ALTER TABLE program_v1
  ADD COLUMN IF NOT EXISTS email_bcc_address VARCHAR(255),
  ADD COLUMN IF NOT EXISTS email_sender_address VARCHAR(255),
  ADD COLUMN IF NOT EXISTS email_bcc_name VARCHAR(255);

COMMENT ON COLUMN program_v1.email_bcc_address IS 'BCC email address for program communications';
COMMENT ON COLUMN program_v1.email_sender_address IS 'Sender email address for program communications';
COMMENT ON COLUMN program_v1.email_bcc_name IS 'BCC recipient name for program communications';

ALTER TABLE program_session
  ADD COLUMN IF NOT EXISTS email_bcc_address VARCHAR(255),
  ADD COLUMN IF NOT EXISTS email_sender_address VARCHAR(255),
  ADD COLUMN IF NOT EXISTS email_bcc_name VARCHAR(255);

COMMENT ON COLUMN program_session.email_bcc_address IS 'BCC email address for session communications';
COMMENT ON COLUMN program_session.email_sender_address IS 'Sender email address for session communications';
COMMENT ON COLUMN program_session.email_bcc_name IS 'BCC recipient name for session communications';

ALTER TABLE program_type_v1
    ADD COLUMN IF NOT EXISTS email_bcc_address VARCHAR(255),
    ADD COLUMN IF NOT EXISTS email_sender_address VARCHAR(255),
    ADD COLUMN IF NOT EXISTS email_bcc_name VARCHAR(255);

COMMENT ON COLUMN program_type_v1.email_bcc_address IS 'BCC email address for program type communications';
COMMENT ON COLUMN program_type_v1.email_sender_address IS 'Sender email address for program type communications';
COMMENT ON COLUMN program_type_v1.email_bcc_name IS 'BCC recipient name for program type communications';

-- Create indexes for BCC fields
CREATE INDEX IF NOT EXISTS idx_program_v1_email_bcc_address ON program_v1(email_bcc_address);
CREATE INDEX IF NOT EXISTS idx_program_session_email_bcc_address ON program_session(email_bcc_address);
CREATE INDEX IF NOT EXISTS idx_program_v1_email_sender_address ON program_v1(email_sender_address);
CREATE INDEX IF NOT EXISTS idx_program_session_email_sender_address ON program_session(email_sender_address);

-- ============================================================
-- PART 5A: Add is_per_recipient Column to Merge Info Map
-- ============================================================

-- Add is_per_recipient column to merge_info_answer_location_map table
-- This column marks fields that need per-recipient computation (e.g., coordinator_name for multiple coordinators)
ALTER TABLE merge_info_answer_location_map
ADD COLUMN IF NOT EXISTS is_per_recipient BOOLEAN DEFAULT FALSE;

COMMENT ON COLUMN merge_info_answer_location_map.is_per_recipient IS
'Indicates if this merge field needs per-recipient computation. When true, value is resolved from extraMergeContext.perRecipient at send time';

-- ============================================================
-- PART 6: Add Columns to HDB Communication Templates Table
-- ============================================================

ALTER TABLE hdb_communication_templates
ADD COLUMN IF NOT EXISTS template_usage_type template_usage_type_enum DEFAULT 'general';

ALTER TABLE hdb_communication_templates
ADD COLUMN IF NOT EXISTS template_access_key communication_template_access_key_enum;

ALTER TABLE hdb_communication_templates
ADD COLUMN IF NOT EXISTS sandbox_template_id VARCHAR(500);

COMMENT ON COLUMN hdb_communication_templates.template_access_key IS 'Common access key across program types for same purpose (e.g., REGISTRATION_COMPLETED for both HDB and ENT)';

-- ============================================================
-- PART 7: Update Existing Records with Access Keys
-- ============================================================


UPDATE communication_templates_master
SET template_access_key =
    (CASE
        -- Registration related
        WHEN template_key::text IN ('hdb_registration_completed_email', 'hdb_seeker_registration_completed_whatsapp', 'hdb_rm_registration_completed_whatsapp', 'hdb_rm_registration_notify_sms', 'HDB_REGISTRATION_COMPLETED_EMAIL', 'HDB_SEEKER_REGISTRATION_COMPLETED_WHATSAPP', 'HDB_RM_REGISTRATION_COMPLETED_WHATSAPP', 'HDB_RM_REGISTRATION_NOTIFY_SMS') THEN 'REGISTRATION_COMPLETED'
        WHEN template_key::text IN ('hdb_cancel_email', 'HDB_CANCEL_EMAIL') THEN 'REGISTRATION_CANCELLED'
        WHEN template_key::text IN ('hdb_registration_refund_email', 'HDB_REGISTRATION_REFUND_EMAIL') THEN 'REGISTRATION_REFUND'
        -- Blessed related
        WHEN template_key::text IN ('hdb_blessed_email', 'hdb_blessed_whatsapp', 'hdb_blessed_rm_whatsapp', 'hdb_rm_bless_notify_sms', 'HDB_BLESSED_EMAIL', 'HDB_BLESSED_WHATSAPP', 'HDB_BLESSED_RM_WHATSAPP', 'HDB_RM_BLESS_NOTIFY_SMS') THEN 'BLESSED'
        WHEN template_key::text IN ('hdb_blessed_no_payment_email', 'hdb_blessed_no_payment_whatsapp', 'HDB_BLESSED_NO_PAYMENT_EMAIL', 'HDB_BLESSED_NO_PAYMENT_WHATSAPP') THEN 'BLESSED_NO_PAYMENT'
        WHEN template_key::text IN ('hdb_hold_email', 'HDB_HOLD_EMAIL') THEN 'HOLD'
        WHEN template_key::text IN ('hdb_rm_hold_email', 'HDB_RM_HOLD_EMAIL') THEN 'RM_HOLD'
        -- Payment related
        WHEN template_key::text IN ('hdb_invoice_email', 'hdb_invoice_whatsapp', 'HDB_INVOICE_EMAIL', 'HDB_INVOICE_WHATSAPP') THEN 'INVOICE'
        WHEN template_key::text IN ('hdb_payment_acknowledgement_offline_email', 'hdb_payment_acknowledgement_offline_whatsapp', 'HDB_PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL', 'HDB_PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WHATSAPP') THEN 'PAYMENT_ACKNOWLEDGEMENT_OFFLINE'
        WHEN template_key::text IN ('hdb_rm_payment_pending_reminder_whatsapp', 'HDB_RM_PAYMENT_PENDING_REMINDER_WHATSAPP') THEN 'PAYMENT_PENDING_REMINDER'
        -- Billing related
        WHEN template_key::text IN ('hdb_billing_details_edit_email', 'HDB_BILLING_DETAILS_EDIT_EMAIL') THEN 'BILLING_DETAILS_EDIT'
        WHEN template_key::text IN ('hdb_billing_details_edit_removed_email', 'HDB_BILLING_DETAILS_EDIT_REMOVED_EMAIL') THEN 'BILLING_DETAILS_REMOVED'
        WHEN template_key::text IN ('hdb_einvoice_error_email', 'HDB_EINVOICE_ERROR_EMAIL') THEN 'EINVOICE_ERROR'
        -- Travel related
        WHEN template_key::text IN ('hdb_coordinator_travel_plan_change_whatsapp', 'HDB_COORDINATOR_TRAVEL_PLAN_CHANGE_WHATSAPP') THEN 'TRAVEL_PLAN_CHANGE'
        WHEN template_key::text IN ('hdb_coordinator_new_travel_plan_made_whatsapp', 'HDB_COORDINATOR_NEW_TRAVEL_PLAN_MADE_WHATSAPP') THEN 'TRAVEL_PLAN_NEW'
        WHEN template_key::text IN ('hdb_coordinator_return_travel_plan_change_whatsapp', 'HDB_COORDINATOR_RETURN_TRAVEL_PLAN_CHANGE_WHATSAPP') THEN 'TRAVEL_PLAN_RETURN_CHANGE'
        WHEN template_key::text IN ('hdb_coordinator_onward_travel_plan_change_whatsapp', 'HDB_COORDINATOR_ONWARD_TRAVEL_PLAN_CHANGE_WHATSAPP') THEN 'TRAVEL_PLAN_ONWARD_CHANGE'
        -- Swap and preference
        WHEN template_key::text IN ('hdb_swap_demand_email', 'hdb_swap_demand_whatsapp', 'HDB_SWAP_DEMAND_EMAIL', 'HDB_SWAP_DEMAND_WHATSAPP') THEN 'SWAP_DEMAND'
        WHEN template_key::text IN ('hdb_rm_preference_edited_whatsapp', 'HDB_RM_PREFERENCE_EDITED_WHATSAPP') THEN 'PREFERENCE_EDITED'
        -- Admin and notifications
        WHEN template_key::text IN ('hdb_admin_message_notification_whatsapp', 'HDB_ADMIN_MESSAGE_NOTIFICATION_WHATSAPP') THEN 'ADMIN_MESSAGE_NOTIFICATION'
        -- Generic and OTP
        WHEN template_key::text IN ('generic_email', 'GENERIC_EMAIL') THEN 'GENERIC_EMAIL'
        WHEN template_key::text IN ('otp_email', 'otp_sms', 'OTP_EMAIL', 'OTP_SMS') THEN 'OTP'
        -- Program specific
        WHEN template_key::text IN ('hdb_first_timer', 'HDB_FIRST_TIMER') THEN 'FIRST_TIMER'
        WHEN template_key::text IN ('hdb_checklist', 'HDB_CHECKLIST') THEN 'CHECKLIST'
        WHEN template_key::text IN ('hdb_preparatory', 'HDB_PREPARATORY') THEN 'PREPARATORY'
        ELSE NULL
    END)::communication_template_access_key_enum
WHERE template_access_key IS NULL;


UPDATE hdb_communication_templates
SET template_access_key =
    (CASE
        -- Registration related
        WHEN template_key::text IN ('hdb_registration_completed_email', 'hdb_seeker_registration_completed_whatsapp', 'hdb_rm_registration_completed_whatsapp', 'hdb_rm_registration_notify_sms', 'HDB_REGISTRATION_COMPLETED_EMAIL', 'HDB_SEEKER_REGISTRATION_COMPLETED_WHATSAPP', 'HDB_RM_REGISTRATION_COMPLETED_WHATSAPP', 'HDB_RM_REGISTRATION_NOTIFY_SMS') THEN 'REGISTRATION_COMPLETED'
        WHEN template_key::text IN ('hdb_cancel_email', 'HDB_CANCEL_EMAIL') THEN 'REGISTRATION_CANCELLED'
        WHEN template_key::text IN ('hdb_registration_refund_email', 'HDB_REGISTRATION_REFUND_EMAIL') THEN 'REGISTRATION_REFUND'
        -- Blessed related
        WHEN template_key::text IN ('hdb_blessed_email', 'hdb_blessed_whatsapp', 'hdb_blessed_rm_whatsapp', 'hdb_rm_bless_notify_sms', 'HDB_BLESSED_EMAIL', 'HDB_BLESSED_WHATSAPP', 'HDB_BLESSED_RM_WHATSAPP', 'HDB_RM_BLESS_NOTIFY_SMS') THEN 'BLESSED'
        WHEN template_key::text IN ('hdb_blessed_no_payment_email', 'hdb_blessed_no_payment_whatsapp', 'HDB_BLESSED_NO_PAYMENT_EMAIL', 'HDB_BLESSED_NO_PAYMENT_WHATSAPP') THEN 'BLESSED_NO_PAYMENT'
        WHEN template_key::text IN ('hdb_hold_email', 'HDB_HOLD_EMAIL') THEN 'HOLD'
        WHEN template_key::text IN ('hdb_rm_hold_email', 'HDB_RM_HOLD_EMAIL') THEN 'RM_HOLD'
        -- Payment related
        WHEN template_key::text IN ('hdb_invoice_email', 'hdb_invoice_whatsapp', 'HDB_INVOICE_EMAIL', 'HDB_INVOICE_WHATSAPP') THEN 'INVOICE'
        WHEN template_key::text IN ('hdb_payment_acknowledgement_offline_email', 'hdb_payment_acknowledgement_offline_whatsapp', 'HDB_PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL', 'HDB_PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WHATSAPP') THEN 'PAYMENT_ACKNOWLEDGEMENT_OFFLINE'
        WHEN template_key::text IN ('hdb_rm_payment_pending_reminder_whatsapp', 'HDB_RM_PAYMENT_PENDING_REMINDER_WHATSAPP') THEN 'PAYMENT_PENDING_REMINDER'
        -- Billing related
        WHEN template_key::text IN ('hdb_billing_details_edit_email', 'HDB_BILLING_DETAILS_EDIT_EMAIL') THEN 'BILLING_DETAILS_EDIT'
        WHEN template_key::text IN ('hdb_billing_details_edit_removed_email', 'HDB_BILLING_DETAILS_EDIT_REMOVED_EMAIL') THEN 'BILLING_DETAILS_REMOVED'
        WHEN template_key::text IN ('hdb_einvoice_error_email', 'HDB_EINVOICE_ERROR_EMAIL') THEN 'EINVOICE_ERROR'
        -- Travel related
        WHEN template_key::text IN ('hdb_coordinator_travel_plan_change_whatsapp', 'HDB_COORDINATOR_TRAVEL_PLAN_CHANGE_WHATSAPP') THEN 'TRAVEL_PLAN_CHANGE'
        WHEN template_key::text IN ('hdb_coordinator_new_travel_plan_made_whatsapp', 'HDB_COORDINATOR_NEW_TRAVEL_PLAN_MADE_WHATSAPP') THEN 'TRAVEL_PLAN_NEW'
        WHEN template_key::text IN ('hdb_coordinator_return_travel_plan_change_whatsapp', 'HDB_COORDINATOR_RETURN_TRAVEL_PLAN_CHANGE_WHATSAPP') THEN 'TRAVEL_PLAN_RETURN_CHANGE'
        WHEN template_key::text IN ('hdb_coordinator_onward_travel_plan_change_whatsapp', 'HDB_COORDINATOR_ONWARD_TRAVEL_PLAN_CHANGE_WHATSAPP') THEN 'TRAVEL_PLAN_ONWARD_CHANGE'
        -- Swap and preference
        WHEN template_key::text IN ('hdb_swap_demand_email', 'hdb_swap_demand_whatsapp', 'HDB_SWAP_DEMAND_EMAIL', 'HDB_SWAP_DEMAND_WHATSAPP') THEN 'SWAP_DEMAND'
        WHEN template_key::text IN ('hdb_rm_preference_edited_whatsapp', 'HDB_RM_PREFERENCE_EDITED_WHATSAPP') THEN 'PREFERENCE_EDITED'
        -- Admin and notifications
        WHEN template_key::text IN ('hdb_admin_message_notification_whatsapp', 'HDB_ADMIN_MESSAGE_NOTIFICATION_WHATSAPP') THEN 'ADMIN_MESSAGE_NOTIFICATION'
        -- Generic and OTP
        WHEN template_key::text IN ('generic_email', 'GENERIC_EMAIL') THEN 'GENERIC_EMAIL'
        WHEN template_key::text IN ('otp_email', 'otp_sms', 'OTP_EMAIL', 'OTP_SMS') THEN 'OTP'
        -- Program specific
        WHEN template_key::text IN ('hdb_first_timer', 'HDB_FIRST_TIMER') THEN 'FIRST_TIMER'
        WHEN template_key::text IN ('hdb_checklist', 'HDB_CHECKLIST') THEN 'CHECKLIST'
        WHEN template_key::text IN ('hdb_preparatory', 'HDB_PREPARATORY') THEN 'PREPARATORY'
        ELSE NULL
    END)::communication_template_access_key_enum
WHERE template_access_key IS NULL;

-- ============================================================
-- PART 8: Make template_access_key NOT NULL (after updating existing records)
-- ============================================================

ALTER TABLE communication_templates_master
ALTER COLUMN template_access_key SET NOT NULL;

ALTER TABLE hdb_communication_templates
ALTER COLUMN template_access_key SET NOT NULL;

-- ============================================================
-- PART 9: Create Indexes
-- ============================================================

CREATE UNIQUE INDEX IF NOT EXISTS idx_ctm_template_key_program_type
    ON communication_templates_master(template_key, COALESCE(program_type_id, 0), template_type);
CREATE INDEX IF NOT EXISTS idx_ctm_template_key
    ON communication_templates_master(template_key);
CREATE INDEX IF NOT EXISTS idx_ctm_template_type
    ON communication_templates_master(template_type);
CREATE INDEX IF NOT EXISTS idx_ctm_category
    ON communication_templates_master(category);
CREATE INDEX IF NOT EXISTS idx_ctm_program_type_id
    ON communication_templates_master(program_type_id);
CREATE INDEX IF NOT EXISTS idx_ctm_is_active
    ON communication_templates_master(is_active);
CREATE INDEX IF NOT EXISTS idx_ctm_usage_type
    ON communication_templates_master(template_usage_type);
CREATE INDEX IF NOT EXISTS idx_ctm_template_access_key
    ON communication_templates_master(template_access_key);
CREATE INDEX IF NOT EXISTS idx_ct_template_access_key
    ON hdb_communication_templates(template_access_key);
CREATE INDEX IF NOT EXISTS idx_ct_sandbox_template_id
    ON hdb_communication_templates(sandbox_template_id);
CREATE INDEX IF NOT EXISTS idx_ct_program_access_key
    ON hdb_communication_templates(program_id, template_access_key);

-- Add new enum values if the enum already exists (for incremental migrations)
DO $$
BEGIN
    ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'REGISTRATION_COMPLETED_EMAIL_SEEKER';
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

DO $$
BEGIN
    ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'REGISTRATION_COMPLETED_WATI_SEEKER';
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

DO $$
BEGIN
    ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'REGISTRATION_COMPLETED_WATI_RM';
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

DO $$
BEGIN
    ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'REGISTRATION_COMPLETED_SMS_RM';
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

DO $$
BEGIN
    ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'BLESSED_EMAIL_SEEKER';
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

DO $$
BEGIN
    ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'BLESSED_WATI_SEEKER';
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

DO $$
BEGIN
    ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'BLESSED_WATI_RM';
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

DO $$
BEGIN
    ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'BLESSED_SMS_RM';
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

DO $$
BEGIN
    ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'BLESSED_NO_PAYMENT_EMAIL';
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

DO $$
BEGIN
    ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'BLESSED_NO_PAYMENT_WATI';
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

DO $$
BEGIN
    ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'INVOICE_EMAIL';
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

DO $$
BEGIN
    ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'INVOICE_WATI';
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

DO $$
BEGIN
    ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL';
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

DO $$
BEGIN
    ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WATI';
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

DO $$
BEGIN
    ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'SWAP_DEMAND_EMAIL';
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

DO $$
BEGIN
    ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'SWAP_DEMAND_WATI';
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

DO $$
BEGIN
    ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'OTP_EMAIL';
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

DO $$
BEGIN
    ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'OTP_SMS';
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

-- ============================================================
-- PART 10: Verification Queries
-- ============================================================

-- Check master templates by access key
SELECT template_access_key, COUNT(*) AS count
FROM communication_templates_master
WHERE template_access_key IS NOT NULL
GROUP BY template_access_key
ORDER BY count DESC;

-- Check program templates by access key
SELECT template_access_key, COUNT(*) AS count
FROM hdb_communication_templates
WHERE template_access_key IS NOT NULL
GROUP BY template_access_key
ORDER BY count DESC;


-- ============================================================
-- PART 6: Add Comments to Master Table
-- ============================================================

COMMENT ON TABLE communication_templates_master IS 'Master communication templates with template IDs and merge field mappings';
COMMENT ON COLUMN communication_templates_master.template_id IS 'Production template ID from provider (Zepto/WATI/MSG91)';
COMMENT ON COLUMN communication_templates_master.sandbox_template_id IS 'Sandbox/dev template ID from provider';
COMMENT ON COLUMN communication_templates_master.category IS 'Template category for classification';
COMMENT ON COLUMN communication_templates_master.template_usage_type IS 'Usage type: general, bulk_email, or portal';
COMMENT ON COLUMN communication_templates_master.template_access_key IS 'Common access key across program types for same purpose (e.g., REGISTRATION_COMPLETED for both HDB and ENT)';
COMMENT ON COLUMN communication_templates_master.program_type_id IS 'Associated program type (nullable for generic templates)';
COMMENT ON COLUMN communication_templates_master.merge_field_map IS 'JSON array of merge field mappings';

-- ============================================================

-- ============================================================
-- PART 8: Update Existing HDB Records
-- ============================================================

-- Update existing records to bulk_email
UPDATE hdb_communication_templates
SET template_usage_type = 'bulk_email'
WHERE template_usage_type IS NULL OR template_usage_type = 'general';

-- Update existing records with access keys based on template_key patterns
UPDATE hdb_communication_templates
SET template_access_key =
    (CASE
        -- Registration related
        WHEN template_key::text IN ('HDB_REGISTRATION_COMPLETED_EMAIL', 'HDB_SEEKER_REGISTRATION_COMPLETED_WHATSAPP', 'HDB_RM_REGISTRATION_COMPLETED_WHATSAPP', 'HDB_RM_REGISTRATION_NOTIFY_SMS') THEN 'REGISTRATION_COMPLETED'
        WHEN template_key::text = 'HDB_CANCEL_EMAIL' THEN 'REGISTRATION_CANCELLED'
        WHEN template_key::text = 'HDB_REGISTRATION_REFUND_EMAIL' THEN 'REGISTRATION_REFUND'

        -- Blessed related
        WHEN template_key::text IN ('HDB_BLESSED_EMAIL', 'HDB_BLESSED_WHATSAPP', 'HDB_BLESSED_RM_WHATSAPP', 'HDB_RM_BLESS_NOTIFY_SMS') THEN 'BLESSED'
        WHEN template_key::text IN ('HDB_BLESSED_NO_PAYMENT_EMAIL', 'HDB_BLESSED_NO_PAYMENT_WHATSAPP') THEN 'BLESSED_NO_PAYMENT'
        WHEN template_key::text = 'HDB_HOLD_EMAIL' THEN 'HOLD'
        WHEN template_key::text = 'HDB_RM_HOLD_EMAIL' THEN 'RM_HOLD'

        -- Payment related
        WHEN template_key::text IN ('HDB_INVOICE_EMAIL', 'HDB_INVOICE_WHATSAPP') THEN 'INVOICE'
        WHEN template_key::text IN ('HDB_PAYMENT_ACKNOWLEDGEMENT_OFFLINE_EMAIL', 'HDB_PAYMENT_ACKNOWLEDGEMENT_OFFLINE_WHATSAPP') THEN 'PAYMENT_ACKNOWLEDGEMENT_OFFLINE'
        WHEN template_key::text = 'HDB_RM_PAYMENT_PENDING_REMINDER_WHATSAPP' THEN 'PAYMENT_PENDING_REMINDER'

        -- Billing related
        WHEN template_key::text = 'HDB_BILLING_DETAILS_EDIT_EMAIL' THEN 'BILLING_DETAILS_EDIT'
        WHEN template_key::text = 'HDB_BILLING_DETAILS_EDIT_REMOVED_EMAIL' THEN 'BILLING_DETAILS_REMOVED'
        WHEN template_key::text = 'HDB_EINVOICE_ERROR_EMAIL' THEN 'EINVOICE_ERROR'

        -- Travel related
        WHEN template_key::text = 'HDB_COORDINATOR_TRAVEL_PLAN_CHANGE_WHATSAPP' THEN 'TRAVEL_PLAN_CHANGE'
        WHEN template_key::text = 'HDB_COORDINATOR_NEW_TRAVEL_PLAN_MADE_WHATSAPP' THEN 'TRAVEL_PLAN_NEW'
        WHEN template_key::text = 'HDB_COORDINATOR_RETURN_TRAVEL_PLAN_CHANGE_WHATSAPP' THEN 'TRAVEL_PLAN_RETURN_CHANGE'
        WHEN template_key::text = 'HDB_COORDINATOR_ONWARD_TRAVEL_PLAN_CHANGE_WHATSAPP' THEN 'TRAVEL_PLAN_ONWARD_CHANGE'

        -- Swap and preference
        WHEN template_key::text IN ('HDB_SWAP_DEMAND_EMAIL', 'HDB_SWAP_DEMAND_WHATSAPP') THEN 'SWAP_DEMAND'
        WHEN template_key::text = 'HDB_RM_PREFERENCE_EDITED_WHATSAPP' THEN 'PREFERENCE_EDITED'

        -- Admin and notifications
        WHEN template_key::text = 'HDB_ADMIN_MESSAGE_NOTIFICATION_WHATSAPP' THEN 'ADMIN_MESSAGE_NOTIFICATION'

        -- Generic and OTP
        WHEN template_key::text = 'GENERIC_EMAIL' THEN 'GENERIC_EMAIL'
        WHEN template_key::text IN ('OTP_EMAIL', 'OTP_SMS') THEN 'OTP'

        -- Program specific
        WHEN template_key::text = 'HDB_FIRST_TIMER' THEN 'FIRST_TIMER'
        WHEN template_key::text = 'HDB_CHECKLIST' THEN 'CHECKLIST'
        WHEN template_key::text = 'HDB_PREPARATORY' THEN 'PREPARATORY'

        ELSE NULL
    END)::communication_template_access_key_enum
WHERE template_access_key IS NULL;

-- Ensure enum value exists for PAYMENT_PENDING_REMINDER_WATI_RM
DO $$
BEGIN
    ALTER TYPE communication_template_access_key_enum ADD VALUE IF NOT EXISTS 'PAYMENT_PENDING_REMINDER_WATI_RM';
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

-- ============================================================
-- PART 9: Create Indexes for HDB Communication Templates
-- ============================================================

CREATE INDEX IF NOT EXISTS idx_ct_template_access_key
    ON hdb_communication_templates(template_access_key);

-- Composite index for common queries
CREATE INDEX IF NOT EXISTS idx_ct_program_access_key
    ON hdb_communication_templates(program_id, template_access_key);

-- ============================================================
-- PART 10: Make template_access_key NOT NULL (Optional - Uncomment when ready)
-- ============================================================

-- Uncomment these after all existing data has been populated with access keys
-- ALTER TABLE communication_templates_master
-- ALTER COLUMN template_access_key SET NOT NULL;

-- ALTER TABLE hdb_communication_templates
-- ALTER COLUMN template_access_key SET NOT NULL;

-- ============================================================
-- VERIFICATION QUERIES
-- ============================================================

-- Check master templates by access key
SELECT template_access_key, COUNT(*) AS count
FROM communication_templates_master
WHERE template_access_key IS NOT NULL
GROUP BY template_access_key
ORDER BY count DESC;

-- Check program templates by access key
SELECT template_access_key, COUNT(*) AS count
FROM hdb_communication_templates
WHERE template_access_key IS NOT NULL
GROUP BY template_access_key
ORDER BY count DESC;

-- ============================================================
-- END OF MIGRATION
-- ============================================================

-- ============================================================
-- CONSOLIDATION NOTICE
-- ============================================================
-- This migration (07) now includes ALL enum values and schema changes
-- that were previously in files 09-14. Those files can be safely removed:
--
-- Files Consolidated:
-- - 09_final-template-access-key-comprehensive-fix-1.sql (all enums)
-- - 10_final-template-access-key-comprehensive-fix-2.sql (access key updates)
-- - 11_fix-coordinator-travel-plan-merge-fields.sql (coordinator templates)
-- - 12_fix-msg91-sms-merge-fields.sql (SMS templates)
-- - 12_payment-template-merge-info-fixes.sql (payment templates)
-- - 13_fix-allocated-program-field-references.sql (allocated fields)
-- - 14_add-is-per-recipient-to-merge-info-map.sql (is_per_recipient column)
--
-- File 08 (communication-templates-seed-complete-v2.sql) contains the
-- complete, corrected seed data with all fixes already applied.
--
-- Files to Keep:
-- - 07_communication-templates-master-and-access-key.sql (this file)
-- - 08_communication-templates-seed-complete-v2.sql (master seed data)
-- - 11_clone-templates-to-existing-programs.sql (program cloning logic)
-- - 13_sync-hdb-templates-with-master.sql (sync logic for updates)
-- ============================================================
