-- Migration 028: Fix Intelligence System Schema Issues (Compatible version)
-- Ensures intelligence_insights table has required is_active column
-- Adds proper indexes and comments for clarity
-- Compatible with older MySQL versions

-- Check if is_active column exists before adding it
SET @column_exists = 0;
SELECT COUNT(*) INTO @column_exists 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() 
  AND TABLE_NAME = 'intelligence_insights' 
  AND COLUMN_NAME = 'is_active';

SET @sql = IF(@column_exists = 0, 
    'ALTER TABLE intelligence_insights ADD COLUMN is_active BOOLEAN DEFAULT TRUE',
    'SELECT "Column is_active already exists" as message');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add indexes (using CREATE INDEX with IF NOT EXISTS if supported, otherwise ignore errors)
CREATE INDEX idx_insights_org_active ON intelligence_insights (organization_id, is_active);
CREATE INDEX idx_insights_priority_active ON intelligence_insights (priority, is_active, expires_at);

-- Add performance indexes for work_sessions
CREATE INDEX idx_work_sessions_org_active ON work_sessions (organization_id, end_time);
CREATE INDEX idx_work_sessions_user_time ON work_sessions (user_id, start_time);
CREATE INDEX idx_work_sessions_task_active ON work_sessions (task_id, end_time);

-- Update any potentially problematic records
UPDATE intelligence_insights 
SET is_active = TRUE 
WHERE is_active IS NULL;

SELECT 'Migration 028: Intelligence schema fixes applied successfully (compatible version)' as result;