const express = require('express');
const session = require('express-session');
const fs = require('fs');
const log4js = require('log4js');
const http = require('http');
const https = require('https');
const date_format = require('date-format');

const { Configuration, OpenAI } = require('openai');

require('dotenv').config();
const { env } = require("process");

const { v4: uuidv4 } = require('uuid');

var SqlString = require('sqlstring');
const e = require('express');
const { stringify } = require('querystring');
const { get } = require('express/lib/response');
const res = require('express/lib/response');

OPENAI_API_KEY = process.env.OPENAI_API_KEY;
const openai = new OpenAI(OPENAI_API_KEY);

const reg_timeout = process.env.REG_LINK_TIMEOUT || 15;
const invite_timeout = process.env.INVITE_LINK_TIMEOUT || 20;

var timer;
if(timer)clearInterval(timer);
timer = setInterval(function(){
  clearTimeoutSessions();
},
60*1000);

async function clearTimeoutSessions(){
  var rtn = false;
  const connection = await connectDB();

  try {
    var results = await new Promise((resolve, reject) => {
      connection.query(SqlString.format('delete from registration_sessions where TIMESTAMPDIFF(MINUTE, created, NOW()) > ? ', 
          [reg_timeout]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    })
    results = await new Promise((resolve, reject) => {
      connection.query(SqlString.format('delete from invite_user_sessions where TIMESTAMPDIFF(MINUTE, created, NOW()) > ? ', 
          [invite_timeout]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    })
  }catch (err) {
    logger.error('DB err:'+err)
  } finally {
    if(connection)connection.release()
  }
}

app.get('/', (req, res) => {
  res.redirect('/top.html');
});

app.get('/working', async (req, res) => {
  const connection = await connectDB();

  try {
    var results = await new Promise((resolve, reject) => {
      connection.query(SqlString.format('select * from accounts limit 1 ', 
          [reg_timeout]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    })
    res.send("sisai is working on "+getLocalIP());
  }catch (err) {
    logger.error('DB err:'+err)
    res.send("sisai is BREAKING");
  } finally {
    if(connection)connection.release()
  }
});

function getLocalIP(){
  var ip = null;
  const os = require('os');

  // Get network interfaces
  const networkInterfaces = os.networkInterfaces();

  for (const interfaceName in networkInterfaces) {
      const networkInterface = networkInterfaces[interfaceName];
      for (const alias of networkInterface) {
          // Check if it's IPv4 and not internal (i.e., not a loopback address)
          if (alias.family === 'IPv4' && !alias.internal) {
              ip=alias.address;
              break;
          }
      }
  }
  return ip;
}

app.post('/login', async (req, res) => {
  const body = await loadJsonBody(req, false)
  const email = body.email.trim().toLowerCase();
  logger.debug('login: '+email);

  if(body.email && body.pwd){
    const uinfo = await login(email, body.pwd);
    if(uinfo){
      req.session.uid = uinfo.id;
      req.session.email = email;
      req.session.fetchtime = new Date().getTime();
      //set user session timeout

      //req.session.abort_stream == 0;
      //req.session.stop_time = 0;
      //req.session.ask_time = req.session.stop_time+100;
      res.send(JSON.stringify({status:'OK', uid:uinfo.id, nickname:uinfo.name}));
    }else res.send(JSON.stringify({status:'error'}));
  }else res.send(JSON.stringify({status:'error'}));
});

app.post('/getusrinf', async (req, res) => {
  if(!checkUserSession(req)){res.status(440).end();return;}

  try{
    const user = await getUserInfo(req.session.uid);
    if(user)user.status = 'OK';
    res.send(user?JSON.stringify(user):JSON.stringify({status:'error'}));
  }catch(err){
    logger.error(err);
  }
});

function checkUserSession(req){
  logger.debug('check User-Session...');
  var rtn = false;
  if(!req.session.uid || !req.session.fetchtime)return rtn;
  else if(new Date().getTime() - req.session.fetchtime > process.env.USER_SESSION_TIMEOUT*1000*60){//minutes
    req.session.destroy();
    rtn = false;
  }else{
    req.session.fetchtime = new Date().getTime();
    rtn = true;
  }
  return rtn;
}

app.post('/renmspc', async (req, res) => {
  if(!checkUserSession(req)){res.status(440).end();return;}

  const body = await loadJsonBody(req)

  if(body.space_id && body.space_name){
    const space_id = await renameSpace(body.space_id, body.space_name, req.session.uid, body.language);
    res.send(JSON.stringify(space_id?{status:'OK',space_id:space_id}:{status:'error'}));
  }else res.send(JSON.stringify({status:'error'}));
});

app.post('/chksession', async (req, res) => {
  if(checkUserSession(req)){
    res.send(JSON.stringify({status:'OK', user_id:req.session.uid}));
  }else res.send(JSON.stringify({status:'error'}));
});

app.post('/invtinguser', async (req, res) => {
  const body = await loadJsonBody(req, false)
  if(body.session){
    const json = await inviteExistAccount(body.session);
    if(null!=json['exist']){
      if(json['exist'])res.send(JSON.stringify({status:'OK', exist:true}));
      else res.send(JSON.stringify({status:'OK', exist:false, user_email:json['email']}));
      return;
    }
  }
  return res.send(JSON.stringify({status:'error'}));
});


app.post('/logout', async (req, res) => {
  req.session.destroy();
  res.send(JSON.stringify({status:'OK'}));
});

app.post('/createspace', async (req, res) => {
  if(!checkUserSession(req)){res.status(440).end();return;}

  const body = await loadJsonBody(req)

  if(body.spacetemp_id && body.space_name){
    const space_id = await insertSpace(body.spacetemp_id, body.space_name, req.session.uid);
    res.send(JSON.stringify(space_id?{status:'OK',space_id:space_id}:{status:'error'}));
  }else res.send(JSON.stringify({status:'error'}));
});

app.post('/ask', async (req, res) => {
  if(!checkUserSession(req)){res.status(440).end();return;}

  try{
    logger.debug('asking');
    const body = await loadJsonBody(req);
    
    session_time.set(req.session.uid+"_ask_time", body.time);

    if(body.new_my_prompt && body.space_id && body.question.length > 0){
      //Insert new my prompt
      const prompt = decodeURIComponent(body.question);
      await insertAccountPrompt(req.session.uid, body.space_id, prompt, body.language);
    }

    //Call OpenAI API
    res.setHeader('Content-Type', 'text/event-stream');
    res.setHeader('Cache-Control', 'no-cache');
    res.setHeader('Connection', 'keep-alive');
    askOpenAPI(req, req.session.uid, body.space_id, body.conversation_id, body.question, body.advice_prompt_id, res, body.language);

  }catch(err){
    logger.error(err);
    res.send(JSON.stringify({status:'error'}));
  }
});

app.post('/stop', async (req, res) => {
  if(!checkUserSession(req)){res.status(440).end();return;}

  try{
    const body = await loadJsonBody(req);
    session_time.set(req.session.uid+"_stop_time", body.time);

    res.send(JSON.stringify({status:'OK', reps:""}));
  }catch(err){
    logger.error(err);
    res.send(JSON.stringify({status:'error'}));
  }
});

app.post('/unspcusr', async (req, res) => {
  if(!checkUserSession(req)){res.status(440).end();return;}

  try{
    const body = await loadJsonBody(req);
    if(await unassignSpaceUser(req.session.uid, body.space_id, body.user_id)){
      res.send(JSON.stringify({status:'OK'}));
    }else res.send(JSON.stringify({status:'error'}));
  }catch(err){
    logger.error(err);
    res.send(JSON.stringify({status:'error'}));
  }
});

app.post('/newconversation', async (req, res) => {
  if(!checkUserSession(req)){res.status(440).end();return;}

  try{
    const body = await loadJsonBody(req);
    const acc_session = await insertAccSpaceSession(req.session.uid, body.space_id, body.prompt, body.language);
    res.send(JSON.stringify({status:'OK', conv_id:acc_session.session_id, short_name:acc_session.short_name}));
  }catch(err){
    logger.error(err);
    res.send(JSON.stringify({status:'error'}));
  }
});

app.post('/settmplbasic', async (req, res) => {
  if(!checkUserSession(req)){res.status(440).end();return;}

  try{
    const body = await loadJsonBody(req);
    if(body.tmpl_id){
      await updateTemplateBasic(req.session.uid, body.tmpl_id, body.tmpl_name, body.tmpl_cmt, body.language);
      res.send(JSON.stringify({status:'OK'}));
    }else{
      const tmpl_id = await insertTemplateBasic(req.session.uid, body.tmpl_name, body.tmpl_cmt, body.language);
      if(tmpl_id >=0 )res.send(JSON.stringify({status:'OK', tmpl_id:tmpl_id}));
      else res.send(JSON.stringify({status:'error'}));
    }
  }catch(err){
    logger.error(err);
    res.send(JSON.stringify({status:'error'}));
  }
});

app.post('/deltmplinf', async (req, res) => {
  if(!checkUserSession(req)){res.status(440).end();return;}

  try{
    const body = await loadJsonBody(req);
    await deleteTemplateInfo(req.session.uid, body.tmpl_id, body.info_id);
    res.send(JSON.stringify({status:'OK'}));
  }catch(err){
    logger.error(err);
    res.send(JSON.stringify({status:'error'}));
  }

});

app.post('/deltmplprmt', async (req, res) => {
  if(!checkUserSession(req)){res.status(440).end();return;}

  try{
    const body = await loadJsonBody(req);
    await deleteTemplatePrompt(req.session.uid, body.tmpl_id, body.prompt_id);
    res.send(JSON.stringify({status:'OK'}));
  }catch(err){
    logger.error(err);
    res.send(JSON.stringify({status:'error'}));
  }
});

app.post('/delspc', async (req, res) => {
 if(!checkUserSession(req)){res.status(440).end();return;}
  
  try{
    const body = await loadJsonBody(req);
    await deleteSpace(req.session.uid, body.space_id);
    res.send(JSON.stringify({status:'OK'}));
  }catch(err){
    logger.error(err);
    res.send(JSON.stringify({status:'error'}));
  }
});

app.post('/settmplpub', async (req, res) => {
  if(!checkUserSession(req)){res.status(440).end();return;}

  try{
    const body = await loadJsonBody(req);
    const tmpl_id = await updateTemplatePublic(req.session.uid, body.tmpl_id, parseInt(body.is_public));
    res.send(JSON.stringify({status:'OK'}));
  }catch(err){
    logger.error(err);
    res.send(JSON.stringify({status:'error'}));
  }
});

app.post('/settmplinf', async (req, res) => {
  if(!checkUserSession(req)){res.status(440).end();return;}

  try{
    const body = await loadJsonBody(req);
    if(body.info_id){
      await updateTemplateInfo(req.session.uid, body.tmpl_id, body.info_id, body.info_name, body.info_cmt, body.language);
      res.send(JSON.stringify({status:'OK'}));
    }else{
      const info_id = await insertTemplateInfo(req.session.uid, body.tmpl_id, body.info_name, body.info_cmt, body.language);
      if(info_id >=0 )res.send(JSON.stringify({status:'OK', info_id:info_id}));
      else res.send(JSON.stringify({status:'error'}));
    }
  }catch(err){
    logger.error(err);
    res.send(JSON.stringify({status:'error'}));
  }
});

app.post('/settmplprmt', async (req, res) => {
  if(!checkUserSession(req)){res.status(440).end();return;}

  try{
    const body = await loadJsonBody(req);
    if(body.prompt_id){
      await updateTemplatePrompt(req.session.uid, body.tmpl_id, body.prompt_id, body.prompt_hint, body.prompt_head, 
        body.prompt_show, body.prompt_foot, body.language);
      res.send(JSON.stringify({status:'OK'}));
    }else{
      const prmt_id = await insertTemplatePrompt(req.session.uid, body.tmpl_id, body.prompt_hint, body.prompt_head, 
        body.prompt_show, body.prompt_foot, body.language);
      if(prmt_id >=0 )res.send(JSON.stringify({status:'OK', prompt_id:prmt_id}));
      else res.send(JSON.stringify({status:'error'}));
    }
  }catch(err){
    logger.error(err);
    res.send(JSON.stringify({status:'error'}));
  }
});

app.post('/chksamenm', async (req, res) => {
  const body = await loadJsonBody(req);
  if(body.type!='nick' && !req.session.uid){res.status(440).end();return;}

  try{
    var same = false;
    switch(body.type){
      case 'space':
        var nm = body.space_name.trim().toLowerCase();
        same = await checkSameSpaceName(req.session.uid, body.space_id, nm);
        break;
      case 'tmpl':
        nm = body.tmpl_name.trim().toLowerCase();
        same = await checkSameTmplName(body.tmpl_id, nm);
        break;
      case 'info':
        nm = body.info_name.trim().toLowerCase();
        same = await checkSameInfoName(body.tmpl_id, body.info_id, nm);
        break;
      case 'nick':
        nm = body.nickname.trim().toLowerCase();
        same = await checkSameNickName(nm);
        break;
      default:
        res.send(JSON.stringify({status:'error'}));
    }
    res.send(JSON.stringify({status:'OK', same:same}));
  }catch(err){
    logger.error(err);
    res.send(JSON.stringify({status:'error'}));
  }
});

app.post('/setuser', async (req, res) => {
  const body = await loadJsonBody(req, false);
  const uname= body.user_name.toLowerCase();
  logger.debug('setuser: '+uname);
  if(!body.invt_session_id && !body.reg_session_id && !req.session.uid){res.status(440).end();return;}

  try{
    if(body.reg_session_id || body.invt_session_id){
      const user_id = await setUser(body.reg_session_id, body.invt_session_id, uname, body.password, body.language);
      if(user_id >=0 )res.send(JSON.stringify({status:'OK', user_id:user_id}));
      else res.send(JSON.stringify({status:'error'}));
    }else{
      //TODO: Update user
      //await updateUser(req.session.uid, body.user_id, body.password);
      //res.send(JSON.stringify({status:'OK'}));
    }
  }catch(err){
    logger.error(err);
    res.send(JSON.stringify({status:'error'}));
  }
});

app.post('/sendregmail', async (req, res) => {
  try{
  //Need not to check session
  const body = await loadJsonBody(req)
  const sid = await makeRegSession(body.email);
  if(!sid){
    res.send({status:'error'});
    return;
  }

  const reg_link = process.env.REG_LINK_PREFIX + sid
  const language = body.language?body.language:req.headers["accept-language"].substring(0,2)
  const msg = makeMessage('reg_email_temp', language, [reg_timeout, reg_link])
  const sent = await sendEmail(body.email, makeMessage('reg_email_subject', language), msg)
  if(sent)res.send({status:'OK'});  
  else res.send({status:'error'});
  }catch(err){
    logger.error(err);
    res.send(JSON.stringify({status:'error'}));
  }
});

app.post('/sendinvtmail', async (req, res) => {
  if(!checkUserSession(req)){res.status(440).end();return;}
  try{
    const body = await loadJsonBody(req)
    if(!body.email || body.role == null || !(await isManager(req.session.uid, body.space_id))){
      res.send({status:'error'});
      return;
    }

    const sid = await generateInviteUserSession(req.session.uid, body.email, body.space_id, parseInt(body.role));
    if(!sid){
      res.send({status:'error'});
      return;
    }

    const reg_link = process.env.INVITE_LINK_PREFIX + sid
    const language = body.language?body.language:req.headers["accept-language"].substring(0,2)
    const msg = makeMessage('invt_email_temp', language, [invite_timeout, reg_link])
    const sent = await sendEmail(body.email, makeMessage('invt_email_subject', language), msg)
    if(sent)res.send({status:'OK'});  
    else res.send({status:'error'});
  }catch(err){
    logger.error(err);
    res.send(JSON.stringify({status:'error'}));
  }
});

app.post('/invt', async (req, res) => {//Invite user
  try{
    const body = await loadJsonBody(req);
    if(!body.session){
      res.send(JSON.stringify({status:'error'}));
      return;
    }
    const user_email = await processInviteMgrSession(body.session);

    res.send(JSON.stringify({status:'OK', user_email:user_email}));
  }catch(err){
    logger.error(err);
    res.send(JSON.stringify({status:'error'}));
  }
});

app.post('/reguser', async (req, res) => {
  try{
    const body = await loadJsonBody(req)
    if(!body.user_email){
      res.send('');
      return;
    }
    const sid = body.user_email
    if(!sid){
      res.send('');
      return;
    }

    await setUser(sid);
    res.json({ status: 'OK'});
  }catch(err){
    logger.error(err);
  }
});

app.post('/listspacenms', async (req, res) => {
  if(!checkUserSession(req)){res.status(440).end();return;}
  try{
    var body=null;
    try{body = await loadJsonBody(req);}catch(err){/*Do nothing*/}

    const spaces = await listSpaceNames(req.session.uid, body&&body.role_type?parseInt(body.role_type):0);
    if(spaces){//} && 0 < spaces.length){
      const rtnJson = {}
      rtnJson['spaces'] = spaces
      rtnJson['status'] = 'OK'
      res.send(JSON.stringify(rtnJson));
    }else res.send(JSON.stringify({spaces:[]}));
  }catch(err){
    logger.error(err);
  }
});

app.post('/listconv', async (req, res) => {
  if(!checkUserSession(req)){res.status(440).end();return;}
  try{
    const body = await loadJsonBody(req);

    const convs = await listAccSpcSessions(req.session.uid, body.space_id);
    if(convs && 0 < convs.length){
      const rtnJson = {}
      rtnJson['convs'] = convs
      rtnJson['status'] = 'OK'
      res.send(JSON.stringify(rtnJson));
    }else res.send(JSON.stringify({status:'OK', convs:[]}));
  }catch(err){
    logger.error(err);
  }
});

app.post('/listhistory', async (req, res) => {await processModule.reqHistory(req, res);});
module.exports.reqHistory =  async (req, res) =>{
  if(!checkUserSession(req)){res.status(440).end();return;}
  try{
    const body = await loadJsonBody(req);

    const history = await processModule.listHistory(req.session.uid, body.space_id, body.conversation_id, body.language);
    if(history && 0 < history.length){
      const rtnJson = {}
      rtnJson['history'] = history
      rtnJson['status'] = 'OK'
      res.send(JSON.stringify(rtnJson));
    }else res.send(JSON.stringify({status:'OK', history:[]}));
  }catch(err){
    logger.error(err);
  }
};

app.post('/listspcusrs', async (req, res) => {
  if(!checkUserSession(req)){res.status(440).end();return;}
  try{
    const list = await listSpaceUsers(req.session.uid);
    if(list && 0 < list.length){
      const rtnJson = {}
      rtnJson['list'] = list
      rtnJson['status'] = 'OK'
      res.send(JSON.stringify(rtnJson));
    }else res.send(JSON.stringify({users:[]}));
  }catch(err){
    logger.error(err);
    res.send(JSON.stringify({status:'error'}));
  }
});

app.post('/chgrlusr', async (req, res) => {
  if(!checkUserSession(req)){res.status(440).end();return;}
  try{
    const body = await loadJsonBody(req)

    if(await changeRoleUser(req.session.uid, body.space_id, body.user_id, parseInt(body.role))){
      res.send(JSON.stringify({status:'OK'}));
    }else res.send(JSON.stringify({status:'error'}));
  }catch(err){
    logger.error(err);
    res.send(JSON.stringify({status:'error'}));
  }
});

app.post('/listtmplnms', async (req, res) => {
  if(!checkUserSession(req)){res.status(440).end();return;}
  try{
    const body = await loadJsonBody(req)

    const tmpls = await listTmplNames(body.all_user?null:req.session.uid);
    if(tmpls && 0 < tmpls.length){
      const rtnJson = {}
      rtnJson['tmpls'] = tmpls
      rtnJson['status'] = 'OK'
      res.send(JSON.stringify(rtnJson));
    }else res.send(JSON.stringify({tmpl:[]}));
  }catch(err){
    logger.error(err);
  }
});

app.post('/gettmplbasic', async (req, res) => {
  if(!checkUserSession(req)){res.status(440).end();return;}
  try{
    const body = await loadJsonBody(req)

    const tmpl = await getTemplateBasic(req.session.uid, body.tmpl_id, body.language);
    if(tmpl){
      tmpl.status = 'OK';
      res.send(JSON.stringify(tmpl));
    }else res.send(JSON.stringify({status:'error'}));
  }catch(err){
    logger.error(err);
  }
});

app.post('/gettmplother', async (req, res) => {
  if(!checkUserSession(req)){res.status(440).end();return;}
  try{
    const body = await loadJsonBody(req)

    const tmpl = await getTemplateOther(req.session.uid, body.tmpl_id, body.language);
    if(tmpl){
      tmpl.status = 'OK';
      res.send(JSON.stringify(tmpl));
    }else res.send(JSON.stringify({status:'error'}));
  }catch(err){
    logger.error(err);
  }
});

app.post('/getspace', async (req, res) => {
  if(!checkUserSession(req)){res.status(440).end();return;}
  try{
    const body = await loadJsonBody(req)
    const space = await getSpaceShow(body.space_id, req.session.uid, body.language);
    if(space)space.status = 'OK';
    res.send(space?JSON.stringify(space):JSON.stringify({status:'OK'}));
  }catch(err){
    logger.error(err);
  }
});

app.post('/listspacetmpls', async (req, res) => {
  if(!checkUserSession(req)){res.status(440).end();return;}
  try{
    const body = await loadJsonBody(req)

    const spaces = await listSpaceTemplates(req.session.uid, body.type, body.page, body.per_page);
    if(spaces && 0 < spaces.length){
      const rtnJson = {}
      rtnJson['tmpls'] = spaces
      rtnJson['status'] = 'OK'
      res.send(JSON.stringify(rtnJson));
    }else res.send(JSON.stringify({tmpls:[]}));
  }catch(err){
    logger.error(err);
  }
});

app.post('/setspcinf', async (req, res) => {
  if(!checkUserSession(req)){res.status(440).end();return;}
  try{
    const body = await loadJsonBody(req)
    await setSpaceInfo(req.session.uid, body.space_id, body.info_id, body.info, body.language);
    res.send(JSON.stringify({status:'OK'}));
  }catch(err){
    logger.error(err);
  }
});

app.post('/getspcinf', async (req, res) => {
  if(!checkUserSession(req)){res.status(440).end();return;}
  try{
    const body = await loadJsonBody(req)
    var rtn = await getSpaceInfo(req.session.uid, body.space_id, body.info_id, body.language);
    res.send(rtn?JSON.stringify({status:'OK', info:rtn}):JSON.stringify({status:'error'}));
  }catch(err){
    logger.error(err);
  }
});

app.post('/getreguser', async (req, res) => {
  try{
    const body = await loadJsonBody(req)
    const user = await getRegUserBySession(body.session);
    res.send(user?JSON.stringify({status:'OK', user:user}):JSON.stringify({status:'error'}));
  }catch(err){
    logger.error(err);
  }
});

app.post('/listmgrs', async (req, res) => {
  const body = await loadJsonBody(req)
  
  if(!body.email || !(await validSession(body.session_id))){
    res.send('');
    return;
  }

  const search_where = body.search && body.search.length >0 ? ' user_email like "%'+body.search+'%" ':' 1=1 '
  
  const connection = await connectDB();

  try {
    var results = await new Promise((resolve, reject) => {
      connection.query(SqlString.format('select u.user_id, role, g.created, u.user_email, g.group_id from user_groups g, users u '+
          'where g.user_id = u.user_id and u.user_email<>? '+
          'and group_id in (select group_id from user_groups, users where users.user_email=? and users.user_id=user_groups.user_id and role=0 and users.deleted is null and user_groups.deleted is null) '+
          'and '+search_where+' and u.deleted is null and g.deleted is null limit 1000',
          [body.email, body.email]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    })
    var resp_body = {status:'OK'}
    var users = []
    if(results && 0 < results.length){
      const urlObject = new URL(process.env.REG_LINK_PREFIX);
      const hostName = urlObject.hostname;

      const portalResilt = await new Promise((resolve, reject) => {
        connection.query(SqlString.format('select domain_id from domains where domain = ? and deleted is null',
            [hostName]),
            (error, results) => {
              if (error) reject(error)
              resolve(results)
            })
      })
      resp_body['domain_id'] = portalResilt[0].domain_id
      resp_body['domain'] = hostName
      for(const row of results){
        users.push({user_id:row.user_id, role:row.role, created:row.created, user_email:row.user_email, group_id:row.group_id})
      }
    }
    
    resp_body['users'] = users
    res.send(JSON.stringify(resp_body))
  }catch (err) {
    logger.error('DB err: '+err)
    res.send({status:'error'});
  }
  finally {
    if(connection)connection.release()
  }
});

/** Hot reload support
 * 1. Add url if in module.exports.processReq
 * 2. Call function as module.exports.doTest
 * 3. Add function as module.exports.doTest
 */
app.post('/api/*', async (req, res) => {await processModule.processReq(req, res);});

module.exports.processReq =  async (req, res) =>{
  if(!checkUserSession(req)){res.status(440).end();return;}

  const url = req.url;

  if(url == '/api/test'){
    const rtn = await processModule.doTest();
    if(rtn)res.send(JSON.stringify({status:'OK', msg:'Test OK-A'}));
    else res.send(JSON.stringify({status:'error'}));
  }
}

/** Process functions and DB access functions */

module.exports.doTest = async () => {
  return true;
}

/** End of hot reload support */

/** DB access functions without hot reload support*/
async function connectDB(){
  var rtn = null;
  try {
  rtn = await new Promise((resolve, reject) => {
    mysql_pool.getConnection((error, connection) => {
      if (error) reject(error)
      resolve(connection)
    })
  });
  }catch (err) {
  logger.error('Connecting DB err: '+err)
  }
  return rtn;
}

async function getUserInfo(uid){
  var rtn = null;
  if(typeof uid != "number") return rtn;

  const connection = await connectDB();
  try {
    var results = await new Promise((resolve, reject) => {
      connection.query(SqlString.format('select account_name, account_email from accounts where account_id = ? and deleted is null ',
          [uid]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    })
    if(results.length > 0){
      rtn = {uid: uid, email:results[0].account_email, name:results[0].account_name};
    }
  }catch (err) {
    logger.error('DB err: '+err)
  }
  finally {
    if(connection)connection.release()
  }
  return rtn;

}

async function getRegUserBySession(session_id){
  var rtn = null;
  if(typeof session_id != "string") return rtn;

  const connection = await connectDB();
  try {
    var results = await new Promise((resolve, reject) => {
      connection.query(SqlString.format('select username from registration_sessions where session_id = ? ',
          [session_id]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    })
    if(results.length > 0){
      rtn = results[0].username;
    }
  }catch (err) {
    logger.error('DB err: '+err)
    rtn = null;
  }
  finally {
    if(connection)connection.release()
  }
  return rtn;
}

async function login(email, pwd) {
  let rtn = null;
  const connection = await connectDB();
  try {
    var results = await new Promise((resolve, reject) => {
      connection.query(SqlString.format('select account_id, account_name from accounts where account_email = ? and account_pw = ? and deleted is null ',
          [email, pwd]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    })
    if(results.length > 0){
      rtn = {id:results[0].account_id, name:results[0].account_name};
    }
  }catch (err) {
    logger.error('DB err: '+err)
  }
  finally {
    if(connection)connection.release()
  }

  return rtn;
}

async function sendEmail(email, subject, message) {
  let rtn=false;
  const nodemailer = require('nodemailer');
  const transporter = nodemailer.createTransport({
    name: process.env.SMTP_HOST,
    host: process.env.SMTP_HOST,
    port: process.env.SMTP_PORT,
    secure: true,
    auth: {
      user: process.env.SMTP_USER,
      pass: process.env.SMTP_PASS
    },
    tls: {
      rejectUnauthorized: false,
    }
  });
  const mailOptions = {
    from: process.env.SMTP_FROM,
    to: email,
    subject: subject,
    html: message
  };
  const result = await transporter.sendMail(mailOptions);
  if(result.response.startsWith('250')){    
    logger.info('Email sent: ' + result.response + '; email=' + mailOptions.to 
          + '; subject=' + mailOptions.subject + '; message=' + mailOptions.text);
    rtn = true;
  }else{
    logger.error(result.response);
    rtn = false;
  }
  return rtn;
}

function makeMessage(msg_key, language, args = null) {
  let msgMap = appMsgs.get(language);
  if (!msgMap) msgMap = appMsgs.get('en-US');
  msg = msgMap.get(msg_key);
  if (args) {
    for (let i = 0; i < args.length; i++) {
      msg = msg.replaceAll('{' + i + '}', args[i]);
    }
  }
  return msg;
}

async function renameSpace(space_id, space_name, uid, language) {
  if(typeof space_id != "string" || typeof space_name != "string" || 
    typeof uid != "number" || typeof language != "string") return;

  var rtn = null;
  const connection = await connectDB();
  try {
    var results = await new Promise((resolve, reject) => {
      connection.query(SqlString.format('select space_name from spaces where space_id = ? and deleted is null ',
          [space_id]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    })
    var jsonName={};
    if(results.length > 0){
      jsonName = JSON.parse(results[0].space_name);
    }
    jsonName[language] = space_name;

    await new Promise((resolve, reject) => {
      connection.query(SqlString.format('update spaces set space_name = ? where space_id = ? and deleted is null ',
          [JSON.stringify(jsonName), space_id]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    })
    rtn = space_id;
  }catch (err) {
    logger.error('DB err: '+err)
    rtn = null;
  }finally {
    if(connection)connection.release()
  }
  return rtn;
}

async function checkSameSpaceName(uid, space_id, space_name){
  if(typeof uid != "number" || typeof space_name != "string") return true;
  var rtn = true;
  const connection = await connectDB();;
  try {
    const txt = '%"'+space_name+'"%';
    var results = await new Promise((resolve, reject) => {
      connection.query(SqlString.format('select s.space_id from spaces s, account_permission a '+
        'where s.space_id=a.space_id and a.account_id = ? and s.space_id <> ? and s.space_name like ? and s.deleted is null and a.deleted is null ',
          [uid, space_id, txt]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    })
    if(results.length == 0){
      rtn = false;
    }
  }catch (err) {
    logger.error('DB err: '+err)
    rtn = true;
  }finally {
    if(connection)connection.release()
  }
  
  return rtn;
}

async function checkSameTmplName(template_id, template_name){
  if(typeof template_name != "string") return true;
  var rtn = true;
  const connection = await connectDB();;
  try {
    const txt = '%"'+template_name+'"%';
    var results = await new Promise((resolve, reject) => {
      connection.query(SqlString.format('select spacetemp_id from space_templates where spacetemp_id<>? and spacetemp_name like ? and deleted is null ',
          [template_id, txt]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    })
    if(results.length == 0){
      rtn = false;
    }
  }catch (err) {
    logger.error('DB err: '+err)
    rtn = true;
  }finally {
    if(connection)connection.release()
  }
  
  return rtn;
}

async function checkSameInfoName(template_id, info_id, info_name){
  if(typeof template_id != "string" || typeof info_name != "string") return true;
  var rtn = true;
  const connection = await connectDB();;
  try {
    const txt = '%"'+info_name+'"%';
    var results = await new Promise((resolve, reject) => {
      connection.query(SqlString.format('select * from spacetplt_info_blocks where spacetemp_id = ? and info_block_id<>? and block_name like ? and deleted is null ',
          [template_id, info_id, txt]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    })
    if(results.length == 0){
      rtn = false;
    }
  }catch (err) {
    logger.error('DB err: '+err)
    rtn = true;
  }finally {
    if(connection)connection.release()
  }

  return rtn;
}

async function inviteExistAccount(invite_session_id){
  var rtn = {};
  if(typeof invite_session_id != "string") return rtn;

  const connection = await connectDB();
  try {
    var results = await new Promise((resolve, reject) => {
      connection.query(SqlString.format('select n.user_email, a.account_id from invite_user_sessions n left join accounts a on a.account_email=n.user_email and a.deleted is null '+
        'where n.session_id = ? ',
          [invite_session_id]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    })
    if(results.length > 0){
      if(results[0].account_id && results[0].account_id > 0)rtn["exist"] = true;
      else{
        rtn["exist"] = false;
        rtn["email"] = results[0].user_email;
      }
    }else rtn = {};
  }catch (err) {
    logger.error('DB err: '+err)
    rtn = {};
  }
  finally {
    if(connection)connection.release()
  }
  return rtn;
}

async function checkSameNickName(nickname){
  if(typeof nickname != "string") return true;
  var rtn = true;
  const connection = await connectDB();
  try {
    const txt = '%"'+nickname+'"%';
    var results = await new Promise((resolve, reject) => {
      connection.query(SqlString.format('select * from accounts where account_name like ? and deleted is null ',
          [txt]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          });
    });
    if(results.length == 0){
      rtn = false;
    }
  }catch (err) {
    logger.error('DB err: '+err)
    rtn = true;
  }finally {
    if(connection)connection.release()
  }

  return rtn;
}

async function setSpaceInfo(uid, space_id, info_id, info, language) {
  if(typeof uid != "number" || typeof space_id != "string" || typeof info_id != "string" 
    || typeof info != "string" || typeof language != "string") return;
  
  const connection = await connectDB();
  try {
    var results = await new Promise((resolve, reject) => {
      connection.query(SqlString.format('select * from accspace_info where account_id = ? and space_id = ? and info_id=? and deleted is null ',
          [uid, space_id, info_id]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    })

    if(results.length == 0){
      var json = {};
      json[language] = info;
      await new Promise((resolve, reject) => {
        connection.query(SqlString.format('insert into accspace_info(account_id, space_id, info_id, info) values(?,?,?,?) ',
            [uid, space_id, info_id, JSON.stringify(json)]),
            (error, results) => {
              if (error) reject(error)
              resolve(results)
            })
      })
    }else {
      var json = JSON.parse(results[0].info);
      json[language] = info;
      await new Promise((resolve, reject) => {
        connection.query(SqlString.format('update accspace_info set info = ? where account_id = ? and space_id = ? and info_id=? and deleted is null ',
            [JSON.stringify(json), uid, space_id, info_id]),
            (error, results) => {
              if (error) reject(error)
              resolve(results)
            })
      })
    }
  }catch (err) {
    logger.error('DB err: '+err)
  }
  finally {
    if(connection)connection.release()
  }
}

function parseLangText(text, language){
  try {
    var json = JSON.parse(text);
    if(typeof json!= "object") return text;
    else if(json[language]!=null) return json[language];
    else{
      var rtn = json['en-US']
      var keys = Object.keys(json);
      if(!rtn && keys.length > 0)rtn = json[keys[0]];
      return rtn?rtn:'';
    }
  } catch (e) {
      return text;
  }
}

async function changeRoleUser(mgr_id, space_id, uid, role) {
if(typeof mgr_id != "number" || typeof space_id != "number" || typeof uid != "number" || typeof role != "number") return false;
  
  const connection = await connectDB();
  try {
    var results = await new Promise((resolve, reject) => {
      connection.query(SqlString.format('select role from space_users where account_id = ? and space_id = ? and deleted is null ',
          [mgr_id, space_id]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    })
    if(results.length == 0 || results[0].role != 1)return false;

    await new Promise((resolve, reject) => {
      connection.query(SqlString.format('update space_users set role = ? where account_id = ? and space_id = ? and deleted is null ',
          [role, uid, space_id]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    })
    return true;
  }catch (err) {
    logger.error('DB err: '+err)
  }
  finally {
    if(connection)connection.release()
  }
  return false;
}

async function getSpaceInfo(uid, space_id, info_id, language) {
  if(typeof uid != "number" || typeof space_id != "string" || typeof info_id != "string" || typeof language != "string") return;
  
  const connection = await connectDB();
  try {
    var results = await new Promise((resolve, reject) => {
      connection.query(SqlString.format('select * from accspace_info where account_id = ? and space_id = ? and info_id=? and deleted is null ',
          [uid, space_id, info_id]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    })

    if(results.length > 0){
      return parseLangText(results[0].info, language);
    }else return '';
  }catch (err) {
    logger.error('DB err: '+err)
  }
  finally {
    if(connection)connection.release()
  }
}

async function makeRegSession(email) {
  if(typeof email != "string") return null;
  const session_id = uuidv4();
  const connection = await connectDB();
  try {
    const result = await new Promise((resolve, reject) => {
      connection.query(SqlString.format('insert into registration_sessions(session_id, username) values(?,?) ',
          [session_id, email]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    });
  }catch (err) {
    logger.error('DB err: '+err)
    session_id = null;
  }finally {
    if(connection)connection.release()
  }
  return session_id;
}

async function setUser(reg_session_id, invt_session_id, user_name, password, language) {
  if((invt_session_id &&typeof invt_session_id != "string") || (reg_session_id &&typeof reg_session_id != "string") || 
    typeof user_name != "string" || typeof password != "string" || typeof language != "string") return null;

  var rtn = -1;

  const connection = await connectDB();

  try {
    var sql;
    if(reg_session_id)sql = SqlString.format('select username from registration_sessions where session_id = ? ', [reg_session_id]);
    else if(invt_session_id)sql = SqlString.format('select user_email from invite_user_sessions where session_id = ? ', [invt_session_id]);
    var result = await new Promise((resolve, reject) => {
      connection.query(sql,
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    });
    
    if(result.length > 0){
      const email = result[0].username?result[0].username:result[0].user_email;
      var result = await new Promise((resolve, reject) => {
        connection.query(SqlString.format('select account_id from accounts where account_email = ? and deleted is null', 
            [email]),
            (error, results) => {
              if (error) reject(error)
              resolve(results)
            })
      });
      const unmtxt = await makeJsonTxt(JSON.stringify({}), user_name, language);
      if(result.length > 0){
        rtn = result[0].account_id;
        result = await new Promise((resolve, reject) => {
          connection.query(SqlString.format('UPDATE accounts set account_email = ?, account_pw = ?, account_name = ? where account_id = ?', 
            [email, password, unmtxt, rtn]),
            (error, results) => {
              if (error) reject(error)
              resolve(results)
            })
        })
      }else{
        result = await new Promise((resolve, reject) => {
          connection.query(SqlString.format('INSERT into accounts(account_email, account_pw, account_name) values(?,?,?) ', 
            [email, password, unmtxt]),
            (error, results) => {
              if (error) reject(error)
              resolve(results)
            })
        })
        rtn = result.insertId;
      }
      if(reg_session_id){
        await new Promise((resolve, reject) => {
          connection.query(SqlString.format('delete from registration_sessions where session_id = ? ',
              [reg_session_id]),
              (error, results) => {
                if (error) reject(error)
                resolve(results)
              })
        });

      }
    }
    }catch (err) {
      logger.error('DB err: '+err)
    }
    finally {
      if(connection)connection.release()
    }
    return rtn;
}

async function setSpaceUser(user_id, role, space_id){
  rtn = -1;
  if(typeof user_id != "number" || typeof role != "number" || typeof space_id != "number") return rtn;

  const connection = await connectDB();

    try {
      var results = await new Promise((resolve, reject) => {
        connection.query(SqlString.format('select account_id from space_users where account_id = ? and space_id =? and deleted is null', 
            [user_id, space_id]),
            (error, results) => {
              if (error) reject(error)
              resolve(results)
            })
      })
      
      if(results.length == 0){
        await new Promise((resolve, reject) => {
          connection.query(SqlString.format('INSERT into space_users( account_id, role, space_id) values(?, ?, ?) ', 
            [user_id, role, space_id]),
            (error, results) => {
              if (error) reject(error)
              resolve(results)
            })
        })
        rtn = results.insertId;
      }else{
        await new Promise((resolve, reject) => {
          connection.query(SqlString.format('update space_users set role = ? where account_id = ? and space_id = ? and deleted is null', 
            [role, user_id, space_id]),
            (error, results) => {
              if (error) reject(error)
              resolve(results)
            })
        })
      }

      results = await new Promise((resolve, reject) => {
        connection.query(SqlString.format('select account_id from account_permission where account_id = ? and space_id =? and deleted is null', 
            [user_id, space_id]),
            (error, results) => {
              if (error) reject(error)
              resolve(results)
            })
      })

      if(results.length == 0){
        await new Promise((resolve, reject) => {
          connection.query(SqlString.format('INSERT into account_permission( account_id, space_id, is_admin ) values(?,?,?) ', 
              [user_id, space_id, 0]),
              (error, results) => {
                if (error) reject(error)
                resolve(results)
              })
          })
        }
    }catch (err) {
      logger.error('DB err: '+err)
    }
    finally {
      if(connection)connection.release()
    }
    return rtn;
}

async function insertSpace(spacetemp_id, space_name, uid){
  var rtn = null;
  if(typeof spacetemp_id != "string" || typeof uid != "number" || typeof space_name != "object") return rtn;

  const connection = await connectDB();
  try {
  var result = await new Promise((resolve, reject) => {
    connection.query(SqlString.format('INSERT into spaces( spacetemp_id, space_name ) values(?,?) ', 
        [spacetemp_id, JSON.stringify(space_name)]),
        (error, results) => {
          if (error) reject(error)
          resolve(results)
        })
    })
   
  const space_id = result.insertId
  if(space_id > 0){
    await new Promise((resolve, reject) => {
      connection.query(SqlString.format('INSERT into account_permission( account_id, space_id, is_admin ) values(?,?,?) ', 
          [uid, result.insertId, 1]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
      })

    rtn = space_id;
  }
  }catch (err) {
    logger.error('DB err: '+err)
  }
  finally {
    if(connection)connection.release()
  }

  return rtn;
}

async function insertAccountPrompt(uid, space_id, prompt, language){
  if(typeof space_id != "string" || typeof uid != "number" || typeof prompt != "string" 
    || typeof language != "string") return rtn;

  const connection = await connectDB();
  try {
    var json = {};
    json[language] = prompt;
    const txt = JSON.stringify(json);
    var result = await new Promise((resolve, reject) => {
    connection.query(SqlString.format('INSERT into accspace_prompts( account_id, space_id, prompt) values(?,?,?) ', 
        [uid, space_id, txt]),
        (error, results) => {
          if (error) reject(error)
          resolve(results)
        })
    });
  }catch (err) {
    logger.error('DB err: '+err)
  }
  finally {
    if(connection)connection.release()
  }
}

async function insertTemplateBasic(uid, name, cmt, language){
  if(typeof name != "string" || typeof uid != "number" || typeof cmt != "string" 
    || typeof language != "string") return rtn;

  var rtn = -1;
  const connection = await connectDB();
  try {
    var jsonName = {};
    jsonName[language] = name;
    var jsonCmt = {};
    jsonCmt[language] = cmt;
    var result = await new Promise((resolve, reject) => {
    connection.query(SqlString.format('INSERT into space_templates( account_id, spacetemp_name, spacetemp_intro) values(?,?,?) ', 
        [uid, JSON.stringify(jsonName), JSON.stringify(jsonCmt)]),
        (error, results) => {
          if (error) reject(error)
          resolve(results)
        })
    });

    if(result.insertId > 0){
      rtn = result.insertId;
    }
  }catch (err) {
    logger.error('DB err: '+err)
  }
  finally {
    if(connection)connection.release()
  }
  return rtn;
}

async function updateTemplateBasic(uid, tmpl_id, name, cmt, language){
  if(typeof uid != "number" || typeof name != "string" || typeof tmpl_id != "string" || typeof cmt != "string"
    || typeof language != "string") return rtn;

  const connection = await connectDB();;
  try {
    var result = await new Promise((resolve, reject) => {
    connection.query(SqlString.format('select spacetemp_name, spacetemp_intro from space_templates where spacetemp_id = ? and deleted is null', 
        [tmpl_id]),
        (error, results) => {
          if (error) reject(error)
          resolve(results) 
        })
    });
    var jsonName = {}; var jsonCmt = {};
    if(result.length > 0){
      jsonName = JSON.parse(result[0].spacetemp_name);
      jsonCmt = JSON.parse(result[0].spacetemp_intro);
    }
    jsonName[language] = name;
    jsonCmt[language] = cmt;
    var result = await new Promise((resolve, reject) => {
    connection.query(SqlString.format('update space_templates set spacetemp_name = ?, spacetemp_intro = ? where account_id = ? and spacetemp_id = ? and deleted is null', 
        [JSON.stringify(jsonName), JSON.stringify(jsonCmt), uid, tmpl_id]),
        (error, results) => {
          if (error) reject(error)
          resolve(results)
        })
    });
  }catch (err) {
    logger.error('DB err: '+err)
  }
  finally {
    if(connection)connection.release()
  }
}

async function unassignSpaceUser(mgr_id, space_id, user_id){
  var rtn = false;
  if(typeof mgr_id != "number" || typeof space_id != "number" || typeof user_id != "number") return rtn;

  const connection = await connectDB();;
  try {
    var results = await new Promise((resolve, reject) => {
      connection.query(SqlString.format('select role from space_users where account_id = ? and space_id = ? and deleted is null ',
          [mgr_id, space_id]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    })
    if(results.length == 0 || results[0].role != 1)return false;

    var result = await new Promise((resolve, reject) => {
    connection.query(SqlString.format('update space_users set deleted = now() where account_id = ? and space_id = ? and deleted is null', 
        [user_id, space_id]),
        (error, results) => {
          if (error) reject(error)
          resolve(results)
        })
    });

    result = await new Promise((resolve, reject) => {
      connection.query(SqlString.format('update account_permission set deleted = now() where account_id = ? and space_id = ? and deleted is null', 
          [user_id, space_id]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
      });
    rtn = true;
  }catch (err) {
    logger.error('DB err: '+err)
  }
  finally {
    if(connection)connection.release()
  }
  return rtn;
}

async function updateTemplatePublic(uid,tmpl_id, is_public){
  if(typeof uid != "number" || typeof is_public != "number" || typeof tmpl_id != "string") return rtn;

  const connection = await connectDB();
  try {
    var result = await new Promise((resolve, reject) => {
    connection.query(SqlString.format('update space_templates set shared = ? where account_id = ? and spacetemp_id = ? and deleted is null', 
        [is_public, uid, tmpl_id]),
        (error, results) => {
          if (error) reject(error)
          resolve(results)
        })
    });
  }catch (err) {
    logger.error('DB err: '+err)
  }
  finally {
    if(connection)connection.release()
  }
}

async function deleteTemplateInfo(uid, tmpl_id, info_id){
  if(typeof uid != "number" || typeof tmpl_id != "string" || typeof info_id != "string") return false;

  const connection = await connectDB();;
  try {
    var result = await new Promise((resolve, reject) => {
    connection.query(SqlString.format('select * from space_templates where account_id = ? and spacetemp_id = ? and deleted is null', 
        [uid, tmpl_id]),
        (error, results) => {
          if (error) reject(error)
          resolve(results) 
        })
    });
    if(result.length == 0){
      return false;
    }

    result = await new Promise((resolve, reject) => {
    connection.query(SqlString.format('update spacetplt_info_blocks set deleted = now() where info_block_id = ? and deleted is null', 
        [info_id]),
        (error, results) => {
          if (error) reject(error)
          resolve(results)
        })
    });
  }catch (err) {
    logger.error('DB err: '+err)
    return false;
  }
  finally {
    if(connection)connection.release()
  }
  return true;
}

async function deleteTemplatePrompt(uid, tmpl_id, prompt_id){
  if(typeof uid != "number" || typeof tmpl_id != "string" || typeof prompt_id != "string") return false;

  const connection = await connectDB();;
  try {
    var result = await new Promise((resolve, reject) => {
    connection.query(SqlString.format('select * from space_templates where account_id = ? and spacetemp_id = ? and deleted is null', 
        [uid, tmpl_id]),
        (error, results) => {
          if (error) reject(error)
          resolve(results) 
        })
    });
    if(result.length == 0){
      return false;
    }

    result = await new Promise((resolve, reject) => {
    connection.query(SqlString.format('update spacetplt_prompts set deleted = now() where prompt_id = ? and deleted is null', 
        [prompt_id]),
        (error, results) => {
          if (error) reject(error)
          resolve(results)
        })
    });
  }catch (err) {
    logger.error('DB err: '+err)
    return false;
  }
  finally {
    if(connection)connection.release()
  }
  return true;

}

async function deleteSpace(uid, space_id){
  if(typeof uid != "number" || typeof space_id != "string") return false;

  var rtn = false;
  const connection = await connectDB();;
  
  try {
    await new Promise((resolve, reject) => {
      connection.beginTransaction();
      resolve();
    });

    var results = await new Promise((resolve, reject) => {
      connection.query(SqlString.format('select s.space_id from spaces s, account_permission a '+
        'where s.space_id=a.space_id and a.account_id = ? and s.space_id = ? and s.deleted is null and a.deleted is null ',
          [uid, space_id]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    })
    if(results.length == 0){
      rtn = false;
    }

    result = await new Promise((resolve, reject) => {
    connection.query(SqlString.format('update spaces set deleted = now() where space_id = ? and deleted is null', 
        [space_id]),
        (error, results) => {
          if (error) reject(error)
          resolve(results)
        })
    });
    result= await new Promise((resolve, reject) => {
    connection.query(SqlString.format('update account_permission set deleted = now() where space_id = ? and deleted is null', 
        [space_id]),
        (error, results) => {
          if (error) reject(error)
          resolve(results)
        })
    });
    result= await new Promise((resolve, reject) => {
    connection.query(SqlString.format('update accspace_prompts set deleted = now() where space_id = ? and deleted is null', 
        [space_id]),
        (error, results) => {
          if (error) reject(error)
          resolve(results)
        })
    });
    result = await new Promise((resolve, reject) => {
    connection.query(SqlString.format('update accspace_info set deleted = now() where space_id = ? and deleted is null', 
        [space_id]),
        (error, results) => {
          if (error) reject(error)
          resolve(results)
        })
    });
    result = await new Promise((resolve, reject) => {
    connection.query(SqlString.format('update accspace_sessions set deleted = now() where space_id = ? and deleted is null', 
        [space_id]),
        (error, results) => {
          if (error) reject(error)
          resolve(results)
        })
    });
    result = await new Promise((resolve, reject) => {
    connection.query(SqlString.format('update accspace_history set deleted = now() where space_id = ? and deleted is null', 
        [space_id]),
        (error, results) => {
          if (error) reject(error)
          resolve(results)
        })
    });
    
    await new Promise((resolve, reject) => {
      connection.commit();
      resolve();
    });
    rtn = true;
  }catch (err) {
    await connection.rollback();
    logger.error('DB err: '+err)
    rtn = false;
  }
  finally {
    if(connection)connection.release()
  }
  return rtn;
}


async function insertTemplateInfo(uid, tmpl_id, name, cmt, language){
  var rtn = -1;

  if(typeof uid != "number" || typeof name != "string" || typeof tmpl_id != "string" || typeof cmt != "string"
    || typeof language != "string") return rtn;

  const connection = await connectDB();;
  try {
    //Only the creator can update the template
    var result = await new Promise((resolve, reject) => {
      connection.query(SqlString.format('select * from space_templates where account_id = ? and spacetemp_id = ? and deleted is null', 
          [uid, tmpl_id]),
          (error, results) => {
            if (error) reject(error)
            resolve(results) 
          })
      });
    if(result.length == 0){
      return rtn;
    }

    var jsonName = {};
    jsonName[language] = name;
    var cmttxt = '';
    if(cmt && cmt.length > 0){
      var jsonCmt = {};
      jsonCmt[language] = cmt;
      cmttxt = JSON.stringify(jsonCmt);
    }
    
    var result = await new Promise((resolve, reject) => {
    connection.query(SqlString.format('INSERT into spacetplt_info_blocks( spacetemp_id, block_name, block_cmmt) values(?,?,?) ', 
        [tmpl_id, JSON.stringify(jsonName), cmttxt]),
        (error, results) => {
          if (error) reject(error)
          resolve(results)
        })
    });

    if(result.insertId > 0){
      rtn = result.insertId;
    }
  }catch (err) {
    logger.error('DB err: '+err)
    rtn = -1;
  }finally {
    if(connection)connection.release()
  }
  return rtn;
}

async function insertTemplatePrompt(uid, tmpl_id, prompt_hint, prompt_head, prompt_show, prompt_foot, language){
  var rtn = -1;
  if(typeof uid != "number" || typeof tmpl_id != "string" || typeof prompt_hint != "string" || typeof prompt_head != "string"
    || typeof prompt_show != "string" || typeof prompt_foot != "string" || typeof language != "string") return rtn;

  const connection = await connectDB();
  try {
    //Only the creator can update the template
    var result = await new Promise((resolve, reject) => {
      connection.query(SqlString.format('select * from space_templates where account_id = ? and spacetemp_id = ? and deleted is null', 
          [uid, tmpl_id]),
          (error, results) => {
            if (error) reject(error)
            resolve(results) 
          })
      });
    if(result.length == 0){
      return rtn;
    }

    const hint = await makeJsonTxt(JSON.stringify({}), prompt_hint, language);
    const head = await makeJsonTxt(JSON.stringify({}), prompt_head, language);
    const show = await makeJsonTxt(JSON.stringify({}), prompt_show, language);
    const foot = await makeJsonTxt(JSON.stringify({}), prompt_foot, language);
    var result = await new Promise((resolve, reject) => {
    connection.query(SqlString.format('INSERT into spacetplt_prompts(spacetemp_id, prompt_hint, prompt_head, prompt_show, prompt_foot) values(?,?,?,?,?) ', 
        [tmpl_id, hint, head, show, foot]),
        (error, results) => {
          if (error) reject(error)
          resolve(results)
        })
    });
    if(result.insertId > 0){
      rtn = result.insertId;
    }
  }catch (err) {
    logger.error('DB err: '+err)
    rtn = -1;
  }
  finally {
    if(connection)connection.release()
  }
  return rtn;
}

async function updateTemplateInfo(uid, tmpl_id, info_id, name, cmt, language){
  if(typeof uid != "number" || typeof name != "string" || typeof tmpl_id != "string" || typeof cmt != "string"
    || typeof language != "string") return rtn;

  var rtn = -1;

  const connection = await connectDB();;
  try {
    //Only the creator can update the template
    var result = await new Promise((resolve, reject) => {
    connection.query(SqlString.format('select * from space_templates where account_id = ? and spacetemp_id = ? and deleted is null', 
        [uid, tmpl_id]),
        (error, results) => {
          if (error) reject(error)
          resolve(results) 
        })
    });
    if(result.length == 0){
      return rtn;
    }

    result = await new Promise((resolve, reject) => {
    connection.query(SqlString.format('select block_name, block_cmmt from spacetplt_info_blocks where info_block_id = ? and deleted is null', 
        [info_id]),
        (error, results) => {
          if (error) reject(error)
          resolve(results) 
        })
    });
    var jsonName = {}; var jsonCmt = {};
    if(result.length > 0){
      jsonName = JSON.parse(result[0].block_name);
      if(result[0].block_cmmt&&result[0].block_cmmt.length>0)jsonCmt = JSON.parse(result[0].block_cmmt);
    }
    jsonName[language] = name;
    jsonCmt[language] = cmt;
    result = await new Promise((resolve, reject) => {
    connection.query(SqlString.format('update spacetplt_info_blocks set block_name = ?, block_cmmt = ? where info_block_id = ? and deleted is null', 
        [JSON.stringify(jsonName), JSON.stringify(jsonCmt) , info_id]),
        (error, results) => {
          if (error) reject(error)
          resolve(results)
        })
    });
  }catch (err) {
    logger.error('DB err: '+err)
  }
  finally {
    if(connection)connection.release()
  }

}

async function updateTemplatePrompt(uid, tmpl_id, prompt_id, prompt_hint, prompt_head, prompt_show, prompt_foot, language){
  var rtn = false;
  if(typeof uid != "number" || typeof tmpl_id != "string" || typeof prompt_hint != "string" || typeof prompt_head != "string"
    || typeof prompt_show != "string" || typeof prompt_foot != "string" || typeof language != "string") return rtn;

  const connection = await connectDB();;
  try {
    //Only the creator can update the template
    var result = await new Promise((resolve, reject) => {
    connection.query(SqlString.format('select * from space_templates where account_id = ? and spacetemp_id = ? and deleted is null', 
        [uid, tmpl_id]),
        (error, results) => {
          if (error) reject(error)
          resolve(results) 
        })
    });
    if(result.length == 0){
      return rtn;
    }

    result = await new Promise((resolve, reject) => {
    connection.query(SqlString.format('select prompt_hint, prompt_head, prompt_show, prompt_foot from spacetplt_prompts '+
      'where prompt_id = ? and deleted is null', 
        [prompt_id]),
        (error, results) => {
          if (error) reject(error)
          resolve(results) 
        })
    });
    var hint = '', head = '', show = '', foot = '';
    if(result.length > 0){
      hint = result[0].prompt_hint;
      head = result[0].prompt_head;
      show = result[0].prompt_show;
      foot = result[0].prompt_foot;
    }

    hint = await makeJsonTxt(hint, prompt_hint, language);
    head = await makeJsonTxt(head, prompt_head, language);
    show = await makeJsonTxt(show, prompt_show, language);
    foot = await makeJsonTxt(foot, prompt_foot, language);
    result = await new Promise((resolve, reject) => {
    connection.query(SqlString.format('update spacetplt_prompts set prompt_hint = ?, prompt_head = ?, prompt_show = ?, prompt_foot = ? where prompt_id = ? and deleted is null', 
        [hint, head, show, foot, prompt_id]),
        (error, results) => {
          if (error) reject(error)
          resolve(results)
        })
    });
    rtn = true;   
  }catch (err) {
    logger.error('DB err: '+err)
    rtn = false;
  }
  finally {
    if(connection)connection.release()
  }
  return rtn;
}

async function insertAccountHistory(uid, space_id, accspc_session_id, prompt, answer, language){
    if(typeof space_id != "string" || typeof uid != "number" || typeof accspc_session_id != "string" 
      || typeof prompt != "string" || typeof answer != "string" || typeof language != "string") return;

    const connection = await connectDB();

    try {
      var json = {};
      json[language] = prompt;
      const txt = JSON.stringify(json);
      json = {};
      json[language] = answer;
      const answerTxt = JSON.stringify(json);
      var result = await new Promise((resolve, reject) => {
      connection.query(SqlString.format('INSERT into accspace_history( account_id, space_id, accspace_sessions_id, prompt, answer) values(?,?,?,?,?) ', 
          [uid, space_id, accspc_session_id, txt, answerTxt]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
      });
      if(result.insertId > 0){
        //update updated column of accspace_sessions
        await new Promise((resolve, reject) => {
          connection.query(SqlString.format('update accspace_sessions set updated = now() where accspace_sessions_id = ? and deleted is null', 
              [accspc_session_id]),
              (error, results) => {
                if (error) reject(error)
                resolve(results)
              })
        });
      }
    }catch (err) {
      logger.error('DB err: '+err)
    }
    finally {
      if(connection)connection.release()
    }

  return;
}

async function insertAccSpaceSession(uid, space_id, prompt, language){
  if(typeof space_id != "string" || typeof uid != "number" || typeof prompt != "string" || typeof language != "string") return rtn;

  accspace_sessions_id = -1;

  const connection = await connectDB();
  var txtName;
  try {
    var json = {};
    json[language] = prompt.substring(0, 30);
    txtName = JSON.stringify(json);
    var result = await new Promise((resolve, reject) => {
    connection.query(SqlString.format('INSERT into accspace_sessions( account_id, space_id, short_name) values(?,?,?) ', 
        [uid, space_id, txtName]),
        (error, results) => {
          if (error) reject(error)
          resolve(results)
        })
    });
    accspace_sessions_id = result.insertId;
  }catch (err) {
    logger.error('DB err: '+err)
  }
  finally {
    if(connection)connection.release()
  }

  return {session_id:accspace_sessions_id, short_name:txtName};
}

async function processInviteMgrSession(invite_session_id){
  if(typeof invite_session_id != "string") return;

  const connection = await connectDB();

  try {
    var results = await new Promise((resolve, reject) => {
      connection.query(SqlString.format('select user_email, role, sender_id, space_id from invite_user_sessions where session_id = ? ', 
        [invite_session_id]),
        (error, results) => {
          if (error) reject(error)
          resolve(results)
        })
    })
    if(results.length > 0){
      const sender_id = results[0].sender_id
      const role = results[0].role
      const user_email = results[0].user_email
      const space_id = results[0].space_id

      results = await new Promise((resolve, reject) => {
        connection.query(SqlString.format('select spcuser_id, role from space_users where account_id = ? and space_id = ? and deleted is null order by created limit 1',
          [sender_id, space_id]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
      })

      if(results.length > 0){
        if(results[0].role == 1)spcuser_id = results[0].spcuser_id 
        else return null
      }else{
        spcuser_id = uuidv4()
        if(0 > setSpaceUser(sender_id, 1, space_id)) return null;//Sender must be mgr
      }

      const new_uid = await findAccountID(user_email)
      if(new_uid < 0) return null

      if(0 > setSpaceUser(new_uid, role, space_id))return null;

      await new Promise((resolve, reject) => {
        connection.query(SqlString.format('delete from invite_user_sessions where session_id = ?',
            [invite_session_id]),
            (error, results) => {
              if (error) reject(error)
              resolve(results)
            })
      })

      return user_email
    }else return null
  }catch (err) {
    logger.error('DB err: '+err)
  }
  finally {
    if(connection)connection.release()
  }
}

async function findAccountID(email){
  if(typeof email != "string") return;
  var rtn = -1;
  const connection = await connectDB();
  try {
    var results = await new Promise((resolve, reject) => {
      connection.query(SqlString.format('select account_id from accounts where account_email = ? and deleted is null ', 
          [email]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    })
    if(results.length > 0){
      rtn = results[0].account_id;
    }
  }catch (err) {
    logger.error('DB err: '+err)
  }finally {
    if(connection)connection.release()
  }
  return rtn;
}

async function listSpaceNames(uid, type=0){//0: all related, 1: include managed, 2: created only
  if(typeof uid != "number") return;

  var rtn = [];
  const connection = await connectDB();
    
  try {
    var results = await new Promise((resolve, reject) => {
      connection.query(
        SqlString.format('select s.space_id, s.space_name from account_permission a, spaces s where a.account_id = ? and a.space_id=s.space_id '+
            ' and is_admin=1 and a.deleted is null and s.deleted is null order by s.created desc ',
          [uid]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    })

    for (const row of results) {
      const rowJson = {};
      rowJson['space_id'] = row.space_id;
      rowJson['space_name'] = row.space_name;
      rtn.push(rowJson);
    }

    if(type == 2) return rtn;

    var role_part;
    switch(type){
      case 1:
        role_part = ' role = 1 ';
        break;
      default:
        role_part = ' 1=1 ';
        break;
      }

      results = await new Promise((resolve, reject) => {
        connection.query(
          SqlString.format('select s.space_id, s.space_name from spaces s, space_users u '+
            'where s.space_id=u.space_id and '+ role_part +' and u.account_id = ? and s.deleted is null and u.deleted is null order by s.created desc ',
            [uid]),
            (error, results) => {
              if (error) reject(error)
              resolve(results)
            })
      })

      for (const row of results) {
        if(rtn.findIndex(x => x.space_id == row.space_id) < 0){
          const rowJson = {};
          rowJson['space_id'] = row.space_id;
          rowJson['space_name'] = row.space_name;
          rtn.push(rowJson);
        }
      }
  }catch (err) {
    logger.error('DB err: '+err)
    rtn = null;
  }
  finally {
    if(connection)connection.release()
  }
  return rtn 
}

async function listTmplNames(uid=-1){
  if(typeof uid != "number") return;

  var rtn = [];
  const connection = await connectDB();
    
  try {
    var results = await new Promise((resolve, reject) => {
      connection.query(
        SqlString.format('select spacetemp_id, spacetemp_name from space_templates where deleted is null and '+
            (uid>=0?' account_id = ? ':' 0 <= ? ')+' order by created desc ',
          [uid]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    })

    for (const row of results) {
      const rowJson = {};
      rowJson['tmpl_id'] = row.spacetemp_id;
      rowJson['tmpl_name'] = row.spacetemp_name;
      rtn.push(rowJson);
    }

  }catch (err) {
    logger.error('DB err: '+err)
    rtn = null;
  }
  finally {
    if(connection)connection.release()
  }
  return rtn 
}

async function listSpaceUsers(uid){
  if(typeof uid != "number") return;

  var rtn = [];
  const connection = await connectDB();
    
  try {
    var sql = 'select s.space_id, s.space_name, u.role, u.account_id, a.account_name, a.account_email '+
            'from space_users u, spaces s, accounts a '+
            'where u.space_id=s.space_id and u.account_id=a.account_id and u.space_id in (select space_id from space_users where account_id=? and deleted is null) '+
            'and u.deleted is null and s.deleted is null and a.deleted is null '+
            'order by s.space_id, s.created desc ';
    var results = await new Promise((resolve, reject) => {
      connection.query(
        SqlString.format(sql,
          [uid]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    })

    //Same space id users into one JSON arry
    var cur_space_id = -1;
    for (const row of results) {
      if(cur_space_id != row.space_id){
        if(cur_space_id >= 0){
          rtn.push(spaceJson);
        }
        cur_space_id = row.space_id;
        var spaceJson = {};
        spaceJson['space_id'] = row.space_id;
        spaceJson['space_name'] = row.space_name;
        spaceJson['users'] = [];
      }
      const userJson = {};
      userJson['account_id'] = row.account_id;
      userJson['account_name'] = row.account_name;
      userJson['account_email'] = row.account_email;
      userJson['role'] = row.role;
      spaceJson['users'].push(userJson);
    }
    if(cur_space_id >= 0){
      rtn.push(spaceJson);
    }
  }catch (err) {
    logger.error('DB err: '+err)
    rtn = null;
  }
  finally {
    if(connection)connection.release()
  }
  return rtn 

}

async function listAccSpcSessions(uid, space_id){
  if(typeof uid != "number" || typeof space_id != "string") return;

  var rtn = [];
  const connection = await connectDB();;
  try {
    var results = await new Promise((resolve, reject) => {
      connection.query(
        SqlString.format('select accspace_sessions_id, short_name from accspace_sessions where deleted is null and '+
            'account_id = ? and space_id = ? order by created desc limit 30',//TODO: paging
          [uid, space_id]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    })

    for (const row of results) {
      const rowJson = {};
      rowJson['id'] = row.accspace_sessions_id;
      rowJson['name'] = row.short_name;
      rtn.push(rowJson);
    }

  }catch (err) {
    logger.error('DB err: '+err)
    rtn = null;
  }
  finally {
    if(connection)connection.release()
  }
  return rtn 

}

async function getSpaceShow(space_id, uid, language){
  if(typeof space_id != "string" || typeof uid != "number" || typeof language != "string") return;

  var rtn = null;
  const connection = await connectDB();
    
  try {
    var results = await new Promise((resolve, reject) => {
      connection.query(
        SqlString.format('select s.space_id, s.space_name, a.permission, a.is_admin, t.spacetemp_id '+
            ' from account_permission a, spaces s, space_templates t '+
            ' where a.account_id = ? and s.space_id=? and a.space_id=s.space_id and s.spacetemp_id=t.spacetemp_id '+
            ' and a.deleted is null and s.deleted is null and t.deleted is null '+
            ' order by s.created desc ',
          [uid, space_id]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    })

    if(results.length > 0){
      const rowJson = {};
      rowJson['space_id'] = results[0].space_id;
      rowJson['space_name'] = parseLangText(results[0].space_name, language);
      rowJson['is_admin'] = results[0].is_admin;
      rowJson['permission'] = results[0].permission;

      const template_id = results[0].spacetemp_id;

      results = await new Promise((resolve, reject) => {
        connection.query(
          SqlString.format('select `info_block_id`, `block_name`, `block_cmmt` from spacetplt_info_blocks '+
              ' where spacetemp_id = ? and deleted is null '+
              ' order by info_block_id ',
            [template_id]),
            (error, results) => {
              if (error) reject(error)
              resolve(results)
            })
      });
      if(results.length > 0){
        const blocks = [];
        for (const row of results) {
          const blockJson = {};
          blockJson['id'] = row.info_block_id;
          blockJson['name'] = parseLangText(row.block_name, language);
          blockJson['cmmt'] = row.block_cmmt?parseLangText(row.block_cmmt, language):'';
          blocks.push(blockJson);
        }
        rowJson['blocks'] = blocks;
      }

      results = await new Promise((resolve, reject) => {
        connection.query(
          SqlString.format('select `prompt_id`, `prompt_hint`, `prompt_show` from spacetplt_prompts '+
              ' where spacetemp_id = ? and deleted is null '+
              ' order by prompt_id ',
            [template_id]),
            (error, results) => {
              if (error) reject(error)
              resolve(results)
            })
      });
      if(results.length > 0){
        const prompts = [];
        for (const row of results) {
          const promptJson = {};
          promptJson['prompt_id'] = row.prompt_id;
          // no head for show: promptJson['prompt_head'] = row.prompt_head?parseLangText(row.prompt_head, language):'';
          promptJson['prompt_hint'] = row.prompt_hint?parseLangText(row.prompt_hint, language):'';
          promptJson['prompt_show'] = row.prompt_show?parseLangText(row.prompt_show, language):'';
          // no foot for show: promptJson['prompt_foot'] = row.prompt_foot?parseLangText(row.prompt_foot, language):'';
          prompts.push(promptJson);
        }
        rowJson['prompts'] = prompts;
      }

      results = await new Promise((resolve, reject) => {
        connection.query(
          SqlString.format('select `prompt` from accspace_prompts where account_id = ? and space_id = ? and deleted is null '+
              ' order by created desc limit 100', //TODO: paging
            [uid, space_id]),
            (error, results) => {
              if (error) reject(error)
              resolve(results)
            })
      });
      if(results.length > 0){
        const prompts = [];
        for (const row of results) {
          prompts.push(row.prompt?parseLangText(row.prompt, language):'');
        }
        rowJson['my_prompts'] = prompts;
      }

      results = await new Promise((resolve, reject) => {
        connection.query(
          SqlString.format('select accspace_sessions_id, short_name from accspace_sessions where deleted is null and '+
              'account_id = ? and space_id = ? order by updated desc limit 30',//TODO: paging
            [uid, space_id]),
            (error, results) => {
              if (error) reject(error)
              resolve(results)
            })
      })
  
      if(results.length > 0){
        const convs = [];
        for (const row of results) {
          const historyJson = {};
          historyJson['id'] = row.accspace_sessions_id;
          historyJson['name'] = row.short_name;
          convs.push(historyJson);
        }
        rowJson['convs'] = convs;
      }

      rtn = rowJson;
    }

  }catch (err) {
    logger.error('DB err: '+err)
    rtn = null;
  }
  finally {
    if(connection)connection.release()
  }
  return rtn 
}

module.exports.listHistory = async (uid, space_id, accspc_session_id, language, limit=20) => {
  if(typeof uid != "number" || typeof space_id != "string" || typeof accspc_session_id != "string" || typeof language != "string") return;

  var rtn = [];
  const connection = await connectDB();;
  try{
  const results = await new Promise((resolve, reject) => {
    connection.query(
      SqlString.format('select prompt, answer, created from accspace_history where account_id = ? and space_id = ? and accspace_sessions_id=? and deleted is null '+
          ' order by created desc limit ?', //TODO: paging
        [uid, space_id, accspc_session_id, limit]),
        (error, results) => {
          if (error) reject(error)
          resolve(results)
        })
  });
  if(results.length > 0){
    const history = [];
    for (const row of results) {
      const askJson = {};
      askJson['prompt'] = row.prompt?parseLangText(row.prompt, language):'';
      askJson['answer'] = row.answer?parseLangText(row.answer, language):'';
      //Format date
      askJson['created'] = date_format.asString('yyyy-MM-dd hh:mm:ss', row.created);
      history.push(askJson);
    }
    rtn = history;
  }
  }catch (err) {
    logger.error('Sys err: '+err)
    rtn = [];
  }finally {
    if(connection)connection.release()
  }

  return rtn;
}

async function listSpaceTemplates(uid, type, page, per_page){
  var rtn = [];

  if(typeof type != "string" || typeof page != "number" || typeof per_page != "number") return rtn;

  const connection = await connectDB();
    
  try {
    if(page < 1) page = 1;
    if(per_page < 1) per_page = 10;
    else if(per_page > 100) per_page = 100;
    var sql;
    if(type == 'myown'){
      sql = SqlString.format('select t.*, a.account_email, a.account_name from space_templates t, accounts a where t.account_id=a.account_id and '+
          ' a.deleted is null and t.deleted is null and t.account_id=? order by created desc limit ? offset ?',
        [uid, per_page, (page-1)*per_page]);
    }else{//public
      sql = SqlString.format('select t.*, a.account_email, a.account_name from space_templates t, accounts a where t.account_id=a.account_id and '+
        ' a.deleted is null and t.deleted is null and shared=1 order by created desc limit ? offset ?',
        [per_page, (page-1)*per_page]);
    }
    var results = await new Promise((resolve, reject) => {
      connection.query(sql,
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    })

    for (const row of results) {
      const rowJson = {};
      rowJson['spacetemp_id'] = row.spacetemp_id;
      rowJson['tenmplate'] = row.tenmplate;
      rowJson['spacetemp_name'] = row.spacetemp_name;
      rowJson['spacetemp_intro'] = row.spacetemp_intro;
      rowJson['creator_name'] = (row.account_name && 0<row.account_name.length)?row.account_name:row.account_email;
      rtn.push(rowJson);
    }

  }catch (err) {
    logger.error('DB err: '+err)
    rtn = [];
  }
  finally {
    if(connection)connection.release()
  }
  return rtn
}

async function getTemplateBasic(uid, template_id, language){
  if(typeof uid != "number" || typeof template_id != "string" || typeof language != "string") return;

  var rtn = null;
  const connection = await connectDB();;
  try {
    var results = await new Promise((resolve, reject) => {
      connection.query(
        SqlString.format('select spacetemp_id, spacetemp_name, spacetemp_intro, shared from space_templates where account_id = ? and spacetemp_id=? and deleted is null ',
          [uid, template_id]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    });
    if(results.length > 0){
      const row = results[0];
      rtn = {};
      rtn['tmpl_id'] = row.spacetemp_id;
      rtn['tmpl_name'] = parseLangText(row.spacetemp_name, language);
      rtn['tmpl_cmt'] = parseLangText(row.spacetemp_intro, language);
      rtn['is_public'] = row.shared;
    }
  }catch (err) {
    logger.error('DB err: '+err)
    rtn = null;
  }
  finally {
    if(connection)connection.release()
  }
  return rtn;
}

async function getTemplateOther(uid, template_id, language){
  if(typeof uid != "number" || typeof template_id != "string" || typeof language != "string") return;

  var rtn = {};
  const connection = await connectDB();;
  try {
    //Only the creator can check the template
    var result = await new Promise((resolve, reject) => {
      connection.query(SqlString.format('select * from space_templates where account_id = ? and spacetemp_id = ? and deleted is null', 
          [uid, template_id]),
          (error, results) => {
            if (error) reject(error)
            resolve(results) 
          })
      });
    if(result.length == 0){
      return rtn;
    }
  
    var results = await new Promise((resolve, reject) => {
      connection.query(
        SqlString.format('select info_block_id, block_name, block_cmmt from spacetplt_info_blocks where spacetemp_id = ? and deleted is null ',
          [template_id]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    });
    if(results.length > 0){
      const blocks = [];
      for (const row of results) {
        const blockJson = {};
        blockJson['id'] = row.info_block_id;
        blockJson['name'] = parseLangText(row.block_name, language);
        blockJson['cmmt'] = row.block_cmmt?parseLangText(row.block_cmmt, language):'';
        blocks.push(blockJson);
      }
      rtn['info_blocks'] = blocks;
    }
    results = await new Promise((resolve, reject) => {
      connection.query(
        SqlString.format('select prompt_id, prompt_hint, prompt_show, prompt_head, prompt_foot from spacetplt_prompts where spacetemp_id = ? and deleted is null ',
          [template_id]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    });
    if(results.length > 0){
      const prompts = [];
      for (const row of results) {
        const promptJson = {};
        promptJson['id'] = row.prompt_id;
        promptJson['hint'] = row.prompt_hint?parseLangText(row.prompt_hint, language):'';
        promptJson['show'] = row.prompt_show?parseLangText(row.prompt_show, language):'';
        promptJson['head'] = row.prompt_head?parseLangText(row.prompt_head, language):'';
        promptJson['foot'] = row.prompt_foot?parseLangText(row.prompt_foot, language):'';
        prompts.push(promptJson);
      }
      rtn['prompts'] = prompts;
    }
  }catch (err) {
    logger.error('DB err: '+err)
    rtn = {};
  }finally {
    if(connection)connection.release()
  }
  return rtn;

}

async function getInfoBlocksContent(uid, space_id, language){
  var rtn = [];
  const connection = await connectDB();
    
  try {
    var results = await new Promise((resolve, reject) => {
      connection.query(
        SqlString.format('select t.block_name, a.info from spacetplt_info_blocks t, accspace_info a where t.info_block_id=a.info_id and '+
          ' a.account_id=? and a.space_id=? and a.deleted is null and t.deleted is null order by a.created ',
          [uid, space_id]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    })

    for (const row of results) {
      const rowJson = {};
      rowJson['block_name'] = parseLangText(row.block_name, language);
      rowJson['info'] = parseLangText(row.info, language);
      rtn.push(rowJson);
    }

  }catch (err) {
    logger.error('DB err: '+err)
    rtn = [];
  }
  finally {
    if(connection)connection.release()
  }
  return rtn
}

async function getPromptHeadFoot(advice_prompt_id, language){
  var rtn = null;
  const connection = await connectDB();
    
  try {
    var results = await new Promise((resolve, reject) => {
      connection.query(
        SqlString.format('select `prompt_head`, `prompt_foot` from spacetplt_prompts where prompt_id=? and '+
          ' deleted is null ',
          [advice_prompt_id]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    })

    if(results.length > 0){
      rtn = {};
      const row = results[0];
      rtn['head'] = row.prompt_head && 0<row.prompt_head.length?parseLangText(row.prompt_head, language):'';
      rtn['foot'] = row.prompt_foot && 0<row.prompt_foot.length?parseLangText(row.prompt_foot, language):'';
    }

  }catch (err) {
    logger.error('DB err: '+err)
    rtn = null;
  }
  finally {
    if(connection)connection.release()
  }
  return rtn
}

async function isManager(uid, spcae_id){
  if(typeof uid != "number" || typeof spcae_id != "string") return;

  var rtn = false;
  const connection = await connectDB();

  try {
    var results = await new Promise((resolve, reject) => {
      connection.query(SqlString.format('select role from space_users where account_id = ? and space_id = ? and deleted is null ',
          [uid, spcae_id]),
          (error, results) => {
            if (error) reject(error)
            resolve(results)
          })
    });
    if(results.length > 0){
      rtn = results[0].role == 1;
    }else{
      //Check if the user is the creator of the space
      results = await new Promise((resolve, reject) => {
        connection.query(SqlString.format('select s.space_id from spaces s, account_permission a '+
        'where s.space_id=a.space_id and a.account_id = ? and s.space_id = ? and s.deleted is null and a.deleted is null ',
            [uid, spcae_id]),
            (error, results) => {
              if (error) reject(error)
              resolve(results)
            })
      });
      rtn = results.length > 0;
    }

  }catch (err) {
    logger.error('DB err: '+err)
    rtn = false;
  }
  finally {
    if(connection)connection.release()
  }
  return rtn 
}

async function generateInviteUserSession(sender_uid, new_user_email, space_id, role){
  if(typeof space_id != "string" || typeof new_user_email != "string" || typeof role != "number" 
      || typeof sender_uid != "number" ) return;

  var session_id = null;
  const connection = await connectDB();

  try {
    session_id = uuidv4()

    const results = await new Promise((resolve, reject) => {
    connection.query(SqlString.format('INSERT into invite_user_sessions( session_id, sender_id, user_email, role, space_id ) values(?,?,?,?,?) ', 
        [session_id, sender_uid, new_user_email, role, space_id]),
        (error, results) => {
          if (error) reject(error)
          resolve(results)
        })
    })
    
  }catch (err) {
    logger.error('DB err: '+err)
    session_id = null
  } finally {
    if(connection)connection.release()
  }
  return session_id
}

async function askOpenAPI(req, uid, space_id, accspace_sessions_id, question, advice_prompt_id=null, response, language){
  if(typeof uid != "number" || typeof space_id != "string" || typeof accspace_sessions_id !="string" || typeof language != "string") return;

  var rtn = null;

  const assistant = await openai.beta.assistants.create({
    model: "gpt-4o"
  });
  const thread = await openai.beta.threads.create();

  const histories = await processModule.listHistory(uid, space_id, accspace_sessions_id, language, 10);
  histories.reverse();
  var text_len = 0;
  for(const history of histories){
    if(text_len > 6000) break;
    text_len += history.prompt.length + history.answer.length;
    await openai.beta.threads.messages.create(
      thread.id,
      {
        role: "user",
        content: history.prompt 
      }
    );
    await openai.beta.threads.messages.create(
      thread.id,
      {
        role: "assistant",
        content: history.answer
      }
    );
  }
  
  const infos =await getInfoBlocksContent(uid, space_id, language);
  if(infos){
    for(const info of infos){
      const message = await openai.beta.threads.messages.create(
        thread.id,
        {
          role: "user",
          content: '#' + info.block_name + ':' + info.info
        }
      );
    }
  }

  var promptFoot = null;
  if(advice_prompt_id){
    const promptHeadFoot = await getPromptHeadFoot(advice_prompt_id, language);
    if(promptHeadFoot['head']){
      const message = await openai.beta.threads.messages.create(
        thread.id,
        {
          role: "user",
          content: promptHeadFoot['head']
        }
      );
    }
    promptFoot = promptHeadFoot['foot'];
  }
  await openai.beta.threads.messages.create(
    thread.id,
    {
      role: "user",
      content: question
    }
  );

  if(promptFoot){
    const message = await openai.beta.threads.messages.create(
      thread.id,
      {
        role: "user",
        content: promptFoot
      }
    );
  }

  const cur_stream = await openai.beta.threads.runs.create(
    thread.id,
    {assistant_id: assistant.id, stream: true}
  );

  var answer = '';
  for await (const event of cur_stream ) {
    const stop_time = session_time.get(req.session.uid+"_stop_time")??0;
    const ask_time = session_time.get(req.session.uid+"_ask_time")??0;
    if(stop_time >= ask_time){
      cur_stream.controller.abort();
      //req.session.abort_stream = 0;
      break;
    }
    /*else{
      logger.debug('session time:'+stop_time + ' ' + ask_time);
    }*/
    if (event.event === 'thread.message.delta') {
      const chunk = event.data.delta.content?.[0];
      if (chunk && chunk.type === 'text') {
        const txt=chunk.text?.value ?? '';
        //logger.debug(txt);
        answer += txt;
        response.write(txt);
      }
    }
  }

  insertAccountHistory(uid, space_id, accspace_sessions_id, question, answer, language);

  response.end();

}

async function makeJsonTxt(json_txt, txt, language){
  var json = await JSON.parse(json_txt);
  if(!json || typeof json!= "object") json = {};
  json[language] = txt;
  return JSON.stringify(json);
}

async function loadJsonBody(request, log=true){
  const buffers = [];

  for await (const chunk of request) {
    buffers.push(chunk);
  }

  const bodytxt = Buffer.concat(buffers).toString();

  const body = JSON.parse(bodytxt);
  if(log)logger.debug(body);

  return body;
}

const appMsgs = new Map();
const appMsgLanguages = {
    english: 'en-US',
    japanese: 'ja',
    chinese_cn: 'zh-CN',
    //chinese_tw: 'zh-TW',
}
appMsgs.set(appMsgLanguages.english, new Map());
appMsgs.set(appMsgLanguages.japanese, new Map());
appMsgs.set(appMsgLanguages.chinese_cn, new Map());

appMsgs.get(appMsgLanguages.chinese_cn).set('reg_email_temp', '<br>感谢您注册SisAi World服务。'+
  '<br><br>请在{0}分钟内点击下面的链接完成注册。<br><br><a href="{1}">{1}</a><br><br>SisAi支持团队');
appMsgs.get(appMsgLanguages.english).set('reg_email_temp', '<br>Thank you for registering to SisAi World.'+
    '<br><br>Click the link below to complete your registration in {0} minutes.<br><br><a href="{1}">{1}</a><br><br>Thank you,<br>SisAi support team');
appMsgs.get(appMsgLanguages.japanese).set('reg_email_temp', '<br>SisAi Worldへの登録ありがとうございます。'+
    '<br><br>以下のリンクを{0}分以内にクリックして登録を完了してください。<br><br><a href="{1}">{1}</a><br><br>SisAi サポートチーム');

appMsgs.get(appMsgLanguages.chinese_cn).set('invt_email_temp', '<br>感谢您接受SisAi World服务邀请。'+
      '<br><br>请在{0}分钟内点击下面的链接接受邀请。<br><br><a href="{1}">{1}</a><br><br>SisAi支持团队');
appMsgs.get(appMsgLanguages.english).set('invt_email_temp', '<br>Thank you for accepting the SisAi World service invitation.'+
      '<br><br>Click the link below to accept the invitation in {0} minutes.<br><br><a href="{1}">{1}</a><br><br>Thank you,<br>SisAi support team');
appMsgs.get(appMsgLanguages.japanese).set('invt_email_temp', '<br>SisAi Worldサービスの招待を受け入れていただきありがとうございます。'+
      '<br><br>以下のリンクを{0}分以内にクリックして招待を受け入れてください。<br><br><a href="{1}">{1}</a><br><br>SisAi サポートチーム');


appMsgs.get(appMsgLanguages.english).set('reg_email_subject', 'SisAi World free account registration');
appMsgs.get(appMsgLanguages.japanese).set('reg_email_subject', 'SisAi World無料アカウント登録');
appMsgs.get(appMsgLanguages.chinese_cn).set('reg_email_subject', 'SisAi World免费帐户注册');

appMsgs.get(appMsgLanguages.english).set('invt_email_subject', 'SisAi World service invitation');
appMsgs.get(appMsgLanguages.japanese).set('invt_email_subject', 'SisAi Worldサービス招待');
appMsgs.get(appMsgLanguages.chinese_cn).set('invt_email_subject', 'SisAi World服务邀请');