var mysql = require('mysql');
var config = require('./config');


// 创建连接池
// 成为一个对象
var pool = mysql.createPool({
    connectionLimit:      100,     //最大连接数
    multipleStatements:   true,    //允许多语句查询
    host          :config.host,
    user          :config.user,
    password      :config.password,
    database      :config.database
})

// 查找所有商品以及按照商品类别查找商品

module.exports.findProduct = function(callback){
    pool.getConnection(function(err,conn){
        if(err){
            return callback('连接池连接失败!'+err,null);
        }
        // 导航栏限制5个商品，最后商品栏每类限制4个
        var sql = `
        SELECT * FROM mi_store WHERE p_type='phone' LIMIT 5;
        SELECT * FROM mi_store WHERE p_type='tv' LIMIT 5;
        SELECT * FROM mi_store WHERE p_type='computer' LIMIT 5;
        SELECT * FROM mi_store WHERE p_type='router' LIMIT 5;
        SELECT * FROM mi_store WHERE p_type='home' LIMIT 5;
        SELECT * FROM mi_store  WHERE
            p_id BETWEEN 1 AND 4  
            OR p_id BETWEEN 6 AND 9  
            OR p_id BETWEEN 11 AND 14  
            OR P_id BETWEEN 16 AND 19  
            OR p_id BETWEEN 21 AND 24; 
        ` ;

        conn.query(sql,function(err,results){
            conn.release();
            if(err){
                return callback('查询失败'+err,null);
            }
            callback(null,results);
        })
    })
}
// 根据电话查找用户信息：定义一个 findPhone()，并导出
module.exports.findPhone = function (phone, callback){
    pool.getConnection(function(err,conn){
      if (err) {
        return callback("连接池连接失败！"+err,null)
      }
      var sql = "SELECT*FROM user_info WHERE phone=?"
      conn.query(sql, [phone], function(err,results) {
        conn.release()
        if (err) {
          return callback("查询失败！" + err, null)
        }
        callback(null, results)
      });
    });
}
// 注册：将信息存储到数据表中
module.exports.doReg = function(phone, nickname, password, callback) {
  // 获取数据库连接池
  pool.getConnection(function(err, conn) {
      if (err) {
          return callback("连接池连接失败!" + err,null)
      }

      // 定义SQL语句
      var sql = 'insert into user_info(u_id,phone, nickname, password) values (null,?,?,?)'

      // 使用连接池分配连接
      conn.query(sql, [phone,nickname,password], function(err, results) {
          // 释放连接
          conn.release();
          if (err) {
              return callback("查询失败!" + err,null);
          }
          callback(null, results);
      })
  })
}