-- Chrome enrollment notification tracking table
-- Stores "Welcome from the Google Chrome Devices Team!" email notifications
-- for audit trail and manual linking of unmatched enrollments.

CREATE TABLE IF NOT EXISTS chrome_enrollment_notifications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  gmail_message_id VARCHAR(255),
  domain_name VARCHAR(255) NOT NULL,
  admin_username VARCHAR(255),
  subject VARCHAR(500),
  mts_number VARCHAR(50),
  match_status ENUM('matched', 'unmatched') NOT NULL DEFAULT 'unmatched',
  received_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_gmail_msg (gmail_message_id),
  KEY idx_domain (domain_name),
  KEY idx_mts (mts_number),
  KEY idx_status (match_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;