// mssql助手

const mssql = require('mssql')
const config = require('../config').mssql

// 合并配置
Object.assign(config, {
  options: {
    encrypt: false,
    enableArithAbort: true
  },
  connectionTimeout: 60000,
  requestTimeout: 60000,
  pool: {
    min: 10,
    max: 50,
    idleTimeoutMillis: 60000
  }
})

// 全局连接池
let pool = null
async function POOL() {
  if (pool) { !pool.connected && await pool.connect() }
  else {
    pool = new mssql.ConnectionPool(config)
    await pool.connect()
  }
  return pool
}

/**
 * 查询结果，sql含top1返回为对象（无数据返回null），多行返回为数组（无数据返回[]）
 */
const select = async sql => {
  const pool = await POOL()
  try {
    const result = await pool.request().query(sql)
    const top = sql.indexOf(' top 1 ') === 6 || sql.indexOf(' top (1) ') === 6
    if (result.rowsAffected[0] > 0) { return top ? result.recordset[0] : result.recordset }
    else { return top ? null : [] }
  }
  catch (err) {
    throw new Error(err)
  }
}

/**
 * 分页数据，传入sql语句、每页条数、页码，sql语句中必须有order by，返回：{total:100,rows:[]}，无数据rows为空数组
 */
const page = async (sql, pagesize = 10, page = 1) => {
  const pool = await POOL()
  try {
    await pool.connect()
    const TableAndWhere = sql.substring(sql.indexOf(' from ') + 6, sql.lastIndexOf(' order by '))
    // 先获取记录总数
    const result = await pool.request().query('select count(id) as count from ' + TableAndWhere)
    const count = result.recordset[0].count
    if (count === 0) { return { total: 0, rows: [] } }
    // 然后查询分页数据
    else {
      const FieldAndTable = sql.substring(7, sql.lastIndexOf(' order by '))
      const orderby = sql.substring(sql.lastIndexOf(' order by ') + 10)
      const start = pagesize * (page - 1) + 1
      const end = pagesize * page
      let rs = await pool.request().query(`select * from (select cast((row_number() over(order by ${orderby})) as int) as row,${FieldAndTable}) a where row between ${start} and ${end}`)
      if (rs.rowsAffected[0] === 0) { return { total: count, rows: [] } }
      else { return { total: count, rows: rs.recordset.sort((a, b) => a.row - b.row) } }
    }
  }
  catch (err) {
    throw new Error(err)
  }
}

/**
 * 执行不返回结果的脚本
 */
const execute = async sql => {
  if ((sql.startsWith('update ') || sql.startsWith('delete ')) && !sql.includes(' where ')) {
    throw { code: -1 }
  } else {
    const pool = await POOL()
    try {
      await pool.connect()
      await pool.request().query(sql)
    } catch (err) {
      throw new Error(err)
    }
  }
}

/**
 * 查寻单一值，数据类型与数据库字段一致，无记录返回null
 */
const scalar = async sql => {
  const pool = await POOL()
  try {
    let result = await pool.request().query(sql)
    if (result.rowsAffected[0] > 0) {
      result = result.recordset[0]
      let key = Object.keys(result)[0]
      return result[key]
    }
    else { return null }
  }
  catch (err) {
    throw new Error(err)
  }
}

/**
 * 单引号替换为两个单引号，返回字符串或空字符串
 */
const escape = data => {
  if (data) {
    // Number类型会导致replace出错
    let s = data.toString()
    s = s.trim()
    s = s.replace(/'/g, '\'\'')
    s = s.replace(/\t/g, '')
    s = s.replace(/<iframe/gi, '&lt;iframe')
    s = s.replace(/<script/gi, '&lt;script')
    s = s.replace(/on([a-z]+)=/gi, 'on$1&equals;')
    return s
  } else { return '' }
}

/**
 * 获取表新记录ID，返回的是数字
 */
const newid = async (table, start = 1) => {
  const sql = 'with temptb as (select id,id-(row_number()over(order by id)+' + (start - 1) + ') as diff from ' + table + ' where id>' + (start - 1) + ') select top 1 max(id)+1 as newid from temptb where diff=0'
  const newid = await scalar(sql)
  return newid || start
}

module.exports = {
  select,
  page,
  execute,
  scalar,
  escape,
  newid
}