/* If haven't already created the db: 
CREATE DATABASE VideoRentals;
USE VideoRentals;
*/

/* Attempt to remove each of the tables in case this script 
   has already been run one. */

DROP TABLE IF EXISTS CheckOut;
DROP TABLE IF EXISTS Customer;
DROP TABLE IF EXISTS Copy;
DROP TABLE IF EXISTS Movie;

/* Create and populate the Movie table */
CREATE TABLE IF NOT EXISTS Movie
(movieId INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(300)) ENGINE=INNODB;

INSERT INTO Movie (name)
VALUES ('Inception');

INSERT INTO Movie (name)
VALUES ('The Butler');

INSERT INTO Movie (name)
VALUES ('Star Wars');

/* Create and populate the Copy table */
CREATE TABLE IF NOT EXISTS Copy
(movieId INT NOT NULL,
copyId INT, 
copyCondition CHAR(30), 
PRIMARY KEY (movieId, copyId), 
CONSTRAINT movieConst 
    FOREIGN KEY (movieId)
    REFERENCES Movie(movieId)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
) ENGINE=INNODB;

INSERT INTO Copy (movieId, copyId, copyCondition)
VALUES (3,1,'scratched');

INSERT INTO Copy (movieId, copyId, copyCondition)
VALUES (3,2,'good');

INSERT INTO Copy (movieId, copyId, copyCondition)
VALUES (2,1,'scratched');


/* Create and populate the Customer table */
CREATE TABLE IF NOT EXISTS Customer
(customerId INT AUTO_INCREMENT PRIMARY KEY,
fName CHAR(30), 
lName CHAR(30)) ENGINE=INNODB;

INSERT INTO Customer (fName, lName)
VALUES ('Eli', 'Rose');

INSERT INTO Customer (fName, lName)
VALUES ('Rio', 'Rose');

INSERT INTO Customer (fName, lName)
VALUES ('Eli', 'James');


/* Create and populate the CheckOut table */
CREATE TABLE IF NOT EXISTS CheckOut
(rentalId INT AUTO_INCREMENT PRIMARY KEY,
movieId INT NOT NULL,
copyId INT NOT NULL, 
customerId INT NOT NULL,
CONSTRAINT movieCheckOutConst 
    FOREIGN KEY (movieId, copyId) 
    REFERENCES Copy(movieId, copyId)
    ON DELETE RESTRICT
    ON UPDATE CASCADE, 
CONSTRAINT customerCheckOutConst 
    FOREIGN KEY (customerId) 
    REFERENCES Customer(customerId)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
) ENGINE=INNODB;

INSERT INTO CheckOut (movieId, copyId, customerId)
VALUES (3,1,3);

INSERT INTO CheckOut (movieId, copyId, customerId)
VALUES (3,1,3);

INSERT INTO CheckOut (movieId, copyId, customerId)
VALUES (3,2,3);

