/* Day 4: Multiple table CRUD
Tim Pierson, Dartmouth CS61, Spring 20
Creates a new schema called nyc_inspections based on restauant_inspections table 
in nyc_data schema.

This script has two parts:
1) create tables -- based on forward engineering nyc_inspections_forward_engineer2.mwb EER
2) load data into tables -- handcrafted scripts to load data
*/

/********************************************
*											*
*             1) create tables             	*
*											*
*********************************************/

-- MySQL Workbench Forward Engineering


-- -----------------------------------------------------
-- Schema nyc_inspections
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `nyc_inspections` ;

-- -----------------------------------------------------
-- Schema nyc_inspections
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `nyc_inspections`;
USE `nyc_inspections` ;

-- -----------------------------------------------------
-- Table `nyc_inspections`.`Actions`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `nyc_inspections`.`Actions` (
  `ActionID` INT(11) NOT NULL AUTO_INCREMENT,
  `ActionDescription` VARCHAR(150) NULL DEFAULT NULL,
  PRIMARY KEY (`ActionID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nyc_inspections`.`Cuisine`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `nyc_inspections`.`Cuisine` (
  `CuisineID` INT(11) NOT NULL AUTO_INCREMENT,
  `CuisineDescription` VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (`CuisineID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nyc_inspections`.`InspectionTypes`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `nyc_inspections`.`InspectionTypes` (
  `InspectionTypeID` INT(11) NOT NULL AUTO_INCREMENT,
  `InspectionTypeDescription` VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (`InspectionTypeID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nyc_inspections`.`Restaurants`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `nyc_inspections`.`Restaurants` (
  `RestaurantID` INT(11) NOT NULL,
  `RestaurantName` VARCHAR(100) NULL DEFAULT NULL,
  `Boro` VARCHAR(20) NULL DEFAULT NULL,
  `Building` VARCHAR(20) NULL DEFAULT NULL,
  `Street` VARCHAR(100) NULL DEFAULT NULL,
  `ZipCode` INT(11) NULL DEFAULT NULL,
  `Phone` BIGINT(20) NULL DEFAULT NULL,
  `Latitude` DOUBLE NULL DEFAULT NULL,
  `Longitude` DOUBLE NULL DEFAULT NULL,
  `CuisineID` INT(11) NOT NULL,
  `InspectionCount` INT(11) NULL DEFAULT NULL,
  `InspectionAvgScore` DOUBLE NULL DEFAULT NULL,
  PRIMARY KEY (`RestaurantID`),
  INDEX `fk_Restaurants_Cuisine1_idx` (`CuisineID` ASC) VISIBLE,
  CONSTRAINT `fk_Restaurants_Cuisine1`
    FOREIGN KEY (`CuisineID`)
    REFERENCES `nyc_inspections`.`Cuisine` (`CuisineID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nyc_inspections`.`Inspections`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `nyc_inspections`.`Inspections` (
  `InspectionID` INT(11) NOT NULL AUTO_INCREMENT,
  `RestaurantID` INT(11) NOT NULL,
  `InspectionDate` DATE NULL DEFAULT NULL,
  `Score` INT(11) NULL DEFAULT NULL,
  `Grade` VARCHAR(4) NULL DEFAULT NULL,
  `GradeDate` DATE NULL DEFAULT NULL,
  `ActionID` INT(11) NULL,
  `InspectionTypeID` INT(11) NULL,
  PRIMARY KEY (`InspectionID`),
  INDEX `fk_Inspections_Restaurants_idx` (`RestaurantID` ASC) VISIBLE,
  INDEX `fk_Inspections_Actions1_idx` (`ActionID` ASC) VISIBLE,
  INDEX `fk_Inspections_InspectionTypes1_idx` (`InspectionTypeID` ASC) VISIBLE,
  CONSTRAINT `fk_Inspections_Restaurants`
    FOREIGN KEY (`RestaurantID`)
    REFERENCES `nyc_inspections`.`Restaurants` (`RestaurantID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Inspections_Actions1`
    FOREIGN KEY (`ActionID`)
    REFERENCES `nyc_inspections`.`Actions` (`ActionID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Inspections_InspectionTypes1`
    FOREIGN KEY (`InspectionTypeID`)
    REFERENCES `nyc_inspections`.`InspectionTypes` (`InspectionTypeID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nyc_inspections`.`ViolationCodes`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `nyc_inspections`.`ViolationCodes` (
  `ViolationCode` VARCHAR(10) NOT NULL,
  `ViolationCodeDescription` VARCHAR(400) NULL DEFAULT NULL,
  `CriticalFlag` VARCHAR(4) NULL DEFAULT NULL,
  PRIMARY KEY (`ViolationCode`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nyc_inspections`.`InspectionViolations`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `nyc_inspections`.`InspectionViolations` (
  `InspectionViolationID` INT(11) NOT NULL AUTO_INCREMENT,
  `InspectionID` INT(11) NOT NULL,
  `ViolationCode` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`InspectionViolationID`),
  INDEX `fk_InspectionViolations_Inspections1_idx` (`InspectionID` ASC) VISIBLE,
  INDEX `fk_InspectionViolations_ViolationCodes1_idx` (`ViolationCode` ASC) VISIBLE,
  CONSTRAINT `fk_InspectionViolations_Inspections1`
    FOREIGN KEY (`InspectionID`)
    REFERENCES `nyc_inspections`.`Inspections` (`InspectionID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_InspectionViolations_ViolationCodes1`
    FOREIGN KEY (`ViolationCode`)
    REFERENCES `nyc_inspections`.`ViolationCodes` (`ViolationCode`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;





/********************************************
*											*
*             2) load data		           	*
*											*
*********************************************/
/* fill forward engineered tables from nyc_inspections_forward_engineer2.mwb */

use nyc_inspections;

-- Fill Cuisine table
TRUNCATE TABLE Cuisine;
ALTER TABLE Cuisine AUTO_INCREMENT = 1;
INSERT INTO Cuisine (CuisineDescription)
	SELECT DISTINCT `cuisine description` as CuisineDescription 
	from nyc_data.restaurant_inspections; 

-- Fill Restaurants table
TRUNCATE TABLE Restaurants;
INSERT INTO Restaurants (RestaurantID, RestaurantName, Boro, Building, Street, ZipCode, Phone, Latitude, Longitude, CuisineID)
	SELECT DISTINCT camis AS RestaurantID, 
		dba AS RestaurantName, boro, building, street, 
		zipcode, phone, latitude, longitude, (SELECT CuisineID from Cuisine c where c.CuisineDescription = r.`cuisine description`) AS CuisineDescription
	FROM nyc_data.restaurant_inspections r 
	WHERE dba IS NOT NULL;
    
-- Fill InspectionTypes table
TRUNCATE TABLE InspectionTypes;
ALTER TABLE InspectionTypes AUTO_INCREMENT = 1;
INSERT INTO InspectionTypes (InspectionTypeDescription)
	SELECT DISTINCT `inspection type` as InspectionTypeDescription
		FROM nyc_data.restaurant_inspections
		WHERE `inspection type` IS NOT NULL;
        
-- Fill Actions table
TRUNCATE TABLE Actions;
ALTER TABLE Actions AUTO_INCREMENT = 1;
INSERT INTO Actions (ActionDescription)
	SELECT DISTINCT `Action` AS ActionDescription
		FROM nyc_data.restaurant_inspections
		WHERE `Action` IS NOT NULL;


-- Fill Inspections table
-- note: inspections data '1/1/00' have only nulls for inspection details, skip them
TRUNCATE TABLE Inspections;
ALTER TABLE Inspections AUTO_INCREMENT = 1;
INSERT INTO Inspections (RestaurantID, InspectionDate, Score, Grade, GradeDate, ActionID, InspectionTypeID)
	WITH tempVals AS (
		SELECT r.*, a.*, t.* 
        FROM nyc_data.restaurant_inspections r LEFT JOIN Actions a ON r.`action` = a.ActionDescription
			LEFT JOIN InspectionTypes t ON t.InspectionTypeDescription = r.`inspection type`
		WHERE r.dba IS NOT NULL AND r.`inspection date` <> '1/1/00'
	)
	SELECT DISTINCT camis AS RestaurantID, 
		str_to_date(`inspection date`,'%m/%d/%y') AS InspectionDate,
		`score` AS Score, 
		`grade` AS Grade, 
		str_to_date(`grade date`, '%m/%d/%y') AS GradeDate,
        ActionID AS ActionID,
        InspectionTypeID AS InspectionTypeID
		FROM tempVals;

-- fill ViolationCodes table
TRUNCATE TABLE ViolationCodes;
ALTER TABLE ViolationCodes AUTO_INCREMENT = 1;
INSERT INTO ViolationCodes(ViolationCode,ViolationCodeDescription,CriticalFlag)
	SELECT DISTINCT `violation code`,`violation description`,`critical flag` 
		FROM nyc_data.restaurant_inspections
        WHERE `violation code` IS NOT NULL;


-- fill InspectionViolations table
TRUNCATE TABLE InspectionViolations;
ALTER TABLE InspectionViolations AUTO_INCREMENT = 1;
INSERT INTO InspectionViolations (InspectionID, ViolationCode)
	WITH tempVals AS (
		SELECT r.*, a.*, t.*, i.InspectionID 
        FROM nyc_data.restaurant_inspections r LEFT JOIN Actions a ON r.`action` = a.ActionDescription
			LEFT JOIN InspectionTypes t ON t.InspectionTypeDescription = r.`inspection type`
            LEFT JOIN Inspections i on i.RestaurantID = r.camis AND 
				i.InspectionDate = str_to_date(r.`inspection date`,'%m/%d/%y')
				AND i.InspectionTypeID = t.InspectionTypeID
		WHERE r.dba IS NOT NULL AND r.`inspection date` <> '1/1/00' AND r.`violation code` IS NOT NULL
	)
	SELECT InspectionID, `violation code`
    FROM tempVals;
    

