let data = {
    "headers": {
        "SalesPerson": [
            "sales_id",
            "name",
            "salary",
            "commission_rate",
            "hire_date"
        ],
        "Company": [
            "com_id",
            "name",
            "city"
        ],
        "Orders": [
            "order_id",
            "order_date",
            "com_id",
            "sales_id",
            "amount"
        ]
    },
    "rows": {
        "SalesPerson": [
            [
                1,
                "John",
                100000,
                6,
                "4/1/2006"
            ],
            [
                2,
                "Amy",
                12000,
                5,
                "5/1/2010"
            ],
            [
                3,
                "Mark",
                65000,
                12,
                "12/25/2008"
            ],
            [
                4,
                "Pam",
                25000,
                25,
                "1/1/2005"
            ],
            [
                5,
                "Alex",
                5000,
                10,
                "2/3/2007"
            ]
        ],
        "Company": [
            [
                1,
                "RED",
                "Boston"
            ],
            [
                2,
                "ORANGE",
                "New York"
            ],
            [
                3,
                "YELLOW",
                "Boston"
            ],
            [
                4,
                "GREEN",
                "Austin"
            ]
        ],
        "Orders": [
            [
                1,
                "1/1/2014",
                3,
                4,
                10000
            ],
            [
                2,
                "2/1/2014",
                4,
                5,
                5000
            ],
            [
                3,
                "3/1/2014",
                1,
                1,
                50000
            ],
            [
                4,
                "4/1/2014",
                1,
                4,
                25000
            ]
        ]
    }
};

let initDataBase = 'drop DATABASE if EXISTS leet_code;\n' +
    'CREATE DATABASE leet_code;\n' +
    'use leet_code;\n'

let creattable = "";
let sql = ""
let deltable = ""
let executeSql = initDataBase;
let tableFiledTypes = {}
let tableInfos = data.headers
//得到插入的行信息
let rows = data.rows
//获得表名,字段名
for (let tableName in tableInfos) {
    //得到列头
    let list = [];

    let fields = tableInfos[tableName];
    let firstRow = rows[tableName][0];
    let fieldTypes = parseFieldType(fields, firstRow);

    tableFiledTypes[tableName] = fieldTypes;

    for (let k = 0; k < tableInfos[tableName].length; k++) {
        list.push(tableInfos[tableName][k] + " " + fieldTypes[k])
    }

    creattable += 'CREATE TABLE IF NOT EXISTS ' + tableName + '(' + list.join(",") + ')' + ';\n';
    deltable += 'DROP TABLE if EXISTS ' + tableName + ';\n';
    executeSql += deltable;
    executeSql += creattable;


    //insert 语句
    let insertValues = rows[tableName];

    //规范参数
    for (let i = 0; i < insertValues.length; i++) {
        for (let j = 0; j < insertValues[i].length; j++) {
            let v = insertValues[i][j];
            if (v == null)
                v = "a"
            if (fieldTypes[j] === 'datetime') {
                v = new Date(v)
                insertValues[i][j] ='"' + v.toLocaleString() + '"'
            } else {
                insertValues[i][j] = '"' + v + '"'
            }

        }
    }


    for (let m = 0; m < insertValues.length; m++) {
        let str = insertValues[m].toString();
        sql += 'INSERT INTO' + ' ' + tableName + ' VALUES (' + str.replace(new RegExp("a", "g"), "null") + ')' + ';\n';
    }

}

function parseFieldType(fields, firstRow) {
    if (fields.length !== firstRow.length) {
        return;
    }
    let fieldCount = fields.length;
    let fieldTypes = []

    for (let i = 0; i < fieldCount; i++) {
        let field = fields[i];
        let value = firstRow[i];

        //目前对四种类型判断 string int float date

        //默认是字符串类型
        let sqlType = 'varchar(1024)'

        let isFinite = Number.isFinite(value)
        let isInteger = Number.isInteger(value)
        let isString = typeof value === 'string';
        let isDate = false;
        let t = field.toLowerCase()
        //字符串类型，并且字段名称中包含 date time，尝试是不是合格的事件类型字段
        // 2006/4/1  1/4/2006 2006-04-01
        if (isString && (t.indexOf('date') >= 0 || t.indexOf('time') >= 0)) {
            let r = value.match(/^(\d{1,4})([-\/])(\d{1,2})([-\/])(\d{1,2})$/);
            isDate = Number.isFinite(new Date(value).valueOf())
            if (isDate && r != null) {
                isDate = true;
            }
        }

        if (isFinite) {
            if (isInteger) {
                sqlType = 'int'
            } else {
                sqlType = 'double'
            }
        }
        if (isDate) {
            sqlType = 'datetime'
        }

        fieldTypes.push(sqlType)
    }

    return fieldTypes;
}

executeSql += sql;
console.log(executeSql)