Chinook_Rev1.sql

USE `chinook`;
SET SQL_SAFE_UPDATES = 0;

/*******************************************************************************
   Create Tables
********************************************************************************/
CREATE TABLE `ApplePay`
(
	`PayId` INT NOT NULL,
    `ApplePayToken` VARCHAR(45) NOT NULL,
    CONSTRAINT `PK_ApplePay` PRIMARY KEY (`PayId`)
);

CREATE TABLE `CreditCard`
(
	`PayId` INT NOT NULL,
    `CreditCardNumber` VARCHAR(45) NOT NULL,
    `ExpirationDate` DATE NOT NULL,
    CONSTRAINT `PK_CreditCard` PRIMARY KEY (`PayId`)
);

CREATE TABLE `GooglePay`
(
	`PayId` INT NOT NULL,
    `GoogleEmail` VARCHAR(45) NOT NULL,
    `GoogleToken` VARCHAR(45) NOT NULL,
    CONSTRAINT `PK_GooglePay` PRIMARY KEY (`PayId`)
);

CREATE TABLE `MyPlaylist`
(
    `PlaylistId` INT NOT NULL AUTO_INCREMENT,
    `Name` NVARCHAR(120),
    `CustomerId` INT NOT NULL,
    CONSTRAINT `PK_MyPlaylist` PRIMARY KEY  (`PlaylistId`)
);

CREATE TABLE `MyPlaylistTrack`
(
    `PlaylistId` INT NOT NULL,
    `TrackId` INT NOT NULL,
    CONSTRAINT `PK_MyPlaylistTrack` PRIMARY KEY  (`PlaylistId`, `TrackId`)
);

CREATE TABLE `Orders`
(
	`OrderId` INT NOT NULL AUTO_INCREMENT,
    `CustomerId` INT NOT NULL,
    CONSTRAINT `PK_Orders` PRIMARY KEY (`OrderId`)
);

CREATE TABLE `OrderTracks`
(
	`OrderTrackId` INT NOT NULL AUTO_INCREMENT,
    `OrderId` INT NOT NULL,
    `TrackId` INT NOT NULL,
    CONSTRAINT `PK_OrderTracks` PRIMARY KEY (`OrderTrackID`)
);

CREATE TABLE `Payment`
(
	`PayId` INT NOT NULL AUTO_INCREMENT,
    `CustomerId` INT NOT NULL,
	CONSTRAINT `PK_Payment` PRIMARY KEY (`PayId`)
);

CREATE TABLE `Person`
(
	`PersonId` INT NOT NULL AUTO_INCREMENT,
    `FirstName` NVARCHAR(40) NOT NULL,
    `LastName` NVARCHAR(20) NOT NULL,
    `Address` NVARCHAR(70),
    `City` NVARCHAR(40),
    `State` NVARCHAR(40),
    `Country` NVARCHAR(40),
    `PostalCode` NVARCHAR(10),
    `Phone` NVARCHAR(24),
    `Fax` NVARCHAR(24),
    `Email` NVARCHAR(60) NOT NULL,
    CONSTRAINT `PK_Person` PRIMARY KEY (`PersonID`)
);

CREATE TABLE `TrackURL`
(
	`TrackID` INT NOT NULL,
    `URL` VARCHAR(150),
    CONSTRAINT `PK_TrackURL` PRIMARY KEY (`TrackID`)
);


/*******************************************************************************
   ALTER TABLES
********************************************************************************/

ALTER TABLE customer ADD PersonID INT NOT NULL;
ALTER TABLE employee ADD PersonID INT NOT NULL;

/*******************************************************************************
   Move Data and alter tables
********************************************************************************/

ALTER TABLE Person
ADD EmployeeId int;

ALTER TABLE Person
ADD CustomerId int;

INSERT INTO Person(FirstName, LastName, Address, City, State, Country, PostalCode,Phone,Fax,Email,CustomerId)
SELECT FirstName, LastName, Address, City, State, Country, PostalCode, Phone, Fax, Email,CustomerId
FROM Customer;

UPDATE Customer
INNER JOIN Person ON (Customer.CustomerId = Person.CustomerId)
SET Customer.PersonId = Person.PersonId;

Set @var1 = LAST_INSERT_ID();

ALTER TABLE Person
DROP COLUMN CustomerId;

INSERT INTO Person(FirstName, LastName, Address, City, State, Country, PostalCode,Phone,Fax,Email,EmployeeId)
SELECT FirstName, LastName, Address, City, State, Country, PostalCode, Phone, Fax, Email,EmployeeId
FROM Employee;

Update Employee 
INNER JOIN Person ON (Person.EmployeeId = Employee.EmployeeId)
SET Employee.PersonId = Person.PersonId;

ALTER TABLE Person
DROP COLUMN EmployeeId;

ALTER TABLE Invoice
ADD PayId int;

ALTER TABLE Employee
DROP COLUMN FirstName, DROP COLUMN LastName, DROP COLUMN Address, DROP COLUMN City, 
DROP COLUMN State, DROP COLUMN Country, DROP COLUMN PostalCode, DROP COLUMN Phone, DROP COLUMN Fax, DROP COLUMN Email;

ALTER TABLE Customer
DROP COLUMN FirstName, DROP COLUMN LastName, DROP COLUMN Address, DROP COLUMN City, 
DROP COLUMN State, DROP COLUMN Country, DROP COLUMN PostalCode, DROP COLUMN Phone, DROP COLUMN Fax, DROP COLUMN Email;



/*******************************************************************************
   Create Foreign Keys
********************************************************************************/

ALTER TABLE `ApplePay` ADD CONSTRAINT `FK_ApplePayId`
	FOREIGN KEY (`PayId`) REFERENCES `Payment` (`PayId`) ON DELETE NO ACTION ON UPDATE NO ACTION;

CREATE INDEX `IFK_ApplePayId` ON `ApplePay` (`PayId`);

ALTER TABLE `CreditCard` ADD CONSTRAINT `FK_CreditCardPayId`
	FOREIGN KEY (`PayId`) REFERENCES `Payment` (`PayId`) ON DELETE NO ACTION ON UPDATE NO ACTION;

CREATE INDEX `IFK_CreditCardPayId` ON `CreditCard` (`PayId`);

ALTER TABLE `Customer` ADD CONSTRAINT `FK_CustomerPersonId`
	FOREIGN KEY (`PersonId`) REFERENCES `Person` (`PersonId`) ON DELETE NO ACTION ON UPDATE NO ACTION;

CREATE INDEX `IFK_CustomerPersonId` ON `Customer` (`PersonId`);

ALTER TABLE `Employee` ADD CONSTRAINT `FK_EmployeePersonId`
    FOREIGN KEY (`PersonId`) REFERENCES `Person` (`PersonId`) ON DELETE NO ACTION ON UPDATE NO ACTION;

CREATE INDEX `IFK_EmployeePersonId` ON `Employee` (`PersonId`);

ALTER TABLE `GooglePay` ADD CONSTRAINT `FK_GooglePayId`
	FOREIGN KEY (`PayId`) REFERENCES `Payment` (`PayId`) ON DELETE NO ACTION ON UPDATE NO ACTION;

CREATE INDEX `IFK_GooglePayId` ON `GooglePay` (`PayId`);

ALTER TABLE `Invoice` ADD CONSTRAINT `FK_InvoicePayId`
	FOREIGN KEY (`PayId`) REFERENCES `Payment` (`PayId`);

CREATE INDEX `IFK_InvoicePayId` ON `Invoice` (`PayId`);

ALTER TABLE `MyPlaylist` ADD CONSTRAINT `FK_MyPlaylistCustomerId`
    FOREIGN KEY (`CustomerId`) REFERENCES `Customer` (`CustomerId`) ON DELETE NO ACTION ON UPDATE NO ACTION;
    
CREATE INDEX `IFK_MyPlaylistCustomerId` ON `MyPlaylist` (`CustomerId`);

ALTER TABLE `MyPlaylistTrack` ADD CONSTRAINT `FK_MyPlaylistTrackPlaylistId`
    FOREIGN KEY (`PlaylistId`) REFERENCES `MyPlaylist` (`PlaylistId`) ON DELETE NO ACTION ON UPDATE NO ACTION;
    
CREATE INDEX `IFK_MyPlaylistTrackPlaylistId` ON `MyPlaylistTrack` (`PlaylistId`);

ALTER TABLE `MyPlaylistTrack` ADD CONSTRAINT `FK_MyPlaylistTrackTrackId`
    FOREIGN KEY (`TrackId`) REFERENCES `Track` (`TrackId`) ON DELETE NO ACTION ON UPDATE NO ACTION;

CREATE INDEX `IFK_MyPlaylistTrackTrackId` ON `MyPlaylistTrack` (`TrackId`);

ALTER TABLE `Orders` ADD CONSTRAINT `FK_OrdersCustomerId`
	FOREIGN KEY (`CustomerId`) REFERENCES `Track` (`TrackId`) ON DELETE NO ACTION ON UPDATE NO ACTION;

CREATE INDEX `IFK_OrdersCustomerId` ON `Orders` (`CustomerId`);

ALTER TABLE `OrderTracks` ADD CONSTRAINT `FK_OrderTracksOrderId`
	FOREIGN KEY (`OrderId`) REFERENCES `Orders` (`OrderId`) ON DELETE NO ACTION ON UPDATE NO ACTION;
    
CREATE INDEX `IFK_OrdertracksOrderId` ON `OrderTracks` (`OrderId`);

ALTER TABLE `OrderTracks` ADD CONSTRAINT `FK_OrderTracksTrackId`
	FOREIGN KEY (`TrackId`) REFERENCES `Track` (`TrackId`) ON DELETE NO ACTION ON UPDATE NO ACTION;
    
CREATE INDEX `IFK_OrdertracksTrackId` ON `OrderTracks` (`TrackId`);

ALTER TABLE `Payment` ADD CONSTRAINT `FK_PaymentCustomerId`
	FOREIGN KEY (`CustomerId`) REFERENCES `Customer` (`CustomerId`) ON DELETE NO ACTION ON UPDATE NO ACTION;
    
CREATE INDEX `IFK_PaymentCustomerId` ON `Payment` (`CustomerId`);

ALTER TABLE `TrackURL` ADD CONSTRAINT `FK_TrackURLID`
	FOREIGN KEY (`TrackID`) REFERENCES `Track` (`TrackID`) ON DELETE NO ACTION ON UPDATE NO ACTION;

CREATE INDEX `IFK_TrackURLID` ON `TrackURL` (`TrackID`);

ALTER TABLE `Invoice` ADD CONSTRAINT `FK_PayId`
	FOREIGN KEY (`PayId`) REFERENCES `Payment` (`PayId`) ON DELETE NO ACTION ON UPDATE NO ACTION;

CREATE INDEX `IFK_PayId` ON `Invoice` (`PayId`);

/*******************************************************************************
   Change a SQL variable and insert some 0 rows to the BLANK value
********************************************************************************/
#this changes the default behavior on 0 being inserted
SET SESSION sql_mode='NO_AUTO_VALUE_ON_ZERO';

#make a 0th person
insert into person(PersonID,LastName,FirstName,Address,City,State,PostalCode,Country,Phone,Fax,Email) VALUES
	(0,"BLANK","BLANK","BLANK","BLANK","BLANK","BLANK","BLANK","BLANK","BLANK","BLANK");

#make a 0th employee
insert into employee(EmployeeId,Title,ReportsTo,BirthDate,HireDate,PersonID) VALUES
	(0,"BLANK",NULL,NULL,NULL,0);

#make a 0th Customer
insert into customer(CustomerID,Company,SupportRepId,PersonID) VALUES
	(0,NULL,0,0);
    
/**************************
Add IsDefault to Payment
**************************/
ALTER TABLE payment ADD COLUMN IsDefault bool;

/**************************
*add things
**************************/
INSERT INTO TrackURL(TrackID)
SELECT TrackID
FROM Track;

UPDATE TrackURL
SET url = "http://a1310.phobos.apple.com/us/r1000/167/Music3/v4/00/23/38/0023383d-1616-fbe2-b3a3-7f2de268911b/mzaf_7209253486009554987.plus.aac.p.m4a";