<?php
	require_once "login.php";
	$connection = new mysqli($db_hostname, $db_username, $db_password, $db_database);	//From login.php, add connection info to there.

	if ($connection->connect_error) {
		die($connection->connect_error);
	}

	$query = [];
	//survey_id becomes foreign key later
	$query[] = "CREATE TABLE question(id INT(6) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, question_text VARCHAR(30) NOT NULL) ENGINE=InnoDB";
	$query[] = "CREATE TABLE survey(id INT(6) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, survey_name VARCHAR(30) NOT NULL,
	description VARCHAR(200), creation_date VARCHAR(30))ENGINE=InnoDB";
	$query[] = "CREATE TABLE survey_question(id INT(6) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	survey_id INT(6) UNSIGNED NOT NULL, question_id INT(6) UNSIGNED NOT NULL,
	FOREIGN KEY(survey_id) REFERENCES survey(id), FOREIGN KEY(question_id) REFERENCES question(id)) ENGINE=InnoDB";
	$query[] = "CREATE TABLE answer(id INT(7) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, text VARCHAR(80) NOT NULL) ENGINE=InnoDB";
	$query[] = "CREATE TABLE survey_question_answer(id INT(7) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	survey_id INT(6) UNSIGNED NOT NULL, question_id INT(6) UNSIGNED NOT NULL,
	answer_id INT(7) UNSIGNED NOT NULL, FOREIGN KEY(survey_id) REFERENCES survey(id), FOREIGN KEY(question_id) REFERENCES question(id),
	FOREIGN KEY(answer_id) REFERENCES answer(id)) ENGINE=InnoDB";
	$query[] = "CREATE TABLE result(id INT(7) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	survey_id INT(6) UNSIGNED NOT NULL, question_id INT(6) UNSIGNED NOT NULL, answer_id INT(7) UNSIGNED,
	FOREIGN KEY(survey_id) REFERENCES survey(id), FOREIGN KEY(question_id) REFERENCES question(id),
	FOREIGN KEY(answer_id) REFERENCES answer(id)) ENGINE=InnoDB";
	//User table might be added later.


	foreach ($query as $qr) {
		$connection->query($qr);
	}

	$check_query = [];
	$check_query[] = "SELECT 1 FROM question LIMIT 1";
	$check_query[] = "SELECT 1 FROM answer LIMIT 1";
	$check_query[] = "SELECT 1 FROM survey LIMIT 1";
	$check_query[] = "SELECT 1 FROM survey_question LIMIT 1";
	$check_query[] = "SELECT 1 FROM survey_question_answer LIMIT 1";
	$check_query[] = "SELECT 1 FROM result LIMIT 1";
	$check_query[] = "SELECT 1 FROM survey LIMIT 1";

	$ans = true;

	foreach($check_query as $cheq) {
		$ans = $connection->query($cheq);
		if ($ans === false) {
			break;
		}
	}

	if ($ans) {
		echo "Tables were created";
	}
	else {
		echo "Error.";
	}
?>