-- +goose Up
CREATE TABLE IF NOT EXISTS medicine_groups (
  id BINARY(16) NOT NULL,
  name VARCHAR(255) NOT NULL,
  sortkey INT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  created_by BINARY(16) NULL,
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  updated_by BINARY(16) NULL,
  deleted_at DATETIME(6) NULL,
  deleted_by BINARY(16) NULL,
  PRIMARY KEY (id),
  KEY idx_medicine_groups_is_active (is_active),
  KEY idx_medicine_groups_sortkey (sortkey)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS motor_reactions (
  id BINARY(16) NOT NULL,
  name VARCHAR(255) NOT NULL,
  score BIGINT NULL,
  sortkey INT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  created_by BINARY(16) NULL,
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  updated_by BINARY(16) NULL,
  deleted_at DATETIME(6) NULL,
  deleted_by BINARY(16) NULL,
  PRIMARY KEY (id),
  KEY idx_motor_reactions_is_active (is_active),
  KEY idx_motor_reactions_sortkey (sortkey)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE medicines
  ADD COLUMN weight BIGINT NULL AFTER dose,
  ADD COLUMN medicine_group_id BINARY(16) NULL AFTER `column`,
  ADD COLUMN motor_reaction_id BINARY(16) NULL AFTER medicine_group_id,
  ADD KEY idx_medicines_medicine_group_id (medicine_group_id),
  ADD KEY idx_medicines_motor_reaction_id (motor_reaction_id),
  ADD CONSTRAINT fk_medicines_medicine_group FOREIGN KEY (medicine_group_id) REFERENCES medicine_groups(id) ON UPDATE CASCADE ON DELETE SET NULL,
  ADD CONSTRAINT fk_medicines_motor_reaction FOREIGN KEY (motor_reaction_id) REFERENCES motor_reactions(id) ON UPDATE CASCADE ON DELETE SET NULL;

-- +goose Down
ALTER TABLE medicines
  DROP FOREIGN KEY fk_medicines_medicine_group,
  DROP FOREIGN KEY fk_medicines_motor_reaction,
  DROP KEY idx_medicines_medicine_group_id,
  DROP KEY idx_medicines_motor_reaction_id,
  DROP COLUMN medicine_group_id,
  DROP COLUMN motor_reaction_id,
  DROP COLUMN weight;

DROP TABLE IF EXISTS motor_reactions;
DROP TABLE IF EXISTS medicine_groups;