text: -- LINKS:
  -- https://stackoverflow.com/questions/199498/foreign-key-naming-scheme
  -- https://docs.microsoft.com/en-us/windows/win32/fileio/naming-a-file#maximum-path-length-limitation
  -- https://stackoverflow.com/a/224866
  -- https://www.worldatlas.com/articles/what-is-the-longest-country-name-in-the-world.html
  -- https://www.php.net/manual/en/function.password-hash.php ("PASSWORD_DEFAULT")
  -- https://stackoverflow.com/a/4729239
  -- http://www.yacoset.com/Home/how-to-secure-a-credit-card-number
  -- https://stackoverflow.com/a/11208468
  
  -- TODO
  -- creditcardnr encrypten (Format Preserving Encryption)
  -- internationaal adresformaat
  -- indexeren (covering indexes)
  
  USE iproject44
  
  CREATE TABLE Land (
      id   CHAR(2)      NOT NULL,
      naam NVARCHAR(56) NOT NULL,
  
      CONSTRAINT PK_Land PRIMARY KEY (id)
  );
  
  CREATE TABLE gebruikerType (
      type VARCHAR(10) NOT NULL,
  
      CONSTRAINT PK_gebruikerType PRIMARY KEY (type)
  );
  
  CREATE TABLE controleType (
      type VARCHAR(20) NOT NULL,
  
      CONSTRAINT PK_controleType PRIMARY KEY (type)
  );
  
  CREATE TABLE feedbackType (
      type VARCHAR(16) NOT NULL,
  
      CONSTRAINT PK_feedbackType PRIMARY KEY (type)
  );
  
  CREATE TABLE Gebruiker (
      gebruikersnaam NVARCHAR(15)  NOT NULL,
      voornaam       NVARCHAR(35)  NOT NULL,
      achternaam     NVARCHAR(35)  NOT NULL,
      adresregel1    NVARCHAR(40)  NOT NULL,
      adresregel2    NVARCHAR(40)  NULL,
      postcode       CHAR(6)       NULL,
      plaatsnaam     NVARCHAR(20)  NOT NULL,
      land           CHAR(2)       NOT NULL,
      geboortedatum  DATE          NOT NULL,
      email          NVARCHAR(254) NOT NULL,
      wachtwoord     NVARCHAR(255) NOT NULL,
      gebruikerType  VARCHAR(10)   NOT NULL,
  
      CONSTRAINT PK_Gebruiker PRIMARY KEY (gebruikersnaam),
  
      CONSTRAINT FK_Gebruiker_Land FOREIGN KEY (land)
          REFERENCES Land(id) ON UPDATE CASCADE ON DELETE CASCADE,
  
      CONSTRAINT UQ_Gebruiker_email UNIQUE (email),
  
      CONSTRAINT CK_Gebruiker_geboortedatum CHECK (geboortedatum <= DATEADD(YEAR, -18, CURRENT_TIMESTAMP)),
      CONSTRAINT CK_Gebruiker_gebruikerType CHECK (gebruikerType IN ('koper', 'verkoper', 'beheerder')),
  );
  
  CREATE TABLE Telefoon (
      gebruiker  NVARCHAR(15) NOT NULL,
      rangnr     TINYINT      NOT NULL,
      telefoonnr VARCHAR(15)  NOT NULL,
  
      CONSTRAINT PK_Telefoon PRIMARY KEY (gebruiker, rangnr),
  
      CONSTRAINT FK_Telefoon_Gebruiker FOREIGN KEY (gebruiker)
          REFERENCES Gebruiker(gebruikersnaam) ON UPDATE CASCADE ON DELETE CASCADE
  );
  
  CREATE TABLE Verkoper (
      gebruiker    NVARCHAR(15) NOT NULL,
      controleType VARCHAR(20)  NOT NULL,
      creditcardnr VARCHAR(16)  NULL,
      bank         NVARCHAR(20) NULL,
      rekeningnr   VARCHAR(16)  NULL,
  
      CONSTRAINT PK_Verkoper PRIMARY KEY (gebruiker),
  
      CONSTRAINT FK_Verkoper_Gebruiker FOREIGN KEY (gebruiker)
          REFERENCES Gebruiker(gebruikersnaam) ON UPDATE CASCADE ON DELETE CASCADE,
  
      CONSTRAINT FK_Verkoper_controleType FOREIGN KEY (controleType)
          REFERENCES controleType(type) ON UPDATE CASCADE ON DELETE CASCADE,
  
      CONSTRAINT CK_Verkoper_CreditcardEnRekening CHECK (COALESCE('creditcardnr', 'rekeningnr') IS NOT NULL),
  
      CONSTRAINT CK_Verkoper_CreditcardEnCreditcardnr CHECK (
          (controleType = 'creditcard' AND creditcardnr IS NOT NULL)
          OR (controleType != 'creditcard' AND creditcardnr IS NULL)
      ),
  
      CONSTRAINT CK_Verkoper_controleType CHECK (controleType IN ('creditcard', 'post'))
  );
  
  CREATE TABLE Veiling (
      id                  INT            NOT NULL IDENTITY(1, 1),
      titel               NVARCHAR(50)   NOT NULL,
      beschrijving        NVARCHAR(512)  NOT NULL,
      looptijdBeginDatum  DATETIME       NOT NULL CONSTRAINT DF_Veiling_looptijdBeginDatum DEFAULT CURRENT_TIMESTAMP,
      looptijdEindDatum   DATETIME       NULL,
      valuta              CHAR(3)        NOT NULL,
      startprijs          DECIMAL(19, 2) NOT NULL,
      verkoopprijs        DECIMAL(19, 2) NULL,
      verkoper            NVARCHAR(15)   NOT NULL,
      koper               NVARCHAR(15)   NULL     CONSTRAINT DF_Veiling_koper DEFAULT '[verwijderd]',
      isGesloten          BIT            NOT NULL CONSTRAINT DF_Veiling_isGesloten DEFAULT 0,
  
      CONSTRAINT PK_Veiling PRIMARY KEY (id),
  
      CONSTRAINT FK_Veiling_Verkoper FOREIGN KEY (verkoper)
          REFERENCES Verkoper(gebruiker), -- ON UPDATE CASCADE ON DELETE CASCADE,
  
      CONSTRAINT FK_Veiling_Gebruiker FOREIGN KEY (koper)
          REFERENCES Gebruiker(gebruikersnaam), -- ON UPDATE CASCADE ON DELETE SET NULL,
  
      CONSTRAINT CK_Veiling_koperVerkoper CHECK (verkoper != koper),
  
      CONSTRAINT CK_Veiling_looptijdEindDatum CHECK (looptijdEindDatum > looptijdBeginDatum),
  );
  
  CREATE TABLE Bod (
      gebodenBedrag DECIMAL(19, 2) NOT NULL,
      veiling       INT            NOT NULL,
      gebruiker     NVARCHAR(15)   NOT NULL CONSTRAINT DF_Bod_gebruiker DEFAULT '[verwijderd]',
      datum         DATETIME       NOT NULL CONSTRAINT DF_Bod_datum     DEFAULT CURRENT_TIMESTAMP,
  
      CONSTRAINT PK_Bod PRIMARY KEY (gebodenBedrag, veiling),
  
      CONSTRAINT FK_Bod_Veiling FOREIGN KEY (veiling)
          REFERENCES Veiling(id) ON UPDATE CASCADE ON DELETE CASCADE,
  
      CONSTRAINT FK_Bod_Gebruiker FOREIGN KEY (gebruiker)
          REFERENCES Gebruiker(gebruikersnaam) ON UPDATE CASCADE ON DELETE SET DEFAULT,
  );
  
  CREATE TABLE Bestand (
      locatie NVARCHAR(260) NOT NULL,
      veiling INT           NOT NULL,
  
      CONSTRAINT PK_Bestand PRIMARY KEY (locatie),
  
      CONSTRAINT FK_Bestand_Veiling FOREIGN KEY (veiling)
          REFERENCES Veiling(id) ON UPDATE CASCADE ON DELETE CASCADE
  );
  
  CREATE TABLE Feedback (
      veiling       INT           NOT NULL,
      gebruikerType VARCHAR(10)   NOT NULL,
      feedbackType  VARCHAR(16)   NOT NULL,
      datum         DATETIME      NOT NULL CONSTRAINT DF_Feedback_datum DEFAULT CURRENT_TIMESTAMP,
      commentaar    NVARCHAR(512) NULL,
  
      CONSTRAINT PK_Feedback PRIMARY KEY (veiling, gebruikerType),
  
      CONSTRAINT FK_Feedback_Veiling FOREIGN KEY (veiling)
          REFERENCES Veiling(id) ON UPDATE CASCADE ON DELETE CASCADE,
  
      CONSTRAINT FK_Feedback_gebruikerType FOREIGN KEY (gebruikerType)
          REFERENCES gebruikerType(type) ON UPDATE NO ACTION ON DELETE NO ACTION,
  
      CONSTRAINT FK_Feedback_feedbackType FOREIGN KEY (feedbackType)
          REFERENCES feedbackType(type) ON UPDATE CASCADE ON DELETE NO ACTION,
  
      CONSTRAINT CK_Feedback_gebruikerType CHECK (gebruikerType IN ('koper', 'verkoper')),
  );
  
  CREATE TABLE Rubriek (
      id             INT          NOT NULL IDENTITY(1, 1),
      naam           NVARCHAR(40) NOT NULL,
      rangnr         TINYINT      NOT NULL CONSTRAINT DF_Rubriek_rangnr DEFAULT 1,
      subRubriekVan  INT          NULL,
      isUitgefaseerd BIT          NOT NULL CONSTRAINT DF_Rubriek_isUitgefaseerd DEFAULT 0,
  
      CONSTRAINT PK_Rubriek PRIMARY KEY (id),
  
      CONSTRAINT CK_Rubriek CHECK (subRubriekVan != id),
  
      CONSTRAINT FK_Rubriek_Rubriek FOREIGN KEY (subRubriekVan)
          REFERENCES Rubriek(id), -- ON UPDATE CASCADE ON DELETE SET NULL,
  );
  
  CREATE TABLE VeilingInRubriek (
      veiling INT NOT NULL,
      rubriek INT NOT NULL CONSTRAINT DF_VeilingInRubriek_rubriek DEFAULT -1,
  
      CONSTRAINT PK_VeilingInRubriek PRIMARY KEY (veiling, rubriek),
  
      CONSTRAINT FK_VeilingInRubriek_Veiling FOREIGN KEY (veiling)
          REFERENCES Veiling(id) ON UPDATE CASCADE ON DELETE CASCADE,
  
      CONSTRAINT FK_VeilingInRubriek_Rubriek FOREIGN KEY (rubriek)
          REFERENCES Rubriek(id) ON UPDATE CASCADE ON DELETE SET DEFAULT,
  );
  
  INSERT INTO gebruikerType VALUES ('koper'), ('verkoper'), ('beheerder')
  INSERT INTO controleType  VALUES ('post'), ('creditcard')
  INSERT INTO feedbackType  VALUES ('negatief'), ('neutraal'), ('positief')
  INSERT INTO Land          VALUES ('NL', 'Nederland')
  INSERT INTO Gebruiker     VALUES ('[verwijderd]', 'NVT', 'NVT', 'NVT', NULL, NULL, 'NVT', 'NL',
                                    '1970-01-01', 'NVT',
                                    '$2y$10$oq9OA6tSdcjXGp.3rAqP0OhVAm9a3FFiHlcyHbm.ONKQIj4X5Lweu', -- VYtsu742
                                    'verkoper'),
                                   ('iproject44', 'I-Project', 'Groep 44', 'Heyendaalseweg 98', NULL, '6525EE', 'Nijmegen', 'NL',
                                    '1970-01-01', 'testmailiproject@gmail.com',
                                    '$2y$10$9OyuwXnkEIsfXS6ge/B20.liqnn5VA6PT65fHBWEHXrMIoxtCj88m',
                                    'beheerder')
  GO
  