-- Migration V011: Add WebAuthn credentials table for biometric authentication
-- Description: Creates table to store WebAuthn/FIDO2 credentials for Face ID, Touch ID, Windows Hello, etc.
-- Date: 2025-10-01

-- Create webauthn_credentials table
CREATE TABLE IF NOT EXISTS webauthn_credentials (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id VARCHAR(20) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    credential_id TEXT NOT NULL UNIQUE,
    public_key TEXT NOT NULL,
    counter BIGINT NOT NULL DEFAULT 0,
    device_type VARCHAR(50),
    transports TEXT[],
    backed_up BOOLEAN DEFAULT false,
    authenticator_attachment VARCHAR(20),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_used TIMESTAMP WITH TIME ZONE,
    friendly_name VARCHAR(100),

    CONSTRAINT fk_webauthn_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Create indexes for performance
CREATE INDEX idx_webauthn_user_id ON webauthn_credentials(user_id);
CREATE INDEX idx_webauthn_credential_id ON webauthn_credentials(credential_id);
CREATE INDEX idx_webauthn_last_used ON webauthn_credentials(last_used);

-- Add comments for documentation
COMMENT ON TABLE webauthn_credentials IS 'Stores WebAuthn/FIDO2 credentials for biometric authentication (Face ID, Touch ID, Windows Hello, Android biometrics)';
COMMENT ON COLUMN webauthn_credentials.credential_id IS 'Base64url-encoded credential ID from authenticator';
COMMENT ON COLUMN webauthn_credentials.public_key IS 'Base64url-encoded public key for signature verification';
COMMENT ON COLUMN webauthn_credentials.counter IS 'Signature counter to prevent credential replay attacks';
COMMENT ON COLUMN webauthn_credentials.device_type IS 'Device type from user agent (e.g., iPhone, Android, Windows)';
COMMENT ON COLUMN webauthn_credentials.transports IS 'Supported transports (internal, nfc, ble, usb)';
COMMENT ON COLUMN webauthn_credentials.backed_up IS 'Whether credential is backed up (synced across devices)';
COMMENT ON COLUMN webauthn_credentials.authenticator_attachment IS 'platform (built-in) or cross-platform (external key)';
COMMENT ON COLUMN webauthn_credentials.friendly_name IS 'User-assigned name for the credential (e.g., "iPhone 15 Pro")';
