package main

import (
	"database/sql"
	"errors"
	"fmt"
	"log"
	"net/http"
	"strconv"
	"strings"
	"time"

	"github.com/drone/drone-go/drone"
	"github.com/gin-gonic/gin"
	"github.com/russross/blackfriday/v2"
)

func declareAPIWorksRoutes(router *gin.RouterGroup) {
	router.GET("/works", func(c *gin.Context) {
		var u *User
		if user, ok := c.Get("LoggedUser"); ok {
			u = user.(*User)
		}

		var works []*Work
		var err error
		if u == nil {
			works, err = getWorks(fmt.Sprintf("WHERE shown = TRUE AND NOW() > start_availability AND promo = %d ORDER BY start_availability ASC", currentPromo))
		} else if u.IsAdmin {
			works, err = getWorks("ORDER BY promo DESC, start_availability ASC")
		} else {
			works, err = getWorks(fmt.Sprintf("WHERE shown = TRUE AND promo = %d ORDER BY start_availability ASC", u.Promo))
		}

		if err != nil {
			log.Println("Unable to getWorks:", err)
			c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": "Impossible de récupérer la liste des travaux. Veuillez réessayer dans quelques instants."})
			return
		}

		var response []*Work
		if u == nil || u.IsAdmin {
			response = works
		} else {
			for _, w := range works {
				if w.Group == "" || strings.Contains(u.Groups, ","+w.Group+",") {
					// Remove informations not needed on front page for students
					w.Promo = 0
					w.Group = ""
					w.DescriptionRaw = ""

					response = append(response, w)
				}
			}
		}

		c.JSON(http.StatusOK, response)
	})
	router.GET("/all_works", func(c *gin.Context) {
		var u *User
		if user, ok := c.Get("LoggedUser"); ok {
			u = user.(*User)
		}

		var works []*OneWork
		var err error
		if u == nil {
			works, err = allWorks(fmt.Sprintf("WHERE shown = TRUE AND NOW() > start_availability AND promo = %d ORDER BY start_availability ASC, end_availability ASC", currentPromo))
		} else if u.IsAdmin {
			works, err = allWorks("ORDER BY promo DESC, start_availability ASC")
		} else {
			works, err = allWorks(fmt.Sprintf("WHERE shown = TRUE AND promo = %d ORDER BY start_availability ASC, end_availability ASC", u.Promo))
		}

		if err != nil {
			log.Println("Unable to getWorks:", err)
			c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": "Impossible de récupérer la liste des travaux. Veuillez réessayer dans quelques instants."})
			return
		}

		var response []*OneWork
		if u == nil || u.IsAdmin {
			response = works
		} else {
			for _, w := range works {
				if w.Group == "" || strings.Contains(u.Groups, ","+w.Group+",") {
					// Remove informations not needed on front page for students
					w.Promo = 0
					w.Group = ""

					response = append(response, w)
				}
			}
		}

		c.JSON(http.StatusOK, response)
	})
}

func declareAPIAdminWorksRoutes(router *gin.RouterGroup) {
	router.POST("/works", func(c *gin.Context) {
		var new Work
		if err := c.ShouldBindJSON(&new); err != nil {
			c.AbortWithStatusJSON(http.StatusBadRequest, gin.H{"errmsg": err.Error()})
			return
		}

		if new.Promo == 0 {
			new.Promo = currentPromo
		}

		work, err := NewWork(new.IdCategory, new.Title, new.Promo, new.Group, new.Shown, new.DescriptionRaw, new.Tag, new.SubmissionURL, new.GradationRepo, new.StartAvailability, new.EndAvailability)
		if err != nil {
			log.Println("Unable to NewWork:", err)
			c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": "An error occurs during work creation"})
			return
		}

		c.JSON(http.StatusOK, work)
	})

	worksRoutes := router.Group("/works/:wid")
	worksRoutes.Use(workHandler)

	worksRoutes.PUT("", func(c *gin.Context) {
		current := c.MustGet("work").(*Work)

		var new Work
		if err := c.ShouldBindJSON(&new); err != nil {
			c.AbortWithStatusJSON(http.StatusBadRequest, gin.H{"errmsg": err.Error()})
			return
		}

		new.Id = current.Id

		work, err := new.Update()
		if err != nil {
			log.Println("Unable to Update work:", err)
			c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": "An error occurs during work update."})
			return
		}

		c.JSON(http.StatusOK, work)
	})
	worksRoutes.DELETE("", func(c *gin.Context) {
		w := c.MustGet("work").(*Work)

		_, err := w.Delete()
		if err != nil {
			log.Println("Unable to Delte work:", err)
			c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": "An error occurs during work deletion."})
			return
		}

		c.JSON(http.StatusOK, nil)
	})
	worksRoutes.DELETE("/tests", func(c *gin.Context) {
		w := c.MustGet("work").(*Work)

		err := w.stopTests()
		if err != nil {
			log.Println("Unable to stop tests:", err)
			c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": "An error occurs during test stop."})
			return
		}

		c.JSON(http.StatusOK, true)
	})

	// Grades related to works
	worksRoutes.GET("/grades", func(c *gin.Context) {
		w := c.MustGet("work").(*Work)

		grades, err := w.GetGrades("")
		if err != nil {
			log.Printf("Unable to GetGrades(wid=%d): %s", w.Id, err.Error())
			c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": "An error occurs during grades retrieval."})
			return
		}

		c.JSON(http.StatusOK, grades)
	})
	worksRoutes.PATCH("/grades", func(c *gin.Context) {
		w := c.MustGet("work").(*Work)

		// Fetch existing grades
		grades, err := w.GetGrades("")
		if err != nil {
			log.Printf("Unable to GetGrades(wid=%d): %s", w.Id, err.Error())
			c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": "An error occurs during grades retrieval."})
			return
		}

		// Create an index
		known_users := map[int64]bool{}
		for _, g := range grades {
			known_users[g.IdUser] = true
		}

		// Fetch students list registered for this course
		users, err := getFilteredUsers(w.Promo, w.Group)
		if err != nil {
			log.Printf("Unable to getFilteredUsers(%d, %s): %s", w.Promo, w.Group, err.Error())
			c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": "An error occurs during users retrieval."})
			return
		}

		var toAdd []WorkGrade
		for _, user := range users {
			if _, ok := known_users[user.Id]; !ok {
				toAdd = append(toAdd, WorkGrade{
					IdUser:  user.Id,
					Login:   user.Login,
					Grade:   0,
					Comment: "- Non rendu -",
				})
			}
		}

		if len(toAdd) > 0 {
			w.AddGrades(toAdd)
		}

		c.JSON(http.StatusOK, toAdd)
	})
	worksRoutes.PUT("/grades", func(c *gin.Context) {
		w := c.MustGet("work").(*Work)

		var grades []WorkGrade
		if err := c.ShouldBindJSON(&grades); err != nil {
			c.AbortWithStatusJSON(http.StatusBadRequest, gin.H{"errmsg": err.Error()})
			return
		}

		_, err := w.DeleteGrades()
		if err != nil {
			log.Printf("Unable to DeleteGrades(wid=%d): %s", w.Id, err.Error())
			c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": "An error occurs during grades deletion."})
			return
		}

		err = w.AddGrades(grades)
		if err != nil {
			log.Printf("Unable to AddGrades(wid=%d): %s", w.Id, err.Error())
			c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": "An error occurs during grades erasing."})
			return
		}

		c.JSON(http.StatusOK, true)
	})

	gradesRoutes := worksRoutes.Group("/grades/:gid")
	gradesRoutes.Use(gradeHandler)
	gradesRoutes.PUT("", func(c *gin.Context) {
		current := c.MustGet("grade").(*WorkGrade)

		var new WorkGrade
		if err := c.ShouldBindJSON(&new); err != nil {
			c.AbortWithStatusJSON(http.StatusBadRequest, gin.H{"errmsg": err.Error()})
			return
		}

		new.Id = current.Id

		grade, err := new.Update()
		if err != nil {
			log.Println("Unable to Update grade:", err)
			c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": "An error occurs during grade update."})
			return
		}

		c.JSON(http.StatusOK, grade)
	})
	gradesRoutes.DELETE("", func(c *gin.Context) {
		g := c.MustGet("grade").(*WorkGrade)

		g.Delete()
		c.JSON(http.StatusOK, true)
	})
	gradesRoutes.GET("/status", func(c *gin.Context) {
		g := c.MustGet("grade").(*WorkGrade)

		var u *User
		if user, ok := c.Get("user"); ok {
			u = user.(*User)
		} else {
			u = c.MustGet("LoggedUser").(*User)
		}

		repo, err := u.getRepositoryByWork(g.IdWork)
		if err != nil {
			log.Printf("Unable to getRepositoryByWork(uid=%d, wid=%d): %s", u.Id, g.IdWork, err.Error())
			c.AbortWithStatusJSON(http.StatusNotFound, gin.H{"errmsg": "Unable to find a corresponding repository."})
			return
		}

		slug := strings.Split(repo.TestsRef, "/")
		if len(slug) < 3 {
			return
		}

		buildn, err := strconv.ParseInt(slug[2], 10, 32)
		if err != nil {
			return
		}

		client := drone.NewClient(droneEndpoint, droneConfig)
		build, err := client.Build(slug[0], slug[1], int(buildn))
		if err != nil {
			log.Println("Unable to communicate with Drone:", err.Error())
			c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": "Unable to communicate with Drone"})
			return
		}

		c.JSON(http.StatusOK, build)
	})
	gradesRoutes.GET("/traces/*path", func(c *gin.Context) {
		g := c.MustGet("grade").(*WorkGrade)

		var u *User
		if user, ok := c.Get("user"); ok {
			u = user.(*User)
		} else {
			u = c.MustGet("LoggedUser").(*User)
		}

		repo, err := u.getRepositoryByWork(g.IdWork)
		if err != nil {
			log.Printf("Unable to getRepositoryByWork(uid=%d, wid=%d): %s", u.Id, g.IdWork, err.Error())
			c.AbortWithStatusJSON(http.StatusNotFound, gin.H{"errmsg": "Unable to find a corresponding repository."})
			return
		}

		c.Redirect(http.StatusFound, fmt.Sprintf("%s/%s", droneEndpoint, repo.TestsRef)+c.Param("path"))
	})
	gradesRoutes.POST("/traces", func(c *gin.Context) {
		w := c.MustGet("work").(*Work)
		g := c.MustGet("grade").(*WorkGrade)

		var u *User
		if user, ok := c.Get("user"); ok {
			u = user.(*User)
		} else {
			u = c.MustGet("LoggedUser").(*User)
		}

		repo, err := u.getRepositoryByWork(g.IdWork)
		if err != nil {
			log.Printf("Unable to getRepositoryByWork(uid=%d, wid=%d): %s", u.Id, g.IdWork, err.Error())
			c.AbortWithStatusJSON(http.StatusNotFound, gin.H{"errmsg": "Unable to find a corresponding repository."})
			return
		}

		TriggerTests(c, w, repo, u)
	})
	gradesRoutes.GET("/forge/*path", func(c *gin.Context) {
		g := c.MustGet("grade").(*WorkGrade)

		var u *User
		if user, ok := c.Get("user"); ok {
			u = user.(*User)
		} else {
			u = c.MustGet("LoggedUser").(*User)
		}

		repo, err := u.getRepositoryByWork(g.IdWork)
		if err != nil {
			log.Printf("Unable to getRepositoryByWork(uid=%d, wid=%d): %s", u.Id, g.IdWork, err.Error())
			c.AbortWithStatusJSON(http.StatusNotFound, gin.H{"errmsg": "Unable to find a corresponding repository."})
			return
		}

		c.Redirect(http.StatusFound, strings.Replace(strings.Replace(repo.URI, ":", "/", 1), "git@", "https://", 1)+c.Param("path"))
	})
}

type UserTraceItem struct {
	Title   string `json:"title"`
	Status  string `json:"status"`
	Message string `json:"msg,omitempty"`
}

type UserTrace struct {
	Title  string          `json:"title"`
	Status string          `json:"status"`
	Logs   []*drone.Line   `json:"logs,omitempty"`
	Items  []UserTraceItem `json:"items,omitempty"`
}

func declareAPIAuthWorksRoutes(router *gin.RouterGroup) {
	worksRoutes := router.Group("/works/:wid")
	worksRoutes.Use(workHandler)
	worksRoutes.Use(workUserAccessHandler)

	worksRoutes.GET("", func(c *gin.Context) {
		c.JSON(http.StatusOK, c.MustGet("work").(*Work))
	})

	// Grades related to works
	worksRoutes.GET("/forge/*path", func(c *gin.Context) {
		w := c.MustGet("work").(*Work)

		var u *User
		if user, ok := c.Get("user"); ok {
			u = user.(*User)
		} else {
			u = c.MustGet("LoggedUser").(*User)
		}

		repo, err := u.getRepositoryByWork(w.Id)
		if err != nil {
			log.Printf("Unable to getRepositoryByWork(uid=%d, wid=%d): %s", u.Id, w.Id, err.Error())
			c.AbortWithStatusJSON(http.StatusNotFound, gin.H{"errmsg": "Unable to find a corresponding repository."})
			return
		}

		c.Redirect(http.StatusFound, strings.TrimSuffix(strings.Replace(strings.Replace(repo.URI, ":", "/", 1), "git@", "https://", 1), ".git")+c.Param("path"))
	})

	worksRoutes.GET("/score", func(c *gin.Context) {
		u := c.MustGet("LoggedUser").(*User)
		w := c.MustGet("work").(*Work)

		if !u.IsAdmin && !w.Corrected {
			c.AbortWithStatusJSON(http.StatusForbidden, gin.H{"errmsg": "Permission denied"})
		} else if g, err := u.GetMyWorkGrade(w); err != nil && errors.Is(err, sql.ErrNoRows) {
			c.AbortWithStatusJSON(http.StatusNotFound, gin.H{"errmsg": "Aucune note n'a été attribuée pour ce travail. Avez-vous rendu ce travail ?"})
		} else if err != nil {
			log.Printf("Unable to GetMyWorkGrade(uid=%d;wid=%d): %s", u.Id, w.Id, err.Error())
			c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": "An error occurs during grade calculation."})
		} else {
			c.JSON(http.StatusOK, g)
		}
	})

	worksRoutes.GET("/traces", func(c *gin.Context) {
		u := c.MustGet("LoggedUser").(*User)
		w := c.MustGet("work").(*Work)

		if !u.IsAdmin && !w.Corrected {
			c.AbortWithStatusJSON(http.StatusForbidden, gin.H{"errmsg": "Permission denied"})
		} else if g, err := u.GetMyWorkGrade(w); err != nil && errors.Is(err, sql.ErrNoRows) {
			c.AbortWithStatusJSON(http.StatusNotFound, gin.H{"errmsg": "Aucune note n'a été attribuée pour ce travail. Avez-vous rendu ce travail ?"})
		} else if err != nil {
			log.Printf("Unable to GetMyWorkGrade(uid=%d;wid=%d): %s", u.Id, w.Id, err.Error())
			c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": "An error occurs during gradation."})
		} else {
			repo, err := u.getRepositoryByWork(g.IdWork)
			if err != nil {
				log.Printf("Unable to getRepositoryByWork(uid=%d, wid=%d): %s", u.Id, g.IdWork, err.Error())
				c.AbortWithStatusJSON(http.StatusNotFound, gin.H{"errmsg": "Unable to find a corresponding repository."})
				return
			}

			slug := strings.Split(repo.TestsRef, "/")
			if len(slug) < 3 {
				return
			}

			buildn, err := strconv.ParseInt(slug[2], 10, 32)
			if err != nil {
				return
			}

			client := drone.NewClient(droneEndpoint, droneConfig)
			build, err := client.Build(slug[0], slug[1], int(buildn))
			if err != nil {
				log.Println("Unable to communicate with Drone:", err.Error())
				c.AbortWithStatusJSON(http.StatusInternalServerError, gin.H{"errmsg": "Unable to communicate with the gradation service"})
				return
			}

			var traces []UserTrace

			for _, stage := range build.Stages {
				for _, step := range stage.Steps {
					if step.Name == "TP checks" || step.Name == "Clean archive" || strings.HasPrefix(step.Name, "Test ") || strings.HasPrefix(step.Name, "Has ") || strings.HasPrefix(step.Name, "Build ") {
						result, err := client.Logs(slug[0], slug[1], int(buildn), stage.Number, step.Number)
						if err != nil {
							log.Printf("An error occurs when retrieving logs from Drone (%s/%s/%d/%d/%d): %s", slug[0], slug[1], buildn, stage.Number, step.Number, err.Error())
							continue
						}

						keeptLogs := []*drone.Line{}
						firstCmdShown := false
						for _, line := range result {
							// Infos about image, skip
							if !firstCmdShown {
								if strings.HasPrefix(line.Message, "+ ") {
									firstCmdShown = true
								} else {
									continue
								}
							}
							if strings.HasPrefix(line.Message, "+ export GRADE") {
								continue
							}
							if strings.HasPrefix(line.Message, "+ echo grade:") {
								line.Message = "+ Your grade for this step is:\r\n"
							}

							keeptLogs = append(keeptLogs, line)
						}

						traces = append(traces, UserTrace{
							Title:  step.Name,
							Status: step.Status,
							Logs:   keeptLogs,
						})
					} else if strings.HasPrefix(step.Name, "Check") {
						result, err := client.Logs(slug[0], slug[1], int(buildn), stage.Number, step.Number)
						if err != nil {
							log.Printf("An error occurs when retrieving logs from Drone (%s/%s/%d/%d/%d): %s", slug[0], slug[1], buildn, stage.Number, step.Number, err.Error())
							continue
						}

						items := []UserTraceItem{}
						for _, line := range result {
							if strings.HasPrefix(line.Message, "report:") {
								tmp := strings.SplitN(strings.TrimSpace(line.Message), ":", 4)
								if len(tmp) < 3 {
									continue
								}

								uti := UserTraceItem{
									Title:  tmp[1],
									Status: tmp[2],
								}
								if len(tmp) >= 4 {
									uti.Message = tmp[3]
								}
								items = append(items, uti)
							}
						}

						traces = append(traces, UserTrace{
							Title:  step.Name,
							Status: step.Status,
							Items:  items,
						})
					}
				}
			}

			if traces == nil {
				c.JSON(http.StatusOK, []interface{}{})
			} else {
				c.JSON(http.StatusOK, traces)
			}
		}
	})

	declareAPIAuthRepositoriesRoutes(worksRoutes)
	declareAPIWorkSubmissionsRoutes(worksRoutes)
}

func workHandler(c *gin.Context) {
	if wid, err := strconv.Atoi(string(c.Param("wid"))); err != nil {
		c.AbortWithStatusJSON(http.StatusBadRequest, gin.H{"errmsg": "Bad work identifier."})
		return
	} else if work, err := getWork(int64(wid)); err != nil {
		c.AbortWithStatusJSON(http.StatusNotFound, gin.H{"errmsg": "Work not found."})
		return
	} else {
		c.Set("work", work)
		c.Next()
	}
}

func (w *Work) checkUserAccessToWork(u *User) bool {
	return u.IsAdmin || (u.Promo == w.Promo && w.Shown && (w.Group == "" || strings.Contains(u.Groups, ","+w.Group+",")))
}

func workUserAccessHandler(c *gin.Context) {
	u := c.MustGet("LoggedUser").(*User)
	w := c.MustGet("work").(*Work)

	if !w.checkUserAccessToWork(u) {
		c.AbortWithStatusJSON(http.StatusNotFound, gin.H{"errmsg": "Work not found."})
		return
	}
	if !u.IsAdmin && w.StartAvailability.After(time.Now()) {
		c.AbortWithStatusJSON(http.StatusForbidden, gin.H{"errmsg": "Not accessible yet"})
		return
	}

	c.Next()
}

type OneWork struct {
	Kind              string    `json:"kind"`
	Id                int64     `json:"id"`
	IdCategory        int64     `json:"id_category"`
	Title             string    `json:"title"`
	Promo             uint      `json:"promo"`
	Group             string    `json:"group"`
	Shown             bool      `json:"shown"`
	Direct            *int64    `json:"direct"`
	SubmissionURL     *string   `json:"submission_url"`
	Corrected         bool      `json:"corrected"`
	StartAvailability time.Time `json:"start_availability"`
	EndAvailability   time.Time `json:"end_availability"`
}

func allWorks(cnd string, param ...interface{}) (items []*OneWork, err error) {
	if rows, errr := DBQuery("SELECT kind, id, id_category, title, promo, grp, shown, direct, submission_url, corrected, start_availability, end_availability FROM all_works "+cnd, param...); errr != nil {
		return nil, errr
	} else {
		defer rows.Close()

		for rows.Next() {
			var w OneWork
			if err = rows.Scan(&w.Kind, &w.Id, &w.IdCategory, &w.Title, &w.Promo, &w.Group, &w.Shown, &w.Direct, &w.SubmissionURL, &w.Corrected, &w.StartAvailability, &w.EndAvailability); err != nil {
				return
			}
			items = append(items, &w)
		}
		if err = rows.Err(); err != nil {
			return
		}

		return
	}
}

type Work struct {
	Id                int64     `json:"id"`
	IdCategory        int64     `json:"id_category"`
	Title             string    `json:"title"`
	Promo             uint      `json:"promo,omitempty"`
	Group             string    `json:"group,omitempty"`
	Shown             bool      `json:"shown"`
	Description       string    `json:"description,omitempty"`
	DescriptionRaw    string    `json:"descr_raw,omitempty"`
	Tag               string    `json:"tag,omitempty"`
	SubmissionURL     *string   `json:"submission_url,omitempty"`
	GradationRepo     *string   `json:"gradation_repo"`
	Corrected         bool      `json:"corrected,omitempty"`
	StartAvailability time.Time `json:"start_availability"`
	EndAvailability   time.Time `json:"end_availability"`
}

func getWorks(cnd string, param ...interface{}) (items []*Work, err error) {
	if rows, errr := DBQuery("SELECT id_work, id_category, title, promo, grp, shown, description, tag, submission_url, gradation_repo, corrected, start_availability, end_availability FROM works "+cnd, param...); errr != nil {
		return nil, errr
	} else {
		defer rows.Close()

		for rows.Next() {
			var w Work
			if err = rows.Scan(&w.Id, &w.IdCategory, &w.Title, &w.Promo, &w.Group, &w.Shown, &w.DescriptionRaw, &w.Tag, &w.SubmissionURL, &w.GradationRepo, &w.Corrected, &w.StartAvailability, &w.EndAvailability); err != nil {
				return
			}
			items = append(items, &w)
		}
		if err = rows.Err(); err != nil {
			return
		}

		return
	}
}

func getWork(id int64) (w *Work, err error) {
	w = new(Work)
	err = DBQueryRow("SELECT id_work, id_category, title, promo, grp, shown, description, tag, submission_url, gradation_repo, corrected, start_availability, end_availability FROM works WHERE id_work=?", id).Scan(&w.Id, &w.IdCategory, &w.Title, &w.Promo, &w.Group, &w.Shown, &w.DescriptionRaw, &w.Tag, &w.SubmissionURL, &w.GradationRepo, &w.Corrected, &w.StartAvailability, &w.EndAvailability)
	w.Description = string(blackfriday.Run([]byte(w.DescriptionRaw)))
	return
}

func NewWork(id_category int64, title string, promo uint, group string, shown bool, description string, tag string, submissionurl *string, gradation_repo *string, startAvailability time.Time, endAvailability time.Time) (*Work, error) {
	if res, err := DBExec("INSERT INTO works (id_category, title, promo, grp, shown, description, tag, submission_url, gradation_repo, start_availability, end_availability) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", id_category, title, promo, group, shown, description, tag, submissionurl, gradation_repo, startAvailability, endAvailability); err != nil {
		return nil, err
	} else if wid, err := res.LastInsertId(); err != nil {
		return nil, err
	} else {
		return &Work{wid, id_category, title, promo, group, shown, description, description, tag, submissionurl, gradation_repo, false, startAvailability, endAvailability}, nil
	}
}

func (w *Work) Update() (*Work, error) {
	if _, err := DBExec("UPDATE works SET id_category = ?, title = ?, promo = ?, grp = ?, shown = ?, description = ?, tag = ?, submission_url = ?, gradation_repo = ?, corrected = ?, start_availability = ?, end_availability = ? WHERE id_work = ?", w.IdCategory, w.Title, w.Promo, w.Group, w.Shown, w.DescriptionRaw, w.Tag, w.SubmissionURL, w.GradationRepo, w.Corrected, w.StartAvailability, w.EndAvailability, w.Id); err != nil {
		return nil, err
	} else {
		w.Description = string(blackfriday.Run([]byte(w.DescriptionRaw)))
		return w, err
	}
}

func (w *Work) Delete() (int64, error) {
	if res, err := DBExec("DELETE FROM works WHERE id_work = ?", w.Id); err != nil {
		return 0, err
	} else if nb, err := res.RowsAffected(); err != nil {
		return 0, err
	} else {
		return nb, err
	}
}

func ClearWorks() (int64, error) {
	if res, err := DBExec("DELETE FROM works"); err != nil {
		return 0, err
	} else if nb, err := res.RowsAffected(); err != nil {
		return 0, err
	} else {
		return nb, err
	}
}

type WorkGrade struct {
	Id      int64     `json:"id"`
	Login   string    `json:"login,omitempty"`
	IdUser  int64     `json:"id_user,omitempty"`
	IdWork  int64     `json:"id_work,omitempty"`
	Date    time.Time `json:"date"`
	Grade   float64   `json:"score"`
	Comment string    `json:"comment,omitempty"`
}

func (g *WorkGrade) Update() (*WorkGrade, error) {
	if _, err := DBExec("UPDATE user_work_grades SET id_user = ?, id_work = ?, date = ?, grade = ?, comment = ? WHERE id_gradation = ?", g.IdUser, g.IdWork, g.Date, g.Grade, g.Comment, g.Id); err != nil {
		return nil, err
	} else {
		return g, err
	}
}

func (g WorkGrade) Delete() (int64, error) {
	if res, err := DBExec("DELETE FROM user_work_grades WHERE id_gradation = ?", g.Id); err != nil {
		return 0, err
	} else if nb, err := res.RowsAffected(); err != nil {
		return 0, err
	} else {
		return nb, err
	}
}

func (w *Work) GetGrades(cnd string, param ...interface{}) (grades []WorkGrade, err error) {
	param = append([]interface{}{w.Id}, param...)

	if rows, errr := DBQuery("SELECT G.id_gradation, G.id_user, U.login, G.id_work, G.date, G.grade, G.comment FROM user_work_grades G INNER JOIN users U ON U.id_user = G.id_user WHERE id_work = ? "+cnd, param...); errr != nil {
		return nil, errr
	} else {
		defer rows.Close()

		for rows.Next() {
			var g WorkGrade
			if err = rows.Scan(&g.Id, &g.IdUser, &g.Login, &g.IdWork, &g.Date, &g.Grade, &g.Comment); err != nil {
				return
			}
			grades = append(grades, g)
		}
		if err = rows.Err(); err != nil {
			return
		}

		return
	}
}

func (w *Work) GetGrade(id int64) (g *WorkGrade, err error) {
	g = new(WorkGrade)
	err = DBQueryRow("SELECT G.id_gradation, G.id_user, U.login, G.id_work, G.date, G.grade, G.comment FROM user_work_grades G INNER JOIN users U ON U.id_user = G.id_user WHERE id_work = ? AND id_gradation = ?", w.Id, id).Scan(&g.Id, &g.IdUser, &g.Login, &g.IdWork, &g.Date, &g.Grade, &g.Comment)
	return
}

func (u *User) GetMyWorkGrade(w *Work) (g WorkGrade, err error) {
	err = DBQueryRow("SELECT id_gradation, id_user, id_work, date, grade, comment FROM user_work_grades WHERE id_work = ? AND id_user = ? ORDER BY date DESC LIMIT 1", w.Id, u.Id).Scan(&g.Id, &g.IdUser, &g.IdWork, &g.Date, &g.Grade, &g.Comment)
	return
}

func (w *Work) AddGrade(grade WorkGrade) error {
	u := User{Id: grade.IdUser}

	// Search a previous record
	g, err := u.GetMyWorkGrade(w)
	if err != nil && err != sql.ErrNoRows {
		return err
	} else if err == nil {
		_, err = g.Delete()
		if err != nil {
			return err
		}
	}

	return w.AddGrades([]WorkGrade{grade})
}

func (w *Work) AddGrades(grades []WorkGrade) error {
	var zerotime time.Time
	for i, g := range grades {
		if g.IdUser == 0 {
			if u, err := getUserByLogin(g.Login); err != nil {
				return fmt.Errorf("user %q: %w", g.Login, err)
			} else {
				grades[i].IdUser = u.Id
			}
		}
		if zerotime == g.Date {
			grades[i].Date = time.Now()
		}
	}

	for _, g := range grades {
		if _, err := DBExec("INSERT INTO user_work_grades (id_user, id_work, date, grade, comment) VALUES (?, ?, ?, ?, ?)", g.IdUser, w.Id, g.Date, g.Grade, g.Comment); err != nil {
			return err
		}
	}

	return nil
}

func (w *Work) DeleteGrades() (int64, error) {
	if res, err := DBExec("DELETE FROM user_work_grades WHERE id_work = ?", w.Id); err != nil {
		return 0, err
	} else if nb, err := res.RowsAffected(); err != nil {
		return 0, err
	} else {
		return nb, err
	}
}