422 lines
14 KiB
SQL
422 lines
14 KiB
SQL
-- +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;
|