-- Migration 0002: User credentials (phone-based identity)
-- Allows idempotent registration: same phone → same user_id → same Universal ID

CREATE TABLE IF NOT EXISTS user_credentials (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    -- Credential type: 'phone', 'email', 'oauth'
    cred_type   VARCHAR(32)  NOT NULL DEFAULT 'phone',
    -- Normalized credential identifier (e.g., E.164 phone number)
    identifier  VARCHAR(256) NOT NULL,
    -- Optional display name associated at registration
    display_name VARCHAR(128),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT uq_credential UNIQUE (cred_type, identifier)
);

CREATE INDEX IF NOT EXISTS idx_user_credentials_user_id  ON user_credentials(user_id);
CREATE INDEX IF NOT EXISTS idx_user_credentials_identifier ON user_credentials(cred_type, identifier);