-- creates the table structure in the boxoffice database (normally called 'spring')
-- for MySQL 4.1 or later

CREATE TABLE Booking (
  id INTEGER NOT NULL AUTO_INCREMENT,
  date_made DATETIME,
  reserved_until DATETIME,
  price DECIMAL(15, 2) NOT NULL,
  seat_count INTEGER NOT NULL,
  Purchase_id INTEGER,
  PRIMARY KEY(id)) TYPE = InnoDB;

CREATE TABLE Genre (
  id INTEGER NOT NULL AUTO_INCREMENT,
  name VARCHAR(50),
  PRIMARY KEY(id)) TYPE = InnoDB;

CREATE TABLE Performance (
  id INTEGER NOT NULL AUTO_INCREMENT,
  date_and_time DATETIME,
  Price_Structure_id INTEGER,
  Show_id INTEGER,
  PRIMARY KEY(id)) TYPE = InnoDB;

CREATE TABLE Price_Band (
  id INTEGER NOT NULL AUTO_INCREMENT,
  price DECIMAL(15, 2),
  Price_Structure_id INTEGER,
  Seat_Class_id INTEGER,
  PRIMARY KEY(id)) TYPE = InnoDB;

CREATE TABLE Price_Structure (
  id INTEGER NOT NULL AUTO_INCREMENT,
  name VARCHAR(80),
  PRIMARY KEY(id)) TYPE = InnoDB;

CREATE TABLE Purchase (
  id INTEGER NOT NULL AUTO_INCREMENT,
  authorization_code VARCHAR(32) NOT NULL,
  purchase_date DATETIME NOT NULL,
  encrypted_card_number VARCHAR(19) NOT NULL,
  customer_name VARCHAR(55) NOT NULL,
  collected_by_customer TINYINT NOT NULL,
  email VARCHAR(100) NOT NULL,
  card_country VARCHAR(40) NOT NULL,
  card_city VARCHAR(40) NOT NULL,
  card_street VARCHAR(40) NOT NULL,
  card_postcode VARCHAR(10) NOT NULL,
  delivery_country VARCHAR(40),
  delivery_city VARCHAR(40),
  delivery_street VARCHAR(40),
  delivery_postcode VARCHAR(10),
  Registered_User_id INTEGER,
  PRIMARY KEY(id)) TYPE = InnoDB;

CREATE TABLE Registered_User (
  id INTEGER NOT NULL AUTO_INCREMENT,
  email VARCHAR(100) NOT NULL,
  password VARCHAR(40) NOT NULL,
  card_street VARCHAR(40) NOT NULL,
  card_line2 VARCHAR(40),
  card_city VARCHAR(40) NOT NULL,
  card_postcode VARCHAR(10) NOT NULL,
  delivery_street VARCHAR(40),
  delivery_line2 VARCHAR(40),
  delivery_city VARCHAR(40),
  delivery_postcode VARCHAR(10),
  PRIMARY KEY(id)) TYPE = InnoDB;

CREATE TABLE Seat (
  id INTEGER NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  left_Seat_id INTEGER,
  right_Seat_id INTEGER,
  PRIMARY KEY(id)) TYPE = InnoDB;

CREATE TABLE Seat_Class (
  id INTEGER NOT NULL AUTO_INCREMENT,
  code VARCHAR(5),
  description VARCHAR(200),
  PRIMARY KEY(id)) TYPE = InnoDB;

CREATE TABLE Seat_Plan_Seat (
  Seat_id INTEGER NOT NULL,
  Seating_Plan_id INTEGER NOT NULL,
  Seat_Class_id INTEGER NOT NULL,
  PRIMARY KEY(Seat_Class_id, Seating_Plan_id, Seat_id)) TYPE = InnoDB;

CREATE TABLE Seat_Status (
  Performance_id INTEGER NOT NULL,
  Seat_id INTEGER NOT NULL,
  Booking_id INTEGER,
  Price_Band_id INTEGER,
  PRIMARY KEY(Seat_id, Performance_id)) TYPE = InnoDB;

CREATE TABLE Seating_Plan (
  id INTEGER NOT NULL AUTO_INCREMENT,
  name VARCHAR(50),
  PRIMARY KEY(id)) TYPE = InnoDB;

CREATE TABLE Shows (
  id INTEGER NOT NULL AUTO_INCREMENT,
  name VARCHAR(50),
  Genre_id INTEGER,
  Seating_Plan_id INTEGER,
  PRIMARY KEY(id)) TYPE = InnoDB;

CREATE INDEX ix_Price_Structure
  ON Performance(Price_Structure_id);
ALTER TABLE Performance
  ADD CONSTRAINT fk_Price_Structure
  FOREIGN KEY (Price_Structure_id)
  REFERENCES Price_Structure (id);

CREATE INDEX ix_Show
  ON Performance(Show_id);
ALTER TABLE Performance
  ADD CONSTRAINT fk_Shows
  FOREIGN KEY (Show_id)
  REFERENCES Shows(id);

CREATE INDEX ix_Price_Structure2
  ON Price_Band(Price_Structure_id);
ALTER TABLE Price_Band
  ADD CONSTRAINT fk_Price_Structure2
  FOREIGN KEY (Price_Structure_id)
  REFERENCES Price_Structure (id);

CREATE INDEX ix_Seat_Class
  ON Price_Band(Seat_Class_id);
ALTER TABLE Price_Band
  ADD CONSTRAINT fk_Seat_Class
  FOREIGN KEY (Seat_Class_id)
  REFERENCES Seat_Class (id);

CREATE INDEX ix_Registered_User
  ON Purchase(Registered_User_id);
ALTER TABLE Purchase
  ADD CONSTRAINT fk_Registered_User
  FOREIGN KEY (Registered_User_id)
  REFERENCES Registered_User (id);

CREATE INDEX ix_left_Seat
  ON Seat(left_Seat_id);
ALTER TABLE Seat
  ADD CONSTRAINT fk_left_Seat
  FOREIGN KEY (left_Seat_id)
  REFERENCES Seat (id);

CREATE INDEX ix_right_Seat
  ON Seat(right_Seat_id);
ALTER TABLE Seat
  ADD CONSTRAINT fk_right_Seat
  FOREIGN KEY (right_Seat_id)
  REFERENCES Seat (id);

CREATE INDEX ix_Seat
  ON Seat_Plan_Seat(Seat_id);
ALTER TABLE Seat_Plan_Seat
  ADD CONSTRAINT fk_Seat
  FOREIGN KEY (Seat_id)
  REFERENCES Seat (id)
  ON DELETE CASCADE;

CREATE INDEX ix_Seat_Class2
  ON Seat_Plan_Seat(Seat_Class_id);
ALTER TABLE Seat_Plan_Seat
  ADD CONSTRAINT fk_Seat_Class2
  FOREIGN KEY (Seat_Class_id)
  REFERENCES Seat_Class (id)
  ON DELETE CASCADE;

CREATE INDEX ix_Seating_Plan
  ON Seat_Plan_Seat(Seating_Plan_id);
ALTER TABLE Seat_Plan_Seat
  ADD CONSTRAINT fk_Seating_Plan
  FOREIGN KEY (Seating_Plan_id)
  REFERENCES Seating_Plan (id)
  ON DELETE CASCADE;

CREATE INDEX ix_Booking
  ON Seat_Status(Booking_id);
ALTER TABLE Seat_Status
  ADD CONSTRAINT fk_Booking
  FOREIGN KEY (Booking_id)
  REFERENCES Booking (id);

CREATE INDEX ix_Performance
  ON Seat_Status(Performance_id);
ALTER TABLE Seat_Status
  ADD CONSTRAINT fk_Performance
  FOREIGN KEY (Performance_id)
  REFERENCES Performance (id)
  ON DELETE CASCADE;

CREATE INDEX ix_Price_Band
  ON Seat_Status(Price_Band_id);
ALTER TABLE Seat_Status
  ADD CONSTRAINT fk_Price_Band
  FOREIGN KEY (Price_Band_id)
  REFERENCES Price_Band (id);

CREATE INDEX ix_Seat2
  ON Seat_Status(Seat_id);
ALTER TABLE Seat_Status
  ADD CONSTRAINT fk_Seat2
  FOREIGN KEY (Seat_id)
  REFERENCES Seat (id)
  ON DELETE CASCADE;

CREATE INDEX ix_Genre
  ON Shows(Genre_id);
ALTER TABLE Shows
  ADD CONSTRAINT fk_Genre
  FOREIGN KEY (Genre_id)
  REFERENCES Genre (id);

CREATE INDEX ix_Seating_Plan2
  ON Shows(Seating_Plan_id);
ALTER TABLE Shows
  ADD CONSTRAINT fk_Seating_Plan2
  FOREIGN KEY (Seating_Plan_id)
  REFERENCES Seating_Plan (id);

CREATE INDEX ix_Purchase
  ON Booking(Purchase_id);
ALTER TABLE Booking
  ADD CONSTRAINT fk_Purchase
  FOREIGN KEY (Purchase_id)
  REFERENCES Purchase (id);
