-- ============================================================================
-- Migration: Add index on locked_until for account lockout queries
-- Task: ST-4.1, GAP-009
-- Description: Creates a more optimized partial index for active account
--              lockouts, improving query performance on account unlock checks
-- ============================================================================

-- Drop the previous index if it exists (less specific)
DROP INDEX IF EXISTS auth.idx_users_locked CASCADE;

-- Create optimized partial index for active lockouts
CREATE INDEX idx_users_locked_until
ON auth.users(locked_until)
WHERE locked_until IS NOT NULL AND locked_until > NOW();

-- Add index comment
COMMENT ON INDEX auth.idx_users_locked_until IS 'Partial index for active account lockouts - optimized for unlock validation queries';

-- ============================================================================
-- Verification
-- ============================================================================
-- This index improves queries like:
-- SELECT id, email FROM auth.users WHERE locked_until > NOW();
--
-- Benefits:
-- 1. Smaller index size (only rows with active locks)
-- 2. Faster scans for checking unlocked accounts
-- 3. Automatic pruning as locks expire
-- ============================================================================