SET NAMES utf8mb4;

ALTER TABLE destinations ADD COLUMN IF NOT EXISTS integration_id BIGINT UNSIGNED NULL AFTER channel_id;
ALTER TABLE scheduled_posts ADD COLUMN IF NOT EXISTS publication_confirmed_at DATETIME NULL AFTER published_at;
ALTER TABLE publication_attempts ADD COLUMN IF NOT EXISTS remote_post_id VARCHAR(255) NULL;
ALTER TABLE publication_attempts ADD COLUMN IF NOT EXISTS correlation_id VARCHAR(80) NULL;

CREATE INDEX IF NOT EXISTS idx_dest_integration ON destinations(integration_id);
CREATE INDEX IF NOT EXISTS idx_pa_correlation ON publication_attempts(correlation_id,created_at);
CREATE UNIQUE INDEX IF NOT EXISTS uq_queue_post_job ON queue_jobs(scheduled_post_id,job_type);

ALTER TABLE destinations
  ADD CONSTRAINT fk_dest_integration
  FOREIGN KEY(integration_id) REFERENCES integrations(id) ON DELETE SET NULL;
