const express = require('express')
const knex = require('knex')
const server = express()

const knexconfig = {
  client: 'mysql',
  connection: {
    host: '---somehost---',
    user: '---someuser---',
    password: '---somepaswd',
    database: '---somedatabase---'
  }
}

const mysql = knex(knexconfig)

const data = async (res) => {
  const raw = await mysql
    .table('evals')
    .join('eval_answers','evals.answer_id','=','eval_answers.id')
    .join('eval_questions','eval_answers.question_id','=','eval_questions.id')
    .join('eval_criterias','evals.criteria_id','=','eval_criterias.id')
    .join('profiles','eval_answers.user_id','=','profiles.user_id')
    .join('profile_registrants','eval_answers.user_id','=','profile_registrants.user_id')
    .whereNotIn('profiles.user_id', [
      ...somethings...
    ])
    .select(
      'profiles.user_id',
      'eval_questions.id',
      'eval_criterias.name',
      'eval_criterias.percentage',
      'evals.score',
      // profile
      'profiles.citizen_id',
      'profiles.first_name',
      'profiles.last_name',
      'profiles.nickname',
      'profiles.congenital_diseases',
      'profiles.allergic_foods',
      'profiles.congenital_drugs',
      'profiles.gender_id',
      'profile_registrants.edu_name',
      'profile_registrants.edu_lv',
      'profile_registrants.edu_major',
      'profile_registrants.edu_gpax',
      'profile_registrants.known_via',
      'profile_registrants.activities',
      'profile_registrants.skill_computer',
      'profile_registrants.past_camp',
      'profile_registrants.tell_wipper',
    )

    const data = {}

    Promise.all(raw.map(r => {
      try {
        data[r.user_id] = {
          ...data[r.user_id],
          profile: {
            citizen_id: r.citizen_id,
            fullname: `${r.first_name} ${r.last_name}`,
            nickname: r.nickname,
            congenital_diseases: r.congenital_diseases,
            allergic_foods: r.allergic_foods,
            congenital_drugs: r.congenital_drugs,
            gender: r.gender_id === 1 ? 'male' : 'female', // 1 - man, 2 - girl
            edu_name: r.edu_name,
            edu_lv: r.edu_lv,
            edu_major: r.edu_major,
            edu_gpax: r.edu_gpax,
            known_via: r.known_via,
            activities: r.activities,
            skill_computer: r.skill_computer,
            past_camp: r.past_camp,
            // tell_wipper: r.tell_wipper,
          },
          [`question-${r.id}`]: [
            ...[...data[r.user_id][`question-${r.id}`],
            {
              score: r.score,
              criteria: r.name,
              percentage: r.percentage
            }],
          ]
        }
      } catch (er) {
        data[r.user_id] = {
          ...data[r.user_id],
          profile: {
            citizen_id: r.citizen_id,
            fullname: `${r.first_name} ${r.last_name}`,
            nickname: r.nickname,
            congenital_diseases: r.congenital_diseases,
            allergic_foods: r.allergic_foods,
            congenital_drugs: r.congenital_drugs,
            gender: r.gender_id === 1 ? 'male' : 'female', // 1 - man, 2 - girl
          },
          [`question-${r.id}`]: [
            {
              score: r.score,
              criteria: r.name,
              percentage: r.percentage
            }
          ]
        }
      }
    })).then(() => {
      result = []

      checker = [0,3,4,5,3,3]

      Promise.all(Object.keys(data)
      .map( async d => {
        let sum_score = 0
        let sum_hscore = 0
        let q = [0,0,0,0,0,0]
        for(i = 1; i <= 5; i ++) {
          try {
            let communication = data[d][`question-${i}`]
              .filter(s => s.criteria === 'communication')
              .reduce((sum, s) => sum + (s.score * s.percentage / 100), 0)
            console.log(`question-${i}: commu:` + communication / checker[i])
            let intelligent = data[d][`question-${i}`]
              .filter(s => s.criteria === 'intelligent')
              .reduce((sum, s) => sum + (s.score * s.percentage / 100), 0)
            console.log(`question-${i}: intelligent:` + intelligent / checker[i])
            let creative = data[d][`question-${i}`]
              .filter(s => s.criteria === 'creative')
              .reduce((sum, s) => sum + (s.score * s.percentage / 100), 0)
            console.log(`question-${i}: creative:` + creative / checker[i])

            // sum score
            let sum_q_score = (communication / checker[i]) + (intelligent / checker[i]) + (creative / checker[i])
            console.log(`question-${i}: ${sum_q_score}`)
            sum_score += sum_q_score
            sum_hscore += 1/sum_q_score

            // set score
            q[i] = sum_q_score
          } catch (err) {
            continue
          }
        }

        console.log(sum_score / 5)
        result = [
          ...result,
          {
            'WIP_ID': d,
            ...data[d].profile,
            // 'Q1': q[1],
            // 'Q2': q[2],
            // 'Q3': q[3],
            // 'Q4': q[4],
            // 'Q5': q[5],
            'MEAN': sum_score /5,
            'H_MEAN': 5/sum_hscore
          }
        ]
      })).then(() => {


      res.send(`<html><body><table>
        <thead>
          ${
            `<tr>${Object.keys(result[0]).map(k => `<th>${k}</th>`).join('')}</tr>`
          }
        </thead>
        <tbody>
          ${
            result.map(r => (`<tr>${Object.keys(r).map(k => `<td>${r[k]}</td>`).join('')}</tr>`)).join('')
          }
        </tbody>
      </table></body></html>`)
      return
      })

    })
}



server.get('/', (req, res) => {
  data(res)
  // res.send(data)
})

server.listen(10000)