-- +goose Up
-- Apply legacy user staging data into new auth/contact schema.
-- Expected flow before this migration:
-- 1) Run previous migration that creates `user_old_import` and role map.
-- 2) Import old dump rows into `user_old_import`.
-- This migration maps old integer IDs into BINARY(16) UUID-style IDs,
-- then upserts into `users`, `user_roles`, and role-specific profile tables.

START TRANSACTION;

-- 1) Reuse existing users by normalized email when possible.
INSERT INTO user_old_id_map (old_id, new_id)
SELECT
  s.id AS old_id,
  u.id AS new_id
FROM user_old_import s
JOIN users u
  ON LOWER(TRIM(u.email)) = LOWER(TRIM(s.email))
LEFT JOIN user_old_id_map m ON m.old_id = s.id
WHERE m.old_id IS NULL
  AND s.email IS NOT NULL
  AND TRIM(s.email) <> '';

-- 2) Generate IDs for remaining legacy users.
INSERT INTO user_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 user_old_import s
LEFT JOIN user_old_id_map m ON m.old_id = s.id
WHERE m.old_id IS NULL;

-- 3) Upsert core users.
INSERT INTO users (
  id,
  role_id,
  email,
  username,
  first_name,
  last_name,
  mobile_phone,
  timezone,
  is_active,
  password_hash,
  salt_value,
  email_verified_at,
  created_at,
  updated_at
)
SELECT
  m.new_id AS id,
  r.id AS role_id,
  CONCAT(
    COALESCE(
      NULLIF(LOWER(TRIM(SUBSTRING_INDEX(NULLIF(s.email, ''), '@', 1))), ''),
      CONCAT('legacy-user-', s.id)
    ),
    '+u', s.id,
    '@mybit-innovation'
  ) AS email,
  NULLIF(LEFT(TRIM(COALESCE(s.username, '')), 100), '') AS username,
  LEFT(SUBSTRING_INDEX(TRIM(COALESCE(NULLIF(s.fullname, ''), NULLIF(s.username, ''), CONCAT('Legacy User ', s.id))), ' ', 1), 100) AS first_name,
  LEFT(
    COALESCE(
      NULLIF(
        TRIM(
          SUBSTRING(
            TRIM(COALESCE(NULLIF(s.fullname, ''), NULLIF(s.username, ''), CONCAT('Legacy User ', s.id))),
            LENGTH(SUBSTRING_INDEX(TRIM(COALESCE(NULLIF(s.fullname, ''), NULLIF(s.username, ''), CONCAT('Legacy User ', s.id))), ' ', 1)) + 1
          )
        ),
        ''
      ),
      '-'
    ),
    100
  ) AS last_name,
  LEFT(TRIM(COALESCE(s.mobile, s.phone, '')), 32) AS mobile_phone,
  'UTC' AS timezone,
  CASE WHEN COALESCE(s.visible, 1) = 1 THEN 1 ELSE 0 END AS is_active,
  NULL AS password_hash,
  NULL AS salt_value,
  NULL AS email_verified_at,
  COALESCE(s.regDate, UTC_TIMESTAMP()) AS created_at,
  COALESCE(s.lastVisit, UTC_TIMESTAMP()) AS updated_at
FROM user_old_import s
JOIN user_old_id_map m ON m.old_id = s.id
LEFT JOIN legacy_user_role_map lrm ON lrm.legacy_role = s.role
JOIN roles r ON r.code = COALESCE(lrm.role_code, 'staff')
ON DUPLICATE KEY UPDATE
  role_id = VALUES(role_id),
  username = VALUES(username),
  first_name = VALUES(first_name),
  last_name = VALUES(last_name),
  mobile_phone = VALUES(mobile_phone),
  timezone = VALUES(timezone),
  is_active = VALUES(is_active),
  password_hash = NULL,
  salt_value = NULL,
  email_verified_at = NULL,
  updated_at = UTC_TIMESTAMP();

-- 4) Ensure user_roles relation exists for mapped role.
INSERT INTO user_roles (
  id,
  user_id,
  role_id,
  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,
  m.new_id AS user_id,
  r.id AS role_id,
  UTC_TIMESTAMP() AS created_at,
  UTC_TIMESTAMP() AS updated_at
FROM user_old_import s
JOIN user_old_id_map m ON m.old_id = s.id
LEFT JOIN legacy_user_role_map lrm ON lrm.legacy_role = s.role
JOIN roles r ON r.code = COALESCE(lrm.role_code, 'staff')
ON DUPLICATE KEY UPDATE
  updated_at = UTC_TIMESTAMP();

-- 5) Pilot profiles.
INSERT INTO pilot_profiles (
  user_id,
  pilot_category,
  short_name,
  license_no,
  technician_license_no,
  is_chief_pilot,
  total_flight_minutes,
  responsible_pilot_minutes,
  second_pilot_minutes,
  double_command_minutes,
  flight_instructor_minutes,
  night_flight_minutes,
  ifr_flight_minutes,
  location,
  postcode,
  street_line,
  photo_file_id,
  created_at,
  updated_at
)
SELECT
  m.new_id AS user_id,
  COALESCE(NULLIF(lrm.pilot_category, ''), 'regular') AS pilot_category,
  LEFT(TRIM(COALESCE(s.shortName, '')), 120) AS short_name,
  LEFT(TRIM(COALESCE(s.license, '')), 120) AS license_no,
  LEFT(TRIM(COALESCE(s.techLicense, '')), 120) AS technician_license_no,
  COALESCE(lrm.is_chief_pilot, 0) AS is_chief_pilot,
  CASE WHEN TRIM(COALESCE(s.airport_pilot, '')) REGEXP '^[0-9]+:[0-9]{1,2}$'
    THEN CAST(SUBSTRING_INDEX(TRIM(s.airport_pilot), ':', 1) AS UNSIGNED) * 60 + CAST(SUBSTRING_INDEX(TRIM(s.airport_pilot), ':', -1) AS UNSIGNED)
    ELSE 0 END AS total_flight_minutes,
  CASE WHEN TRIM(COALESCE(s.responsible_pilot, '')) REGEXP '^[0-9]+:[0-9]{1,2}$'
    THEN CAST(SUBSTRING_INDEX(TRIM(s.responsible_pilot), ':', 1) AS UNSIGNED) * 60 + CAST(SUBSTRING_INDEX(TRIM(s.responsible_pilot), ':', -1) AS UNSIGNED)
    ELSE 0 END AS responsible_pilot_minutes,
  CASE WHEN TRIM(COALESCE(s.second_pilot, '')) REGEXP '^[0-9]+:[0-9]{1,2}$'
    THEN CAST(SUBSTRING_INDEX(TRIM(s.second_pilot), ':', 1) AS UNSIGNED) * 60 + CAST(SUBSTRING_INDEX(TRIM(s.second_pilot), ':', -1) AS UNSIGNED)
    ELSE 0 END AS second_pilot_minutes,
  CASE WHEN TRIM(COALESCE(s.double_tax_pilot, '')) REGEXP '^[0-9]+:[0-9]{1,2}$'
    THEN CAST(SUBSTRING_INDEX(TRIM(s.double_tax_pilot), ':', 1) AS UNSIGNED) * 60 + CAST(SUBSTRING_INDEX(TRIM(s.double_tax_pilot), ':', -1) AS UNSIGNED)
    ELSE 0 END AS double_command_minutes,
  CASE WHEN TRIM(COALESCE(s.flight_instructor_pilot, '')) REGEXP '^[0-9]+:[0-9]{1,2}$'
    THEN CAST(SUBSTRING_INDEX(TRIM(s.flight_instructor_pilot), ':', 1) AS UNSIGNED) * 60 + CAST(SUBSTRING_INDEX(TRIM(s.flight_instructor_pilot), ':', -1) AS UNSIGNED)
    ELSE 0 END AS flight_instructor_minutes,
  CASE WHEN TRIM(COALESCE(s.night_pilot, '')) REGEXP '^[0-9]+:[0-9]{1,2}$'
    THEN CAST(SUBSTRING_INDEX(TRIM(s.night_pilot), ':', 1) AS UNSIGNED) * 60 + CAST(SUBSTRING_INDEX(TRIM(s.night_pilot), ':', -1) AS UNSIGNED)
    ELSE 0 END AS night_flight_minutes,
  CASE WHEN TRIM(COALESCE(s.fir_pilot, '')) REGEXP '^[0-9]+:[0-9]{1,2}$'
    THEN CAST(SUBSTRING_INDEX(TRIM(s.fir_pilot), ':', 1) AS UNSIGNED) * 60 + CAST(SUBSTRING_INDEX(TRIM(s.fir_pilot), ':', -1) AS UNSIGNED)
    ELSE 0 END AS ifr_flight_minutes,
  LEFT(TRIM(COALESCE(s.address, '')), 255) AS location,
  LEFT(TRIM(COALESCE(s.zipCode, '')), 32) AS postcode,
  LEFT(TRIM(COALESCE(s.street, '')), 255) AS street_line,
  LEFT(TRIM(COALESCE(s.photo, '')), 255) AS photo_file_id,
  COALESCE(s.regDate, UTC_TIMESTAMP()) AS created_at,
  COALESCE(s.lastVisit, UTC_TIMESTAMP()) AS updated_at
FROM user_old_import s
JOIN user_old_id_map m ON m.old_id = s.id
JOIN users u ON u.id = m.new_id
JOIN legacy_user_role_map lrm ON lrm.legacy_role = s.role
WHERE lrm.role_code = 'pilot'
ON DUPLICATE KEY UPDATE
  pilot_category = VALUES(pilot_category),
  short_name = VALUES(short_name),
  license_no = VALUES(license_no),
  technician_license_no = VALUES(technician_license_no),
  is_chief_pilot = VALUES(is_chief_pilot),
  total_flight_minutes = VALUES(total_flight_minutes),
  responsible_pilot_minutes = VALUES(responsible_pilot_minutes),
  second_pilot_minutes = VALUES(second_pilot_minutes),
  double_command_minutes = VALUES(double_command_minutes),
  flight_instructor_minutes = VALUES(flight_instructor_minutes),
  night_flight_minutes = VALUES(night_flight_minutes),
  ifr_flight_minutes = VALUES(ifr_flight_minutes),
  location = VALUES(location),
  postcode = VALUES(postcode),
  street_line = VALUES(street_line),
  photo_file_id = VALUES(photo_file_id),
  updated_at = UTC_TIMESTAMP();

-- 6) Doctor profiles.
INSERT INTO doctor_profiles (
  user_id,
  short_name,
  medical_license_no,
  is_chief_physician,
  location,
  postcode,
  street_line,
  photo_file_id,
  created_at,
  updated_at
)
SELECT
  m.new_id AS user_id,
  LEFT(TRIM(COALESCE(s.shortName, '')), 120) AS short_name,
  LEFT(TRIM(COALESCE(s.license, '')), 120) AS medical_license_no,
  COALESCE(lrm.is_chief_physician, 0) AS is_chief_physician,
  LEFT(TRIM(COALESCE(s.address, '')), 255) AS location,
  LEFT(TRIM(COALESCE(s.zipCode, '')), 32) AS postcode,
  LEFT(TRIM(COALESCE(s.street, '')), 255) AS street_line,
  LEFT(TRIM(COALESCE(s.photo, '')), 255) AS photo_file_id,
  COALESCE(s.regDate, UTC_TIMESTAMP()) AS created_at,
  COALESCE(s.lastVisit, UTC_TIMESTAMP()) AS updated_at
FROM user_old_import s
JOIN user_old_id_map m ON m.old_id = s.id
JOIN users u ON u.id = m.new_id
JOIN legacy_user_role_map lrm ON lrm.legacy_role = s.role
WHERE lrm.role_code = 'doctor'
ON DUPLICATE KEY UPDATE
  short_name = VALUES(short_name),
  medical_license_no = VALUES(medical_license_no),
  is_chief_physician = VALUES(is_chief_physician),
  location = VALUES(location),
  postcode = VALUES(postcode),
  street_line = VALUES(street_line),
  photo_file_id = VALUES(photo_file_id),
  updated_at = UTC_TIMESTAMP();

-- 7) Air rescuer profiles.
INSERT INTO air_rescuer_profiles (
  user_id,
  short_name,
  is_chief_physician_in_charge,
  responsible_flight_rescuer,
  location,
  postcode,
  street_line,
  photo_file_id,
  created_at,
  updated_at
)
SELECT
  m.new_id AS user_id,
  LEFT(TRIM(COALESCE(s.shortName, '')), 120) AS short_name,
  COALESCE(lrm.is_chief_physician_in_charge, 0) AS is_chief_physician_in_charge,
  0 AS responsible_flight_rescuer,
  LEFT(TRIM(COALESCE(s.address, '')), 255) AS location,
  LEFT(TRIM(COALESCE(s.zipCode, '')), 32) AS postcode,
  LEFT(TRIM(COALESCE(s.street, '')), 255) AS street_line,
  LEFT(TRIM(COALESCE(s.photo, '')), 255) AS photo_file_id,
  COALESCE(s.regDate, UTC_TIMESTAMP()) AS created_at,
  COALESCE(s.lastVisit, UTC_TIMESTAMP()) AS updated_at
FROM user_old_import s
JOIN user_old_id_map m ON m.old_id = s.id
JOIN users u ON u.id = m.new_id
JOIN legacy_user_role_map lrm ON lrm.legacy_role = s.role
WHERE lrm.role_code = 'air_rescuer'
ON DUPLICATE KEY UPDATE
  short_name = VALUES(short_name),
  is_chief_physician_in_charge = VALUES(is_chief_physician_in_charge),
  responsible_flight_rescuer = VALUES(responsible_flight_rescuer),
  location = VALUES(location),
  postcode = VALUES(postcode),
  street_line = VALUES(street_line),
  photo_file_id = VALUES(photo_file_id),
  updated_at = UTC_TIMESTAMP();

-- 8) Technician profiles.
INSERT INTO technician_profiles (
  user_id,
  short_name,
  license_no,
  is_chief_technician,
  location,
  postcode,
  street_line,
  photo_file_id,
  created_at,
  updated_at
)
SELECT
  m.new_id AS user_id,
  LEFT(TRIM(COALESCE(s.shortName, '')), 120) AS short_name,
  LEFT(TRIM(COALESCE(s.license, '')), 120) AS license_no,
  COALESCE(lrm.is_chief_technician, 0) AS is_chief_technician,
  LEFT(TRIM(COALESCE(s.address, '')), 255) AS location,
  LEFT(TRIM(COALESCE(s.zipCode, '')), 32) AS postcode,
  LEFT(TRIM(COALESCE(s.street, '')), 255) AS street_line,
  LEFT(TRIM(COALESCE(s.photo, '')), 255) AS photo_file_id,
  COALESCE(s.regDate, UTC_TIMESTAMP()) AS created_at,
  COALESCE(s.lastVisit, UTC_TIMESTAMP()) AS updated_at
FROM user_old_import s
JOIN user_old_id_map m ON m.old_id = s.id
JOIN users u ON u.id = m.new_id
JOIN legacy_user_role_map lrm ON lrm.legacy_role = s.role
WHERE lrm.role_code = 'technician'
ON DUPLICATE KEY UPDATE
  short_name = VALUES(short_name),
  license_no = VALUES(license_no),
  is_chief_technician = VALUES(is_chief_technician),
  location = VALUES(location),
  postcode = VALUES(postcode),
  street_line = VALUES(street_line),
  photo_file_id = VALUES(photo_file_id),
  updated_at = UTC_TIMESTAMP();

-- 9) Flight assistant profiles.
INSERT INTO flight_assistant_profiles (
  user_id,
  short_name,
  location,
  postcode,
  street_line,
  photo_file_id,
  created_at,
  updated_at
)
SELECT
  m.new_id AS user_id,
  LEFT(TRIM(COALESCE(s.shortName, '')), 120) AS short_name,
  LEFT(TRIM(COALESCE(s.address, '')), 255) AS location,
  LEFT(TRIM(COALESCE(s.zipCode, '')), 32) AS postcode,
  LEFT(TRIM(COALESCE(s.street, '')), 255) AS street_line,
  LEFT(TRIM(COALESCE(s.photo, '')), 255) AS photo_file_id,
  COALESCE(s.regDate, UTC_TIMESTAMP()) AS created_at,
  COALESCE(s.lastVisit, UTC_TIMESTAMP()) AS updated_at
FROM user_old_import s
JOIN user_old_id_map m ON m.old_id = s.id
JOIN users u ON u.id = m.new_id
JOIN legacy_user_role_map lrm ON lrm.legacy_role = s.role
WHERE lrm.role_code = 'flight_assistant'
ON DUPLICATE KEY UPDATE
  short_name = VALUES(short_name),
  location = VALUES(location),
  postcode = VALUES(postcode),
  street_line = VALUES(street_line),
  photo_file_id = VALUES(photo_file_id),
  updated_at = UTC_TIMESTAMP();

-- 10) Staff profiles.
INSERT INTO staff_profiles (
  user_id,
  short_name,
  role_label,
  location,
  postcode,
  street_line,
  photo_file_id,
  created_at,
  updated_at
)
SELECT
  m.new_id AS user_id,
  LEFT(TRIM(COALESCE(s.shortName, '')), 120) AS short_name,
  LEFT(TRIM(COALESCE(lrm.staff_role_label, 'staff')), 120) AS role_label,
  LEFT(TRIM(COALESCE(s.address, '')), 255) AS location,
  LEFT(TRIM(COALESCE(s.zipCode, '')), 32) AS postcode,
  LEFT(TRIM(COALESCE(s.street, '')), 255) AS street_line,
  LEFT(TRIM(COALESCE(s.photo, '')), 255) AS photo_file_id,
  COALESCE(s.regDate, UTC_TIMESTAMP()) AS created_at,
  COALESCE(s.lastVisit, UTC_TIMESTAMP()) AS updated_at
FROM user_old_import s
JOIN user_old_id_map m ON m.old_id = s.id
JOIN users u ON u.id = m.new_id
JOIN legacy_user_role_map lrm ON lrm.legacy_role = s.role
WHERE lrm.role_code = 'staff'
ON DUPLICATE KEY UPDATE
  short_name = VALUES(short_name),
  role_label = VALUES(role_label),
  location = VALUES(location),
  postcode = VALUES(postcode),
  street_line = VALUES(street_line),
  photo_file_id = VALUES(photo_file_id),
  updated_at = UTC_TIMESTAMP();

COMMIT;

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