-- 0023 — Phase 9 medical upgrades + rule usage telemetry
--
-- Two additive changes:
--   1. UserProfile: add canonical `sex` (MALE/FEMALE/OTHER) + `heightCm`
--      for BMI enforcement. Legacy `gender` free-text stays for backward
--      compat; medical services prefer `sex` when populated.
--   2. New `rule_usage_events` table records per-rule trigger events
--      (WARNING / BLOCK / OVERRIDE) for manager-facing analytics. Kept
--      separate from audit_logs to avoid polluting the checksum-chained
--      audit trail with high-volume evaluation events.
--
-- Additive only. Safe to re-run on MySQL since ALTER TABLE IF NOT EXISTS
-- and CREATE TABLE IF NOT EXISTS handle repeats.

-- 1. UserProfile.sex + heightCm
ALTER TABLE `user_profiles`
  ADD COLUMN IF NOT EXISTS `sex` VARCHAR(10) NULL AFTER `gender`,
  ADD COLUMN IF NOT EXISTS `heightCm` INT NULL AFTER `sex`;

-- 2. RuleUsageEvent telemetry table
CREATE TABLE IF NOT EXISTS `rule_usage_events` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `dropzoneId` INT NOT NULL,
  `ruleKey` VARCHAR(100) NOT NULL,
  `category` VARCHAR(30) NOT NULL,
  `outcome` VARCHAR(30) NOT NULL,
  `userId` INT NULL,
  `loadId` INT NULL,
  `slotId` INT NULL,
  `actorUserId` INT NULL,
  `context` TEXT NULL,
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  INDEX `rule_usage_events_dz_createdAt_idx` (`dropzoneId`, `createdAt`),
  INDEX `rule_usage_events_ruleKey_createdAt_idx` (`ruleKey`, `createdAt`),
  INDEX `rule_usage_events_category_idx` (`category`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;