-- Migration 021: sequence_steps table for campaign step builder
-- Steps define the ordered, multi-channel touch sequence for a campaign.

CREATE TABLE IF NOT EXISTS sequence_steps (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    campaign_id     BIGINT UNSIGNED NOT NULL,
    step_order      TINYINT UNSIGNED NOT NULL DEFAULT 1,
    channel         ENUM('linkedin','email','x','instagram','facebook','webhook') NOT NULL DEFAULT 'email',
    delay_days      TINYINT UNSIGNED NOT NULL DEFAULT 1,
    message_template TEXT NULL,
    condition_type  ENUM('always','if_no_reply','if_opened') NOT NULL DEFAULT 'always',
    is_active       TINYINT(1) NOT NULL DEFAULT 1,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_seq_campaign (campaign_id),
    CONSTRAINT fk_seq_campaign FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
