-- The files in /models determine the database scheme.
-- The organisational split into several files has no operational effects.
-- White-space and case matters: Each SQL table is named in 1st column of this file
-- Indendent lower-case lines describe the SQL-columns of the table with name, type and options
-- Nullable columns have "Maybe" written after their type
-- Option "default=xyz" is only used for database migrations due to changes in the SQL-schema, also see Model.Migration
-- Indendent upper-case lines usually impose Uniqueness constraints for rows by some columns.
-- Each table will also have an column storing a unique numeric row key, unless there is a row Primary columnname
--
User json    -- Each Uni2work user has a corresponding row in this table; created upon first login.
    surname        UserSurname              -- Display user names always through 'nameWidget displayName surname'
    displayName    UserDisplayName          -- we only show LDAP-DisplayName, and highlight LDAP-Surname within (appended if not contained)
    email          (CI Text)                -- Case-insensitive eMail address
    ident          (CI Text)                -- Case-insensitive user-identifier
    authentication AuthenticationMode       -- 'AuthLDAP' or ('AuthPWHash'+password-hash)
    lastAuthentication UTCTime Maybe        -- last login date
    created        UTCTime default=now()
    lastLdapSynchronisation UTCTime Maybe
    tokensIssuedAfter  UTCTime Maybe        -- do not accept bearer tokens issued before this time (accept all tokens if null)
    matrikelnummer UserMatriculation Maybe  -- optional immatriculation-string; usually a number, but not always (e.g. lecturers, pupils, guests,...)
    firstName      Text                     -- For export in tables, pre-split firstName from displayName
    title          Text Maybe               -- For upcoming name customisation
    maxFavourites  Int default=12           -- max number of rows with this userId in table "CourseFavourite"; for convenience links; user-defined
    theme          Theme default='Default'  -- Color-theme of the frontend; user-defined
    dateTimeFormat DateTimeFormat "default='%a %d %b %Y %R'" -- preferred Date+Time display format for user; user-defined
    dateFormat     DateTimeFormat "default='%d.%m.%Y'"       -- preferred Date-only display format for user; user-defined
    timeFormat     DateTimeFormat "default='%R'"             -- preferred Time-only display format for user; user-defined
    downloadFiles  Bool default=false           -- Should files be opened in browser or downloaded? (users often oblivious that their browser has a setting for this)
    mailLanguages  MailLanguages "default='[]'::jsonb"  -- Preferred language for eMail; i18n not yet implemented; user-defined
    notificationSettings NotificationSettings   -- Bit-array for which events email notifications are requested by user; user-defined
    warningDays    NominalDiffTime default=1209600 -- timedistance to pending deadlines for homepage infos
    UniqueAuthentication ident                  -- Column 'ident' can be used as a row-key in this table
    UniqueEmail email                           -- Column 'email' can be used as a row-key in this table
    deriving Show Eq Ord Generic                -- Haskell-specific settings for runtime-value representing a row in memory
UserFunction -- Administratively assigned functions (lecturer, admin, evaluation, ...)
    user     UserId
    school   SchoolId
    function SchoolFunction
    UniqueUserFunction user school function
UserExamOffice
    user     UserId
    field    StudyTermsId
    UniqueUserExamOffice user field
UserSchool -- Managed by users themselves, encodes "schools of interest"
    user     UserId
    school   SchoolId
    isOptOut Bool     -- true if this a marker, that the user manually deleted this entry; it should not be recreated automatically 
    UniqueUserSchool user school
StudyFeatures  -- multiple entries possible for students pursuing several degrees at once, usually created upon LDAP login
    user          UserId
    degree        StudyDegreeId   -- Abschluss, i.e. Master, Bachelor, etc.
    field         StudyTermsId    -- Fach, i.e. Informatics, Philosophy, etc.
    type          StudyFieldType  -- Major or minor, i.e. Haupt-/Nebenfach
    semester      Int
    updated       UTCTime default=now() -- last update from LDAP
    valid         Bool default=true       -- marked as active in LDAP (students may switch, but LDAP never forgets)
    UniqueStudyFeatures user degree field type semester
    -- UniqueUserSubject ubuser degree field -- There exists a counterexample
StudyDegree   -- Studienabschluss
    key           Int            -- LMU-internal key
    shorthand     Text Maybe     -- admin determined shorthand
    name          Text Maybe     -- description given by LDAP
    Primary key                  -- column key is used as actual DB row key
                                 -- newtype Key StudyDegree = StudyDegreeKey' { unStudyDegreeKey :: Int }
    deriving Show
StudyTerms    -- Studiengang
    key           Int            -- LMU-internal key
    shorthand     Text Maybe     -- admin determined shorthand
    name          Text Maybe     -- description given by LDAP
    Primary key                  -- column key is used as actual DB row key
                                 -- newtype Key StudyTerms = StudyTermsKey' { unStudyTermsKey :: Int }
    deriving Show
StudyTermCandidate    -- No one at LMU is willing and able to tell us the meaning of the keys for StudyDegrees and StudyTerms.
    -- Each LDAP login provides an unordered set of keys and an unordered set of plain text description with an unknown 1-1 correspondence.
    -- This table helps us to infer which key belongs to which plain text by recording possible combinations at login.
    -- If a login provides n keys and n plan texts, then n^2 rows with the same incidence are created, storing all combinations
    incidence     TermCandidateIncidence -- random id, generated once per login to associate matching pairs
    key           Int      -- a possible key for the studyTermName
    name          Text     -- studyTermName as plain text from LDAP
    deriving Show Eq Ord
