-- 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
    displayEmail   UserEmail
    email          UserEmail                -- Case-insensitive eMail address
    ident          UserIdent                -- 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 non-manual entries in favourites bar (pruned only if below a set importance threshold)
    maxFavouriteTerms Int default=2         -- max number of term-sections in favourites bar
    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)
    languages      Languages Maybe          -- Preferred language; 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
    csvOptions     CsvOptions "default='{}'::jsonb"
    sex            Sex Maybe
    showSex        Bool default=false
    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.
    superField    StudyTermsId Maybe
    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
    deriving Eq Show
    -- 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 Eq Show
StudyTerms    -- Studiengang
    key           Int            -- standardised key
    shorthand     Text Maybe     -- admin determined shorthand
    name          Text Maybe     -- description given by LDAP
    defaultDegree StudyDegreeId Maybe
    defaultType   StudyFieldType Maybe
    Primary key                  -- column key is used as actual DB row key
                                 -- newtype Key StudyTerms = StudyTermsKey' { unStudyTermsKey :: Int }
    deriving Eq Ord Show
StudySubTerms
    child         StudyTermsId
    parent        StudyTermsId
    UniqueStudySubTerms child parent
StudyTermNameCandidate    -- 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 or studySubTermName
    name          Text     -- studyTermName as plain text from LDAP
    deriving Show Eq Ord
StudySubTermParentCandidate
    incidence     TermCandidateIncidence
    key           Int
    parent        Int
    deriving Show Eq Ord
StudyTermStandaloneCandidate
    incidence     TermCandidateIncidence
    key           Int
    deriving Show Eq Ord

UserGroupMember
    group         UserGroupName
    user          UserId
    primary       Checkmark nullable

    UniquePrimaryUserGroupMember group primary !force
    UniqueUserGroupMember group user
    
