<?php
/**
 * Enterprise HRM ERP System - DFC Excel Migration Runner
 * Run this script to create/update DFC database tables with all Excel columns
 */

require_once __DIR__ . '/../../includes/init.php';

// Check if user is admin
if (!hasAdminAccess()) {
    die('Access denied. Admin privileges required.');
}

$errors = [];
$success = [];

try {
    $db = getDB();
    $db->beginTransaction();
    
    // Read SQL file
    $sqlFile = __DIR__ . '/dfc_excel_schema.sql';
    if (!file_exists($sqlFile)) {
        throw new Exception("SQL file not found: {$sqlFile}");
    }
    
    $sql = file_get_contents($sqlFile);
    
    // Remove comments and split by semicolon
    $sql = preg_replace('/--.*$/m', '', $sql);
    $statements = array_filter(array_map('trim', explode(';', $sql)));
    
    foreach ($statements as $statement) {
        if (empty($statement)) continue;
        
        try {
            $db->exec($statement);
            $success[] = "Executed: " . substr($statement, 0, 50) . "...";
        } catch (PDOException $e) {
            // Ignore "table already exists", "duplicate column", and "duplicate key" errors
            $errorMsg = $e->getMessage();
            if (strpos($errorMsg, 'already exists') !== false || 
                strpos($errorMsg, 'Duplicate column') !== false ||
                strpos($errorMsg, 'Duplicate key') !== false ||
                strpos($errorMsg, '1060') !== false || // Duplicate column name
                strpos($errorMsg, '1061') !== false) { // Duplicate key name
                // Silently ignore - column/table already exists
            } else {
                $errors[] = "Error: " . $errorMsg . " (Statement: " . substr($statement, 0, 50) . "...)";
            }
        }
    }
    
    $db->commit();
    
} catch (Exception $e) {
    if (isset($db) && $db->inTransaction()) {
        $db->rollBack();
    }
    $errors[] = "Migration failed: " . $e->getMessage();
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>DFC Excel Migration</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            padding: 40px;
            background: #f5f5f5;
        }
        .container {
            background: white;
            padding: 30px;
            border-radius: 8px;
            max-width: 800px;
            margin: 0 auto;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
        }
        h1 {
            color: #333;
        }
        .success {
            color: #28a745;
            padding: 10px;
            background: #d4edda;
            border: 1px solid #c3e6cb;
            border-radius: 4px;
            margin: 10px 0;
        }
        .error {
            color: #dc3545;
            padding: 10px;
            background: #f8d7da;
            border: 1px solid #f5c6cb;
            border-radius: 4px;
            margin: 10px 0;
        }
        .btn {
            display: inline-block;
            padding: 10px 20px;
            background: #4a90e2;
            color: white;
            text-decoration: none;
            border-radius: 4px;
            margin-top: 20px;
        }
    </style>
</head>
<body>
    <div class="container">
        <h1>DFC Excel Database Migration</h1>
        
        <?php if (!empty($success)): ?>
        <div class="success">
            <strong>✓ Migration completed successfully!</strong>
            <ul>
                <?php foreach ($success as $msg): ?>
                <li><?php echo htmlspecialchars($msg); ?></li>
                <?php endforeach; ?>
            </ul>
        </div>
        <?php endif; ?>
        
        <?php if (!empty($errors)): ?>
        <div class="error">
            <strong>⚠ Errors occurred:</strong>
            <ul>
                <?php foreach ($errors as $error): ?>
                <li><?php echo htmlspecialchars($error); ?></li>
                <?php endforeach; ?>
            </ul>
        </div>
        <?php endif; ?>
        
        <a href="<?php echo BASE_URL; ?>/modules/dfc/list.php" class="btn">Go to DFC Module</a>
    </div>
</body>
</html>

