-- This script creates the user_session table to manage user login sessions.

-- Create an ENUM type for session status to ensure data consistency.
CREATE TYPE user_session_status_enum AS ENUM (
  'initiated',
  'validated',
  'logged_out',
  'expired',
  'failed'
);

-- Create the user_session table.
-- This table stores session information for each user login.
CREATE TABLE IF NOT EXISTS user_session (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT,
    phone_number VARCHAR(255),
    country_code VARCHAR(5),
    email VARCHAR(320),
    login_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    otp VARCHAR(6),
    attempt_count SMALLINT DEFAULT 0,
    jwt_token TEXT,
    session_status user_session_status_enum NOT NULL DEFAULT 'initiated',
    user_agent VARCHAR(1024),
    ip_address VARCHAR(45),
    expires_at TIMESTAMPTZ,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_user_session_user
        FOREIGN KEY (user_id)
        REFERENCES users(id) ON DELETE CASCADE
);

-- Add comments for columns for better understanding.
COMMENT ON TABLE user_session IS 'Stores user session information, including JWTs and OTPs.';
COMMENT ON COLUMN user_session.id IS 'Unique identifier for the user session.';
COMMENT ON COLUMN user_session.user_id IS 'Foreign key to the users table, identifying the user.';
COMMENT ON COLUMN user_session.phone_number IS 'The phone number used to initiate this session, if applicable.';
COMMENT ON COLUMN user_session.country_code IS 'The country code for the phone number used, if applicable.';
COMMENT ON COLUMN user_session.email IS 'The email address used to initiate this session, if applicable.';
COMMENT ON COLUMN user_session.login_at IS 'Timestamp of when the user logged in and the session was created.';
COMMENT ON COLUMN user_session.otp IS 'The one-time password sent for login, if applicable (6-digit alphanumeric).';
COMMENT ON COLUMN user_session.jwt_token IS 'The JWT bearer token for this session. Populated after successful validation.';
COMMENT ON COLUMN user_session.last_activity_at IS 'Timestamp of the last user activity for this session.';
COMMENT ON COLUMN user_session.session_status IS 'The current status of the session (e.g., initiated, validated, logged_out, expired, failed).';
COMMENT ON COLUMN user_session.expires_at IS 'Timestamp when the session is set to expire.';
COMMENT ON COLUMN user_session.attempt_count IS 'Number of OTP validation attempts for this session.';
COMMENT ON COLUMN user_session.user_agent IS 'Information about the user''s device, such as User-Agent, OS, etc.';
COMMENT ON COLUMN user_session.ip_address IS 'The IP address from which the session was initiated.';

-- Create indexes for better query performance.
CREATE INDEX IF NOT EXISTS idx_user_session_user_id ON user_session(user_id) WHERE user_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_user_session_phone_number ON user_session(country_code, phone_number) WHERE phone_number IS NOT NULL AND country_code IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_user_session_email ON user_session(email) WHERE email IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_user_session_jwt_token ON user_session(jwt_token) WHERE jwt_token IS NOT NULL;
