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

exports.studentdash = async (req, res) => {
  const input = await helper.decryptRequest(req.body.data);
  var domains = await Domain.findAll({
    where: {
      certi_id: input.cource_id,
      status: 0
    }
  });
  const cohorts = await Cohort.findAll({
    where: {
      id: input.cohort_id,
      status: 0
    },
    attributes: ['id', 'domain_sequence'],
  });

  let data = [];
  for (let i = 0; i < domains.length; i++) {
    let kp = 0;
    let ttlkp = 0;
    let pp = 0;
    let ttlpp = 0;
    let pAppP = 0;
    let ttlpApp = 0;
    let fp = 0;
    let ttlfp = 0;
    let qp = 0;
    let ttlqp = 0;
    let ttlkque = await db_helper.query("select count(*) as ttlques from know_ass_ques where status=0 and  d_id =" + domains[i].id + " AND ass_id in(select type_id from cohort_schedules where type='KA' and cohort_id=" + input.cohort_id + " and due_date <> '')");
    let totalKnowPoint = parseInt(ttlkque[0][0].ttlques);
    const know = await db_helper.query("SELECT COUNT(*) AS kpoint FROM know_ass_ques_ans WHERE  cohort_id=" + input.cohort_id + " and active=1 and  user_id = " + input.user_id + " AND d_id =" + domains[i].id + " AND ass_id in(select type_id from cohort_schedules where type='KA' and cohort_id=" + input.cohort_id + " and due_date <> '')");

    kp += parseInt(know[0][0].kpoint);
    ttlkp = totalKnowPoint;

    let ttlprque = await db_helper.query("select count(*) as ttlprques from practiceques where status=0 and d_id=" + domains[i].id + " and test_id in(select type_id from cohort_schedules where type='PRACTICE' and cohort_id=" + input.cohort_id + " and due_date <> '')");
    let totalPracticePoint = parseInt(ttlprque[0][0].ttlprques);
    const cohortscorePra = await db_helper.query("SELECT COUNT(*) AS ppoint FROM practice_ans WHERE  cohort_id=" + input.cohort_id + " and active=1 and  user_id = " + input.user_id + " AND d_id =" + domains[i].id + " and test_id in (select type_id from cohort_schedules where type = 'PRACTICE' and cohort_id = " + input.cohort_id + " and due_date <> '')");

    pp += parseInt(cohortscorePra[0][0].ppoint);
    ttlpp = totalPracticePoint;

    const flash = await db_helper.query("select sum(correct) fp, (SELECT count(*) FROM flashcards WHERE d_id=" + domains[i].id + " AND status=0) as ttlcards from flashcard_offlines where UserId=" + input.user_id + " and cohort_id=" + input.cohort_id + " and domain=" + domains[i].id);
    ttlfp = parseInt(flash[0][0].ttlcards);
    fp = parseInt(flash[0][0].fp);

    const ques = await db_helper.query("select sum(case when correct=answer then 1 else 0 end) correct,count(*) ttlq from practiceapp_ans where active=1 and user_id=" + input.user_id + " and domain_id=" + domains[i].id);
    ttlqp = parseInt(ques[0][0].ttlq);
    qp = parseInt(ques[0][0].correct);

    const video_query = await db_helper.query("SELECT id from videos where status=0 and d_id=" + domains[i].id + "; SELECT * from video_mind_views where type_id in(SELECT id from videos where status=0 and d_id=" + domains[i].id + ") and type='VIDEO' and user_id=" + input.user_id + " and view_percent=100 and cohort_id=" + input.cohort_id);
    let video_point_ttl = video_query[0][0].length;
    let video_point = video_query[0][1].length;

    const mind_query = await db_helper.query("SELECT id from mind_maps where status=0 and d_id=" + domains[i].id + "; SELECT * from video_mind_views where type_id in(SELECT id from mind_maps where status=0 and d_id=" + domains[i].id + ") and type='MIND' and user_id=" + input.user_id + " and view_percent=100 and cohort_id=" + input.cohort_id);
    let mind_point_ttl = mind_query[0][0].length;
    let mind_point = mind_query[0][1].length;

    let kper = 0;
    if (ttlkp > 0 && kp > 0)
      kper = Math.round((kp * 100) / ttlkp);
    else
      kper = 0;
    let pper = 0;
    if (ttlpp > 0 && pp > 0)
      pper = Math.round((pp * 100) / ttlpp);
    else
      pper = 0;
    let pApp = 0;
    if (ttlpApp > 0 && pAppP > 0)
      pApp = Math.round((pAppP * 100) / ttlpApp);
    else
      pApp = 0;
    let fper = 0;
    if (ttlfp > 0)
      fper = Math.round(((fp ? fp : 0) * 100) / ttlfp);
    else
      fper = 0;
    let vper = 0;
    if (video_point_ttl > 0)
      vper = Math.round((video_point * 100) / video_point_ttl);
    else
      vper = 0;
    let mper = 0;
    if (mind_point_ttl > 0)
      mper = Math.round((mind_point * 100) / mind_point_ttl);
    else
      mper = 0;

    let qper = 0;
    if (ttlqp > 0)
      qper = Math.round((qp * 100) / ttlqp);
    else
      qper = 0;

    if (fper >= 100) {
      fper = 100;
    }
    let totalPro = 0;
    totalPro = Math.round((kper + pper + fper + vper + mper + qper) / 6);

    data.push({
      num: i + 1,
      title: domains[i].domin_name,
      id: domains[i].id,
      weight: 10,
      totalPro: totalPro,
      kp: kp,
      kper: kper,
      ttlkp: ttlkp,
      cohorts: cohorts,
      subPros: [{
        title: 'Getting Started',
        val: vper > 100 ? 100 : vper,
        type: 'video',
        text: 'Play',
        link: 'true'
      }, {
        title: 'E-brochure',
        val: fper,
        type: 'flash',
        text: 'App',
        link: 'sidenav/resource#Apps'
      },
      {
        title: 'Press Release',
        val: kper,
        type: 'KA',
        text: 'Go',
        link: 'sidenav/knowledge'
      },

      {
        title: 'Webinar',
        val: pper,
        type: 'PT',
        text: 'Go',
        link: 'sidenav/practicetest'
      },
      {
        title: 'Brand Kit',
        val: mper,
        type: 'mind',
        text: 'Play',
        link: 'true'
      }, {
        title: 'Videos',
        val: qper,
        type: 'ques',
        text: 'App',
        link: 'sidenav/resource#PracticeQuestionApp'
      }
      ]
    })
  }
  if (cohorts.length > 0) {
    let domain_sequence = cohorts[0].domain_sequence;
    if (domain_sequence) {
      data = data.sort(function (a, b) {
        const indexA = domain_sequence.indexOf(a.id);
        const indexB = domain_sequence.indexOf(b.id);
        return indexA - indexB;
      });
    }
  }
  res.status(200).send(API._200(await data));
};
exports.cohortsCertificateExpireDate = async (req, res) => {
  const input = await helper.decryptRequest(req.body.data);
  const getCohorts = await db.sequelize.query(" select * from cohorts where id=:cohort_id", { replacements: { cohort_id: input.cohort_id } })
    .then(function (data) {
      return Array.from(new Set(data[0]));
    });
  var SelfCohorts = [];
  for (let i = 0; i < getCohorts.length; i++) {
    if (getCohorts[0].course_type == '' || getCohorts[0].course_type == null) {
      SelfCohorts = await db.sequelize.query(" select max(overdue_date)expire_date from cohort_schedules where cohort_id=:cohort_id", { replacements: { cohort_id: input.cohort_id } })
        .then(function (data) {
          return Array.from(new Set(data[0]));
        });
    }
    else {
      SelfCohorts = await db.sequelize.query("select exam_date expire_date from self_paced_schedules where cohort_id=:cohort_id and user_id=:user_id order by id limit 1", { replacements: { cohort_id: input.cohort_id, user_id: input.user_id } })
        .then(function (data) {
          return Array.from(new Set(data[0]));
        });
    }
  }
  res.status(200).send(API._200(await SelfCohorts));
};
exports.dashactivity = async (req, res) => {
  const input = await helper.decryptRequest(req.body.data);
  let time = await db.sequelize.query("select cs.*,ka.knowass_name,pt.practice_name,cat.ketexam_name,case when kans is not null then kans else case when pans is not null then pans else case when catans is not null then catans else 0 end end end resume from cohort_schedules as cs left join knowledge_asses as ka on ka.id=cs.type_id and cs.type='KA' left join (select count(ass_id)kans,ass_id from know_ass_ques_ans where user_id=:user_id and cohort_id=:cohort_id and active=1 group by ass_id,user_id)kaans on kaans.ass_id=cs.type_id and cs.type='KA'  left join practicetests as pt on pt.id=cs.type_id and cs.type='PRACTICE' left join (select count(test_id)pans,test_id from practice_ans where user_id=:user_id and cohort_id=:cohort_id and active=1 group by test_id,user_id)paans on paans.test_id=cs.type_id and cs.type='PRACTICE' left join ketexamtests as cat on cat.id=cs.type_id and cs.type='CAT' left join  (select count(test_id)catans,test_id from ketexam_ans where user_id=:user_id and cohort_id=:cohort_id group by test_id,user_id)ketans on ketans.test_id=cs.type_id and cs.type='CAT' where cs.type<>'EMAIL' and cs.type<>'video' and cs.type<>'mindmap' and cs.cohort_id in (select id from cohorts where cohort_id= :cohort_id and certi_id= :certi_id and find_in_set(" + input.user_id + ",userslist))", { replacements: { cohort_id: input.cohort_id, certi_id: input.certi_id, user_id: input.user_id } }).then((res) => {
    return Array.from(new Set(res));
  })
  for (let i = 0; i < time[0].length; i++) {
    if (time[0][i].type == "KA") {
      time[0][i].examname = time[0][i].knowass_name;
      const ka = await db_helper.query("select * from knowcompletes where ka_id=" + time[0][i].type_id + " and cohort_id=" + input.cohort_id + " and user_id=" + input.user_id);
      if (ka[0].length > 0) {
        time[0][i].status = "Completed";
      } else {
        time[0][i].status = "";
      }
    }
    if (time[0][i].type == "CAT") {
      time[0][i].examname = time[0][i].ketexam_name;
      const cat = await db_helper.query("select * from ctestcomplates where ct_id=" + time[0][i].type_id + " and cohort_id=" + input.cohort_id + " and user_id=" + input.user_id);
      if (cat[0].length > 0) {
        time[0][i].status = "Completed";
      } else {
        time[0][i].status = "";
      }
    }
    if (time[0][i].type == "PRACTICE") {
      time[0][i].examname = time[0][i].practice_name;
      const cat = await db_helper.query("select * from ptestcomplates where pt_id=" + time[0][i].type_id + " and cohort_id=" + input.cohort_id + " and user_id=" + input.user_id);
      if (cat[0].length > 0) {
        time[0][i].status = "Completed";
      } else {
        time[0][i].status = "";
      }
    }
  }
  res.status(200).send(API._200(await time));
};
exports.get_baseline_ka = async (req, res) => {
  const input = await helper.decryptRequest(req.body.data);
  const cohort_data = await db.sequelize.query("select baseline_ka from cohorts where id= :cohort_id", { replacements: { cohort_id: input.cohort_id } }).then((res) => {
    return Array.from(new Set(res));
  })
  let time = [];
  if (cohort_data[0][0].baseline_ka) {
    const baseline_ka = await db.sequelize.query("select id,knowass_name from knowledge_asses where id=" + cohort_data[0][0].baseline_ka)
      .then(function (data) {
        return Array.from(new Set(data[0]));
      })
    if (baseline_ka.length > 0) {
      let newevt = {
        examname: baseline_ka[0].knowass_name,
        type: "KA",
        cohort_id: cohort_data[0][0].id,
        status: "Not Started",
        id: baseline_ka[0].id,
        time: "00:00",
        KA_access: 1,
        RowNumber: 0
      }
      const ka = await db_helper.query("select * from knowcompletes where ka_id=" + baseline_ka[0].id + " and cohort_id=" + input.cohort_id + "  and user_id=" + input.user_id);
      if (ka[0].length > 0) {
        const knowAssQues = await db.sequelize.query("SELECT ans,correct FROM know_ass_ques_ans WHERE user_id=" + input.user_id + " AND active=1 AND ass_id=" + baseline_ka[0].id + " AND cohort_id=" + input.cohort_id)
          .then(function (data) {
            return Array.from(new Set(data[0]));
          });
        let correct = [];
        correct = await knowAssQues.filter(res => res.correct == 1);
        let wrong = [];
        wrong = await knowAssQues.filter(res => res.correct == 0);
        let dknow = [];
        dknow = await knowAssQues.filter(res => res.correct == 2);
        newevt.status = "Completed";
        newevt.correct = correct.length;
        newevt.wrong = wrong.length;
        newevt.ttlq = correct.length + wrong.length + dknow.length;
        newevt.completed_date = ka[0][0].createdAt;
      } else {
        const kq = await db_helper.query("select count(*) as ttlq from know_ass_ques where status=0 and find_in_set(" + baseline_ka[0].id + ",ass_id); select count(*) as ttlcomplete from know_ass_ques_ans where status=0 and active=1 and cohort_id=" + input.cohort_id + " and ass_id=" + baseline_ka[0].id + " and user_id=" + input.user_id);
        newevt.ttlq = kq[0][0][0].ttlq;
        newevt.ttlcomplete = kq[0][1][0].ttlcomplete;
        newevt.completed_date = "";
        if (kq[0][1][0].ttlcomplete == 0) {
          newevt.status = "Not Started";
        } else {
          newevt.status = "In Progress";
        }
      }

      time.push(newevt);
    }
  }

  res.status(200).send(API._200(await time));
};
exports.get_next_live_call = async (req, res) => {
  const input = await helper.decryptRequest(req.body.data);
  const cohort_data = await db_helper.query("select time_zone,events,start_date,live_events,videos,mindmaps,KA_access,PT_access,CAT_access from cohorts where id=" + input.cohort_id);
  var time = []
  if (cohort_data[0][0].live_events) {
    var evts = cohort_data[0][0].live_events.split(",");
    const getaccduration = await db.sequelize.query("SELECT * FROM access_durations WHERE user_id=" + input.user_id + " and cohort_id=" + input.cohort_id)
      .then(function (data) {
        return Array.from(new Set(data[0]));
      });
    for (let ev = 0; ev < evts.length; ev++) {
      if (evts[ev] != 0) {

        let startaccessdate = new Date(new Date().setDate(new Date().getDate() - 1)).toISOString();
        let endaccessdate = new Date(new Date().setDate(new Date().getDate() + 30)).toISOString();
        if (getaccduration.length > 0) {
          startaccessdate = new Date(new Date(getaccduration[0].createdAt)).toISOString();
          endaccessdate = new Date(new Date(getaccduration[0].createdAt).setDate(new Date(getaccduration[0].createdAt).getDate() + getaccduration[0].access_duration)).toISOString();
        }
        var getEvents = [];
        getEvents = await db.sequelize.query("SELECT * FROM all_live_events WHERE live_event_id=" + evts[ev])//  + " and start_date between '" + startaccessdate + "' and '" + endaccessdate + "' and status=0")
          .then(function (data) {
            return Array.from(new Set(data[0]));
          });
        if (getEvents.length <= 0) {
          getEvents = await db.sequelize.query("SELECT *,id AS live_event_id FROM live_events WHERE id=" + evts[ev])//  + " and start_date between '" + startaccessdate + "' and '" + endaccessdate + "' and status=0")
            .then(function (data) {
              return Array.from(new Set(data[0]));
            });
        }
        for (let i = 0; i < getEvents.length; i++) {
          let start_date = getEvents[i].start_date
          if (start_date) {
            let meet_url = "";
            let alreadyJoin = 0;

            let attendy = getEvents[i].attendees ? JSON.parse(getEvents[i].attendees) : [];
            if (attendy.length > 0) {
              let user = attendy.filter(x => x.user_id == input.user_id);
              if (user.length > 0) {
                const meet_count = await db.sequelize.query("select count(*) ttljoin from meeting_chats where aws_meeting_id='" + getEvents[i].meeting_id + "' AND user_id=" + input.user_id + " AND  message_type='log'")
                  .then(function (data) {
                    return Array.from(new Set(data[0]));
                  });
                alreadyJoin = meet_count[0] ? meet_count[0].ttljoin : 0;
                meet_url = {
                  "AttendeeId": user[0].AttendeeId,
                  "meeting_id": getEvents[i].live_event_id
                }

              }
            }
            let due_date = new Date(start_date) //.setHours(getEvents[i].start_time.split(":")[0], getEvents[i].start_time.split(":")[1], 0, 0)
            let overdue_date = new Date(start_date).setHours(getEvents[i].end_time.split(":")[0], getEvents[i].end_time.split(":")[1], 0, 0)
            let event_minutes = (overdue_date - due_date) / 60000;
            var status = "";
            var complete_date = "";
            if (new Date(start_date) >= new Date()) {
              status = "Not Started"
            } else if (new Date() <= new Date(overdue_date)) {
              status = "In Progress"
            } else if (getEvents[i].is_completed == 1) {
              status = "Completed";
              complete_date = getEvents[i].complete_date
            }
            // else {
            //     status = "In Progress"
            // }

            let myevent = {
              examname: getEvents[i].event_name,
              type: "Events",
              cohort_id: input.cohort_id,//cohort_data[0][0].id,
              due_date: getEvents[i].start_date,
              overdue_date: getEvents[i].start_date,//new Date(overdue_date),
              event_duration: event_minutes,
              event_time: getEvents[i].start_time,
              event_url: getEvents[i].meeting_playform == 1 ? getEvents[i].event_link : meet_url,//getEvents[i].url ? getEvents[i].url : "#",
              end_time: getEvents[i].end_time,
              status: status,
              time: "",
              id: getEvents[i].live_event_id,
              event_time_zone: getEvents[i].time_zone,
              complete_date: complete_date,
              meeting_playform: getEvents[i].meeting_playform,
              is_completed: getEvents[i].is_completed,
              is_live: getEvents[i].is_live,
              alreadyJoin: alreadyJoin,
              is_repeat: getEvents[i].is_repeat,
              repeat_count: getEvents[i].repeat_count,
              repeat_type: getEvents[i].repeat_type,
              repeat_on: getEvents[i].repeat_on,
              ends_on: getEvents[i].ends_on,
              ends_type: getEvents[i].ends_type,
              total_occurances: getEvents[i].total_occurances
            }
            time.push(myevent);
          }

        }

      }
    }
  }
  if (cohort_data[0][0].events) {
    var evts = JSON.parse(cohort_data[0][0].events);
    for (let ev = 0; ev < evts.length; ev++) {
      let due_date = new Date(evts[ev].event_date).setHours(parseInt(evts[ev].event_time.split(":")[0]), parseInt(evts[ev].event_time.split(":")[1]), 0, 0)
      let overdue_date = new Date(due_date).setHours(new Date(due_date).getHours(), (new Date(due_date).getMinutes() + parseInt(evts[ev].minutes)), 0, 0)
      let event_minutes = (overdue_date - due_date) / 60000;
      var status = "";
      var complete_date = "";
      var is_completed = 0;
      if (new Date(due_date) > new Date()) {
        status = "Not Started"
      }
      else {
        status = "In Progress"
      }
      let hourmeeting = (parseInt(evts[ev].event_time.split(":")[1]) + parseInt(evts[ev].minutes)) >= 60 ? 1 : 0;
      let myevent = {
        examname: evts[ev].event_name,
        type: "Events",
        cohort_id: cohort_data[0][0].id,
        due_date: evts[ev].event_date,
        overdue_date: new Date(overdue_date),
        event_duration: event_minutes,
        end_time: (Math.round(((parseInt(evts[ev].event_time.split(":")[0]) * 60) + (parseInt(evts[ev].minutes))) / 60) + hourmeeting) + ":" + (Math.floor(((parseInt(evts[ev].event_time.split(":")[1])) + (parseInt(evts[ev].minutes))) % 60).toString().length > 1 ? Math.floor(((parseInt(evts[ev].event_time.split(":")[1])) + (parseInt(evts[ev].minutes))) % 60) : "0" + Math.floor(((parseInt(evts[ev].event_time.split(":")[1])) + (parseInt(evts[ev].minutes))) % 60)),
        event_time: evts[ev].event_time + ":00",//evts[ev].minutes,
        event_url: evts[ev].url ? evts[ev].url : "#",
        status: status,
        time: "",
        id: 0,
        event_time_zone: cohort_data[0][0].time_zone,
        complete_date: complete_date,
        meeting_playform: 1,
        is_completed: is_completed,
        is_live: 0,
        my_data: new Date(due_date)
      }
      time.push(myevent);
    }
  }
  res.status(200).send(API._200(await time));
};
exports.knowasscomplete = async (req, res) => {
  const data = await helper.decryptRequest(req.body.data);
  const select = await db_helper.selectByWhere('KaComplete', data);
  var create;
  if (select.length == 0) {
    create = await db_helper.addData('KaComplete', data);
  } else {
    await db_helper.query("delete from knowcompletes where user_id=" + data.user_id + " and cohort_id=" + data.cohort_id + " and ka_id=" + data.ka_id)
    create = await db_helper.addData('KaComplete', data);
  }
  res.status(200).send(API._200(await create));
};
exports.lbwithdomainid = async (req, res) => {
  const input = await helper.decryptRequest(req.body.data);
  const domains = await Domain.findAll({
    where: {
      certi_id: input.cource_id,
      status: 0
    }
  });
  let data = [];
  let all_score = await db_helper.allData("Scores");
  for (let i = 0; i < domains.length; i++) {
    let kp = 0;
    let ttlkp = 0;
    let pp = 0;
    let ttlpp = 0;
    let fp = 0;
    let ttlfp = 0;
    let knowled = 0;
    let fr = await db_helper.query("SELECT ka.id as id from knowledge_asses as ka inner join cohorts as c on find_in_set(ka.id,c.knowassessments) where find_in_set(" + input.user_id + ",c.userslist) and ka.status=0 group by ka.id");
    for (let ka = 0; ka < fr[0].length; ka++) {
      let ttlkque = await db_helper.query("select count(*) as ttlques from know_ass_ques where status=0 and d_id=" + domains[i].id + " and find_in_set(" + fr[0][ka].id + ",ass_id)");
      knowled += ttlkque[0][0].ttlques;
    }

    let totalKnowPoint = parseInt(knowled) * all_score[0].ka_r;

    const know = await db_helper.query("SELECT SUM(CASE WHEN correct = 1 THEN " + all_score[0].ka_r + " ELSE " + all_score[0].ka_w + " END) AS kpoint FROM know_ass_ques_ans WHERE cohort_id=" + input.cohort_id + " and active=1 and user_id = " + input.user_id + " AND d_id =" + domains[i].id + " and ass_id in(SELECT ka.id as id from knowledge_asses as ka inner join cohorts as c on find_in_set(ka.id,c.knowassessments) where find_in_set(" + input.user_id + ",c.userslist) and ka.status=0 group by ka.id)");

    kp = parseInt(know[0][0].kpoint);
    ttlkp = totalKnowPoint;

    let ttlprque = await db_helper.query("select count(*) as ttlprques from practiceques where d_id=" + domains[i].id + " and 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(" + input.user_id + ",c.userslist) and pt.status=0  group by pt.id)");
    let totalPracticePoint = parseInt(ttlprque[0][0].ttlprques) * all_score[0].pt_r;

    const cohortscorePra = await db_helper.query("SELECT SUM(CASE WHEN correct = 1 THEN " + all_score[0].pt_r + " ELSE " + all_score[0].pt_w + " END) AS ppoint FROM practice_ans WHERE  cohort_id=" + input.cohort_id + " and active=1 and user_id = " + input.user_id + " AND d_id =" + domains[i].id);

    pp = parseInt(cohortscorePra[0][0].ppoint);
    ttlpp = totalPracticePoint;
    const flash = await db_helper.query("select sum(correct) fp, (SELECT count(*) FROM flashcards WHERE d_id=" + domains[i].id + " AND status=0) as ttlcards from flashcard_offlines where UserId=" + input.user_id + " and cohort_id=" + input.cohort_id + " and domain=" + domains[i].id);
    ttlfp = parseInt(flash[0][0].ttlcards);
    fp = parseInt(flash[0][0].fp);

    const video_query = await db_helper.query("SELECT id from videos where d_id=" + domains[i].id + " and status=0; SELECT * from video_mind_views where type_id in(SELECT id from videos where d_id=" + domains[i].id + " and status=0) and type='VIDEO' and user_id=" + input.user_id + " and view_percent=100 and cohort_id=" + input.cohort_id);
    let video_point_ttl = video_query[0][0].length * 2;
    let video_point = video_query[0][1].length * 2;

    let pq_point = 0;
    let pq_point_ttl = 0;
    const ques = await db_helper.query("select sum(case when correct=answer then 1 else 0 end) correct,count(*) ttlq from practiceapp_ans where user_id=" + input.user_id + " and domain_id=" + domains[i].id);
    pq_point_ttl = parseInt(ques[0][0].ttlq);
    pq_point = parseInt(ques[0][0].correct);
    const mind_query = await db_helper.query("SELECT id from mind_maps where d_id=" + domains[i].id + "; SELECT * from video_mind_views where type_id in(SELECT id from mind_maps where d_id=" + domains[i].id + ") and type='MIND' and user_id=" + input.user_id + " and cohort_id=" + input.cohort_id + " and view_percent=100");
    let mind_point_ttl = mind_query[0][0].length;
    let mind_point = mind_query[0][1].length;
    if (isNaN(kp))
      kp = 0;
    data.push({
      id: domains[i].id,
      domin_name: domains[i].domin_name,
      kpoint: kp,
      kpoint_ttl: ttlkp,
      ppoint: pp,
      ppoint_ttl: ttlpp,
      flashpoint: fp > ttlfp ? ttlfp : (fp > 0 ? fp : 0),
      flashpoint_ttl: ttlfp ? ttlfp : 0,
      pq_point: pq_point,
      pq_point_ttl: pq_point_ttl,
      video_point: video_point,
      video_point_ttl: video_point_ttl,
      mind_point: mind_point,
      mind_point_ttl: mind_point_ttl
    })
  }
  res.status(200).send(API._200(await data));
};
exports.usp_leaderborad = async (req, res) => {
  const input = await helper.decryptRequest(req.body.data);
  const time = await db_helper.query("call usp_leaderborad(" + input.cohort_id + ")")
  res.status(200).send(API._200(await time));
};
exports.hybrid_schedules_test = async (req, res) => {
  const input = await helper.decryptRequest(req.body.data);
  const cohort_data = await db_helper.query("select time_zone,events,start_date,live_events,videos,mindmaps,KA_access,PT_access,CAT_access from cohorts where id=" + input.cohort_id);
  const time = await db.sequelize.query("select cs.*,ka.knowass_name,pt.practice_name,cat.ketexam_name,pt.time,cat.time from hybrid_schedules as cs left join knowledge_asses as ka on ka.id=cs.type_id and cs.type='KA' left join practicetests as pt on pt.id = cs.type_id and cs.type = 'PRACTICE' left join ketexamtests as cat on cat.id = cs.type_id and cs.type = 'CAT' left join videos as vid on vid.id = cs.type_id and cs.type = 'video' left join mind_maps as mind on mind.id = cs.type_id and cs.type = 'mindmap' where cs.type <> 'EMAIL' and cs.cohort_id in (select id from cohorts where id = :cohort_id and certi_id = :certi_id and  find_in_set(" + input.user_id + ", userslist)) and (vid.status=0 or ka.status=0 or pt.status=0 or cat.status=0 or mind.status=0 or cs.type='title')", { replacements: { cohort_id: input.cohort_id, certi_id: input.certi_id } })
    .then(function (data) {
      return Array.from(new Set(data[0]));
    })
  let titleDuedate;
  var getminDate = await db.sequelize.query("select min(due_date)due_date from hybrid_schedules where cohort_id = :cohort_id and type != 'title'", { replacements: { cohort_id: input.cohort_id } })
    .then(function (data) {
      return Array.from(new Set(data[0]));
    });
  for (let i = 0; i < time.length; i++) {
    if (i == 0) {
      if (time[i].type == 'title' && time[i].title_type != 'video') {
        time[i].due_date = getminDate[0].due_date;
        titleDuedate = time[i].due_date;
      }
    }
    if (time[i].type != 'title') {
      titleDuedate = time[i].due_date;
    }
    if (time[i].type == "KA") {
      time[i].KA_access = cohort_data[0][0].KA_access;
      time[i].examname = time[i].knowass_name;
      const ka = await db_helper.query("select * from knowcompletes where ka_id=" + time[i].type_id + " and cohort_id=" + input.cohort_id + "  and user_id=" + input.user_id);
      if (ka[0].length > 0) {
        const knowAssQues = await db.sequelize.query("SELECT ans,correct FROM know_ass_ques_ans WHERE user_id=" + input.user_id + " AND active=1 AND ass_id=" + time[i].type_id + " AND cohort_id=" + input.cohort_id)
          .then(function (data) {
            return Array.from(new Set(data[0]));
          });
        let correct = [];
        correct = await knowAssQues.filter(res => res.correct == 1);
        let wrong = [];
        wrong = await knowAssQues.filter(res => res.correct == 0);
        let dknow = [];
        dknow = await knowAssQues.filter(res => res.ans == 2);
        time[i].status = "Completed";
        time[i].correct = correct.length;
        time[i].wrong = wrong.length;
        time[i].completed_date = ka[0][0].createdAt;
        time[i].ttlq = correct.length + wrong.length + dknow.length;
        time[i].ttlcomplete = correct.length + wrong.length + dknow.length;
      } else {
        const kq = await db_helper.query("select count(*) as ttlq from know_ass_ques where status=0 and find_in_set(" + time[i].type_id + ",ass_id); select count(*) as ttlcomplete from know_ass_ques_ans where status=0 and active=1 and cohort_id=" + input.cohort_id + " and ass_id=" + time[i].type_id + " and user_id=" + input.user_id);
        time[i].ttlq = kq[0][0][0].ttlq;
        time[i].ttlcomplete = kq[0][1][0].ttlcomplete;
        time[i].completed_date = "";
        if (kq[0][1][0].ttlcomplete == 0) {
          time[i].status = "Not Started";
        } else {
          time[i].status = "In Progress";
        }
      }
    } else if (time[i].type == "CAT") {
      time[i].KA_access = cohort_data[0][0].CAT_access;
      time[i].examname = time[i].ketexam_name;
      const cat = await db_helper.query("select * from ctestcomplates where ct_id=" + time[i].type_id + " and cohort_id=" + input.cohort_id + " and user_id=" + input.user_id);
      if (cat[0].length > 0) {
        time[i].status = "Completed";
        time[i].completed_date = cat[0][0].createdAt;
        const catexamQues = await db.sequelize.query("SELECT CASE WHEN pa.ans IS NULL then 'pending' WHEN ptq.answer=pa.ans THEN 'true' ELSE 'false' END as validation FROM ketexamques as ptq LEFT JOIN (SELECT * from ketexam_ans where user_id=" + input.user_id + " and test_id=" + time[i].type_id + " and cohort_id=" + input.cohort_id + ") as pa ON ptq.id=pa.KEQ_id and find_in_set(pa.test_id,ptq.exam_id) WHERE FIND_IN_SET(" + time[i].type_id + ",ptq.exam_id) and ptq.status=0")
          .then(function (data) {
            return Array.from(new Set(data[0]));
          });
        let correct = [];
        correct = await catexamQues.filter(res => res.validation == "true");
        let wrong = [];
        wrong = await catexamQues.filter(res => res.validation == "false");
        time[i].correct = correct.length;
        time[i].wrong = wrong.length;
        time[i].ttlq = correct.length + wrong.length;
        time[i].ttlcomplete = 0;
      } else {
        const cat = await db_helper.query("select count(*) as ttlq from ketexamques where status=0 and find_in_set(" + time[i].type_id + ",exam_id); select count(*) as ttlcomplete from ketexam_ans where status=0 and cohort_id=" + input.cohort_id + " and  test_id=" + time[i].type_id + " and user_id=" + input.user_id);
        time[i].ttlq = cat[0][0][0].ttlq;
        time[i].ttlcomplete = cat[0][1][0].ttlcomplete;
        time[i].completed_date = "";
        if (cat[0][1][0].ttlcomplete == 0) {
          time[i].status = "Not Started";
        } else {
          time[i].status = "In Progress";
        }
      }
    } else if (time[i].type == "PRACTICE") {
      time[i].KA_access = cohort_data[0][0].PT_access;
      time[i].examname = time[i].practice_name;
      const cat = await db_helper.query("select * from ptestcomplates where pt_id=" + time[i].type_id + " and cohort_id=" + input.cohort_id + "  and user_id=" + input.user_id);
      if (cat[0].length > 0) {
        time[i].status = "Completed";
        time[i].completed_date = cat[0][0].createdAt;
        const practiceQues = await db.sequelize.query("SELECT (CASE WHEN pa.ans IS NULL then 'pending' WHEN ptq.answer=pa.ans THEN 'true' ELSE 'false' END) as validation FROM practiceques as ptq LEFT JOIN (SELECT * from practice_ans where cohort_id=" + input.cohort_id + " and user_id=" + input.user_id + " and active=1 and test_id=" + time[i].type_id + ") as pa ON ptq.id=pa.ptq_id WHERE FIND_IN_SET(" + time[i].type_id + ",ptq.test_id) and ptq.status=0")
          .then(function (data) {
            return Array.from(new Set(data[0]));
          });
        let correct = [];
        correct = await practiceQues.filter(res => res.validation == "true");
        let wrong = [];
        wrong = await practiceQues.filter(res => res.validation == "false");
        time[i].correct = correct.length;
        time[i].wrong = wrong.length;
        time[i].ttlq = correct.length + wrong.length;
        time[i].ttlcomplete = 0;
      } else {
        const prac = await db_helper.query("select count(*) as ttlq from practiceques where status=0 and test_id=" + time[i].type_id + "; select count(*) as ttlcomplete from practice_ans where active=1 and cohort_id=" + input.cohort_id + " and  test_id=" + time[i].type_id + " and user_id=" + input.user_id);
        time[i].ttlq = prac[0][0][0].ttlq;
        time[i].ttlcomplete = prac[0][1][0].ttlcomplete;
        time[i].completed_date = "";
        if (prac[0][1][0].ttlcomplete == 0) {
          time[i].status = "Not Started";
        } else {
          time[i].status = "In Progress";
        }
      }
    } else if (time[i].type == "video") {
      const videos = await db_helper.query("select v.id,case when vv.view_percent is null then 0 else vv.view_percent end view_percent,v.video_title,v.duration,case when vv.view_percent = 100 then vv.createdAt else null end view_date,case when vv.view_percent = 100 then 'Completed'  else 'Not Started' end status from videos as v left join video_mind_views vv on vv.type_id=v.id and vv.type='VIDEO' and vv.user_id=" + input.user_id + " and vv.cohort_id=" + input.cohort_id + " where v.status=0 and v.id =" + time[i].type_id);
      if (videos[0].length > 0) {
        time[i].status = videos[0][0].status;
        time[i].percentage = videos[0][0].view_percent;
        time[i].examname = videos[0][0].video_title;
        time[i].time = videos[0][0].duration;
        time[i].completed_date = videos[0][0].view_date;
        time[i].type = "VideoLesson";
      }
    }
    else if (time[i].type == "mindmap") {
      const videos = await db_helper.query("select 0 view_percent,v.id,v.name,case when vv.view_percent = 100 then vv.createdAt else null end view_date,v.duration,case when vv.user_id =" + input.user_id + " then 'Completed' else 'Not Started' end status from mind_maps as v left join video_mind_views vv on vv.type_id=v.id and vv.type='MIND' and vv.user_id=" + input.user_id + " and vv.cohort_id=" + input.cohort_id + " where v.status=0 and v.id=" + time[i].type_id);
      if (videos[0].length > 0) {
        time[i].status = videos[0][0].status;
        time[i].percentage = videos[0][0].view_percent;
        time[i].examname = videos[0][0].name;
        time[i].time = videos[0][0].duration;
        time[i].completed_date = videos[0][0].view_date;
        time[i].type = "MindMap";
      }
    }
    else if (time[i].type == 'title') {
      time[i].due_date = titleDuedate;
      if (time[i].title_type == "video") {
        time[i].type = 'VideoLesson';
        time[i].percentage = 0;
        time[i].completed_date = "";
        time[i].status = "Not Started";
        time[i].time = "00:00";
        time[i].examname = time[i].title_name;
      }
      else {
        time[i].examname = time[i].title_name;
      }
    }
    time[i]['homework'] = 1;
  }
  if (cohort_data[0][0].live_events) {
    var evts = cohort_data[0][0].live_events.split(",");
    for (let ev = 0; ev < evts.length; ev++) {
      if (evts[ev] != 0) {
        const getaccduration = await db.sequelize.query("SELECT * FROM access_durations WHERE user_id=" + input.user_id + " and cohort_id=" + input.cohort_id)
          .then(function (data) {
            return Array.from(new Set(data[0]));
          });
        let startaccessdate = new Date(new Date().setDate(new Date().getDate() - 1)).toISOString();
        let endaccessdate = new Date(new Date().setDate(new Date().getDate() + 30)).toISOString();
        if (getaccduration.length > 0) {
          startaccessdate = new Date(new Date(getaccduration[0].createdAt)).toISOString();
          endaccessdate = new Date(new Date(getaccduration[0].createdAt).setDate(new Date(getaccduration[0].createdAt).getDate() + getaccduration[0].access_duration)).toISOString();
        }
        const getEvents = await db.sequelize.query("SELECT * FROM all_live_events WHERE live_event_id=" + evts[ev] + " and start_date between '" + startaccessdate + "' and '" + endaccessdate + "' and status=0")
          .then(function (data) {
            return Array.from(new Set(data[0]));
          });
        for (let i = 0; i < getEvents.length; i++) {
          let start_date = getEvents[i].start_date
          if (start_date) {
            let meet_url = "";
            let alreadyJoin = 0;

            let attendy = getEvents[i].attendees ? JSON.parse(getEvents[i].attendees) : [];
            if (attendy.length > 0) {
              let user = attendy.filter(x => x.user_id == input.user_id);
              if (user.length > 0) {
                const meet_count = await db.sequelize.query("select count(*) ttljoin from meeting_chats where aws_meeting_id='" + getEvents[i].meeting_id + "' AND user_id=" + input.user_id + " AND  message_type='log'")
                  .then(function (data) {
                    return Array.from(new Set(data[0]));
                  });
                alreadyJoin = meet_count[0] ? meet_count[0].ttljoin : 0;
                meet_url = {
                  "AttendeeId": user[0].AttendeeId,
                  "meeting_id": getEvents[i].live_event_id
                }

              }
            }
            let due_date = new Date(start_date).setHours(getEvents[i].start_time.split(":")[0], getEvents[i].start_time.split(":")[1], 0, 0)
            let overdue_date = new Date(start_date).setHours(getEvents[i].end_time.split(":")[0], getEvents[i].end_time.split(":")[1], 0, 0)
            let event_minutes = (overdue_date - due_date) / 60000;
            var status = "";
            var complete_date = "";
            if (new Date(due_date) > new Date()) {
              status = "Not Started"
            } else if (new Date() < new Date(overdue_date)) {
              status = "In Progress"
            } else if (getEvents[i].is_completed == 1) {
              status = "Completed";
              complete_date = getEvents[i].complete_date
            } else {
              status = "In Progress"
            }
            let myevent = {
              examname: getEvents[i].event_name,
              type: "Events",
              cohort_id: input.cohort_id,//cohort_data[0][0].id,
              due_date: getEvents[i].start_date,//new Date(due_date),
              overdue_date: getEvents[i].start_date,//new Date(overdue_date),
              event_duration: event_minutes,
              event_time: getEvents[i].start_time,
              event_url: getEvents[i].meeting_playform == 1 ? getEvents[i].event_link : meet_url,//getEvents[i].url ? getEvents[i].url : "#",
              end_time: getEvents[i].end_time,
              status: status,
              time: "",
              id: getEvents[i].live_event_id,
              event_time_zone: getEvents[i].time_zone,
              complete_date: complete_date,
              meeting_playform: getEvents[i].meeting_playform,
              is_completed: getEvents[i].is_completed,
              is_live: getEvents[i].is_live,
              alreadyJoin: alreadyJoin
            }
            time.push(myevent);
          }

        }

      }
    }
  }
  if (cohort_data[0][0].events) {
    var evts = JSON.parse(cohort_data[0][0].events);
    for (let ev = 0; ev < evts.length; ev++) {
      let due_date = new Date(evts[ev].event_date).setHours(parseInt(evts[ev].event_time.split(":")[0]), parseInt(evts[ev].event_time.split(":")[1]), 0, 0)
      let overdue_date = new Date(due_date).setHours(new Date(due_date).getHours(), (new Date(due_date).getMinutes() + parseInt(evts[ev].minutes)), 0, 0)
      let event_minutes = (overdue_date - due_date) / 60000;
      var status = "";
      var complete_date = "";
      var is_completed = 0;
      if (new Date(due_date) > new Date()) {
        status = "Not Started"
      }
      else {
        status = "In Progress"
      }
      let hourmeeting = (parseInt(evts[ev].event_time.split(":")[1]) + parseInt(evts[ev].minutes)) >= 60 ? 1 : 0;
      let myevent = {
        examname: evts[ev].event_name,
        type: "Events",
        cohort_id: cohort_data[0][0].id,
        due_date: evts[ev].event_date,//new Date(due_date),
        overdue_date: new Date(overdue_date),
        event_duration: event_minutes,
        end_time: (Math.round(((parseInt(evts[ev].event_time.split(":")[0]) * 60) + (parseInt(evts[ev].minutes))) / 60) + hourmeeting) + ":" + (Math.floor(((parseInt(evts[ev].event_time.split(":")[1])) + (parseInt(evts[ev].minutes))) % 60).toString().length > 1 ? Math.floor(((parseInt(evts[ev].event_time.split(":")[1])) + (parseInt(evts[ev].minutes))) % 60) : "0" + Math.floor(((parseInt(evts[ev].event_time.split(":")[1])) + (parseInt(evts[ev].minutes))) % 60)),
        //Math.floor(parseInt(evts[ev].minutes) / 60) + ":" + Math.floor(parseInt(evts[ev].minutes) % 60),
        event_time: evts[ev].event_time + ":00",//evts[ev].minutes,
        event_url: evts[ev].url ? evts[ev].url : "#",
        status: status,
        time: "",
        id: 0,
        event_time_zone: cohort_data[0][0].time_zone,
        complete_date: complete_date,
        meeting_playform: 1,
        is_completed: is_completed,
        is_live: 0,
        my_data: new Date(due_date)
      }
      time.push(myevent);
    }
  }
  res.status(200).send(API._200(await time));
};
exports.schedules_test = async (req, res) => {
  const input = await helper.decryptRequest(req.body.data);
  const cohort_data = await db_helper.query("select time_zone,events,start_date,live_events,videos,mindmaps,KA_access,PT_access,CAT_access from cohorts where id=" + input.cohort_id);

  const time = await db.sequelize.query("select cs.*,ka.knowass_name,pt.practice_name,cat.ketexam_name,pt.time,cat.time from cohort_schedules as cs left join knowledge_asses as ka on ka.id=cs.type_id and cs.type='KA' and ka.knowass_name!='Baseline' left join practicetests as pt on pt.id = cs.type_id and cs.type = 'PRACTICE' left join ketexamtests as cat on cat.id = cs.type_id and cs.type = 'CAT' where cs.type <> 'EMAIL' and cs.cohort_id in (select id from cohorts where id = :cohort_id and certi_id = :certi_id  and find_in_set(" + input.user_id + ", userslist)) and (ka.status=0 or pt.status=0 or cat.status=0)", { replacements: { cohort_id: input.cohort_id, certi_id: input.certi_id } })
    .then(function (data) {
      return Array.from(new Set(data[0]));
    })

  for (let i = 0; i < time.length; i++) {
    if (time[i].type == "KA") {
      time[i].KA_access = cohort_data[0][0].KA_access;
      time[i].examname = time[i].knowass_name;
      const ka = await db_helper.query("select * from knowcompletes where ka_id=" + time[i].type_id + " and cohort_id=" + input.cohort_id + "  and user_id=" + input.user_id);
      if (ka[0].length > 0) {
        const knowAssQues = await db.sequelize.query("SELECT ans,correct FROM know_ass_ques_ans WHERE user_id=" + input.user_id + " AND active=1 AND ass_id=" + time[i].type_id + " AND cohort_id=" + input.cohort_id)
          .then(function (data) {
            return Array.from(new Set(data[0]));
          });
        let correct = [];
        correct = await knowAssQues.filter(res => res.correct == 1);
        let wrong = [];
        wrong = await knowAssQues.filter(res => res.correct == 0);
        let dknow = [];
        dknow = await knowAssQues.filter(res => res.ans == 2);
        time[i].status = "Completed";
        time[i].correct = correct.length;
        time[i].wrong = wrong.length;
        time[i].completed_date = ka[0][0].createdAt;
        time[i].ttlq = correct.length + wrong.length + dknow.length;
        time[i].ttlcomplete = correct.length + wrong.length + dknow.length;
      } else {
        const kq = await db_helper.query("select count(*) as ttlq from know_ass_ques where status=0 and find_in_set(" + time[i].type_id + ",ass_id); select count(*) as ttlcomplete from know_ass_ques_ans where status=0 and active=1 and cohort_id=" + input.cohort_id + " and ass_id=" + time[i].type_id + " and user_id=" + input.user_id);
        time[i].ttlq = kq[0][0][0].ttlq;
        time[i].ttlcomplete = kq[0][1][0].ttlcomplete;
        time[i].completed_date = "";
        if (kq[0][1][0].ttlcomplete == 0) {
          time[i].status = "Not Started";
        } else {
          time[i].status = "In Progress";
        }
      }
    } else if (time[i].type == "CAT") {
      time[i].KA_access = cohort_data[0][0].CAT_access;
      time[i].examname = time[i].ketexam_name;
      const cat = await db_helper.query("select * from ctestcomplates where ct_id=" + time[i].type_id + " and cohort_id=" + input.cohort_id + " and user_id=" + input.user_id);
      if (cat[0].length > 0) {
        time[i].status = "Completed";
        time[i].completed_date = cat[0][0].createdAt;
        const catexamQues = await db.sequelize.query("SELECT CASE WHEN pa.ans IS NULL then 'pending' WHEN ptq.answer=pa.ans THEN 'true' ELSE 'false' END as validation FROM ketexamques as ptq LEFT JOIN (SELECT * from ketexam_ans where user_id=" + input.user_id + " and test_id=" + time[i].type_id + " and cohort_id=" + input.cohort_id + ") as pa ON ptq.id=pa.KEQ_id and find_in_set(pa.test_id,ptq.exam_id) WHERE FIND_IN_SET(" + time[i].type_id + ",ptq.exam_id) and ptq.status=0")
          .then(function (data) {
            return Array.from(new Set(data[0]));
          });
        let correct = [];
        correct = await catexamQues.filter(res => res.validation == "true");
        let wrong = [];
        wrong = await catexamQues.filter(res => res.validation == "false");
        time[i].correct = correct.length;
        time[i].wrong = wrong.length;
        time[i].ttlq = correct.length + wrong.length;
        time[i].ttlcomplete = 0;
      } else {
        const cat = await db_helper.query("select count(*) as ttlq from ketexamques where status=0 and find_in_set(" + time[i].type_id + ",exam_id); select count(*) as ttlcomplete from ketexam_ans where status=0 and cohort_id=" + input.cohort_id + " and  test_id=" + time[i].type_id + " and user_id=" + input.user_id);
        time[i].ttlq = cat[0][0][0].ttlq;
        time[i].ttlcomplete = cat[0][1][0].ttlcomplete;
        time[i].completed_date = "";
        if (cat[0][1][0].ttlcomplete == 0) {
          time[i].status = "Not Started";
        } else {
          time[i].status = "In Progress";
        }
      }
    } else if (time[i].type == "PRACTICE") {
      time[i].KA_access = cohort_data[0][0].PT_access;
      time[i].examname = time[i].practice_name;
      const cat = await db_helper.query("select * from ptestcomplates where pt_id=" + time[i].type_id + " and cohort_id=" + input.cohort_id + "  and user_id=" + input.user_id);
      if (cat[0].length > 0) {
        time[i].status = "Completed";
        time[i].completed_date = cat[0][0].createdAt;
        const practiceQues = await db.sequelize.query("SELECT (CASE WHEN pa.ans IS NULL then 'pending' WHEN ptq.answer=pa.ans THEN 'true' ELSE 'false' END) as validation FROM practiceques as ptq LEFT JOIN (SELECT * from practice_ans where cohort_id=" + input.cohort_id + " and user_id=" + input.user_id + " and active=1 and test_id=" + time[i].type_id + ") as pa ON ptq.id=pa.ptq_id WHERE FIND_IN_SET(" + time[i].type_id + ",ptq.test_id) and ptq.status=0")
          .then(function (data) {
            return Array.from(new Set(data[0]));
          });
        let correct = [];
        correct = await practiceQues.filter(res => res.validation == "true");
        let wrong = [];
        wrong = await practiceQues.filter(res => res.validation == "false");
        time[i].correct = correct.length;
        time[i].wrong = wrong.length;
        time[i].ttlq = correct.length + wrong.length;
        time[i].ttlcomplete = 0;
      } else {
        const prac = await db_helper.query("select count(*) as ttlq from practiceques where status=0 and test_id=" + time[i].type_id + "; select count(*) as ttlcomplete from practice_ans where active=1 and cohort_id=" + input.cohort_id + " and  test_id=" + time[i].type_id + " and user_id=" + input.user_id);
        time[i].ttlq = prac[0][0][0].ttlq;
        time[i].ttlcomplete = prac[0][1][0].ttlcomplete;
        time[i].completed_date = "";
        if (prac[0][1][0].ttlcomplete == 0) {
          time[i].status = "Not Started";
        } else {
          time[i].status = "In Progress";
        }
      }
    }
    time[i]['homework'] = 1;
  }
  if (cohort_data[0][0].live_events) {
    var evts = cohort_data[0][0].live_events.split(",");
    for (let ev = 0; ev < evts.length; ev++) {
      if (evts[ev] != 0) {
        const getaccduration = await db.sequelize.query("SELECT * FROM access_durations WHERE user_id=" + input.user_id + " and cohort_id=" + input.cohort_id)
          .then(function (data) {
            return Array.from(new Set(data[0]));
          });
        let startaccessdate = new Date(new Date().setDate(new Date().getDate() - 1)).toISOString();
        let endaccessdate = new Date(new Date().setDate(new Date().getDate() + 30)).toISOString();
        if (getaccduration.length > 0) {
          startaccessdate = new Date(new Date(getaccduration[0].createdAt)).toISOString();
          endaccessdate = new Date(new Date(getaccduration[0].createdAt).setDate(new Date(getaccduration[0].createdAt).getDate() + getaccduration[0].access_duration)).toISOString();
        }
        var getEvents = []
        getEvents = await db.sequelize.query("SELECT * FROM all_live_events WHERE live_event_id=" + evts[ev] + " and start_date between '" + startaccessdate + "' and '" + endaccessdate + "' and status=0")
          .then(function (data) {
            return Array.from(new Set(data[0]));
          });
        if (getEvents.length <= 0) {
          getEvents = await db.sequelize.query("SELECT *,id live_event_id FROM live_events WHERE id=" + evts[ev] + " and status=0")
            .then(function (data) {
              return Array.from(new Set(data[0]));
            });
        }
        for (let i = 0; i < getEvents.length; i++) {
          let start_date = getEvents[i].start_date
          if (start_date) {
            let meet_url = "";
            let alreadyJoin = 0;

            let attendy = getEvents[i].attendees ? JSON.parse(getEvents[i].attendees) : [];
            if (attendy.length > 0) {
              let user = attendy.filter(x => x.user_id == input.user_id);
              if (user.length > 0) {
                const meet_count = await db.sequelize.query("select count(*) ttljoin from meeting_chats where aws_meeting_id='" + getEvents[i].meeting_id + "' AND user_id=" + input.user_id + " AND  message_type='log'")
                  .then(function (data) {
                    return Array.from(new Set(data[0]));
                  });
                alreadyJoin = meet_count[0] ? meet_count[0].ttljoin : 0;
                meet_url = {
                  "AttendeeId": user[0].AttendeeId,
                  "meeting_id": getEvents[i].live_event_id
                }
                // meet_url = "http://64.23.254.43/#/conference/" + btoa(JSON.stringify(mylink));

              }
            }
            let due_date = new Date(start_date).setHours(getEvents[i].start_time.split(":")[0], getEvents[i].start_time.split(":")[1], 0, 0)
            let overdue_date = new Date(start_date).setHours(getEvents[i].end_time.split(":")[0], getEvents[i].end_time.split(":")[1], 0, 0)
            let event_minutes = (overdue_date - due_date) / 60000;
            var status = "";
            var complete_date = "";
            if (new Date(due_date) > new Date()) {
              status = "Not Started"
            } else if (new Date() < new Date(overdue_date)) {
              status = "In Progress"
            } else if (getEvents[i].is_completed == 1) {
              status = "Completed";
              complete_date = getEvents[i].complete_date
            } else {
              status = "In Progress"
            }
            let myevent = {
              examname: getEvents[i].event_name,
              type: "Events",
              cohort_id: input.cohort_id,//cohort_data[0][0].id,
              due_date: getEvents[i].start_date,//new Date(due_date),
              overdue_date: getEvents[i].start_date,//new Date(overdue_date),
              event_duration: event_minutes,
              event_time: getEvents[i].start_time,
              event_url: getEvents[i].meeting_playform == 1 ? getEvents[i].event_link : meet_url,//getEvents[i].url ? getEvents[i].url : "#",
              end_time: getEvents[i].end_time,
              status: status,
              time: "",
              id: getEvents[i].live_event_id,
              event_time_zone: getEvents[i].time_zone,
              complete_date: complete_date,
              meeting_playform: getEvents[i].meeting_playform,
              is_completed: getEvents[i].is_completed,
              is_live: getEvents[i].is_live,
              alreadyJoin: alreadyJoin,
              is_day_event: false
            }
            time.push(myevent);
          }

        }

      }
    }
  }
  if (cohort_data[0][0].events) {
    var evts = JSON.parse(cohort_data[0][0].events);
    for (let ev = 0; ev < evts.length; ev++) {
      let event_time = evts[ev].event_time.split(":")
      let time_zone = JSON.parse(cohort_data[0][0].time_zone)
      let stringDate = evts[ev].event_date.split("T")[0] + "T" + event_time[0].toString().padStart(2, "0") + ":" + event_time[1].toString().padStart(2, "0") + ":00" + time_zone.timeValue
      let due_date = new Date(evts[ev].event_date).setHours(parseInt(evts[ev].event_time.split(":")[0]), parseInt(evts[ev].event_time.split(":")[1]), 0, 0)
      let overdue_date = new Date(due_date).setHours(new Date(due_date).getHours(), (new Date(due_date).getMinutes() + parseInt(evts[ev].minutes)), 0, 0)
      let event_minutes = (overdue_date - due_date) / 60000;
      var status = "";
      var complete_date = "";
      var is_completed = 0;

      if (new Date(due_date) > new Date()) {
        status = "Not Started"
      } else if (new Date() < new Date(overdue_date)) {
        status = "In Progress"
      } else {
        status = "Completed";
        complete_date = new Date(overdue_date)
        is_completed = 1
      }

      let hourmeeting = (parseInt(evts[ev].event_time.split(":")[1]) + parseInt(evts[ev].minutes)) >= 60 ? 1 : 0;
      let myevent = {
        examname: evts[ev].event_name,
        type: "Events",
        cohort_id: cohort_data[0][0].id,
        due_date: stringDate,//evts[ev].event_date,//new Date(due_date),
        overdue_date: new Date(overdue_date),
        event_duration: event_minutes,
        end_time: (Math.round(((parseInt(evts[ev].event_time.split(":")[0]) * 60) + (parseInt(evts[ev].minutes))) / 60) + hourmeeting) + ":" + (Math.floor(((parseInt(evts[ev].event_time.split(":")[1])) + (parseInt(evts[ev].minutes))) % 60).toString().length > 1 ? Math.floor(((parseInt(evts[ev].event_time.split(":")[1])) + (parseInt(evts[ev].minutes))) % 60) : "0" + Math.floor(((parseInt(evts[ev].event_time.split(":")[1])) + (parseInt(evts[ev].minutes))) % 60)),
        event_time: evts[ev].event_time + ":00",//evts[ev].minutes,
        event_url: evts[ev].url ? evts[ev].url : "#",
        status: status,
        time: "",
        id: 0,
        event_time_zone: cohort_data[0][0].time_zone,
        complete_date: complete_date,
        meeting_playform: 1,
        is_completed: is_completed,
        is_live: 0,
        my_data: new Date(due_date),
        is_day_event: true
      }
      time.push(myevent);
    }
  }
  res.status(200).send(API._200(await time));
};

exports.selfpaced_schedules_test = async (req, res) => {
  const input = await helper.decryptRequest(req.body.data);
  const time = await db.sequelize.query("select cs.*,cs.duration vid_sec_duration,m.name mindmap_title,m.duration mindmap_duration,vid.video_title,vid.duration,ka.knowass_name,pt.practice_name from self_paced_materials as cs left join knowledge_asses as ka on ka.id=cs.material_id and cs.material_type='KA' left join practicetests as pt on pt.id=cs.material_id and cs.material_type='Test' left join videos as vid on vid.id=cs.material_id and cs.material_type='Video' left join mind_maps as m on m.id=cs.material_id and cs.material_type='Mindmap' where cs.method_id= :method_id and cs.method_type='" + input.method_type + "'", { replacements: { method_id: input.method_id } })
    .then(function (data) {
      return Array.from(new Set(data[0]));
    })
  const dueDate = await db.sequelize.query("select * from payment_transactions where user_id=" + input.user_id + " and plan_id=" + input.method_id + " order by id desc limit 1")
    .then(function (data) {
      return Array.from(new Set(data[0]));
    })
  if (dueDate.length <= 0) {
    const users = await db.sequelize.query("select * from users where id= :user_id", { replacements: { user_id: input.user_id } })
      .then(function (data) {
        return Array.from(new Set(data[0]));
      })
    if (users.length > 0) {
      let createDate = new Date(users[0].createdAt)
      createDate.setFullYear(createDate.getFullYear() + 1)
      dueDate.push({ createdAt: createDate.toISOString() })
    }
  }
  let total_flashcards = 0;
  let total_practice = 0;
  for (let i = 0; i < time.length; i++) {
    time[i].due_date = dueDate[0].createdAt;
    if (time[i].material_type == 'Video') {
      const vids = await db.sequelize.query("select view_percent,case when view_percent = 100 then createdAt else null end  video_view_date,case when view_percent >= 100 then 'Completed'  else (case when view_percent>0 then 'In Progress' else 'Not Started' end ) end video_status from video_mind_views where type_id=" + time[i].material_id + " AND type='VIDEO' and user_id=" + input.user_id + " and cohort_id=" + input.cohort_id + " and status=0")
        .then(function (data) {
          return Array.from(new Set(data[0]));
        });
      if (vids.length > 0) {
        time[i].view_percent = vids[0].view_percent ? vids[0].view_percent : 0;
        time[i].view_date = vids[0].video_view_date;
        time[i].status = vids[0].video_status
      } else {
        time[i].view_percent = 0;
        time[i].view_date = null;
        time[i].status = 'Not Started'
      }
    }
    if (time[i].material_type == 'title') {
      if (time[i].title_type == "Video") {
        const vid_sec = await db_helper.query("select * from video_section_views where user_id=" + input.user_id + " and type_id=" + time[i].id + " and cohort_id=" + input.cohort_id);
        if (vid_sec[0].length > 0) {
          if (vid_sec[0][0].is_completed == 1) {
            time[i].view_percent = 100;
            time[i].view_date = vid_sec[0][0].createdAt;
            time[i].status = "Completed";
          } else {
            time[i].view_percent = 0;
            time[i].view_date = "";
            time[i].status = "Not Started";
          }
        } else {
          time[i].view_percent = 0;
          time[i].view_date = "";
          time[i].status = "Not Started";
        }
        if (time[i].vid_sec_duration) {
          time[i].duration = time[i].vid_sec_duration;
        } else {
          time[i].duration = "00:00";
        }
        time[i].material_type = 'Video';


        time[i].video_title = time[i].title_name;
      }
      else if (time[i].title_type == 'Section') {
        let title_name = time[i].title_name
        let title_name_array = title_name.split(": ")
        if (title_name_array.length > 0) {
          let domain_name = title_name_array[1]
          const flashcard = await db.sequelize.query("select count(*) ttl_cards from flashcards where status=0 and d_id in(select id from domains where certi_id=11 and domin_name like '%" + domain_name + "%')")
            .then(function (data) {
              return Array.from(new Set(data[0]));
            })
          time[i].download_description = flashcard.length > 0 ? flashcard[0].ttl_cards : null
          total_flashcards = flashcard.length > 0 ? flashcard[0].ttl_cards : 0;
          const PAQues = await db.sequelize.query("select count(*) ttl_ques from practice_appques where status=0 and d_id in(select id from domains where certi_id=11 and domin_name like '%" + domain_name + "%')")
            .then(function (data) {
              return Array.from(new Set(data[0]));
            })
          time[i].download_url = PAQues.length > 0 ? PAQues[0].ttl_ques : null
          total_practice = PAQues.length > 0 ? PAQues[0].ttl_ques : 0;

        }
      }
    }
    if (time[i].title_type != 'Download' && time[i].download_url == null) {
      time[i].download_description = total_flashcards
      time[i].download_url = total_practice
    }
    if (time[i].material_type == 'Mindmap') {
      const vids = await db.sequelize.query("select case when view_percent = 100 then createdAt else null end mindmap_view_date,case when view_percent = 100 then 'Completed'  else 'Not Started' end mindmap_status from video_mind_views where type_id=" + time[i].material_id + " and type='MIND' and user_id= " + input.user_id + " and cohort_id=" + input.cohort_id + " and status=0")
        .then(function (data) {
          return Array.from(new Set(data[0]));
        });
      if (vids.length > 0) {
        time[i].view_percent = vids[0].view_percent ? vids[0].view_percent : 0;
        time[i].view_date = vids[0].mindmap_view_date;
        time[i].status = vids[0].mindmap_status
      } else {
        time[i].view_percent = 0;
        time[i].view_date = null;
        time[i].status = 'Not Started'
      }
    }
    if (time[i].material_type == 'KA') {
      time[i].examname = time[i].knowass_name;
      const ka = await db_helper.query("select * from knowcompletes where ka_id=" + time[i].material_id + " and cohort_id=" + input.cohort_id + "  and user_id=" + input.user_id);
      if (ka[0].length > 0) {
        const knowAssQues = await db.sequelize.query("SELECT ans,correct FROM know_ass_ques_ans WHERE user_id=" + input.user_id + " AND active=1 AND ass_id=" + time[i].material_id + " AND cohort_id=" + input.cohort_id)
          .then(function (data) {
            return Array.from(new Set(data[0]));
          });
        let correct = [];
        correct = await knowAssQues.filter(res => res.correct == 1);
        let wrong = [];
        wrong = await knowAssQues.filter(res => res.correct == 0);
        let dknow = [];
        dknow = await knowAssQues.filter(res => res.ans == 2);
        time[i].status = "Completed";
        time[i].correct = correct.length;
        time[i].wrong = wrong.length;
        time[i].ttlq = correct.length + wrong.length + dknow.length;
        time[i].completed_date = ka[0][0].createdAt;
        time[i].ttlcomplete = correct.length + wrong.length + dknow.length;
      } else {

        const kq = await db.sequelize.query("select count(*) as ttlq from know_ass_ques where status=0 and find_in_set(" + time[i].material_id + ",ass_id); select count(*) as ttlcomplete from know_ass_ques_ans where active=1 and cohort_id= :cohort_id and ass_id=" + time[i].material_id + " and user_id= :user_id", { replacements: { user_id: input.user_id, cohort_id: input.cohort_id } }).then((res) => {
          return Array.from(new Set(res));
        })
        time[i].ttlq = kq[0][0][0].ttlq;
        time[i].ttlcomplete = kq[0][1][0].ttlcomplete;
        time[i].completed_date = "";
        if (kq[0][1][0].ttlcomplete == 0) {
          time[i].status = "Not Started";
        } else {
          time[i].status = "In Progress";
        }
      }
    }
    if (time[i].material_type == "Test") {
      time[i].examname = time[i].practice_name;

      const cat = await db_helper.query("select * from ptestcomplates where pt_id=" + time[i].material_id + " and cohort_id=" + input.cohort_id + "  and user_id=" + input.user_id);
      if (cat[0].length > 0) {
        time[i].status = "Completed";
        time[i].completed_date = cat[0][0].createdAt;
        const practiceQues = await db.sequelize.query("select ans,correct from practice_ans where user_id=" + input.user_id + " and cohort_id=" + input.cohort_id + " and active=1 and test_id=" + time[i].material_id + " and status=0")
          .then(function (data) {
            return Array.from(new Set(data[0]));
          });
        let correct = [];
        correct = await practiceQues.filter(res => res.correct == 1);
        let wrong = [];
        wrong = await practiceQues.filter(res => res.correct == 0);
        time[i].correct = correct.length;
        time[i].wrong = wrong.length;
        time[i].ttlq = correct.length + wrong.length;
        time[i].ttlcomplete = correct.length + wrong.length;
      } else {

        const prac = await db.sequelize.query("select count(*) as ttlq from practiceques where test_id=" + time[i].material_id + "; select count(*) as ttlcomplete from practice_ans where active=1 and cohort_id= :cohort_id and  test_id=" + time[i].material_id + " and user_id= :user_id", { replacements: { user_id: input.user_id, cohort_id: input.cohort_id } }).then((res) => {
          return Array.from(new Set(res));
        })
        time[i].ttlq = prac[0][0][0].ttlq;
        time[i].ttlcomplete = prac[0][1][0].ttlcomplete;
        time[i].completed_date = "";
        if (prac[0][1][0].ttlcomplete == 0) {
          time[i].status = "Not Started";
        } else {
          time[i].status = "In Progress";
        }
      }
    }
    time[i]['homework'] = 1;
  }
  let table_name = "self_paced_one_times"
  if (input.method_type == 'subscription') {
    table_name = "self_paced_subscriptions"
  } else if (input.method_type == 'Free') {
    table_name = "self_paceds";
  } else {
    table_name = "self_paced_one_times"
  }
  const event_data = await db_helper.query("select * from " + table_name + " where cohort_id=" + input.cohort_id);
  //select userslist,id from cohorts where id in(select cohort_id  from " + table_name + " where find_in_set(" + input.id + ",live_events) and status=0)
  if (event_data[0].length > 0) {
    if (event_data[0][0].live_events && event_data[0][0].live_events != "") {
      var evts = event_data[0][0].live_events.split(",");
      for (let ev = 0; ev < evts.length; ev++) {
        const getaccduration = await db.sequelize.query("SELECT * FROM access_durations WHERE user_id=" + input.user_id + " and cohort_id=" + input.cohort_id)
          .then(function (data) {
            return Array.from(new Set(data[0]));
          });
        let startaccessdate = new Date(new Date().setDate(new Date().getDate() - 1)).toISOString();
        let endaccessdate = new Date(new Date().setDate(new Date().getDate() + 30)).toISOString();
        if (getaccduration.length > 0) {
          startaccessdate = new Date(new Date(getaccduration[0].createdAt)).toISOString();
          endaccessdate = new Date(new Date(getaccduration[0].createdAt).setDate(new Date(getaccduration[0].createdAt).getDate() + getaccduration[0].access_duration)).toISOString();
        }
        var getEvents = [];
        getEvents = await db.sequelize.query("SELECT l.*,l.id live_event_id,r.recording_url FROM all_live_events as l left join event_recordings r on r.event_id=l.id WHERE l.live_event_id=" + evts[ev] + " and l.status=0 and l.start_date between '" + startaccessdate + "' and '" + endaccessdate + "';")
          .then(function (data) {
            return Array.from(new Set(data[0]));
          });
        if (getEvents.length <= 0) {
          getEvents = await db.sequelize.query("SELECT l.*,l.id live_event_id,r.recording_url FROM live_events as l left join event_recordings r on r.event_id=l.id WHERE l.id=" + evts[ev] + " and l.status=0;")
            .then(function (data) {
              return Array.from(new Set(data[0]));
            });
        }
        for (let i = 0; i < getEvents.length; i++) {

          let meet_url = "";
          let alreadyJoin = 0;
          let attendy = getEvents[i].attendees ? JSON.parse(getEvents[i].attendees) : [];
          if (attendy.length > 0) {
            let user = attendy.filter(x => x.user_id == input.user_id);
            if (user.length > 0) {
              const meet_count = await db.sequelize.query("select count(*) ttljoin from meeting_chats where aws_meeting_id='" + getEvents[i].meeting_id + "' AND user_id=" + input.user_id + " AND  message_type='log'")
                .then(function (data) {
                  return Array.from(new Set(data[0]));
                });
              alreadyJoin = meet_count[0] ? meet_count[0].ttljoin : 0;
              meet_url = {
                "AttendeeId": user[0].AttendeeId,
                "meeting_id": getEvents[i].live_event_id
              }
              // meet_url = "http://64.23.254.43/#/conference/" + btoa(JSON.stringify(mylink));
            }
          }
          let due_date = new Date(getEvents[i].start_date).setHours(getEvents[i].start_time.split(":")[0], getEvents[i].start_time.split(":")[1], 0, 0)
          let overdue_date = new Date(getEvents[i].start_date).setHours(getEvents[i].end_time.split(":")[0], getEvents[i].end_time.split(":")[1], 0, 0)
          let event_minutes = (overdue_date - due_date) / 60000;
          let myevent = {
            examname: getEvents[i].event_name,
            material_type: "Events",
            cohort_id: input.cohort_id, // event_data[0][0].id,
            due_date: getEvents[i].start_date,
            overdue_date: getEvents[i].start_date,
            event_duration: event_minutes,
            event_time: getEvents[i].start_time,
            end_time: getEvents[i].end_time,
            event_url: getEvents[i].meeting_playform == 1 ? getEvents[i].event_link : meet_url,
            status: "",
            material_id: getEvents[i].live_event_id,
            time: event_minutes,
            event_time_zone: getEvents[i].time_zone,
            complete_date: getEvents[i].complete_date,
            is_completed: getEvents[i].is_completed,
            meeting_playform: getEvents[i].meeting_playform,
            id: getEvents[i].live_event_id,
            is_live: getEvents[i].is_live,
            alreadyJoin: alreadyJoin,
            recording_url: getEvents[i].recording_url
          }
          time.push(myevent);
        }

      }
    }
  }
  res.status(200).send(API._200(await time));
};