CREATE DATABASE Acronyms;

USE Acronyms;

CREATE TABLE Acronym
(
	AcronymID VARCHAR(16) NOT NULL,
	Description VARCHAR(255) NOT NULL,
	IsPrimary BIT NOT NULL,
	CONSTRAINT CN_Disambiguation UNIQUE INDEX (AcronymID, Description)
);

DELIMITER //
CREATE FUNCTION AddAcronym(_AcronymID VARCHAR(16), _Description VARCHAR(255), IsFirst BIT) RETURNS INT
BEGIN

	DECLARE Result INT;
	DECLARE Existing INT;
	DECLARE ExistingAcronym INT;
	DECLARE InsertAsPrimary BIT;

	SET Result = 0;

	# Return value:
	#	0 - Success
	#	1 - Same (AcronymID, Description) pair already exists
	#	2 - Same AcronymID exists and IsPrimary=1 - must invoke with IsPrimary=0

	SET Existing = (SELECT COUNT(*) FROM Acronym WHERE AcronymID=_AcronymID AND Description=_Description);
	SET ExistingAcronym = (SELECT COUNT(*) FROM Acronym WHERE AcronymID=_AcronymID);

	IF Existing > 0 THEN 
		SET Result = 1;
	ELSEIF IsFirst=1 AND ExistingAcronym > 0 THEN 
		SET Result = 2;
	ELSE 

		SET InsertAsPrimary = 0;
		IF ExistingAcronym = 0 THEN 
			SET InsertAsPrimary = 1;
		END IF;

		INSERT INTO Acronym(AcronymID, Description, IsPrimary) VALUES (_AcronymID, _Description, InsertAsPrimary);

	END IF;

	RETURN Result;

END; //

DELIMITER //
DROP PROCEDURE SetPrimaryDescription;//

CREATE PROCEDURE SetPrimaryDescription(_AcronymID VARCHAR(16), _Description VARCHAR(255))
BEGIN

	IF EXISTS (SELECT AcronymID FROM Acronym WHERE AcronymID=_AcronymID AND Description=_Description) THEN
	
		UPDATE Acronym SET IsPrimary=0 WHERE AcronymID=_AcronymID;
		UPDATE Acronym SET IsPrimary=1 WHERE AcronymID=_AcronymID AND Description=_Description;

	END IF;

END;

DELIMITER ;
