-- 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';

-- 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);

-- 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';

-- 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';

-- Final optimizations
OPTIMIZE TABLE ai_chat_sessions;

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