-- Migration: Add user_qr_auth table for QR code authentication
-- This table stores QR code authentication credentials for users
-- Users can generate QR codes that contain encrypted authentication data

-- Create user_qr_auth table with UUIDv7 primary key
CREATE TABLE IF NOT EXISTS user_qr_auth (
    auth_id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL,
    secret VARCHAR(255) NOT NULL,
    hashed VARCHAR(255) NOT NULL,
    capabilities JSONB DEFAULT '[]'::jsonb,
    created_at TIMESTAMP DEFAULT NOW(),
    expires_at TIMESTAMP NULL,
    is_active BOOLEAN DEFAULT true,
    last_used_at TIMESTAMP NULL,
    use_count INTEGER DEFAULT 0,
    isDeleted BOOLEAN DEFAULT false,
    CONSTRAINT fk_user_qr_auth_user FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

-- Create index on user_id for faster lookups
CREATE INDEX IF NOT EXISTS idx_user_qr_auth_user_id ON user_qr_auth(user_id);

-- Create index on secret for verification
CREATE INDEX IF NOT EXISTS idx_user_qr_auth_secret ON user_qr_auth(secret);

-- Create index on is_active for active auth lookups
CREATE INDEX IF NOT EXISTS idx_user_qr_auth_active ON user_qr_auth(is_active) WHERE is_active = true;

COMMENT ON TABLE user_qr_auth IS 'Stores QR code authentication credentials for users';
COMMENT ON COLUMN user_qr_auth.auth_id IS 'UUIDv7 primary key for this QR auth record';
COMMENT ON COLUMN user_qr_auth.user_id IS 'Reference to the user who owns this QR auth credential';
COMMENT ON COLUMN user_qr_auth.secret IS 'Secret key used for QR code generation and verification';
COMMENT ON COLUMN user_qr_auth.hashed IS 'Hashed version of the secret for additional security';
COMMENT ON COLUMN user_qr_auth.capabilities IS 'JSON array of capabilities/permissions for this QR auth';
COMMENT ON COLUMN user_qr_auth.expires_at IS 'Optional expiration timestamp for this QR auth credential (nullable for no expiry)';
COMMENT ON COLUMN user_qr_auth.is_active IS 'Whether this QR auth credential is currently active';
COMMENT ON COLUMN user_qr_auth.last_used_at IS 'Last time this QR auth was used for authentication';
COMMENT ON COLUMN user_qr_auth.use_count IS 'Number of times this QR auth has been used';
