<?php
/**
 * Chat System Database Setup
 * Run this once to create necessary tables
 */

require_once __DIR__ . '/../../config/env-loader.php';
require_once __DIR__ . '/../config/db.php';

// Create chat_sessions table
$sql_sessions = "CREATE TABLE IF NOT EXISTS chat_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(255) UNIQUE NOT NULL,
    category VARCHAR(50) NOT NULL DEFAULT 'general',
    user_email VARCHAR(255),
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ended_at TIMESTAMP NULL,
    is_active TINYINT(1) DEFAULT 1,
    ip_address VARCHAR(45),
    user_agent VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_session_id (session_id),
    INDEX idx_user_email (user_email),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

// Create chat_messages table
$sql_messages = "CREATE TABLE IF NOT EXISTS chat_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(255) NOT NULL,
    sender VARCHAR(50) NOT NULL,
    message LONGTEXT NOT NULL,
    category VARCHAR(50) NOT NULL DEFAULT 'general',
    user_identifier VARCHAR(255),
    is_read TINYINT(1) DEFAULT 1,
    response_time_ms INT DEFAULT NULL,
    tokens_used INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_session_id (session_id),
    INDEX idx_sender (sender),
    INDEX idx_created_at (created_at),
    FOREIGN KEY (session_id) REFERENCES chat_sessions(session_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

// Create chat_settings table
$sql_settings = "CREATE TABLE IF NOT EXISTS chat_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(255) UNIQUE NOT NULL,
    setting_value TEXT,
    setting_type VARCHAR(50),
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

// Execute queries
$tables = [
    'chat_sessions' => $sql_sessions,
    'chat_messages' => $sql_messages,
    'chat_settings' => $sql_settings
];

foreach ($tables as $table_name => $sql) {
    if ($conn->query($sql)) {
        echo "✅ Table '$table_name' created/verified successfully.\n";
    } else {
        echo "❌ Error creating table '$table_name': " . $conn->error . "\n";
    }
}

// Insert default settings if they don't exist
$default_settings = [
    ['ai_service', 'openai', 'string', 'AI service provider (openai, google, etc)'],
    ['max_message_length', '1000', 'integer', 'Maximum characters per message'],
    ['response_timeout', '30', 'integer', 'API response timeout in seconds'],
    ['enable_chat', '1', 'boolean', 'Enable/disable chat widget'],
    ['welcome_message', 'Hi! How can I help you today?', 'string', 'Default welcome message']
];

foreach ($default_settings as $setting) {
    $check = $conn->prepare("SELECT id FROM chat_settings WHERE setting_key = ?");
    $check->bind_param('s', $setting[0]);
    $check->execute();
    
    if ($check->get_result()->num_rows === 0) {
        $stmt = $conn->prepare("INSERT INTO chat_settings (setting_key, setting_value, setting_type, description) VALUES (?, ?, ?, ?)");
        $stmt->bind_param('ssss', $setting[0], $setting[1], $setting[2], $setting[3]);
        
        if ($stmt->execute()) {
            echo "✅ Setting '{$setting[0]}' inserted.\n";
        } else {
            echo "❌ Error inserting setting '{$setting[0]}': " . $conn->error . "\n";
        }
    }
}

echo "\n✅ Chat system database setup complete!\n";
echo "\nNext steps:\n";
echo "1. Set OpenAI API key: export OPENAI_API_KEY='your-api-key'\n";
echo "2. Or configure in .env file\n";
echo "3. Test the API at: /auth-system/api/chat-ai.php?action=get_status\n";
