CREATE TABLE [RECHTSPERSOON] (
    [RECHTSPERSOON_ID] INT PRIMARY KEY,
    [ADRESBINNENLAND] VARCHAR(255),
    [ADRESBUITENLAND] VARCHAR(255),
    [ADRESCORRESPONDENTIE] VARCHAR(100),
    [EMAILADRES] VARCHAR(80),
    [FAXNUMMER] VARCHAR(20),
    [IDENTIFICATIE] VARCHAR(255),
    [KVKNUMMER] VARCHAR(20),
    [NAAM] VARCHAR(200),
    [RECHTSVORM] VARCHAR(100),
    [REKENINGNUMMER] VARCHAR(255),
    [TELEFOONNUMMER] VARCHAR(20)
);

CREATE TABLE [RAADSCOMMISSIE] (
    [RAADSCOMMISSIE_ID] INT PRIMARY KEY,
    [NAAM] VARCHAR(255)
);

CREATE TABLE [STEMMINGSRESULTAATTYPE_ENUM] (
    [STEMMINGSRESULTAATTYPE_ENUM_ID] INT PRIMARY KEY,
    VALUE VARCHAR(255)
);

INSERT INTO [STEMMINGSRESULTAATTYPE_ENUM] ([STEMMINGSRESULTAATTYPE_ENUM_ID], VALUE) VALUES
    (1, 'GELIJK'),
    (2, 'TEGEN'),
    (3, 'VOOR');

CREATE TABLE [STEMMINGSTYPE_ENUM] (
    [STEMMINGSTYPE_ENUM_ID] INT PRIMARY KEY,
    VALUE VARCHAR(255)
);

INSERT INTO [STEMMINGSTYPE_ENUM] ([STEMMINGSTYPE_ENUM_ID], VALUE) VALUES
    (1, 'HOOFDELIJK'),
    (2, 'REGULIER'),
    (3, 'SCHRIFTELIJK');

CREATE TABLE [RAADSSTUK] (
    [RAADSSTUK_ID] INT PRIMARY KEY,
    [BESLOTEN] BIT,
    [DATUMEXPIRATIE] DATE,
    [DATUMPUBLICATIE] DATE,
    [DATUMREGISTRATIE] DATE,
    [TYPERAADSSTUK] VARCHAR(80)
);

CREATE TABLE [DEELNEMERSROL_ENUM] (
    [DEELNEMERSROL_ENUM_ID] INT PRIMARY KEY,
    VALUE VARCHAR(255)
);

INSERT INTO [DEELNEMERSROL_ENUM] ([DEELNEMERSROL_ENUM_ID], VALUE) VALUES
    (1, 'INSPREKER'),
    (2, 'OVERIG'),
    (3, 'PORTEFEUILLEHOUDER'),
    (4, 'RAADSLID'),
    (5, 'STATENLID'),
    (6, 'VICE_VOORZITTER'),
    (7, 'VOORZITTER');

CREATE TABLE [INGEZETENE] (
    [INGEZETENE_ID] INT PRIMARY KEY,
    [AANDUIDINGEUROPEESKIESRECHT] BIT,
    [AANDUIDINGUITGESLOTENKIESRECHT] BIT,
    [DATUMVERKRIJGINGVERBLIJFSTITEL] DATE,
    [DATUMVERLIESVERBLIJFSTITEL] DATE,
    [INDICATIEBLOKKERING] VARCHAR(255),
    [INDICATIECURATELEREGISTER] VARCHAR(255),
    [INDICATIEGEZAGMINDERJARIGE] 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 [NATUURLIJKPERSOON] (
    [RECHTSPERSOON_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 [VERGADERING] (
    [VERGADERING_ID] INT PRIMARY KEY,
    [EINDTIJD] DATETIME2,
    [HEEFT_RAADSCOMMISSIE_ID] INT,
    [HEEFT_VERSLAG_RAADSSTUK_ID] INT,
    [LOCATIE] VARCHAR(255),
    [STARTTIJD] DATETIME2,
    [TITEL] VARCHAR(255)
);

CREATE TABLE [COLLEGELID] (
    [INGEZETENE_ID] INT PRIMARY KEY,
    [ACHTERNAAM] VARCHAR(255),
    [DATUMAANSTELLING] DATE,
    [DATUMUITTREDING] DATE,
    [FRACTIE] VARCHAR(255),
    [PORTEFEUILLE] VARCHAR(200),
    [TITEL] VARCHAR(80),
    [VOORNAAM] VARCHAR(255)
);

CREATE TABLE [AANWEZIGE_DEELNEMER] (
    [AANWEZIGE_DEELNEMER_ID] INT PRIMARY KEY,
    [AANVANGAANWEZIGHEID] VARCHAR(255),
    [EINDEAANWEZIGHEID] DATE,
    [IS_INGEZETENE_ID] INT,
    [IS_RECHTSPERSOON_ID] INT,
    [NAAM] VARCHAR(255),
    [ROL_ENUM_ID] INT,
    [VERGADERING_ID] INT,
    [VERTEGENWOORDIGTORGANISATIE] VARCHAR(255)
);

CREATE TABLE [INDIENER] (
    [INDIENER_ID] INT PRIMARY KEY,
    [IS_INGEZETENE_ID] INT,
    [IS_RECHTSPERSOON_ID] INT,
    [NAAM] VARCHAR(255),
    [OMSCHRIJVING] NVARCHAR(MAX)
);

CREATE TABLE [KP_RDSSTK_DCMNT] (
    [KP_RDSSTK_DCMNT_ID] INT PRIMARY KEY,
    [RAADSSTUK_ID] INT,
    [DOCUMENT_ID] INT
);

CREATE TABLE [KP_NTRLJKPRSN_RCHTSPRSN] (
    [KP_NTRLJKPRSN_RCHTSPRSN_ID] INT PRIMARY KEY,
    [NATUURLIJKPERSOON_ID] INT,
    [RECHTSPERSOON_ID] INT
);

CREATE TABLE [KP_RDSSTK_VRGDRNG] (
    [KP_RDSSTK_VRGDRNG_ID] INT PRIMARY KEY,
    [RAADSSTUK_ID] INT,
    [VERGADERING_ID] INT
);

CREATE TABLE [VIDEO_OPNAME] (
    [VIDEO_OPNAME_ID] INT PRIMARY KEY,
    [BESTANDSGROOTTE] INT,
    [BETREFT_VERGADERING_ID] INT,
    [DATUMTIJD] DATETIME2,
    [LENGTE] INT,
    [VIDEOFORMAAT] VARCHAR(80)
);

CREATE TABLE [KP_CLLGLD_INGZTN] (
    [KP_CLLGLD_INGZTN_ID] INT PRIMARY KEY,
    [COLLEGELID_ID] INT,
    [INGEZETENE_ID] INT
);

CREATE TABLE [RAADSLID] (
    [INGEZETENE_ID] INT PRIMARY KEY,
    [ACHTERNAAM] VARCHAR(255),
    [DATUMAANSTELLING] DATE,
    [DATUMUITTREDING] DATE,
    [FRACTIE] VARCHAR(255),
    [IS_AANWEZIGE_DEELNEMER_ID] INT,
    [TITEL] VARCHAR(80),
    [VOORNAAM] VARCHAR(255)
);

CREATE TABLE [KP_INDNR_RDSSTK] (
    [KP_INDNR_RDSSTK_ID] INT PRIMARY KEY,
    [INDIENER_ID] INT,
    [RAADSSTUK_ID] INT
);

CREATE TABLE [AGENDAPUNT] (
    [AGENDAPUNT_ID] INT PRIMARY KEY,
    [BETREFT_VIDEO_OPNAME_ID] INT,
    [HEEFT_VERGADERING_ID] INT,
    [NUMMER] INT,
    [OMSCHRIJVING] NVARCHAR(MAX),
    [TITEL] VARCHAR(255)
);

CREATE TABLE [KP_RDSLD_INGZTN] (
    [KP_RDSLD_INGZTN_ID] INT PRIMARY KEY,
    [RAADSLID_ID] INT,
    [INGEZETENE_ID] INT
);

CREATE TABLE [KP_RDSLD_RDSCMMSS] (
    [KP_RDSLD_RDSCMMSS_ID] INT PRIMARY KEY,
    [RAADSLID_ID] INT,
    [RAADSCOMMISSIE_ID] INT
);

CREATE TABLE [KP_RDSSTK_AGNDPNT] (
    [KP_RDSSTK_AGNDPNT_ID] INT PRIMARY KEY,
    [RAADSSTUK_ID] INT,
    [AGENDAPUNT_ID] INT
);

CREATE TABLE [STEMMING] (
    [STEMMING_ID] INT PRIMARY KEY,
    [BETREFT_RAADSSTUK_ID] INT,
    [HOORT_BIJ_AGENDAPUNT_ID] INT,
    [RESULTAAT_ENUM_ID] INT,
    [STEMMINGSTYPE_ENUM_ID] INT
);

ALTER TABLE [COLLEGELID] ADD CONSTRAINT FK_COLLEGELID_GEN_INGEZETENE FOREIGN KEY ([INGEZETENE_ID]) REFERENCES [INGEZETENE] ([INGEZETENE_ID]);
-- Generalization to INGEZETENE

ALTER TABLE [RAADSLID] ADD CONSTRAINT FK_RAADSLID_GEN_INGEZETENE FOREIGN KEY ([INGEZETENE_ID]) REFERENCES [INGEZETENE] ([INGEZETENE_ID]);
-- Generalization to INGEZETENE

ALTER TABLE [NATUURLIJKPERSOON] ADD CONSTRAINT FK_NATUURLIJKPERSOON_GEN_RECHTSPERSOON FOREIGN KEY ([RECHTSPERSOON_ID]) REFERENCES [RECHTSPERSOON] ([RECHTSPERSOON_ID]);
-- Generalization to RECHTSPERSOON

ALTER TABLE [KP_RDSSTK_VRGDRNG] ADD CONSTRAINT FK_KP_RDSSTK_VRGDRNG_1 FOREIGN KEY ([RAADSSTUK_ID]) REFERENCES [RAADSSTUK] ([RAADSSTUK_ID]);
-- RAADSSTUK (many-to-many junction, Connector_ID: 1336)

ALTER TABLE [KP_RDSSTK_VRGDRNG] ADD CONSTRAINT FK_KP_RDSSTK_VRGDRNG_2 FOREIGN KEY ([VERGADERING_ID]) REFERENCES [VERGADERING] ([VERGADERING_ID]);
-- VERGADERING (many-to-many junction, Connector_ID: 1336)

ALTER TABLE [KP_RDSSTK_DCMNT] ADD CONSTRAINT FK_KP_RDSSTK_DCMNT_1 FOREIGN KEY ([RAADSSTUK_ID]) REFERENCES [RAADSSTUK] ([RAADSSTUK_ID]);
-- RAADSSTUK (many-to-many junction, Connector_ID: 1337)

ALTER TABLE [KP_RDSSTK_DCMNT] ADD CONSTRAINT FK_KP_RDSSTK_DCMNT_2 FOREIGN KEY ([DOCUMENT_ID]) REFERENCES [DOCUMENT] ([DOCUMENT_ID]);
-- DOCUMENT (many-to-many junction, Connector_ID: 1337)

ALTER TABLE [KP_RDSLD_INGZTN] ADD CONSTRAINT FK_KP_RDSLD_INGZTN_1 FOREIGN KEY ([RAADSLID_ID]) REFERENCES [RAADSLID] ([INGEZETENE_ID]);
-- RAADSLID (many-to-many junction, Connector_ID: 1338)

ALTER TABLE [KP_RDSLD_INGZTN] ADD CONSTRAINT FK_KP_RDSLD_INGZTN_2 FOREIGN KEY ([INGEZETENE_ID]) REFERENCES [INGEZETENE] ([INGEZETENE_ID]);
-- INGEZETENE (many-to-many junction, Connector_ID: 1338)

ALTER TABLE [KP_RDSLD_RDSCMMSS] ADD CONSTRAINT FK_KP_RDSLD_RDSCMMSS_1 FOREIGN KEY ([RAADSLID_ID]) REFERENCES [RAADSLID] ([INGEZETENE_ID]);
-- RAADSLID (many-to-many junction, Connector_ID: 1339)

ALTER TABLE [KP_RDSLD_RDSCMMSS] ADD CONSTRAINT FK_KP_RDSLD_RDSCMMSS_2 FOREIGN KEY ([RAADSCOMMISSIE_ID]) REFERENCES [RAADSCOMMISSIE] ([RAADSCOMMISSIE_ID]);
-- RAADSCOMMISSIE (many-to-many junction, Connector_ID: 1339)

ALTER TABLE [KP_INDNR_RDSSTK] ADD CONSTRAINT FK_KP_INDNR_RDSSTK_1 FOREIGN KEY ([INDIENER_ID]) REFERENCES [INDIENER] ([INDIENER_ID]);
-- INDIENER (many-to-many junction, Connector_ID: 1343)

ALTER TABLE [KP_INDNR_RDSSTK] ADD CONSTRAINT FK_KP_INDNR_RDSSTK_2 FOREIGN KEY ([RAADSSTUK_ID]) REFERENCES [RAADSSTUK] ([RAADSSTUK_ID]);
-- RAADSSTUK (many-to-many junction, Connector_ID: 1343)

ALTER TABLE [KP_CLLGLD_INGZTN] ADD CONSTRAINT FK_KP_CLLGLD_INGZTN_1 FOREIGN KEY ([COLLEGELID_ID]) REFERENCES [COLLEGELID] ([INGEZETENE_ID]);
-- COLLEGELID (many-to-many junction, Connector_ID: 1347)

ALTER TABLE [KP_CLLGLD_INGZTN] ADD CONSTRAINT FK_KP_CLLGLD_INGZTN_2 FOREIGN KEY ([INGEZETENE_ID]) REFERENCES [INGEZETENE] ([INGEZETENE_ID]);
-- INGEZETENE (many-to-many junction, Connector_ID: 1347)

ALTER TABLE [KP_RDSSTK_AGNDPNT] ADD CONSTRAINT FK_KP_RDSSTK_AGNDPNT_1 FOREIGN KEY ([RAADSSTUK_ID]) REFERENCES [RAADSSTUK] ([RAADSSTUK_ID]);
-- RAADSSTUK (many-to-many junction, Connector_ID: 1350)

ALTER TABLE [KP_RDSSTK_AGNDPNT] ADD CONSTRAINT FK_KP_RDSSTK_AGNDPNT_2 FOREIGN KEY ([AGENDAPUNT_ID]) REFERENCES [AGENDAPUNT] ([AGENDAPUNT_ID]);
-- AGENDAPUNT (many-to-many junction, Connector_ID: 1350)

ALTER TABLE [KP_NTRLJKPRSN_RCHTSPRSN] ADD CONSTRAINT FK_KP_NTRLJKPRSN_RCHTSPRSN_1 FOREIGN KEY ([NATUURLIJKPERSOON_ID]) REFERENCES [NATUURLIJKPERSOON] ([RECHTSPERSOON_ID]);
-- NATUURLIJKPERSOON (many-to-many junction, Connector_ID: 1898)

ALTER TABLE [KP_NTRLJKPRSN_RCHTSPRSN] ADD CONSTRAINT FK_KP_NTRLJKPRSN_RCHTSPRSN_2 FOREIGN KEY ([RECHTSPERSOON_ID]) REFERENCES [RECHTSPERSOON] ([RECHTSPERSOON_ID]);
-- RECHTSPERSOON (many-to-many junction, Connector_ID: 1898)

ALTER TABLE [VIDEO_OPNAME] ADD CONSTRAINT FK_VIDEO_OPNAME_BETREFT FOREIGN KEY ([BETREFT_VERGADERING_ID]) REFERENCES [VERGADERING] ([VERGADERING_ID]);
-- BETREFT (1 to 0..*, Connector_ID: 1332)

ALTER TABLE [STEMMING] ADD CONSTRAINT FK_STEMMING_BETREFT FOREIGN KEY ([BETREFT_RAADSSTUK_ID]) REFERENCES [RAADSSTUK] ([RAADSSTUK_ID]);
-- BETREFT (0..1 to 1, Connector_ID: 1333)

ALTER TABLE [VERGADERING] ADD CONSTRAINT FK_VERGADERING_HEEFT_VERSLAG FOREIGN KEY ([HEEFT_VERSLAG_RAADSSTUK_ID]) REFERENCES [RAADSSTUK] ([RAADSSTUK_ID]);
-- HEEFT_VERSLAG (0..1 to 0..1, Connector_ID: 1334)

ALTER TABLE [VERGADERING] ADD CONSTRAINT FK_VERGADERING_HEEFT FOREIGN KEY ([HEEFT_RAADSCOMMISSIE_ID]) REFERENCES [RAADSCOMMISSIE] ([RAADSCOMMISSIE_ID]);
-- HEEFT (0..1 to 0..*, Connector_ID: 1340)

ALTER TABLE [INDIENER] ADD CONSTRAINT FK_INDIENER_IS FOREIGN KEY ([IS_RECHTSPERSOON_ID]) REFERENCES [RECHTSPERSOON] ([RECHTSPERSOON_ID]);
-- IS (0..1 to 0..1, Connector_ID: 1342)

ALTER TABLE [INDIENER] ADD CONSTRAINT FK_INDIENER_IS_2 FOREIGN KEY ([IS_INGEZETENE_ID]) REFERENCES [INGEZETENE] ([INGEZETENE_ID]);
-- IS (0..1 to 0..1, Connector_ID: 1344)

ALTER TABLE [INDIENER] ADD CONSTRAINT FK_INDIENER_IS_3 FOREIGN KEY ([IS_INGEZETENE_ID]) REFERENCES [INGEZETENE] ([INGEZETENE_ID]);
-- IS (0..1 to 0..1, Connector_ID: 1346)

ALTER TABLE [AGENDAPUNT] ADD CONSTRAINT FK_AGENDAPUNT_HEEFT FOREIGN KEY ([HEEFT_VERGADERING_ID]) REFERENCES [VERGADERING] ([VERGADERING_ID]);
-- HEEFT (1 to 0..*, Connector_ID: 1351)

ALTER TABLE [STEMMING] ADD CONSTRAINT FK_STEMMING_HOORT_BIJ FOREIGN KEY ([HOORT_BIJ_AGENDAPUNT_ID]) REFERENCES [AGENDAPUNT] ([AGENDAPUNT_ID]);
-- HOORT_BIJ (0..* to 0..1, Connector_ID: 1352)

ALTER TABLE [RAADSLID] ADD CONSTRAINT FK_RAADSLID_IS FOREIGN KEY ([IS_AANWEZIGE_DEELNEMER_ID]) REFERENCES [AANWEZIGE_DEELNEMER] ([AANWEZIGE_DEELNEMER_ID]);
-- IS (0..1 to 0..1, Connector_ID: 1353)

ALTER TABLE [AANWEZIGE_DEELNEMER] ADD CONSTRAINT FK_AANWEZIGE_DEELNEMER_VERGADERING FOREIGN KEY ([VERGADERING_ID]) REFERENCES [VERGADERING] ([VERGADERING_ID]);
-- unnamed (1 to 0..*, Connector_ID: 1354)

ALTER TABLE [AANWEZIGE_DEELNEMER] ADD CONSTRAINT FK_AANWEZIGE_DEELNEMER_IS FOREIGN KEY ([IS_RECHTSPERSOON_ID]) REFERENCES [RECHTSPERSOON] ([RECHTSPERSOON_ID]);
-- IS (0..1 to 0..1, Connector_ID: 1355)

ALTER TABLE [AANWEZIGE_DEELNEMER] ADD CONSTRAINT FK_AANWEZIGE_DEELNEMER_IS_2 FOREIGN KEY ([IS_INGEZETENE_ID]) REFERENCES [INGEZETENE] ([INGEZETENE_ID]);
-- IS (0..1 to 0..1, Connector_ID: 1356)

ALTER TABLE [AGENDAPUNT] ADD CONSTRAINT FK_AGENDAPUNT_BETREFT FOREIGN KEY ([BETREFT_VIDEO_OPNAME_ID]) REFERENCES [VIDEO_OPNAME] ([VIDEO_OPNAME_ID]);
-- BETREFT (0..1 to 0..*, Connector_ID: 2221)

ALTER TABLE [AANWEZIGE_DEELNEMER] ADD CONSTRAINT FK_AANWEZIGE_DEELNEMER_ROL_ENUM_ID FOREIGN KEY ([ROL_ENUM_ID_DEELNEMERSROL_ENUM_ID]) REFERENCES [DEELNEMERSROL_ENUM] ([DEELNEMERSROL_ENUM_ID]);
-- ROL_ENUM_ID (1 to 1, Connector_ID: enum_ref)

ALTER TABLE [STEMMING] ADD CONSTRAINT FK_STEMMING_STEMMINGSTYPE_ENUM_ID FOREIGN KEY ([STEMMINGSTYPE_ENUM_ID_STEMMINGSTYPE_ENUM_ID]) REFERENCES [STEMMINGSTYPE_ENUM] ([STEMMINGSTYPE_ENUM_ID]);
-- STEMMINGSTYPE_ENUM_ID (1 to 1, Connector_ID: enum_ref)

ALTER TABLE [STEMMING] ADD CONSTRAINT FK_STEMMING_RESULTAAT_ENUM_ID FOREIGN KEY ([RESULTAAT_ENUM_ID_STEMMINGSRESULTAATTYPE_ENUM_ID]) REFERENCES [STEMMINGSRESULTAATTYPE_ENUM] ([STEMMINGSRESULTAATTYPE_ENUM_ID]);
-- RESULTAAT_ENUM_ID (1 to 1, Connector_ID: enum_ref)