-- Advanced Search System Migration
-- Creates tables for search history and suggestions tracking

-- Search history table for analytics and recent searches
CREATE TABLE IF NOT EXISTS search_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    query TEXT NOT NULL,
    search_type ENUM('text', 'image', 'voice') DEFAULT 'text',
    filters JSON,
    results_count INT DEFAULT 0,
    clicked_result_id INT,
    clicked_result_type VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_created_at (created_at),
    INDEX idx_search_type (search_type)
);

-- Search suggestions cache for autocomplete
CREATE TABLE IF NOT EXISTS search_suggestions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    term VARCHAR(255) NOT NULL UNIQUE,
    frequency INT DEFAULT 1,
    category VARCHAR(50) DEFAULT 'general',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_term (term),
    INDEX idx_frequency (frequency DESC)
);

-- Insert some initial popular search terms
INSERT IGNORE INTO search_suggestions (term, frequency, category) VALUES
('calculus', 100, 'math'),
('physics', 95, 'science'),
('chemistry', 90, 'science'),
('programming', 85, 'technology'),
('python', 80, 'technology'),
('javascript', 75, 'technology'),
('machine learning', 70, 'technology'),
('biology', 65, 'science'),
('economics', 60, 'business'),
('accounting', 55, 'business'),
('english grammar', 50, 'language'),
('nepali', 45, 'language'),
('history', 40, 'humanities'),
('geography', 35, 'humanities'),
('mathematics', 30, 'math');
