CREATE TABLE IF NOT EXISTS opportunity_channels (
  opportunity_id BIGINT UNSIGNED NOT NULL,
  channel VARCHAR(80) NOT NULL,
  remote_id VARCHAR(100) NOT NULL,
  origin_url VARCHAR(500) NULL,
  payload JSON NOT NULL,
  collected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (opportunity_id,channel),
  UNIQUE KEY unique_remote_channel (channel,remote_id),
  FOREIGN KEY (opportunity_id) REFERENCES opportunities(id)
) ENGINE=InnoDB;
INSERT IGNORE INTO opportunity_channels (opportunity_id,channel,remote_id,origin_url,payload)
SELECT opportunity_id,'PNCP',external_id,official_url,payload FROM opportunity_sources;