-- +goose Up
CREATE TABLE IF NOT EXISTS medicine_motor_reactions (
  medicine_id BINARY(16) NOT NULL,
  motor_reaction_id BINARY(16) NOT NULL,
  PRIMARY KEY (medicine_id, motor_reaction_id),
  KEY idx_mmr_motor_reaction_id (motor_reaction_id),
  CONSTRAINT fk_mmr_medicine FOREIGN KEY (medicine_id) REFERENCES medicines(id) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_mmr_motor_reaction FOREIGN KEY (motor_reaction_id) REFERENCES motor_reactions(id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO medicine_motor_reactions (medicine_id, motor_reaction_id)
SELECT id, motor_reaction_id
FROM medicines
WHERE motor_reaction_id IS NOT NULL;

-- +goose Down
DROP TABLE IF EXISTS medicine_motor_reactions;