-- Scheduled follow-ups (reminder notification or resend campaign email)

CREATE TABLE IF NOT EXISTS campaign_contact_followups (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  campaign_id INT NOT NULL,
  pipeline_contact_id BIGINT NULL,
  recipient_id BIGINT NULL,
  conversation_id BIGINT NULL,
  contact_email VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  contact_name VARCHAR(200) NULL,
  action_type ENUM('reminder', 'resend_email') NOT NULL,
  scheduled_at DATETIME NOT NULL,
  status ENUM('pending', 'processing', 'completed', 'cancelled', 'failed') NOT NULL DEFAULT 'pending',
  notes TEXT NULL,
  created_by INT NOT NULL,
  completed_at DATETIME NULL,
  error_message VARCHAR(500) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_followup_due (status, scheduled_at),
  INDEX idx_followup_campaign (campaign_id, contact_email),
  CONSTRAINT fk_followup_campaign FOREIGN KEY (campaign_id) REFERENCES email_campaigns(id) ON DELETE CASCADE,
  CONSTRAINT fk_followup_creator FOREIGN KEY (created_by) REFERENCES users(id)
);

ALTER TABLE email_delivery_events
  MODIFY event_type ENUM(
    'sent', 'delivered', 'bounced', 'complained', 'deferred', 'failed', 'open', 'click', 'followup_resend'
  ) NOT NULL;