-- Shared DB SAFE backfill pass
-- Purpose: populate company_id and identity fields without breaking old code.

-- 1) users identity cleanup
UPDATE users
SET company_id = 1
WHERE company_id IS NULL OR company_id = 0;

UPDATE users
SET staff_code = COALESCE(NULLIF(TRIM(staff_code), ''), NULLIF(TRIM(employee_code), ''), CONCAT('USR', id))
WHERE staff_code IS NULL OR TRIM(staff_code) = '';

UPDATE users
SET full_name = TRIM(CONCAT(COALESCE(first_name, ''), ' ', COALESCE(last_name, '')))
WHERE (full_name IS NULL OR TRIM(full_name) = '')
  AND ((first_name IS NOT NULL AND TRIM(first_name) <> '') OR (last_name IS NOT NULL AND TRIM(last_name) <> ''));

UPDATE users
SET full_name = COALESCE(NULLIF(TRIM(full_name), ''), NULLIF(TRIM(staff_code), ''), CONCAT('User ', id))
WHERE full_name IS NULL OR TRIM(full_name) = '';

-- 2) work_sessions.company_id from users
UPDATE work_sessions ws
JOIN users u ON u.id = ws.user_id
SET ws.company_id = u.company_id
WHERE ws.company_id IS NULL OR ws.company_id = 0;

-- 3) work_session_events.company_id and user_id from session/users
UPDATE work_session_events wse
JOIN work_sessions ws ON ws.id = wse.work_session_id
SET wse.company_id = ws.company_id
WHERE wse.company_id IS NULL OR wse.company_id = 0;

UPDATE work_session_events wse
JOIN work_sessions ws ON ws.id = wse.work_session_id
SET wse.user_id = ws.user_id
WHERE wse.user_id IS NULL OR wse.user_id = 0;

-- 4) api_sessions.company_id from users
UPDATE api_sessions s
JOIN users u ON u.id = s.user_id
SET s.company_id = u.company_id
WHERE s.company_id IS NULL OR s.company_id = 0;

-- 5) worksites.company_id default to tenant 1 where not set yet
UPDATE worksites
SET company_id = 1
WHERE company_id IS NULL OR company_id = 0;

-- 6) user_worksites.company_id from users
UPDATE user_worksites uw
JOIN users u ON u.id = uw.user_id
SET uw.company_id = u.company_id
WHERE uw.company_id IS NULL OR uw.company_id = 0;

-- 7) location_logs.company_id from users
UPDATE location_logs ll
JOIN users u ON u.id = ll.user_id
SET ll.company_id = u.company_id
WHERE ll.company_id IS NULL OR ll.company_id = 0;

-- 8) location_stops.company_id from users
UPDATE location_stops ls
JOIN users u ON u.id = ls.user_id
SET ls.company_id = u.company_id
WHERE ls.company_id IS NULL OR ls.company_id = 0;

-- 9) admin_audit_log.company_id from acting admin user
UPDATE admin_audit_log aal
JOIN users u ON u.id = aal.admin_user_id
SET aal.company_id = u.company_id
WHERE aal.company_id IS NULL OR aal.company_id = 0;