<?php
/**
 * GoPools CRM — Database Migration Runner
 * 
 * Τρέχει αυτόματα κατά το deploy (από .cpanel.yml).
 * Μπορεί να τρέξει και χειροκίνητα: php scripts/migrate.php
 * 
 * Κάθε migration αρχείο: migrations/YYYYMMDD_HHMMSS_description.sql
 */

// ── Bootstrap ──────────────────────────────────────────────
define('MIGRATE_ROOT', __DIR__);
$configPath = __DIR__ . '/../app/config/database.php';

if (!file_exists($configPath)) {
    die("❌ Config file not found: $configPath\n");
}

$cfg = require $configPath;

// Support both array formats
$host = $cfg['host'] ?? $cfg['DB_HOST'] ?? '127.0.0.1';
$db   = $cfg['dbname'] ?? $cfg['DB_NAME'] ?? $cfg['database'] ?? '';
$user = $cfg['username'] ?? $cfg['DB_USER'] ?? $cfg['user'] ?? 'root';
$pass = $cfg['password'] ?? $cfg['DB_PASS'] ?? $cfg['pass'] ?? '';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$db;charset=utf8mb4", $user, $pass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    ]);
} catch (PDOException $e) {
    die("❌ DB connection failed: " . $e->getMessage() . "\n");
}

// ── Create migrations tracking table ──────────────────────
$pdo->exec("
    CREATE TABLE IF NOT EXISTS _migrations (
        id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        filename VARCHAR(255) NOT NULL UNIQUE,
        applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB
");

// ── Get already applied migrations ─────────────────────────
$applied = $pdo->query("SELECT filename FROM _migrations ORDER BY filename ASC")
               ->fetchAll(PDO::FETCH_COLUMN);
$applied = array_flip($applied); // for O(1) lookup

// ── Find all migration files ────────────────────────────────
$migrationsDir = __DIR__ . '/../migrations';
if (!is_dir($migrationsDir)) {
    mkdir($migrationsDir, 0755, true);
}

$files = glob($migrationsDir . '/*.sql');
sort($files); // apply in chronological order

$ran = 0;
$errors = 0;

foreach ($files as $filePath) {
    $filename = basename($filePath);

    if (isset($applied[$filename])) {
        echo "  ⏭  Skipped (already applied): $filename\n";
        continue;
    }

    $sql = file_get_contents($filePath);
    if (empty(trim($sql))) {
        echo "  ⚠️  Empty file, skipping: $filename\n";
        continue;
    }

    try {
        $pdo->exec($sql);
        $pdo->prepare("INSERT INTO _migrations (filename) VALUES (?)")->execute([$filename]);
        echo "  ✅ Applied: $filename\n";
        $ran++;
    } catch (PDOException $e) {
        echo "  ❌ ERROR in $filename: " . $e->getMessage() . "\n";
        $errors++;
        // Continue with next migration (don't stop all)
    }
}

echo "\n";
echo "────────────────────────────────────\n";
echo "  Migrations run: $ran\n";
if ($errors > 0) echo "  Errors: $errors\n";
echo "  DB: $db @ $host\n";
echo "  Done at: " . date('Y-m-d H:i:s') . "\n";
echo "────────────────────────────────────\n";
