-- Enhanced Task Comments System Migration
-- Adds modern comment features for better UX

-- First, update the task_comments table structure
ALTER TABLE task_comments 
ADD COLUMN IF NOT EXISTS content TEXT,
ADD COLUMN IF NOT EXISTS mentions JSON DEFAULT NULL,
ADD COLUMN IF NOT EXISTS attachments JSON DEFAULT NULL,
ADD COLUMN IF NOT EXISTS edited_at TIMESTAMP NULL,
ADD COLUMN IF NOT EXISTS parent_comment_id INT DEFAULT NULL,
ADD COLUMN IF NOT EXISTS reactions JSON DEFAULT NULL,
ADD COLUMN IF NOT EXISTS is_deleted BOOLEAN DEFAULT FALSE,
ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

-- Add foreign key for reply threads
ALTER TABLE task_comments 
ADD CONSTRAINT fk_parent_comment 
FOREIGN KEY (parent_comment_id) REFERENCES task_comments(id) ON DELETE CASCADE;

-- Add index for better performance
CREATE INDEX IF NOT EXISTS idx_task_comments_parent ON task_comments(parent_comment_id);
CREATE INDEX IF NOT EXISTS idx_task_comments_deleted ON task_comments(is_deleted, created_at);

-- Update the comment column to content if it exists
UPDATE task_comments SET content = comment WHERE content IS NULL AND comment IS NOT NULL;

-- Make sure flochief_tasks has comments_count field
ALTER TABLE flochief_tasks 
ADD COLUMN IF NOT EXISTS comments_count INT DEFAULT 0;

-- Update comment counts for existing tasks
UPDATE flochief_tasks ft 
SET comments_count = (
  SELECT COUNT(*) 
  FROM task_comments tc 
  WHERE tc.task_id = ft.id AND tc.is_deleted = FALSE
) 
WHERE EXISTS (
  SELECT 1 FROM task_comments tc WHERE tc.task_id = ft.id
);