USE db_vorlesung;

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS Saal;
DROP TABLE IF EXISTS Sitzplatz;
DROP TABLE IF EXISTS Reservierung;
DROP TABLE IF EXISTS Reservierung_Log;
DROP TABLE IF EXISTS Vorstellung;
DROP TABLE IF EXISTS Film;
DROP TABLE IF EXISTS Schauspieler;
DROP TABLE IF EXISTS nm_Reservierung_Sitzplatz;
DROP TABLE IF EXISTS nm_Film_Schauspieler;

SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE IF NOT EXISTS Saal (
  ID INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  Name VARCHAR(50) NOT NULL,
  Beschreibung MEDIUMTEXT,
  UNIQUE u_saal_name (Name)
);

CREATE TABLE IF NOT EXISTS Sitzplatz (
  ID INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  Reihe INTEGER UNSIGNED NOT NULL,
  Platz INTEGER UNSIGNED NOT NULL,
  Preis FLOAT UNSIGNED NOT NULL,
  fk_Saal_ID INTEGER UNSIGNED NOT NULL,
  UNIQUE u_sitzplatz (Reihe, Platz, fk_Saal_ID),
  CONSTRAINT fk_sitzplatz_saal_id
    FOREIGN KEY (fk_Saal_ID)
    REFERENCES Saal (ID)
      ON UPDATE CASCADE
      ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS Schauspieler (
  ID INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  Vorname VARCHAR(255) NOT NULL,
  Nachname VARCHAR(255) NOT NULL,
  Geburtstag DATE
);

CREATE TABLE IF NOT EXISTS Film (
  ID INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  Name VARCHAR(255) NOT NULL,
  Spieldauer INTEGER UNSIGNED NOT NULL,
  Bewertung FLOAT UNSIGNED NOT NULL,
  Beschreibung MEDIUMTEXT,
  INDEX i_film_name (Name)
);

CREATE TABLE IF NOT EXISTS Vorstellung (
  ID INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  Beginn DATETIME NOT NULL,
  fk_Film_ID INTEGER UNSIGNED NOT NULL,
  fk_Saal_ID INTEGER UNSIGNED NOT NULL,
  CONSTRAINT fk_vorstellung_film_id
    FOREIGN KEY (fk_Film_ID)
    REFERENCES Film (ID)
      ON UPDATE CASCADE
      ON DELETE RESTRICT,
  CONSTRAINT fk_vorstellung_saal_id
    FOREIGN KEY (fk_Saal_ID)
    REFERENCES Saal (ID)
      ON UPDATE CASCADE
      ON DELETE RESTRICT
);

CREATE TABLE IF NOT EXISTS Reservierung (
  ID INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  Vorname VARCHAR(255) NOT NULL,
  Nachname VARCHAR(255) NOT NULL,
  Mail VARCHAR(255) NOT NULL,
  fk_Vorstellung_ID INTEGER UNSIGNED NOT NULL,
  INDEX i_reservierung_name (Vorname, Nachname),
  INDEX i_reservierung_name2 (Nachname),
  CONSTRAINT fk_reservierung_vorstellung_id
    FOREIGN KEY (fk_Vorstellung_ID)
    REFERENCES Vorstellung (ID)
      ON UPDATE CASCADE
      ON DELETE RESTRICT
);

CREATE TABLE Reservierung_Log LIKE Reservierung;
select
  concat('ALTER TABLE Reservierung_Log', GROUP_CONCAT(' MODIFY COLUMN ', c.COLUMN_NAME, ' ', c.COLUMN_TYPE)) as alter_statement INTO @prep_statement
  from information_schema.COLUMNS c
  where
  c.IS_NULLABLE = 'NO'
  and c.TABLE_SCHEMA = 'db_vorlesung'
  and c.TABLE_NAME = 'Reservierung_Log'
  group by c.TABLE_NAME;
PREPARE query_nullable FROM @prep_statement;
EXECUTE query_nullable;
DEALLOCATE PREPARE query_nullable;

ALTER TABLE Reservierung_Log DROP PRIMARY KEY;
ALTER TABLE Reservierung_Log ADD `LogID` INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY FIRST;
ALTER TABLE Reservierung_Log ADD `VorheigeID` INTEGER UNSIGNED;
ALTER TABLE Reservierung_Log ADD `Zeitstempel` TIMESTAMP DEFAULT CURRENT_TIMESTAMP() NOT NULL;
ALTER TABLE Reservierung_Log ADD `is_Erfolgreich` BIT NOT NULL;
ALTER TABLE Reservierung_Log ADD `Methode` ENUM ('insert', 'update', 'delete') NOT NULL;

CREATE TABLE IF NOT EXISTS nm_Film_Schauspieler (
  fk_Film_ID INTEGER UNSIGNED NOT NULL,
  fk_Schauspieler_ID INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY (fk_Film_ID, fk_Schauspieler_ID),
  CONSTRAINT fk_nm_film_schauspieler_film_id
    FOREIGN KEY (fk_Film_ID)
    REFERENCES Film (ID)
      ON UPDATE CASCADE
      ON DELETE CASCADE,
  CONSTRAINT fk_nm_film_schauspieler_schauspieler_id
    FOREIGN KEY (fk_Schauspieler_ID)
    REFERENCES Schauspieler (ID)
      ON UPDATE CASCADE
      ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS nm_Reservierung_Sitzplatz (
  fk_Reservierung_ID INTEGER UNSIGNED NOT NULL,
  fk_Sitzplatz_ID INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY pk_nm_reservierung_sitzplatz_reservierung (fk_Reservierung_ID, fk_Sitzplatz_ID),
  CONSTRAINT fk_nm_reservierung_sitzplatz_reservierung_id
    FOREIGN KEY (fk_Reservierung_ID)
    REFERENCES Reservierung (ID)
      ON UPDATE CASCADE
      ON DELETE CASCADE,
  CONSTRAINT fk_nm_reservierung_sitzplatz_sitzplatz_id
    FOREIGN KEY (fk_Sitzplatz_ID)
    REFERENCES Sitzplatz (ID)
      ON UPDATE CASCADE
      ON DELETE CASCADE
);