USE iptstat;
DELIMITER $$
ALTER TABLE `thermostat` CHANGE `ThermostatId` `ThermostatId` MEDIUMINT( 8 ) UNSIGNED NOT NULL AUTO_INCREMENT;
DROP PROCEDURE IF EXISTS sp_User_Insert_Update;
CREATE PROCEDURE sp_User_Insert_Update(IN pUserId MEDIUMINT, IN pUserName VARCHAR(20), IN pMaxNumThermostats MEDIUMINT, IN pLangCode VARCHAR(3))
BEGIN
		if pUserId is null then
			insert into `user`(UserName, MaxNumThermostats, LangCode) values(pUserName, pMaxNumThermostats, pLangCode);
			select last_insert_id();
		else
		
			update `user` set
				UserName = pUserName,
				MaxNumThermostats = pMaxNumThermostats,
        LangCode = pLangCode
			where UserId = pUserId;
		end if;
    END
$$

DROP PROCEDURE IF EXISTS sp_User_Load_All;
--
-- Definition for procedure sp_User_Load_All
--
CREATE PROCEDURE sp_User_Load_All()
BEGIN
	select UserId, UserName, FORMAT(MaxNumThermostats,0) AS MaxNumThermostats, LangCode from `user` order by UserName;
    END
$$

DROP PROCEDURE IF EXISTS sp_User_Load_Details_By_UserID;
--
-- Definition for procedure sp_User_Load_Details_By_UserID
--
CREATE PROCEDURE sp_User_Load_Details_By_UserID(IN pUserId mediumint)
BEGIN	
		SELECT UserName, IFNULL(FORMAT(MaxNumThermostats,0), 0) `MaxNumThermostats`, LangCode FROM `user`
		WHERE UserId = pUserId;
    END
$$

DROP PROCEDURE IF EXISTS sp_User_Load_Details_By_UserName;
--
-- Definition for procedure sp_User_Load_Details_By_UserName
--
CREATE PROCEDURE sp_User_Load_Details_By_UserName(IN pUserName varchar(20))
BEGIN	
		SELECT UserId, IFNULL(FORMAT(MaxNumThermostats,0), 0) `MaxNumThermostats`, LangCode FROM `user`
		WHERE UserName = pUserName;
    END
$$
