-- Migration to update intelligence_insights table with new insight types
-- This adds the new insight types used by the insight generator service

USE flowchief;

-- Update the insight_type ENUM to use only the new insight types
ALTER TABLE intelligence_insights 
MODIFY COLUMN insight_type ENUM(
  'productivity_decline',
  'overdue_tasks',
  'workload_imbalance', 
  'blocked_tasks',
  'inactive_projects',
  'high_priority_backlog',
  'team_availability',
  'deadline_risk'
) NOT NULL;

-- Add index on insight_type for better query performance
CREATE INDEX IF NOT EXISTS idx_insights_type_active 
ON intelligence_insights (insight_type, is_active);

-- Add index on expires_at for cleanup queries
CREATE INDEX IF NOT EXISTS idx_insights_expires 
ON intelligence_insights (expires_at, is_active);

SELECT 'Updated intelligence_insights table with new insight types' as migration_status;