-- Application-local IDs are stable user_serial values, not usernames.
CREATE TABLE IF NOT EXISTS user_application_accounts (
    user_serial BIGINT UNSIGNED NOT NULL,
    application_serial BIGINT UNSIGNED NOT NULL,
    local_user_serial BIGINT UNSIGNED NOT NULL,
    linked_by VARCHAR(128) NOT NULL,
    linked_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_serial, application_serial),
    UNIQUE KEY application_account (application_serial, local_user_serial),
    FOREIGN KEY (user_serial) REFERENCES users(user_serial) ON DELETE CASCADE,
    FOREIGN KEY (application_serial) REFERENCES applications(application_serial) ON DELETE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;