const db = require("../models");
const API = require('../helper/API_Response');
const db_helper = require("../helper/db_helper");
const helper = require("../helper/helper");
const { ClientCohort, Cohort, User } = require("../models");

exports.addclientcohort = async (req, res) => {
  var data = await helper.decryptRequest(req.body.data);
  let usr = {
    "user_id": data.user_id
  }
  var err = [];
  var c = [];
  const cc = await db_helper.selectByWhere("ClientCohort", usr);
  if (cc.length > 0) {
    const coh = await db_helper.selectByPk("ClientCohort", cc[0].id);
    if (data.user_id) coh.user_id = data.user_id;
    if (data.cohortIds) coh.cohortIds = data.cohortIds;
    c = coh.save();
  } else {
    c = await ClientCohort.create(data).catch((ex) => err = ex.errors[0]);
  }
  if (err.length > 0) {
    res.status(400).send(API._400({ message: err.message }));
  }
  res.status(200).send(API._200(await c));
};
exports.admin_add_strudent_cohort = async (req, res) => {
  var input = await helper.decryptRequest(req.body.data);
  var result = [];
  const allcohort = await Cohort.findAll({
    where: {
      status: 0
    }
  });
  for (let c = 0; c < allcohort.length; c++) {
    let u = "";
    u = allcohort[c].userslist;
    let ids = "";
    if (u[0] > 0) {
      for (let ui = 0; ui < u.length; ui++) {
        if (u[ui] != input.user_id) {
          ids += u[ui] + ","
        }
      }
    }
    if (ids.endsWith(",")) {
      ids = ids.substring(0, ids.length - 1);
    }
    if (ids.startsWith(",")) {
      ids = ids.substring(1, ids.length);
    }
    if (ids != "") {
      const coh = await Cohort.findByPk(allcohort[c].id);
      coh.userslist = ids;
      await coh.save();
    }
  }
  if (input.cohortIds) {
    var id = input.cohortIds;
    if (id.includes(",")) {
      var cohortIds = id.split(",");
      for (let i = 0; i < cohortIds.length; i++) {
        const cohort = await Cohort.findByPk(cohortIds[i]);
        let uid = cohort.userslist;
        if (uid[0] > 0) {
          if (!uid.includes(input.user_id))
            cohort.userslist = uid + "," + input.user_id;
        } else {
          cohort.userslist = input.user_id;
        }
        let success = await cohort.save();
        result.push(success);
      }
    } else {
      const cohort = await Cohort.findByPk(id);
      let uid = cohort.userslist;
      if (uid[0] > 0) {
        if (!uid.includes(input.user_id))
          cohort.userslist = uid + "," + input.user_id;
      } else {
        cohort.userslist = input.user_id;
      }
      let success = await cohort.save();
      result.push(success);
    }
  }

  res.status(200).send(API._200(await result));
};
exports.allclient = async (req, res) => {
  const clients = await User.findAll({
    where: {
      status: 0,
      roll_id: 4
    }
  });
  let all = [];
  for (let i = 0; i < clients.length; i++) {
    const cl = await ClientCohort.findAll({
      where: {
        status: 0,
        user_id: clients[i].id
      }
    });
    let cohort = [];
    let ccid = 0;
    if (cl.length > 0) {
      cohort = await db_helper.query("select * from cohorts where id in(" + cl[0].cohortIds + ")");
      ccid = cl[0].id;
    }
    if (cohort.length > 0) {
      cohort = cohort[0];
    }
    all.push({
      clientcohortId: ccid,
      id: clients[i].id,
      name: clients[i].name,
      email: clients[i].email,
      roll_id: clients[i].roll_id,
      email_verified_at: clients[i].email_verified_at,
      email_verified: clients[i].email_verified,
      calling_code: clients[i].calling_code,
      mobile: clients[i].mobile,
      profile_img: clients[i].profile_img,
      permissions: clients[i].permissions,
      password: clients[i].password,
      remember_token: clients[i].remember_token,
      free_flashcard: clients[i].free_flashcard,
      free_practque: clients[i].free_practque,
      free_flashccsp: clients[i].free_flashccsp,
      pass_rest_code: clients[i].pass_rest_code,
      avatar_url: clients[i].avatar_url,
      is_first: clients[i].is_first,
      aliasname: clients[i].aliasname,
      is_temp: clients[i].is_temp,
      notes: clients[i].notes,
      address: clients[i].address,
      status: clients[i].status,
      cohorts: cohort
    })
  }
  res.status(200).send(API._200(await all));
};
exports.cohortdetailview = async (req, res) => {
  var input = await helper.decryptRequest(req.body.data);
  const all = await Cohort.findAll({
    where: {
      id: input.id,
      status: 0
    }
  });
  let std = [];
  let co = [];
  for (let i = 0; i < all.length; i++) {
    let element = all[i].userslist;
    let ids = element.splice(",");
    if (ids.length > 0) {
      for (let j = 0; j < ids.length; j++) {
        let ttlkp = 0;
        let ttlcp = 0;
        let ttlpp = 0;
        let kp = 0;
        let cp = 0;
        let pp = 0;

        let overdueClassWork = 0;
        let knowOverdue = await db_helper.query("select * from cohort_schedules where cohort_id=" + input.id + " and type='KA' and overdue_date < now() and type_id not in (select ka_id from knowcompletes where cohort_id=" + input.id + " and  user_id=" + ids[j] + ")");
        if (knowOverdue[0].length <= 0) {
          let practiceOverdue = await db_helper.query("select * from cohort_schedules where cohort_id=" + input.id + " and type='PRACTICE' and overdue_date < now() and type_id not in (select pt_id from ptestcomplates where cohort_id=" + input.id + " and  user_id=" + ids[j] + ")");
          if (practiceOverdue[0].length <= 0) {
            let catOverdue = await db_helper.query("select * from cohort_schedules where cohort_id=" + input.id + " and type='CAT' and overdue_date < now() and type_id not in (select ct_id from ctestcomplates where cohort_id=" + input.id + " and user_id=" + ids[j] + ")");
            if (catOverdue[0].length <= 0) {
              overdueClassWork = 0;
            } else {
              overdueClassWork = 1;
            }
          } else {
            overdueClassWork = 1;
          }
        } else {
          overdueClassWork = 1;
        }
        let usr = await db_helper.query("select id,name,email,avatar_url,aliasname from users where id=" + ids[j]);
        let ttlkque = await db_helper.query("select count(*) as ttlques from know_ass_ques where ass_id in(SELECT ka.id from knowledge_asses as ka inner join cohorts as c on find_in_set(ka.id,c.knowassessments) where find_in_set(" + ids[j] + ",c.userslist) and ka.status=0 and c.id=" + input.id + " group by ka.id) and status=0");
        ttlkp = parseInt(ttlkque[0][0].ttlques);
        const know = await db_helper.query("SELECT count(*)  AS kpoint FROM know_ass_ques_ans LEFT JOIN knowledge_asses ka ON ass_id = ka.id INNER JOIN cohorts c ON Find_in_set(ka.id, c.knowassessments) AND Find_in_set(user_id, c.userslist) AND cohort_id = c.id WHERE user_id = " + ids[j] + " and cohort_id=" + input.id + " and active=1");
        kp = parseInt(know[0][0].kpoint);

        let ttlcatrque = await db_helper.query("SELECT count(*) as ttlcatrque from ketexamtests kt left join ketexamques kq on find_in_set(kt.id,kq.exam_id) inner join cohorts as c on find_in_set(kt.id,c.ketexamtests) where find_in_set(" + ids[j] + ",c.userslist) and find_in_set(kt.id,kq.exam_id) and kt.status=0 and kq.status=0 and c.id=" + input.id);
        ttlcp = parseInt(ttlcatrque[0][0].ttlcatrque);
        const cohortscoreCat = await db_helper.query("SELECT count(kn.id) AS cpoint FROM ketexam_ans kn inner join ketexamques kq on find_in_set(kn.test_id,kq.exam_id) and kq.id=kn.KEQ_id WHERE user_id = " + ids[j] + " and cohort_id=" + input.id);
        cp = parseInt(cohortscoreCat[0][0].cpoint);

        let ttlprque = await db_helper.query("select count(*) as ttlprques from practiceques where test_id in(SELECT pt.id from practicetests as pt inner join cohorts as c on find_in_set(pt.id,c.practicetests) where find_in_set(" + ids[j] + ",c.userslist) and pt.status=0  and c.id=" + input.id + "  group by pt.id) and status=0");
        ttlpp = parseInt(ttlprque[0][0].ttlprques);
        const cohortscorePra = await db_helper.query("SELECT count(id) AS ppoint FROM practice_ans WHERE user_id = " + ids[j] + " and cohort_id=" + input.id + " and active=1");
        pp = parseInt(cohortscorePra[0][0].ppoint);

        let avgpercetage = 0;

        if (usr[0].length > 0)
          std.push({ "id": ids[j], "name": usr[0][0].name, "email": usr[0][0].email, "avatar_url": usr[0][0].avatar_url, "aliasname": usr[0][0].aliasname, "kpoint": kp, "ttlkpoint": ttlkp, "cpoint": cp, "ttlcpoint": ttlcp, "ppoint": pp, "ttlppoint": ttlpp, "overdueClassWork": overdueClassWork, "avgpercetage": avgpercetage });
      }
    }
    let loc = "-"
    if (all[i].location != null)
      loc = all[i].location;
    co.push({ "cohort_name": all[i].cohort_name, "location": loc, "id": all[i].id, "start_date": all[i].createdAt, "No_Of_Student": ids.length, "Student": std })
    std = [];
  }
  res.status(200).send(API._200(await co));
};
exports.cohortreport = async (req, res) => {
  var input = await helper.decryptRequest(req.body.data);
  const coh = await db.sequelize.query("select userslist,certi_id,id from cohorts where id= :cohort_id", { replacements: { cohort_id: input.cohort_id } })
    .then(function (data) {
      return Array.from(new Set(data));
    })
  if (coh[0].length > 0) {
    if (coh[0][0].userslist != '') {

      const students = await db_helper.query("select id,name,email,avatar_url from users where id in(" + coh[0][0].userslist + ")");
      let data = [];
      for (let i = 0; i < students[0].length; i++) {
        const know = await db_helper.query("(select sum(case when kaqa.correct=1 then 1 else 0 end) ttlpointget,ka.id,ka.knowass_name,(select count(*) from know_ass_ques where ass_id=ka.id) as ttlpoint from know_ass_ques_ans as kaqa left join knowledge_asses as ka on ka.id=kaqa.ass_id  where kaqa.active=1 and kaqa.cohort_id=" + input.cohort_id + " and kaqa.user_id=" + students[0][i].id + " group by ka.id)");
        const flashcarddata = await db_helper.query("select * from flashans_offlines where UserId=" + students[0][i].id + " and cohort_id=" + input.cohort_id + " limit 1");
        var flash;
        if (flashcarddata[0].length > 0) {
          flash = await db_helper.query("select count(distinct (case when user_res = 1 then cid end)) as ttlright,count(distinct (case when user_res = 2 then cid end)) as ttlreview,count(distinct (case when mark = 1 then cid end)) as ttlbookmark, count(distinct (case when(notes = 1 && user_res=0)  then cid end)) as ttlnotes, ifnull(ttlcards,0)ttlcards from flashans_offlines fo right join (SELECT COUNT(*) as ttlcards FROM flashcards WHERE certi_id in(select certi_id from cohorts where id=" + input.cohort_id + ") and status=0)fl on 1=1 where UserId=" + students[0][i].id + " and cohort_id=" + input.cohort_id);
        }
        else {
          // flash = await db_helper.query("select count(distinct (case when user_res = 1 then cid end)) as ttlright,count(distinct (case when user_res = 2 then cid end)) as ttlreview,count(distinct (case when mark = 1 then cid end)) as ttlbookmark, count(distinct (case when(notes = 1 && user_res=0)  then cid end)) as ttlnotes, ifnull(ttlcards,0)ttlcards from flashans_offlines fo right join (SELECT COUNT(*) as ttlcards FROM flashcards WHERE certi_id in(select certi_id from cohorts where id=" + input.cohort_id + ") and status=0)fl on 1=1 where UserId=" + students[0][i].id);
          flash = await db_helper.query("SELECT COUNT(DISTINCT CASE WHEN user_res = 1 THEN cid END) AS ttlright, COUNT(DISTINCT CASE WHEN user_res = 2 THEN cid END) AS ttlreview, COUNT(DISTINCT CASE WHEN mark = 1 THEN cid END) AS ttlbookmark, COUNT(DISTINCT CASE WHEN (notes = 1 AND user_res = 0) THEN cid END) AS ttlnotes, IFNULL((SELECT COUNT(*) FROM flashcards WHERE certi_id IN (SELECT certi_id FROM cohorts WHERE id = " + input.cohort_id + ") AND status = 0), 0) AS ttlcards FROM flashans_offlines fo WHERE UserId =" + students[0][i].id);
        }
        let ttlprque = await db_helper.query("select count(*) as ttlprques from practiceques where test_id in(SELECT pt.id from practicetests as pt inner join cohorts as c on find_in_set(pt.id,c.practicetests) where c.id=" + coh[0][0].id + " and  pt.status=0  group by pt.id) and certi_id = " + coh[0][0].certi_id);
        let ttlprques = parseInt(ttlprque[0][0].ttlprques);
        const cohortscorePra = await db_helper.query(`
                            SELECT
                                CASE WHEN (Sum( CASE WHEN correct = 1 THEN 1 ELSE 0 END)) IS NULL THEN 0 ELSE Sum(CASE WHEN correct = 1 THEN 1 ELSE 0 END) END AS ppoint,
                                CASE WHEN (Sum( CASE WHEN correct = 0 THEN 1 ELSE 0 END)) IS NULL THEN 0 ELSE Sum(CASE WHEN correct = 0 THEN 1 ELSE 0 END) END AS wppoint,
                                CASE WHEN (Sum( CASE WHEN flag_review = 1 THEN 1 ELSE 0 END)) IS NULL THEN 0 ELSE Sum(CASE WHEN flag_review = 1 THEN 1 ELSE 0 END) END AS marked
                            FROM   practice_ans
                            WHERE active=1 and cohort_id=${input.cohort_id} and user_id =${students[0][i].id}`);
        let right = parseInt(cohortscorePra[0][0].ppoint);
        let wrong = parseInt(cohortscorePra[0][0].wppoint);
        let marked = parseInt(cohortscorePra[0][0].marked);
        let prac = { "ttlprques": ttlprques ? ttlprques : 0, "right": right ? right : 0, "wrong": wrong ? wrong : 0, "marked": marked ? marked : 0 };
        await data.push({ "id": students[0][i].id, "name": students[0][i].name, "email": students[0][i].email, "avatar_url": students[0][i].avatar_url, "KnowAss": know[0], "Flashcards": flash[0], "Practice": prac })
      }
      res.status(200).send(API._200(await data));
    }
    else {
      res.status(200).send(API._200([]));
    }
  }
  else {
    res.status(200).send(API._200([]));
  }

};
exports.countdash = async (req, res) => {
  let data = await db_helper.query("SELECT SUM(CASE WHEN roll_id=3 THEN 1 ELSE 0 END) as TotalStudents,SUM(CASE WHEN roll_id=5 THEN 1 ELSE 0 END) as TotalTeachers,SUM(CASE WHEN roll_id=4 THEN 1 ELSE 0 END) as TotalClients FROM users where status=0");
  let course = await db_helper.query("SELECT COUNT(*) as TotalCourse FROM certificates WHERE status=0");
  let std = { "TotalStudents": data[0][0].TotalStudents, "TotalTeachers": data[0][0].TotalTeachers, "TotalCourse": course[0][0].TotalCourse, "TotalClients": data[0][0].TotalClients };
  res.status(200).send(API._200(await std));
};
exports.deleteclientcohort = async (req, res) => {
  var input = await helper.decryptRequest(req.body.data);
  const cohort = await ClientCohort.findByPk(input.id)
  if (!cohort) {
    res.status(404).send(API._404({ message: `Cohort with id: ${input.id} was not found` }));
  }
  cohort.status = 1
  let c = cohort.save();
  res.status(200).send(API._200(await c));
};
exports.emptyclientcohort = async (req, res) => {
  var input = await helper.decryptRequest(req.body.data);
  let c = await db_helper.query("delete from clientcohorts where user_id=" + input.user_id);
  res.status(200).send(API._200(await c));
};
exports.clientlogs_all = async (req, res) => {
  res.status(200).send(API._200(await db_helper.allData('ClientLogs')));
};