-- Migration: Communication Template Configuration — Schema
-- Description: Adds step / subject / body / is_default columns to communication_templates_master,
--              adds is_enabled / master_template_id / step / attached_steps to
--              hdb_communication_templates, and creates the merge_field_catalog table.
-- Spec: docs/technical-specs/COMMUNICATION_TEMPLATE_CONFIG_SPEC.md
-- Idempotent — safe to re-run.

-- =====================================================================
-- Step 1: communication_templates_master — new columns
-- =====================================================================
ALTER TABLE communication_templates_master
  ADD COLUMN IF NOT EXISTS step       VARCHAR(64)  NULL,
  ADD COLUMN IF NOT EXISTS subject    VARCHAR(500) NULL,
  ADD COLUMN IF NOT EXISTS body       TEXT         NULL,
  ADD COLUMN IF NOT EXISTS is_default BOOLEAN      NOT NULL DEFAULT true;

COMMENT ON COLUMN communication_templates_master.step IS 'CommunicationStepEnum value — the lifecycle step this template fires on';
COMMENT ON COLUMN communication_templates_master.is_default IS 'When true this template auto-enables on new programs of its program_type';

-- =====================================================================
-- Step 2: hdb_communication_templates — new columns
--   `step` mirrors the master's NATIVE step (snapshot).
--   `attached_steps` is the per-(program, master) array of steps the template
--   is currently wired to. Native attachment lives in this array too. Cross-step
--   attachment = same master, multiple step values in the array. Empty array +
--   `deleted_at` set = admin removed it from every step.
-- =====================================================================
ALTER TABLE hdb_communication_templates
  ADD COLUMN IF NOT EXISTS is_enabled         BOOLEAN     NOT NULL DEFAULT true,
  ADD COLUMN IF NOT EXISTS master_template_id BIGINT      NULL REFERENCES communication_templates_master(id) ON DELETE SET NULL,
  ADD COLUMN IF NOT EXISTS step               VARCHAR(64) NULL,
  ADD COLUMN IF NOT EXISTS attached_steps     TEXT[]      NOT NULL DEFAULT ARRAY[]::TEXT[];

COMMENT ON COLUMN hdb_communication_templates.is_enabled IS 'Legacy per-program on/off bit. Kept for back-compat; per-step state lives in attached_steps.';
COMMENT ON COLUMN hdb_communication_templates.master_template_id IS 'FK back to the master row this clone was created from.';
COMMENT ON COLUMN hdb_communication_templates.attached_steps IS 'Per-(program, master) array of steps this template fires on. Native step is included by default. Empty array means not attached anywhere.';

-- =====================================================================
-- Step 3: Uniqueness on master
-- =====================================================================
CREATE UNIQUE INDEX IF NOT EXISTS uq_ctm_pt_ak_type
  ON communication_templates_master (program_type_id, template_access_key, template_type)
  WHERE is_active = true;

-- =====================================================================
-- Step 4: Supporting indexes
-- =====================================================================
CREATE INDEX IF NOT EXISTS idx_ctm_step_channel
  ON communication_templates_master (program_type_id, step, template_type)
  WHERE is_active = true;

CREATE INDEX IF NOT EXISTS idx_ctm_program_type_default
  ON communication_templates_master (program_type_id, is_default)
  WHERE is_active = true;

CREATE INDEX IF NOT EXISTS idx_hct_program_master
  ON hdb_communication_templates (program_id, master_template_id);

-- GIN index supports "find clones of this program attached to step S" via
-- WHERE program_id = ? AND :step = ANY(attached_steps).
CREATE INDEX IF NOT EXISTS idx_hct_attached_steps_gin
  ON hdb_communication_templates USING GIN (attached_steps);

-- =====================================================================
-- Step 5: merge_field_catalog table
-- =====================================================================
CREATE TABLE IF NOT EXISTS merge_field_catalog (
  id                BIGSERIAL    PRIMARY KEY,
  catalog_key       VARCHAR(128) NOT NULL UNIQUE,
  display_name      VARCHAR(255) NOT NULL,
  source_table      VARCHAR(255) NOT NULL,
  source_column     VARCHAR(255) NOT NULL,
  data_type         VARCHAR(32)  NOT NULL,
  format_type       VARCHAR(50)  NULL,
  default_value     VARCHAR(500) NULL,
  description       VARCHAR(500) NULL,
  is_per_recipient  BOOLEAN      NOT NULL DEFAULT false,
  is_active         BOOLEAN      NOT NULL DEFAULT true,
  created_at        TIMESTAMPTZ  NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMPTZ  NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by        BIGINT       NULL,
  updated_by        BIGINT       NULL
);

COMMENT ON TABLE merge_field_catalog IS 'Global registry of backend-resolvable merge fields used by master merge_field_map JSONB';

CREATE INDEX IF NOT EXISTS idx_mfc_active ON merge_field_catalog (is_active);
CREATE INDEX IF NOT EXISTS idx_mfc_data_type ON merge_field_catalog (data_type);
