--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src/Helpers/RelationshipNavigator.php	Fri Apr 11 20:50:20 2025 -0400
@@ -0,0 +1,205 @@
+<?php
+
+namespace Wizard\MagicForger\Helpers;
+
+use Illuminate\Support\Facades\DB;
+
+class RelationshipNavigator
+{
+    public static function handle()
+    {
+        $tables = DB::select('SHOW TABLES');
+        $tableNames = array_map(function ($table) {
+            return current((array) $table);
+        }, $tables);
+
+        foreach ($tableNames as $table) {
+            echo "Table: $table \n";
+
+            $relations = self::getRelations($table);
+            echo "Relationships: \n";
+            foreach ($relations as $relation => $related_tables) {
+                echo $relation.": \n";
+                foreach ($related_tables as $related_table) {
+                    echo "\t";
+                    foreach ($related_table as $key => $value) {
+                        echo "$key : ";
+                        if (is_array($value)) {
+                            foreach ($related_table as $key => $value) {
+                                echo "\n\t\t";
+                                echo "$key: $value";
+                            }
+                        } else {
+                            echo $value.' ';
+                        }
+                    }
+                    echo "\n";
+                }
+            }
+            echo "\n --- \n";
+        }
+    }
+
+    public static function getRelations($table)
+    {
+        $relations = [
+            'belongsTo' => [],
+            'hasMany' => [],
+            'hasManyThrough' => [],
+        ];
+
+        $foreignKeys = DB::select("SHOW KEYS FROM $table WHERE Key_name != 'PRIMARY'");
+        $referencedTables = self::getAllReferencedTables($table);
+
+        // BelongsTo Relationships
+        foreach ($foreignKeys as $fk) {
+            $column = $fk->Column_name;
+
+            // skip created and updated by
+            if (in_array($column, ['created_by', 'updated_by'])) {
+                continue;
+            }
+
+            $referencedTable = $referencedTables[$column] ?? null;
+
+            if ($referencedTable) {
+                $relations['belongsTo'][] = ['column' => $column, 'table' => $referencedTable->REFERENCED_TABLE_NAME];
+            }
+
+        }
+
+        // HasMany Relationships
+        if ($reverseRelation = self::findReverseRelation($table)) {
+            foreach ($reverseRelation as $relatedTable) {
+                $relations['hasMany'][] = $relatedTable;
+            }
+        }
+
+        // HasManyThrough Relationships
+        if ($hasManyThroughRelations = self::findHasManyThroughRelations($table)) {
+            foreach ($hasManyThroughRelations as $relatedTable) {
+                $relations['hasManyThrough'][] = $relatedTable;
+            }
+        }
+
+        return $relations;
+    }
+
+    public static function getAllReferencedTables($table)
+    {
+        $results = DB::select("
+            SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME 
+            FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
+            WHERE TABLE_SCHEMA = DATABASE() 
+            AND TABLE_NAME = '$table' 
+        ");
+
+        $tables = self::re_key_array($results, 'COLUMN_NAME');
+
+        return (count($tables) > 0) ? $tables : null;
+    }
+
+    public static function findReverseRelation($table)
+    {
+        $relations = DB::select("
+            SELECT TABLE_NAME, COLUMN_NAME
+            FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
+            WHERE TABLE_SCHEMA = DATABASE() 
+            AND REFERENCED_TABLE_NAME = '$table' 
+            AND REFERENCED_COLUMN_NAME = 'id'
+					  AND COLUMN_NAME != 'created_by'
+					  AND COLUMN_NAME != 'updated_by'
+        ");
+
+        $relatedTables = array_map(function ($rel) {
+            return ['table' => $rel->TABLE_NAME, 'column' => $rel->COLUMN_NAME];
+        }, $relations);
+
+        return $relatedTables ? $relatedTables : null;
+    }
+
+    public static function findHasManyThroughRelations($table)
+    {
+        $relations = [];
+
+        // Find potential intermediary tables
+        $intermediaryTables = self::findReverseRelation($table);
+
+        if (! is_null($intermediaryTables)) {
+
+            foreach ($intermediaryTables as $intermediary) {
+
+                if ($is_pivot = self::isPivot($intermediary['table'])) {
+                    $is_pivot = current($is_pivot);
+
+                    // reformat the table based on the current and external
+										$potential_tables = array_keys($is_pivot['tables']);
+										$external_table = $potential_tables[0] == $table ? $is_pivot['tables'][$potential_tables[1]] : $is_pivot['tables'][$potential_tables[0]];
+										$internal_table = $potential_tables[0] == $table ? $is_pivot['tables'][$potential_tables[0]] : $is_pivot['tables'][$potential_tables[1]];
+
+										$hasManyThrough = [
+											'table' => $external_table['table_name'],
+											'through' => [
+												'table' => $is_pivot['table'],
+												'external_column' => $external_table['column'],
+												'internal_column' => $internal_table['column']
+											]
+                    ];
+                    $relations[] = $hasManyThrough;
+                }
+            }
+
+        }
+
+        return $relations;
+    }
+
+    public static function isPivot($table)
+    {
+
+        $relations = [];
+        // TODO: alsot get the columns that are relevant
+        $pivotTables = DB::select("
+                SELECT TABLE_NAME, TABLE_COMMENT
+                FROM INFORMATION_SCHEMA.TABLES 
+                WHERE TABLE_SCHEMA = DATABASE() 
+                AND TABLE_NAME = '{$table}'
+								AND TABLE_COMMENT != ''
+            ");
+
+				if(!is_null($pivotTables) && count($pivotTables) > 0) {
+					$ref = current($pivotTables);
+
+					$pivots = json_decode(str_replace('PIVOT:', '', $ref->TABLE_COMMENT), true);
+
+					$tables = [];
+        	if (count($pivots) > 0) {
+							//re-key array
+        	    $references = self::getAllReferencedTables($table);
+							$references = self::re_key_array($references, 'REFERENCED_TABLE_NAME');
+
+							foreach($pivots as $key => $value) {
+								if($ref_data = ($references[$value] ?? null)) {
+									$tables[$value] = [
+										'table_name' => $value,
+										'column' => $ref_data->COLUMN_NAME
+									];
+								}
+							}
+        	}
+        	$relations[] = ['table' => $ref->TABLE_NAME, 'tables' => $tables];
+				}
+
+        return $relations != [] ? $relations : null;
+    }
+
+		public static function re_key_array($old_array, $key) {
+			$new_array = [];
+      if (count($old_array) > 0) {
+          foreach ($old_array as $array) {
+              $new_array[$array->$key] = $array;
+          }
+      }
+			return $new_array;
+		}
+}