CREATE TABLE [KP_LNKBR_CMDB_ITM_CMDB_ITM_] (
    [KP_LNKBR_CMDB_ITM_CMDB_ITM__ID] INT PRIMARY KEY,
    [LINKBAAR_CMDB_ITEM_ID] INT,
    [CMDB_ITEM__ID] INT
);

CREATE TABLE [LEVERANCIER] (
    [LEVERANCIER_ID] INT PRIMARY KEY,
    [NAAM] VARCHAR(200),
    [NUMMER] VARCHAR(255)
);

CREATE TABLE [GEBRUIKERROL_835] (
    [GEBRUIKERROL_835_ID] INT PRIMARY KEY,
    [ROL_ENUM_ID] INT
);

CREATE TABLE [APPLICATIECATEGORIE_ENUM] (
    [APPLICATIECATEGORIE_ENUM_ID] INT PRIMARY KEY,
    VALUE VARCHAR(255)
);

INSERT INTO [APPLICATIECATEGORIE_ENUM] ([APPLICATIECATEGORIE_ENUM_ID], VALUE) VALUES
    (1, 'BBA'),
    (2, 'BEHEER_EN_SYSTEEM'),
    (3, 'KA_BASIS'),
    (4, 'KA_EXTRA'),
    (5, 'KERNAPPLICATIE'),
    (6, 'NIET_INGEDEELD');

CREATE TABLE [NOTITIE] (
    [NOTITIE_ID] INT PRIMARY KEY,
    [AUTEUR_MEDEWERKER_ID] INT,
    [DATUM] DATE,
    [HEEFT_NOTITIES_CMDB_ITEM__ID] INT,
    [INHOUD] NVARCHAR(MAX)
);

CREATE TABLE [SERVER] (
    [CMDB_ITEM__ID] INT PRIMARY KEY,
    [ACTIEF] BIT,
    [HEEFT_LEVERANCIER_LEVERANCIER_ID] INT,
    [IPADRES] VARCHAR(255),
    [LOCATIE] VARCHAR(255),
    [ORGANISATIE] VARCHAR(80),
    [SERIENUMMER] VARCHAR(255),
    [SERVERTYPE_ENUM_ID] INT,
    [VLAN] VARCHAR(255)
);

CREATE TABLE [KOPPELING] (
    [KOPPELING_ID] INT PRIMARY KEY,
    [BESCHRIJVING] VARCHAR(200),
    [DIRECT] BIT,
    [LINK_NAAR_CMDB_ITEM__ID] INT,
    [LINK_VAN_CMDB_ITEM__ID] INT,
    [TOELICHTING] NVARCHAR(MAX)
);

CREATE TABLE [MEDEWERKER] (
    [MEDEWERKER_ID] INT PRIMARY KEY,
    [ACHTERNAAM] VARCHAR(200),
    [DATUMINDIENST] DATE,
    [DATUMUITDIENST] DATE,
    [EMAILADRES] VARCHAR(255),
    [EXTERN] BIT,
    [FUNCTIE] VARCHAR(50),
    [GELEVERD_VIA_LEVERANCIER_ID] INT,
    [GESLACHTSAANDUIDING] VARCHAR(255),
    [MEDEWERKERIDENTIFICATIE] VARCHAR(255),
    [MEDEWERKERTOELICHTING] VARCHAR(255),
    [ROEPNAAM] VARCHAR(255),
    [TELEFOONNUMMER] VARCHAR(20),
    [VOORLETTERS] VARCHAR(20),
    [VOORVOEGSELACHTERNAAM] VARCHAR(255)
);

CREATE TABLE [KP_APPLCT_LNKBR_CMDB_ITM] (
    [KP_APPLCT_LNKBR_CMDB_ITM_ID] INT PRIMARY KEY,
    [APPLICATIE_ID] INT,
    [LINKBAAR_CMDB_ITEM_ID] INT
);

CREATE TABLE [KP_APPLCT_DCMNT] (
    [KP_APPLCT_DCMNT_ID] INT PRIMARY KEY,
    [APPLICATIE_ID] INT,
    [DOCUMENT_ID] INT
);

CREATE TABLE [SERVERTYPES_ENUM] (
    [SERVERTYPES_ENUM_ID] INT PRIMARY KEY,
    VALUE VARCHAR(255)
);

INSERT INTO [SERVERTYPES_ENUM] ([SERVERTYPES_ENUM_ID], VALUE) VALUES
    (1, 'NIET_VIRTUEEL'),
    (2, 'VIRTUEEL');

CREATE TABLE [APPLICATIE] (
    [CMDB_ITEM__ID] INT PRIMARY KEY,
    [APPLICATIEURL] VARCHAR(255),
    [BEHEERSTATUS] INT,
    [BELEIDSDOMEIN] VARCHAR(255),
    [CATEGORIE_ENUM_ID] INT,
    [GUID] VARCHAR(255),
    [HEEFT_LEVERANCIER_LEVERANCIER_ID] INT,
    [NAAM] VARCHAR(255),
    [OMSCHRIJVING] NVARCHAR(MAX),
    [PACKAGINGSTATUS] INT
);

CREATE TABLE [PACKAGINGSTATUS_ENUM] (
    [PACKAGINGSTATUS_ENUM_ID] INT PRIMARY KEY,
    VALUE VARCHAR(255)
);

INSERT INTO [PACKAGINGSTATUS_ENUM] ([PACKAGINGSTATUS_ENUM_ID], VALUE) VALUES
    (1, 'ALLEEN_AANBIEDEN'),
    (2, 'API_MOGELIJK_'),
    (3, 'HANDMATIG_INSTALLEREN'),
    (4, 'NIET_AANBIEDEN'),
    (5, 'NIET_INGEDEELD'),
    (6, 'PACKAGEN_EN_DISTRIBUEREN');

CREATE TABLE [CMDB_ITEM_] (
    [CMDB_ITEM__ID] INT PRIMARY KEY,
    [BESCHRIJVING] NVARCHAR(MAX),
    [NAAM] VARCHAR(255)
);

CREATE TABLE [LINKBAAR_CMDB_ITEM] (
    [CMDB_ITEM__ID] INT PRIMARY KEY
);

CREATE TABLE [VERSIE] (
    [VERSIE_ID] INT PRIMARY KEY,
    [AANTAL] INT,
    [DATUMEINDESUPPORT] DATE,
    [HEEFT_VERSIES_CMDB_ITEM__ID] INT,
    [KOSTEN] DECIMAL(10,2),
    [LICENTIE] VARCHAR(255),
    [STATUS] VARCHAR(255),
    [VERSIENUMMER] VARCHAR(255)
);

CREATE TABLE [BEHEERSTATUS_ENUM] (
    [BEHEERSTATUS_ENUM_ID] INT PRIMARY KEY,
    VALUE VARCHAR(255)
);

INSERT INTO [BEHEERSTATUS_ENUM] ([BEHEERSTATUS_ENUM_ID], VALUE) VALUES
    (1, 'BESCHIKBAAR_STELLEN'),
    (2, 'FUNCTIONEEL_ONDERSTEUNEN'),
    (3, 'INTERN_ONTWIKKELD'),
    (4, 'NIET_INGEDEELD'),
    (5, 'TECHNISCH_ONDERSTEUNEN'),
    (6, 'VOLLEDIG_BEHEER');

CREATE TABLE [KP_DTBS_LNKBR_CMDB_ITM] (
    [KP_DTBS_LNKBR_CMDB_ITM_ID] INT PRIMARY KEY,
    [DATABASE_ID] INT,
    [LINKBAAR_CMDB_ITEM_ID] INT
);

CREATE TABLE [GEBRUIKERROL_863_ENUM] (
    [GEBRUIKERROL_863_ENUM_ID] INT PRIMARY KEY,
    VALUE VARCHAR(255)
);

INSERT INTO [GEBRUIKERROL_863_ENUM] ([GEBRUIKERROL_863_ENUM_ID], VALUE) VALUES
    (1, 'EIGENAAR'),
    (2, 'FUNCTIONEEL_BEHEERDER'),
    (3, 'GEBRUIKER'),
    (4, 'GEGEVENSBEHEERDER'),
    (5, 'SUPERUSER');

CREATE TABLE [PACKAGE] (
    [PACKAGE_ID] INT PRIMARY KEY,
    [HEEFT_PACKAGES_CMDB_ITEM__ID] INT,
    [NAAM] VARCHAR(80),
    [PROCES] VARCHAR(255),
    [PROJECT] VARCHAR(255),
    [STATUS] VARCHAR(255),
    [TOELICHTING] NVARCHAR(MAX)
);

CREATE TABLE [KP_SRVR_LNKBR_CMDB_ITM] (
    [KP_SRVR_LNKBR_CMDB_ITM_ID] INT PRIMARY KEY,
    [SERVER_ID] INT,
    [LINKBAAR_CMDB_ITEM_ID] INT
);

CREATE TABLE [KP_APPLCT_MDWRKR] (
    [KP_APPLCT_MDWRKR_ID] INT PRIMARY KEY,
    [APPLICATIE_ID] INT,
    [MEDEWERKER_ID] INT
);

CREATE TABLE [DATABASE] (
    [CMDB_ITEM__ID] INT PRIMARY KEY,
    [ARCHITECTUUR] VARCHAR(255),
    [DATABASEINSTANTIE] VARCHAR(255),
    [DATABASEVERSIE] VARCHAR(255),
    [DBMS] VARCHAR(255),
    [OMSCHRIJVING] VARCHAR(80),
    [OTAP] BIT,
    [SERVER_VAN_DATABASE_CMDB_ITEM__ID] INT,
    [VLAN] VARCHAR(255)
);

CREATE TABLE [DOCUMENT] (
    [DOCUMENT_ID] INT PRIMARY KEY,
    [COCUMENTBESCHRIJVING] VARCHAR(255),
    [DATUMCREATIEDOCUMENT] DATE,
    [DATUMONTVANGSTDOCUMENT] DATE,
    [DATUMVERZENDINGDOCUMENT] DATE,
    [DOCUMENTAUTEUR] VARCHAR(200),
    [DOCUMENTIDENTIFICATIE] VARCHAR(255),
    [DOCUMENTTITEL] VARCHAR(200),
    [VERTROUWELIJKAANDUIDING] VARCHAR(20)
);

CREATE TABLE [LOG] (
    [LOG_ID] INT PRIMARY KEY,
    [HEEFT_CHANGELOG_CMDB_ITEM__ID] INT,
    [KORTEOMSCHRIJVING] VARCHAR(80),
    [OMSCHRIJVING] NVARCHAR(MAX),
    [TIJD] DATETIME2
);

ALTER TABLE [APPLICATIE] ADD CONSTRAINT FK_APPLICATIE_GEN_LINKBAAR_CMDB_ITEM FOREIGN KEY ([CMDB_ITEM__ID]) REFERENCES [LINKBAAR_CMDB_ITEM] ([CMDB_ITEM__ID]);
-- Generalization to LINKBAAR_CMDB_ITEM

ALTER TABLE [DATABASE] ADD CONSTRAINT FK_DATABASE_GEN_LINKBAAR_CMDB_ITEM FOREIGN KEY ([CMDB_ITEM__ID]) REFERENCES [LINKBAAR_CMDB_ITEM] ([CMDB_ITEM__ID]);
-- Generalization to LINKBAAR_CMDB_ITEM

ALTER TABLE [LINKBAAR_CMDB_ITEM] ADD CONSTRAINT FK_LINKBAAR_CMDB_ITEM_GEN_CMDB_ITEM_ FOREIGN KEY ([CMDB_ITEM__ID]) REFERENCES [CMDB_ITEM_] ([CMDB_ITEM__ID]);
-- Generalization to CMDB_ITEM_

ALTER TABLE [SERVER] ADD CONSTRAINT FK_SERVER_GEN_LINKBAAR_CMDB_ITEM FOREIGN KEY ([CMDB_ITEM__ID]) REFERENCES [LINKBAAR_CMDB_ITEM] ([CMDB_ITEM__ID]);
-- Generalization to LINKBAAR_CMDB_ITEM

ALTER TABLE [KP_SRVR_LNKBR_CMDB_ITM] ADD CONSTRAINT FK_KP_SRVR_LNKBR_CMDB_ITM_1 FOREIGN KEY ([SERVER_ID]) REFERENCES [SERVER] ([CMDB_ITEM__ID]);
-- SERVER (many-to-many junction, Connector_ID: 613)

ALTER TABLE [KP_SRVR_LNKBR_CMDB_ITM] ADD CONSTRAINT FK_KP_SRVR_LNKBR_CMDB_ITM_2 FOREIGN KEY ([LINKBAAR_CMDB_ITEM_ID]) REFERENCES [LINKBAAR_CMDB_ITEM] ([CMDB_ITEM__ID]);
-- LINKBAAR_CMDB_ITEM (many-to-many junction, Connector_ID: 613)

ALTER TABLE [KP_LNKBR_CMDB_ITM_CMDB_ITM_] ADD CONSTRAINT FK_KP_LNKBR_CMDB_ITM_CMDB_ITM__1 FOREIGN KEY ([LINKBAAR_CMDB_ITEM_ID]) REFERENCES [LINKBAAR_CMDB_ITEM] ([CMDB_ITEM__ID]);
-- LINKBAAR_CMDB_ITEM (many-to-many junction, Connector_ID: 620)

ALTER TABLE [KP_LNKBR_CMDB_ITM_CMDB_ITM_] ADD CONSTRAINT FK_KP_LNKBR_CMDB_ITM_CMDB_ITM__2 FOREIGN KEY ([CMDB_ITEM__ID]) REFERENCES [CMDB_ITEM_] ([CMDB_ITEM__ID]);
-- CMDB_ITEM_ (many-to-many junction, Connector_ID: 620)

ALTER TABLE [KP_APPLCT_MDWRKR] ADD CONSTRAINT FK_KP_APPLCT_MDWRKR_1 FOREIGN KEY ([APPLICATIE_ID]) REFERENCES [APPLICATIE] ([CMDB_ITEM__ID]);
-- APPLICATIE (many-to-many junction, Connector_ID: 628)

ALTER TABLE [KP_APPLCT_MDWRKR] ADD CONSTRAINT FK_KP_APPLCT_MDWRKR_2 FOREIGN KEY ([MEDEWERKER_ID]) REFERENCES [MEDEWERKER] ([MEDEWERKER_ID]);
-- MEDEWERKER (many-to-many junction, Connector_ID: 628)

ALTER TABLE [KP_DTBS_LNKBR_CMDB_ITM] ADD CONSTRAINT FK_KP_DTBS_LNKBR_CMDB_ITM_1 FOREIGN KEY ([DATABASE_ID]) REFERENCES [DATABASE] ([CMDB_ITEM__ID]);
-- DATABASE (many-to-many junction, Connector_ID: 637)

ALTER TABLE [KP_DTBS_LNKBR_CMDB_ITM] ADD CONSTRAINT FK_KP_DTBS_LNKBR_CMDB_ITM_2 FOREIGN KEY ([LINKBAAR_CMDB_ITEM_ID]) REFERENCES [LINKBAAR_CMDB_ITEM] ([CMDB_ITEM__ID]);
-- LINKBAAR_CMDB_ITEM (many-to-many junction, Connector_ID: 637)

ALTER TABLE [KP_APPLCT_DCMNT] ADD CONSTRAINT FK_KP_APPLCT_DCMNT_1 FOREIGN KEY ([APPLICATIE_ID]) REFERENCES [APPLICATIE] ([CMDB_ITEM__ID]);
-- APPLICATIE (many-to-many junction, Connector_ID: 646)

ALTER TABLE [KP_APPLCT_DCMNT] ADD CONSTRAINT FK_KP_APPLCT_DCMNT_2 FOREIGN KEY ([DOCUMENT_ID]) REFERENCES [DOCUMENT] ([DOCUMENT_ID]);
-- DOCUMENT (many-to-many junction, Connector_ID: 646)

ALTER TABLE [KP_APPLCT_LNKBR_CMDB_ITM] ADD CONSTRAINT FK_KP_APPLCT_LNKBR_CMDB_ITM_1 FOREIGN KEY ([APPLICATIE_ID]) REFERENCES [APPLICATIE] ([CMDB_ITEM__ID]);
-- APPLICATIE (many-to-many junction, Connector_ID: 649)

ALTER TABLE [KP_APPLCT_LNKBR_CMDB_ITM] ADD CONSTRAINT FK_KP_APPLCT_LNKBR_CMDB_ITM_2 FOREIGN KEY ([LINKBAAR_CMDB_ITEM_ID]) REFERENCES [LINKBAAR_CMDB_ITEM] ([CMDB_ITEM__ID]);
-- LINKBAAR_CMDB_ITEM (many-to-many junction, Connector_ID: 649)

ALTER TABLE [SERVER] ADD CONSTRAINT FK_SERVER_HEEFT_LEVERANCIER FOREIGN KEY ([HEEFT_LEVERANCIER_LEVERANCIER_ID]) REFERENCES [LEVERANCIER] ([LEVERANCIER_ID]);
-- HEEFT_LEVERANCIER (0..* to 0..1, Connector_ID: 612)

ALTER TABLE [NOTITIE] ADD CONSTRAINT FK_NOTITIE_AUTEUR FOREIGN KEY ([AUTEUR_MEDEWERKER_ID]) REFERENCES [MEDEWERKER] ([MEDEWERKER_ID]);
-- AUTEUR (0..* to 1, Connector_ID: 618)

ALTER TABLE [KOPPELING] ADD CONSTRAINT FK_KOPPELING_LINK_VAN FOREIGN KEY ([LINK_VAN_CMDB_ITEM__ID]) REFERENCES [CMDB_ITEM_] ([CMDB_ITEM__ID]);
-- LINK_VAN (1 to 0..*, Connector_ID: 622)

ALTER TABLE [KOPPELING] ADD CONSTRAINT FK_KOPPELING_LINK_NAAR FOREIGN KEY ([LINK_NAAR_CMDB_ITEM__ID]) REFERENCES [CMDB_ITEM_] ([CMDB_ITEM__ID]);
-- LINK_NAAR (0..* to 1, Connector_ID: 623)

ALTER TABLE [DATABASE] ADD CONSTRAINT FK_DATABASE_SERVER_VAN_DATABASE FOREIGN KEY ([SERVER_VAN_DATABASE_CMDB_ITEM__ID]) REFERENCES [CMDB_ITEM_] ([CMDB_ITEM__ID]);
-- SERVER_VAN_DATABASE (0..* to 1, Connector_ID: 636)

ALTER TABLE [LOG] ADD CONSTRAINT FK_LOG_HEEFT_CHANGELOG FOREIGN KEY ([HEEFT_CHANGELOG_CMDB_ITEM__ID]) REFERENCES [CMDB_ITEM_] ([CMDB_ITEM__ID]);
-- HEEFT_CHANGELOG (1 to 0..*, Connector_ID: 638)

ALTER TABLE [PACKAGE] ADD CONSTRAINT FK_PACKAGE_HEEFT_PACKAGES FOREIGN KEY ([HEEFT_PACKAGES_CMDB_ITEM__ID]) REFERENCES [CMDB_ITEM_] ([CMDB_ITEM__ID]);
-- HEEFT_PACKAGES (1 to 0..*, Connector_ID: 642)

ALTER TABLE [NOTITIE] ADD CONSTRAINT FK_NOTITIE_HEEFT_NOTITIES FOREIGN KEY ([HEEFT_NOTITIES_CMDB_ITEM__ID]) REFERENCES [CMDB_ITEM_] ([CMDB_ITEM__ID]);
-- HEEFT_NOTITIES (1 to 0..*, Connector_ID: 643)

ALTER TABLE [APPLICATIE] ADD CONSTRAINT FK_APPLICATIE_HEEFT_LEVERANCIER FOREIGN KEY ([HEEFT_LEVERANCIER_LEVERANCIER_ID]) REFERENCES [LEVERANCIER] ([LEVERANCIER_ID]);
-- HEEFT_LEVERANCIER (0..* to 0..1, Connector_ID: 647)

ALTER TABLE [VERSIE] ADD CONSTRAINT FK_VERSIE_HEEFT_VERSIES FOREIGN KEY ([HEEFT_VERSIES_CMDB_ITEM__ID]) REFERENCES [CMDB_ITEM_] ([CMDB_ITEM__ID]);
-- HEEFT_VERSIES (1 to 1..*, Connector_ID: 648)

ALTER TABLE [MEDEWERKER] ADD CONSTRAINT FK_MEDEWERKER_GELEVERD_VIA FOREIGN KEY ([GELEVERD_VIA_LEVERANCIER_ID]) REFERENCES [LEVERANCIER] ([LEVERANCIER_ID]);
-- GELEVERD_VIA (0..* to 0..1, Connector_ID: 2194)

ALTER TABLE [APPLICATIE] ADD CONSTRAINT FK_APPLICATIE_CATEGORIE_ENUM_ID FOREIGN KEY ([CATEGORIE_ENUM_ID_APPLICATIECATEGORIE_ENUM_ID]) REFERENCES [APPLICATIECATEGORIE_ENUM] ([APPLICATIECATEGORIE_ENUM_ID]);
-- CATEGORIE_ENUM_ID (1 to 1, Connector_ID: enum_ref)

ALTER TABLE [GEBRUIKERROL_835] ADD CONSTRAINT FK_GEBRUIKERROL_835_ROL_ENUM_ID FOREIGN KEY ([ROL_ENUM_ID_GEBRUIKERROL_863_ENUM_ID]) REFERENCES [GEBRUIKERROL_863_ENUM] ([GEBRUIKERROL_863_ENUM_ID]);
-- ROL_ENUM_ID (1 to 1, Connector_ID: enum_ref)

ALTER TABLE [SERVER] ADD CONSTRAINT FK_SERVER_SERVERTYPE_ENUM_ID FOREIGN KEY ([SERVERTYPE_ENUM_ID_SERVERTYPES_ENUM_ID]) REFERENCES [SERVERTYPES_ENUM] ([SERVERTYPES_ENUM_ID]);
-- SERVERTYPE_ENUM_ID (1 to 1, Connector_ID: enum_ref)

