**************************************************************************************
* Bemærk at denne fil kun er til orientering om databaseændringer til denne release. *
* Filen MÅ IKKE afvikles!                                                            *
**************************************************************************************

-- aljun håndbåret på release dagen
delete from WebMenuPunktAspekt where navn = 'Progressionsoversigt'; commit;
delete from WebBrugerGruppeFunktionAspekt where navn = 'Progressionsoversigt'; commit;

IF NOT EXISTS (select 1 from sys.sysIndexes where tname = 'WebAmuTilmelding' and iname = 'Modified') THEN
  CREATE INDEX Modified ON WebAmuTilmelding
    (modified desc);
END IF;

-- LUD 63111
IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'UngeDbUddPaalaegElevStatus' AND cname = 'indberettet') THEN
  ALTER TABLE UngeDbUddPaalaegElevStatus ADD indberettet BIT DEFAULT '0';
END IF;

-- LUD 71193
DROP TABLE IF EXISTS ArkivUngeDbUddannelsesPaalaegElevStatus
CREATE TABLE ArkivUngeDbUddannelsesPaalaegElevStatus(
    id INTEGER NOT NULL DEFAULT AUTOINCREMENT,
    uddPaalaegId BIGINT NOT NULL,
    fornavn VARCHAR(50),
    efternavn VARCHAR(50),
    cprNr VARCHAR(10) NOT NULL,
    personnummer VARCHAR(11) NOT NULL,
    aktivUddPaalaeg BIT NOT NULL,
    fejlKode INTEGER,
    fejlTekst VARCHAR(500),
    senestIndlaest TIMESTAMP,
    kursist VARCHAR(500),
    ungeDbUddPaalaegDetaljer VARCHAR(500),
    ungeDbUddPaalaegJobcenterKontakt VARCHAR(500),
    ungeDbUddPaalaegSkoleKontaktId VARCHAR(500),
    indberettet BIT NOT NULL,
    arkiveringsdato DATE,
    tidligstSletDato DATE,
    PRIMARY KEY (id)
);

-- LUD-70367
IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'KursistJournal' AND cname = 'webBrugerId') THEN
  ALTER TABLE KursistJournal ADD webBrugerId INTEGER;
END IF;
IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'KursistJournal' and role = 'webBruger' ) THEN
   ALTER TABLE KursistJournal ADD FOREIGN KEY webBruger(webBrugerId) REFERENCES WebBruger(id) ON UPDATE CASCADE ON DELETE SET NULL;
END IF;

IF EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'KursistJournal' and role = 'bruger' ) THEN
   ALTER TABLE KursistJournal DROP FOREIGN KEY bruger;
END IF;
IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'KursistJournal' and role = 'bruger' ) THEN
   ALTER TABLE KursistJournal ADD FOREIGN KEY bruger(brugerId) REFERENCES bruger(brugerId) ON UPDATE CASCADE ON DELETE SET NULL;
END IF;


DROP TABLE IF EXISTS  TildeltWebBrugerKursistJournal;
CREATE TABLE TildeltWebBrugerKursistJournal (
    id INTEGER NOT NULL DEFAULT AUTOINCREMENT,
	identifikation INTEGER NOT NULL,
	personnummer char(11) NOT NULL,
	webBrugerId INTEGER NOT NULL,
	journalLaest bit NULL DEFAULT  0,
    version INTEGER,
    modified DATETIME DEFAULT TIMESTAMP,
PRIMARY KEY (id));


IF NOT EXISTS (select 1 from sys.sysIndexes where tname = 'TildeltWebBrugerKursistJournal' and iname = 'TildeltWebBrugerKursistJournal1') THEN
  CREATE UNIQUE INDEX TildeltWebBrugerKursistJournal1 ON TildeltWebBrugerKursistJournal (identifikation, personnummer, webBrugerId);
END IF;
IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'TildeltWebBrugerKursistJournal' and role = 'webBruger' ) THEN
   ALTER TABLE TildeltWebBrugerKursistJournal ADD FOREIGN KEY webBruger(webBrugerId) REFERENCES WebBruger(id) ON UPDATE CASCADE ON DELETE CASCADE;
END IF;
IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'TildeltWebBrugerKursistJournal' and role = 'kursistJournal' ) THEN
   ALTER TABLE TildeltWebBrugerKursistJournal ADD FOREIGN KEY kursistJournal(identifikation, personnummer) REFERENCES KursistJournal(identifikation, personnummer ) ON UPDATE CASCADE ON DELETE CASCADE;
END IF;


-- max(id) er tilføjet da 2 webbrugere godt kan være tilknyttet samme LudusBruger
UPDATE KursistJournal kj set webBrugerId = (select max(id) from webBruger wb where wb.LudusBrugerId = kj.brugerId ); commit;

--DELETE FROM TildeltWebBrugerKursistJournal; commit;
IF NOT EXISTS (select 1 FROM TildeltWebBrugerKursistJournal) THEN
  INSERT INTO TildeltWebBrugerKursistJournal (identifikation,personnummer,webBrugerId,journalLaest, version)
  select identifikation,personnummer,
    (select max(id) from webBruger wb where wb.LudusBrugerId = kj.brugerId), 
    (CASE kj.journalLaest WHEN 'X' THEN 1 ELSE 0 END), 0
  from TildeltBrugerKursistJournal kj
  where exists (select 1 from webBruger wb where wb.LudusBrugerId = kj.brugerId);
COMMIT;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'WebBrugerIndstillinger' AND cname = 'holdStart') THEN
  ALTER TABLE WebBrugerIndstillinger ADD holdStart  CHAR(8);
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'WebBrugerIndstillinger' AND cname = 'holdTypeKode') THEN
  ALTER TABLE WebBrugerIndstillinger ADD holdTypeKode CHAR(10);
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'WebBrugerIndstillinger' AND cname = 'duBeregningsform') THEN
  ALTER TABLE WebBrugerIndstillinger ADD duBeregningsform CHAR(30);
END IF;

UPDATE KontaktgruppeHenvendelse 
SET arkiveringsKategori = 'UNDERVISNINGSPLAN' 
WHERE arkiveringsKategori = 'OBU_UNDERVISNINGSPLAN';

UPDATE KontaktgruppeSvar 
SET arkiveringsKategori = 'UNDERVISNINGSPLAN' 
WHERE arkiveringsKategori = 'OBU_UNDERVISNINGSPLAN';

UPDATE Goeremaal 
SET arkiveringsKategori = 'UNDERVISNINGSPLAN' 
WHERE arkiveringsKategori = 'OBU_UNDERVISNINGSPLAN';

UPDATE KursistJournal 
SET arkiveringsKategori = 'UNDERVISNINGSPLAN' 
WHERE arkiveringsKategori = 'OBU_UNDERVISNINGSPLAN';

UPDATE Dokument 
SET arkiveringsKategori = 'UNDERVISNINGSPLAN' 
WHERE arkiveringsKategori = 'OBU_UNDERVISNINGSPLAN';

UPDATE sag 
SET arkiveringsKategori = 'UNDERVISNINGSPLAN' 
WHERE arkiveringsKategori = 'OBU_UNDERVISNINGSPLAN';

UPDATE EudDokument 
SET arkiveringsKategori = 'UNDERVISNINGSPLAN' 
WHERE arkiveringsKategori = 'OBU_UNDERVISNINGSPLAN';

UPDATE PDFOgEmailSkabelon 
SET arkiveringsKategori = 'UNDERVISNINGSPLAN' 
WHERE arkiveringsKategori = 'OBU_UNDERVISNINGSPLAN';

UPDATE Holdgruppe 
SET arkiveringsKategori = 'UNDERVISNINGSPLAN' 
WHERE arkiveringsKategori = 'OBU_UNDERVISNINGSPLAN';
COMMIT;

-- LUD-71132
DROP TABLE IF EXISTS ErrorLog;
CREATE TABLE ErrorLog
(
    id INTEGER NOT NULL DEFAULT AUTOINCREMENT,
    version INTEGER,
    created DATETIME NOT NULL DEFAULT TIMESTAMP,
    correlationId varchar(36) NOT NULL,
    stacktrace long varchar,
    msg long varchar,
    clazz varchar(250) NOT NULL,
PRIMARY KEY (id));
CREATE UNIQUE INDEX CorrelationId ON ErrorLog (correlationId asc);

update hold set LudusWebKonverteret = 'Nej' where  LudusWebKonverteret is null; commit;

IF EXISTS(SELECT 1 FROM LudusWebProperty WHERE propertyName = 'ARKIVERING_REGEL_KONFIGURATION') THEN
BEGIN
DECLARE @originalConfig LONG VARCHAR;
DECLARE @firstHalf LONG VARCHAR;
DECLARE @originalconfigLength INTEGER;
DECLARE @secondHalf LONG VARCHAR;
DECLARE @newName LONG VARCHAR;

SET @originalConfig = (SELECT value FROM LudusWebProperty WHERE propertyName = 'ARKIVERING_REGEL_KONFIGURATION');
SET @firstHalf = (SELECT SUBSTRING(@originalConfig, 0, CHARINDEX(',"SU_GRUNDLAG"', @originalConfig)));
SET @originalconfigLength = (SELECT LEN(@originalConfig));
SET @secondHalf = (SELECT SUBSTRING(@originalConfig,
                      CHARINDEX(',"TRO_OG_LOVE"', @originalConfig),
                      @originalconfigLength - CHARINDEX(',"TRO_OG_LOVE"', @originalConfig) + 1));
SET @newName = (SELECT @firstHalf + ',"SU_GRUNDLAG":{"years":30,"months":0}' + @secondHalf);

UPDATE LudusWebProperty SET value = @newName WHERE propertyName = 'ARKIVERING_REGEL_KONFIGURATION';
END;
COMMIT;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'bevisKarakter' and role = 'karakter' ) THEN
   ALTER TABLE bevisKarakter
        ADD FOREIGN KEY karakter(_karakter_fagKode,
        _karakter_holdId,
        _karakter_holdStart, 
        personnummer, 
        _karakter_proeveform,
        _karakter_terminKode) 
    REFERENCES karakter(fagKode,
        holdId,
        holdStart, 
        personnummer, 
        proeveform,
        terminKode) ON UPDATE CASCADE;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'bevisKarakter' and role = 'eksternKarakter' ) THEN
   ALTER TABLE bevisKarakter
        ADD FOREIGN KEY eksternKarakter(personnummer,
        _eksternKarakter_fagKode,
        _eksternKarakter_proeveform,
        _eksternKarakter_terminKode,
        _eksternKarakter_institutionKode,
        _eksternKarakter_aarskarakter) 
    REFERENCES eksternKarakter(personnummer,
        fagKode,
        proeveform,
        terminKode,
        institutionKode,
        aarskarakter) ON UPDATE CASCADE;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'bevisKarakter' and role = 'aarsKarakter' ) THEN
   ALTER TABLE bevisKarakter
        ADD FOREIGN KEY aarsKarakter(personnummer,
        _aarsKarakter_holdId,
        _aarsKarakter_holdStart,
        _aarsKarakter_fagKode,
        _aarsKarakter_proeveform) 
    REFERENCES aarsKarakter(personnummer,
        holdId,
        holdStart,
        fagKode,
        proeveform) ON UPDATE CASCADE;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'bevisKarakter' and role = 'proeveholdKarakter' ) THEN
   ALTER TABLE bevisKarakter
        ADD FOREIGN KEY proeveholdKarakter(personnummer,
        _proeveholdKarakter_proeve,
        _proeveholdKarakter_terminKode) 
    REFERENCES proeveholdKarakter(personnummer,
        proeve,
        terminKode) ON UPDATE CASCADE;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'bevisKarakter' and role = 'standpunktKarakter' ) THEN
   ALTER TABLE bevisKarakter
        ADD FOREIGN KEY standpunktKarakter(_standpunktKarakter_id) 
    REFERENCES standpunktKarakter(id) ON UPDATE CASCADE;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'bevisKarakter' and role = 'webKarakter' ) THEN
   ALTER TABLE bevisKarakter
        ADD FOREIGN KEY webKarakter(_webKarakter_id) 
    REFERENCES webKarakter(id) ON UPDATE CASCADE;
END IF;

IF NOT EXISTS (select 1 from SYS.SYSCOLUMNS where tname = 'Tilskud' and cname = 'udgaaet') THEN
  ALTER TABLE Tilskud ADD udgaaet bit NOT NULL DEFAULT  0;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'proevebeviskarakter' and role = 'karakter' ) THEN
   ALTER TABLE proevebeviskarakter
        ADD FOREIGN KEY karakter(_karakter_fagKode,
        _karakter_holdId,
        _karakter_holdStart, 
        personnummer, 
        _karakter_proeveform,
        _karakter_terminKode) 
    REFERENCES karakter(fagKode,
        holdId,
        holdStart, 
        personnummer, 
        proeveform,
        terminKode) ON UPDATE CASCADE;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'proevebeviskarakter' and role = 'eksternKarakter' ) THEN
   ALTER TABLE proevebeviskarakter
        ADD FOREIGN KEY eksternKarakter(personnummer,
        _eksternKarakter_fagKode,
        _eksternKarakter_proeveform,
        _eksternKarakter_terminKode,
        _eksternKarakter_institutionKode,
        _eksternKarakter_aarskarakter) 
    REFERENCES eksternKarakter(personnummer,
        fagKode,
        proeveform,
        terminKode,
        institutionKode,
        aarskarakter) ON UPDATE CASCADE;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'proevebeviskarakter' and role = 'aarsKarakter' ) THEN
   ALTER TABLE proevebeviskarakter
        ADD FOREIGN KEY aarsKarakter(personnummer,
        _aarsKarakter_holdId,
        _aarsKarakter_holdStart,
        _aarsKarakter_fagKode,
        _aarsKarakter_proeveform) 
    REFERENCES aarsKarakter(personnummer,
        holdId,
        holdStart,
        fagKode,
        proeveform) ON UPDATE CASCADE;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'proevebeviskarakter' and role = 'proeveholdKarakter' ) THEN
   ALTER TABLE proevebeviskarakter
        ADD FOREIGN KEY proeveholdKarakter(personnummer,
        _proeveholdKarakter_proeve,
        _proeveholdKarakter_terminKode) 
    REFERENCES proeveholdKarakter(personnummer,
        proeve,
        terminKode) ON UPDATE CASCADE;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'proevebeviskarakter' and role = 'standpunktKarakter' ) THEN
   ALTER TABLE proevebeviskarakter
        ADD FOREIGN KEY standpunktKarakter(_standpunktKarakter_id) 
    REFERENCES standpunktKarakter(id) ON UPDATE CASCADE;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'proevebeviskarakter' and role = 'webKarakter' ) THEN
   ALTER TABLE proevebeviskarakter
        ADD FOREIGN KEY webKarakter(_webKarakter_id) 
    REFERENCES webKarakter(id) ON UPDATE CASCADE;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'LocalUdmeldingsinformation') THEN
    CREATE TABLE LocalUdmeldingsinformation
    (id INTEGER NOT NULL DEFAULT AUTOINCREMENT,
     version INTEGER NOT NULL,
     modified DATETIME NOT NULL DEFAULT TIMESTAMP,
     udmeldingsId INTEGER NOT NULL,
     udmeldingsNummer INTEGER NOT NULL,
     udmeldingsDato DATE NOT NULL,
     primary key
         (id));
END IF;
CREATE UNIQUE INDEX udmeldingsNummer ON LocalUdmeldingsinformation (udmeldingsNummer asc);

COMMIT;
QUIT;