DROP PROCEDURE IF EXISTS CreateDiscusson;
DELIMITER //
CREATE PROCEDURE CreateDiscusson
(
  IN p_sid INT,
  IN p_password TEXT,
  IN p_name TEXT,
  OUT outputCode INT
)
MODIFIES SQL DATA
BEGIN

CALL `CheckIdLogin`(p_sid,p_password,outputCode);
IF outputCode = 42 THEN 
	IF  (SELECT COUNT(*) FROM membres WHERE ID = p_sid AND adminLevel >= 2) = 1 THEN
		SET outputCode :=(
			SELECT
			(
				CASE 
					WHEN LENGTH(p_name) > 255 THEN 2014
					WHEN CAST(p_name AS BINARY) NOT REGEXP BINARY '^[-a-zA-Z0-9\_ ]+$' THEN 2015
					WHEN(
						SELECT
							COUNT(*)
						FROM
							discussions
						WHERE
							name = p_name
					) > 0 THEN 2016
					ELSE 42
				END
			)
		);
	END IF;
ELSE
	SET outputCode = 2013;
END IF;

IF outputCode = 42 THEN INSERT INTO discussions(name,creatorID,dateCreation,authorizedGroupId) VALUES (CAST(p_name AS CHAR(255)),p_sid,NOW(),0); END IF;

END//
DELIMITER ;

