-- Memory System Database Schema
-- Adds intelligent memory capabilities to the existing DeepLeaf AI system

-- User profiles table for storing extracted user information
CREATE TABLE IF NOT EXISTS user_profiles (
    user_id VARCHAR(255) PRIMARY KEY,
    demographics JSON,
    interests JSON,
    survey_preferences JSON,
    expertise_level ENUM('beginner', 'intermediate', 'expert') DEFAULT 'beginner',
    monetization_profile JSON, -- Offer completion history, preferences
    personality_traits JSON, -- Communication style, preferences
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_expertise (expertise_level),
    INDEX idx_updated (updated_at)
);

-- Survey progress tracking for personalized survey flows
CREATE TABLE IF NOT EXISTS survey_progress (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id VARCHAR(255) NOT NULL,
    survey_id VARCHAR(255) NOT NULL,
    current_section VARCHAR(255),
    completed_questions JSON,
    abandonment_points JSON, -- Where users tend to drop off
    question_preferences JSON, -- Preferred question types/styles
    time_spent_per_section JSON, -- Engagement metrics
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES user_profiles(user_id) ON DELETE CASCADE,
    INDEX idx_user_survey (user_id, survey_id),
    INDEX idx_last_activity (last_activity),
    UNIQUE KEY unique_user_survey (user_id, survey_id)
);

-- Entity extraction results for user profiling
CREATE TABLE IF NOT EXISTS user_entities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id VARCHAR(255) NOT NULL,
    entity_type VARCHAR(100) NOT NULL, -- 'demographic', 'interest', 'preference', etc.
    entity_value VARCHAR(255) NOT NULL,
    confidence_score FLOAT DEFAULT 1.0,
    source_message TEXT, -- Original message where entity was extracted
    context JSON, -- Additional context about the extraction
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES user_profiles(user_id) ON DELETE CASCADE,
    INDEX idx_user_entity (user_id, entity_type),
    INDEX idx_confidence (confidence_score),
    UNIQUE KEY unique_entity (user_id, entity_type, entity_value)
);

-- Monetization memory for personalized offers
CREATE TABLE IF NOT EXISTS monetization_memory (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id VARCHAR(255) NOT NULL,
    offer_id VARCHAR(100) NOT NULL,
    action ENUM('shown', 'clicked', 'completed', 'rejected', 'dismissed') NOT NULL,
    offer_category VARCHAR(100), -- 'business', 'health', 'finance', etc.
    offer_value DECIMAL(10,2), -- Payout value
    context JSON, -- When/why the offer was shown
    response_time_seconds INT, -- How long user took to respond
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES user_profiles(user_id) ON DELETE CASCADE,
    INDEX idx_user_offer (user_id, offer_id),
    INDEX idx_category (offer_category),
    INDEX idx_action (action),
    INDEX idx_created (created_at)
);

-- System performance metrics
CREATE TABLE IF NOT EXISTS memory_performance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    metric_name VARCHAR(100) NOT NULL,
    metric_value FLOAT NOT NULL,
    context JSON, -- Additional context about the measurement
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_metric (metric_name, created_at)
);
END //

DELIMITER ;

-- Scheduled events for cleanup (run daily)
CREATE EVENT IF NOT EXISTS daily_memory_cleanup
ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP
DO
BEGIN
    CALL cleanup_expired_sessions();
    CALL cleanup_expired_memory();
END;
CREATE INDEX idx_entities_user_type ON user_entities (user_id, entity_type, confidence_score);

-- Enable event scheduler
SET GLOBAL event_scheduler = ON;
