#!/bin/node

var fs = require("fs");
var lines = fs.readFileSync(process.argv[2]).toString().trim().split("\n");
var records = [];

function objArrToSQLInsertArr(objArr) {
  var temp = JSON.stringify(objArr).replaceAll("[", "(").replaceAll("]", ")");
	return temp.substr(1, temp.length - 2)
}

lines.forEach(line => {
  var columns = line.split("\t");
  var plaats = columns[4].split(",");
  records.push({
    "Merk": columns[0],
    "Type": columns[1],
    "Vermogen": Number(columns[3]),
    "Locatie": plaats[0].trim(),
    "Land": plaats[1].trim(),
  });
});

var landen = [];
records.forEach(record => landen.push(record.Land));
landen = Array.from(new Set(landen));
console.log(`insert into Land (Naam) values ${objArrToSQLInsertArr(landen.map(i => [i]))};`);

var merken = [];
records.forEach(record => merken.push(record.Merk));
merken = Array.from(new Set(merken));
console.log(`insert into Merk (Naam) values ${objArrToSQLInsertArr(merken.map(i => [i]))};`);

var locaties = [];
records.forEach(record => locaties.push(JSON.stringify([record.Locatie, landen.indexOf(record.Land) + 1])));
locaties = Array.from(new Set(locaties)).map(i => JSON.parse(i));
console.log(`insert into Locatie (Naam, LandID) values ${objArrToSQLInsertArr(locaties)};`);

var hoofdkantoren = [];
records.forEach(record => hoofdkantoren.push(JSON.stringify([locaties.indexOf(locaties.find(x => x[0] == record.Locatie)) + 1, merken.indexOf(record.Merk) + 1])));
hoofdkantoren = Array.from(new Set(hoofdkantoren)).map(i => JSON.parse(i));
console.log(`insert into Hoofdkantoor (LocatieID, MerkID) values ${objArrToSQLInsertArr(hoofdkantoren)};`);

var types = [];
records.forEach(record => types.push(JSON.stringify([merken.indexOf(record.Merk) + 1, record.Type, record.Vermogen])));
types = Array.from(new Set(types)).map(i => JSON.parse(i));
console.log(`insert into \`Type\` (MerkID, Naam, Vermogen) values ${objArrToSQLInsertArr(types)};`);