-- Rollback Migration: Remove session lifecycle management columns
-- Date: 2024-01-26
-- Description: Rollback script for 004_add_session_lifecycle_columns.sql
-- WARNING: This will remove the last_accessed_at and deleted_at columns and their indexes

-- Drop indexes first
DROP INDEX IF EXISTS idx_learning_sessions_user_status_deleted;
DROP INDEX IF EXISTS idx_learning_sessions_last_accessed;
DROP INDEX IF EXISTS idx_learning_sessions_status_deleted;

-- Note: SQLite does not support DROP COLUMN directly in older versions
-- To remove columns, we need to recreate the table without those columns

-- Create a backup table with the original structure
CREATE TABLE IF NOT EXISTS learning_sessions_backup (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    trader_id TEXT,
    
    title TEXT DEFAULT '',
    status TEXT NOT NULL CHECK(status IN ('ACTIVE', 'COMPLETED', 'ARCHIVED')),
    
    position_ids TEXT DEFAULT '[]',
    data_count INTEGER DEFAULT 0,
    
    conversation TEXT DEFAULT '[]',
    insights TEXT DEFAULT '[]',
    suggestions TEXT DEFAULT '[]',
    
    model_id TEXT DEFAULT '',
    last_model_id TEXT DEFAULT '',
    
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Copy data from current table to backup (excluding the new columns)
INSERT INTO learning_sessions_backup 
SELECT 
    id, user_id, trader_id, title, status, 
    position_ids, data_count, conversation, insights, suggestions,
    model_id, last_model_id,
    created_at, updated_at, completed_at
FROM learning_sessions;

-- Drop the current table
DROP TABLE learning_sessions;

-- Rename backup to original name
ALTER TABLE learning_sessions_backup RENAME TO learning_sessions;

-- Recreate original indexes
CREATE INDEX IF NOT EXISTS idx_learning_sessions_user ON learning_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_learning_sessions_trader ON learning_sessions(trader_id);
CREATE INDEX IF NOT EXISTS idx_learning_sessions_status ON learning_sessions(status);
CREATE INDEX IF NOT EXISTS idx_learning_sessions_created_at ON learning_sessions(created_at);
CREATE INDEX IF NOT EXISTS idx_learning_sessions_model ON learning_sessions(model_id);

-- Recreate trigger
CREATE TRIGGER IF NOT EXISTS update_learning_sessions_updated_at
    AFTER UPDATE ON learning_sessions
    BEGIN
        UPDATE learning_sessions SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
    END;

-- Verify rollback completed
SELECT 'Rollback completed: Session lifecycle columns removed from learning_sessions' as status;
