<?php
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Methods: PUT, GET, POST");
header("Access-Control-Allow-Headers: Origin, X-Requested-With, Content-Type, Accept");
if(isset($_REQUEST["data"]))
	$json_data= $_REQUEST["data"];
else
	$json_data='{
    "driver": "mysql",
    "host": "127.0.0.1",
    "dbname": "wintergenomics_site",
    "username": "xor1",
    "password": "LB8nqZh3IsaJvGhS"
}';

//CONNECTION

$conn = json_decode($json_data,true);
$dsn = "{$conn["driver"]}:host={$conn["host"]};dbname={$conn["dbname"]}";
$options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'); 
try {$dbh = new PDO($dsn, $conn["username"], $conn["password"], $options);} catch (PDOException $e) {echo 'Connection failed: ' . $e->getMessage();}

//GENERAL DESCRIPTION OF TABLE 
$tHeader = array(
					'Field',
					'Type',
					'Null',
					'Key',
					'Default',
					'Extra'
);
//GENERAL DESCRIPTION OF FOREIGN KEYS
$fHeader = array(
					'CONSTRAINT_CATALOG',
					'CONSTRAINT_SCHEMA',
					'CONSTRAINT_NAME',
					'TABLE_CATALOG',
					'TABLE_SCHEMA',
					'TABLE_NAME',
					'COLUMN_NAME',
					'ORDINAL_POSITION',
					'POSITION_IN_UNIQUE_CONSTRAINT',
					'REFERENCED_TABLE_SCHEMA',
					'REFERENCED_TABLE_NAME',
					'REFERENCED_COLUMN_NAME'
);


//ARRAY TABLES
$masterArray = array();
$tableArray = array();
//GET ALL TABLES
$masterArray["tables"]=array();
$query_str = "SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA='{$conn["dbname"]}'";
$tables = $dbh->query($query_str);
$it1=0;
foreach ($tables as $table){
	//TOTALS OF RESULTING VIEW
	$totalColumns = array();
	$relatedTablesNames = array();
	$foreignKeysArray = array();
	$selectedTableName = $table["TABLE_NAME"];
	$tableArray["selectedTable"]=array();
	
	//CURRENT TABLE
	$query_str= "DESCRIBE {$selectedTableName}";
	$columns = $dbh->query($query_str);
	$columnNumber = $columns->columnCount();
	$tableArray["selectedTable"]["name"]=$selectedTableName;
	foreach($columns as $column){
		$tableArray["selectedTable"]["columns"][]=$column;
		$totalColumns[]=$selectedTableName.".".$column["Field"];
	}
	
	//FOREINGN KEYS
	$tableArray["foreignKeys"] = array();
	$query_str = "SELECT * FROM information_schema.KEY_COLUMN_USAGE ke WHERE ke.table_name = '{$selectedTableName}' AND  ke.CONSTRAINT_SCHEMA='{$conn["dbname"]}'ORDER BY  ke.referenced_table_name";
	$foreignKeys = $dbh->query($query_str);
	$foreignKeysCount  = $foreignKeys->rowCount();
	if($foreignKeysCount>0){
		foreach ($foreignKeys as $foreignKey){
			array_push($relatedTablesNames,$foreignKey["REFERENCED_TABLE_NAME"]);
			array_push($foreignKeysArray,$foreignKey);
		}
	}
	$tableArray["foreignKeys"] = $foreignKeysArray;
	//RELATED TABLES
	$tableArray["relatedTables"] = array();
	$it2=0;
	foreach($relatedTablesNames as $relatedTableName){
		if($relatedTableName!=null){
			$query_str= "DESCRIBE {$relatedTableName}";
			$columns = $dbh->query($query_str);
			$columnNumber = $columns->columnCount();
			$tableArray["relatedTables"][$it2]["name"] =$relatedTableName;
			foreach($columns as $column){
			$tableArray["relatedTables"][$it2]["columns"][]=$column;
				$totalColumns[]=$relatedTableName.".".$column["Field"];
			}
			$it2++;
		}
	}
	$tableArray["totalColumns"] =  $totalColumns;
	$masterArray["tables"][$it1]=$tableArray;
	 $it1++;
}


header('Content-Type: application/json');
echo json_encode($masterArray,JSON_PRETTY_PRINT);



?>