-- Migration script for chat tables - Add message status indicators
-- Run this after updating the create-chat-tables.sql file

-- Add status tracking columns to messages table
ALTER TABLE messages
ADD COLUMN status ENUM('sent', 'delivered', 'read') DEFAULT 'sent' AFTER message_type,
ADD COLUMN delivered_at TIMESTAMP NULL AFTER status,
ADD COLUMN read_by JSON NULL COMMENT 'JSON array of user IDs who have read the message' AFTER delivered_at;

-- Update existing messages to 'delivered' status (assuming they were delivered)
UPDATE messages SET status = 'delivered', delivered_at = created_at WHERE status = 'sent';

-- Add index for message status queries
CREATE INDEX idx_messages_status ON messages(status);
CREATE INDEX idx_messages_delivered_at ON messages(delivered_at);

-- Update the message column name to message_content (if not already done)
-- Check if message column exists and rename it
SET @column_exists = (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'messages'
    AND COLUMN_NAME = 'message'
);

SET @sql = IF(@column_exists > 0,
    'ALTER TABLE messages CHANGE message message_content TEXT NOT NULL',
    'SELECT "message_content column already exists or renamed" AS status'
);

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