// 学校相关

const util = require('../utils')
const db = require('../data')
const Token = require('../token')
const dayjs = require('dayjs')
const EnDeNum = require('../utils/encrypt-decrypt-number')
let [sql, rs] = ['', null]

/**
 * 按日期输出预约时间段（学校id为加密后）
 * get: /school/yuyue-by-date
 */
exports.YuyueByDate = async ctx => {
  const date = ctx.query.date
  const day = ctx.query.day
  const path = ctx.query.path
  if (util.isDate(date) && day && util.isIdList(path)) {
    const data = []
    // 查询时间段
    sql = `select id,consultant,realname,photo,thetime,remark,room,way from v_zx_time where member_type in (${path}) and (weekday=${day} or thedate='${date}')`
    rs = await db.select(sql)
    if (rs) {
      // 查询当天忙碌状态
      sql = `select zx_time_id from zx_busy where yydate='${date}'`
      const buzy = await db.select(sql) || []
      // 查询当天被预约的时间段
      sql = `select consultant,yytime from zx where status<>1 and yydate='${date}'`
      const zx = await db.select(sql) || []
      // 剔除忙碌和被预约的项目
      rs.forEach(item => {
        let show = true
        if (buzy.some(item1 => item1.zx_time_id === item.id)) { show = false }
        else if (zx.some(item1 => item1.consultant === item.consultant && item1.yytime === item.thetime)) { show = false }
        show && data.push(item)
      })
    }
    ctx.body = { code: 0, data }
  } else { throw { code: -2 } }
}

/**
 * 按咨询师输出时间段（咨询师ID为加密后）
 * get: /school/yuyue-by-consultant
 */
exports.YuyueByConsultant = async ctx => {
  const id = EnDeNum.decrypt(ctx.query.id)
  if (id) {
    const data = []
    sql = 'select id,weekday,thetime,temp,convert(varchar,thedate,23) as thedate,room,remark,way from v_zx_time where consultant=' + id + ' order by time1'
    rs = await db.select(sql)
    if (rs) {
      // 忙碌状态
      sql = 'select convert(varchar,yydate,23) as yydate,yytime from v_zx_busy where yydate>getdate()-1 and consultant=' + id
      const buzy = await db.select(sql) || []
      // 查询被预约过的时间段
      sql = 'select convert(varchar,yydate,23) as yydate,yytime from zx where status<>1 and yydate>getdate()-1 and consultant=' + id
      const zx = await db.select(sql) || []
      // 处理
      const now = dayjs()
      const closedate = ctx.query.closedate
      const beforetime = ctx.query.beforetime
      const jiezhi = now.add(beforetime || 0, 'minute').format('YYYY-MM-DD HH:mm')
      let yyday = Number(ctx.query.yyday) || 7
      now.hour() >= 22 && (yyday += 1)
      for (let i = 0; i <= yyday; i++) {
        // 当日日期对象、星期几、日期字符串
        const theday = now.add(i, 'day')
        const wd = theday.day()
        const thedayStr = theday.format('YYYY-MM-DD')
        if (closedate && thedayStr >= closedate) { break }
        const child = []
        rs.forEach(item => {
          let show = true
          if (buzy.some(item1 => item1.yydate === thedayStr && item1.yytime === item.thetime)) { show = false }
          else if (zx.some(item1 => item1.yydate === thedayStr && item1.yytime === item.thetime)) { show = false }
          else if (thedayStr + ' ' + item.thetime.substring(0, 5) < jiezhi) { show = false }
          // 固定和临时情况
          show && (item.weekday === wd || item.thedate === thedayStr) && child.push({ time: item.thetime, way: item.way, room: item.room, remark: item.remark })
        })
        child.length && data.push({ date: thedayStr, day: wd, thetime: child })
      }
    }
    ctx.body = { code: 0, data }
  } else { throw { code: -2 } }
}

/**
 * 获取某人员当周预约次数
 * get: /school/yuyue-week-count
 */
exports.YuyueWeekCount = async ctx => {
  const member = Token.Decode(ctx).id
  const yydate = ctx.query.yydate
  const start = dayjs(yydate).startOf('week')
  sql = `select count(id) as count from zx where member=${member} and yydate>='${start.format('YYYY-MM-DD')}' and yydate<'${start.add(8, 'day').format('YYYY-MM-DD')}'`
  rs = await db.select(sql)
  ctx.body = { code: 0, data: rs[0].count }
}

/**
 * 处理超过学期限制后的预约申请
 * all: /school/yuyue-apply
 */
exports.YuyueApply = async ctx => {
  const member = Token.Decode(ctx).id
  if (ctx.method === 'GET') {
    rs = await db.select('select top 1 status from zx_apply where member=' + member + ' order by id desc')
    // -1未申请，0未处理，1已同意，2被拒绝
    const data = rs ? rs.status : -1
    ctx.body = { code: 0, data }
  } else if (ctx.method === 'POST') {
    const content = db.escape(ctx.request.body.content)
    sql = 'insert into zx_apply(member,content,datetime,status) values('
      + member
      + `,'${content}'`
      + ',getdate()'
      + ',0'
      + ')'
    db.execute(sql)
    ctx.body = { code: 0 }
  } else { throw { code: -6 } }
}

/**
 * 查询是否已经反馈过上次的咨询记录
 * get: /school/last-zixun-feedback-exist
 */
exports.LastZixunFeedbackExist = async ctx => {
  const member = Token.Decode(ctx).id
  rs = await db.select('select top 1 status from zx where status>=5 and member=' + member + ' order by id desc')
  if (rs && rs.status === 5) { ctx.body = { code: 0, data: false } }
  else { ctx.body = { code: 0, data: true } }
}

/**
 * 新增预约
 * post: /school/yuyue
 */
exports.AddYuyue = async ctx => {
  const member = Token.Decode(ctx).id
  const body = ctx.request.body
  const consultant = body.consultant
  if (consultant === 0) {
    // 中心安排
    sql = 'insert into zx(member,consultant,question,way,status,datetime,contact,intent_time) values('
      + member
      + ',0'
      + `,'${body.question}'`
      + ',0'
      + ',0'
      + ',getdate()'
      + `,'${body.contact}'`
      + `,'${body.intent_time}'`
      + ');'
    util.isMobile(body.mobile) && (sql += `update member set mobile='${body.mobile}',qq='${body.qq}' where id=${member};`)
    db.execute(sql)
    ctx.body = { code: 0 }
  } else {
    // 按时间预约
    const yydate = body.yydate
    const yytime = body.yytime
    // 查询忙碌状态
    rs = await db.select(`select top 1 consultant from v_zx_busy where consultant=${consultant} and yydate='${yydate}' and yytime='${yytime}'`)
    if (rs) { throw { code: 1, message: '该时间为忙碌状态' } }
    else {
      // 没有记录或者被老师取消的算没有被预约
      rs = await db.select(`select top 1 id from zx where status<>1 and consultant=${consultant} and yydate='${yydate}' and yytime='${yytime}'`)
      if (rs) { throw { code: 1, message: '已被预约' } }
      else {
        const school = body.school
        const mobile = body.mobile
        const way = Number(body.way) || 0
        // 取学校设置
        const config = await db.select('select top 1 yuyue_checked,fds,sms,yuyue_sms_tpl,sms_sign,netzx_minute from school where id=' + school)
        if (config) {
          const status = config.yuyue_checked ? 3 : 0
          sql = ''

          // 更新来访者手机号
          util.isMobile(mobile) && (sql += `update member set mobile='${mobile}',qq='${body.qq}' where id=${member};`)

          // 插入预约记录
          sql += 'insert into zx(member,consultant,yydate,yytime,zxtime,question,way,status,room,datetime,contact,signature) values('
            + member
            + ',' + consultant
            + `,'${yydate}'`
            + `,'${yytime}'`
            + `,'${yydate}'`
            + `,'${body.question}'`
            + ',' + way
            + ',' + status
            + `,'${body.room}'`
            + ',getdate()'
            + `,'${body.contact}'`
            + `,'${body.signature}'`
            + ');'

          // 只要首诊负责制就绑定咨询师不管什么状态
          config.fds && (sql += 'update member set bind_consultant_id=' + consultant + ' where id=' + member + ';')

          // 咨询的完整日期时间
          const yydatetime = yydate + '（星期' + ['日', '一', '二', '三', '四', '五', '六'][dayjs(yydate).day()] + '）' + yytime
          const message = require('./message')

          // 给学生发送手机短信（自动确认的预约、开通了短信功能、短信模板不为空，来访者手机号不为空）
          if (status === 3 && config.sms && config.yuyue_sms_tpl && util.isMobile(mobile)) {
            rs = await db.select('select top 1 realname,weixin from member with (nolock) where id = ' + consultant)
            const content = config.sms_sign + config.yuyue_sms_tpl
              .replace(/{{Name}}/g, body.realname)
              .replace(/{{Consultant}}/g, rs.realname)
              .replace(/{{Weixin}}/g, rs.weixin)
              .replace(/{{Date}}/g, yydatetime)
              .replace(/{{Room}}/g, body.room)
              .replace(/{{Way}}/g, way === 0 ? '面询' : '网络')
            message.SendSMS(mobile, content)

            sql += 'insert into mobile_sms_timing(mobile,content,datetime) values('
              + `'${mobile}'`
              + `,'${content}'`
              + `,'${dayjs(yydate).subtract(1, 'day').format('YYYY-MM-DD') + ' 08:00'}'`
              + ');'
          }

          // 给咨询师发送手机短信（开通了短信功能、咨询师手机号存在）
          if (config.sms) {
            rs = await db.select('select top 1 mobile from member with (nolock) where id = ' + consultant)
            if (util.isMobile(rs.mobile)) {
              const content = config.sms_sign + '有来访者预约您，姓名：' + body.realname + '，咨询时间：' + yydatetime + (status === 3 ? '，请您准时进行心理咨询。' : '，请您及时处理预约。')
              message.SendSMS(rs.mobile, content)
            }
          }

          db.execute(sql)
          ctx.body = { code: 0 }
        } else { throw { code: 2, message: '单位（' + school + '）未找到' } }
      }
    }
  }
}

/**
 * 学校咨询师，传入t表示只取父类的（id为加密后的）
 * get: /school/:id/consultant
 */
exports.Consultant = async ctx => {
  const school = EnDeNum.decrypt(ctx.params.id)
  const type = ctx.query.t
  const id = ctx.query.id
  let path = ctx.query.path
  if (util.isInteger(id)) {
    // 查单个
    sql = 'select top 1 id,photo,realname,intro_aword,intro from member with (nolock) where id = ' + id
    rs = await db.select(sql)
    ctx.body = { code: 0, data: rs ? [rs] : [] }
  } else if (util.isInteger(type) || util.isIdList(path)) {
    // 查上层
    if (!util.isIdList(path)) { path = await db.scalar('select top 1 path from member_type where id=' + type) }
    sql = `select id,photo,realname,intro_aword,intro from v_member with (nolock) where isconsultant=1 and state=5 and type in (${path}) order by schooladmintype,regtime`
    rs = await db.select(sql)
    ctx.body = { code: 0, data: rs || [] }
  } else if (school) {
    // 查下层
    sql = `select id,photo,realname,intro_aword,intro from v_member with (nolock) where isconsultant=1 and state=5 and member_type_top=${school} order by schooladmintype,regtime`
    rs = await db.select(sql)
    ctx.body = { code: 0, data: rs || [] }
  } else { throw { code: -2 } }
}

/**
 * 自己（咨询师）的咨询记录操作
 * all: /school/zixun
 */
exports.Zixun = async ctx => {
  const id = ctx.query.id
  const status = ctx.query.s
  const school = ctx.query.school
  const consultant = Token.Decode(ctx).id
  if (ctx.method === 'GET') {
    if (util.isInteger(id)) {
      // 取单条
      sql = 'select top 1 '
      sql += ctx.query.mini ? 'member,zxtime,problem,problem_sub,serious,content,status,yydate,yytime,question' : '*'
      sql += ' from zx where id = ' + id + ' and consultant = ' + consultant
      rs = await db.select(sql)
      if (rs) { ctx.body = { code: 0, data: rs } }
      else { throw { code: 1, message: '记录(' + id + ')未找到' } }
    } else {
      // 取多条
      sql = 'select top 10 id,member,realname,yydate,yytime,problem,zxtime from v_zx where consultant=' + consultant
      if (school) { sql += ' and member_type_top=' + school }
      if (status) { sql += ' and status in (' + status + ')' }
      if (status === '3') { sql += ' order by yydate,yytime' }
      else { sql += ' order by datetime desc,id desc' }
      rs = await db.select(sql)
      ctx.body = { code: 0, data: rs || [] }
    }
  } else if (ctx.method === 'DELETE') {
    if (!util.isInteger(id)) { throw { code: -2 } }
    else {
      const studentid = await db.scalar('select top 1 member from zx where id=' + id + ' and consultant=' + consultant)
      if (studentid === null) { throw { code: 1, message: '记录(' + id + ')未找到' } }
      else {
        sql = 'update zx set next=NULL where next=' + id
          + ';delete from zx where id=' + id
          + ';update member set zx_times=(select count(id) from zx where status>=4 and member=' + studentid + ') where id=' + studentid
        db.execute(sql)
        ctx.body = { code: 0 }
      }
    }
  } else if (ctx.method === 'PUT') {
    if (!util.isInteger(id)) { throw { code: -2 } }
    else {
      const studentid = await db.scalar('select top 1 member from zx where id=' + id + ' and consultant=' + consultant)
      if (studentid === null) { throw { code: 1, message: '记录(' + id + ')未找到' } }
      else {
        sql = 'update zx set'
          + ` content='${ctx.request.body.content}'`
          + `,problem='${ctx.request.body.problem}'`
          + `,problem_sub='${ctx.request.body.problem_sub}'`
          + `,serious='${ctx.request.body.serious}'`
          + ',status=' + ctx.request.body.status
          + `,zxtime='${ctx.request.body.zxtime}'`
          + ' where id=' + id
        sql += ';update member set zx_times=(select count(id) from zx where status>=4 and member=' + studentid + ') where id=' + studentid
        db.execute(sql)
        ctx.body = { code: 0 }
      }
    }
  } else { throw { code: -6 } }
}

/**
 * 学生手机端团辅功能
 * all: /school/tuanfu
 */
exports.Tuanfu = async ctx => {
  const member = Token.Decode(ctx).id
  if (ctx.method === 'GET') {
    const query = ctx.query
    const id = query.id
    const act = query.act
    if (act === 'member-count') {
      sql = 'select count(id) as count from groupguidance_member where status=1 and courseid=' + id
      const data = await db.scalar(sql)
      ctx.body = { code: 0, data }
    } else if (act === 'exist-zixun') {
      sql = `select top 1 id from zx where status<>1 and member=${member} and yydate>='${dayjs().format('YYYY-MM-DD')}'`
      rs = await db.select(sql)
      ctx.body = { code: 0, data: rs ? true : false }
    } else if (act === 'history') {
      sql = 'select id,name,[time],status,courseid,feedback from v_groupguidance_member where member=' + member + ' order by id desc'
      rs = await db.select(sql)
      ctx.body = { code: 0, data: rs || [] }
    } else {
      const today = dayjs().format('YYYY-MM-DD')
      const bm = await db.select('select courseid,status,time1,time2 from v_groupguidance_member where member=' + member) || []
      sql = 'select id,name,[time],place,author,convert(varchar,recruit_startdate,23) as recruit_startdate,convert(varchar,recruit_enddate,23) as recruit_enddate,picture,recruit_max,time1,time2,recruit_test_lb from groupguidance where'
        + ' membertype=' + query.school
        + ' and recruit_enddate>getdate()-1'
        + ' and (recruit_way=3 or (recruit_dept in (' + query.path + ') and (recruit_grade=' + query.grade + ' or recruit_grade=0) and (recruit_group=' + query.group + ' or recruit_group=0)))'
      util.isInteger(id) && (sql += ' and id=' + id)
      rs = await db.select(sql)
      if (rs) {
        rs.forEach(item => {
          item.status = 0
          if (item.recruit_startdate > today) { item.status = 1 }
          else {
            const bmItem = bm.find(bm => bm.courseid === item.id)
            if (bmItem) {
              switch (bmItem.status) {
                case 0: item.status = 3; break
                case 1: item.status = 4; break
                default: item.status = 5
              }
            } else if (item.time1 && item.time2 && bm.some(bm => bm.status === 1 && bm.time1 >= item.time1 && bm.time2 <= item.time2)) {
              item.status = 6
            }
          }
        })
        ctx.body = { code: 0, data: rs }
      } else { ctx.body = { code: 0, data: [] } }
    }
  } else if (ctx.method === 'POST') {
    const body = ctx.request.body
    sql = ''
    util.isMobile(body.mobile) && (sql += `update member set mobile='${body.mobile}',email='${body.email}' where id=${member};`)
    sql += 'insert into GroupGuidance_Member(courseid,member,datetime,focus,status,feedback_like) values(' + body.courseid + ',' + member + ',getdate(),0,0,0);'

    // 发送手机短信（开通短信功能、接收者手机号格式合法）
    const school = body.school
    const config = await db.select('select top 1 sms,sms_receiver,sms_sign from school where id=' + school)
    if (config.sms && util.isMobile(config.sms_receiver)) {
      const message = require('./message')
      const content = config.sms_sign + '您好，有新成员报名加入团辅活动——' + body.coursename + '，请您尽快处理。'
      message.SendSMS(config.sms_receiver, content)
    }

    db.execute(sql)
    ctx.body = { code: 0 }
  } else { throw { code: -6 } }
}