-- Migration: Add session lifecycle management columns
-- Date: 2024-01-26
-- Description: Adds last_accessed_at and deleted_at columns to learning_sessions table for session lifecycle management and soft delete functionality

-- Add last_accessed_at column to track when sessions were last accessed
-- This enables automatic archival of inactive sessions
ALTER TABLE learning_sessions ADD COLUMN last_accessed_at DATETIME;

-- Add deleted_at column for soft delete functionality
-- NULL means not deleted, non-NULL means soft deleted
ALTER TABLE learning_sessions ADD COLUMN deleted_at DATETIME;

-- Create composite index for efficient session list queries
-- This index supports queries filtering by user, status, and deleted status
CREATE INDEX IF NOT EXISTS idx_learning_sessions_user_status_deleted 
ON learning_sessions(user_id, status, deleted_at);

-- Create index for archival queries based on last access time
-- This enables efficient identification of inactive sessions
CREATE INDEX IF NOT EXISTS idx_learning_sessions_last_accessed 
ON learning_sessions(last_accessed_at);

-- Create composite index for status and deleted_at
-- This supports queries that filter active vs deleted sessions
CREATE INDEX IF NOT EXISTS idx_learning_sessions_status_deleted 
ON learning_sessions(status, deleted_at);

-- Update existing sessions to set last_accessed_at to their created_at time
-- This ensures backward compatibility for existing sessions
UPDATE learning_sessions 
SET last_accessed_at = created_at 
WHERE last_accessed_at IS NULL;

-- Verify migration completed
SELECT 'Migration completed: Session lifecycle columns and indexes added to learning_sessions' as status;
