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

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'WebBedoemmelse' AND cname = 'merit') THEN
ALTER TABLE WebBedoemmelse ADD merit bit default 0;
END IF;
IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'WebBedoemmelseAendret' AND cname = 'nyMerit') THEN
ALTER TABLE WebBedoemmelseAendret ADD nyMerit bit default 0;
END IF;
IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'WebBedoemmelseAendret' AND cname = 'gammelMerit') THEN
ALTER TABLE WebBedoemmelseAendret ADD gammelMerit bit default 0;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'WebBedoemmelse' AND cname = 'meritTermin') THEN
ALTER TABLE WebBedoemmelse ADD meritTermin varchar(80);
END IF;
IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'WebBedoemmelseAendret' AND cname = 'nyMeritTermin') THEN
ALTER TABLE WebBedoemmelseAendret ADD nyMeritTermin varchar(80);
END IF;
IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'WebBedoemmelseAendret' AND cname = 'gammelMeritTermin') THEN
ALTER TABLE WebBedoemmelseAendret ADD gammelMeritTermin varchar(80);
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'WebBedoemmelse' AND cname = 'meritAar') THEN
ALTER TABLE WebBedoemmelse ADD meritAar Integer;
END IF;
IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'WebBedoemmelseAendret' AND cname = 'nyMeritAar') THEN
ALTER TABLE WebBedoemmelseAendret ADD nyMeritAar Integer;
END IF;
IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'WebBedoemmelseAendret' AND cname = 'gammelMeritAar') THEN
ALTER TABLE WebBedoemmelseAendret ADD gammelMeritAar Integer;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'WebBedoemmelse' AND cname = 'meritInstitutionKode') THEN
ALTER TABLE WebBedoemmelse ADD meritInstitutionKode char(10);
END IF;
IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'WebBedoemmelseAendret' AND cname = 'nyMeritInstitutionKode') THEN
ALTER TABLE WebBedoemmelseAendret ADD nyMeritInstitutionKode char(10);
END IF;
IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'WebBedoemmelseAendret' AND cname = 'gammelMeritInstitutionKode') THEN
ALTER TABLE WebBedoemmelseAendret ADD gammelMeritInstitutionKode char(10);
END IF;
IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'WebBedoemmelse' and role = 'meritInstitutionKode' ) THEN
ALTER TABLE WebBedoemmelse ADD FOREIGN KEY meritInstitutionKode(meritInstitutionKode) REFERENCES Institution("institutionKode")  ON UPDATE CASCADE ON DELETE SET NULL;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'CensorLogin' AND cname = 'email') THEN
  ALTER TABLE CensorLogin ADD email varchar(100);
END IF;

// --- Delete duplicates ---
DELETE FROM AmuAktLinie a1
WHERE bidrag = 0 AND antalElever = 0
AND EXISTS
    (
        SELECT 1 FROM AmuAktLinie a2
        WHERE a1.id <> a2.id
        AND a1.tiaIndberetning_id = a2.tiaIndberetning_id
        AND a1.afdelingKode = a2.afdelingKode
        AND a1.afholdendeSkoleKode = a2.afholdendeSkoleKode
        AND a1.betalingsstatus = a2.betalingsstatus
        AND a1.xprsFag_id = a2.xprsFag_id
        AND a1.xprsRekvirenttype_id = a2.xprsRekvirenttype_id
        AND a1.startDato = a2.startDato
        AND a1.xprsTmk_id = a2.xprsTmk_id
        AND a1.xprsUddannelse_id = a2.xprsUddannelse_id
        AND a1.varighedDage = a2.varighedDage
        AND (a2.bidrag <> 0 or a2.antalElever <> 0)
    );
COMMIT;

IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'Bevis' and role = 'webUdsteder' ) THEN
   ALTER TABLE Bevis DROP CONSTRAINT udsteder;
   ALTER TABLE Bevis MODIFY brugerId NULL;
   ALTER TABLE Bevis ADD CONSTRAINT udsteder FOREIGN KEY (brugerId) REFERENCES Bruger(brugerId) ON UPDATE CASCADE ON DELETE SET NULL;
   ALTER TABLE Bevis ADD webBrugerId INTEGER NULL;
   ALTER TABLE Bevis ADD webUdstederBrugerId CHAR(80) NULL;
   ALTER TABLE Bevis ADD CONSTRAINT webUdsteder FOREIGN KEY (webBrugerId) REFERENCES WebBruger(id) ON UPDATE CASCADE ON DELETE SET NULL;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'SofBevis' and role = 'webUdsteder' ) THEN
   ALTER TABLE SofBevis MODIFY brugerId NULL;
   ALTER TABLE SofBevis ADD webBrugerId INTEGER NULL;
   ALTER TABLE SofBevis ADD webUdstederBrugerId CHAR(80) NULL;
   ALTER TABLE SofBevis ADD CONSTRAINT webUdsteder FOREIGN KEY (webBrugerId) REFERENCES WebBruger(id) ON UPDATE CASCADE ON DELETE SET NULL;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'Proevebevis' and role = 'webUdsteder' ) THEN
   ALTER TABLE Proevebevis RENAME webBrugerId TO webUdstederBrugerId;
   ALTER TABLE Proevebevis ADD webBrugerId INTEGER NULL;
   ALTER TABLE Proevebevis ADD CONSTRAINT webUdsteder FOREIGN KEY (webBrugerId) REFERENCES WebBruger(id) ON UPDATE CASCADE ON DELETE SET NULL;
END IF;

-- LUD-68901 - SyncHold V2 new fields
ALTER Table Holdgruppe modify Modified DATETIME default null; //Should not change modified in respect to the following changes
IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'Holdgruppe' AND cname = 'omproeve') THEN
  ALTER TABLE Holdgruppe ADD omproeve bit default 0;
END IF;
IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'Holdgruppe' AND cname = 'tolkebistand') THEN
  ALTER TABLE Holdgruppe ADD tolkebistand bit default 0;
END IF;
IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'Holdgruppe' AND cname = 'undervisningssprog') THEN
  ALTER TABLE Holdgruppe ADD undervisningssprog char(10) NOT NULL default 'DAN';
END IF;
IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'Holdgruppe' and role = 'undervisningssprog' ) THEN
   ALTER TABLE Holdgruppe ADD FOREIGN KEY undervisningssprog(undervisningssprog) REFERENCES Sprog(sprogKode) ON UPDATE CASCADE ON DELETE CASCADE;
END IF;
ALTER Table Holdgruppe modify Modified DATETIME DEFAULT TIMESTAMP;  //Should change modified again

update LUDUSWebProperty
set value = replace(value, 'VEU/SyncHold/v1', 'VEU/SyncHold/v2')
where value not like '%VEU/SyncHold/v2%'
and propertyName like 'voksen%'; 
commit;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'Karakter' AND cname = 'senestRettetAfMedarbejder') THEN
	ALTER TABLE Karakter ADD senestRettetAfMedarbejder CHAR(11);
	ALTER TABLE Karakter ADD FOREIGN KEY Medarbejder(senestRettetAfMedarbejder) REFERENCES Medarbejder(personnummer) ON UPDATE CASCADE ON DELETE SET NULL;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'Karakter' AND cname = 'rettetAfMedarbejderDatoTid') THEN
	ALTER TABLE Karakter ADD rettetAfMedarbejderDatoTid TIMESTAMP;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'KarakterAendret' AND cname = 'senestRettetAfMedarbejder') THEN
	ALTER TABLE KarakterAendret ADD senestRettetAfMedarbejder CHAR(11);
	ALTER TABLE KarakterAendret ADD FOREIGN KEY Medarbejder(senestRettetAfMedarbejder) REFERENCES Medarbejder(personnummer) ON UPDATE CASCADE ON DELETE SET NULL;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'KarakterAendret' AND cname = 'rettetAfMedarbejderDatoTid') THEN
	ALTER TABLE KarakterAendret ADD rettetAfMedarbejderDatoTid TIMESTAMP;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'ProeveHoldKarakter' AND cname = 'senestRettetAfMedarbejder') THEN
	ALTER TABLE ProeveHoldKarakter ADD senestRettetAfMedarbejder CHAR(11);
	ALTER TABLE ProeveHoldKarakter ADD FOREIGN KEY Medarbejder(senestRettetAfMedarbejder) REFERENCES Medarbejder(personnummer) ON UPDATE CASCADE ON DELETE SET NULL;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'ProeveHoldKarakter' AND cname = 'rettetAfMedarbejderDatoTid') THEN
	ALTER TABLE ProeveHoldKarakter ADD rettetAfMedarbejderDatoTid TIMESTAMP;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'ProeveHoldKarakterAendret' AND cname = 'senestRettetAfMedarbejder') THEN
	ALTER TABLE ProeveHoldKarakterAendret ADD senestRettetAfMedarbejder CHAR(11);
	ALTER TABLE ProeveHoldKarakterAendret ADD FOREIGN KEY Medarbejder(senestRettetAfMedarbejder) REFERENCES Medarbejder(personnummer) ON UPDATE CASCADE ON DELETE SET NULL;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'ProeveHoldKarakterAendret' AND cname = 'rettetAfMedarbejderDatoTid') THEN
	ALTER TABLE ProeveHoldKarakterAendret ADD rettetAfMedarbejderDatoTid TIMESTAMP;
END IF;

/* Sammenhæng mellem CensorLogin og dokumenter */
DROP TABLE IF EXISTS CensorLoginDokument
CREATE TABLE CensorLoginDokument (
    id INTEGER NOT NULL DEFAULT AUTOINCREMENT,
    version INTEGER NULL,
    modified DATETIME NOT NULL DEFAULT TIMESTAMP,
    censorLogin_id INTEGER NOT NULL,
    dokument_id INTEGER NOT NULL,
PRIMARY KEY 
    (id)
);

IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'CensorLoginDokument' and role = 'dokument' ) THEN
    ALTER TABLE CensorLoginDokument ADD FOREIGN KEY dokument(dokument_id) REFERENCES Dokument ON UPDATE CASCADE ON DELETE CASCADE;
END IF;
IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'CensorLoginDokument' and role = 'censorLogin' ) THEN
    ALTER TABLE CensorLoginDokument ADD FOREIGN KEY censorLogin(censorLogin_id) REFERENCES CensorLogin ON UPDATE CASCADE ON DELETE CASCADE;
END IF;

/* Copy attributes from CensorLogin */
IF EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'CensorLoginDokument') THEN
    INSERT INTO CensorLoginDokument (censorLogin_id, dokument_id, version) 
    SELECT id, censorbrevDokument_id, 1 FROM CensorLogin where censorbrevDokument_id IS NOT  NULL ;
    COMMIT;
END IF;

/* Tilføjelser til sammenhæng mellem CensorLogin og dokumenter */

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

IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'CensorLoginDokument' and role = 'termin' ) THEN
    ALTER TABLE CensorLoginDokument ADD FOREIGN KEY termin(terminKode) REFERENCES Termin (terminKode) ON UPDATE CASCADE;
END IF;
COMMIT;QUIT;