<?php
/**
 * Tap2Order Database Migration Runner
 * Run from CLI: php database/migrate.php
 * Or access via browser: http://localhost/tap2order/database/migrate.php (requires super_admin auth)
 */

$isCli = php_sapi_name() === 'cli';

if (!$isCli) {
    session_start();
    require_once __DIR__ . '/../config/database.php';
    require_once __DIR__ . '/../config/config.php';
    $auth = new Auth();
    if (!$auth->check() || !$auth->isSuperAdmin()) {
        http_response_code(403);
        die('Access denied. Super Admin only.');
    }
}

require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../config/config.php';

$db = Database::getInstance();
$migrationsDir = __DIR__ . '/migrations';

if (!is_dir($migrationsDir)) {
    echo "No migrations directory found.\n";
    exit(0);
}

// Create migrations tracking table if not exists
$db->query("
    CREATE TABLE IF NOT EXISTS `migrations` (
        `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
        `filename` VARCHAR(255) NOT NULL,
        `applied_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `uk_migration_filename` (`filename`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
");

// Get already applied migrations
$pdo = $db->getConnection();
$stmt = $pdo->query("SELECT filename FROM migrations ORDER BY id ASC");
$applied = $stmt->fetchAll(PDO::FETCH_ASSOC);
$appliedFiles = array_column($applied, 'filename');

// Find new migration files
$files = glob($migrationsDir . '/*.sql');
sort($files);

$newCount = 0;
foreach ($files as $file) {
    $basename = basename($file);
    if (in_array($basename, $appliedFiles)) {
        if ($isCli) {
            echo "[SKIP] {$basename} (already applied)\n";
        }
        continue;
    }

    if ($isCli) {
        echo "[APPLY] {$basename} ... ";
    }

    $sql = file_get_contents($file);

    // Split by semicolons (basic approach, handles most cases)
    $statements = array_filter(array_map('trim', explode(';', $sql)));

    try {
        $db->beginTransaction();
        foreach ($statements as $stmt) {
            if (!empty($stmt) && $stmt !== '--') {
                $db->query($stmt);
            }
        }
        $db->insert('migrations', ['filename' => $basename]);
        $db->commit();
        $newCount++;
        if ($isCli) {
            echo "DONE\n";
        }
    } catch (Exception $e) {
        $db->rollback();
        if ($isCli) {
            echo "FAILED: " . $e->getMessage() . "\n";
        }
        error_log("Migration failed: {$basename} - " . $e->getMessage());
    }
}

if ($isCli) {
    echo "\nMigration complete. {$newCount} new migration(s) applied.\n";
} else {
    header('Content-Type: application/json');
    echo json_encode(['success' => true, 'message' => "{$newCount} migration(s) applied successfully"]);
}
