-- Chat System Setup Script
-- Run this script to set up the AI chat functionality in your database

-- Use your existing database
-- USE your_database_name;

-- Create AI chat sessions table
CREATE TABLE IF NOT EXISTS ai_chat_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    session_title VARCHAR(255) DEFAULT 'New Chat',
    session_type ENUM('text', 'multivoice', 'pdf') DEFAULT 'text',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_created_at (created_at),
    FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE
);

-- Create AI chat messages table
CREATE TABLE IF NOT EXISTS ai_chat_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id INT NOT NULL,
    message_type ENUM('user', 'assistant', 'system') NOT NULL,
    content TEXT NOT NULL,
    metadata JSON DEFAULT NULL,
    parent_message_id INT DEFAULT NULL,
    audio_url VARCHAR(500) DEFAULT NULL,
    image_url VARCHAR(500) DEFAULT NULL,
    tokens_used INT DEFAULT 0,
    processing_time FLOAT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_session_id (session_id),
    INDEX idx_message_type (message_type),
    INDEX idx_created_at (created_at),
    INDEX 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
);

-- Create AI chat analytics table
CREATE TABLE IF NOT EXISTS ai_chat_analytics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    session_id INT NOT NULL,
    interaction_type VARCHAR(100) NOT NULL,
    language_used VARCHAR(10) DEFAULT 'en-US',
    voice_used VARCHAR(50) DEFAULT NULL,
    success BOOLEAN DEFAULT TRUE,
    error_message TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_session_id (session_id),
    INDEX idx_interaction_type (interaction_type),
    INDEX idx_created_at (created_at),
    FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE,
    FOREIGN KEY (session_id) REFERENCES ai_chat_sessions(id) ON DELETE CASCADE
);

-- Create AI chat favorites table
CREATE TABLE IF NOT EXISTS ai_chat_favorites (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    session_id INT NOT NULL,
    message_id INT DEFAULT NULL,
    notes TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_session_id (session_id),
    UNIQUE KEY unique_user_session_message (user_id, session_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
);

-- Create AI chat search index for full-text search
CREATE TABLE IF NOT EXISTS ai_chat_search_index (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id INT NOT NULL,
    message_id INT NOT NULL,
    user_id INT NOT NULL,
    searchable_content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FULLTEXT KEY ft_searchable_content (searchable_content),
    INDEX idx_user_id (user_id),
    INDEX idx_session_id (session_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
);

-- Verify tables were created
SHOW TABLES LIKE 'ai_chat%';

-- Display table structures
DESCRIBE ai_chat_sessions;
DESCRIBE ai_chat_messages;
DESCRIBE ai_chat_analytics;
DESCRIBE ai_chat_favorites;
DESCRIBE ai_chat_search_index;