-- +goose Up
-- Phone verification tables (privacy-first: only hashed phones stored)

-- Stores phone verification status (hashed phone only, no plaintext)
CREATE TABLE phone_verifications (
    did TEXT PRIMARY KEY REFERENCES users(did) ON DELETE CASCADE,
    phone_hash TEXT UNIQUE NOT NULL, -- HMAC-SHA256(phone, pepper) for duplicate detection
    verified_at TIMESTAMP WITH TIME ZONE NOT NULL,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL, -- Annual re-verification (365 days)
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Index for checking if phone is already verified (duplicate prevention)
CREATE INDEX idx_phone_verifications_phone_hash ON phone_verifications(phone_hash);
CREATE INDEX idx_phone_verifications_expires_at ON phone_verifications(expires_at);

-- Stores pending verification requests (short-lived, auto-cleanup)
CREATE TABLE phone_verification_requests (
    request_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    did TEXT NOT NULL REFERENCES users(did) ON DELETE CASCADE,
    phone_hash TEXT NOT NULL, -- Same hash as phone_verifications for duplicate check
    otp_code_hash TEXT NOT NULL, -- Bcrypt hash of OTP code
    attempts INT DEFAULT 0, -- Track failed attempts (max 3)
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL -- OTP expires in 10 minutes
);

CREATE INDEX idx_phone_verification_requests_did ON phone_verification_requests(did);
CREATE INDEX idx_phone_verification_requests_expires_at ON phone_verification_requests(expires_at);

-- Rate limiting table (prevent SMS abuse)
CREATE TABLE phone_verification_rate_limits (
    identifier TEXT PRIMARY KEY, -- Could be DID or phone_hash
    request_count INT DEFAULT 1,
    window_start TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_request_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_phone_verification_rate_limits_window ON phone_verification_rate_limits(window_start);

-- Audit log for security monitoring
CREATE TABLE phone_verification_audit_log (
    id BIGSERIAL PRIMARY KEY,
    did TEXT REFERENCES users(did) ON DELETE SET NULL,
    event_type TEXT NOT NULL, -- 'request_sent', 'verification_success', 'verification_failed', 'rate_limit_hit'
    phone_hash TEXT, -- Optional, for tracking abuse patterns
    ip_address INET, -- Track source IP for fraud detection
    user_agent TEXT,
    metadata JSONB, -- Additional context (error details, etc.)
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_phone_verification_audit_log_did ON phone_verification_audit_log(did);
CREATE INDEX idx_phone_verification_audit_log_event_type ON phone_verification_audit_log(event_type);
CREATE INDEX idx_phone_verification_audit_log_created_at ON phone_verification_audit_log(created_at);

-- Cleanup function for expired requests (run via cron)
CREATE OR REPLACE FUNCTION cleanup_expired_verification_requests()
RETURNS void AS $$
BEGIN
    DELETE FROM phone_verification_requests WHERE expires_at < NOW();
    DELETE FROM phone_verification_rate_limits WHERE window_start < NOW() - INTERVAL '1 hour';
END;
$$ LANGUAGE plpgsql;

-- +goose Down
DROP FUNCTION IF EXISTS cleanup_expired_verification_requests();
DROP TABLE IF EXISTS phone_verification_audit_log;
DROP TABLE IF EXISTS phone_verification_rate_limits;
DROP TABLE IF EXISTS phone_verification_requests;
DROP TABLE IF EXISTS phone_verifications;