<?php

$hostname = 'localhost';
$username = 'narKampen';
$pass = '9Bq.6[AcTc2ADwN-';
$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'";
        $result = $conn->query($sql);
        if ($result === false) {
            echo "Insert new question $question \r\n";
        } else {
            $questionId = $result->fetch_object()->id;
        }
        $result->free_result();

        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);
        if ($result === false) {
            echo "Failed to get category, add New with name $categoryName";
        } else {
            $categoryId = $result->fetch_object()->id;
        }
        $result->free_result();

        if ($categoryId == null) {
            $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';
}