-- Messaging system tables for client-coach communication

-- Conversations table
CREATE TABLE IF NOT EXISTS conversations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    coach_id INT NOT NULL,
    last_message TEXT,
    last_message_time DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (coach_id) REFERENCES coaches(id) ON DELETE CASCADE,
    UNIQUE KEY unique_conversation (user_id, coach_id),
    INDEX idx_user_last_message (user_id, last_message_time DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Messages table
CREATE TABLE IF NOT EXISTS messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    conversation_id INT NOT NULL,
    sender_type ENUM('client', 'coach') NOT NULL,
    sender_id INT NOT NULL,
    message TEXT NOT NULL,
    is_read TINYINT(1) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
    INDEX idx_conversation_time (conversation_id, created_at ASC),
    INDEX idx_unread (conversation_id, is_read, sender_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert sample conversation (optional - for testing)
-- Replace user_id and coach_id with actual IDs from your database
-- INSERT INTO conversations (user_id, coach_id, last_message, last_message_time, created_at)
-- VALUES (38, 1, 'Hello, I have a question about my wellness plan.', NOW(), NOW());

-- Insert sample messages (optional - for testing)
-- INSERT INTO messages (conversation_id, sender_type, sender_id, message, is_read, created_at)
-- VALUES 
-- (1, 'client', 38, 'Hello, I have a question about my wellness plan.', 1, NOW() - INTERVAL 2 HOUR),
-- (1, 'coach', 1, 'Hi! I\'d be happy to help. What would you like to know?', 1, NOW() - INTERVAL 1 HOUR),
-- (1, 'client', 38, 'What supplements do you recommend for better sleep?', 1, NOW() - INTERVAL 30 MINUTE),
-- (1, 'coach', 1, 'Great question! I recommend starting with magnesium and chamomile tea. Let\'s discuss this more in your next appointment.', 0, NOW());
