const { globalLink } = require('../app');
const db = require('../db');

exports.createGroupPage = async (req, res, next) => {
  res.status(200).render('./createPages/group', {
    globalLink,
  });
};

exports.createGroup = async (req, res, next) => {
  const groupData = req.body;

  db.oneOrNone(`INSERT INTO groups(name, class_number)
        VALUES('${groupData.name}', ${Number(groupData.class_number)})` + 'RETURNING group_id')
    .then(({ group_id }) => res.status(200).redirect(`${globalLink}/groups/${group_id}`));
};



exports.getGroup = async (req, res, next) => {

  return db.task(t => {
    const groupId = req.params.id;
    let group = {

    };

    return t.oneOrNone(`SELECT * from groups where group_id = ${groupId}`)
    .then((groupData) => {
      group.group_info = groupData;

      return t.manyOrNone(`select * from group_subjects a
      join subjects b
      on a.subject_id = b.id
      where a.group_id = ${groupId}`)
    })
    .then((group_subjects) => {
      group.subjects = group_subjects;

      return t.manyOrNone(`select a.group_id, d.name as subject_name,
      a.student_id, b.name as student_name
      from group_students a
      join students b
      on a.student_id = b.student_id
      join student_subjects c
      on b.student_id = c.student_id
      join subjects d
      on c.subject_id = d.id
      where a.group_id = ${groupId} and active = true`)
    }).then((group_students) => {
      group.students = group_students;

      group.group_students_by_subjects = group.subjects.map((subject) => {

       subject.subject_students = group.students.filter((student) => student.subject_name == subject.name);
       return subject;
      });

      return t.manyOrNone(`SELECT a.review_id,  a.posting_date, c.name,
      count(b.attendance) AS marked_attendance,
      count(*) filter (where b.attendance) as attendance,
      count(b.activity) AS marked_activity,
      count(*) filter (where b.activity) as activity,
      count(b.homework) AS marked_homework,
      count(*) filter (where b.homework) as homework
      from group_reviews a
      JOIN student_records b
      ON a.review_id = b.review_id
      JOIN subjects c ON a.subject_id = c.id
      WHERE a.group_id = ${groupId}
      GROUP BY a.review_id, c.name`)
    })
    .then((group_reviews) => {
      group.reviews = group_reviews;

      return t.manyOrNone(`select distinct format, group_id from group_custom_tests
      where group_id = ${groupId}`);
    })
    .then((testFormatsData) => {

      return t.tx((tt) => {

        const queries = testFormatsData.map((format) => {
          return tt.manyOrNone(`select * from group_custom_tests
          where format = '${format.format}' and group_id = ${groupId}`)
          .then((formatTestsData) => {
            format.tests = formatTestsData;
            return format;
          })

        });
        return tt.batch(queries);


      })
    })
    .then((data) => console.log(data))

    // .then((tests) => {

    //   return t.tx((tt => {

    //     const queries = tests.map((test) => {
    //       return tt.manyOrNone(`select  b.student_id,
    //       d.name as student_name,
    //       b.subject_id, c.name as subject_name,
    //       b.max_points, b.points, b.test_date
    //       from group_custom_tests a
    //       join custom_tests_results b
    //       on a.id = b.custom_test_id
    //       join subjects c
    //       on b.subject_id = c.id
    //       join students d
    //       on b.student_id = d.student_id
    //       where a.id = ${test.id}`)
    //       .then((resultsData) => {
            
    //         test.results = resultsData;
    //         return test;
    //       })
    //     });
    //     return t.batch(queries);
    //   }));
    // }).then((data) => console.log(data))
    .then(() => res.status(200).render('./pages/groupPage', {
      // tests: complexTests,
      group_students_by_subjects: group.group_students_by_subjects,
      group: group.group_info,
      students: group.students,
      subjects: group.subjects,
      reviews: group.reviews,
      globalLink,
    }));
  })
  .catch((error) => {
    console.log('ERROR:', error);

    res.status(500).json({
      error,
    });
  });




  // db.task(t => {
  //   const groupId = req.params.id;
  //   let group;
  //   let students;
  //   let subjects;
  //   let complexTests;
  //   let reviews;

  //   return t.one(`SELECT * from groups WHERE group_id = '${groupId}'`)
  //   .then((groupData) => group = groupData)
  //   .then(() => t.manyOrNone(`SELECT * from group_students a
  //   JOIN students b ON 
  //   a.student_id = b.student_id
  //   WHERE a.group_id = ${groupId} AND b.active = true`))
  //   .then((groupStudentsData) => students = groupStudentsData)
  //   .then(() => t.manyOrNone(`SELECT * from group_subjects a
  //       JOIN subjects b
  //       ON a.subject_id = b.id 
  //       where a.group_id = ${groupId}`)
  //   .then((groupSubjectsData) => subjects = groupSubjectsData))
  //   .then(() => t.query(`SELECT a.review_id,  a.posting_date, c.name,
  //   count(b.attendance) AS marked_attendance,
  //   count(*) filter (where b.attendance) as attendance,
  //   count(b.activity) AS marked_activity,
  //   count(*) filter (where b.activity) as activity,
  //   count(b.homework) AS marked_homework,
  //   count(*) filter (where b.homework) as homework
  //   from group_reviews a
  //   JOIN student_records b
  //   ON a.review_id = b.review_id
  //   JOIN subjects c ON a.subject_id = c.id
  //   WHERE a.group_id = ${groupId}
  //   GROUP BY a.review_id, c.name`))
  //   .then((reviewsData) => reviews = reviewsData)
  //   .then(() => t.query(`select distinct a.id, a.format,
  //   a.group_id,
	//   avg(b.max_points) as avg_max_points,
	//   avg(b.points) as avg_points,
	//   count(distinct b.student_id)
  //   from group_custom_tests a
  //   join custom_tests_results b
  //   on a.id = b.custom_test_id
  //   where a.group_id = ${groupId}
	//   GROUP BY a.id`))
  //   .then((complexTestsData) => {

  //     complexTests = complexTestsData.map((complexTest) => {
  //       complexTest.percent = Math.round(complexTest.avg_points / complexTest.avg_max_points * 100);
  //       return complexTest;
  //     });
    
  //   })
  //   .then(() => {
  //     t.tx((tt) => {
  //       const queries = complexTests.map((test) => tt.manyOrNone(`select distinct b.name
  //       from custom_tests_results a
  //       join subjects b on a.subject_id = b.id
  //       where a.custom_test_id = ${test.id}`).then((testSubjectsData) => {
  //         test.test_subjects = testSubjectsData;
  //       }));
  //       return tt.batch(queries);
  //     })
  //   })
  //     .then(() => res.status(200).render('./pages/groupPage', {
  //         tests: complexTests,
  //         complexTests,
  //         group,
  //         students,
  //         subjects,
  //         reviews,
  //         globalLink,
  //       }));
  //   })
  //   .catch((error) => {
  //     console.log('ERROR:', error);

  //     res.status(500).json({
  //       error,
  //     });
  //   });




};



exports.addStudentToGroupPage = async (req, res, next) => {

  db.task(t => {

    const groupId = req.params.id;
    let group;

    return t.oneOrNone(`SELECT * from groups WHERE group_id = ${groupId}`)
    .then((groupData) => group = groupData)
    .then(() => t.manyOrNone(`SELECT a.student_id, a.name, a.class_number
     from students a
    FULL JOIN group_students b
    ON a.student_id = b.student_id
          WHERE 
        a.class_number = ${group.class_number} AND b.group_id <> ${groupId}
        OR b.group_id IS NULL AND a.class_number = ${group.class_number}`)
      .then((possibleNewStudents) => res.status(200).render('./updatePages/addStudent', {
        group,
        possibleNewStudents,
        globalLink,
      }))
    )
      .catch((error) => res.status(500).json({
        error
      }))

  });
};





exports.addStudentToGroup = async (req, res, next) => {
  const groupId = req.params.id;
  const { student } = req.body;
  console.log(student);
  db.oneOrNone(`SELECT * from students WHERE student_id = ${student}`)
    .then(({ student_id }) => db.query(`INSERT INTO group_students(student_id, group_id) VALUES(${student_id}, ${groupId})`))
    .then(() => res.redirect(`${globalLink}/groups/${groupId}/`));
};

exports.removeStudentFromGroupPage = async (req, res, next) => {
  const groupId = req.params.id;

  let group;
  let groupStudentsIds;
  db.one(`SELECT * from groups WHERE group_id = ${groupId}`).then((groupData) => {
    group = groupData;
    return db.manyOrNone(`SELECT * from group_students WHERE group_id = ${groupId}`);
  })
    .then((groupStudents) => {
      groupStudentsIds = groupStudents.map((student) => student.student_id);

      groupStudentsIds.reduce((acc, student_id) => {
        const newAcc = acc.then((contents) => db.manyOrNone(`SELECT * from students WHERE student_id = ${student_id}`)
          .then((data) => contents.concat(data)));
        return newAcc;
      }, Promise.resolve([]))
        .then((students) => {
          res.status(200).render('./removePages/removeStudent', {
            group,
            students,
            globalLink,
          });
        });
    });
};

exports.removeStudentFromGroup = async (req, res, next) => {
  const groupId = req.params.id;
  const studentId = req.body.student;

  db.any(`DELETE FROM group_students WHERE group_id = ${groupId} AND student_id = ${studentId}`)
    .then(() => res.redirect(`${globalLink}/groups/${groupId}/`));
};

// subjects

exports.addSubjectToGroupPage = async (req, res, next) => {
  const groupId = req.params.id;
  let group;
  let groupSubjectsIds;
  let allSubjects;
  db.one(`SELECT * FROM groups WHERE group_id = ${groupId}`)
    .then((groupData) => {
      group = groupData;
    }).then(() => db.manyOrNone('SELECT * from subjects'))
    .then((subjects) => {
      allSubjects = subjects;
    })
    .then(() => db.manyOrNone(`SELECT * from group_subjects WHERE group_id = ${groupId}`)
      .then((groupSubjects) => {
        groupSubjectsIds = groupSubjects.map((subject) => subject.subject_id);
      }))
    .then(() => allSubjects.filter((subject) => {
      if (groupSubjectsIds.includes(subject.id)) {
        return;
      }
      return subject;
    }))
    .then((availableSubjects) => res.status(200).render('./updatePages/addSubject', {
      group,
      availableSubjects,
      globalLink,
    }));
};

exports.addSubjectToGroup = async (req, res, next) => {
  const groupId = req.params.id;
  const subjectId = req.body.subject;
  let group;

  db.one(`SELECT * FROM groups WHERE group_id = ${groupId}`)
    .then((groupData) => {
      group = groupData;
    })
    .then(() => db.one(`SELECT * from subjects WHERE id = ${subjectId}`)
      .then((subject) => {
        console.log(subject);
        db.query(`INSERT INTO group_subjects(group_id, subject_id) VALUES (${groupId}, ${subject.id})`);
      }))
    .then(() => res.redirect(`${globalLink}/groups/${groupId}`));
};

exports.removeSubjectFromGroupPage = async (req, res, next) => {
  const groupId = req.params.id;

  let group;
  db.one(`SELECT * from groups WHERE group_id = ${groupId}`).then((groupData) => {
    group = groupData;
    return db.manyOrNone(`SELECT * from group_subjects a
        JOIN subjects b
        ON a.subject_id = b.id
        WHERE group_id = ${groupId}`);
  })
    .then((subjects) => res.status(200).render('./removePages/removeSubject', {
      group,
      subjects,
      globalLink,
    }));
};

exports.removeSubjectFromGroup = async (req, res, next) => {
  const groupId = req.params.id;
  const subjectId = req.body.subject;

  db.any(`DELETE FROM group_subjects WHERE group_id = ${groupId} AND subject_id = ${subjectId}`)
    .then(() => res.redirect(`${globalLink}/groups/${groupId}/`));
};



exports.removeGroupPage = async (req, res, next) => {

  db.task(t => {

    return t.manyOrNone(`SELECT * from groups where active`)
    .then((groups) => res.status(200).render('./removePages/removeGroup', {
      groups,
      globalLink,
    }))
  })


};


exports.removeGroup = async (req, res, next) => {

  db.task(t => {
    const groupId = req.body.group_id;
    return t.query(`UPDATE groups SET active = false WHERE group_id = ${groupId}`)
           .then(() => res.redirect(`${globalLink}/users/profile/`))

  })
  
}