-- =====================================================================
-- Migration: hdb_communication_templates.deleted_at (rev 11 — clone soft-delete)
-- Description: When an admin removes a template across all its steps for a
--              program (attached_steps becomes empty), the clone row is
--              soft-deleted (deleted_at set). Restored on re-tick.
-- Idempotent: safe to re-run.
-- =====================================================================

ALTER TABLE hdb_communication_templates
  ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ NULL;

COMMENT ON COLUMN hdb_communication_templates.deleted_at IS
  'Set when attached_steps is empty (no step wired for this program). Cleared on re-tick.';

CREATE INDEX IF NOT EXISTS idx_hct_program_active
  ON hdb_communication_templates (program_id)
  WHERE deleted_at IS NULL;
