<?php

namespace Wizard\MagicForger\Helpers;

use Illuminate\Support\Facades\DB;

/**
 * Class RelationshipNavigator
 *
 * This class is responsible for navigating database table relationships, specifically 
 * identifying and categorizing 'belongsTo', 'hasMany', and 'hasManyThrough' relationships.
 */
class RelationshipNavigator
{
    /**
     * Handles the retrieval and display of table relationships.
     *
     * @return void
     */
    public static function handle()
    {
        $tables = DB::select('SHOW TABLES');
        $tableNames = array_map(fn($table) => current((array) $table), $tables);

        foreach ($tableNames as $table) {
            echo "Table: $table \n";

            $relations = self::getRelations($table);
            echo "Relationships: \n";

            foreach ($relations as $relation => $relatedTables) {
                echo "$relation: \n";
                foreach ($relatedTables as $relatedTable) {
                    echo "\t";
                    foreach ($relatedTable as $key => $value) {
                        if (is_array($value)) {
                            echo "\n\t\t" . implode("\n\t\t", array_map(fn($k, $v) => "$k: $v", array_keys($value), $value));
                        } else {
                            echo "$key: $value ";
                        }
                    }
                    echo "\n";
                }
            }
            echo "\n --- \n";
        }
    }

    /**
     * Retrieves relationships of a specific table.
     *
     * @param string $table The table name.
     * @return array An array containing 'belongsTo', 'hasMany', and 'hasManyThrough' relations.
     */
    public static function getRelations($table)
    {
        $relations = [
            'belongsTo' => [],
            'hasMany' => [],
            'hasManyThrough' => [],
        ];

        $foreignKeys = DB::select("SHOW KEYS FROM $table WHERE Key_name != 'PRIMARY'");
        $referencedTables = self::getAllReferencedTables($table);

        // Determine 'belongsTo' Relationships
        foreach ($foreignKeys as $fk) {
            $column = $fk->Column_name;

            // Skip certain columns
            if (in_array($column, ['created_by', 'updated_by'])) {
                continue;
            }

            $referencedTable = $referencedTables[$column] ?? null;

            if ($referencedTable) {
                $relations['belongsTo'][] = ['column' => $column, 'table' => $referencedTable->REFERENCED_TABLE_NAME];
            }
        }

        // Determine 'hasMany' Relationships
        if ($reverseRelation = self::findReverseRelation($table)) {
            foreach ($reverseRelation as $relatedTable) {
                $relations['hasMany'][] = $relatedTable;
            }
        }

        // Determine 'hasManyThrough' Relationships
        if ($hasManyThroughRelations = self::findHasManyThroughRelations($table)) {
            foreach ($hasManyThroughRelations as $relatedTable) {
                $relations['hasManyThrough'][] = $relatedTable;
            }
        }

        return $relations;
    }

    /**
     * Retrieves all referenced tables for a given table.
     *
     * @param string $table The table name.
     * @return array|null An associative array of referenced tables, keyed by column name.
     */
    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 REFERENCED_TABLE_NAME IS NOT NULL
            AND TABLE_NAME = ?
        ", [$table]);

        return self::re_key_array($results, 'COLUMN_NAME') ?: null;
    }

    /**
     * Finds 'hasMany' inverse relationships for a given table.
     *
     * @param string $table The table name.
     * @return array|null An array of related tables with column names.
     */
    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 = ? 
            AND REFERENCED_COLUMN_NAME = 'id'
            AND COLUMN_NAME NOT IN ('created_by', 'updated_by')
        ", [$table]);

        return array_map(fn($rel) => ['table' => $rel->TABLE_NAME, 'column' => $rel->COLUMN_NAME], $relations) ?: null;
    }
    
    /**
     * Finds 'hasManyThrough' relationships for a given table.
     *
     * @param string $table The table name.
     * @return array An array of 'hasManyThrough' relationships.
     */
    public static function findHasManyThroughRelations($table)
    {
        $relations = [];
        $intermediaryTables = self::findReverseRelation($table);

        if ($intermediaryTables !== null) {
            foreach ($intermediaryTables as $intermediary) {
                if ($isPivot = self::isPivot($intermediary['table'])) {
                    $isPivot = current($isPivot);

                    $potentialTables = array_keys($isPivot['tables']);
                    $externalTable = $potentialTables[0] === $table ? $isPivot['tables'][$potentialTables[1]] : $isPivot['tables'][$potentialTables[0]];
                    $internalTable = $potentialTables[0] === $table ? $isPivot['tables'][$potentialTables[0]] : $isPivot['tables'][$potentialTables[1]];

                    $hasManyThrough = [
                        'table' => $externalTable['table_name'],
                        'through' => [
                            'table' => $isPivot['table'],
                            'external_column' => $externalTable['column'],
                            'internal_column' => $internalTable['column'],
                        ],
                    ];
                    $relations[] = $hasManyThrough;
                }
            }
        }

        return $relations;
    }

    /**
     * Determines if a table is a pivot table.
     *
     * @param string $table The table name.
     * @return array|null An array with pivot details or null if not a pivot.
     */
    public static function isPivot($table)
    {
        $relations = [];
        $pivotTables = DB::select("
            SELECT TABLE_NAME, TABLE_COMMENT
            FROM INFORMATION_SCHEMA.TABLES 
            WHERE TABLE_SCHEMA = DATABASE() 
            AND TABLE_NAME = ?
            AND TABLE_COMMENT != ''
        ", [$table]);

        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) {
                $references = self::getAllReferencedTables($table);
                $references = self::re_key_array($references, 'REFERENCED_TABLE_NAME');

                foreach ($pivots as $key => $value) {
                    if ($refData = ($references[$value] ?? null)) {
                        $tables[$value] = [
                            'table_name' => $value,
                            'column' => $refData->COLUMN_NAME,
                        ];
                    }
                }
            }
            $relations[] = ['table' => $ref->TABLE_NAME, 'tables' => $tables];
        }

        return !empty($relations) ? $relations : null;
    }

    /**
     * Re-keys an array of objects using a specific object's property.
     *
     * @param array $oldArray The original array of objects.
     * @param string $key The key to re-index by.
     * @return array The re-keyed array.
     */
    public static function re_key_array($oldArray, $key)
    {
        $newArray = [];
        if (count($oldArray) > 0) {
            foreach ($oldArray as $array) {
                $newArray[$array->$key] = $array;
            }
        }
        return $newArray;
    }
}