-- Migration: 000003_password_auth.up.sql
-- Add password authentication as optional fallback

-- Add password hash column to admin_users (nullable for Passkey-only accounts)
ALTER TABLE admin_users ADD COLUMN password_hash TEXT;

-- Sessions table for JWT token management
CREATE TABLE IF NOT EXISTS sessions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    admin_user_id UUID NOT NULL REFERENCES admin_users(id) ON DELETE CASCADE,
    token_hash TEXT NOT NULL UNIQUE, -- SHA256 hash of JWT
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    last_activity TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    ip_address TEXT,
    user_agent TEXT
);

CREATE INDEX IF NOT EXISTS idx_sessions_user ON sessions(admin_user_id);
CREATE INDEX IF NOT EXISTS idx_sessions_token ON sessions(token_hash);
CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sessions(expires_at);

-- Add auth_method to track how user logged in
ALTER TABLE admin_users ADD COLUMN preferred_auth_method TEXT DEFAULT 'password';
-- Options: 'password', 'passkey', 'both'