DROP DATABASE IF EXISTS cinecity;
CREATE DATABASE cinecity;
USE cinecity;

# --------------------------------------------------------
CREATE TABLE tblArtikel (
  artikelnummer       varchar(50)  NOT NULL PRIMARY KEY,
  bezeichnung         varchar(50)  NOT NULL,
  einkaufspreis       decimal(4,2),
  verkaufspreis       decimal(4,2)
);

LOAD DATA LOCAL INFILE "../tmp/CineCity/tblArtikel.csv" INTO TABLE tblArtikel LINES TERMINATED BY '\r\n';
# --------------------------------------------------------
CREATE TABLE tblArtikelverkauf (
  nr                  int          NOT NULL PRIMARY KEY,
  datum               date,
  artikelnummer       varchar(50),
  anzahl              int
);

LOAD DATA LOCAL INFILE "../tmp/CineCity/tblArtikelverkauf.csv" INTO TABLE tblArtikelverkauf LINES TERMINATED BY '\r\n';
# --------------------------------------------------------
CREATE TABLE tblKinos (
  kinonr              int          NOT NULL PRIMARY KEY,
  kino                varchar(50)  NOT NULL,
  plaetze             int,
  turnaround          int,
  kommentar           text
);

LOAD DATA LOCAL INFILE "../tmp/CineCity/tblKinos.csv" INTO TABLE tblKinos LINES TERMINATED BY '\r\n';
# --------------------------------------------------------
CREATE TABLE tblFilme (
  filmnr              int          NOT NULL PRIMARY KEY,
  filmtitel           varchar(100) NOT NULL,
  plakat              varchar(1),
  fsk                 tinyint,
  laenge              int,
  website             varchar(100),
  originaltitel       varchar(100),
  sonderveranstaltung tinyint,
  zusatztext          text,
  bundesstartdatum    date,
  werbetext           varchar(1)
);

LOAD DATA LOCAL INFILE "../tmp/CineCity/tblFilme.csv" INTO TABLE tblFilme LINES TERMINATED BY '\r\n';
# --------------------------------------------------------
CREATE TABLE tblKinokasse (
  eintrittskartenr    int          NOT NULL PRIMARY KEY,
  verkaufsdatum       date,
  terminnr            int,
  anzahlpersonen      int,
  betrag              decimal(4,2),
  vorstellungsdatum   date
);

LOAD DATA LOCAL INFILE "../tmp/CineCity/tblKinokasse.csv" INTO TABLE tblKinokasse LINES TERMINATED BY '\r\n';
# --------------------------------------------------------
CREATE TABLE tblTermine (
  terminnr            int          NOT NULL PRIMARY KEY,
  wochennr            int,
  kinonr              int,
  kommentar           text,
  tag                 tinyint,
  zeit                time
);

LOAD DATA LOCAL INFILE "../tmp/CineCity/tblTermine.csv" INTO TABLE tblTermine LINES TERMINATED BY '\r\n';
# --------------------------------------------------------
CREATE TABLE tblWochen (
  wochennr            int          NOT NULL PRIMARY KEY,
  filmnr              int,
  kalenderwoche       date,
  lfdwoche            int,
  preis               decimal(4,2),
  kommentar           text
);

LOAD DATA LOCAL INFILE "../tmp/CineCity/tblWochen.csv" INTO TABLE tblWochen LINES TERMINATED BY '\r\n';
# --------------------------------------------------------