-- Add username to users table
ALTER TABLE users 
ADD COLUMN username TEXT UNIQUE,
ADD COLUMN password_hash TEXT;

-- Create credentials table for WebAuthn passkeys
CREATE TABLE credentials (
    id TEXT PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    credential_public_key BYTEA NOT NULL,
    credential_id BYTEA NOT NULL UNIQUE,
    sign_count BIGINT DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    last_used_at TIMESTAMP WITH TIME ZONE,
    transports TEXT[] DEFAULT ARRAY[]::TEXT[]
);

-- Create table for WebAuthn registration/authentication challenges
CREATE TABLE auth_challenges (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    challenge BYTEA NOT NULL,
    challenge_type TEXT NOT NULL, -- 'registration' or 'authentication'
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    used_at TIMESTAMP WITH TIME ZONE
);

CREATE INDEX idx_credentials_user_id ON credentials(user_id);
CREATE INDEX idx_auth_challenges_user_id ON auth_challenges(user_id);
CREATE INDEX idx_auth_challenges_expires_at ON auth_challenges(expires_at);