CREATE TABLE [BOA] (
    [BOA_ID] INT PRIMARY KEY
);

CREATE TABLE [BEOORDELINGSOORT_ENUM] (
    [BEOORDELINGSOORT_ENUM_ID] INT PRIMARY KEY,
    VALUE VARCHAR(255)
);

INSERT INTO [BEOORDELINGSOORT_ENUM] ([BEOORDELINGSOORT_ENUM_ID], VALUE) VALUES
    (1, 'NIET_OKE'),
    (2, 'NIET_RELEVANT'),
    (3, 'OKE');

CREATE TABLE [OBJECT] (
    [OBJECT_ID] INT PRIMARY KEY,
    [ADRESBINNENLAND] VARCHAR(255),
    [ADRESBUITENLAND] VARCHAR(200),
    [DOMEIN] VARCHAR(200),
    [GEOMETRIE] GEOMETRY,
    [IDENTIFICATIE] VARCHAR(50),
    [INDICATIERISICO] BIT,
    [KADASTRALEAANDUIDING] VARCHAR(255),
    [NAAM] VARCHAR(200),
    [OBJECTTYPE] VARCHAR(255),
    [TOELICHTING] VARCHAR(255)
);

CREATE TABLE [AANVRAAGOFMELDING] (
    [AANVRAAGOFMELDING_ID] INT PRIMARY KEY,
    [AFGEHANDELD] BIT,
    [CATEGORIE] VARCHAR(200),
    [CATEGORIECODE] VARCHAR(80),
    [DATUMAANMAAK] DATE,
    [DATUMAFHANDELING] DATE,
    [DATUMBEGINSTATUS] DATE,
    [DATUMEINDESTATUS] DATE,
    [HOOFDCATEGORIE] VARCHAR(80),
    [HOOFDCATEGORIECODE] VARCHAR(80),
    [IDENTIFICATIE] VARCHAR(255),
    [KANAAL] VARCHAR(80),
    [ONDERWERP] VARCHAR(200),
    [ONDERWERPCODE] VARCHAR(80),
    [SOORT] VARCHAR(80),
    [STATUS] VARCHAR(80),
    [STATUSCODE] VARCHAR(80),
    [STATUSVOLGORDE] VARCHAR(80),
    [SUBCATEGORIE] VARCHAR(200),
    [SUBCATEGORIECODE] VARCHAR(80)
);

CREATE TABLE [FOTO] (
    [FOTO_ID] INT PRIMARY KEY,
    [BESTANDSGROOTTE] INT,
    [BESTANDSNAAM] VARCHAR(200),
    [BESTANDSTYPE] VARCHAR(80),
    [DATUMTIJD] DATETIME2,
    [HEEFT_AANVRAAGOFMELDING_ID] INT,
    [LOCATIE] VARCHAR(255),
    [PIXELSX] INT,
    [PIXELSY] INT
);

CREATE TABLE [FIETSREGISTRATIE] (
    [AANVRAAGOFMELDING_ID] INT PRIMARY KEY,
    [GELABELD] BIT,
    [VERWIJDERD] BIT
);

CREATE TABLE [COMBIBON] (
    [AANVRAAGOFMELDING_ID] INT PRIMARY KEY,
    [SANCTIE] VARCHAR(255)
);

CREATE TABLE [VTH_MELDING] (
    [AANVRAAGOFMELDING_ID] INT PRIMARY KEY,
    [ACTIVITEIT] VARCHAR(80),
    [BEOORDELING_ENUM_ID] INT,
    [DATUMSEPONERING] DATE,
    [DATUMTIJDTOT] DATETIME2,
    [GESEPONEERD] BIT,
    [LOCATIE] VARCHAR(255),
    [ORGANISATIEONDERDEEL] VARCHAR(80),
    [OVERTREDINGSCODE] VARCHAR(20),
    [OVERTREDINGSGROEP] VARCHAR(200),
    [REFERENTIENUMMER] VARCHAR(80),
    [RESULTAAT] VARCHAR(80),
    [SOORTVTHMELDING] VARCHAR(80),
    [STATUS] VARCHAR(255),
    [STRAATNAAM] VARCHAR(255),
    [TAAKTYPE] VARCHAR(80),
    [VERBALISANT_BOA_ID] INT,
    [ZAAKNUMMER] VARCHAR(20)
);

CREATE TABLE [WAARNEMING] (
    [AANVRAAGOFMELDING_ID] INT PRIMARY KEY
);

CREATE TABLE [KP_VTH_MLDNG_ANVRGFMLDNG] (
    [KP_VTH_MLDNG_ANVRGFMLDNG_ID] INT PRIMARY KEY,
    [VTH_MELDING_ID] INT,
    [AANVRAAGOFMELDING_ID] INT
);

CREATE TABLE [KP_FTSRGSTRT_VTH_MLDNG] (
    [KP_FTSRGSTRT_VTH_MLDNG_ID] INT PRIMARY KEY,
    [FIETSREGISTRATIE_ID] INT,
    [VTH_MELDING_ID] INT
);

CREATE TABLE [KP_VTH_MLDNG_OBJCT] (
    [KP_VTH_MLDNG_OBJCT_ID] INT PRIMARY KEY,
    [VTH_MELDING_ID] INT,
    [OBJECT_ID] INT
);

CREATE TABLE [KP_CMBBN_VTH_MLDNG] (
    [KP_CMBBN_VTH_MLDNG_ID] INT PRIMARY KEY,
    [COMBIBON_ID] INT,
    [VTH_MELDING_ID] INT
);

CREATE TABLE [KP_WRNMNG_VTH_MLDNG] (
    [KP_WRNMNG_VTH_MLDNG_ID] INT PRIMARY KEY,
    [WAARNEMING_ID] INT,
    [VTH_MELDING_ID] INT
);

ALTER TABLE [COMBIBON] ADD CONSTRAINT FK_COMBIBON_GEN_VTH_MELDING FOREIGN KEY ([AANVRAAGOFMELDING_ID]) REFERENCES [VTH_MELDING] ([AANVRAAGOFMELDING_ID]);
-- Generalization to VTH_MELDING

ALTER TABLE [FIETSREGISTRATIE] ADD CONSTRAINT FK_FIETSREGISTRATIE_GEN_VTH_MELDING FOREIGN KEY ([AANVRAAGOFMELDING_ID]) REFERENCES [VTH_MELDING] ([AANVRAAGOFMELDING_ID]);
-- Generalization to VTH_MELDING

ALTER TABLE [VTH_MELDING] ADD CONSTRAINT FK_VTH_MELDING_GEN_AANVRAAGOFMELDING FOREIGN KEY ([AANVRAAGOFMELDING_ID]) REFERENCES [AANVRAAGOFMELDING] ([AANVRAAGOFMELDING_ID]);
-- Generalization to AANVRAAGOFMELDING

ALTER TABLE [WAARNEMING] ADD CONSTRAINT FK_WAARNEMING_GEN_VTH_MELDING FOREIGN KEY ([AANVRAAGOFMELDING_ID]) REFERENCES [VTH_MELDING] ([AANVRAAGOFMELDING_ID]);
-- Generalization to VTH_MELDING

ALTER TABLE [KP_WRNMNG_VTH_MLDNG] ADD CONSTRAINT FK_KP_WRNMNG_VTH_MLDNG_1 FOREIGN KEY ([WAARNEMING_ID]) REFERENCES [WAARNEMING] ([AANVRAAGOFMELDING_ID]);
-- WAARNEMING (many-to-many junction, Connector_ID: 1295)

ALTER TABLE [KP_WRNMNG_VTH_MLDNG] ADD CONSTRAINT FK_KP_WRNMNG_VTH_MLDNG_2 FOREIGN KEY ([VTH_MELDING_ID]) REFERENCES [VTH_MELDING] ([AANVRAAGOFMELDING_ID]);
-- VTH_MELDING (many-to-many junction, Connector_ID: 1295)

ALTER TABLE [KP_VTH_MLDNG_OBJCT] ADD CONSTRAINT FK_KP_VTH_MLDNG_OBJCT_1 FOREIGN KEY ([VTH_MELDING_ID]) REFERENCES [VTH_MELDING] ([AANVRAAGOFMELDING_ID]);
-- VTH_MELDING (many-to-many junction, Connector_ID: 1297)

ALTER TABLE [KP_VTH_MLDNG_OBJCT] ADD CONSTRAINT FK_KP_VTH_MLDNG_OBJCT_2 FOREIGN KEY ([OBJECT_ID]) REFERENCES [OBJECT] ([OBJECT_ID]);
-- OBJECT (many-to-many junction, Connector_ID: 1297)

ALTER TABLE [KP_VTH_MLDNG_ANVRGFMLDNG] ADD CONSTRAINT FK_KP_VTH_MLDNG_ANVRGFMLDNG_1 FOREIGN KEY ([VTH_MELDING_ID]) REFERENCES [VTH_MELDING] ([AANVRAAGOFMELDING_ID]);
-- VTH_MELDING (many-to-many junction, Connector_ID: 1299)

ALTER TABLE [KP_VTH_MLDNG_ANVRGFMLDNG] ADD CONSTRAINT FK_KP_VTH_MLDNG_ANVRGFMLDNG_2 FOREIGN KEY ([AANVRAAGOFMELDING_ID]) REFERENCES [AANVRAAGOFMELDING] ([AANVRAAGOFMELDING_ID]);
-- AANVRAAGOFMELDING (many-to-many junction, Connector_ID: 1299)

ALTER TABLE [KP_FTSRGSTRT_VTH_MLDNG] ADD CONSTRAINT FK_KP_FTSRGSTRT_VTH_MLDNG_1 FOREIGN KEY ([FIETSREGISTRATIE_ID]) REFERENCES [FIETSREGISTRATIE] ([AANVRAAGOFMELDING_ID]);
-- FIETSREGISTRATIE (many-to-many junction, Connector_ID: 1324)

ALTER TABLE [KP_FTSRGSTRT_VTH_MLDNG] ADD CONSTRAINT FK_KP_FTSRGSTRT_VTH_MLDNG_2 FOREIGN KEY ([VTH_MELDING_ID]) REFERENCES [VTH_MELDING] ([AANVRAAGOFMELDING_ID]);
-- VTH_MELDING (many-to-many junction, Connector_ID: 1324)

ALTER TABLE [KP_CMBBN_VTH_MLDNG] ADD CONSTRAINT FK_KP_CMBBN_VTH_MLDNG_1 FOREIGN KEY ([COMBIBON_ID]) REFERENCES [COMBIBON] ([AANVRAAGOFMELDING_ID]);
-- COMBIBON (many-to-many junction, Connector_ID: 1325)

ALTER TABLE [KP_CMBBN_VTH_MLDNG] ADD CONSTRAINT FK_KP_CMBBN_VTH_MLDNG_2 FOREIGN KEY ([VTH_MELDING_ID]) REFERENCES [VTH_MELDING] ([AANVRAAGOFMELDING_ID]);
-- VTH_MELDING (many-to-many junction, Connector_ID: 1325)

ALTER TABLE [FOTO] ADD CONSTRAINT FK_FOTO_HEEFT FOREIGN KEY ([HEEFT_AANVRAAGOFMELDING_ID]) REFERENCES [AANVRAAGOFMELDING] ([AANVRAAGOFMELDING_ID]);
-- HEEFT (0..1 to 0..*, Connector_ID: 1298)

ALTER TABLE [VTH_MELDING] ADD CONSTRAINT FK_VTH_MELDING_VERBALISANT FOREIGN KEY ([VERBALISANT_BOA_ID]) REFERENCES [BOA] ([BOA_ID]);
-- VERBALISANT (1 to 0..*, Connector_ID: 1326)

ALTER TABLE [VTH_MELDING] ADD CONSTRAINT FK_VTH_MELDING_BEOORDELING_ENUM_ID FOREIGN KEY ([BEOORDELING_ENUM_ID_BEOORDELINGSOORT_ENUM_ID]) REFERENCES [BEOORDELINGSOORT_ENUM] ([BEOORDELINGSOORT_ENUM_ID]);
-- BEOORDELING_ENUM_ID (1 to 1, Connector_ID: enum_ref)

