<?php
// Database connection details
$db_host = 'localhost';
$db_user = 'pma_user';
$db_password = 'strong_password';

// Enable error reporting
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Create connection
$conn = new mysqli($db_host, $db_user, $db_password);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

echo "<!DOCTYPE html><html><head><title>Database Table Creator</title>";
echo "<style>
    body { font-family: monospace; margin: 20px; }
    .success { color: green; }
    .skip { color: orange; }
    .error { color: red; }
    .info { color: blue; }
    .summary { margin-top: 20px; padding: 10px; background: #f0f0f0; border-radius: 5px; }
    h1 { color: #333; }
    pre { background: #fafafa; padding: 10px; border: 1px solid #ddd; }
</style></head><body>";
echo "<h1>Creating tbl_marketing_campaigns in all databases</h1>";
echo "<pre>";

// SQL to create the table
$create_table_sql = "
CREATE TABLE IF NOT EXISTS `tbl_marketing_campaigns` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `group_filter` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'all',
  `router_filter` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `message` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `via` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'sms',
  `batch_size` int NOT NULL DEFAULT '50',
  `delay_seconds` int NOT NULL DEFAULT '0',
  `scheduled_at` datetime NOT NULL,
  `status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `total_recipients` int NOT NULL DEFAULT '0',
  `total_sent` int NOT NULL DEFAULT '0',
  `total_failed` int NOT NULL DEFAULT '0',
  `sent_at` datetime DEFAULT NULL,
  `created_by` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

// First, get all databases from MySQL
echo "<span class='info'>?? Fetching all databases from MySQL server...</span>\n";

$all_databases = [];
$result = $conn->query("SHOW DATABASES");

if ($result) {
    while ($row = $result->fetch_array()) {
        $db_name = $row[0];
        // Skip system databases
        if (!in_array($db_name, ['information_schema', 'mysql', 'performance_schema', 'sys', 'phpmyadmin'])) {
            $all_databases[] = $db_name;
        }
    }
}

echo "<span class='info'>? Found " . count($all_databases) . " user databases</span>\n\n";

// If you want to only process specific databases, uncomment this:
/*
$specific_databases = [
    'alpha', 'amzwifi', 'basiru', 'billing', 'bissau', 'blacka', 'bulanjor',
    'busra', 'byd', 'byde', 'dembawifi', 'dembawifi1', 'dijastarnet', 'empire',
    'enjoy', 'enjoy1', 'fast', 'fatty', 'foday', 'isp', 'jahstarnet1',
    'jahstarnet2', 'jallowwifi', 'jamano', 'jambur', 'jamisa', 'jamono', 'janz',
    'kanneh', 'kiosk', 'master', 'master-installer', 'modou', 'mummy', 'network',
    'njies', 'nnnwifi', 'oj', 'pkt', 'pointnet', 'register', 'sara', 'sol',
    'stayfocus', 'tendaba', 'vpnonex', 'wurry', 'zainaba'
];

// Filter to only include databases that exist
$databases_to_process = array_intersect($specific_databases, $all_databases);
$missing_databases = array_diff($specific_databases, $all_databases);

if (!empty($missing_databases)) {
    echo "<span class='error'>?? Warning: These databases don't exist: " . implode(', ', $missing_databases) . "</span>\n\n";
}
*/

// If you want to process ALL user databases, use this instead:
$databases_to_process = $all_databases;

echo "<span class='info'>?? Processing " . count($databases_to_process) . " databases...</span>\n\n";
echo str_repeat("=", 60) . "\n";

$results = ['success' => [], 'skip' => [], 'error' => []];

foreach ($databases_to_process as $database) {
    echo "Processing: {$database}... ";
    
    // Select the database
    if (!$conn->select_db($database)) {
        echo "<span class='error'>? Cannot select database</span>\n";
        $results['error'][] = $database;
        continue;
    }
    
    // Check if table already exists
    $check_table = $conn->query("SHOW TABLES LIKE 'tbl_marketing_campaigns'");
    
    if ($check_table && $check_table->num_rows > 0) {
        echo "<span class='skip'>?? Table already exists - skipped</span>\n";
        $results['skip'][] = $database;
        continue;
    }
    
    // Create the table
    if ($conn->query($create_table_sql)) {
        echo "<span class='success'>? Table created successfully</span>\n";
        $results['success'][] = $database;
    } else {
        echo "<span class='error'>? Error: " . $conn->error . "</span>\n";
        $results['error'][] = $database;
    }
}

// Summary
echo "\n" . str_repeat("=", 60) . "\n";
echo "<div class='summary'>";
echo "<strong>?? SUMMARY:</strong><br>";
echo "? New tables created: " . count($results['success']) . " databases<br>";
echo "?? Skipped (table existed): " . count($results['skip']) . " databases<br>";
echo "? Errors: " . count($results['error']) . " databases<br>";
echo "?? Total processed: " . count($databases_to_process) . " databases<br>";

if (!empty($results['success'])) {
    echo "<br><strong>? Tables created in:</strong><br>";
    foreach ($results['success'] as $db) {
        echo "  • $db<br>";
    }
}

if (!empty($results['skip']) && count($results['skip']) < 50) {
    echo "<br><strong>?? Skipped (already had table):</strong><br>";
    foreach ($results['skip'] as $db) {
        echo "  • $db<br>";
    }
}

if (!empty($results['error'])) {
    echo "<br><strong>? Errors occurred in:</strong><br>";
    foreach ($results['error'] as $db) {
        echo "  • $db<br>";
    }
}
echo "</div>";

$conn->close();
echo "</pre></body></html>";
?>