-- ============================================================================
-- OrbiQuant IA - Trading Platform
-- Schema: auth
-- File: tables/06-phone_verifications.sql
-- Description: Phone number verification tokens and tracking
-- ============================================================================

CREATE TABLE auth.phone_verifications (
  -- Primary Key
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Foreign Key to Users
  user_id UUID NOT NULL,

  -- Phone and Verification Code
  phone_number VARCHAR(20) NOT NULL,
  verification_code VARCHAR(10) NOT NULL,
  channel auth.phone_channel NOT NULL DEFAULT 'sms',

  -- Token Lifecycle
  expires_at TIMESTAMPTZ NOT NULL,
  verified_at TIMESTAMPTZ,
  is_verified BOOLEAN NOT NULL DEFAULT false,

  -- Attempt Tracking
  send_attempts INTEGER NOT NULL DEFAULT 0,
  verification_attempts INTEGER NOT NULL DEFAULT 0,
  max_attempts INTEGER NOT NULL DEFAULT 3,

  -- Metadata
  ip_address INET,
  user_agent TEXT,

  -- Audit Fields
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  -- Foreign Key Constraints
  CONSTRAINT fk_phone_verifications_user FOREIGN KEY (user_id)
    REFERENCES auth.users(id)
    ON DELETE CASCADE,

  -- Check Constraints
  CONSTRAINT valid_expiration CHECK (expires_at > created_at),
  CONSTRAINT verified_consistency CHECK (
    (is_verified = true AND verified_at IS NOT NULL) OR
    (is_verified = false AND verified_at IS NULL)
  ),
  CONSTRAINT valid_attempts CHECK (
    send_attempts >= 0 AND
    verification_attempts >= 0 AND
    verification_attempts <= max_attempts
  )
);

-- Indexes for Performance
CREATE INDEX idx_phone_verifications_user_id ON auth.phone_verifications(user_id);
CREATE INDEX idx_phone_verifications_phone ON auth.phone_verifications(phone_number);
CREATE INDEX idx_phone_verifications_expires ON auth.phone_verifications(expires_at);
CREATE INDEX idx_phone_verifications_pending ON auth.phone_verifications(user_id, is_verified, expires_at)
  WHERE is_verified = false;

-- Table Comments
COMMENT ON TABLE auth.phone_verifications IS 'Phone number verification codes and tracking for user phone confirmation';

-- Column Comments
COMMENT ON COLUMN auth.phone_verifications.id IS 'Unique identifier for the verification record';
COMMENT ON COLUMN auth.phone_verifications.user_id IS 'Reference to the user account';
COMMENT ON COLUMN auth.phone_verifications.phone_number IS 'Phone number to be verified';
COMMENT ON COLUMN auth.phone_verifications.verification_code IS 'Verification code sent via SMS';
COMMENT ON COLUMN auth.phone_verifications.expires_at IS 'Code expiration timestamp';
COMMENT ON COLUMN auth.phone_verifications.verified_at IS 'Timestamp when phone was verified';
COMMENT ON COLUMN auth.phone_verifications.is_verified IS 'Whether the phone has been verified';
COMMENT ON COLUMN auth.phone_verifications.send_attempts IS 'Number of times code was sent';
COMMENT ON COLUMN auth.phone_verifications.verification_attempts IS 'Number of verification attempts';
COMMENT ON COLUMN auth.phone_verifications.max_attempts IS 'Maximum allowed verification attempts';
COMMENT ON COLUMN auth.phone_verifications.ip_address IS 'IP address when verification was requested';
COMMENT ON COLUMN auth.phone_verifications.user_agent IS 'User agent when verification was requested';
COMMENT ON COLUMN auth.phone_verifications.created_at IS 'Timestamp when verification was created';