<?php
/**
 * HR Management System - Database Migration Script
 * Applies hrm_extensions.sql migration
 * Created: 2026-02-16
 */

require_once 'config/config.php';

echo "╔════════════════════════════════════════════════════════════════╗\n";
echo "║   HR MANAGEMENT SYSTEM - DATABASE MIGRATION SCRIPT             ║\n";
echo "╚════════════════════════════════════════════════════════════════╝\n\n";

// Read the migration SQL file
$migration_file = __DIR__ . '/database/migrations/hrm_extensions.sql';

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

echo "📂 Reading migration file: $migration_file\n";
$sql_content = file_get_contents($migration_file);

// Parse SQL file - split by statement properly
$statements = [];
$current_statement = '';
$in_string = false;
$string_char = '';

$lines = explode("\n", $sql_content);

foreach ($lines as $line) {
    // Skip comments
    if (trim($line) === '' || substr(trim($line), 0, 2) === '--') {
        continue;
    }

    $current_statement .= $line . "\n";

    // Check if statement ends with semicolon
    if (substr(trim($line), -1) === ';') {
        $stmt = trim($current_statement);
        if (!empty($stmt)) {
            $statements[] = substr($stmt, 0, -1); // Remove trailing semicolon
        }
        $current_statement = '';
    }
}

$total = count($statements);
$success = 0;
$failed = 0;
$errors = [];

echo "📊 Found " . $total . " SQL statements to execute\n\n";
echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\n";

try {
    foreach ($statements as $index => $statement) {
        $statement = trim($statement);
        if (empty($statement)) {
            continue;
        }

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

            // Determine what was created
            if (stripos($statement, 'CREATE TABLE') !== false) {
                preg_match('/`?(\w+)`?.*\(/i', $statement, $matches);
                $table = isset($matches[1]) ? $matches[1] : 'table';
                echo "✅ Created table: $table\n";
            } elseif (stripos($statement, 'INSERT INTO') !== false) {
                preg_match('/INSERT INTO `?(\w+)`?/i', $statement, $matches);
                $table = isset($matches[1]) ? $matches[1] : 'table';
                echo "✅ Inserted data into: $table\n";
            } else {
                echo "✅ Executed statement " . ($index + 1) . "\n";
            }
        } catch (PDOException $e) {
            // Some statements might fail if tables already exist, which is OK
            if (strpos($e->getMessage(), 'already exists') !== false) {
                echo "⚠️  Table already exists (skipped)\n";
                $success++;
            } elseif (strpos($e->getMessage(), 'tbl_system_migrations') !== false) {
                // Skip the system migrations table if it doesn't exist
                echo "⚠️  System migrations table not available (skipped)\n";
                $success++;
            } else {
                $failed++;
                $errors[] = "Statement " . ($index + 1) . ": " . $e->getMessage();
                echo "❌ Failed: " . substr($e->getMessage(), 0, 80) . "\n";
            }
        }
    }
} catch (Exception $e) {
    echo "❌ Fatal error: " . $e->getMessage() . "\n";
    exit(1);
}

echo "\n━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\n";
echo "🔍 VERIFYING TABLES...\n";
echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\n\n";

$tables_to_check = [
    'tbl_salary_templates',
    'tbl_employee_salary_templates',
    'tbl_shifts',
    'tbl_employee_shifts',
    'tbl_attendance',
    'tbl_attendance_summary',
    'tbl_payroll',
    'tbl_payroll_adjustments'
];

$tables_created = 0;

foreach ($tables_to_check as $table) {
    try {
        $result = $pdo->query("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = '$table'");
        $exists = $result->fetchColumn() > 0;

        if ($exists) {
            // Get table row count
            $count_result = $pdo->query("SELECT COUNT(*) FROM $table");
            $count = $count_result->fetchColumn();
            echo "✅ $table";
            if ($count > 0) {
                echo " (Rows: $count)";
            }
            echo "\n";
            $tables_created++;
        } else {
            echo "❌ $table - NOT FOUND\n";
        }
    } catch (Exception $e) {
        echo "❓ $table - Error: " . substr($e->getMessage(), 0, 60) . "\n";
    }
}

echo "\n━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\n";
echo "📊 MIGRATION SUMMARY\n";
echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\n\n";

echo "Statements Executed: $success/$total\n";
echo "Tables Created: $tables_created/8\n";
echo "Failed Statements: $failed\n\n";

if ($failed > 0) {
    echo "❌ ERRORS:\n";
    foreach ($errors as $error) {
        echo "  • $error\n";
    }
    echo "\n";
}

if ($tables_created === 8) {
    echo "✅ MIGRATION COMPLETED SUCCESSFULLY!\n";
    echo "   All 8 tables have been created.\n";
    echo "\n🎉 Sample Data Loaded:\n";
    echo "   • 4 Shifts\n";
    echo "   • 4 Salary Templates\n";
    echo "\n📚 Next Steps:\n";
    echo "   1. Read: README_HR_SYSTEM.md\n";
    echo "   2. Review: HRM_IMPLEMENTATION_GUIDE.md\n";
    echo "   3. Start Phase 1: Salary Templates\n";
    exit(0);
} else {
    echo "⚠️  MIGRATION PARTIALLY COMPLETE\n";
    echo "   " . (8 - $tables_created) . " tables are missing.\n";
    exit(1);
}

?>
