<?php

/**
 * Database Migration Script
 * Run this script to set up the database tables
 */

require __DIR__ . '/vendor/autoload.php';

use App\Config;

echo "=== BotAJS Git Integration - Database Migration ===\n\n";

// Load configuration
Config::load(__DIR__ . '/.env');

$host = Config::get('DB_HOST', 'localhost');
$dbname = Config::get('DB_NAME', 'dannvtuc_botajs');
$username = Config::get('DB_USER', 'dannvtuc_botajs');
$password = Config::get('DB_PASSWORD', '');

echo "Database Configuration:\n";
echo "  Host: $host\n";
echo "  Database: $dbname\n";
echo "  Username: $username\n";
echo "  Password: " . (empty($password) ? '[NOT SET]' : '[SET]') . "\n\n";

if (empty($password)) {
    echo "⚠️  WARNING: Database password is not set in .env file!\n";
    echo "   Please update DB_PASSWORD in your .env file.\n\n";
    $continue = readline("Continue anyway? (y/n): ");
    if (strtolower($continue) !== 'y') {
        exit(0);
    }
    echo "\n";
}

// Connect to database
echo "Connecting to database...\n";
try {
    $dsn = "mysql:host={$host};dbname={$dbname};charset=utf8mb4";
    $pdo = new PDO($dsn, $username, $password, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
    echo "✓ Connected successfully\n\n";
} catch (PDOException $e) {
    echo "✗ Connection failed: " . $e->getMessage() . "\n\n";
    echo "Troubleshooting:\n";
    echo "1. Verify database exists in cPanel → MySQL Databases\n";
    echo "2. Verify user has privileges for this database\n";
    echo "3. Check DB_PASSWORD in .env file\n";
    exit(1);
}

// Read and execute schema
echo "Reading schema file...\n";
$schemaFile = __DIR__ . '/database/schema.sql';

if (!file_exists($schemaFile)) {
    echo "✗ Schema file not found: $schemaFile\n";
    exit(1);
}

$sql = file_get_contents($schemaFile);
echo "✓ Schema file loaded\n\n";

// Remove comments and split into statements
$statements = array_filter(
    array_map('trim', preg_split('/;[\s]*$/m', $sql)),
    function ($stmt) {
        return !empty($stmt) && !preg_match('/^--/', $stmt);
    }
);

echo "Executing migration...\n";
$success = 0;
$failed = 0;

foreach ($statements as $statement) {
    try {
        $pdo->exec($statement);
        $success++;

        // Extract table name from CREATE TABLE statement
        if (preg_match('/CREATE TABLE.*?`(\w+)`/i', $statement, $matches)) {
            echo "✓ Created table: {$matches[1]}\n";
        }
    } catch (PDOException $e) {
        $failed++;
        echo "✗ Error: " . $e->getMessage() . "\n";
    }
}

echo "\n";
echo "Migration completed:\n";
echo "  Successful: $success\n";
echo "  Failed: $failed\n\n";

// Verify tables exist
echo "Verifying tables...\n";
try {
    $stmt = $pdo->query("SHOW TABLES LIKE 'ssh_keys'");
    $table = $stmt->fetch();

    if ($table) {
        echo "✓ Table 'ssh_keys' exists\n";

        // Show table structure
        $stmt = $pdo->query("DESCRIBE ssh_keys");
        $columns = $stmt->fetchAll();

        echo "\nTable structure:\n";
        foreach ($columns as $column) {
            echo "  - {$column['Field']} ({$column['Type']})\n";
        }

        // Show row count
        $stmt = $pdo->query("SELECT COUNT(*) as count FROM ssh_keys");
        $count = $stmt->fetch();
        echo "\nCurrent rows: {$count['count']}\n";

    } else {
        echo "✗ Table 'ssh_keys' not found\n";
    }
} catch (PDOException $e) {
    echo "✗ Verification error: " . $e->getMessage() . "\n";
}

echo "\n=== Migration Complete ===\n\n";

if ($failed === 0) {
    echo "✓ All migrations successful!\n";
    echo "  You can now use the database for SSH key storage.\n\n";
    echo "Next steps:\n";
    echo "1. Run: php test-setup.php (to verify full installation)\n";
    echo "2. Generate SSH key: POST /api/git/generate-key\n";
    echo "3. Check your SSH keys: POST /api/user/ssh-keys\n";
} else {
    echo "⚠️  Some migrations failed. Please check the errors above.\n";
    echo "   You may need to manually create the tables via phpMyAdmin.\n";
}

echo "\n";
