package se.kits.gakusei.content.model;

import com.fasterxml.jackson.annotation.*;
import org.hibernate.annotations.Fetch;
import org.hibernate.annotations.FetchMode;

import javax.persistence.*;
import java.io.Serializable;
import java.util.List;

@Entity
@Table(name = "lessons", schema = "contentschema")
@NamedNativeQueries({
        @NamedNativeQuery(
                name = "Lesson.findNuggetsByTwoFactTypes",
                query = "select * from contentschema.nuggets n " +
                        "where n.hidden = FALSE AND n.id in " +
                        "(select filtered.nugget_id from contentschema.facts " +
                        "inner join (select nugget_id from contentschema.lessons_nuggets where lesson_id = " +
                        "(select distinct id from contentschema.lessons where name = :lessonName)) as filtered " +
                        "on nuggetid = nugget_id " +
                        "where facts.type IN (:factType1, :factType2) " +
                        "GROUP BY filtered.nugget_id HAVING count(filtered.nugget_id) > 1)",
                resultClass = Nugget.class),
        @NamedNativeQuery(
                name = "Lesson.findKanjiLessNuggetsByFactType",
                query = "select * from contentschema.nuggets n " +
                        "where n.type  <> 'kanji' and n.id in " +
                        "(select filtered.nugget_id from contentschema.facts " +
                        "inner join (select nugget_id from contentschema.lessons_nuggets where lesson_id = " +
                        "(select distinct id from contentschema.lessons where name = :lessonName)) as filtered " +
                        "on nuggetid = nugget_id " +
                        "where facts.type IN (:factType1, :factType2) " +
                        "GROUP BY filtered.nugget_id HAVING count(filtered.nugget_id) > 1)",
                resultClass = Nugget.class),
        @NamedNativeQuery(
                name = "Lesson.findKanjiNuggetsByFactType",
                query = "select * from contentschema.nuggets n " +
                        "where n.type  = 'kanji' and n.id in " +
                        "(select filtered.nugget_id from contentschema.facts " +
                        "inner join (select nugget_id from contentschema.lessons_nuggets where lesson_id = " +
                        "(select distinct id from contentschema.lessons where name = :lessonName)) as filtered " +
                        "on nuggetid = nugget_id " +
                        "where facts.type IN (:factType1, :factType2) " +
                        "GROUP BY filtered.nugget_id HAVING count(filtered.nugget_id) > 1)",
                resultClass = Nugget.class),
        @NamedNativeQuery(
                name = "Lesson.findNuggetsBySuccessrate",
                query = "select * from contentschema.nuggets where id in " +
                        "(select nugget_id from progresstrackinglist " +
                        " where user_ref = :username " +
                        "    and incorrect_count > correct_count) " +
                        "    and :lessonName in " +
                        "    (select name from contentschema.lessons inner join contentschema.lessons_nuggets \n" +
                        "       on contentschema.lessons.id=contentschema.lessons_nuggets.lesson_id)",
                resultClass = Nugget.class),
        @NamedNativeQuery(
                name = "Lesson.findUnansweredNuggets",
                query = "select * from contentschema.nuggets where id not in " +
                        "(select nugget_id from progresstrackinglist where user_ref = :username)" +
                        "and id in " +
                        "(select ln.nugget_id from contentschema.lessons l, contentschema.lessons_nuggets ln" +
                        "   where l.id=ln.lesson_id and l.name =  :lessonName)",
                resultClass = Nugget.class),
        @NamedNativeQuery(
                name = "Lesson.findCorrectlyAnsweredNuggets",
                query = "select * from contentschema.nuggets where id in " +
                        "(select nugget_id from progresstrackinglist where user_ref = :username and correct_count > 0)" +
                        "and id in " +
                        "(select nugget_id from contentschema.lessons inner join contentschema.lessons_nuggets on " +
                        "contentschema.lessons.id=contentschema.lessons_nuggets.lesson_id where name = :lessonName)",
                resultClass = Nugget.class),
        @NamedNativeQuery(
                name = "Lesson.findVerbNuggets",
                query = "select * from contentschema.nuggets n where n.hidden = FALSE and n.id in" +
                        "  (select verbs.id from contentschema.nuggets verbs " +
                        "  INNER JOIN contentschema.lessons_nuggets ln on n.id = ln.nugget_id " +
                        "  WHERE n.type LIKE 'verb' AND ln.lesson_id = :lessonId)",
                resultClass = Nugget.class)
})
@JsonIdentityInfo(generator = ObjectIdGenerators.PropertyGenerator.class, property = "id")
public class Lesson implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, unique = true)
    private String name;

    private String description;

    @ManyToMany
    //@JsonManagedReference(value = "lessonnugget")
    @JoinTable(
            name = "lessons_nuggets",
            schema = "contentschema",
            joinColumns = @JoinColumn(name = "lesson_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name = "nugget_id", referencedColumnName = "id"))
    private List<Nugget> nuggets;

    @ManyToMany
    @JoinTable(
            name = "lessons_kanjis",
            schema = "contentschema",
            joinColumns = @JoinColumn(name = "lesson_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name = "kanji_id", referencedColumnName = "id")
    )
    private List<Kanji> kanjis;

    @JsonIgnore
    @OneToMany(mappedBy="lesson", fetch = FetchType.LAZY)
    @Fetch(value = FetchMode.SUBSELECT)
    private List<UserLesson> userLessons;

    @ManyToOne
    @JsonBackReference(value = "courselesson")
    @JoinColumn(name="course_ref")
    private Course course;

    public Lesson() {
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public List<Nugget> getNuggets() {
        return nuggets;
    }

    public void setNuggets(List<Nugget> nuggets) {
        this.nuggets = nuggets;
    }

    public List<UserLesson> getUserLessons() {
        return userLessons;
    }

    public void setUserLessons(List<UserLesson> userLessons) {
        this.userLessons = userLessons;
    }

    public Course getCourse() {
        return course;
    }

    public void setCourse(Course course) {
        this.course = course;
    }

    public List<Kanji> getKanjis() {
        return kanjis;
    }

    public void setKanjis(List<Kanji> kanjis) {
        this.kanjis = kanjis;
    }
}

@@ -6005,16 +6005,108 @@
  = %22id%22)
+,%0A            uniqueConstraints = @UniqueConstraint(columnNames = %7B%22lesson_id%22, %22kanji_id%22%7D)
 %0A    )%0A 

Add uniqueness constraint