/// <summary>
        /// Creates a new assignment for the given class and category.
        /// </summary>
        /// <param name="subject">The course subject abbreviation</param>
        /// <param name="num">The course number</param>
        /// <param name="season">The season part of the semester for the class the assignment belongs to</param>
        /// <param name="year">The year part of the semester for the class the assignment belongs to</param>
        /// <param name="category">The name of the assignment category in the class</param>
        /// <param name="asgname">The new assignment name</param>
        /// <param name="asgpoints">The max point value for the new assignment</param>
        /// <param name="asgdue">The due DateTime for the new assignment</param>
        /// <param name="asgcontents">The contents of the new assignment</param>
        /// <returns>A JSON object containing success = true/false</returns>
        public IActionResult CreateAssignment(string subject, int num, string season, int year, string category, string asgname, int asgpoints, DateTime asgdue, string asgcontents)  // works
        {
            //subject, num -- catalogId
            //catalogID, semester -- > classID
            //class ID  , Name - ACID
            // asgname, asgcontenst, asgpoint, asgdue, acid  --- add new assignment
            using (Team9LMSContext db = new Team9LMSContext())
            {
                var acIDQuery = from ci in (from cid in (from c in db.Courses
                                                         where c.Subject == subject && c.Num == num
                                                         select new { CatalogId = c.CatalogId })
                                            join cl in db.Classes
                                            on cid.CatalogId equals cl.CatalogId
                                            where cl.Semester == year.ToString() + season
                                            select new { classID = cl.ClassId })
                                join ac in db.AssignmentCategories
                                on ci.classID equals ac.ClassId
                                where ac.Name == category
                                select new { acID = ac.AcId, ci.classID };
                uint acID = (uint)Convert.ToInt32(acIDQuery.First().acID);



                //check to void duiplicate (name , acID )
                var duplicateCheckQuery = from asg in db.Assignments
                                          where asg.Name == asgname && asg.AcId == acID
                                          select asg;
                if (duplicateCheckQuery.Count() > 0)
                {
                    return(Json(new { success = false }));
                }

                if (asgpoints < 0)
                {
                    return(Json(new { success = false }));
                }

                //add new entry to db
                Assignments newAS = new Assignments();
                newAS.AcId     = acID;
                newAS.Name     = asgname;
                newAS.Contents = asgcontents;
                newAS.Points   = (uint)asgpoints;
                newAS.Due      = asgdue;
                db.Assignments.Add(newAS);
                try
                {
                    db.SaveChanges();
                }
                catch (Exception e)
                {
                    return(Json(new { success = false }));
                }
                //updata all students's grades.
                uint classID = (uint)Convert.ToInt32(acIDQuery.First().classID);
                GPA.ClassGradeUpdate(classID);
            }
            return(Json(new { success = true }));
        }
        /// <summary>
        /// Set the score of an assignment submission
        /// </summary>
        /// <param name="subject">The course subject abbreviation</param>
        /// <param name="num">The course number</param>
        /// <param name="season">The season part of the semester for the class the assignment belongs to</param>
        /// <param name="year">The year part of the semester for the class the assignment belongs to</param>
        /// <param name="category">The name of the assignment category in the class</param>
        /// <param name="asgname">The name of the assignment</param>
        /// <param name="uid">The uid of the student who's submission is being graded</param>
        /// <param name="score">The new score for the submission</param>
        /// <returns>A JSON object containing success = true/false</returns>
        public IActionResult GradeSubmission(string subject, int num, string season, int year, string category, string asgname, string uid, int score)
        //works
        {
            uint classID = 0;

            using (Team9LMSContext db = new Team9LMSContext())
            {
                var subQuery = from aid in (from acID in (from ci in (from cid in (from c in db.Courses
                                                                                   where c.Subject == subject && c.Num == num
                                                                                   select new { CatalogId = c.CatalogId })
                                                                      join cl in db.Classes
                                                                      on cid.CatalogId equals cl.CatalogId
                                                                      where cl.Semester == year.ToString() + season
                                                                      select new { classID = cl.ClassId })
                                                          join ac in db.AssignmentCategories
                                                          on ci.classID equals ac.ClassId
                                                          where ac.Name == category
                                                          select new { acID = ac.AcId })
                                            join asg in db.Assignments
                                            on acID.acID equals asg.AcId
                                            where asg.Name == asgname
                                            select new { asg.AId, asg.Points })
                               join s in db.Submission
                               on aid.AId equals s.AId
                               where s.UId == uid
                               select new { s, aid.Points };

                uint points = (uint)subQuery.First().Points;
                if (score < 0 || score > points)
                {
                    return(Json(new { success = false }));
                }

                subQuery.First().s.Score = (uint)score;
                db.Update(subQuery.First().s);
                db.SaveChanges();

                //update student's letter grade.
                var classIDQuery = from cid in (from c in db.Courses
                                                where c.Subject == subject && c.Num == num
                                                select new { CatalogId = c.CatalogId })
                                   join cl in db.Classes
                                   on cid.CatalogId equals cl.CatalogId
                                   where cl.Semester == year.ToString() + season
                                   select new { classID = cl.ClassId };
                classID = (uint)Convert.ToInt32(classIDQuery.First().classID);
                GPA.GradeUpdate(uid, classID);
            }

            return(Json(new { success = true }));
        }
        /// <summary>
        /// Calculates a student's GPA
        /// A student's GPA is determined by the grade-point representation of the average grade in all their classes.
        /// Assume all classes are 4 credit hours.
        /// If a student does not have a grade in a class ("--"), that class is not counted in the average.
        /// If a student is not enrolled in any classes, they have a GPA of 0.0.
        /// Otherwise, the point-value of a letter grade is determined by the table on this page:
        /// https://advising.utah.edu/academic-standards/gpa-calculator-new.php
        /// </summary>
        /// <param name="uid">The uid of the student</param>
        /// <returns>A JSON object containing a single field called "gpa" with the number value</returns>
        public IActionResult GetGPA(string uid)
        {
            var finalGPA = 0.0;

            using (Team9LMSContext db = new Team9LMSContext())
            {
                var gradeQuery = from e in db.Enrolled
                                 where e.UId == uid
                                 select new { e.Grade };
                List <string> grades = new List <string>();
                foreach (var g in gradeQuery)
                {
                    if (g.Grade != "--")
                    {
                        grades.Add(g.Grade);
                    }
                }

                finalGPA = GPA.GPACalculation(grades);
            }

            return(Json(new { gpa = finalGPA }));
        }