-- +goose Up
-- Apply legacy bundesland staging data into `federal_states`.
-- Expected flow before this migration:
-- 1) Run previous migration that creates `bundesland_old_import`.
-- 2) Import old dump rows into `bundesland_old_import`.
-- This migration maps old integer IDs into BINARY(16) UUID-style IDs,
-- then upserts into `federal_states` with `is_active` forced to 1.

START TRANSACTION;

INSERT INTO bundesland_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 bundesland_old_import s
LEFT JOIN bundesland_old_id_map m ON m.old_id = s.id
WHERE m.old_id IS NULL;

INSERT INTO lands (
  id,
  name,
  land_iso_code,
  is_active,
  created_at,
  updated_at
)
SELECT
  UUID_TO_BIN(UUID()) AS id,
  src.land_name AS name,
  '' AS land_iso_code,
  1 AS is_active,
  UTC_TIMESTAMP() AS created_at,
  UTC_TIMESTAMP() AS updated_at
FROM (
  SELECT DISTINCT
    LEFT(
      CASE
        WHEN NULLIF(TRIM(s.state), '') IS NOT NULL THEN TRIM(s.state)
        WHEN TRIM(s.name) IN (
          'Vorarlberg',
          'Tirol',
          'Burgenland',
          'Wien',
          'Salzburg',
          'Steiermark',
          'Kärnten',
          'Oberösterreich',
          'Niederösterreich'
        ) THEN 'Österreich'
        ELSE ''
      END,
      128
    ) AS land_name
  FROM bundesland_old_import s
  WHERE NULLIF(
    CASE
      WHEN NULLIF(TRIM(s.state), '') IS NOT NULL THEN TRIM(s.state)
      WHEN TRIM(s.name) IN (
        'Vorarlberg',
        'Tirol',
        'Burgenland',
        'Wien',
        'Salzburg',
        'Steiermark',
        'Kärnten',
        'Oberösterreich',
        'Niederösterreich'
      ) THEN 'Österreich'
      ELSE ''
    END,
    ''
  ) IS NOT NULL
) src
LEFT JOIN lands l
  ON LOWER(TRIM(l.name)) = LOWER(src.land_name)
  AND l.deleted_at IS NULL
WHERE l.id IS NULL;

INSERT INTO federal_states (
  id,
  land_id,
  name,
  is_active,
  created_at,
  updated_at
)
SELECT
  m.new_id,
  l.id AS land_id,
  LEFT(COALESCE(NULLIF(TRIM(s.name), ''), CONCAT('Federal State ', s.id)), 128) AS name,
  1 AS is_active,
  UTC_TIMESTAMP() AS created_at,
  UTC_TIMESTAMP() AS updated_at
FROM bundesland_old_import s
JOIN bundesland_old_id_map m ON m.old_id = s.id
LEFT JOIN lands l
  ON LOWER(TRIM(l.name)) = LOWER(LEFT(
    CASE
      WHEN NULLIF(TRIM(s.state), '') IS NOT NULL THEN TRIM(s.state)
      WHEN TRIM(s.name) IN (
        'Vorarlberg',
        'Tirol',
        'Burgenland',
        'Wien',
        'Salzburg',
        'Steiermark',
        'Kärnten',
        'Oberösterreich',
        'Niederösterreich'
      ) THEN 'Österreich'
      ELSE ''
    END,
    128
  ))
  AND l.deleted_at IS NULL
ON DUPLICATE KEY UPDATE
  land_id = VALUES(land_id),
  name = VALUES(name),
  is_active = 1,
  updated_at = UTC_TIMESTAMP();

COMMIT;

-- +goose Down
-- Data migration is intentionally non-reversible.
-- Keep imported data and ID mapping as audit trail.
SELECT 1;