aquariumTycoonFullDatabaseScript.sql

-- MySQL Script generated by MySQL Workbench
-- Thu Dec  7 16:28:20 2017
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema aquarium_tycoon_development
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema aquarium_tycoon_development
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `aquarium_tycoon_development` DEFAULT CHARACTER SET utf8 ;
USE `aquarium_tycoon_development` ;

-- -----------------------------------------------------
-- Table `aquarium_tycoon_development`.`habitats`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `aquarium_tycoon_development`.`habitats` ;

CREATE TABLE IF NOT EXISTS `aquarium_tycoon_development`.`habitats` (
  `habitat_id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `description` VARCHAR(200) NULL DEFAULT NULL,
  `year_added` INT(11) NOT NULL,
  PRIMARY KEY (`habitat_id`))
ENGINE = InnoDB
AUTO_INCREMENT = 5
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `aquarium_tycoon_development`.`tanks`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `aquarium_tycoon_development`.`tanks` ;

CREATE TABLE IF NOT EXISTS `aquarium_tycoon_development`.`tanks` (
  `tank_id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `description` VARCHAR(200) NULL DEFAULT NULL,
  `water_type` ENUM('fresh', 'salt') NOT NULL,
  `volume` DOUBLE NOT NULL,
  `temperature` DOUBLE NOT NULL,
  `year_built` INT(11) NOT NULL,
  `habitat_id` INT(11) NOT NULL,
  PRIMARY KEY (`tank_id`),
  INDEX `fk_habitat_tank` (`habitat_id` ASC),
  CONSTRAINT `fk_habitat_tank`
    FOREIGN KEY (`habitat_id`)
    REFERENCES `aquarium_tycoon_development`.`habitats` (`habitat_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 15
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `aquarium_tycoon_development`.`animals`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `aquarium_tycoon_development`.`animals` ;

CREATE TABLE IF NOT EXISTS `aquarium_tycoon_development`.`animals` (
  `animal_id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL DEFAULT NULL,
  `species` VARCHAR(45) NOT NULL,
  `tank_id` INT(11) NOT NULL,
  `birth_date` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`animal_id`),
  INDEX `fk_tank_animal` (`tank_id` ASC),
  CONSTRAINT `fk_tank_animal`
    FOREIGN KEY (`tank_id`)
    REFERENCES `aquarium_tycoon_development`.`tanks` (`tank_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 155
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `aquarium_tycoon_development`.`ar_internal_metadata`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `aquarium_tycoon_development`.`ar_internal_metadata` ;

CREATE TABLE IF NOT EXISTS `aquarium_tycoon_development`.`ar_internal_metadata` (
  `key` VARCHAR(255) NOT NULL,
  `value` VARCHAR(255) NULL DEFAULT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`key`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `aquarium_tycoon_development`.`employees`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `aquarium_tycoon_development`.`employees` ;

CREATE TABLE IF NOT EXISTS `aquarium_tycoon_development`.`employees` (
  `employee_id` INT(11) NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(45) NOT NULL,
  `last_name` VARCHAR(45) NOT NULL,
  `hired_date` DATE NOT NULL,
  `fired_date` DATE NULL DEFAULT NULL,
  `salary` DOUBLE NOT NULL,
  PRIMARY KEY (`employee_id`))
ENGINE = InnoDB
AUTO_INCREMENT = 10
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `aquarium_tycoon_development`.`events`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `aquarium_tycoon_development`.`events` ;

CREATE TABLE IF NOT EXISTS `aquarium_tycoon_development`.`events` (
  `event_id` INT(11) NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(45) NOT NULL,
  `description` VARCHAR(200) NULL DEFAULT NULL,
  `price` FLOAT NOT NULL,
  `managing_employee` INT(11) NOT NULL,
  `max_guests_per_person` INT(11) NULL DEFAULT NULL,
  `max_attending` INT(11) NOT NULL,
  `event_date` DATETIME NOT NULL,
  PRIMARY KEY (`event_id`),
  INDEX `manager` (`managing_employee` ASC),
  CONSTRAINT `fk_employee_events`
    FOREIGN KEY (`managing_employee`)
    REFERENCES `aquarium_tycoon_development`.`employees` (`employee_id`)
    ON UPDATE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 12
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `aquarium_tycoon_development`.`members`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `aquarium_tycoon_development`.`members` ;

CREATE TABLE IF NOT EXISTS `aquarium_tycoon_development`.`members` (
  `member_id` INT(11) NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(45) NOT NULL,
  `last_name` VARCHAR(45) NOT NULL,
  `date_joined` DATE NOT NULL,
  `expiration_date` DATE NOT NULL,
  `fee` DOUBLE NOT NULL,
  PRIMARY KEY (`member_id`))
ENGINE = InnoDB
AUTO_INCREMENT = 27
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `aquarium_tycoon_development`.`event_registrations`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `aquarium_tycoon_development`.`event_registrations` ;

CREATE TABLE IF NOT EXISTS `aquarium_tycoon_development`.`event_registrations` (
  `registration_code` INT(11) NOT NULL AUTO_INCREMENT,
  `time` DATETIME NOT NULL,
  `member_id` INT(11) NOT NULL,
  `event_id` INT(11) NOT NULL,
  `guest_count` INT(11) NULL DEFAULT NULL,
  PRIMARY KEY (`registration_code`),
  INDEX `eventRegistered` (`event_id` ASC),
  INDEX `memberRegistering` (`member_id` ASC),
  CONSTRAINT `fk_event_registration`
    FOREIGN KEY (`event_id`)
    REFERENCES `aquarium_tycoon_development`.`events` (`event_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_member_registration`
    FOREIGN KEY (`member_id`)
    REFERENCES `aquarium_tycoon_development`.`members` (`member_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 19
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `aquarium_tycoon_development`.`feeding_logs`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `aquarium_tycoon_development`.`feeding_logs` ;

CREATE TABLE IF NOT EXISTS `aquarium_tycoon_development`.`feeding_logs` (
  `feeding_id` INT(11) NOT NULL AUTO_INCREMENT,
  `time` DATETIME NOT NULL,
  `food` VARCHAR(45) NOT NULL,
  `quantity` FLOAT NOT NULL,
  `employee_id` INT(11) NOT NULL,
  `tank_id` INT(11) NULL DEFAULT NULL,
  PRIMARY KEY (`feeding_id`),
  INDEX `fk_tank_feeding` (`tank_id` ASC),
  INDEX `fk_employee_feeding` (`employee_id` ASC),
  CONSTRAINT `fk_employee_feeding`
    FOREIGN KEY (`employee_id`)
    REFERENCES `aquarium_tycoon_development`.`employees` (`employee_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_tank_feeding`
    FOREIGN KEY (`tank_id`)
    REFERENCES `aquarium_tycoon_development`.`tanks` (`tank_id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 393
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `aquarium_tycoon_development`.`schema_migrations`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `aquarium_tycoon_development`.`schema_migrations` ;

CREATE TABLE IF NOT EXISTS `aquarium_tycoon_development`.`schema_migrations` (
  `version` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`version`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `aquarium_tycoon_development`.`treatment_logs`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `aquarium_tycoon_development`.`treatment_logs` ;

CREATE TABLE IF NOT EXISTS `aquarium_tycoon_development`.`treatment_logs` (
  `treatment_id` INT(11) NOT NULL AUTO_INCREMENT,
  `time` DATETIME NOT NULL,
  `medicine` VARCHAR(45) NOT NULL,
  `quantity` FLOAT NOT NULL,
  `employee_id` INT(11) NOT NULL,
  `animal_id` INT(11) NOT NULL,
  PRIMARY KEY (`treatment_id`),
  INDEX `fk_animal_treatment` (`animal_id` ASC),
  INDEX `fk_employee_treatment` (`employee_id` ASC),
  CONSTRAINT `fk_animal_treatment`
    FOREIGN KEY (`animal_id`)
    REFERENCES `aquarium_tycoon_development`.`animals` (`animal_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_employee_treatment`
    FOREIGN KEY (`employee_id`)
    REFERENCES `aquarium_tycoon_development`.`employees` (`employee_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 53
DEFAULT CHARACTER SET = utf8;

USE `aquarium_tycoon_development` ;

-- -----------------------------------------------------
-- procedure addMember
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`addMember`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `addMember`(fName varchar(45), lName varchar(45), expirationDate date, feePaid float)
BEGIN 

    -- general error handler
    DECLARE sqlError INT DEFAULT FALSE;

    BEGIN
    -- Error handler setups
    DECLARE EXIT HANDLER FOR SQLEXCEPTION SET sqlError = TRUE;


    INSERT INTO Members (firstName, lastName, dateJoined, expirationDate, fee) 
      VALUES (fName, lName, CURDATE(), DATE_ADD(curdate(), INTERVAL 18 month), feePaid)  ;
    SELECT CONCAT('new member "', fName, ' ', lastName, '" was inserted.') AS 'result';    

    END; 
      IF sqlError = TRUE THEN
      SELECT CONCAT('SQL Error. Member "', fName, ' ', lastName, '" was not inserted') AS 'result';    
      END IF;

    END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure allRegistrations
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`allRegistrations`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `allRegistrations`()
BEGIN

        SELECT * FROM event_registrations;

        END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure all_animals
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`all_animals`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `all_animals`()
BEGIN
        SELECT * FROM animals;
      END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure createEvent
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`createEvent`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `createEvent`(eventName VARCHAR(45), eventDescription VARCHAR(45), price float, manager int, guestsPerPerson int, maxGuests int, eventStart datetime)
BEGIN

    -- general error handler
    DECLARE sqlError INT DEFAULT FALSE;

    BEGIN
    -- Error handler setups
    DECLARE EXIT HANDLER FOR SQLEXCEPTION SET sqlError = TRUE;


    INSERT INTO Events (title, description, price, managing_employee, max_guests_per_person, max_attending, event_date)
      VALUES (eventName , eventDescription, price, manager, guestsPerPerson, maxGuests, eventStart);
    SELECT CONCAT('new event "', eventName, '" was inserted.') AS 'result';

    END;

      IF sqlError = TRUE THEN
      SELECT CONCAT('SQL Error. Event "', eventName, '" was not inserted') AS 'result';
      END IF;

    END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure createHabitat
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`createHabitat`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `createHabitat`(habitatName VARCHAR(45), habitatDescription VARCHAR(200))
BEGIN

          -- Habitats must have unique names
          DECLARE duplicateName INT DEFAULT FALSE;
          -- general error handler
          DECLARE sqlError INT DEFAULT FALSE;

          BEGIN
          -- Error handler setups
          DECLARE CONTINUE HANDLER FOR 1062 SET duplicateName = TRUE;
          DECLARE EXIT HANDLER FOR SQLEXCEPTION SET sqlError = TRUE;
          INSERT INTO HABITATS (name, description, yearAdded) VALUES (habitatName, habitatDescription, YEAR(CURDATE()));
          SELECT CONCAT('new habitat "', habitatName, '" was inserted.') AS 'result';
          END;

          IF duplicateName = TRUE THEN
            SELECT CONCAT('Habitat "', habitatName, '" was not inserted - there is already a habitat with this name') AS 'result';
          ELSEIF sqlError = TRUE THEN
            SELECT CONCAT('SQL Error. Habitat "', habitatName, '" was not inserted') AS 'result';
          END IF;

          END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure create_animal
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`create_animal`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `create_animal`(
        a_name varchar(45),
        a_species varchar(45),
        t_id int(11),
        birthday dateTime
      )
BEGIN
        INSERT INTO animals
        (name, species, tank, birth_date)
        VALUES
        (a_name, a_species, t_id, birthday);
      END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure current_employees
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`current_employees`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `current_employees`()
BEGIN
SELECT * FROM employees WHERE fired_date IS NULL ORDER BY last_name;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure current_members
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`current_members`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `current_members`()
BEGIN
SELECT * FROM members WHERE expiration_date >= CURDATE() ORDER BY last_name asc;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure deleteEvent
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`deleteEvent`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `deleteEvent`(eventNo int)
BEGIN

    -- general error handler
    DECLARE sqlError INT DEFAULT FALSE;

    BEGIN
    -- Error handler setups
    DECLARE EXIT HANDLER FOR SQLEXCEPTION SET sqlError = TRUE;

    DELETE FROM EVENTS WHERE event_ID = eventNo;

    END;

      IF sqlError = TRUE THEN
      SELECT CONCAT('SQL Error. Event "', eventNo, '" was not deleted') AS 'result';
      ELSE
      SELECT CONCAT('Event "', eventNo, '" removed.') AS 'result';
      END IF;

    END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure deleteHabitat
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`deleteHabitat`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `deleteHabitat`(habitatName VARCHAR(45))
BEGIN

      -- general error handler
      DECLARE sqlError INT DEFAULT FALSE;

      BEGIN
      -- Error handler setups
      DECLARE EXIT HANDLER FOR SQLEXCEPTION SET sqlError = TRUE;


      -- DELETE HABITAT, DELETE ALL ANIMALS IN IT? **************
      DELETE FROM HABITATS WHERE name = habitatName;

      END;

      IF sqlError = TRUE THEN
        SELECT CONCAT('SQL Error. Habitat "', habitatName, '" was not deleted') AS 'result';
      ELSE
        SELECT CONCAT('Habitat "', habitatName, '" removed.') AS 'result';
      END IF;

    END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure deleteMember
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`deleteMember`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `deleteMember`(ID int)
BEGIN 
    DECLARE memberExist INT DEFAULT 0;
    DECLARE sqlError TINYINT DEFAULT FALSE;

    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET sqlError = TRUE;
    SET memberExist = (SELECT COUNT(*) FROM members WHERE ID = memberID);

    DELETE FROM Members WHERE ID = memberID;

    IF sqlError = FALSE and employeeExist = 1 THEN
        SELECT CONCAT('Employee ', ID, ' no longer a member.') as 'result';
    ELSE
      SELECT CONCAT('cannot remove employee ', ID, '.') as 'result';

    END IF;
    END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure deleteRegistration
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`deleteRegistration`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `deleteRegistration`(memNo int, eventNo int)
BEGIN

        -- general error handler
        DECLARE sqlError INT DEFAULT FALSE;

        BEGIN
        -- Error handler setups
        DECLARE EXIT HANDLER FOR SQLEXCEPTION SET sqlError = TRUE;

        DELETE FROM event_registrations WHERE event_id = eventNo AND member_id = memNo;

        END;

          IF sqlError = TRUE THEN
          SELECT CONCAT('SQL Error. Registration not eleted.') AS 'result';
          ELSE
          SELECT CONCAT('Registration deleted') AS 'result';
          END IF;

        END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure delete_animal
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`delete_animal`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `delete_animal`(
        a_id int(11)
      )
BEGIN
        DELETE from animals
          WHERE animal_id = a_id;
      END$$

DELIMITER ;

-- -----------------------------------------------------
-- function events_managed
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP function IF EXISTS `aquarium_tycoon_development`.`events_managed`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `events_managed`(emp int) RETURNS int(11)
RETURN (SELECT COUNT(*) FROM events where emp = managing_employee AND events.event_date >= CURDATE())$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure expiringMembers
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`expiringMembers`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `expiringMembers`(days int)
BEGIN 

    SELECT * FROM events
      WHERE expirationDate <= DATE_ADD(curdate(), INTERVAL days day)
        ORDER BY time DESC;

    END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure get_animal
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`get_animal`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_animal`(
        aID int(11),
        aname varchar(45),
        aspecies varchar(45),
        tID int(11),
        birthday dateTime
      )
BEGIN
        SELECT * FROM animals
        where (animalID = aID OR aID IS NULL) AND
        (aname = name OR aname IS NULL) AND
        (aspecies = species OR aspecies IS NULL) AND
        ( tID = tank OR tID is NULL) AND 
        (birthday = birthDate OR birthday IS NULL);
      END$$

DELIMITER ;

-- -----------------------------------------------------
-- function get_animal_count
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP function IF EXISTS `aquarium_tycoon_development`.`get_animal_count`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `get_animal_count`(
        aID int(11),
        aname varchar(45),
        aspecies varchar(45),
        tID int(11),
        birthday dateTime
      ) RETURNS int(11)
BEGIN
        DECLARE numAnimals INT;
    
        SELECT count(*) 
        INTO numAnimals 
        FROM animals
        where (animalID = aID OR aID IS NULL) AND
        (aname = name OR aname IS NULL) AND
        (aspecies = species OR aspecies IS NULL) AND
        ( tID = tank OR tID is NULL) AND 
        (birthday = birthDate OR birthday IS NULL);
    
        RETURN numAnimals;
      END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure get_by_birthday
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`get_by_birthday`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_by_birthday`(
        birthday dateTime
      )
BEGIN
        SELECT *
          FROM animals
          WHERE birth_date = birthday;
      END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure get_by_habitat
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`get_by_habitat`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_by_habitat`(
        habitat_name VARCHAR(45)
      )
BEGIN
        SELECT animal_id, animals.name, species, tank, birth_date
        FROM animals
        JOIN tanks on animals.tank = tanks.tank_id
        JOIN habitats on tanks.habitat_id = habitats.habitat_id
        WHERE habitats.name = habitat_name;
      END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure get_by_name
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`get_by_name`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_by_name`(
        animal_name VARCHAR(45)
      )
BEGIN
        select * from animals
          where name = animal_name;
      END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure get_by_species
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`get_by_species`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_by_species`(
        species_name VARCHAR(45)
      )
BEGIN
        SELECT * FROM animals
        WHERE species = species_name;
      END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure get_by_tank_id
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`get_by_tank_id`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_by_tank_id`(
        tank_id integer
      )
BEGIN
        SELECT *
        FROM animals
        WHERE tank = tank_id;
      END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure get_by_tank_name
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`get_by_tank_name`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_by_tank_name`(
        tank_name VARCHAR(45)
      )
BEGIN
        SELECT animal_id, animals.name, species, animals.tank, birth_date
        FROM animals
        JOIN tanks ON animals.tank = tanks.tank_id
        WHERE tanks.name = tank_name;
      END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure get_employee
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`get_employee`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_employee`(
    eID int(11),
      fName varchar(45),
      lName varchar(45),
      hired varChar(45),
      eSalary double
  )
BEGIN
    SELECT * FROM employees
      where (eID = employee_id OR eID IS NULL) AND
      (fname = first_name OR fName IS NULL) AND
    (lName = last_name OR lName IS NULL) AND
     ( hired = hired_date OR hired is NULL) AND
      (eSalary = salary OR eSalary IS NULL);
  END$$

DELIMITER ;

-- -----------------------------------------------------
-- function get_employee_count
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP function IF EXISTS `aquarium_tycoon_development`.`get_employee_count`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `get_employee_count`(
    eID int(11),
      fName varchar(45),
      lName varchar(45),
      hired varChar(45),
      eSalary double
  ) RETURNS int(11)
BEGIN
    DECLARE numEmployees INT;

    SELECT count(*)
      INTO numEmployees
      FROM employees
      where (eID = employee_id OR eID IS NULL) AND
      (fname = first_name OR fName IS NULL) AND
    (lName = last_name OR lName IS NULL) AND
     ( hired = hired_date OR hired is NULL) AND
      (eSalary = salary OR eSalary IS NULL);

      RETURN numEmployees;
  END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure get_event
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`get_event`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_event`(
        eID int(11),
        eTitle varchar(45),
        eDescription varchar(200),
        ePrice float,
        eManager int(11),
        maxGuests int(11),
        maxAttend int(11),
        eDate dateTime
    )
BEGIN
        SELECT * FROM events
        where (eID = event_ID OR eID IS NULL) AND
        (eTitle = title OR eTitle IS NULL) AND
        (eDescription = description OR eDescription IS NULL) AND
       (ePrice = price OR ePrice is NULL) AND
        (eManager = managing_employee OR eManager IS NULL) AND
        (maxGuests = max_guests_per_person OR maxGuests is NULL) AND
        ( maxAttend = max_attending OR maxAttend is NULL) AND
        (eDate = event_date OR eDate is NULL);
    END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure get_event_registration
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`get_event_registration`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_event_registration`(
        regCode int(11),
          eTime datetime,
          eMember int(11),
          eID int(11),
          guests int(11)
      )
BEGIN
        SELECT * FROM event_registrations
          where (regCode = registration_code OR regCode IS NULL) AND
          (eTime = time OR eTime IS NULL) AND
        (eMember = member_id OR eMember IS NULL) AND
         ( eID = event_id OR eID is NULL) AND
          (guests = guest_count OR guests IS NULL);
      END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure get_feeding
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`get_feeding`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_feeding`(
    fID int(11),
      fTime datetime,
      fFood varchar(45),
      fQuantity float,
      fEmployee int(11),
      tID int(11)
  )
BEGIN
    SELECT * FROM feeding_logs
      where (fID = feeding_id OR fID IS NULL) AND
      (fTime = time OR fTime IS NULL) AND
    (fFood = food OR fFood IS NULL) AND
     (fQuantity = quantity OR fQuantity is NULL) AND 
      (fEmployee = employee_ID OR fEmployee IS NULL) AND
      (tID = tank_ID OR tID is NULL);
  END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure get_habitats
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`get_habitats`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_habitats`(
        hID int(11),
          hName varchar(45),
          hDescription varchar(200),
          hYear int(11)
      )
BEGIN
        SELECT * FROM habitats
          where (hID = habitatID OR hID IS NULL) AND
          (hName = name OR hName IS NULL) AND
        (hDescription = description OR hDescription IS NULL) AND
         (hYear = yearAdded OR hYear is NULL);
      END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure get_member
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`get_member`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_member`(
    memID int(11),
      fName varchar(45),
      lName varchar(45),
      joined date,
      exp date,
      mFee double
  )
BEGIN
    SELECT * FROM members
      where (memID = memberID OR memID IS NULL) AND
      (fname = firstName OR fName IS NULL) AND
    (lName = lastName OR lName IS NULL) AND
     ( joined = dateJoined OR joined is NULL) AND 
      ( exp = expirationDate OR exp is NULL) AND
      (mFee = fee OR mFee IS NULL);
  END$$

DELIMITER ;

-- -----------------------------------------------------
-- function get_member_count
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP function IF EXISTS `aquarium_tycoon_development`.`get_member_count`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `get_member_count`(
    memID int(11),
      fName varchar(45),
      lName varchar(45),
      joined date,
      exp date,
      mFee double
  ) RETURNS int(11)
BEGIN
    DECLARE memCount INT;
      
    SELECT count(*)
      INTO memCount
      FROM members
      where (memID = memberID OR memID IS NULL) AND
      (fname = firstName OR fName IS NULL) AND
    (lName = lastName OR lName IS NULL) AND
     ( joined = dateJoined OR joined is NULL) AND 
      ( exp = expirationDate OR exp is NULL) AND
      (mFee = fee OR mFee IS NULL);
      
      RETURN memCount;
  END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure logFeeding
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`logFeeding`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `logFeeding`(
    tank_IDID varchar(45), employee_IDID int, foodGiven varchar(45), quantityGiven double)
BEGIN
    -- general error handler
    DECLARE sqlError INT DEFAULT FALSE;

    BEGIN
    -- Error handler setups
    DECLARE EXIT HANDLER FOR SQLEXCEPTION SET sqlError = TRUE;


    INSERT INTO feeding_logs (time, food, quantity, employee_ID, tank_ID) VALUES (NOW(), foodGiven, quantityGiven, employee_IDID, tank_IDID);
    SELECT CONCAT('feeding logged for tank_ID ', tank_IDID) AS 'result';

    END;

    IF sqlError = TRUE THEN
      SELECT CONCAT('SQL Error. Feeding not logged.') AS 'result';
      END IF;

    END$$

DELIMITER ;

-- -----------------------------------------------------
-- function memsRegistered
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP function IF EXISTS `aquarium_tycoon_development`.`memsRegistered`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `memsRegistered`(eventNo int) RETURNS int(11)
BEGIN
    RETURN (SELECT SUM(guest_count + 1) FROM event_registrations where event_id = eventNo);
    END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure registrationsForEvent
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`registrationsForEvent`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `registrationsForEvent`(eventNo int)
BEGIN

        SELECT * FROM event_registrations
          WHERE event_id = eventNo
            ORDER BY time desc;

        END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure registrationsForMember
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`registrationsForMember`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `registrationsForMember`(memberID int)
BEGIN

        SELECT * FROM event_registrations
          WHERE member_id = memberID
            ORDER BY time desc;

        END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure renewMember
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`renewMember`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `renewMember`(memID int, Newfee float)
BEGIN 
    DECLARE memberExist INT DEFAULT 0;
    SET memberExist = (SELECT COUNT(*) FROM members WHERE memID = memberID);

    UPDATE members
    SET expirationDate = DATE_ADD(expirationDate, INTERVAL 18 month), fee = newFee
    WHERE memID = memberID;

    IF memberExist = 0 THEN 
      SELECT CONCAT('Member ', memID, ' does not exist') as 'result';
    ELSEIF memberExist = 1 THEN
      SELECT CONCAT('Member ', memID, ' has renewed their membership.') as 'result';
    END IF;
    END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure upcoming_events
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`upcoming_events`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `upcoming_events`()
BEGIN
SELECT * FROM events WHERE event_date >= CURDATE() ORDER BY event_date asc;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure updateEventDescription
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`updateEventDescription`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `updateEventDescription`(eventNo int, newDescription varchar(45))
BEGIN
    DECLARE eventExist INT DEFAULT 0;
    SET eventExist = (SELECT COUNT(*) FROM events WHERE eventNo = event_ID);

    UPDATE events
    SET description = newDescription WHERE eventNo = event_ID;

    IF eventExist = 0 THEN
      SELECT CONCAT('Event ', eventNo, ' does not exist') as 'result';
    ELSEIF eventExist = 1 THEN
      SELECT CONCAT('Event ', eventNo, '''s description updated to ', newDescription) as 'result';
    END IF;
    END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure updateEventManager
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`updateEventManager`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `updateEventManager`(eventNo int, newManager int)
BEGIN
    DECLARE eventExist INT DEFAULT 0;
    DECLARE employeeExist INT DEFAULT 0;
    SET eventExist = (SELECT COUNT(*) FROM events WHERE eventNo = event_ID);
    SET employeeExist = (SELECT COUNT(*) FROM employees WHERE newManager = employeeID);

    UPDATE events
    SET managing_employee = newManager WHERE eventNo = event_ID;

    IF eventExist = 0 THEN
      SELECT CONCAT('Event ', eventNo, ' does not exist') as 'result';
    ELSEIF employeeExist = 0 THEN
      SELECT CONCAT('Employee ', newManger, ' does not exist. Pick a different manager') as 'result';
    ELSEIF eventExist = 1 THEN
      SELECT CONCAT('Event ', eventNo, 'is now managed by employee', newManager) as 'result';
    END IF;
    END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure updateEventTitle
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`updateEventTitle`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `updateEventTitle`(eventNo int, newTitle varchar(45))
BEGIN
    DECLARE eventExist INT DEFAULT 0;
    SET eventExist = (SELECT COUNT(*) FROM events WHERE eventNo = event_ID);

    UPDATE events
    SET title = newTitle WHERE eventNo = event_ID;

    IF eventExist = 0 THEN
      SELECT CONCAT('Event ', eventNo, ' does not exist') as 'result';
    ELSEIF eventExist = 1 THEN
      SELECT CONCAT('Event ', eventNo, '''s title updated to ', newTitle) as 'result';
    END IF;
    END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure updateHabitatDescription
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`updateHabitatDescription`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `updateHabitatDescription`(ID int, newDescription varchar(200))
BEGIN
      DECLARE habitatExist INT DEFAULT 0;
      SET habitatExist = (SELECT COUNT(*) FROM habitats WHERE habitatID = ID);

      UPDATE habitats
      SET description = newDescription WHERE habitatID = ID;

      IF habitatExist = 0 THEN
        SELECT CONCAT('Habitat ', ID, ' does not exist') as 'result';
      ELSEIF employeeExist = 1 THEN
        SELECT CONCAT('Habitat ', ID, '''s description updated to ', newDescription) as 'result';
      END IF;
      END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure updateHabitatName
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`updateHabitatName`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `updateHabitatName`(ID int, newName varchar(45))
BEGIN
      DECLARE habitatExist INT DEFAULT 0;
      SET habitatExist = (SELECT COUNT(*) FROM habitats WHERE habitatID = ID);

      UPDATE habitats
      SET name = newName WHERE habitatID = ID;

      IF habitatExist = 0 THEN
        SELECT CONCAT('Habitat ', ID, ' does not exist') as 'result';
      ELSEIF employeeExist = 1 THEN
        SELECT CONCAT('Habitat ', ID, '''s name updated to ', newName) as 'result';
      END IF;
      END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure update_animal
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`update_animal`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `update_animal`(
        a_id int(11),
        a_name varchar(45),
        a_species varchar(45),
        t_id int(11),
        birthday dateTime
      )
BEGIN
        UPDATE animals
         SET name = IFNULL(a_name, name),
             species    = IFNULL(a_species, species),
             tank   = IFNULL(t_id, tank),
             birthDate = IFNULL(birthday, birth_date)
       WHERE animal_id = a_id;
      END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure viewDateFeedings
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`viewDateFeedings`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `viewDateFeedings`(feedingDate DATE)
BEGIN

    DECLARE notFound INT DEFAULT FALSE;

    SET notFound = (SELECT COUNT(*) FROM feeding_logs where DATE(time) = feedingDate);

    SELECT * FROM feeding_logs
      WHERE DATE(time) = feedingDate
        ORDER BY time DESC;


      IF notFound = 0 THEN
        SELECT CONCAT('No feedings on ', feedingDate) AS 'result';
      END IF;
    END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure viewEmployeeFeedings
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`viewEmployeeFeedings`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `viewEmployeeFeedings`(ID int)
BEGIN

    DECLARE notFound INT DEFAULT FALSE;

    SET notFound = (SELECT COUNT(*) FROM feeding_logs where ID = employee_ID);

    SELECT * FROM feeding_logs
      WHERE ID = employee_ID
        ORDER BY time DESC;


      IF notFound = 0 THEN
        SELECT CONCAT('Employee "', ID, '" does not exist.') AS 'result';
      END IF;
    END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure viewEvents
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`viewEvents`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `viewEvents`()
BEGIN

    SELECT * FROM events
      ORDER BY event_date DESC;
    END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure viewEventsByDate
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`viewEventsByDate`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `viewEventsByDate`(Eyear int, Emonth int)
BEGIN

    SELECT * FROM events
      WHERE MONTH(event_date) = Emonth AND YEAR(event_date) = Eyear
        ORDER BY time DESC;

    END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure viewEventsByManager
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`viewEventsByManager`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `viewEventsByManager`(ID int)
BEGIN

    DECLARE notFound INT DEFAULT FALSE;

    SET notFound = (SELECT COUNT(*) FROM events where managing_employee = ID);

    SELECT * FROM events
      WHERE ID = Managingemployee
        ORDER BY time DESC;


      IF notFound = 0 THEN
        SELECT CONCAT('Employee "', ID, '" does not have any upcoming events.') AS 'result';
      END IF;
    END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure viewEventsByPrice
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`viewEventsByPrice`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `viewEventsByPrice`(low float, high float)
BEGIN

    SELECT * FROM events
      WHERE price >= low AND price <=high
        ORDER BY time DESC;

    END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure viewHabitat
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`viewHabitat`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `viewHabitat`()
BEGIN

      -- general error handler
      DECLARE sqlError INT DEFAULT FALSE;

      SELECT * FROM habitats;

      END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure viewHabitatByName
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`viewHabitatByName`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `viewHabitatByName`(habitatName VARCHAR(45))
BEGIN

        DECLARE notFound INT DEFAULT FALSE;

        SET notFound = (SELECT COUNT(*) FROM habitats where name = habitatName);

        SELECT * FROM habitats
        WHERE name = habitatName;

        IF notFound = 0 THEN
          SELECT CONCAT('Habitat "', habitatName, '" does not exist.') AS 'result';
        END IF;

      END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure viewHabitatByYear
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`viewHabitatByYear`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `viewHabitatByYear`(constructionYear int)
BEGIN

      DECLARE notFound INT DEFAULT FALSE;

      SET notFound = (SELECT COUNT(*) FROM habitats where yearAdded = constructionYear);

      SELECT * FROM habitats
        WHERE yearAdded = constructionYear;

      IF notFound = 0 THEN
        SELECT CONCAT('No habitats built in ', constructionYear) AS 'result';
      END IF;

    END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure viewMembers
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`viewMembers`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `viewMembers`()
BEGIN 

    SELECT * FROM members
      ORDER BY dateJoined DESC;
    END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure viewTankFeedings
-- -----------------------------------------------------

USE `aquarium_tycoon_development`;
DROP procedure IF EXISTS `aquarium_tycoon_development`.`viewTankFeedings`;

DELIMITER $$
USE `aquarium_tycoon_development`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `viewTankFeedings`(ID int)
BEGIN

    DECLARE notFound INT DEFAULT FALSE;

    SET notFound = (SELECT COUNT(*) FROM feeding_logs where ID = tank_ID);

    SELECT * FROM feeding_logs
      WHERE ID = tank_ID
        ORDER BY time DESC;


      IF notFound = 0 THEN
        SELECT CONCAT('Tank "', ID, '" does not exist.') AS 'result';
      END IF;
    END$$

DELIMITER ;
USE `aquarium_tycoon_development`;

DELIMITER $$

USE `aquarium_tycoon_development`$$
DROP TRIGGER IF EXISTS `aquarium_tycoon_development`.`add_habitat` $$
USE `aquarium_tycoon_development`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `aquarium_tycoon_development`.`add_habitat`
BEFORE INSERT ON `aquarium_tycoon_development`.`habitats`
FOR EACH ROW
BEGIN
  IF New.name = '' THEN
    SIGNAL SQLSTATE 'HY000' -- 'HY000' indicates a general error
      SET MESSAGE_TEXT = 'Must input a title';
  ELSEIF New.description = '' THEN
    SIGNAL SQLSTATE 'HY000'
      SET MESSAGE_TEXT = 'Must input a description';  
  END IF;
end$$


USE `aquarium_tycoon_development`$$
DROP TRIGGER IF EXISTS `aquarium_tycoon_development`.`update_habitat` $$
USE `aquarium_tycoon_development`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `aquarium_tycoon_development`.`update_habitat`
BEFORE UPDATE ON `aquarium_tycoon_development`.`habitats`
FOR EACH ROW
BEGIN
  IF New.name = '' THEN
    SIGNAL SQLSTATE 'HY000' -- 'HY000' indicates a general error
      SET MESSAGE_TEXT = 'Must input a title';
  ELSEIF New.description = '' THEN
    SIGNAL SQLSTATE 'HY000'
      SET MESSAGE_TEXT = 'Must input a description';    
  END IF;

end$$


USE `aquarium_tycoon_development`$$
DROP TRIGGER IF EXISTS `aquarium_tycoon_development`.`add_tank` $$
USE `aquarium_tycoon_development`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `aquarium_tycoon_development`.`add_tank`
BEFORE INSERT ON `aquarium_tycoon_development`.`tanks`
FOR EACH ROW
BEGIN
  IF New.name = '' THEN
    SIGNAL SQLSTATE 'HY000' -- 'HY000' indicates a general error
      SET MESSAGE_TEXT = 'Must input a title';
  ELSEIF New.description = '' THEN
    SIGNAL SQLSTATE 'HY000'
      SET MESSAGE_TEXT = 'Must input a description';  
  ELSEIF New.volume < 500 OR New.Volume > 100000 THEN
    SIGNAL SQLSTATE 'HY000'
      SET MESSAGE_TEXT = 'Volume must be between 500 and 100,000 gallons';
  ELSEIF New.volume IS NULL THEN
    SIGNAL SQLSTATE 'HY000'
      SET MESSAGE_TEXT = 'Must input volume';
  ELSEIF New.temperature IS NULL THEN
    SIGNAL SQLSTATE 'HY000'
  	SET MESSAGE_TEXT = 'Must input a temperature';
  ELSEIF New.temperature < 0 OR New.Temperature > 100 THEN
    SIGNAL SQLSTATE 'HY000'
  	SET MESSAGE_TEXT = 'Temerature must be between 0 and 100';	
  END IF;

end$$


USE `aquarium_tycoon_development`$$
DROP TRIGGER IF EXISTS `aquarium_tycoon_development`.`update_tank` $$
USE `aquarium_tycoon_development`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `aquarium_tycoon_development`.`update_tank`
BEFORE UPDATE ON `aquarium_tycoon_development`.`tanks`
FOR EACH ROW
BEGIN
  IF New.name = '' THEN
    SIGNAL SQLSTATE 'HY000' -- 'HY000' indicates a general error
      SET MESSAGE_TEXT = 'Must input a title';
  ELSEIF New.description = '' THEN
    SIGNAL SQLSTATE 'HY000'
      SET MESSAGE_TEXT = 'Must input a description';  
  ELSEIF New.volume < 500 OR New.Volume > 100000 THEN
    SIGNAL SQLSTATE 'HY000'
      SET MESSAGE_TEXT = 'Volume must be between 500 and 100,000 gallons';
  ELSEIF New.volume IS NULL THEN
    SIGNAL SQLSTATE 'HY000'
      SET MESSAGE_TEXT = 'Must input volume';
  ELSEIF New.temperature IS NULL THEN
    SIGNAL SQLSTATE 'HY000'
  	SET MESSAGE_TEXT = 'Must input a temperature';
  ELSEIF New.temperature < 0 OR New.Temperature > 100 THEN
    SIGNAL SQLSTATE 'HY000'
  	SET MESSAGE_TEXT = 'Temerature must be between 0 and 100';	
  ELSEIF New.volume < Old.volume THEN
    SIGNAL SQLSTATE 'HY000'
    	SET MESSAGE_TEXT = 'Cannot shrink a tank.';
  END IF;

end$$


USE `aquarium_tycoon_development`$$
DROP TRIGGER IF EXISTS `aquarium_tycoon_development`.`add_animal` $$
USE `aquarium_tycoon_development`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `aquarium_tycoon_development`.`add_animal`
BEFORE INSERT ON `aquarium_tycoon_development`.`animals`
FOR EACH ROW
BEGIN
  IF New.species = '' THEN
    SIGNAL SQLSTATE 'HY000' -- 'HY000' indicates a general error
      SET MESSAGE_TEXT = 'Must input a species';
  ELSEIF New.birth_date > CURDATE() THEN
    SIGNAL SQLSTATE 'HY000'
      SET MESSAGE_TEXT = 'Can only add animals that have already been born.';  
  END IF;
  END$$


USE `aquarium_tycoon_development`$$
DROP TRIGGER IF EXISTS `aquarium_tycoon_development`.`update_animal` $$
USE `aquarium_tycoon_development`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `aquarium_tycoon_development`.`update_animal`
BEFORE UPDATE ON `aquarium_tycoon_development`.`animals`
FOR EACH ROW
BEGIN
  IF New.species != Old.species THEN
    SIGNAL SQLSTATE 'HY000' -- 'HY000' indicates a general error
      SET MESSAGE_TEXT = 'Cannot change species';
  ELSEIF New.birth_date != Old.birth_date THEN
    SIGNAL SQLSTATE 'HY000'
      SET MESSAGE_TEXT = 'Cannot change birthday';  
  END IF;
END$$


USE `aquarium_tycoon_development`$$
DROP TRIGGER IF EXISTS `aquarium_tycoon_development`.`add_employee` $$
USE `aquarium_tycoon_development`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `aquarium_tycoon_development`.`add_employee`
BEFORE INSERT ON `aquarium_tycoon_development`.`employees`
FOR EACH ROW
BEGIN
  IF New.salary < 40000 THEN
    SIGNAL SQLSTATE 'HY000' -- 'HY000' indicates a general error
      SET MESSAGE_TEXT = '$40,000.00 Is the minimum salary';
  ELSEIF New.first_name = '' THEN
	SIGNAL SQLSTATE 'HY000'
		SET MESSAGE_TEXT = 'Must insert first name';
  ELSEIF New.last_name = '' THEN
	SIGNAL SQLSTATE 'HY000'
		SET MESSAGE_TEXT = 'Must insert last name';
  END IF;
  END$$


USE `aquarium_tycoon_development`$$
DROP TRIGGER IF EXISTS `aquarium_tycoon_development`.`update_employee` $$
USE `aquarium_tycoon_development`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `aquarium_tycoon_development`.`update_employee`
BEFORE UPDATE ON `aquarium_tycoon_development`.`employees`
FOR EACH ROW
BEGIN
  IF New.salary < 40000 THEN
    SIGNAL SQLSTATE 'HY000' -- 'HY000' indicates a general error
      SET MESSAGE_TEXT = '$40,000.00 Is the minimum salary';
  ELSEIF New.fired_date IS NOT NULL AND Old.fired_date IS NOT NULL THEN
    SIGNAL SQLSTATE 'HY000'
      SET MESSAGE_TEXT = 'Cannot refire this employee';
  ELSEIF New.fired_date IS NOT NULL AND events_managed(New.employee_id) > 0 THEN
	SIGNAL SQLSTATE 'HY000'
		SET MESSAGE_TEXT = 'Cannot fire this employee until all events under their management are transferred to another employee';
  END IF;
  END$$


USE `aquarium_tycoon_development`$$
DROP TRIGGER IF EXISTS `aquarium_tycoon_development`.`invalid_event_insert` $$
USE `aquarium_tycoon_development`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `aquarium_tycoon_development`.`invalid_event_insert`
BEFORE INSERT ON `aquarium_tycoon_development`.`events`
FOR EACH ROW
BEGIN
  IF New.managing_employee NOT IN (SELECT employee_id FROM employees) THEN
    SIGNAL SQLSTATE 'HY000' -- 'HY000' indicates a general error
      SET MESSAGE_TEXT = 'Must have a valid managing employee';
  ELSEIF New.event_id IN (SELECT event_id from events) THEN
  SIGNAL SQLSTATE 'HY000'
    SET MESSAGE_TEXT = 'An event with the given ID already exists';
  ELSEIF New.title = '' THEN
  SIGNAL SQLSTATE 'HY000'
    SET MESSAGE_TEXT = 'Must have an event title';
  ELSEIF 0 < (SELECT COUNT(*) FROM events WHERE event_id != New.event_id AND DATE(event_date) = DATE(New.event_date)) THEN
   SIGNAL SQLSTATE 'HY000'
    SET MESSAGE_TEXT = 'There is already an event scheduled for that date';
  ELSEIF 0 > New.max_guests_per_person THEN
  SIGNAL SQLSTATE 'HY000'
    SET MESSAGE_TEXT = 'Max guests per person must be at least 0';
  ELSEIF New.price < 0 THEN
  SIGNAL SQLSTATE 'HY000'
    SET MESSAGE_TEXT = 'Must have price listed of at least 0';
  ELSEIF DATE(New.event_date) <= CURDATE() THEN
   SIGNAL SQLSTATE 'HY000'
    SET MESSAGE_TEXT = 'Event must take place after today';
  ELSEIF New.max_attending <= New.max_guests_per_person THEN
   SIGNAL SQLSTATE 'HY000'
    SET MESSAGE_TEXT = 'Capacity of event must be greater than the number of guests per person';
  END IF;
  END$$


USE `aquarium_tycoon_development`$$
DROP TRIGGER IF EXISTS `aquarium_tycoon_development`.`invalid_event_update` $$
USE `aquarium_tycoon_development`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `aquarium_tycoon_development`.`invalid_event_update`
BEFORE UPDATE ON `aquarium_tycoon_development`.`events`
FOR EACH ROW
BEGIN
  IF New.managing_employee NOT IN (SELECT employee_id FROM employees) THEN
    SIGNAL SQLSTATE 'HY000' -- 'HY000' indicates a general error
      SET MESSAGE_TEXT = 'Must have a valid managing employee';
  ELSEIF New.title = '' THEN
  SIGNAL SQLSTATE 'HY000'
    SET MESSAGE_TEXT = 'Must have an event title';
  ELSEIF 0 > New.max_guests_per_person THEN
  SIGNAL SQLSTATE 'HY000'
    SET MESSAGE_TEXT = 'Max guests per person must be at least 0';
  ELSEIF New.price < 0 THEN
  SIGNAL SQLSTATE 'HY000'
    SET MESSAGE_TEXT = 'Must have price listed of at least 0';
  ELSEIF DATE(New.event_date) <= CURDATE() THEN
   SIGNAL SQLSTATE 'HY000'
    SET MESSAGE_TEXT = 'Event must take place after today';
  ELSEIF New.event_date < Old.event_date THEN
   SIGNAL SQLSTATE 'HY000'
    SET MESSAGE_TEXT = 'Can only move an event to a later date';
  ELSEIF New.max_attending < Old.max_attending THEN
   SIGNAL SQLSTATE 'HY000'
    SET MESSAGE_TEXT = 'Can only increase the max number of guests';
  ELSEIF 0 < (SELECT COUNT(*) FROM events WHERE event_id != New.event_id AND DATE(event_date) =DATE(New.event_date)) THEN
   SIGNAL SQLSTATE 'HY000'
    SET MESSAGE_TEXT = 'There is already an event scheduled for that date';
  ELSEIF New.max_attending <= New.max_guests_per_person THEN
   SIGNAL SQLSTATE 'HY000'
    SET MESSAGE_TEXT = 'Capacity of event must be greater than the number of guests per person';
  END IF;
  END$$


USE `aquarium_tycoon_development`$$
DROP TRIGGER IF EXISTS `aquarium_tycoon_development`.`invalid_member_insert` $$
USE `aquarium_tycoon_development`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `aquarium_tycoon_development`.`invalid_member_insert`
BEFORE INSERT ON `aquarium_tycoon_development`.`members`
FOR EACH ROW
BEGIN
  IF New.fee < 100 THEN
    SIGNAL SQLSTATE 'HY000' -- 'HY000' indicates a general error
      SET MESSAGE_TEXT = 'Fee must be greater than $100';
        ELSEIF New.first_name = '' THEN
    SIGNAL SQLSTATE 'HY000'
      SET MESSAGE_TEXT = 'First name must be input';  
    ELSEIF New.last_name = '' THEN
    SIGNAL SQLSTATE 'HY000'
      SET MESSAGE_TEXT = 'Last name must be input';   
  END IF;
  END$$


USE `aquarium_tycoon_development`$$
DROP TRIGGER IF EXISTS `aquarium_tycoon_development`.`invalid_member_update` $$
USE `aquarium_tycoon_development`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `aquarium_tycoon_development`.`invalid_member_update`
BEFORE UPDATE ON `aquarium_tycoon_development`.`members`
FOR EACH ROW
BEGIN
  IF New.fee < 100 THEN
    SIGNAL SQLSTATE 'HY000' -- 'HY000' indicates a general error
      SET MESSAGE_TEXT = 'Fee must be greater than $100';
  ELSEIF New.first_name = '' THEN
    SIGNAL SQLSTATE 'HY000'
      SET MESSAGE_TEXT = 'First name must be input';  
    ELSEIF New.last_name = '' THEN
    SIGNAL SQLSTATE 'HY000'
      SET MESSAGE_TEXT = 'Last name must be input';    
  END IF;
  END$$


USE `aquarium_tycoon_development`$$
DROP TRIGGER IF EXISTS `aquarium_tycoon_development`.`add_registration_guests` $$
USE `aquarium_tycoon_development`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `aquarium_tycoon_development`.`add_registration_guests`
BEFORE INSERT ON `aquarium_tycoon_development`.`event_registrations`
FOR EACH ROW
BEGIN
DECLARE maxGuestsTotal INT DEFAULT 0;
DECLARE guestsSoFar INT DEFAULT 0;
DECLARE maxGuest INT DEFAULT 0;

SET maxGuestsTotal = (SELECT max_attending FROM events where event_id = New.event_id);
SET maxGuest = (SELECT max_guests_per_person FROM events where event_id = New.event_id);
SET guestsSoFar = (SELECT SUM(guest_count + 1) FROM event_registrations where event_id= New.event_id);
  IF New.guest_count < 0 THEN
    SIGNAL SQLSTATE 'HY000' -- 'HY000' indicates a general error
      SET MESSAGE_TEXT = 'Guest count must be at least 0';
  ELSEIF New.member_id IN (SELECT member_id FROM event_registrations WHERE New.event_id = event_id) THEN
    SIGNAL SQLSTATE 'HY000'
      SET MESSAGE_TEXT = 'This member is already registered for this event';

  ELSEIF New.member_id NOT IN (SELECT member_id from members) THEN
  SIGNAL SQLSTATE 'HY000'
    SET MESSAGE_TEXT = 'Not a valid member';
  ELSEIF New.event_id NOT IN (SELECT event_id from events) THEN
  SIGNAL SQLSTATE 'HY000'
    SET MESSAGE_TEXT = 'Not a valid event';
  ELSEIF New.guest_count > maxGuest THEN
  SIGNAL SQLSTATE 'HY000'
    SET MESSAGE_TEXT = 'Number of guests exceeds limit for this event';
  ELSEIF New.guest_count + guestsSoFar > maxGuestsTotal THEN
  SIGNAL SQLSTATE 'HY000'
    SET MESSAGE_TEXT = 'There are not enough tickets for you and your guests';
  ELSEIF (SELECT DATE(event_date) FROM events WHERE event_id = New.event_id) > (SELECT DATE(expiration_date) FROM members WHERE member_id = new.member_id) THEN
    SIGNAL SQLSTATE 'HY000'
    SET MESSAGE_TEXT = 'That member must renew their membership in order to attend.';  
  END IF;
END$$


USE `aquarium_tycoon_development`$$
DROP TRIGGER IF EXISTS `aquarium_tycoon_development`.`update_registration_guests` $$
USE `aquarium_tycoon_development`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `aquarium_tycoon_development`.`update_registration_guests`
BEFORE UPDATE ON `aquarium_tycoon_development`.`event_registrations`
FOR EACH ROW
BEGIN
  IF New.guest_count < 0 THEN
    SIGNAL SQLSTATE 'HY000' -- 'HY000' indicates a general error
      SET MESSAGE_TEXT = 'Guest count must be at least 0';
  ELSEIF New.event_id != Old.event_id THEN
    SIGNAL SQLSTATE 'HY000'
      SET MESSAGE_TEXT = 'To register for a different event, cancel this registration and start a new one.';  
 ELSEIF New.member_id != Old.member_id THEN
    SIGNAL SQLSTATE 'HY000'
      SET MESSAGE_TEXT = 'To register a different member, cancel this registration and start a new one.';
 ELSEIF New.guest_count > (SELECT max_guests_per_person FROM events WHERE event_id = New.event_id) THEN
    SIGNAL SQLSTATE 'HY000'
      SET MESSAGE_TEXT = 'Exceeding guest limit';
  END IF;
  END$$


USE `aquarium_tycoon_development`$$
DROP TRIGGER IF EXISTS `aquarium_tycoon_development`.`add_feeding` $$
USE `aquarium_tycoon_development`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `aquarium_tycoon_development`.`add_feeding`
BEFORE INSERT ON `aquarium_tycoon_development`.`feeding_logs`
FOR EACH ROW
BEGIN
  IF New.food = '' THEN
    SIGNAL SQLSTATE 'HY000' -- 'HY000' indicates a general error
      SET MESSAGE_TEXT = 'Must input food type.';
  ELSEIF New.quantity IS NULL THEN
    SIGNAL SQLSTATE 'HY000'
      SET MESSAGE_TEXT = 'Must input a quantity';  
  ELSEIF New.quantity <= 0 THEN 
    SIGNAL SQLSTATE 'HY000'
      SET MESSAGE_TEXT = 'Must input a valid quantity';    
  END IF;

end$$


USE `aquarium_tycoon_development`$$
DROP TRIGGER IF EXISTS `aquarium_tycoon_development`.`add_treatment` $$
USE `aquarium_tycoon_development`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `aquarium_tycoon_development`.`add_treatment`
BEFORE INSERT ON `aquarium_tycoon_development`.`treatment_logs`
FOR EACH ROW
BEGIN
  IF New.medicine = '' THEN
    SIGNAL SQLSTATE 'HY000' -- 'HY000' indicates a general error
      SET MESSAGE_TEXT = 'Must input a medicine';
  ELSEIF New.quantity IS NULL THEN
    SIGNAL SQLSTATE 'HY000'
      SET MESSAGE_TEXT = 'Must input a quantity';  
  ELSEIF New.quantity < 0 THEN
    SIGNAL SQLSTATE 'HY000'
      SET MESSAGE_TEXT = 'Must input a valid quantity';   
  END IF;

end$$


DELIMITER ;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;