Qualification
  -- INVARIANT: 2*refreshWithin < validDuration 
  school              SchoolId --TODO: Ansprechpartner der Schule in Briefe erwähnen
  shorthand           (CI Text)
  name                (CI Text)
  description         StoredMarkup Maybe        -- user-defined large Html, ought to contain full description
  validDuration       Word Maybe    -- qualification is valid indefinitely or for a specified number of months
  auditDuration       Word Maybe                -- number of month to keep audit log; or indefinitely
  refreshWithin       CalendarDiffDays Maybe    -- notify users about renewal within this number of month/days before expiry
  elearningStart      Bool                      -- automatically schedule e-refresher
  -- elearningOnly       Bool                   -- successful E-learing automatically increases validity. NO!
  -- refreshInvitation   StoredMarkup           -- hard-coded I18N-MSGs used instead, but displayed on qualification page NO!
  -- expiryNotification  StoredMarkup Maybe     -- configurable user-profile-notifcations are used instead NO!
  SchoolQualificationShort school shorthand            -- must be unique per school and shorthand
  SchoolQualificationName  school name                 -- must be unique per school and name
  deriving Generic

-- TODOs:
--  - Enstehen Kosten, wenn Teilnehmer für KnowHow eingereiht werden, aber nicht am Kurs teilnehmen?
--    Falls ja, so sollte bei automatischem refresher vorher der Kunde durch FRADrive befragt werden?!
--    A: Der Inhaber per Email informieren! 
--    A: Es kann gleich eine LMS Pin generiert und verschickt werden!
--  - Aufteilung Qualification "R" in zwei Teile: "R e-learning" und "R praxis" okay? 

-- Besonderheiten:
--  - LmsIdent muss für alle Qualificationen einzigartig sein!
--  - Durchfallen wird mit UserList ständig erneut gesandt, bis Löschantrag gestellt wurde.
--  - Bestehen mit Result wird nur ein einziges mal gesendet! (Ausfallrisiko: keine Bestätigung der Kommunikation!)
--  - Explizites Löschen eines LmsIdent nach Success/Failure ist notwendig (feedback bei Block)
--  - LmsUser soll nur DELTA übermitteln. (GET Request will always return the same; until POST Request was processed!)
--  - PinReset==1 mit bestehendem Passwort kann problemlos erneut gesendet werden
--  - Flag "interner Mitarbeiter" wird von Know-How ignoriert / nicht ausgewertet (legacy)

QualificationPrecondition
  qualification     QualificationId OnDeleteCascade OnUpdateCascade -- AND: not unique, ie. qualification can have multiple required preconditions
  required          [QualificationId]           -- OR : alternatives, any one will suffice 
  continuous        Bool                        -- expiring precondition removes qualification
  deriving Generic

-- TODO: connect Qualification with Exams!

QualificationEdit
  user            UserId
  time            UTCTime
  qualification   QualificationId OnDeleteCascade OnUpdateCascade
  deriving Generic

QualificationUser
  user              UserId OnDeleteCascade OnUpdateCascade
  qualification     QualificationId OnDeleteCascade OnUpdateCascade
  validUntil        Day
  lastRefresh       Day                     -- lastRefresh > validUntil possible, if Qualification^elearningOnly == False
  firstHeld         Day                     -- first time the qualification was earned, should never change
  -- temporärer Entzug vorsehen
  -- Begründungsfeld vorsehen 
  UniqueQualificationUser qualification user
  deriving Generic

  -- LMS Interface Tables, need regular processing by background jobs, per QualificationId:
  -- 
  -- 1. Daily Job: Add to LmsUser daily all qualification holders with 
  --          QualificationUserValidUntil >= now 
  --       /\ QualificationUserValudUntil <= now + QualificationRefreshWithin    (time to schedule refresher)
  --       /\ not already enlisted  
  -- 
  -- 2. REST GET User.csv:
  --     - where LmsUserReceived == Nothing \/ (LmsUserResetPin /\ LmsUserEnded == Nothing)
  --     - delete-flag: isJust LmsUserStatus
  --   Note: REST means that LmsUserResetPin and LmsUserDelete remain unchanged by this GET request!
  --
  -- 3. REST POST Userlist.csv: just save as is to LmsUserlist
  --
  -- 4. REST POST Ergebnisse.csv: just save as is to LmsResult
  --
  -- 5. When received: Job LmsUserlist:               -- Note: containment needs at-once processing
  --     - For all LmsUser:
  --         + if contained:
  --              set LmsUserReceived to Just now()
  --              if LmsUserlistFailed: set LmsUserStatus to Just Day  
  --         + not contianed, by LmsUserReceived is set: set LmsUserEnded to Just now()
  --     - move row to LmsAudit
  --
  -- 6. When received: Daily Job LmsResult:
  --     - set LmsUserReceived to Just now()
  --     - set LmsUserStatus  to Just Day    -- always
  --     - move row to LmsAudit
  --
  -- 7. Daily Job: dequeue LMS Users
  --     - renew qualification, if passed
  --     - remove from LmsUser after audit Period has passed

LmsUser
  qualification     QualificationId OnDeleteCascade OnUpdateCascade
  user              UserId OnDeleteCascade OnUpdateCascade
  ident             LmsIdent                    -- must be unique accross all LMS courses!
  pin               Text
  resetPin          Bool          default=false -- should pin be reset?
  status            LmsStatus Maybe             -- open, success or failure; isJust indicates user will be deleted from LMS 
  --toDelete encoded by Handler.Utils.LMS.lmsUserToDelete
  started           UTCTime       default=now()
  received          UTCTime Maybe               -- last acknowledgement by LMS 
  ended             UTCTime Maybe               -- ident was deleted from LMS 
  -- Primary ident -- newtype Key LmsUserId = LmsUserKey { unLmsUser :: Text } -- change LmsIdent -> Text. Do we want this?
  UniqueLmsUser ident -- idents must be unique accross all qualifications, since idents are global within LMS!
  deriving Generic

-- LmsUserlist stores LMS upload for later processing only 
LmsUserlist
  qualification     QualificationId OnDeleteCascade OnUpdateCascade   
  ident             LmsIdent
  failed            Bool
  timestamp         UTCTime default=now()
  UniqueLmsUserlist qualification ident
  deriving Generic

-- LmsResult stores LMS upload for later processing only 
LmsResult
  qualification     QualificationId OnDeleteCascade OnUpdateCascade
  ident             LmsIdent
  success           Day
  timestamp         UTCTime default=now()
  UniqueLmsResult qualification ident -- required by DBTable
  deriving Generic

-- Logs all processed rows from LmsUserlist and LmsResult
LmsAudit 
  qualification     QualificationId OnDeleteCascade OnUpdateCascade
  ident             LmsIdent
  notificationType  LmsStatus               -- LmsBlocked Day | LmsSuccess Day   
  received          UTCTime                 -- timestamp from LmsUserlist/LmsResult
  processed         UTCTime default=now()
  deriving Generic
