<?php
set_time_limit(0);
ini_set('memory_limit', '1024M');

try {

    // SOURCE DATABASE
    $source = new mysqli("localhost", "u926327694_test_abhpu", "SmartSchool@987", "u926327694_test_abhpu");

    // TARGET DATABASE
    $target = new mysqli("localhost", "u926327694_ABHPU_clg", "SmartSchool@987", "u926327694_ABHPU_clg");

    if ($source->connect_error) {
        die("Source DB Connection Failed: " . $source->connect_error);
    }

    if ($target->connect_error) {
        die("Target DB Connection Failed: " . $target->connect_error);
    }

    $target->query("SET FOREIGN_KEY_CHECKS=0");

    echo "<h2>Restore Started...</h2>";

    /*
    |--------------------------------------------------------------------------
    | TABLES TO RESTORE
    |--------------------------------------------------------------------------
    */

    $tables = [
        'sections',
        'class_sections',
        'students',
        'student_session',
        'student_fees_master',
        'student_fees_deposite',
        'student_transport_fees'
    ];

    foreach ($tables as $table) {

        echo "<hr>";
        echo "<h3>Processing Table: {$table}</h3>";

        // Fetch all records from source
        $result = $source->query("SELECT * FROM {$table}");

        if (!$result) {
            echo "Error reading source table {$table}: " . $source->error . "<br>";
            continue;
        }

        $inserted = 0;
        $skipped = 0;
        $errors = 0;

        while ($row = $result->fetch_assoc()) {

            $id = (int)$row['id'];

            // Check if already exists
            $check = $target->query("SELECT id FROM {$table} WHERE id = {$id} LIMIT 1");

            if ($check && $check->num_rows > 0) {
                $skipped++;
                continue;
            }

            // Build INSERT query dynamically
            $columns = [];
            $values = [];

            foreach ($row as $column => $value) {

                $columns[] = "`{$column}`";

                if (is_null($value)) {
                    $values[] = "NULL";
                } else {
                    $values[] = "'" . $target->real_escape_string($value) . "'";
                }
            }

            $columns_sql = implode(",", $columns);
            $values_sql = implode(",", $values);

            $insert_sql = "
                INSERT INTO {$table}
                ({$columns_sql})
                VALUES
                ({$values_sql})
            ";

            if ($target->query($insert_sql)) {
                $inserted++;
            } else {
                $errors++;

                echo "<div style='color:red'>";
                echo "Error inserting ID {$id} in {$table}: ";
                echo $target->error;
                echo "</div>";
            }
        }

        echo "<b>Inserted:</b> {$inserted}<br>";
        echo "<b>Skipped:</b> {$skipped}<br>";
        echo "<b>Errors:</b> {$errors}<br>";
    }

    $target->query("SET FOREIGN_KEY_CHECKS=1");

    echo "<hr>";
    echo "<h2 style='color:green'>Restore Completed Successfully</h2>";

} catch (Exception $e) {

    echo "<h2 style='color:red'>Error:</h2>";
    echo $e->getMessage();
}
?>