-- SPDX-FileCopyrightText: 2022-25 Sarah Vaupel <sarah.vaupel@ifi.lmu.de>,Steffen Jost <s.jost@fraport.de>
--
-- SPDX-License-Identifier: AGPL-3.0-or-later

Qualification
  -- INVARIANT: 2*refreshWithin < validDuration
  school              SchoolId                  --  1
  shorthand           (CI Text)                 --  2
  name                (CI Text)                 --  3
  description         StoredMarkup Maybe        --  4 user-defined large Html, ought to contain full description
  validDuration       Int Maybe                 --  5 if > 0, qualification is valid indefinitely or for a specified number of months, use with addMonthsDay
  auditDuration       Int  default=366          --  6 number of days to keep LMS audit log and LmsUserIdents -- TODO: audit period for QualificationUser/Block as well
  refreshWithin       CalendarDiffDays Maybe    --  7 notify users about renewal within this number of month/days before expiry; to be used with addGregorianDurationClip
  refreshReminder     CalendarDiffDays Maybe    --  8 send a second notification about renewal within this number of month/days before expiry
  elearningStart      Bool                      --  9 automatically schedule e-refresher
  elearningRenews     Bool default=true         -- 10 successful e-learing automatically increases validity automatically by validDuration
  elearningLimit      Int Maybe                 -- 11 limit of e-learning attempts, currently only for informative purposes, as it is enforced by LMS only
  lmsReuses           QualificationId Maybe     -- 12 if set, lms is also included within the given qualification's lms, but only for direct routes. AuditDuration is used from this Qualification instead.
  expiryNotification  Bool default=true         -- 13 should expiryNotification be generated for this qualification?
  avsLicence          AvsLicence Maybe          -- 14 if set, valid QualificationUsers are synchronized to AVS as a driving licence
  sapId               Text Maybe                -- 15 if set, valid QualificationUsers with userCompanyPersonalNumber are transmitted via SAP interface under this id  
  SchoolQualificationShort school shorthand            -- must be unique per school and shorthand
  SchoolQualificationName  school name                 -- must be unique per school and name
    -- across all schools, only one qualification may be a driving licence -- NO LONGER TRUE
    -- UniqueQualificationAvsLicence avsLicence      !force -- either empty or unique 
    -- NOTE: two NULL values are not equal for the purpose of Uniqueness constraints!
  deriving Show Eq Generic Binary

-- 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                       -- NOTE: this can only be enforced through a background job adding or removing qualifications
--  qualification     QualificationId OnDeleteCascade OnUpdateCascade -- AND: not unique, ie. qualification can have multiple required preconditions
--  required          [QualificationId]           -- OR : alternatives, any one will suffice -- we don't want array, since we have recursive CTEs
--  continuous        Bool                        -- expiring precondition blocks qualification
--  deriving Generic Show

-- Maybe an alternative for online qualification validity checking, transitivity through recursive CTEs? (already available in our version)
QualificationRequirement
 qualification     QualificationId OnDeleteCascade OnUpdateCascade
 requirement       QualificationId OnDeleteCascade OnUpdateCascade
 group             Int        -- OR: several requirements within the same group are considered equivalent; no order between groups
 note              Text       -- for humans only, no semantical effect
 UniqueQualificationRequirement qualification requirement
 deriving Generic Show

-- TODO: connect Qualification with Exams!

QualificationEdit
  user            UserId
  time            UTCTime
  qualification   QualificationId OnDeleteCascade OnUpdateCascade
  deriving Generic Show

QualificationUser
  user              UserId OnDeleteCascade OnUpdateCascade
  qualification     QualificationId OnDeleteCascade OnUpdateCascade
  validUntil        Day                           -- addGregorianMonthsRollOver (toInteger renewalMonths) qualificationUserValidUntil
  lastRefresh       Day                           -- lastRefresh > validUntil possible, if Qualification^elearningOnly == False
  firstHeld         Day                           -- first time the qualification was earned, should never change
  scheduleRenewal   Bool  default=true            -- if false, no automatic renewal is scheduled and the qualification expires
  lastNotified      UTCTime default=now()         -- last notficiation about actual licence validity changes (does not entail e-learning notifications)
  -- Reasons and temporary revocations are implemented through QualificationUserBlock
  -- TODO: adjust SAP interface to transmit end dates
  UniqueQualificationUser qualification user
  deriving Generic Show

QualificationUserBlock
  qualificationUser QualificationUserId OnDeleteCascade OnUpdateCascade
  unblock           Bool
  from              UTCTime  
  reason            Text  
  blocker           UserId Maybe
  deriving Eq Ord Read Show 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 Report.csv: just save as is to LmsReport for later processing
  --
  -- 4. When received: Job LmsReport:               -- Note: containment needs at-once processing
  --     - For all LmsUser:
  --         + if contained:
  --              set LmsUserReceived to Just now()
  --              if Failed:  set LmsUserStatus to Just LmsBlocked now
  --              if Success: set LmsUserStatus to Just LmsSuccess now
  --                          and renew QualificationValidTo
  --         + not contained, by LmsUserReceived is set: set LmsUserEnded to Just now()
  --     - move row to LmsAudit
  --
  -- 5. Daily Job: dequeue LMS Users
  --     - fail and mark expired LmsUser
  --     - 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?  
  datePin           UTCTime         default=now() -- time pin was created
  status            LmsStatus Maybe               -- Nothing=open, LmsSuccess, LmsBlocked or LmsExpired; status should never change unless isNothing; isJust indicates lms is finished and user shall be deleted from LMS
  --toDelete encoded by Handler.Utils.LMS.lmsUserToDelete
  statusDay         UTCTime Maybe                 -- last status change; should be isJust iff isJust status; modelling as a separate table too bothersome, unlike qualification block  
  started           UTCTime         default=now()
  received          UTCTime Maybe                 -- last acknowledgement by LMS
  notified          UTCTime Maybe                 -- last notified by FRADrive
  ended             UTCTime Maybe                 -- ident was deleted from LMS
  resetTries        Bool            default=false -- V2 should e-learning exam tries be reset?
  locked            Bool            default=false -- V2 last returned lock status
  -- Primary ident -- newtype Key LmsUserId = LmsUserKey { unLmsUser :: Text } -- change LmsIdent -> Text. Do we want this? No.
  UniqueLmsIdent ident                            -- idents must be unique accross all qualifications, since idents are global within LMS!
  UniqueLmsQualificationUser qualification user   -- each user may be enrolled at most once per course
  deriving Generic Show

-- LmsUserStatus
--   lmsUser LmsUserId OnDeleteCascade OnUpdateCascade
--   result  LmsStatus -- data LmsStatus = LmsBlocked | LmsExpired | LmsSuccess
--   day     Day   
--   UniqueLmsUserStatus lmsUser -- enforcing uniqueness prohibits history
--   deriving Generic

-- V2 Stores LMS upload for processing in Background Job
LmsReport
  qualification     QualificationId OnDeleteCascade OnUpdateCascade
  ident             LmsIdent
  date              UTCTime Maybe   -- BEWARE: timezone is local as submitted by LMS
  result            LmsState        -- (0|1|2) 0=LmsFailed[too many tries], 1=LmsOpen, 2=LmsPassed[success]
  lock              Bool            -- (0|1)
  timestamp         UTCTime         default=now()
  UniqueLmsReport qualification ident -- required by DBTable
  deriving Generic Show

-- LmsAudit removed by commit 71cde92a
-- due to frequent transmit errors, a separate lms tranmission log is necessary again
LmsReportLog
  qualification     QualificationId OnDeleteCascade OnUpdateCascade
  ident             LmsIdent
  date              UTCTime Maybe   -- BEWARE: timezone is local as submitted by LMS
  result            LmsState        -- (0|1|2) 0=LmsFailed[too many tries], 1=LmsOpen, 2=LmsPassed[success]
  lock              Bool            -- (0|1)
  timestamp         UTCTime         default=now()  
  missing           Bool            default=false
  deriving Generic Show

-- Table to manage unknown or orphaned lms identifiers
LmsOrphan
  qualification             QualificationId OnDeleteCascade OnUpdateCascade  
  ident                     LmsIdent                  -- must be unique accross all LMS courses!
  seenFirst                 UTCTime   default=now()   -- first time reported by LMS
  seenLast                  UTCTime   default=now()   -- last acknowledgement by LMS, deletion uses QualificationAuditDuration
  deletedLast               UTCTime   Maybe           -- last deletion request sent to LMS
  resultLast                LmsState                  -- last received learning state
  reason                    Text      Maybe           -- to mark explicit e-learning deletions, etc
  UniqueLmsOrphan qualification ident                 -- unlike other tables, LMS Idents must only be unique within qualification, allowing orphans to be handled independently
  deriving Generic Show