fm_be/migrations/goose/20260530_bases_import_apply.sql

-- +goose Up
-- Apply legacy base staging data into new bases schema.

START TRANSACTION;

-- 1) Reuse existing base rows by normalized (base, abbreviation) when possible.
INSERT INTO base_old_id_map (old_id, new_id)
SELECT
  s.id AS old_id,
  b.id AS new_id
FROM bases_old_import s
JOIN bases b
  ON LOWER(TRIM(b.base)) = LOWER(TRIM(s.name))
 AND LOWER(TRIM(COALESCE(b.base_abbreviation, ''))) = LOWER(TRIM(COALESCE(s.code, '')))
LEFT JOIN base_old_id_map m ON m.old_id = s.id
WHERE m.old_id IS NULL;

-- 2) Generate IDs for remaining legacy bases.
INSERT INTO base_old_id_map (old_id, new_id)
SELECT
  s.id AS old_id,
  UNHEX(REPLACE(
    LOWER(CONCAT(
      LPAD(HEX(FLOOR(CAST(UNIX_TIMESTAMP(UTC_TIMESTAMP(3)) * 1000 AS UNSIGNED) / 65536)), 8, '0'),
      '-',
      LPAD(HEX(MOD(CAST(UNIX_TIMESTAMP(UTC_TIMESTAMP(3)) * 1000 AS UNSIGNED), 65536)), 4, '0'),
      '-',
      '7', LPAD(HEX(FLOOR(RAND() * 4096)), 3, '0'),
      '-',
      LPAD(HEX(FLOOR(RAND() * 16384) + 32768), 4, '0'),
      '-',
      LPAD(HEX(FLOOR(RAND() * 281474976710656)), 12, '0')
    )),
    '-', ''
  )) AS new_id
FROM bases_old_import s
LEFT JOIN base_old_id_map m ON m.old_id = s.id
WHERE m.old_id IS NULL;

-- 3) Upsert base rows.
INSERT INTO bases (
  id,
  base,
  base_category_id,
  base_abbreviation,
  address,
  landline_number,
  mobile_number,
  email,
  sortkey,
  sms_alert,
  checklist,
  dry,
  control_center,
  dul,
  is_active,
  created_at,
  updated_at
)
SELECT
  m.new_id AS id,
  LEFT(TRIM(COALESCE(s.name, '')), 150) AS base,
  bc.id AS base_category_id,
  LEFT(TRIM(COALESCE(s.code, '')), 64) AS base_abbreviation,
  LEFT(TRIM(COALESCE(s.address, '')), 255) AS address,
  LEFT(TRIM(COALESCE(s.phone, '')), 64) AS landline_number,
  LEFT(TRIM(COALESCE(s.mobile, '')), 64) AS mobile_number,
  LEFT(TRIM(COALESCE(s.email, '')), 191) AS email,
  COALESCE(s.position, 0) AS sortkey,
  CASE WHEN COALESCE(s.sms_alert, 0) <> 0 THEN 1 ELSE 0 END AS sms_alert,
  CASE WHEN COALESCE(s.check_list, 0) <> 0 THEN 1 ELSE 0 END AS checklist,
  CASE WHEN COALESCE(s.dry, 0) <> 0 THEN 1 ELSE 0 END AS dry,
  CASE WHEN COALESCE(s.control_center, 0) <> 0 THEN 1 ELSE 0 END AS control_center,
  CASE WHEN COALESCE(s.service_transfer_list, 0) <> 0 THEN 1 ELSE 0 END AS dul,
  CASE WHEN COALESCE(s.visible, 0) <> 0 THEN 1 ELSE 0 END AS is_active,
  UTC_TIMESTAMP() AS created_at,
  UTC_TIMESTAMP() AS updated_at
FROM bases_old_import s
JOIN base_old_id_map m ON m.old_id = s.id
LEFT JOIN base_categories bc
  ON bc.`key` = CASE WHEN LOWER(TRIM(COALESCE(s.type, ''))) = 'hems' THEN 'hems' ELSE 'regular' END
ON DUPLICATE KEY UPDATE
  base = VALUES(base),
  base_category_id = VALUES(base_category_id),
  base_abbreviation = VALUES(base_abbreviation),
  address = VALUES(address),
  landline_number = VALUES(landline_number),
  mobile_number = VALUES(mobile_number),
  email = VALUES(email),
  sortkey = VALUES(sortkey),
  sms_alert = VALUES(sms_alert),
  checklist = VALUES(checklist),
  dry = VALUES(dry),
  control_center = VALUES(control_center),
  dul = VALUES(dul),
  is_active = VALUES(is_active),
  updated_at = UTC_TIMESTAMP();

-- 4) Build extracted legacy base-contact links from serialized fields.
DROP TEMPORARY TABLE IF EXISTS tmp_base_contact_extract;
CREATE TEMPORARY TABLE tmp_base_contact_extract (
  old_base_id int(11) NOT NULL,
  role_code varchar(64) NOT NULL,
  legacy_user_id int(11) NOT NULL,
  PRIMARY KEY (old_base_id, role_code, legacy_user_id)
) ENGINE=MEMORY;

INSERT IGNORE INTO tmp_base_contact_extract (old_base_id, role_code, legacy_user_id)
WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 128
),
src AS (
  SELECT
    b.id AS old_base_id,
    'responsible_flight_rescuer' AS role_code,
    COALESCE(b.rescue_emails, '') AS serialized
  FROM bases_old_import b
  UNION ALL
  SELECT
    b.id AS old_base_id,
    'chief_physician_in_charge' AS role_code,
    COALESCE(b.doctor_emails, '') AS serialized
  FROM bases_old_import b
)
SELECT
  src.old_base_id,
  src.role_code,
  CAST(
    SUBSTRING_INDEX(
      SUBSTRING_INDEX(
        REGEXP_SUBSTR(src.serialized, 's:[0-9]+:"[0-9]+"', 1, seq.n),
        '"',
        2
      ),
      '"',
      -1
    ) AS UNSIGNED
  ) AS legacy_user_id
FROM src
JOIN seq
  ON REGEXP_SUBSTR(src.serialized, 's:[0-9]+:"[0-9]+"', 1, seq.n) IS NOT NULL;

-- 5) Replace base_contact_roles for imported bases (targeted role codes only).
DELETE bcr
FROM base_contact_roles bcr
JOIN base_old_id_map bm ON bm.new_id = bcr.base_id
WHERE bcr.role_code IN ('responsible_flight_rescuer', 'chief_physician_in_charge');

-- 6) Insert deduplicated base_contact_roles by mapping legacy user IDs to new user IDs.
INSERT INTO base_contact_roles (
  id,
  base_id,
  contact_id,
  role_code,
  created_at,
  updated_at
)
SELECT
  UNHEX(REPLACE(
    LOWER(CONCAT(
      LPAD(HEX(FLOOR(CAST(UNIX_TIMESTAMP(UTC_TIMESTAMP(3)) * 1000 AS UNSIGNED) / 65536)), 8, '0'),
      '-',
      LPAD(HEX(MOD(CAST(UNIX_TIMESTAMP(UTC_TIMESTAMP(3)) * 1000 AS UNSIGNED), 65536)), 4, '0'),
      '-',
      '7', LPAD(HEX(FLOOR(RAND() * 4096)), 3, '0'),
      '-',
      LPAD(HEX(FLOOR(RAND() * 16384) + 32768), 4, '0'),
      '-',
      LPAD(HEX(FLOOR(RAND() * 281474976710656)), 12, '0')
    )),
    '-', ''
  )) AS id,
  bm.new_id AS base_id,
  um.new_id AS contact_id,
  e.role_code AS role_code,
  UTC_TIMESTAMP() AS created_at,
  UTC_TIMESTAMP() AS updated_at
FROM (
  SELECT DISTINCT old_base_id, role_code, legacy_user_id
  FROM tmp_base_contact_extract
) e
JOIN base_old_id_map bm ON bm.old_id = e.old_base_id
JOIN user_old_id_map um ON um.old_id = e.legacy_user_id
;

DROP TEMPORARY TABLE IF EXISTS tmp_base_contact_extract;

COMMIT;

-- +goose Down
-- no-op rollback