fm_be/migrations/goose/20260525_medicine_clegacy_import_apply.sql

-- +goose Up
-- Apply legacy c* staging data into target tables:
-- - medicine_groups
-- - motor_reactions
-- - medicines
-- - medicine_motor_reactions (optional backfill via motor_reaction_id)

START TRANSACTION;

CREATE TABLE IF NOT EXISTS cmedikamente_group_old_import (
  id INT(11) NOT NULL,
  name VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE IF NOT EXISTS cmotorische_old_import (
  id INT(11) NOT NULL,
  name VARCHAR(255) DEFAULT NULL,
  ball INT(11) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE IF NOT EXISTS cmedikamente_old_import (
  id INT(11) NOT NULL,
  name VARCHAR(255) DEFAULT NULL,
  dose VARCHAR(255) DEFAULT NULL,
  weight INT(11) DEFAULT NULL,
  col INT(11) DEFAULT NULL,
  cmedikamente_group_id INT(11) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_cmedikamente_old_import_group_id (cmedikamente_group_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE IF NOT EXISTS cmedikamente_group_old_id_map (
  old_id INT(11) NOT NULL,
  new_id BINARY(16) NOT NULL,
  PRIMARY KEY (old_id),
  UNIQUE KEY uq_cmedikamente_group_old_new_id (new_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE IF NOT EXISTS cmotorische_old_id_map (
  old_id INT(11) NOT NULL,
  new_id BINARY(16) NOT NULL,
  PRIMARY KEY (old_id),
  UNIQUE KEY uq_cmotorische_old_new_id (new_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE IF NOT EXISTS cmedikamente_old_id_map (
  old_id INT(11) NOT NULL,
  new_id BINARY(16) NOT NULL,
  PRIMARY KEY (old_id),
  UNIQUE KEY uq_cmedikamente_old_new_id (new_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

INSERT INTO cmedikamente_group_old_id_map (old_id, new_id)
SELECT
  s.id,
  UNHEX(REPLACE(UUID(), '-', ''))
FROM cmedikamente_group_old_import s
LEFT JOIN cmedikamente_group_old_id_map m ON m.old_id = s.id
WHERE m.old_id IS NULL;

INSERT INTO cmotorische_old_id_map (old_id, new_id)
SELECT
  s.id,
  UNHEX(REPLACE(UUID(), '-', ''))
FROM cmotorische_old_import s
LEFT JOIN cmotorische_old_id_map m ON m.old_id = s.id
WHERE m.old_id IS NULL;

INSERT INTO cmedikamente_old_id_map (old_id, new_id)
SELECT
  s.id,
  UNHEX(REPLACE(UUID(), '-', ''))
FROM cmedikamente_old_import s
LEFT JOIN cmedikamente_old_id_map m ON m.old_id = s.id
WHERE m.old_id IS NULL;

INSERT INTO medicine_groups (
  id,
  name,
  is_active,
  created_at,
  updated_at
)
SELECT
  map.new_id,
  LEFT(COALESCE(NULLIF(TRIM(s.name), ''), CONCAT('Medicine Group ', s.id)), 255),
  1,
  UTC_TIMESTAMP(),
  UTC_TIMESTAMP()
FROM cmedikamente_group_old_import s
JOIN cmedikamente_group_old_id_map map ON map.old_id = s.id
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  is_active = 1,
  updated_at = UTC_TIMESTAMP();

INSERT INTO motor_reactions (
  id,
  name,
  score,
  is_active,
  created_at,
  updated_at
)
SELECT
  map.new_id,
  LEFT(COALESCE(NULLIF(TRIM(s.name), ''), CONCAT('Motor Reaction ', s.id)), 255),
  s.ball,
  1,
  UTC_TIMESTAMP(),
  UTC_TIMESTAMP()
FROM cmotorische_old_import s
JOIN cmotorische_old_id_map map ON map.old_id = s.id
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  score = VALUES(score),
  is_active = 1,
  updated_at = UTC_TIMESTAMP();

INSERT INTO medicines (
  id,
  name,
  dose,
  weight,
  `column`,
  medicine_group_id,
  is_active,
  created_at,
  updated_at
)
SELECT
  med_map.new_id,
  LEFT(COALESCE(NULLIF(TRIM(s.name), ''), CONCAT('Medicine ', s.id)), 128),
  LEFT(TRIM(COALESCE(s.dose, '')), 128),
  s.weight,
  COALESCE(s.col, 0),
  grp_map.new_id,
  1,
  UTC_TIMESTAMP(),
  UTC_TIMESTAMP()
FROM cmedikamente_old_import s
JOIN cmedikamente_old_id_map med_map ON med_map.old_id = s.id
LEFT JOIN cmedikamente_group_old_id_map grp_map ON grp_map.old_id = s.cmedikamente_group_id
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  dose = VALUES(dose),
  weight = VALUES(weight),
  `column` = VALUES(`column`),
  medicine_group_id = VALUES(medicine_group_id),
  is_active = 1,
  updated_at = UTC_TIMESTAMP();

-- Optional compatibility backfill: if medicines.motor_reaction_id exists, sync into join table.
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;

COMMIT;

-- +goose Down
-- Data migration is intentionally non-reversible.
SELECT 1;