<?php

require_once('../../php/definitions.php');

$mysqli = new mysqli(PACKAGE_LOG_DB_SERVER, PACKAGE_LOG_DB_USER, PACKAGE_LOG_DB_PASS, PACKAGE_LOG_DB_NAME);
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

if (!$mysqli->query("DROP PROCEDURE IF EXISTS Login_AgentInfoReturn") ||
    !$mysqli->query("
	
	CREATE PROCEDURE Login_AgentInfoReturn(IN useremail VARCHAR(50), IN userpassword VARCHAR(128), IN userpin VARCHAR(30))
	BEGIN

	SELECT 	`agent_key`, 
					`agent`.`email`, 
					`firstname`, 
					`lastname`, 
					`access`, 
					`agent`.`store_key`, 
					`storename`, 
					`primaryphone`, 
					`faxphone`, 
					`hoursmf`, 
					`hourssa`, 
					`hourssu`, 
					`streetaddress1`, 
					`streetaddress2`, 
					`city`, 
					`state`, 
					`zip`, 
					`agent`.`franchise_key`, 
					`store`.`printer_package_labels`,
					`username`,
					`store_email`.`password` AS 'password',
					`host`,
					`port`
	FROM `agent` 
	LEFT JOIN `store` ON `agent`.`store_key`=`store`.`store_key`
	LEFT JOIN `store_email` ON `agent`.`store_key` = `store_email`.`store_key`
	WHERE `agent`.`email` = useremail AND `agent`.`password` = userpassword AND `agent`.`pin` = userpin
	LIMIT 1;

	END;
	
	")) {
    echo "Stored procedure Login_AgentInfoReturn creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
}else{
	echo "Stored Procedure Login_AgentInfoReturn created successfully.";
}

?>