<?php 

	$hostname = 'localhost';
	$username = 'narKampen';
	$pass = 'IfRLzj2HJBXA9eei';
	$database = 'narKampen';
	
	$conn = new mysqli($hostname, $username, $pass, $database);
	if (!$conn) {
		die("Connection Failed. ". mysqli_connect_error());
	}
	mysqli_set_charset($conn,'utf8');
	
// Check if file is CSV
// get Filename, remove ending, this is the category name
// get file contents 

if (file_exists($argv[1])) {
	$file = fopen($argv[1], "r");
	$fileinfo = pathinfo($argv[1]);
	
	$categoryName = str_replace("_"," ", $fileinfo['filename']);
	
	while (($data = fgetcsv($file, 1000, ",")) !== false) {
		$answer = intval($data[0]);
		$question = $data[1];
	 
		if ($answer == null || $question == null || $answer == "" || $question == "") {
			echo "Failed to get answer and/or question from " . print_r($data,true) .  "\r\n";
			die();
		}
	 
		$sql = "SELECT id FROM questions WHERE question = '$question'";
		$questionId = $conn->query($sql)->fetch_object()->id;
		
		if($questionId == null) {
			$sql = "INSERT INTO questions (answer, question) 
				VALUES ($answer, '$question') 
				ON DUPLICATE KEY UPDATE 
				answer = $answer,
				id = LAST_INSERT_ID(id)";
			$conn->query($sql);
			$questionId = $conn->insert_id;	 
		}
		
		if ($questionId == 0) {
		
			echo "Nothing insered or updated by sql: \r\n";
			echo $sql;
			die(); // Should not happen!
		}
		
		$sql = "SELECT id FROM category WHERE name = '$categoryName'";
		$result = $conn->query($sql);
		
		$categoryId = -1;
		while ($row = $result->fetch_assoc()) {
			$categoryId = $row['id'];
		}
		
		if ($categoryId == -1) {
			$sql = "INSERT INTO category (name, r,g,b,a) VALUES ($categoryName, 255,255,255,255)";
			$conn->query($sql);
			$categoryId = $conn->insert_id;
		}
		
		$sql = "INSERT INTO questionToCategory (questionId, categoryId) VALUES ($questionId, $categoryId)";
		$conn->query($sql);
		
		echo "inserted question $question into category $categoryName \r\n";
 }
 
 fclose($file);
} else {
	echo "Run with file as first argument";
}