<?php
	session_start();
	include("ini.php");
	if(!isset($_SESSION["Db"]))
	{
		echo "<html><meta http-equiv='refresh' content='1; URL=Index.php'><head><title>Automatische Weiterleitung erfolgt</title></head><body>keine Datenbank ausgewählt!</body></html>";
		return;
	}
	$db = $_SESSION["Db"];

	$table = (isset($_GET["Table"])) ? $_GET["Table"]: "";
	if (isset($_GET["CreateTable"])) $table = $_GET["CreateTable"];
	if($table != "") 
		$_SESSION["Table"] = $table;
	else
		$table = $_SESSION["Table"];

	if(isset($_GET["AddField"]))
	{	
		$dbcon = mysqli_connect (SERVER, USER, PASSWORD, $db);
		if (mysqli_connect_errno()==0)
		{
			$sql = "SHOW COLUMNS FROM ".$table;
			if($cols = mysqli_query ($dbcon, $sql))
				$sql = "ALTER TABLE ".$table." ADD ".$_POST["FName"]." ".$_POST["FType"]." NOT NULL;";
			else
				$sql = "CREATE TABLE IF NOT EXISTS ".$table." ( ".$_POST["FName"]." ".$_POST["FType"]." NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
			mysqli_query ($dbcon, $sql);
		}
		mysqli_close($dbcon);
	}

	if(isset($_GET["DelField"]))
	{	
		$delField = $_GET["DelField"];
		$dbcon = mysqli_connect (SERVER, USER, PASSWORD, $db);
		if (mysqli_connect_errno()==0)
		{
			$sql = "ALTER TABLE ".$table." DROP ".$delField;
			mysqli_query ($dbcon, $sql);
		}
		mysqli_close($dbcon);
	}
	
	if(isset($_GET["DelFK"]))
	{	
		$constraint = $_GET["DelFK"];
		$dbcon = mysqli_connect (SERVER, USER, PASSWORD, $db);
		if (mysqli_connect_errno()==0)
		{
			$sql = "ALTER TABLE ".$table." DROP FOREIGN KEY ".$constraint;
			mysqli_query ($dbcon, $sql);
		}
		mysqli_close($dbcon);
	}

	function ChangePrim($db, $table)
	{
		$dbcon = mysqli_connect (SERVER, USER, PASSWORD, $db);
		if (mysqli_connect_errno()==0)
		{
			$prim = "";
			$oldPrim = false;
			$sql = "SHOW COLUMNS FROM ".$table;
			if($cols = mysqli_query ($dbcon, $sql))
			{
				$c = 0;
				while($col = mysqli_fetch_array($cols))
				{
					if($_POST["C".$c]) $prim = $prim.$col[0].", ";
					if($col[3]=="PRI") $oldPrim = true;
					$c++;
				}
			}
			if($oldPrim)
			{
				$sql = "ALTER TABLE ".$table." DROP PRIMARY KEY";
				mysqli_query ($dbcon, $sql);
			}
			if($prim != "")
			{
				$prim = substr($prim, 0, -2);
				$sql = "ALTER TABLE ".$table." ADD PRIMARY KEY (".$prim.")";
				mysqli_query ($dbcon, $sql);
			}
		}
		mysqli_close($dbcon);
	}

	function MakeForeignKey($db, $table)
	{
		$dbcon = mysqli_connect (SERVER, USER, PASSWORD, $db);
		if (mysqli_connect_errno()==0)
		{
			$fk = "";
			$sql = "SHOW COLUMNS FROM ".$table;
			if($cols = mysqli_query ($dbcon, $sql))
			{
				$c = 0;
				while($col = mysqli_fetch_array($cols))
				{
					if($_POST["C".$c]) $fk = $fk.$col[0].", ";
					$c++;
				}
			}
			if($fk != "")
			{
				$fk = substr($fk, 0, -2);
				$sql = "ALTER TABLE ".$table." ADD CONSTRAINT FOREIGN KEY (".$fk.") REFERENCES ".$_POST["FK"]." (".$fk.") ON DELETE CASCADE ON UPDATE CASCADE";
				mysqli_query ($dbcon, $sql);
			}
		}
		mysqli_close($dbcon);
	}
	
	if(isset($_GET["ChangeKeys"]))
	{
		if($_POST["ToDo"] == "PRI")
			ChangePrim($db, $table);
		elseif($_POST["ToDo"] == "FK")
			MakeForeignKey($db, $table);
	}

	function TableList($dbcon)
	{
		$opt = "";
		$sql = "SHOW TABLES";
		$tables = mysqli_query ($dbcon, $sql);
		while($ttable = mysqli_fetch_array($tables))
			$opt= $opt."<option>".$ttable[0]."</option>";
		return $opt;
	}
	
?>
<!DOCTYPE html>
<html lang="de">
	<head>
		<link rel="stylesheet" href="Standard.css">
		<title>Datenbankverwaltung</title>
	</head>
	<body>
		<header>
			<h1>Tabellen-Design in <font color='red'><?php echo $db;?></font></h1>
		</header>
		<aside>
			<h2>Tabellen</h2>
			<p>
<?PHP 
	if($db != "")
	{
		$dbcon = mysqli_connect (SERVER, USER, PASSWORD, $db);
		if (mysqli_connect_errno()==0)
		{
			$sql = "SHOW TABLES";
			$tables = mysqli_query ($dbcon, $sql);
			while($ttable = mysqli_fetch_array($tables))
			{
				if($ttable[0] == $table) 
					echo "<a href='TableDesign.php?Table=".$ttable[0]."'><font color = 'red'>".$ttable[0] ."</font></a><br>";
				else
					echo "<a href='TableDesign.php?Table=".$ttable[0]."'>".$ttable[0] ."</a><br>";
			}
		}
		mysqli_close($dbcon);
	}
?>			
			</p>
			<form action="TableDesign.php"><input type="submit" value="Tabelle erstellen: "><input type="text" name="CreateTable" size="30"></form>

		</aside>
		<article>
			<h2><?php echo $table;?></h2>
			<section>
<?PHP 
	if($db != "" && $table != "" )
	{
		$dbcon = mysqli_connect (SERVER, USER, PASSWORD, $db);
		if (mysqli_connect_errno()==0)
		{
			echo "<form action='TableDesign.php?ChangeKeys=true' method='POST'>";
			echo "Make selected fields <input type='radio' name='ToDo' value='PRI'>to Primary Key ";
			echo "<input type='radio' name='ToDo' value='FK'>to Foreign Key referencing <select name='FK'>".TableList($dbcon)."</select>";
			echo "<input type='submit' value='Now'>";
			$sql = "SHOW COLUMNS FROM ".$table;
			if($cols = mysqli_query ($dbcon, $sql))
			{
				echo "<table>";
				$c = 0;
				while($col = mysqli_fetch_array($cols))
				{
					$fname = ($col[3]=="PRI")? "<font color = 'red'>".$col[0] ."</font>": $col[0];
					echo "<tr><td><input type='checkbox' name='C".$c."'></td><td>".$fname."</td><td>".$col[1]."</td><td><a href='TableDesign.php?DelField=".$col[0]."'><img src='Delete.png'></a></td>";
					$sql ="SELECT CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME  FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = '".$db."' AND TABLE_NAME = '".$table."' AND COLUMN_NAME = '".$col[0]."' AND REFERENCED_TABLE_NAME IS NOT NULL";
					$constrs = mysqli_query ($dbcon, $sql);
					if($constr = mysqli_fetch_array($constrs))
						echo "<td><font color = 'green'> ==> ".$constr[1].".".$constr[2]."</font></td><td><a href='TableDesign.php?DelFK=".$constr[0]."'><img src='Delete.png'></a></td></tr>";
					else
						echo "<td></td><td></td></tr>";					
					$c++;
				}
				echo "</table>";
			}
			echo "</form>";
				
			echo "<form action='TableDesign.php?&AddField=true' method='POST'>";
			echo "<input type='text' name='FName'>";
			echo "<select name='FType'><option>int(11)</option><option>varchar(20)</option><option>varchar(50)</option><option>double</option><option>text</option><option>date</option></select>";
			echo "<input type='submit' value='Add'>";
			echo "</form>";
		}
		mysqli_close($dbcon);
	}
?>			
			</section>
		</article>
		<aside>
			<h2>Aktionen</h2>
			<p>
				<a href="Index.php">Zur&uuml;ck zur Hauptseite</a>
			</p>
		</aside>
		<footer>
			<p>&copy; 2019 by L&uuml;der Reimers</p>
		</footer>  
	</body>
</html>