/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;


--
-- Create schema water_uwi
--

CREATE DATABASE IF NOT EXISTS water_uwi;
USE water_uwi;

--
-- Definition of table `delivery`
--

DROP TABLE IF EXISTS `delivery`;
CREATE TABLE `delivery` (
  `delivery_id` INT(11) NOT NULL AUTO_INCREMENT,
  `delivery_date` DATE DEFAULT NULL,
  `delivery_time` TIME DEFAULT NULL,
  `truck_id` VARCHAR(6) NOT NULL,
  `location_id` INT(11) NOT NULL,
  `tank_id` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`delivery_id`),
  KEY `idtruck` (`truck_id`),
  KEY `dev_loc` (`location_id`),
  KEY `tank_d` (`tank_id`),
  CONSTRAINT `idtruck` FOREIGN KEY (`truck_id`) REFERENCES `truck` (`truck_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `dev_loc` FOREIGN KEY (`location_id`) REFERENCES `tank_location` (`tank_location_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `tank_d` FOREIGN KEY (`tank_id`) REFERENCES `tank` (`tank_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `delivery`
--

/*!40000 ALTER TABLE `delivery` DISABLE KEYS */;
INSERT INTO `delivery` (`delivery_id`,`delivery_date`,`delivery_time`,`truck_id`,`location_id`,`tank_id`) VALUES
 (1,'2010-02-05','03:11:39','TR123',2325,'43434');
/*!40000 ALTER TABLE `delivery` ENABLE KEYS */;


--
-- Definition of table `location`
--

DROP TABLE IF EXISTS `location`;
CREATE TABLE `location` (
  `location_id` INT(11) NOT NULL AUTO_INCREMENT,
  `location_code` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `location`
--

/*!40000 ALTER TABLE `location` DISABLE KEYS */;
INSERT INTO `location` (`location_id`,`location_code`) VALUES
 (1,'Mary Secole'),
 (2,'Taylor'),
 (3,'Rex'),
 (4,'Preston'),
 (5,'Chanc');
/*!40000 ALTER TABLE `location` ENABLE KEYS */;


--
-- Definition of table `phone_call`
--

DROP TABLE IF EXISTS `phone_call`;
CREATE TABLE `phone_call` (
  `call_id` INT(11) NOT NULL AUTO_INCREMENT,
  `location_id` INT(11) NOT NULL,
  `call_date` datetime NOT NULL,
  `status` INT(11) DEFAULT NULL,
  `notes` VARCHAR(500) DEFAULT NULL,
  PRIMARY KEY (`call_id`),
  KEY `call_loc_id` (`location_id`),
  KEY `stat` (`status`),
  CONSTRAINT `call_loc_id` FOREIGN KEY (`location_id`) REFERENCES `tank_location` (`tank_location_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `stat` FOREIGN KEY (`status`) REFERENCES `status` (`status_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `phone_call`
--

/*!40000 ALTER TABLE `phone_call` DISABLE KEYS */;
INSERT INTO `phone_call` (`call_id`,`location_id`,`call_date`,`status`,`notes`) VALUES
 (1,2324,'2010-02-05 02:41:36',1,NULL),
 (12,2325,'2010-02-12 03:08:45',2,NULL);
/*!40000 ALTER TABLE `phone_call` ENABLE KEYS */;


--
-- Definition of table `status`
--

DROP TABLE IF EXISTS `status`;
CREATE TABLE `status` (
  `status_id` INT(11) NOT NULL AUTO_INCREMENT,
  `description` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`status_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `status`
--

/*!40000 ALTER TABLE `status` DISABLE KEYS */;
INSERT INTO `status` (`status_id`,`description`) VALUES
 (1,'empty'),
 (2,'full');
/*!40000 ALTER TABLE `status` ENABLE KEYS */;


--
-- Definition of table `tank`
--

DROP TABLE IF EXISTS `tank`;
CREATE TABLE `tank` (
  `tank_id` VARCHAR(10) NOT NULL,
  `location_id` INT(11) NOT NULL,
  `capacity` INT(11) NOT NULL,
  `capacity_measure` INT(11) NOT NULL,
  PRIMARY KEY (`tank_id`),
  KEY `tank_loc_id` (`location_id`),
  CONSTRAINT `tank_loc_id` FOREIGN KEY (`location_id`) REFERENCES `tank_location` (`tank_location_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tank`
--

/*!40000 ALTER TABLE `tank` DISABLE KEYS */;
INSERT INTO `tank` (`tank_id`,`location_id`,`capacity`,`capacity_measure`) VALUES
 ('',2325,0,0),
 ('43434',2324,232323,2323);
/*!40000 ALTER TABLE `tank` ENABLE KEYS */;


--
-- Definition of table `tank_location`
--

DROP TABLE IF EXISTS `tank_location`;
CREATE TABLE `tank_location` (
  `tank_location_id` INT(11) NOT NULL AUTO_INCREMENT,
  `location_id` INT(11) NOT NULL,
  `description` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`tank_location_id`),
  KEY `tank_loc` (`location_id`),
  CONSTRAINT `tank_loc` FOREIGN KEY (`location_id`) REFERENCES `location` (`location_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2326 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tank_location`
--

/*!40000 ALTER TABLE `tank_location` DISABLE KEYS */;
INSERT INTO `tank_location` (`tank_location_id`,`location_id`,`description`) VALUES
 (1,1,'2 Floor'),
 (2324,1,'Front Gate'),
 (2325,2,'Roosters');
/*!40000 ALTER TABLE `tank_location` ENABLE KEYS */;


--
-- Definition of table `truck`
--

DROP TABLE IF EXISTS `truck`;
CREATE TABLE `truck` (
  `truck_id` VARCHAR(6) NOT NULL,
  `driver_name` VARCHAR(45) DEFAULT NULL,
  `capacity` INT(11) DEFAULT NULL,
  `capacity_measure` INT(11) DEFAULT NULL,
  PRIMARY KEY (`truck_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `truck`
--

/*!40000 ALTER TABLE `truck` DISABLE KEYS */;
INSERT INTO `truck` (`truck_id`,`driver_name`,`capacity`,`capacity_measure`) VALUES
 ('FT322','jim brown',22221,11223),
 ('ST453','andre pink',22556,343322),
 ('TR123','john brown',232323,23231212),
 ('TR234','joe black',22222,3455555),
 ('WQ232','daine k',3434322,34523);
/*!40000 ALTER TABLE `truck` ENABLE KEYS */;


--
-- Definition of table `user`
--

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `user_id` INT(11) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(45) NOT NULL,
  `user_pass` VARCHAR(45) NOT NULL,
  `user_type` INT(11) NOT NULL,
  PRIMARY KEY (`user_id`),
  KEY `u_type` (`user_type`),
  CONSTRAINT `u_type` FOREIGN KEY (`user_type`) REFERENCES `user_type` (`usertype_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `user`
--

/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` (`user_id`,`username`,`user_pass`,`user_type`) VALUES
 (1,'admin','password',1),
 (2,'test','test',2);
/*!40000 ALTER TABLE `user` ENABLE KEYS */;


--
-- Definition of table `user_type`
--

DROP TABLE IF EXISTS `user_type`;
CREATE TABLE `user_type` (
  `usertype_id` INT(11) NOT NULL,
  `description` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`usertype_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `user_type`
--

/*!40000 ALTER TABLE `user_type` DISABLE KEYS */;
INSERT INTO `user_type` (`usertype_id`,`description`) VALUES
 (1,'admin'),
 (2,'normal');
/*!40000 ALTER TABLE `user_type` ENABLE KEYS */;


--
-- Definition of procedure `insert_delivery`
--

DROP PROCEDURE IF EXISTS `insert_delivery`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_delivery`(delivery_dt DATE, delivery_t TIME, truck_num VARCHAR(6),loc_id INT,tank_num VARCHAR(6))
BEGIN
    INSERT INTO delivery (delivery_date,delivery_time,truck_id,location_id,tank_id) VALUES (delivery_dt,delivery_t,truck_num,loc_id,tank_num);
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `insert_location`
--

DROP PROCEDURE IF EXISTS `insert_location`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_location`(loc_code VARCHAR(50))
BEGIN
    INSERT INTO location (location_code) VALUES (loc_code);
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `insert_phone_call`
--

DROP PROCEDURE IF EXISTS `insert_phone_call`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_phone_call`(loc_id INT,call_dt DATETIME, stat INT, nt VARCHAR(500))
BEGIN
    INSERT INTO phone_call(location_id,call_date,STATUS,notes) VALUES (loc_id,call_dt,stat,nt);
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `insert_tank`
--

DROP PROCEDURE IF EXISTS `insert_tank`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_tank`(tank_num VARCHAR(10), loc_id INT, cap INT, cap_meter INT)
BEGIN
    INSERT INTO tank (tank_id,location_id,capacity,capacity_measure) VALUES (tank_num,loc_id,cap,cap_meter);
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `insert_tank_location`
--

DROP PROCEDURE IF EXISTS `insert_tank_location`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_tank_location`(loc_id INT, descript VARCHAR(100))
BEGIN
    INSERT INTO tank_location (location_id,description) VALUES (loc_id,descript);
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `insert_truck`
--

DROP PROCEDURE IF EXISTS `insert_truck`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_truck`(truck_license VARCHAR(6), driver_nm VARCHAR(50), cap INT, cap_meter INT)
BEGIN
    INSERT INTO truck (truck_id,driver_name,capacity,capacity_measure) VALUES (tank_license,driver_nm,cap,cap_meter);
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `insert_user`
--

DROP PROCEDURE IF EXISTS `insert_user`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_user`(user_nm VARCHAR(50),user_ps VARCHAR(50), user_t INT)
BEGIN
    INSERT INTO USER(username,user_pass,user_type) VALUES (user_nm,user_ps,user_t);
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `query_location`
--

DROP PROCEDURE IF EXISTS `query_location`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `query_location`()
BEGIN
    SELECT * FROM tank_location;
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `validate_user`
--

DROP PROCEDURE IF EXISTS `validate_user`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `validate_user`(user_nm VARCHAR(50),user_ps VARCHAR(50), user_t INT)
BEGIN
    SELECT user_type FROM USER WHERE username = um AND user_pass = user_ps;
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;



/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;