-- Migration 028: Fix Intelligence System Schema Issues  
-- Ensures intelligence_insights table has required is_active column
-- Adds proper indexes and comments for clarity
-- Note: task_priorities and work_sessions do NOT need is_active columns

-- Fix intelligence_insights table - ensure is_active column exists
ALTER TABLE intelligence_insights 
ADD COLUMN IF NOT EXISTS is_active BOOLEAN DEFAULT TRUE;

-- Ensure intelligence_insights has proper indexes
CREATE INDEX IF NOT EXISTS idx_insights_org_active 
ON intelligence_insights (organization_id, is_active);

CREATE INDEX IF NOT EXISTS idx_insights_priority_active 
ON intelligence_insights (priority, is_active, expires_at);

-- Ensure work_sessions table structure is correct
-- (Note: work_sessions uses end_time IS NULL for active sessions, not is_active column)

-- Add any missing indexes for work_sessions performance
CREATE INDEX IF NOT EXISTS idx_work_sessions_org_active 
ON work_sessions (organization_id, end_time);

CREATE INDEX IF NOT EXISTS idx_work_sessions_user_time 
ON work_sessions (user_id, start_time);

CREATE INDEX IF NOT EXISTS idx_work_sessions_task_active 
ON work_sessions (task_id, end_time);

-- Note: task_priorities table uses expires_at > NOW() instead of is_active column
-- No is_active column needed for task_priorities

-- Update any potentially problematic records
-- Set default values for any NULL is_active columns
UPDATE intelligence_insights 
SET is_active = TRUE 
WHERE is_active IS NULL;

-- No updates needed for task_priorities (no is_active column added)

-- Add helpful comments for future reference
ALTER TABLE work_sessions 
COMMENT = 'Work sessions track active work periods. Use end_time IS NULL to check if session is active, NOT is_active column';

ALTER TABLE intelligence_insights 
COMMENT = 'Intelligence insights with is_active column for soft deletion';

ALTER TABLE task_priorities 
COMMENT = 'Task priorities use expires_at > NOW() to check if active, no is_active column needed';

SELECT 'Migration 028: Intelligence schema fixes applied successfully' as result;