-- Migration 028: Enhance Smart Reminder System
-- Adds support for reminder chains and new event types

-- 1. Extend work_events with new event types
ALTER TABLE work_events 
MODIFY COLUMN event_type ENUM(
  'task_start', 
  'task_stop', 
  'task_move', 
  'heartbeat', 
  'context_switch',
  'comment_added',
  'reminder_responded', 
  'slack_presence', 
  'work_session_extend'
) NOT NULL;

-- 2. Create new smart_task_reminders table for the Smart Reminder System
-- This is separate from the existing task_reminders table to avoid conflicts
CREATE TABLE IF NOT EXISTS smart_task_reminders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  organization_id INT NOT NULL,
  task_id INT NOT NULL,
  user_id INT NOT NULL,
  reminder_type ENUM('work_status', 'daily_checkin', 'task_update', 'health_check') NOT NULL DEFAULT 'work_status',
  reminder_time DATETIME NOT NULL,
  status ENUM('pending', 'sent', 'responded', 'cancelled') DEFAULT 'pending',
  
  -- Smart reminder chain fields
  reminder_chain_id VARCHAR(50) NULL,
  chain_sequence INT DEFAULT 1,
  parent_reminder_id INT NULL,
  user_response ENUM('continue', 'pause', 'complete', 'ignored') NULL,
  next_check_at TIMESTAMP NULL,
  escalation_level INT DEFAULT 0,
  trigger_reason VARCHAR(100) NULL,
  slack_message_ts VARCHAR(50) NULL,
  slack_channel_id VARCHAR(50) NULL,
  
  -- Timestamps
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  sent_at TIMESTAMP NULL,
  responded_at TIMESTAMP NULL,
  
  -- Indexes for performance
  INDEX idx_org_task_user (organization_id, task_id, user_id),
  INDEX idx_reminder_chain (reminder_chain_id),
  INDEX idx_next_check (next_check_at),
  INDEX idx_escalation (escalation_level, next_check_at),
  INDEX idx_status_time (status, reminder_time),
  
  -- Foreign keys
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (task_id) REFERENCES flochief_tasks(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (parent_reminder_id) REFERENCES smart_task_reminders(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Add index to users table for presence queries
CREATE INDEX idx_users_slack_status ON users(slack_status, last_slack_activity);

-- 4. Add index to flochief_tasks for stale task detection
CREATE INDEX idx_tasks_status_updated ON flochief_tasks(status, updated_at, assigned_to);