-- RPN factors: same semantics as SAT `rpn_*` tables, stored as `orac_likelihood_of_harm` / `orac_severity_of_harm`.
-- Optional: run if these tables are not already present. Skip or edit if your DB already has them.

CREATE TABLE IF NOT EXISTS `orac_likelihood_of_harm` (
  `likelihood_of_harm` VARCHAR(512) NOT NULL,
  `value` DECIMAL(12, 4) NOT NULL,
  PRIMARY KEY (`likelihood_of_harm`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `orac_severity_of_harm` (
  `severity_of_harm` VARCHAR(512) NOT NULL,
  `value` DECIMAL(12, 4) NOT NULL,
  PRIMARY KEY (`severity_of_harm`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed defaults (idempotent: ignore duplicates)
INSERT IGNORE INTO `orac_likelihood_of_harm` (`likelihood_of_harm`, `value`) VALUES
  ('Remote', 1),
  ('Possible', 2),
  ('Likely', 4),
  ('Probable', 8);

INSERT IGNORE INTO `orac_severity_of_harm` (`severity_of_harm`, `value`) VALUES
  ('Low', 1),
  ('Medium', 2),
  ('High', 4),
  ('Severe', 8),
  ('Fatality', 16);
