<?php

$source_database = 'erpels3edygt4it_2024';
$target_database = 'erpels3edygt4it_els3edy';

// Get the list of tables in the source database
$tables = R::getAll("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" . $source_database . "'");

foreach ($tables as $table) {
    $tableName = $table['TABLE_NAME'];

    // Check if primary key exists before recreating it
    $primaryKeyExists = R::getCell("SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = '" . $target_database . "' AND TABLE_NAME = ? AND INDEX_NAME = 'PRIMARY'", [$tableName]) > 0;
    if (!$primaryKeyExists) {
        // Recreate the primary key in the target database
        $primaryKeys = R::getAll("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = '" . $source_database . "' AND TABLE_NAME = ? AND CONSTRAINT_NAME = 'PRIMARY'", [$tableName]);
        foreach ($primaryKeys as $primaryKey) {
            $columnName = $primaryKey['COLUMN_NAME'];
            //R::exec("ALTER TABLE $tableName ADD PRIMARY KEY ($columnName)");
            R::exec("ALTER TABLE $tableName CHANGE $columnName $columnName INT(11) NOT NULL AUTO_INCREMENT, add PRIMARY KEY ($columnName)");
        }
    }

    // Check if foreign key exists before recreating it
    $foreignKeys = R::getAll("SELECT COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = '" . $source_database . "' AND TABLE_NAME = ? AND REFERENCED_TABLE_NAME IS NOT NULL", [$tableName]);
    foreach ($foreignKeys as $foreignKey) {
        $columnName = $foreignKey['COLUMN_NAME'];
        $constraintName = $foreignKey['CONSTRAINT_NAME'];
        $referencedTableName = $foreignKey['REFERENCED_TABLE_NAME'];
        $referencedColumnName = $foreignKey['REFERENCED_COLUMN_NAME'];

        $foreignKeyExists = R::getCell("SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = '" . $target_database . "' AND TABLE_NAME = ? AND CONSTRAINT_NAME = ?", [$tableName, $constraintName]) > 0;
        if (!$foreignKeyExists) {
            // Recreate the foreign key in the target database
            R::exec("ALTER TABLE $tableName ADD CONSTRAINT $constraintName FOREIGN KEY ($columnName) REFERENCES $referencedTableName ($referencedColumnName)");
        }
    }

    // Check if index exists before recreating it
    $indexes = R::getAll("SELECT INDEX_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = '" . $source_database . "' AND TABLE_NAME = ?", [$tableName]);
    foreach ($indexes as $index) {
        $indexName = $index['INDEX_NAME'];
        $columnName = $index['COLUMN_NAME'];

        $indexExists = R::getCell("SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = '" . $target_database . "' AND TABLE_NAME = ? AND INDEX_NAME = ?", [$tableName, $indexName]) > 0;
        if (!$indexExists) {
            // Recreate the index in the target database
            R::exec("CREATE INDEX $indexName ON $tableName ($columnName)");
        }
    }
}
