CREATE TABLE [LOGBOEK] (
    [LOGBOEK_ID] INT PRIMARY KEY
);

CREATE TABLE [NATUURLIJKPERSOON] (
    [NATUURLIJKPERSOON_ID] INT PRIMARY KEY,
    [AANDUIDINGNAAMGEBRUIK] VARCHAR(50),
    [AANHEFAANSCHRIJVING] VARCHAR(50),
    [ACADEMISCHETITEL] VARCHAR(80),
    [ACHTERNAAM] VARCHAR(100),
    [ADELLIJKETITELOFPREDIKAAT] VARCHAR(255),
    [ANUMMER] VARCHAR(20),
    [BIJZONDERNEDERLANDERSCHAP] VARCHAR(50),
    [BURGERSERVICENUMMER] VARCHAR(255),
    [DATUMGEBOORTE] DATE,
    [DATUMOVERLIJDEN] DATE,
    [GEBOORTELAND] VARCHAR(255),
    [GEBOORTEPLAATS] VARCHAR(200),
    [GESLACHTSAANDUIDING] VARCHAR(255),
    [GESLACHTSNAAM] VARCHAR(200),
    [GESLACHTSNAAMAANSCHRIJVING] VARCHAR(200),
    [HANDLICHTING] VARCHAR(50),
    [INDICATIEAFSCHERMINGPERSOONSGEGEVENS] BIT,
    [INDICATIEOVERLEDEN] BIT,
    [LANDOVERLIJDEN] VARCHAR(255),
    [NATIONALITEIT] VARCHAR(100),
    [OVERLIJDENSPLAATS] VARCHAR(200),
    [VOORLETTERSAANSCHRIJVING] VARCHAR(20),
    [VOORNAMEN] VARCHAR(200),
    [VOORNAMENAANSCHRIJVING] VARCHAR(200),
    [VOORVOEGSELGESLACHTSNAAM] VARCHAR(80)
);

CREATE TABLE [KWALITEITSCATALOGUS_OPENBARE_RUIMTE] (
    [KWALITEITSCATALOGUS_OPENBARE_RUIMTE_ID] INT PRIMARY KEY
);

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

CREATE TABLE [CROW_KWALITEITSNIVEAUS_ENUM] (
    [CROW_KWALITEITSNIVEAUS_ENUM_ID] INT PRIMARY KEY,
    VALUE VARCHAR(255)
);

INSERT INTO [CROW_KWALITEITSNIVEAUS_ENUM] ([CROW_KWALITEITSNIVEAUS_ENUM_ID], VALUE) VALUES
    (1, 'NIVEAU_A'),
    (2, 'NIVEAU_A_'),
    (3, 'NIVEAU_B'),
    (4, 'NIVEAU_C'),
    (5, 'NIVEAU_D');

CREATE TABLE [BEHEEROBJECT] (
    [BEHEEROBJECT_ID] INT PRIMARY KEY,
    [AANGEMAAKTDOOR] VARCHAR(200),
    [BEGINGARANTIEPERIODE] VARCHAR(255),
    [BEHEERGEBIED] VARCHAR(255),
    [BEHEEROBJECTBEHEERVAK] VARCHAR(255),
    [BEHEEROBJECTGEBRUIKSFUNCTIE] VARCHAR(255),
    [BEHEEROBJECTMEMO] VARCHAR(255),
    [BESCHERMDEFLORAENFAUNA] BIT,
    [BUURT] VARCHAR(80),
    [CONVERSIEID] VARCHAR(50),
    [DATUMMUTATIE] DATE,
    [DATUMOPLEVERING] DATE,
    [DATUMPUBLICATIELV] DATE,
    [DATUMVERWIJDERING] DATE,
    [EINDEGARANTIEPERIODE] DATE,
    [GEBIEDSTYPE] VARCHAR(255),
    [GEMEENTE] VARCHAR(80),
    [GEOMETRIE] GEOMETRY,
    [GEWIJZIGDDOOR] VARCHAR(200),
    [GRONDSOORT] VARCHAR(255),
    [GRONDSOORTPLUS] VARCHAR(255),
    [HEEFT_LOGBOEK_ID] INT,
    [IDENTIFICATIEIMBOR] VARCHAR(255),
    [IDENTIFICATIEIMGEO] VARCHAR(255),
    [JAARVANAANLEG] VARCHAR(255),
    [OBJECTBEGINTIJD] DATETIME2,
    [OBJECTEINDTIJD] DATETIME2,
    [ONDERHOUDSPLICHTIGE] VARCHAR(255),
    [OPENBARERUIMTE] VARCHAR(80),
    [POSTCODE] VARCHAR(255),
    [RELATIEVEHOOGTELIGGING] VARCHAR(255),
    [STADSDEEL] VARCHAR(80),
    [STATUS] VARCHAR(255),
    [THEORETISCHEINDEJAAR] DATE,
    [TIJDSTIPREGISTRATIE] DATETIME2,
    [TYPEBEHEERDER] VARCHAR(255),
    [TYPEBEHEERDERPLUS] VARCHAR(255),
    [TYPEEIGENAAR] VARCHAR(255),
    [TYPEEIGENAARPLUS] VARCHAR(255),
    [TYPELIGGING] VARCHAR(255),
    [WATERSCHAP] VARCHAR(80),
    [WIJK] VARCHAR(80),
    [WOONPLAATS] VARCHAR(80),
    [ZETTINGSGEVOELIGHEID] VARCHAR(255),
    [ZETTINGSGEVOELIGHEIDPLUS] VARCHAR(255)
);

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 [SCHOUWRONDE] (
    [SCHOUWRONDE_ID] INT PRIMARY KEY,
    [VOERT_UIT_MEDEWERKER_ID] INT
);

CREATE TABLE [MELDING] (
    [MELDING_ID] INT PRIMARY KEY,
    [ADVIES] NVARCHAR(MAX),
    [BEVAT_LOGBOEK_ID] INT,
    [CATEGORIE] VARCHAR(80),
    [CONSTATERING] NVARCHAR(MAX),
    [DATUMADVIES] DATE,
    [DATUMMELDING] DATE,
    [DATUMUITVOERING] DATE,
    [FOTO] VARCHAR(255),
    [HEEFT_SCHOUWRONDE_ID] INT,
    [LOCATIE] VARCHAR(255),
    [MELDER_MEDEWERKER_ID] INT,
    [MELDER_NATUURLIJKPERSOON_ID] INT,
    [OPMERKINGEN] NVARCHAR(MAX),
    [STATUS] VARCHAR(255),
    [UITGEVOERD] BIT,
    [UITVOERDER_LEVERANCIER_ID] INT,
    [UITVOERDER_MEDEWERKER_ID] INT
);

CREATE TABLE [INSPECTIE] (
    [MELDING_ID] INT PRIMARY KEY
);

CREATE TABLE [CROW_MELDING] (
    [MELDING_ID] INT PRIMARY KEY,
    [CONFORM_KWALITEITSCATALOGUS_OPENBARE_RUIMTE_ID] INT,
    [KWALITEITSNIVEAU_ENUM_ID] INT
);

CREATE TABLE [KP_MLDNG_BHRBJCT] (
    [KP_MLDNG_BHRBJCT_ID] INT PRIMARY KEY,
    [MELDING_ID] INT,
    [BEHEEROBJECT_ID] INT
);

CREATE TABLE [MELDINGONGEVAL] (
    [MELDING_ID] INT PRIMARY KEY
);

CREATE TABLE [ACTIE] (
    [MELDING_ID] INT PRIMARY KEY
);

CREATE TABLE [STORING] (
    [MELDING_ID] INT PRIMARY KEY
);

CREATE TABLE [KP_INSPCT_MLDNG] (
    [KP_INSPCT_MLDNG_ID] INT PRIMARY KEY,
    [INSPECTIE_ID] INT,
    [MELDING_ID] INT
);

CREATE TABLE [KP_CRW_MLDNG_MLDNG] (
    [KP_CRW_MLDNG_MLDNG_ID] INT PRIMARY KEY,
    [CROW_MELDING_ID] INT,
    [MELDING_ID] INT
);

CREATE TABLE [KP_MLDNGNGVL_MLDNG] (
    [KP_MLDNGNGVL_MLDNG_ID] INT PRIMARY KEY,
    [MELDINGONGEVAL_ID] INT,
    [MELDING_ID] INT
);

CREATE TABLE [KP_ACT_MLDNG] (
    [KP_ACT_MLDNG_ID] INT PRIMARY KEY,
    [ACTIE_ID] INT,
    [MELDING_ID] INT
);

CREATE TABLE [KP_STRNG_MLDNG] (
    [KP_STRNG_MLDNG_ID] INT PRIMARY KEY,
    [STORING_ID] INT,
    [MELDING_ID] INT
);

ALTER TABLE [ACTIE] ADD CONSTRAINT FK_ACTIE_GEN_MELDING FOREIGN KEY ([MELDING_ID]) REFERENCES [MELDING] ([MELDING_ID]);
-- Generalization to MELDING

ALTER TABLE [CROW_MELDING] ADD CONSTRAINT FK_CROW_MELDING_GEN_MELDING FOREIGN KEY ([MELDING_ID]) REFERENCES [MELDING] ([MELDING_ID]);
-- Generalization to MELDING

ALTER TABLE [INSPECTIE] ADD CONSTRAINT FK_INSPECTIE_GEN_MELDING FOREIGN KEY ([MELDING_ID]) REFERENCES [MELDING] ([MELDING_ID]);
-- Generalization to MELDING

ALTER TABLE [MELDINGONGEVAL] ADD CONSTRAINT FK_MELDINGONGEVAL_GEN_MELDING FOREIGN KEY ([MELDING_ID]) REFERENCES [MELDING] ([MELDING_ID]);
-- Generalization to MELDING

ALTER TABLE [STORING] ADD CONSTRAINT FK_STORING_GEN_MELDING FOREIGN KEY ([MELDING_ID]) REFERENCES [MELDING] ([MELDING_ID]);
-- Generalization to MELDING

ALTER TABLE [KP_STRNG_MLDNG] ADD CONSTRAINT FK_KP_STRNG_MLDNG_1 FOREIGN KEY ([STORING_ID]) REFERENCES [STORING] ([MELDING_ID]);
-- STORING (many-to-many junction, Connector_ID: 722)

ALTER TABLE [KP_STRNG_MLDNG] ADD CONSTRAINT FK_KP_STRNG_MLDNG_2 FOREIGN KEY ([MELDING_ID]) REFERENCES [MELDING] ([MELDING_ID]);
-- MELDING (many-to-many junction, Connector_ID: 722)

ALTER TABLE [KP_MLDNGNGVL_MLDNG] ADD CONSTRAINT FK_KP_MLDNGNGVL_MLDNG_1 FOREIGN KEY ([MELDINGONGEVAL_ID]) REFERENCES [MELDINGONGEVAL] ([MELDING_ID]);
-- MELDINGONGEVAL (many-to-many junction, Connector_ID: 732)

ALTER TABLE [KP_MLDNGNGVL_MLDNG] ADD CONSTRAINT FK_KP_MLDNGNGVL_MLDNG_2 FOREIGN KEY ([MELDING_ID]) REFERENCES [MELDING] ([MELDING_ID]);
-- MELDING (many-to-many junction, Connector_ID: 732)

ALTER TABLE [KP_INSPCT_MLDNG] ADD CONSTRAINT FK_KP_INSPCT_MLDNG_1 FOREIGN KEY ([INSPECTIE_ID]) REFERENCES [INSPECTIE] ([MELDING_ID]);
-- INSPECTIE (many-to-many junction, Connector_ID: 742)

ALTER TABLE [KP_INSPCT_MLDNG] ADD CONSTRAINT FK_KP_INSPCT_MLDNG_2 FOREIGN KEY ([MELDING_ID]) REFERENCES [MELDING] ([MELDING_ID]);
-- MELDING (many-to-many junction, Connector_ID: 742)

ALTER TABLE [KP_CRW_MLDNG_MLDNG] ADD CONSTRAINT FK_KP_CRW_MLDNG_MLDNG_1 FOREIGN KEY ([CROW_MELDING_ID]) REFERENCES [CROW_MELDING] ([MELDING_ID]);
-- CROW_MELDING (many-to-many junction, Connector_ID: 745)

ALTER TABLE [KP_CRW_MLDNG_MLDNG] ADD CONSTRAINT FK_KP_CRW_MLDNG_MLDNG_2 FOREIGN KEY ([MELDING_ID]) REFERENCES [MELDING] ([MELDING_ID]);
-- MELDING (many-to-many junction, Connector_ID: 745)

ALTER TABLE [KP_ACT_MLDNG] ADD CONSTRAINT FK_KP_ACT_MLDNG_1 FOREIGN KEY ([ACTIE_ID]) REFERENCES [ACTIE] ([MELDING_ID]);
-- ACTIE (many-to-many junction, Connector_ID: 751)

ALTER TABLE [KP_ACT_MLDNG] ADD CONSTRAINT FK_KP_ACT_MLDNG_2 FOREIGN KEY ([MELDING_ID]) REFERENCES [MELDING] ([MELDING_ID]);
-- MELDING (many-to-many junction, Connector_ID: 751)

ALTER TABLE [KP_MLDNG_BHRBJCT] ADD CONSTRAINT FK_KP_MLDNG_BHRBJCT_1 FOREIGN KEY ([MELDING_ID]) REFERENCES [MELDING] ([MELDING_ID]);
-- MELDING (many-to-many junction, Connector_ID: 806)

ALTER TABLE [KP_MLDNG_BHRBJCT] ADD CONSTRAINT FK_KP_MLDNG_BHRBJCT_2 FOREIGN KEY ([BEHEEROBJECT_ID]) REFERENCES [BEHEEROBJECT] ([BEHEEROBJECT_ID]);
-- BEHEEROBJECT (many-to-many junction, Connector_ID: 806)

ALTER TABLE [MELDING] ADD CONSTRAINT FK_MELDING_HEEFT FOREIGN KEY ([HEEFT_SCHOUWRONDE_ID]) REFERENCES [SCHOUWRONDE] ([SCHOUWRONDE_ID]);
-- HEEFT (0..1 to 0..*, Connector_ID: 733)

ALTER TABLE [MELDING] ADD CONSTRAINT FK_MELDING_UITVOERDER FOREIGN KEY ([UITVOERDER_MEDEWERKER_ID]) REFERENCES [MEDEWERKER] ([MEDEWERKER_ID]);
-- UITVOERDER (0..* to 0..1, Connector_ID: 734)

ALTER TABLE [MELDING] ADD CONSTRAINT FK_MELDING_UITVOERDER_2 FOREIGN KEY ([UITVOERDER_LEVERANCIER_ID]) REFERENCES [LEVERANCIER] ([LEVERANCIER_ID]);
-- UITVOERDER (0..* to 0..1, Connector_ID: 735)

ALTER TABLE [MELDING] ADD CONSTRAINT FK_MELDING_MELDER FOREIGN KEY ([MELDER_NATUURLIJKPERSOON_ID]) REFERENCES [NATUURLIJKPERSOON] ([NATUURLIJKPERSOON_ID]);
-- MELDER (0..* to 0..1, Connector_ID: 736)

ALTER TABLE [MELDING] ADD CONSTRAINT FK_MELDING_MELDER_2 FOREIGN KEY ([MELDER_MEDEWERKER_ID]) REFERENCES [MEDEWERKER] ([MEDEWERKER_ID]);
-- MELDER (0..* to 0..1, Connector_ID: 737)

ALTER TABLE [MELDING] ADD CONSTRAINT FK_MELDING_BEVAT FOREIGN KEY ([BEVAT_LOGBOEK_ID]) REFERENCES [LOGBOEK] ([LOGBOEK_ID]);
-- BEVAT (1 to 0..*, Connector_ID: 738)

ALTER TABLE [CROW_MELDING] ADD CONSTRAINT FK_CROW_MELDING_CONFORM FOREIGN KEY ([CONFORM_KWALITEITSCATALOGUS_OPENBARE_RUIMTE_ID]) REFERENCES [KWALITEITSCATALOGUS_OPENBARE_RUIMTE] ([KWALITEITSCATALOGUS_OPENBARE_RUIMTE_ID]);
-- CONFORM (0..* to 1, Connector_ID: 744)

ALTER TABLE [BEHEEROBJECT] ADD CONSTRAINT FK_BEHEEROBJECT_HEEFT FOREIGN KEY ([HEEFT_LOGBOEK_ID]) REFERENCES [LOGBOEK] ([LOGBOEK_ID]);
-- HEEFT (1 to 0..1, Connector_ID: 807)

ALTER TABLE [SCHOUWRONDE] ADD CONSTRAINT FK_SCHOUWRONDE_VOERT_UIT FOREIGN KEY ([VOERT_UIT_MEDEWERKER_ID]) REFERENCES [MEDEWERKER] ([MEDEWERKER_ID]);
-- VOERT_UIT (1 to 0..*, Connector_ID: 2193)

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 [CROW_MELDING] ADD CONSTRAINT FK_CROW_MELDING_KWALITEITSNIVEAU_ENUM_ID FOREIGN KEY ([KWALITEITSNIVEAU_ENUM_ID_CROW_KWALITEITSNIVEAUS_ENUM_ID]) REFERENCES [CROW_KWALITEITSNIVEAUS_ENUM] ([CROW_KWALITEITSNIVEAUS_ENUM_ID]);
-- KWALITEITSNIVEAU_ENUM_ID (1 to 1, Connector_ID: enum_ref)

