<?php
/**
 * Database Migration Runner
 * Executes all pending migration scripts
 */

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

use SSCI\Classes\Database;

// Load database configuration
$dbConfig = require __DIR__ . '/../config/database.php';

// Create database instance
$db = new Database($dbConfig);

$migrationDir = __DIR__ . '/migrations';

// Get all SQL migration files
$files = glob($migrationDir . '/*.sql');
sort($files);

echo "Starting database migrations...\n";
echo "Found " . count($files) . " migration files\n\n";

foreach ($files as $file) {
    echo "Running migration: " . basename($file) . "\n";
    
    try {
        $sql = file_get_contents($file);
        
        // Split by semicolons and execute each statement
        $lines = array_map('trim', explode("\n", $sql));
        $statement = '';
        
        foreach ($lines as $line) {
            // Skip comments and empty lines
            if (empty($line) || strpos($line, '--') === 0) {
                continue;
            }
            
            $statement .= ' ' . $line;
            
            if (strpos($line, ';') !== false) {
                if (!empty(trim($statement))) {
                    try {
                        $db->query(trim($statement));
                    } catch (Exception $e) {
                        // Ignore errors for existing columns, etc.
                        if (strpos($e->getMessage(), 'Duplicate') === false && 
                            strpos($e->getMessage(), 'already exists') === false &&
                            strpos($e->getMessage(), 'cannot add') === false) {
                            throw $e;
                        }
                    }
                }
                $statement = '';
            }
        }
        
        echo "✓ Completed\n";
    } catch (Exception $e) {
        echo "✗ Error: " . $e->getMessage() . "\n";
    }
}

echo "\nAll migrations completed!\n";
?>
