<?php
/**
 * Safe OBGYN Dummy Data Seeder - Complete Version
 * This script safely inserts ALL dummy data from obgyn_dummy_data.sql, ignoring duplicates and invalid entries
 */

session_start();
require_once '../config/database.php';

// Simple admin check
if (!isset($_SESSION['user_id'])) {
    die('<h1>Access Denied</h1><p>Please <a href="../login.php">login</a> first.</p>');
}

try {
    $db = getDB();
    $stmt = $db->prepare("SELECT role_id FROM users WHERE id = ?");
    $stmt->execute([$_SESSION['user_id']]);
    $user = $stmt->fetch();

    if (!$user || ($user['role_id'] != 1 && $user['role_id'] != 2)) {
        die('<h1>Access Denied</h1><p>Admin access required.</p>');
    }
} catch (Exception $e) {
    die('<h1>Database Error</h1><p>' . $e->getMessage() . '</p>');
}

?>
<!DOCTYPE html>
<html>
<head>
    <title>Safe OBGYN Dummy Data Seeder - Complete</title>
    <link href='https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css' rel='stylesheet'>
    <style>
        .progress { height: 20px; }
        .log-area { max-height: 400px; overflow-y: auto; background: #f8f9fa; padding: 10px; border: 1px solid #dee2e6; font-family: monospace; font-size: 12px; }
    </style>
</head>
<body>
<div class='container mt-5'>
    <h1>Safe OBGYN Dummy Data Seeder - Complete Version</h1>
    <p>This script will safely insert ALL dummy data from <code>obgyn_dummy_data.sql</code>, skipping duplicates and invalid entries.</p>

    <div class="alert alert-warning">
        <strong>Warning:</strong> This will process over 5,000 lines of SQL. It may take several minutes to complete.
    </div>

    <?php
    if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
        echo '<form method="post">';
        echo '<button type="submit" class="btn btn-primary btn-lg">Start Complete Seeding</button>';
        echo '</form>';
    } else {
        try {
            $db = getDB();
            $db->beginTransaction();

            $successCount = 0;
            $skipCount = 0;
            $errorCount = 0;
            $errors = [];

            echo "<div class='progress mb-3'><div class='progress-bar' role='progressbar' style='width: 0%'></div></div>";
            echo "<div class='log-area' id='logArea'>Starting data insertion...</div>";
            flush();

            // Function to safely execute INSERT IGNORE
            function safeExecute($db, $sql, &$successCount, &$skipCount, &$errorCount, &$errors, $description = "") {
                try {
                    $stmt = $db->prepare($sql);
                    $result = $stmt->execute();

                    if ($stmt->rowCount() > 0) {
                        $successCount++;
                        echo "<div class='text-success'>✓ $description</div>";
                    } else {
                        $skipCount++;
                        echo "<div class='text-warning'>⚠ Skipped: $description</div>";
                    }
                } catch (Exception $e) {
                    $errorCount++;
                    $errors[] = "$description: " . $e->getMessage();
                    echo "<div class='text-danger'>✗ Error in $description: " . $e->getMessage() . "</div>";
                }
                flush();
            }

            // Read the SQL file
            $sqlFile = __DIR__ . '/obgyn_dummy_data.sql';
            if (!file_exists($sqlFile)) {
                throw new Exception("SQL file not found: $sqlFile");
            }

            $sqlContent = file_get_contents($sqlFile);

            // Split into individual statements (basic parsing)
            $statements = array_filter(array_map('trim', explode(';', $sqlContent)));
            $totalStatements = count($statements);

            echo "<div class='alert alert-info'>Found $totalStatements SQL statements to process.</div>";

            $processed = 0;
            foreach ($statements as $statement) {
                if (empty($statement) || strpos($statement, '--') === 0) {
                    continue; // Skip comments and empty statements
                }

                // Skip transaction control statements
                if (preg_match('/^(SET|START|COMMIT|ROLLBACK)/i', $statement)) {
                    continue;
                }

                $processed++;

                // Update progress
                $progress = round(($processed / $totalStatements) * 100);
                echo "<script>document.querySelector('.progress-bar').style.width = '$progress%';</script>";
                flush();

                // Convert INSERT INTO to INSERT IGNORE for safety
                if (preg_match('/^INSERT INTO/i', $statement) && !preg_match('/INSERT IGNORE/i', $statement)) {
                    $statement = preg_replace('/^INSERT INTO/i', 'INSERT IGNORE INTO', $statement);
                }

                // Extract table name for description
                if (preg_match('/INSERT IGNORE INTO (\w+)/i', $statement, $matches)) {
                    $table = $matches[1];
                    $description = "Insert into $table (statement $processed/$totalStatements)";
                } else {
                    $description = "SQL statement $processed/$totalStatements";
                }

                safeExecute($db, $statement, $successCount, $skipCount, $errorCount, $errors, $description);
            }

            $db->commit();

            echo "<script>document.querySelector('.progress-bar').style.width = '100%';</script>";
            echo "<div class='alert alert-success mt-3'>";
            echo "<h3>✓ Complete Seeding Finished!</h3>";
            echo "<p><strong>Records inserted:</strong> $successCount</p>";
            echo "<p><strong>Records skipped (duplicates):</strong> $skipCount</p>";
            echo "<p><strong>Statements processed:</strong> $processed</p>";
            if ($errorCount > 0) {
                echo "<p><strong>Errors encountered:</strong> $errorCount</p>";
                echo "<details><summary>View Errors</summary><ul class='text-danger'>";
                foreach (array_slice($errors, 0, 50) as $error) { // Show first 50 errors
                    echo "<li>$error</li>";
                }
                if (count($errors) > 50) {
                    echo "<li>... and " . (count($errors) - 50) . " more errors</li>";
                }
                echo "</ul></details>";
            }
            echo "</div>";

        } catch (Exception $e) {
            $db->rollBack();
            echo "<div class='alert alert-danger'>";
            echo "<h3>✗ Transaction Failed</h3>";
            echo "<p>" . $e->getMessage() . "</p>";
            echo "</div>";
        }
    }
    ?>

    <a href="../admin/dashboard.php" class="btn btn-primary">Back to Dashboard</a>
</div>

<script>
// Auto-scroll log area
const logArea = document.getElementById('logArea');
const observer = new MutationObserver(() => {
    logArea.scrollTop = logArea.scrollHeight;
});
observer.observe(logArea, { childList: true });
</script>
</body>
</html>