<?php
/**
 * ============================================
 * DCI Control Center v1.0 - Database Migration
 * ============================================
 * Creates tables for crawler_settings and crawler_alerts
 * Run this script once before using the Control Center
 * ============================================
 */

// Configuration
$DB_HOST = 'localhost';
$DB_NAME = 'digupdog_FEED';
$DB_USER = 'digupdog_FEEDadmin';
$DB_PASS = 'Raimundinho1';

echo "==============================================\n";
echo "DCI Control Center v1.0 - Database Migration\n";
echo "==============================================\n\n";

try {
    $pdo = new PDO(
        "mysql:host=$DB_HOST;dbname=$DB_NAME;charset=utf8mb4",
        $DB_USER,
        $DB_PASS,
        [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
    );
    echo "[OK] Database connected\n";
} catch (PDOException $e) {
    die("[ERROR] Database connection failed: " . $e->getMessage() . "\n");
}

// Migration 1: crawler_settings table
echo "\n[1/4] Creating crawler_settings table...\n";
try {
    $pdo->exec("CREATE TABLE IF NOT EXISTS crawler_settings (
        id INT PRIMARY KEY AUTO_INCREMENT,
        setting_key VARCHAR(100) UNIQUE NOT NULL,
        setting_value TEXT,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_setting_key (setting_key)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    echo "[OK] crawler_settings table created/verified\n";
} catch (PDOException $e) {
    echo "[WARN] crawler_settings: " . $e->getMessage() . "\n";
}

// Migration 2: crawler_alerts table
echo "\n[2/4] Creating crawler_alerts table...\n";
try {
    $pdo->exec("CREATE TABLE IF NOT EXISTS crawler_alerts (
        id INT PRIMARY KEY AUTO_INCREMENT,
        process_id VARCHAR(32),
        alert_type ENUM('error', 'warning', 'info') DEFAULT 'info',
        message TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        acknowledged BOOLEAN DEFAULT FALSE,
        INDEX idx_process_id (process_id),
        INDEX idx_alert_type (alert_type),
        INDEX idx_acknowledged (acknowledged),
        INDEX idx_created_at (created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    echo "[OK] crawler_alerts table created/verified\n";
} catch (PDOException $e) {
    echo "[WARN] crawler_alerts: " . $e->getMessage() . "\n";
}

// Migration 3: Insert default settings
echo "\n[3/4] Inserting default settings...\n";
$defaultSettings = [
    'default_concurrency' => '10',
    'heartbeat_interval' => '15',
    'dead_timeout' => '45',
    'auto_cleanup' => '0',
    'max_processes' => '999999999',
    'default_wave_size' => '10'
];

$stmt = $pdo->prepare("INSERT IGNORE INTO crawler_settings (setting_key, setting_value) VALUES (?, ?)");
foreach ($defaultSettings as $key => $value) {
    try {
        $stmt->execute([$key, $value]);
        echo "  - $key = $value\n";
    } catch (PDOException $e) {
        echo "  - $key (already exists)\n";
    }
}
echo "[OK] Default settings configured\n";

// Migration 4: Verify existing tables have required columns
echo "\n[4/4] Verifying existing tables...\n";

// Check crawler_processes table for level_stats column
try {
    $checkCol = $pdo->query("SHOW COLUMNS FROM crawler_processes LIKE 'level_stats'");
    if ($checkCol->rowCount() === 0) {
        $pdo->exec("ALTER TABLE crawler_processes ADD COLUMN level_stats JSON NULL AFTER rate");
        echo "[OK] Added level_stats column to crawler_processes\n";
    } else {
        echo "[OK] crawler_processes.level_stats exists\n";
    }
} catch (PDOException $e) {
    echo "[WARN] crawler_processes: " . $e->getMessage() . "\n";
}

// Check crawler_pool table for level column
try {
    $checkCol = $pdo->query("SHOW COLUMNS FROM crawler_pool LIKE 'level'");
    if ($checkCol->rowCount() === 0) {
        $pdo->exec("ALTER TABLE crawler_pool ADD COLUMN level INT UNSIGNED DEFAULT 1 AFTER priority");
        $pdo->exec("ALTER TABLE crawler_pool ADD INDEX idx_level (level)");
        echo "[OK] Added level column to crawler_pool\n";
    } else {
        echo "[OK] crawler_pool.level exists\n";
    }
} catch (PDOException $e) {
    echo "[WARN] crawler_pool: " . $e->getMessage() . "\n";
}

echo "\n==============================================\n";
echo "Migration complete!\n";
echo "==============================================\n";

// Display table info
echo "\nTable Statistics:\n";
$tables = ['crawler_settings', 'crawler_alerts', 'crawler_processes', 'crawler_pool', 'crawler_log', 'crawler_domains'];
foreach ($tables as $table) {
    try {
        $count = $pdo->query("SELECT COUNT(*) FROM $table")->fetchColumn();
        echo "  - $table: $count rows\n";
    } catch (PDOException $e) {
        echo "  - $table: (not found)\n";
    }
}

echo "\nDCI Control Center is ready to use!\n";
echo "Access: /flowb0t.com/v2/views/dci-control-center.php\n";
