#!/usr/bin/env php
<?php

declare(strict_types=1);

function loadEnv(string $filePath): void
{
	if (!file_exists($filePath)) {
		throw new Exception("The .env file was not found. Please create one by copying the .env.example file and renaming it to .env.");
	}

	$lines = file($filePath, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
	foreach ($lines as $line) {
		if (strpos(trim($line), '#') === 0) {
			continue; // Skip comments
		}

		list($name, $value) = explode('=', $line, 2);
		$name = trim($name);
		$value = trim($value);

		putenv("$name=$value");
		$_ENV[$name] = $value;
		$_SERVER[$name] = $value;
	}
}

function getDbConnection(string $host, string $user, string $password, string $database): PDO
{
	return new PDO("mysql:host=$host;dbname=$database;charset=utf8mb4", $user, $password, [
		PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
		PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
	]);
}

function getCreateStatements(PDO $pdo, string $database, string $type): array
{
	$query = match ($type) {
		'tables' => "SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema = ? AND TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME NOT REGEXP '_(mv|pv)$'",
		'views' => "SELECT TABLE_NAME FROM information_schema.views WHERE table_schema = ?",
		'procedures' => "SELECT ROUTINE_NAME FROM information_schema.routines WHERE routine_schema = ? AND ROUTINE_TYPE = 'PROCEDURE'",
		'functions' => "SELECT ROUTINE_NAME FROM information_schema.routines WHERE routine_schema = ? AND ROUTINE_TYPE = 'FUNCTION'",
		default => throw new InvalidArgumentException("Unknown type: $type"),
	};


	$stmt = $pdo->prepare($query);
	$stmt->execute([$database]);

	$items = [];
	while ($row = $stmt->fetch()) {
		$name = in_array($type, ['tables', 'views']) ? $row['TABLE_NAME'] : $row['ROUTINE_NAME'];

		// Unterschiedliche SQL-Befehle für verschiedene Typen
		switch ($type) {
			case 'tables':
				$createQuery = "SHOW CREATE TABLE `$name`";
				$column = 'Create Table';
				break;
			case 'views':
				$createQuery = "SHOW CREATE VIEW `$name`";
				$column = 'Create View';
				break;
			case 'procedures':
				$createQuery = "SHOW CREATE PROCEDURE `$name`";
				$column = 'Create Procedure';
				break;
			case 'functions':
				$createQuery = "SHOW CREATE FUNCTION `$name`";
				$column = 'Create Function';
				break;
			default:
				throw new Exception("Unbekannter Typ: $type");
		}

		if ($createQuery !== null) {
			$stmtCreate = $pdo->query($createQuery);
			$result = $stmtCreate->fetch(PDO::FETCH_ASSOC);
			$createStmt = $result[$column];

			// Entferne AUTO_INCREMENT=X (egal, welche Zahl X hat)
			$createStmt = preg_replace('/AUTO_INCREMENT=\d+\s*/i', '', $createStmt);

			// Entferne den DEFINER, z. B. DEFINER=`dbadmin`@`%` oder DEFINER=`root`@`192.168.%.%`
			$createStmt = preg_replace('/DEFINER=`[^`]+`@`[^`]+`\s*/i', '', $createStmt);

			$items[$name] = $createStmt;
		}
	}

	return $items;
}

function compareStructures(string $label, array $dev, array $prod): bool
{
	$diffs = [];
	$allKeys = array_unique(array_merge(array_keys($dev), array_keys($prod)));

	foreach ($allKeys as $key) {
		$devStmt = $dev[$key] ?? null;
		$prodStmt = $prod[$key] ?? null;

		#echo $devStmt.PHP_EOL;
		#echo $prodStmt.PHP_EOL;

		if ($devStmt !== $prodStmt) {
			$diffs[] = "\033[31m❌ Difference in $label: $key\033[0m"; // Rote Markierung

			if ($devStmt === '') {
				$diffs[] = "\033[31m❌ Not found in DEV\033[0m";
			} elseif ($prodStmt === '') {
				$diffs[] = "\033[31m❌ Not found in PROD\033[0m";
			} else {
				$diffs[] = generateDiff($devStmt, $prodStmt);
			}
		}
	}

	if (count($diffs) > 0) {
		echo implode("\n", $diffs) . "\n";
		return false;
	}

	echo "\033[32m✅ No differences in $label\033[0m\n"; // Grün für OK
	return true;
}

function generateDiff(string $devStmt, string $prodStmt, int $context = 3): string
{
	$devLines = explode("\n", $devStmt);
	$prodLines = explode("\n", $prodStmt);
	$numDev = count($devLines);
	$numProd = count($prodLines);
	$maxLines = max($numDev, $numProd);

	// Ermitteln der Indizes, an denen sich die Zeilen unterscheiden
	$diffIndices = [];
	for ($i = 0; $i < $maxLines; $i++) {
		$devLine = $devLines[$i] ?? '';
		$prodLine = $prodLines[$i] ?? '';
		if ($devLine !== $prodLine) {
			$diffIndices[] = $i;
		}
	}

	// Falls es keine Unterschiede gibt, sofort zurückgeben
	if (empty($diffIndices)) {
		return "Keine Unterschiede gefunden.";
	}

	// Bestimme Blöcke (Bereiche) mit Kontext, in denen Unterschiede enthalten sind.
	$blocks = [];
	$blockStart = null;
	$blockEnd = null;
	foreach ($diffIndices as $diffIndex) {
		if ($blockStart === null) {
			$blockStart = max(0, $diffIndex - $context);
			$blockEnd = min($maxLines - 1, $diffIndex + $context);
		} else {
			// Liegt der nächste Unterschied im Kontext des aktuellen Blocks?
			if ($diffIndex - $context <= $blockEnd + 1) {
				$blockEnd = min($maxLines - 1, $diffIndex + $context);
			} else {
				$blocks[] = [$blockStart, $blockEnd];
				$blockStart = max(0, $diffIndex - $context);
				$blockEnd = min($maxLines - 1, $diffIndex + $context);
			}
		}
	}
	if ($blockStart !== null) {
		$blocks[] = [$blockStart, $blockEnd];
	}

	// Baue den Ausgabe-String anhand der Blöcke
	$outputLines = [];
	$prevBlockEnd = -1;
	foreach ($blocks as $block) {
		list($start, $end) = $block;
		// Bei Lücken zwischen Blöcken einen Trenner einfügen
		if ($start > $prevBlockEnd + 1) {
			$outputLines[] = "...";
		}
		for ($i = $start; $i <= $end; $i++) {
			$devLine = $devLines[$i] ?? '';
			$prodLine = $prodLines[$i] ?? '';
			if ($devLine === $prodLine) {
				// Unveränderte Zeile ohne Farbcodierung
				$outputLines[] = "  $devLine";
			} else {
				// Unterschiede farblich hervorheben:
				// - Rot für die Zeile aus der DEV-Definition
				// - Grün für die Zeile aus der PROD-Definition
				$outputLines[] = "\033[31m- $devLine\033[0m";
				$outputLines[] = "\033[32m+ $prodLine\033[0m";
			}
		}
		$prevBlockEnd = $end;
	}

	return implode("\n", $outputLines);
}


// Hauptskript
try {
	loadEnv(__DIR__ . '/.env');

	$devPdo = getDbConnection(getenv('DEV_DB_HOST'), getenv('DEV_DB_USER'), getenv('DEV_DB_PASS'), getenv('DEV_DB_NAME'));
	$prodPdo = getDbConnection(getenv('PROD_DB_HOST'), getenv('PROD_DB_USER'), getenv('PROD_DB_PASS'), getenv('PROD_DB_NAME'));

	$checks = [
		'tables' => 'Tables',
		'views' => 'Views',
		'procedures' => 'Procedures',
		'functions' => 'Functions'
	];

	$errors = false;

	foreach ($checks as $type => $label) {
		$devStruct = getCreateStatements($devPdo, getenv('DEV_DB_NAME'), $type);
		$prodStruct = getCreateStatements($prodPdo, getenv('PROD_DB_NAME'), $type);

		if (!compareStructures($label, $devStruct, $prodStruct)) {
			$errors = true;
		}
	}

	if ($errors) {
		exit(1); // Fehlerstatus für Git Hook
	}

	exit(0); // Erfolgreich
} catch (Exception $e) {
	fwrite(STDERR, "Error: " . $e->getMessage() . "\n");
	exit(1);
}

#!/bin/bash
php path/to/compare_db.php
if [ $? -ne 0 ]; then
    echo "❌ Database structure mismatch detected. Push aborted."
    exit 1
fi
exit 0

chmod +x .git/hooks/pre-push

# Entwicklungsdatenbank (Localhost)
DEV_DB_HOST=localhost
DEV_DB_USER=root
DEV_DB_PASS=root
DEV_DB_NAME=dev_database

# Produktionsdatenbank (Remote)
PROD_DB_HOST=remote.host
PROD_DB_USER=prod_user
PROD_DB_PASS=prod_pass
PROD_DB_NAME=prod_database