-- M3 Auth Completeness Migration
-- Add support for deleted_at, email_change tracking, and MFA challenges

-- Add deleted_at column for soft delete support
ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ;

-- Add email change tracking columns
ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS email_change TIMESTAMPTZ;
ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS email_change_token_new TEXT;

-- Create MFA challenges table for tracking MFA verification attempts
CREATE TABLE IF NOT EXISTS auth.mfa_challenges (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    factor_id UUID NOT NULL REFERENCES auth.mfa_factors(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    verified_at TIMESTAMPTZ,
    ip_address TEXT
);

CREATE INDEX IF NOT EXISTS idx_mfa_challenges_factor ON auth.mfa_challenges(factor_id);