-- +goose Up
-- Apply legacy sling staging data into new facilities schema.

START TRANSACTION;

-- 1) Reuse existing facilities rows by normalized (category, name, type) when possible.
INSERT INTO slings_old_id_map (old_id, new_id)
SELECT
  s.id AS old_id,
  f.id AS new_id
FROM slings_old_import s
JOIN facilities f
  ON LOWER(TRIM(f.category)) = LOWER(TRIM(
    CASE
      WHEN s.cat = 1 THEN 'hems-hec-sling'
      WHEN s.cat = 2 THEN 'logging-sling'
      WHEN s.cat = 3 THEN 'heslo-sling'
      WHEN s.cat IN (4, 6) THEN 'fuel-truck'
      WHEN s.cat = 5 THEN 'hec-sling'
      ELSE 'hec-sling'
    END
  ))
 AND LOWER(TRIM(f.name)) = LOWER(TRIM(COALESCE(s.name, '')))
 AND LOWER(TRIM(f.type)) = LOWER(TRIM(COALESCE(s.type, '')))
LEFT JOIN slings_old_id_map m ON m.old_id = s.id
WHERE m.old_id IS NULL;

-- 2) Generate IDs for remaining legacy sling rows.
INSERT INTO slings_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 slings_old_import s
LEFT JOIN slings_old_id_map m ON m.old_id = s.id
WHERE m.old_id IS NULL;

-- 3) Upsert into facilities.
INSERT INTO facilities (
  id,
  category,
  name,
  type,
  length,
  weight,
  electric,
  note,
  is_active,
  created_at,
  updated_at
)
SELECT
  m.new_id AS id,
  CASE
    WHEN s.cat = 1 THEN 'hems-hec-sling'
    WHEN s.cat = 2 THEN 'logging-sling'
    WHEN s.cat = 3 THEN 'heslo-sling'
    WHEN s.cat IN (4, 6) THEN 'fuel-truck'
    WHEN s.cat = 5 THEN 'hec-sling'
    ELSE 'hec-sling'
  END AS category,
  LEFT(TRIM(COALESCE(s.name, '')), 150) AS name,
  LEFT(TRIM(COALESCE(s.type, '')), 100) AS type,
  LEFT(TRIM(COALESCE(s.length, '')), 64) AS length,
  LEFT(TRIM(COALESCE(s.weight, '')), 64) AS weight,
  LEFT(TRIM(COALESCE(s.electric, '')), 255) AS electric,
  NULL AS note,
  1 AS is_active,
  UTC_TIMESTAMP() AS created_at,
  UTC_TIMESTAMP() AS updated_at
FROM slings_old_import s
JOIN slings_old_id_map m ON m.old_id = s.id
ON DUPLICATE KEY UPDATE
  category = VALUES(category),
  name = VALUES(name),
  type = VALUES(type),
  length = VALUES(length),
  weight = VALUES(weight),
  electric = VALUES(electric),
  note = VALUES(note),
  is_active = VALUES(is_active),
  updated_at = UTC_TIMESTAMP();

COMMIT;

-- +goose Down
-- no-op rollback