ALTER TABLE internal_wall_posts
    ADD COLUMN target_filters TEXT NULL AFTER target_lead_id;

CREATE TABLE communication_templates (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    title VARCHAR(180) NOT NULL,
    summary VARCHAR(255) NULL,
    body TEXT NOT NULL,
    type VARCHAR(40) NOT NULL DEFAULT 'announcement',
    audience ENUM('internal','clients','both') NOT NULL DEFAULT 'clients',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_communication_templates_active (is_active),
    CONSTRAINT fk_communication_templates_created_by FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_communication_templates_updated_by FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE communication_automations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    template_id INT UNSIGNED NOT NULL,
    trigger_type ENUM('lead_status','proposal_status') NOT NULL,
    trigger_value VARCHAR(60) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_communication_automations_active (is_active),
    INDEX idx_communication_automations_trigger (trigger_type, trigger_value),
    CONSTRAINT fk_communication_automations_template FOREIGN KEY (template_id) REFERENCES communication_templates (id) ON DELETE CASCADE,
    CONSTRAINT fk_communication_automations_created_by FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_communication_automations_updated_by FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;