---------------Drop Database-----------------------------------

DROP DATABASE IF EXISTS `sample`
GO
---------------Create Database---------------------------------

CREATE DATABASE `sample`
	DEFAULT CHARACTER SET latin1
	DEFAULT COLLATE latin1_swedish_ci
GO

--------------Create Tables-----------------------------------
//First Table

CREATE TABLE `sample`.`language`  ( 
	`language_id`	tinyint(4) UNSIGNED AUTO_INCREMENT COMMENT 'Primary constraint is applied on this column'  NOT NULL,
	`name`       	char(20) NOT NULL,
	`last_update`	timestamp COMMENT 'referred in table film'  NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY(`language_id`)
)
ENGINE = InnoDB
AUTO_INCREMENT = 1
MIN_ROWS = 0
MAX_ROWS = 1000
COMMENT = 'This table is referred by table Film' 
GO

//Second Table

CREATE TABLE `sample`.`film`  ( 
	`film_id`             	smallint(6) AUTO_INCREMENT COMMENT 'This column is a film identifier'  NOT NULL,
	`title`               	varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci COMMENT 'This column contains stores Film Title'  NULL DEFAULT 'Film_name',
	`description`         	text COMMENT 'Blob/Text datatype cannot have default values'  NULL,
	`release_year`        	year(4) COMMENT 'This column stores Release year of the film'  NULL DEFAULT '2013',
	`languale_id`         	tinyint(4) UNSIGNED COMMENT 'This column stores the language identifiers'  NOT NULL,
	`original_language_id`	longblob COMMENT 'Blob/Text datatype cannot have default values'  NULL,
	`rental_duration`     	bigint(20) ZEROFILL COMMENT 'This column stores the rental duration of film'  NOT NULL DEFAULT '3',
	`rental_rate`         	decimal(12,2) COMMENT 'This column contains the rental  rate of film '  NOT NULL DEFAULT '4.99',
	`length`              	smallint(6) UNSIGNED COMMENT 'This Column can be dropped from table'  NULL DEFAULT '0',
	`replacement_cost`    	double COMMENT 'This column stores the replacement cost'  NOT NULL DEFAULT '4.99',
	`rating`              	enum('G','PG','PG-13','R','NC-17') COMMENT 'Can take only one value from ENUM'  NOT NULL DEFAULT 'G',
	`special_features`    	set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') COMMENT 'Can take zero or more values from a SET'  NULL DEFAULT 'Trailers',
	`last_update`         	timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY(`film_id`)
)
ENGINE = InnoDB
CHARACTER SET latin1
COLLATE latin1_swedish_ci
AUTO_INCREMENT = 1
MIN_ROWS = 1
DELAY_KEY_WRITE = 1
PACK_KEYS = 1
AVG_ROW_LENGTH = 1000
ROW_FORMAT = DYNAMIC
MAX_ROWS = 1000
INSERT_METHOD = FIRST
CHECKSUM = 1
COMMENT = 'This table stores the information related to Film' 
GO
ALTER TABLE `sample`.`film`
	ADD CONSTRAINT `language_id_fk`
	FOREIGN KEY(`languale_id`)
	REFERENCES `sample`.`language`(`language_id`)
	ON DELETE RESTRICT 
	ON UPDATE CASCADE 
GO

//Third Table

CREATE TABLE `sample`.`store`  ( 
	`store_id`   	integer(11) UNSIGNED AUTO_INCREMENT COMMENT 'Refered in Table staff'  NOT NULL,
	`address_id` 	numeric(10,0) NULL,
	`last_update`	datetime COMMENT 'datetime datatype is used'  NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY(`store_id`)
)
AUTO_INCREMENT = 1
MIN_ROWS = 0
AVG_ROW_LENGTH = 1000
MAX_ROWS = 1000
COMMENT = 'This table is referred by table staff' 
GO





//Fourth Table

CREATE TABLE `sample`.`staff`  ( 
	`staff_id`        	mediumint(9) UNSIGNED AUTO_INCREMENT COMMENT 'Auto incremented column'  NOT NULL,
	`firstname`       	mediumtext COMMENT 'Blob/text does not have default values'  NOT NULL,
	`lastname`        	longtext COMMENT 'Blob/text does not have default values'  NOT NULL,
	`address_id`      	numeric(10,0) NOT NULL DEFAULT '0000',
	`picture`         	blob COMMENT 'Blob/text does not have default values.The image path mentioned should be the local path where server is installed'  NOT NULL,
	`secret_code`           	varbinary(200) COMMENT 'Enter valid email address'  NOT NULL DEFAULT 'null',
	`store_id`        	int(11) UNSIGNED COMMENT 'It unsigned column'  NOT NULL,
	`active`          	bool COMMENT 'This is a boolean value'  NOT NULL,
	`username`        	tinytext COMMENT 'Enter username'  NOT NULL,
	`password`        	varchar(25) COMMENT 'Default value is *****'  NOT NULL DEFAULT '*****',
	`last_update_time`	time NOT NULL DEFAULT '00:00:00',
	`last_update_date`	date NOT NULL DEFAULT '2013-10-11',
	PRIMARY KEY(`staff_id`)
)
ENGINE = InnoDB
AUTO_INCREMENT = 1
MIN_ROWS = 0
AVG_ROW_LENGTH = 1000
MAX_ROWS = 1000
ROW_FORMAT = REDUNDANT
COMMENT = 'This table contains information about staff' 
GO
ALTER TABLE `sample`.`staff`
	ADD CONSTRAINT `address_id_uk`
	UNIQUE (`address_id`)
GO
ALTER TABLE `sample`.`staff`
	ADD CONSTRAINT `store_id_fk`
	FOREIGN KEY(`store_id`)
	REFERENCES `sample`.`store`(`store_id`)
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION 
GO

//Fifth

CREATE TABLE `sample`.`customer`  ( 
	`customer_id`	real COMMENT 'Stores customer identifiers'  NOT NULL,
	`firstname`  	tinyblob COMMENT 'Blob/text does not have default values'  NULL,
	`lastname`   	mediumblob COMMENT 'Blob/text does not have default values' NULL,
	`address_id` 	long
 COMMENT 'Stores address identifiers' NULL,
	PRIMARY KEY(`customer_id`)
)
ENGINE = InnoDB
MIN_ROWS = 0
PACK_KEYS = 1
ROW_FORMAT = COMPACT
MAX_ROWS = 10
CHECKSUM = 1
COMMENT = 'This table stores the information related to customer' 
GO

//Sixth

CREATE TABLE `sample`.`payment`  ( 
	`payment_id` 	binary(200) COMMENT 'binary datatype value' NOT NULL DEFAULT '0000001',
	`customer_id`	real COMMENT 'Real datatype value'  NULL DEFAULT '1.00',
	`amount`     	float COMMENT 'Folat value'  NULL DEFAULT '10.11',
	`film_code`  	bit(8) COMMENT 'this is bit datatype'  NULL DEFAULT 00000001,
	PRIMARY KEY(`payment_id`)
)
AUTO_INCREMENT = 1
MIN_ROWS = 0
PACK_KEYS = 1
MAX_ROWS = 5
ROW_FORMAT = FIXED
COMMENT = 'This table contains information about payment done by the customers ' 
GO
ALTER TABLE `sample`.`payment`
	ADD CONSTRAINT `customer_id`
	FOREIGN KEY(`customer_id`)
	REFERENCES `sample`.`customer`(`customer_id`)
	ON DELETE SET NULL 
	ON UPDATE CASCADE 
GO

//Seventh Table.

CREATE TABLE `sample`.`messages`  ( 
	`id`     	int(11) UNSIGNED ZEROFILL AUTO_INCREMENT COMMENT 'ID COLUMN'  NOT NULL,
	`message`	varchar(255) CHARACTER SET ascii COLLATE ascii_bin COMMENT 'MESSAGE COLUMN'  NOT NULL DEFAULT 'NULL',
	`time`   	timestamp COMMENT 'TIMESTAMP' NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY(`id`)
)
ENGINE = InnoDB
CHARACTER SET latin1
COLLATE latin1_spanish_ci
AUTO_INCREMENT = 1
MIN_ROWS = 1
DELAY_KEY_WRITE = 0
PACK_KEYS = 0
AVG_ROW_LENGTH = 100
ROW_FORMAT = COMPRESSED
MAX_ROWS = 1000
INSERT_METHOD = LAST
CHECKSUM = 0
COMMENT = 'Triggers result of exectuion is stored in this table ' 
GO

//Eighth Table

CREATE TABLE `sample`.`spatial_datatypes`  ( 
	`col1_geometry`          	geometry COMMENT 'This is geometry datatype'  NULL,
	`col2_point`             	point COMMENT 'This is point datatype'  NULL,
	`col3_linestring`        	linestring COMMENT 'This is linestring datatype'  NULL,
	`col4_polygon`           	polygon COMMENT 'This is polygon datatype'  NULL,
	`col5_multipoint`        	multipoint COMMENT 'This is multipoint datatype'  NULL,
	`col6_multilinestring`   	multilinestring COMMENT 'This is multilinestring datatype'  NULL,
	`col7_multipolygon`      	multipolygon COMMENT 'This is multipolygon datatype'  NULL,
	`col8_geometrycollection`	geometrycollection COMMENT 'This is geometrycollection datatype'  NULL 
	)
ENGINE = InnoDB
CHARACTER SET ascii
COLLATE ascii_bin
MIN_ROWS = 1
DELAY_KEY_WRITE = 1
PACK_KEYS = 0
ROW_FORMAT = COMPACT
MAX_ROWS = 100
INSERT_METHOD = NO
CHECKSUM = 0
COMMENT = 'This is a table with all Spatial Datatypes' 
GO

--------------Create Indexes-----------------------------------
// Create Index on Table film:

USE `sample`
GO
CREATE UNIQUE INDEX `film_index` USING BTREE 
	ON `film`(`title` (12))
GO

// Create Index on Table customer:

USE `sample`
GO
CREATE UNIQUE INDEX `customer_index` USING HASH 
	ON `customer`(`lastname` (12))
GO

// Create Index on Table language:

USE `sample`
GO
CREATE FULLTEXT INDEX `language_index`
	ON `language`(`name`)
GO

--------------Create Views-----------------------------------
// Create View on Table staff and store:

CREATE VIEW `sample`.`staff_list`
AS
SELECT 
 	`staff`.`staff_id` AS `Staff Id`,
	 CONCAT(`staff`.`firstname`,_utf8 ' ',`staff`.`lastname`) AS `Staff Member`
FROM 	`sample`.`staff` `staff` LEFT OUTER JOIN `sample`.`store` `store` 
ON	`staff`.`store_id` = `store`	.`store_id`
GO

//Create View on Table language and film:

CREATE VIEW `sample`.`movie_list`
AS
SELECT 
  	`film`.`title` AS `Movie Name`,	
	`language`.`name` AS `Movie Language`
FROM 	`sample`.`film` `film` INNER JOIN 	`sample`.`language` `language` 
ON	`film`.`languale_id` = `language`.`language_id`
GO

//Create View on Table customer and payment:

CREATE VIEW `sample`.`customer_list`
AS
SELECT
	`customer`.`customer_id` AS `Customer Id`,
	`payment`.`amount` AS `Balance` 
FROM `sample`.`payment` `payment` RIGHT OUTER JOIN `sample`.`customer` `customer` 
ON 	`payment`.`customer_id` = `customer`.`customer_id`
GO

--------------Create Triggers-----------------------------------

// Create trigger on inserting values in Table film

CREATE TRIGGER `sample`.`trigger_on_film`
    AFTER INSERT
    ON `sample`.`film`
    FOR EACH ROW
BEGIN
  INSERT INTO `sample`.`messages`(message) VALUES ('Values Inserted in Table film');
END
GO

//Create trigger on deleting values from Table payment

CREATE TRIGGER `sample`.`trigger_on_payment`
    AFTER DELETE
    ON `sample`.`payment`
    FOR EACH ROW
BEGIN
  INSERT INTO `sample`.`messages`(message) VALUES ('Vaules Deleted from Table payment');
END
GO

//Create trigger on updating values in Table staff


CREATE TRIGGER `sample`.`trigger_on_staff`
    BEFORE UPDATE
    ON `sample`.`staff`
    FOR EACH ROW
BEGIN
  INSERT INTO `sample`.`messages`(message) VALUES ('Vaules Updated from Table staff ');
END
GO

--------------Create Procedures-----------------------------------

// Create procedure to display the no of row count in Table store

CREATE PROCEDURE `sample`.`proc_store` (OUT parameter INT)
     BEGIN
      SELECT COUNT(*) INTO parameter FROM `sample`.`store`;
END
GO

// Create procedure to insert the values in Table messages

CREATE PROCEDURE `sample`.`proc_insertion` (IN W_IN INT) 
BEGIN
IF W_IN >= 0 THEN
    INSERT INTO `sample`.`messages`(`message`)
    VALUES('Procedure proc_insertion executed successfully');
END IF;
END
GO
--------------Create Functions-----------------------------------
// Create Function display Fullname of Staff members from Table staff

CREATE FUNCTION `sample`.`getfullname`(fname CHAR(250),lname CHAR(250))
    RETURNS CHAR(250)
    BEGIN
        DECLARE fullname CHAR(250);
        SET fullname=CONCAT(fname,' ',lname);
        RETURN fullname;
    END 
GO

//Create Simple Function that prints a message.

CREATE FUNCTION `sample`.`print`()
  RETURNS TEXT
  LANGUAGE SQL
BEGIN
  RETURN 'Function Executed Successfully';
END
GO