<?php
if (!isset($connection)) {
    require_once dirname(__DIR__) . "/public/connection.php";
}

$tables_to_update = ['results1', 'students', 'fee_payment', 'fee_structure', 'behavioral', 'student_attendance'];

foreach ($tables_to_update as $table) {
    // Check if table exists
    $table_check = mysqli_query($connection, "SHOW TABLES LIKE '$table'");
    if ($table_check && mysqli_num_rows($table_check) > 0) {
        // Check if column exists
        $check = mysqli_query($connection, "SHOW COLUMNS FROM `$table` LIKE 'is_synced'");
        if ($check && mysqli_num_rows($check) == 0) {
            $q = "ALTER TABLE `$table` ADD COLUMN `is_synced` TINYINT(1) DEFAULT 0";
            if (mysqli_query($connection, $q)) {
                echo "Added is_synced to $table\n";
            } else {
                echo "Error updating $table: " . mysqli_error($connection) . "\n";
            }
        } else {
            echo "is_synced already exists in $table\n";
        }
    } else {
        echo "Table $table does not exist in database, skipped.\n";
    }
}

// Add sync settings to administratives if not exists
$admin_table_check = mysqli_query($connection, "SHOW TABLES LIKE 'administratives'");
if ($admin_table_check && mysqli_num_rows($admin_table_check) > 0) {
    $check_url = mysqli_query($connection, "SHOW COLUMNS FROM `administratives` LIKE 'remote_sync_url'");
    if ($check_url && mysqli_num_rows($check_url) == 0) {
        mysqli_query($connection, "ALTER TABLE `administratives` ADD COLUMN `remote_sync_url` VARCHAR(255) DEFAULT ''");
        echo "Added remote_sync_url to administratives\n";
    } else {
        echo "remote_sync_url already exists in administratives\n";
    }

    $check_key = mysqli_query($connection, "SHOW COLUMNS FROM `administratives` LIKE 'sync_api_key'");
    if ($check_key && mysqli_num_rows($check_key) == 0) {
        mysqli_query($connection, "ALTER TABLE `administratives` ADD COLUMN `sync_api_key` VARCHAR(100) DEFAULT ''");
        echo "Added sync_api_key to administratives\n";
    } else {
        echo "sync_api_key already exists in administratives\n";
    }
}
echo "Database sync update completed successfully.\n";
?>
