CREATE TABLE IF NOT EXISTS user_languages (
    user_id BIGINT UNSIGNED NOT NULL,
    language_code VARCHAR(8) NOT NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (user_id, language_code),
    KEY idx_user_languages_language (language_code),
    CONSTRAINT fk_user_languages_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

/* Existing accounts keep their current interface language as their first
   communication language until they edit the profile. */
INSERT IGNORE INTO user_languages (user_id, language_code, created_at)
SELECT id, language, NOW()
FROM users
WHERE language IN ('en', 'it', 'ru');
