
CREATE TABLE "User" (
                        ID                  BIGSERIAL   NOT NULL,
                        Username            text,
                        Password            text,
                        Email               text,
                        Name                text,
                        Surname             text,
                        Biography           text,
                        FacultyID           BIGSERIAL   NOT NULL,
                        PRIMARY KEY (ID)
);

CREATE TABLE Faculty (
                         ID                  BIGSERIAL   NOT NULL,
                         Name                text,
                         Description         text,
                         DateFounded         timestamp,
                         Address             text,
                         Contact             text,
                         Dean                text,
                         UniversityID        BIGSERIAL   NOT NULL,
                         PRIMARY KEY (ID)
);

CREATE TABLE University (
                            ID                  BIGSERIAL   NOT NULL,
                            Name                text,
                            Description         text,
                            DateFounded         timestamp,
                            Address             text,
                            Contact             text,
                            Rector              text,
                            PRIMARY KEY (ID)
);

CREATE TABLE Permission (
                            ID                  BIGSERIAL   NOT NULL,
                            PermissionName      text,
                            PRIMARY KEY (ID)
);

CREATE TABLE Role (
                      ID                  BIGSERIAL   NOT NULL,
                      RoleName            text,
                      PRIMARY KEY (ID)
);

CREATE TABLE User_Role (
                           ID                  BIGSERIAL   NOT NULL,
                           UserID              BIGSERIAL   NOT NULL,
                           RoleID              BIGSERIAL   NOT NULL,
                           PRIMARY KEY (ID, UserID, RoleID)
);

CREATE TABLE Role_Permission (
                                 ID                  BIGSERIAL   NOT NULL,
                                 RoleID              BIGSERIAL   NOT NULL,
                                 PermissionID        BIGSERIAL   NOT NULL,
                                 PRIMARY KEY (ID, RoleID, PermissionID)
);

CREATE TABLE StudyProgram (
                              ID                  BIGSERIAL   NOT NULL,
                              Name                text,
                              Description         text,
                              FacultyID           BIGSERIAL   NOT NULL,
                              PRIMARY KEY (ID)
);

CREATE TABLE Subject (
                         ID                  BIGSERIAL   NOT NULL,
                         Name                text,
                         Description         text,
                         PRIMARY KEY (ID)
);

CREATE TABLE Subject_StudyProgram (
                                      ID                  BIGSERIAL   NOT NULL,
                                      SubjectID           BIGSERIAL   NOT NULL,
                                      StudyProgramID      BIGSERIAL   NOT NULL,
                                      PRIMARY KEY (ID)
);

CREATE TABLE User_Subject (
                              ID                  BIGSERIAL   NOT NULL,
                              SubjectID           BIGSERIAL   NOT NULL,
                              UserID              BIGSERIAL   NOT NULL,
                              Semester            int4,
                              PRIMARY KEY (ID)
);

CREATE TABLE Interest (
                          ID                  BIGSERIAL   NOT NULL,
                          Name                text,
                          Description         text,
                          PRIMARY KEY (ID)
);

CREATE TABLE User_Interest (
                               ID                  BIGSERIAL   NOT NULL,
                               UserID              BIGSERIAL   NOT NULL,
                               InterestID          BIGSERIAL   NOT NULL,
                               PRIMARY KEY (ID)
);

CREATE TABLE TopicSuggestion (
                                 ID                  BIGSERIAL   NOT NULL,
                                 Name                text,
                                 Description         text,
                                 "Date"              timestamp,
                                 isAvailable         bool,
                                 StudentID           BIGSERIAL   NOT NULL,
                                 MentorID            BIGSERIAL   NOT NULL,
                                 SubjectID           BIGSERIAL   NOT NULL,
                                 PRIMARY KEY (ID)
);

CREATE TABLE UserAttribute (
                               ID                  BIGSERIAL   NOT NULL,
                               Name                text,
                               PRIMARY KEY (ID)
);

CREATE TABLE UserAttributeValue (
                                    ID                  BIGSERIAL   NOT NULL,
                                    Value               text,
                                    UserID              BIGSERIAL   NOT NULL,
                                    UserAttributeID     BIGSERIAL   NOT NULL,
                                    PRIMARY KEY (ID)
);

CREATE TABLE Mentorship (
                            ID                  BIGSERIAL   NOT NULL,
                            StudentID           BIGSERIAL   NOT NULL,
                            MentorID            BIGSERIAL   NOT NULL,
                            TopicSuggestionID   BIGSERIAL   NOT NULL,
                            MentorshipTypeID    BIGSERIAL   NOT NULL,
                            PRIMARY KEY (ID)
);

CREATE TABLE MentorshipAttribute (
                                     ID                  BIGSERIAL   NOT NULL,
                                     Name                text,
                                     PRIMARY KEY (ID)
);

CREATE TABLE MentorshipType (
                                ID                      BIGSERIAL   NOT NULL,
                                Type                    text,
                                MentorshipAttributeID   BIGSERIAL   NOT NULL,
                                PRIMARY KEY (ID)
);

CREATE TABLE MentorshipAttributeValue (
                                          ID                      BIGSERIAL   NOT NULL,
                                          Value                   text,
                                          MentorshipID            BIGSERIAL   NOT NULL,
                                          MentorshipAttributeID   BIGSERIAL   NOT NULL,
                                          PRIMARY KEY (ID)
);

CREATE TABLE Task (
                      ID              BIGSERIAL   NOT NULL,
                      EndDate         timestamp,
                      StartDate       timestamp,
                      Status          int4,
                      Description     text,
                      MentorshipID    BIGSERIAL   NOT NULL,
                      PRIMARY KEY (ID)
);

CREATE TABLE Chat (
                      ID          BIGSERIAL   NOT NULL,
                      Title       text,
                      Topic       text,
                      Status      int4,
                      "Date"      timestamp,
                      StudentID   BIGSERIAL   NOT NULL,
                      MentorID    BIGSERIAL   NOT NULL,
                      PRIMARY KEY (ID)
);

CREATE TABLE Message (
                         ID          BIGSERIAL   NOT NULL,
                         Content     text,
                         isRead      bool,
                         Timestamp   timestamp,
                         ChatID      BIGSERIAL   NOT NULL,
                         UserID      BIGSERIAL   NOT NULL,
                         PRIMARY KEY (ID)
);

CREATE TABLE CommentOpinion (
                                ID              BIGSERIAL   NOT NULL,
                                Comment         text,
                                MentorRating    int4,
                                Timestamp       timestamp,
                                Status          int4,
                                StudentID       BIGSERIAL   NOT NULL,
                                MentorID        BIGSERIAL   NOT NULL,
                                PRIMARY KEY (ID)
);

CREATE TABLE Notification (
                              ID                  BIGSERIAL   NOT NULL,
                              Content             text,
                              Timestamp           timestamp,
                              isRead              bool,
                              UserToNotifyID      BIGSERIAL   NOT NULL,
                              CommentOpinionID    BIGSERIAL   NOT NULL,
                              MessageID           BIGSERIAL   NOT NULL,
                              PRIMARY KEY (ID)
);

CREATE TABLE Notification_Type (
                                   ID              BIGSERIAL   NOT NULL,
                                   Type            text,
                                   NotificationID  BIGSERIAL   NOT NULL,
                                   PRIMARY KEY (ID)
);



ALTER TABLE "User"              ADD CONSTRAINT "student enrolled in"           FOREIGN KEY (FacultyID)             REFERENCES Faculty          (ID);
ALTER TABLE Faculty              ADD CONSTRAINT FKFaculty543242                 FOREIGN KEY (UniversityID)          REFERENCES University        (ID);
ALTER TABLE User_Role            ADD CONSTRAINT FKUser_Role973318               FOREIGN KEY (UserID)                REFERENCES "User"            (ID);
ALTER TABLE User_Role            ADD CONSTRAINT FKUser_Role494723               FOREIGN KEY (RoleID)                REFERENCES Role              (ID);
ALTER TABLE Role_Permission      ADD CONSTRAINT FKRole_Permi373264              FOREIGN KEY (RoleID)                REFERENCES Role              (ID);
ALTER TABLE Role_Permission      ADD CONSTRAINT FKRole_Permi82941               FOREIGN KEY (PermissionID)          REFERENCES Permission        (ID);
ALTER TABLE StudyProgram         ADD CONSTRAINT FKStudyProgr253332              FOREIGN KEY (FacultyID)             REFERENCES Faculty           (ID);
ALTER TABLE Subject_StudyProgram ADD CONSTRAINT FKSubject_St661562              FOREIGN KEY (SubjectID)             REFERENCES Subject           (ID);
ALTER TABLE Subject_StudyProgram ADD CONSTRAINT FKSubject_St673947              FOREIGN KEY (StudyProgramID)        REFERENCES StudyProgram      (ID);
ALTER TABLE User_Subject         ADD CONSTRAINT FKUser_Subje900582              FOREIGN KEY (SubjectID)             REFERENCES Subject           (ID);
ALTER TABLE User_Subject         ADD CONSTRAINT FKUser_Subje776066              FOREIGN KEY (UserID)                REFERENCES "User"            (ID);
ALTER TABLE User_Interest        ADD CONSTRAINT FKUser_Inter956062              FOREIGN KEY (UserID)                REFERENCES "User"            (ID);
ALTER TABLE User_Interest        ADD CONSTRAINT FKUser_Inter806540              FOREIGN KEY (InterestID)            REFERENCES Interest          (ID);
ALTER TABLE TopicSuggestion      ADD CONSTRAINT "role=student"                  FOREIGN KEY (StudentID)             REFERENCES "User"            (ID);
ALTER TABLE TopicSuggestion      ADD CONSTRAINT "role=mentor"                   FOREIGN KEY (MentorID)              REFERENCES "User"            (ID);
ALTER TABLE TopicSuggestion      ADD CONSTRAINT FKTopicSugge693337              FOREIGN KEY (SubjectID)             REFERENCES Subject           (ID);
ALTER TABLE UserAttributeValue   ADD CONSTRAINT FKUserAttrib58999               FOREIGN KEY (UserID)                REFERENCES "User"            (ID);
ALTER TABLE UserAttributeValue   ADD CONSTRAINT FKUserAttrib518781              FOREIGN KEY (UserAttributeID)       REFERENCES UserAttribute     (ID);
ALTER TABLE Mentorship           ADD CONSTRAINT "available=false if accepted"   FOREIGN KEY (TopicSuggestionID)     REFERENCES TopicSuggestion   (ID);
ALTER TABLE Mentorship           ADD CONSTRAINT FKMentorship580471              FOREIGN KEY (MentorshipTypeID)      REFERENCES MentorshipType    (ID);
ALTER TABLE MentorshipType       ADD CONSTRAINT FKMentorship152845              FOREIGN KEY (MentorshipAttributeID) REFERENCES MentorshipAttribute (ID);
ALTER TABLE MentorshipAttributeValue ADD CONSTRAINT FKMentorship527684         FOREIGN KEY (MentorshipID)          REFERENCES Mentorship        (ID);
ALTER TABLE MentorshipAttributeValue ADD CONSTRAINT FKMentorship933147         FOREIGN KEY (MentorshipAttributeID) REFERENCES MentorshipAttribute (ID);
ALTER TABLE Task                 ADD CONSTRAINT FKTask579434                    FOREIGN KEY (MentorshipID)          REFERENCES Mentorship        (ID);
ALTER TABLE Chat                 ADD CONSTRAINT "student sends contact"         FOREIGN KEY (StudentID)             REFERENCES "User"            (ID);
ALTER TABLE Chat                 ADD CONSTRAINT "mentor receives contact"        FOREIGN KEY (MentorID)              REFERENCES "User"            (ID);
ALTER TABLE Message              ADD CONSTRAINT FKMessage915620                 FOREIGN KEY (ChatID)                REFERENCES Chat              (ID);
ALTER TABLE Message              ADD CONSTRAINT FKMessage63769                  FOREIGN KEY (UserID)                REFERENCES "User"            (ID);
ALTER TABLE CommentOpinion       ADD CONSTRAINT "student writes"                FOREIGN KEY (StudentID)             REFERENCES "User"            (ID);
ALTER TABLE CommentOpinion       ADD CONSTRAINT "mentor approves"               FOREIGN KEY (MentorID)              REFERENCES "User"            (ID);
ALTER TABLE Notification         ADD CONSTRAINT "user receives notification"    FOREIGN KEY (UserToNotifyID)        REFERENCES "User"            (ID);
ALTER TABLE Notification         ADD CONSTRAINT FKNotificati961095              FOREIGN KEY (CommentOpinionID)      REFERENCES CommentOpinion    (ID);
ALTER TABLE Notification         ADD CONSTRAINT FKNotificati879184              FOREIGN KEY (MessageID)             REFERENCES Message           (ID);
ALTER TABLE Notification_Type    ADD CONSTRAINT FKNotificati268813              FOREIGN KEY (NotificationID)        REFERENCES Notification      (ID);