/**
 * 批量导入数据库示例
 * 难点在于遇到关键字时需使用引号
 *
 */

const mysql = require('mysql2')
require('dotenv').config()
const json = require('./log/userbook.json')
let {DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE} = process.env
let pool = mysql.createPool({
  host: DB_HOST, user: DB_USER, password: DB_PASSWORD, database: DB_DATABASE
})
let a = {
  "userbookid": "a1a9edd853f941dab79bba1b3e3c543d",
  "usercode": "0420177210",
  "useraccount": null,
  "usercnname": "吕银龙",
  "sex": null,
  "birth": null,
  "iccode": null,
  "mobilephone1": "13806211816",
  "mobilephone2": null,
  "telephone": null,
  "email": null,
  "addressid": "320000,320500,320507",
  "areaid": "e8a9367b463746edae43ae2cb7ac79f6",
  "building": "31",
  "unitnum": "0",
  "houseno": "101",
  "addressdetails": "爱丁堡31幢0单元101室",
  "usertype": null,
  "createuser": null,
  "createtime": "2020-01-09 09:43:03.173",
  "modifyuser": null,
  "modifytime": null,
  "isused": null,
  "orgid": null,
  "apply": "2020-01-09 09:43:03",
  "areaname": "爱丁堡",
  "usertypeName": "普通用户",
  "meterid": null,
  "meterno": "1809201008089",
  "meterstatus": "已通气",
  "currentdata": "1146.7"
}

pool.getConnection((err, connection) => {
  if (err) {
    return err
  }
  let str = []
  let keys = Object.keys(a)
  for (let i = 0; i < keys.length; i++) {
    str.push(`${keys[i]} text`)
  }
  str = str.join(',')

  let createSql = `
      create table if not exists songshu.userbook
      (
          id integer auto_increment primary key,
          ${str}
      )
  `;

  connection.execute(createSql, (err, rows, fields) => {
    if (err) {
      console.log(err)
      return err
    }
    console.log(rows)
    connection.release()
  })

  for (const jsonElement of json) {
    let keysStr = keys.join(',')
    let sql;
    sql = `insert into songshu.userbook (id, ${keysStr})
           VALUES (default, ${Object.values(jsonElement).map(item => `"${item}"`).join(',')})`;
    connection.execute(sql, (err, rows, fields) => {
      if (err) {
        console.log(err)
        return err
      }
      return rows
      connection.release()
    })

  }
  pool.end()
})