-- Analytics Database Schema
-- Tables for tracking user behavior, conversions, and platform analytics

-- User engagement tracking
CREATE TABLE IF NOT EXISTS analytics_user_sessions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    session_id VARCHAR(255),
    vertical_id INT,
    start_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    end_time TIMESTAMP NULL,
    duration_seconds INT DEFAULT 0,
    messages_sent INT DEFAULT 0,
    offers_viewed INT DEFAULT 0,
    offers_saved INT DEFAULT 0,
    offers_clicked INT DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES tbl_users(id) ON DELETE CASCADE,
    FOREIGN KEY (vertical_id) REFERENCES verticals(id) ON DELETE SET NULL,
    INDEX idx_user_session (user_id, session_id),
    INDEX idx_vertical (vertical_id),
    INDEX idx_start_time (start_time)
);

-- Offer performance tracking
CREATE TABLE IF NOT EXISTS analytics_offer_performance (
    id INT PRIMARY KEY AUTO_INCREMENT,
    offer_id INT,
    vertical_id INT,
    user_id INT,
    action_type ENUM('view', 'save', 'click', 'convert') NOT NULL,
    action_value DECIMAL(10,2) DEFAULT 0,
    session_id VARCHAR(255),
    user_agent TEXT,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES tbl_users(id) ON DELETE CASCADE,
    FOREIGN KEY (vertical_id) REFERENCES verticals(id) ON DELETE SET NULL,
    INDEX idx_offer (offer_id),
    INDEX idx_user (user_id),
    INDEX idx_action (action_type),
    INDEX idx_created_at (created_at)
);

-- Vertical switch tracking
CREATE TABLE IF NOT EXISTS analytics_vertical_switches (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    from_vertical VARCHAR(100) NOT NULL,
    to_vertical VARCHAR(100) NOT NULL,
    switch_reason VARCHAR(100) DEFAULT 'user_initiated',
    conversation_context TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES tbl_users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_from_to (from_vertical, to_vertical),
    INDEX idx_created_at (created_at)
);

-- Upsell attempt tracking
CREATE TABLE IF NOT EXISTS analytics_upsells (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    from_vertical VARCHAR(100) NOT NULL,
    to_vertical VARCHAR(100) NOT NULL,
    trigger_reason VARCHAR(255),
    confidence_score DECIMAL(3,2),
    user_response ENUM('accepted', 'declined', 'ignored') DEFAULT 'ignored',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES tbl_users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_verticals (from_vertical, to_vertical),
    INDEX idx_response (user_response),
    INDEX idx_created_at (created_at)
);

-- Daily analytics summary
CREATE TABLE IF NOT EXISTS analytics_daily_summary (
    id INT PRIMARY KEY AUTO_INCREMENT,
    date DATE NOT NULL,
    vertical_id INT,
    total_users INT DEFAULT 0,
    new_users INT DEFAULT 0,
    total_sessions INT DEFAULT 0,
    total_messages INT DEFAULT 0,
    offers_viewed INT DEFAULT 0,
    offers_saved INT DEFAULT 0,
    offers_clicked INT DEFAULT 0,
    revenue_generated DECIMAL(10,2) DEFAULT 0,
    avg_session_duration INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (vertical_id) REFERENCES verticals(id) ON DELETE CASCADE,
    UNIQUE KEY unique_date_vertical (date, vertical_id),
    INDEX idx_date (date),
    INDEX idx_vertical (vertical_id)
);

-- Popular topics/questions tracking
CREATE TABLE IF NOT EXISTS analytics_popular_topics (
    id INT PRIMARY KEY AUTO_INCREMENT,
    vertical_id INT,
    topic VARCHAR(255) NOT NULL,
    question_count INT DEFAULT 0,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (vertical_id) REFERENCES verticals(id) ON DELETE CASCADE,
    UNIQUE KEY unique_vertical_topic (vertical_id, topic),
    INDEX idx_vertical (vertical_id),
    INDEX idx_count (question_count DESC)
);

-- Conversion funnel tracking
CREATE TABLE IF NOT EXISTS analytics_conversion_funnel (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    vertical_id INT,
    funnel_step ENUM('landing', 'survey_start', 'survey_complete', 'email_verify', 'chat_start', 'offer_view', 'offer_save', 'offer_click', 'conversion') NOT NULL,
    step_order INT NOT NULL,
    metadata JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES tbl_users(id) ON DELETE CASCADE,
    FOREIGN KEY (vertical_id) REFERENCES verticals(id) ON DELETE SET NULL,
    INDEX idx_user (user_id),
    INDEX idx_vertical (vertical_id),
    INDEX idx_step (funnel_step),
    INDEX idx_created_at (created_at)
);

-- Insert sample analytics data
INSERT IGNORE INTO analytics_popular_topics (vertical_id, topic, question_count) VALUES
(1, 'cannabis strains', 150),
(1, 'growing tips', 89),
(1, 'legal compliance', 67),
(2, 'cryptocurrency basics', 120),
(2, 'trading strategies', 95),
(2, 'risk management', 78),
(3, 'property investment', 110),
(3, 'market analysis', 82),
(3, 'financing options', 64);
