<?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;
		}
}