-- RAIET platform account layer. Platform links are optional and belong to the
-- RAIET account; they never replace RAIET authentication.
CREATE TABLE IF NOT EXISTS user_platform_accounts (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    provider VARCHAR(24) NOT NULL,
    provider_user_id VARCHAR(128) NOT NULL,
    display_name VARCHAR(64) NULL,
    linked_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_platform_user_provider (user_id, provider),
    UNIQUE KEY uq_platform_identity (provider, provider_user_id),
    KEY idx_platform_provider (provider),
    CONSTRAINT fk_platform_account_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
