<?php
/**
 * Original content of setup_ai_survey_system.php (archived)
 * Saved: archived_test_files/setup_ai_survey_system.original.txt
 */
<?php
/**
 * Setup AI Survey System Database Tables
 * This script creates all required tables for the AI survey generator system
 */

require_once 'config/DB.php';

try {
    $pdo = DB::getConnection();
    echo "Setting up AI Survey System database tables...\n\n";
    
    // Create ai_surveys table
    $sql = "CREATE TABLE IF NOT EXISTS ai_surveys (
        id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
        title varchar(255) NOT NULL,
        description text,
        survey_data JSON,
        status enum('draft','active','completed','archived') NOT NULL DEFAULT 'draft',
        target_responses int(11) DEFAULT 100,
        actual_responses int(11) DEFAULT 0,
        hot_topic_id int(11),
        hot_topic_trend_score decimal(5,2) DEFAULT 0.00,
        trending_score decimal(5,2) DEFAULT 0.00,
        start_date datetime,
        end_date datetime,
        created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_status (status),
        INDEX idx_created_at (created_at),
        INDEX idx_end_date (end_date)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
    
    $pdo->exec($sql);
    echo "✓ ai_surveys table created/verified\n";
    
    // Create hot_topics_tracker table
    $sql = "CREATE TABLE IF NOT EXISTS hot_topics_tracker (
        id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
        topic_name varchar(255) NOT NULL,
        description text,
        keywords text,
        trend_score decimal(5,2) NOT NULL DEFAULT 0.00,
        source varchar(100) DEFAULT 'manual',
        status enum('active','inactive','used') NOT NULL DEFAULT 'active',
        last_used_date datetime NULL,
        created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_status (status),
        INDEX idx_trend_score (trend_score),
        INDEX idx_updated_at (updated_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
    
    $pdo->exec($sql);
    echo "✓ hot_topics_tracker table created/verified\n";
    
    // Create survey_responses table
    $sql = "CREATE TABLE IF NOT EXISTS survey_responses (
        id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
        survey_id int(11) NOT NULL,
        response_data JSON,
        participant_ip varchar(45),
        participant_age_group varchar(20),
        participant_location varchar(100),
        created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_survey_id (survey_id),
        INDEX idx_created_at (created_at),
        FOREIGN KEY (survey_id) REFERENCES ai_surveys(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
    
    $pdo->exec($sql);
    echo "✓ survey_responses table created/verified\n";
    
    // Create survey_social_shares table
    $sql = "CREATE TABLE IF NOT EXISTS survey_social_shares (
        id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
        survey_id int(11) NOT NULL,
        platform varchar(50) NOT NULL,
        share_data JSON,
        status enum('scheduled','posted','failed') NOT NULL DEFAULT 'scheduled',
        scheduled_at datetime,
        posted_at datetime,
        created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_survey_id (survey_id),
        INDEX idx_platform (platform),
        INDEX idx_status (status),
        FOREIGN KEY (survey_id) REFERENCES ai_surveys(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
    
    $pdo->exec($sql);
    echo "✓ survey_social_shares table created/verified\n";
    
    // Create survey_analytics table
    $sql = "CREATE TABLE IF NOT EXISTS survey_analytics (
        id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
        survey_id int(11) NOT NULL,
        metric_name varchar(100) NOT NULL,
        metric_value text,
        metric_category varchar(50) DEFAULT 'general',
        calculated_date date NOT NULL,
        created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_survey_id (survey_id),
        INDEX idx_metric_name (metric_name),
        INDEX idx_calculated_date (calculated_date),
        FOREIGN KEY (survey_id) REFERENCES ai_surveys(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
    
    $pdo->exec($sql);
    echo "✓ survey_analytics table created/verified\n";
    
    // Create ai_survey_logs table (if not already created)
    $sql = "CREATE TABLE IF NOT EXISTS ai_survey_logs (
        id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
        event_type varchar(50) NOT NULL,
        event_data JSON,
        status enum('success','error','skipped','info') NOT NULL DEFAULT 'info',
        created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_event_type (event_type),
        INDEX idx_status (status),
        INDEX idx_created_at (created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
    
    $pdo->exec($sql);
    echo "✓ ai_survey_logs table created/verified\n";
    
    // Insert sample hot topics if none exist
    $stmt = $pdo->query("SELECT COUNT(*) as count FROM hot_topics_tracker");
    $count = $stmt->fetch()['count'];
    
    if ($count == 0) {
        // (Original had sample insertion here)
    }
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}

