let fs = require("fs");

fs.readFile("./in.txt", "utf8", (err, data) => {
    let lines = data.split("\r\n");
    let name = lines[0];
    let colNames = lines[1].split("\t");
    let colTypes = lines[2].split("\t").map(val => {
        let ret = "text";

        if (/^(\d+-)+\d+$/.test(val)) {
            ret = "date";
        } else if (!isNaN(parseInt(val))) {
            ret = "real";
        }

        return ret;
    });
    let cols = "";
    let vals = "";
    let output = `\
CREATE TABLE IF NOT EXISTS ${name} (
:cols);

INSERT INTO ${name} VALUES
:vals`;

    colNames.forEach((col, i) => {
        cols += `\t${col} ${colTypes[i]}${i === colNames.length - 1 ? "" : ","}\n`
    });
    lines = lines.slice(2);
    lines.forEach((dataLine, i) => {
        let dataArr = dataLine.split("\t");
        let lastLine = i === lines.length - 1;

        vals += "\t("
        dataArr.forEach((val, j) => {
            let last = j === dataArr.length - 1;
            let formattedVal;

            if (val) {
                formattedVal = colTypes[j] === "real" ? val : `'${val}'`;
            } else {
                formattedVal = "NULL";
            }

            vals += `${formattedVal}${last ? "" : ", "}`
        });
        vals += `)${lastLine ? ";" : ","}\n`
    });
    output = output.replace(":cols", cols).replace(":vals", vals);

    fs.writeFile(`${name}.sql`, output, (err, data) => console.log(err));
});