-- Enhanced AI Chat System Schema
-- This script contains enhancements for the existing AI chat system
-- The main tables already exist, this adds indexes and optimizations

USE ashlya_academy;

-- Ensure all AI Chat tables exist with proper structure
-- (These should already exist, but adding for completeness)

-- Main AI Chat Sessions Table
CREATE TABLE IF NOT EXISTS `ai_chat_sessions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `session_title` varchar(255) DEFAULT 'New Chat',
  `session_type` enum('text','multivoice','pdf') DEFAULT 'text',
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_active` (`is_active`),
  FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
COMMENT='AI Chat sessions with support for multiple modes';

-- AI Chat Messages Table
CREATE TABLE IF NOT EXISTS `ai_chat_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `session_id` int(11) NOT NULL,
  `message_type` enum('user','assistant','system') NOT NULL,
  `content` text NOT NULL,
  `metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`metadata`)),
  `parent_message_id` int(11) DEFAULT NULL,
  `audio_url` varchar(500) DEFAULT NULL,
  `image_url` varchar(500) DEFAULT NULL,
  `tokens_used` int(11) DEFAULT 0,
  `processing_time` decimal(5,2) DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_session_id` (`session_id`),
  KEY `idx_message_type` (`message_type`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_parent_message` (`parent_message_id`),
  FOREIGN KEY (`session_id`) REFERENCES `ai_chat_sessions` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`parent_message_id`) REFERENCES `ai_chat_messages` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
COMMENT='AI Chat messages with support for different types and metadata';

-- AI Chat Favorites Table
CREATE TABLE IF NOT EXISTS `ai_chat_favorites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `session_id` int(11) NOT NULL,
  `message_id` int(11) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_session_message` (`user_id`,`session_id`,`message_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_session_id` (`session_id`),
  KEY `message_id` (`message_id`),
  FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`session_id`) REFERENCES `ai_chat_sessions` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`message_id`) REFERENCES `ai_chat_messages` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
COMMENT='User favorite AI chat messages';

-- AI Chat Search Index Table
CREATE TABLE IF NOT EXISTS `ai_chat_search_index` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `session_id` int(11) NOT NULL,
  `message_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `searchable_content` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_session_id` (`session_id`),
  KEY `message_id` (`message_id`),
  FULLTEXT KEY `ft_searchable_content` (`searchable_content`),
  FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`session_id`) REFERENCES `ai_chat_sessions` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`message_id`) REFERENCES `ai_chat_messages` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
COMMENT='Full-text search index for AI chat messages';

-- AI Chat Analytics Table
CREATE TABLE IF NOT EXISTS `ai_chat_analytics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `session_id` int(11) NOT NULL,
  `interaction_type` varchar(100) NOT NULL,
  `language_used` varchar(10) DEFAULT 'en-US',
  `voice_used` varchar(50) DEFAULT NULL,
  `success` tinyint(1) DEFAULT 1,
  `error_message` text DEFAULT NULL,
  `mode_used` varchar(50) DEFAULT 'general',
  `tokens_consumed` int(11) DEFAULT 0,
  `response_time` decimal(8,3) DEFAULT 0.000,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_session_id` (`session_id`),
  KEY `idx_interaction_type` (`interaction_type`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_mode_used` (`mode_used`),
  FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`session_id`) REFERENCES `ai_chat_sessions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
COMMENT='Analytics and usage tracking for AI chat system';

-- Add missing columns to existing tables
ALTER TABLE `ai_chat_analytics` 
ADD COLUMN IF NOT EXISTS `mode_used` varchar(50) DEFAULT 'general' AFTER `error_message`,
ADD COLUMN IF NOT EXISTS `tokens_consumed` int(11) DEFAULT 0 AFTER `mode_used`,
ADD COLUMN IF NOT EXISTS `response_time` decimal(8,3) DEFAULT 0.000 AFTER `tokens_consumed`;

-- Add enhanced indexes for better performance
CREATE INDEX IF NOT EXISTS idx_session_user_active ON ai_chat_sessions (user_id, is_active, updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_message_session_type ON ai_chat_messages (session_id, message_type, created_at);
CREATE INDEX IF NOT EXISTS idx_analytics_user_date ON ai_chat_analytics (user_id, created_at, mode_used);
CREATE INDEX IF NOT EXISTS idx_mode_used ON ai_chat_analytics (mode_used);

-- Add sample data for AI chat modes (optional)
INSERT IGNORE INTO ai_chat_sessions (id, user_id, session_title, session_type, created_at) VALUES 
(1, 1, 'Sample AI Chat Session', 'text', NOW()) 
ON DUPLICATE KEY UPDATE session_title = 'Sample AI Chat Session';

-- Create a view for chat session summaries
CREATE OR REPLACE VIEW ai_chat_session_summary AS
SELECT 
    s.id as session_id,
    s.user_id,
    s.session_title,
    s.session_type,
    s.is_active,
    s.created_at,
    s.updated_at,
    COUNT(m.id) as total_messages,
    COUNT(CASE WHEN m.message_type = 'user' THEN 1 END) as user_messages,
    COUNT(CASE WHEN m.message_type = 'assistant' THEN 1 END) as assistant_messages,
    MAX(m.created_at) as last_message_at,
    SUBSTRING(
        COALESCE(
            (SELECT content FROM ai_chat_messages 
             WHERE session_id = s.id AND message_type = 'user' 
             ORDER BY created_at DESC LIMIT 1), 
            'No messages'
        ), 1, 100
    ) as last_user_message_preview
FROM ai_chat_sessions s
LEFT JOIN ai_chat_messages m ON s.id = m.session_id
GROUP BY s.id, s.user_id, s.session_title, s.session_type, s.is_active, s.created_at, s.updated_at;

-- Create a view for user chat statistics
CREATE OR REPLACE VIEW user_chat_statistics AS
SELECT 
    u.id as user_id,
    u.fullname as username,
    u.email as user_email,
    COUNT(DISTINCT s.id) as total_sessions,
    COUNT(DISTINCT CASE WHEN s.is_active = 1 THEN s.id END) as active_sessions,
    COUNT(m.id) as total_messages,
    COUNT(CASE WHEN m.message_type = 'user' THEN 1 END) as user_messages_sent,
    COUNT(CASE WHEN m.message_type = 'assistant' THEN 1 END) as ai_responses_received,
    COUNT(f.id) as favorite_messages,
    COALESCE(SUM(a.tokens_consumed), 0) as total_tokens_used,
    MAX(s.updated_at) as last_chat_activity
FROM user u
LEFT JOIN ai_chat_sessions s ON u.id = s.user_id
LEFT JOIN ai_chat_messages m ON s.id = m.session_id
LEFT JOIN ai_chat_favorites f ON u.id = f.user_id
LEFT JOIN ai_chat_analytics a ON u.id = a.user_id
GROUP BY u.id, u.fullname, u.email;

-- Add some helpful procedures
DELIMITER //

-- Procedure to clean up old inactive sessions
CREATE PROCEDURE CleanupInactiveSessions(IN days_old INT)
BEGIN
    DELETE FROM ai_chat_sessions 
    WHERE is_active = 0 
    AND updated_at < DATE_SUB(NOW(), INTERVAL days_old DAY);
    
    SELECT ROW_COUNT() as deleted_sessions;
END //

-- Procedure to get user chat summary
CREATE PROCEDURE GetUserChatSummary(IN p_user_id INT)
BEGIN
    SELECT 
        user_id,
        username,
        user_email,
        total_sessions,
        active_sessions,
        total_messages,
        user_messages_sent,
        ai_responses_received,
        favorite_messages,
        total_tokens_used,
        last_chat_activity
    FROM user_chat_statistics 
    WHERE user_id = p_user_id;
END //

DELIMITER ;

-- Add some helpful comments
ALTER TABLE ai_chat_sessions COMMENT = 'Enhanced AI Chat sessions with multi-mode support';
ALTER TABLE ai_chat_messages COMMENT = 'AI Chat messages with rich metadata and mode context';
ALTER TABLE ai_chat_analytics COMMENT = 'Detailed analytics for AI chat usage and performance';

-- Create indexes for common queries
CREATE INDEX IF NOT EXISTS idx_messages_session_time ON ai_chat_messages (session_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_favorites_user_time ON ai_chat_favorites (user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_search_user_content ON ai_chat_search_index (user_id, searchable_content(50));

-- Final optimizations
OPTIMIZE TABLE ai_chat_sessions;
OPTIMIZE TABLE ai_chat_messages;
OPTIMIZE TABLE ai_chat_favorites;
OPTIMIZE TABLE ai_chat_search_index;
OPTIMIZE TABLE ai_chat_analytics;

-- Success message
SELECT 'AI Chat System Enhanced Successfully!' as status,
       'All tables, indexes, views, and procedures created' as details,
       NOW() as completed_at;
