CREATE TABLE IF NOT EXISTS users (
  id CHAR(36) PRIMARY KEY,
  full_name VARCHAR(180) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  phone VARCHAR(60) NOT NULL DEFAULT '',
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  INDEX idx_users_active_name (active, full_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO users (id, full_name, email)
SELECT UUID(), cleaner_name, LOWER(cleaner_contact)
FROM shifts
WHERE cleaner_contact LIKE '%@%'
GROUP BY LOWER(cleaner_contact), cleaner_name;

ALTER TABLE shifts ADD COLUMN user_id CHAR(36) NULL AFTER site_id;

UPDATE shifts s
JOIN users u ON u.email = LOWER(s.cleaner_contact)
SET s.user_id = u.id
WHERE s.user_id IS NULL;

-- Any older shift that used a phone number instead of email must be completed
-- or removed before changing user_id to NOT NULL. New shifts use cleaner email.
ALTER TABLE shifts MODIFY user_id CHAR(36) NOT NULL;
ALTER TABLE shifts ADD CONSTRAINT fk_shifts_user FOREIGN KEY (user_id) REFERENCES users(id);