#<pre>
# these should already be set to the same types,
# but just in case set them to correct type,attribute
#
/**
You have a pmaTrigger essay quiz which is based on these 5 essay questions,
and 10 more you will see in future scripts, you need to answer these questions
in your notebook and compare your 
answers to http://cim.saddleback.edu/~premiere/essay5.html before attempting the pmaView quiz.

	Essay 1 Which database engine are we using and why?
	may want to revisit this after you watch the entire phpMyAdmin VG5 Series
	However, you can type the command show engines; in a sql window 
	You should revisit this essay qeustion after we cover constraints/trigger
   
	Essay 2 What are the primary keys for each table, make sure you include complex
	Multi keys.
	
	Essay 3 When a row gets added to table: orders what is the 
        default start and ship date, does this make sense?

	Essay 4 For all currency denomimations what is the default data type and why?

	Essay 5 When we exported the tables (fruitReset VG4 video) what were
	the starting default values of primary keys in tables customer, fruit and orders,
	do you notice anything specific, why do the tables orderLine and inventory
	do not have default values?

	Essay 6-9 are in 
	<a href="http://cim.saddleback.edu/~premiere/fruitTrigger.sql.html">
	fruitTrigger.sql.html</a>

 */
  

/**
You cannot run the file if any foreign key constraints exists,
therefore I must first drop all foreign key contraints before altering tables,
note a foreign key constraints stops you from deleting a table,
therefore alter will give...
#1025 - Error on rename of './fruit/#sql-f05_7838' to './fruit/fruit' (errno: 150)

note if students follow instructions, these problems will not occur,
but following instructions is a thing of the past. 

*/


/* only these should exists

alter table `inventory` drop foreign key `fruitID`;
alter table `orderLine` drop foreign key `orderID`, drop foreign key `fruitID`;
alter table `orders` drop foreign key `customerID`; */
#call fruitShared.dropAllForeignKeys( database() );
#call fruitShared.dropForeignKeys( database(), 'orderLine' );
#call fruitShared.dropForeignKeys( database(), 'orders' );

# drop procedure if exists 
#call fruitShared.dropAllViews( database() );
#drop view if exists `fruitView`;
#drop view if exists `inventoryQuantity`;

ALTER TABLE `customer` 
CHANGE `customerID` `customerID` MEDIUMINT( 11 ) NOT NULL AUTO_INCREMENT,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`customerID`),
engine=InnoDB; #will be discussing engines w constraints/triggers

alter table `fruit` #make sure all columns same datatypes
change `fruitID` `fruitID` smallint( 6 ) unsigned NOT NULL AUTO_INCREMENT,
CHANGE `price`  `price` decimal( 5, 2 ) unsigned NOT NULL, 
CHANGE `name` `name` VARCHAR( 24 ) NOT NULL,
#changing fruitID may require resetting primary key, only orders required this 
DROP PRIMARY KEY,
ADD PRIMARY KEY (`fruitID`),
#note drop primary key if exists does not exist
engine=InnoDB;

alter table `inventory`
change `fruitID` `fruitID` smallint( 6 ) unsigned NOT NULL,
change `quantity` `quantity` mediumint( 11 ) unsigned NOT NULL,
engine=InnoDB;

alter table `orderLine`
change `orderID` `orderID` MEDIUMINT( 11 ) NOT NULL,
change `fruitID` `fruitID` smallint( 6 ) unsigned NOT NULL,
change `quantity` `quantity` mediumint( 11 ) unsigned NOT NULL,
engine=InnoDB;


alter table `orders`
change `orderID` `orderID` MEDIUMINT( 11 ) NOT NULL AUTO_INCREMENT,
change `customerID` `customerID` MEDIUMINT( 11 ) NOT NULL,
# note Timestamp supports defaults, DATETIME doesn't
# note once you change date to startDate, you cannot do it  2nd time
# change if exists `date` doesn not exist in mysql standard
CHANGE `startDate`  `startDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
# DROP IF EXISTS `shipDate` and check for date is not part of mysql standard
DROP PRIMARY KEY,
ADD PRIMARY KEY (`orderID`),
engine=InnoDB;

/** PTD
Note this we cannot add shipDate if it already exists 
would be nice if sql had an if statement, but it doesn't 
 if( (select count( * ) from `orders`) = 3 )

or a change if exists

but instead I need to write an entire procedure to check for shipDate
and then either modify or add it.
problems is if a students runs this on an already modified it will fail
so I needed to add the procedure below, note it is automatically called
*/

drop procedure if exists updateShipDate;

delimiter $$

#unable to move this to fruitShared

CREATE PROCEDURE updateShipDate( )
BEGIN
	-- add a column safely
	IF NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=database()
					        AND COLUMN_NAME='shipDate' AND TABLE_NAME='orders') ) THEN
		alter table `orders` ADD `shipDate` TIMESTAMP NULL DEFAULT NULL;
	ELSE
		alter table `orders` CHANGE `shipDate` `shipDate` TIMESTAMP NULL DEFAULT NULL;
	END IF;
END $$


delimiter ;

call updateShipDate( );

check table customer, fruit, inventory, `orders`, `orderLine`;
#</pre>