-- CREATE FIELD "partnerID" ------------------------------------
ALTER TABLE `cms__user` ADD COLUMN `partnerID` Int( 11 ) UNSIGNED NULL;
-- -------------------------------------------------------------

-- CREATE INDEX "index_partnerID1" -----------------------------
CREATE INDEX `index_partnerID1` ON `cms__user`( `partnerID` );
-- -------------------------------------------------------------

-- CREATE TABLE "smd__partner" ---------------------------------
CREATE TABLE `smd__partner` (
	`partnerID` Int( 11 ) UNSIGNED AUTO_INCREMENT NOT NULL,
	`name` varchar( 255 ) NOT NULL,
	PRIMARY KEY ( `partnerID` ) )
ENGINE = InnoDB;
-- -------------------------------------------------------------

-- CREATE TABLE "smd__organization" ----------------------------
CREATE TABLE `smd__organization` (
	`organizationID` Int( 11 ) UNSIGNED AUTO_INCREMENT NOT NULL,
	`name` VarChar( 255 ) NOT NULL,
	`parentID` Int( 11 ) UNSIGNED DEFAULT NULL,
	`partnerID` Int( 11 ) UNSIGNED NOT NULL,
	PRIMARY KEY ( `organizationID` ) )
ENGINE = InnoDB;
-- -------------------------------------------------------------

-- CREATE INDEX "index_parentID" -------------------------------
CREATE INDEX `index_parentID` ON `smd__organization`( `parentID` );
-- -------------------------------------------------------------

-- CREATE INDEX "index_partnerID" ------------------------------
CREATE INDEX `index_partnerID` ON `smd__organization`( `partnerID` );
-- -------------------------------------------------------------

-- CREATE TABLE "smd__organization_role" -----------------------
CREATE TABLE `smd__organization_role` (
	`roleID` VarChar( 255 ) NOT NULL,
	`name` VarChar( 255 ) NOT NULL,
	`description` VarChar( 255 ) NOT NULL,
	`organizationID` Int( 11 ) UNSIGNED NOT NULL,
	PRIMARY KEY ( `roleID` ) )
ENGINE = InnoDB;
-- -------------------------------------------------------------

-- CREATE INDEX "index_organizationID" -------------------------
CREATE INDEX `index_organizationID` ON `smd__organization_role`( `organizationID` );

-- CREATE TABLE "smd__user_has_organization_role" --------------
CREATE TABLE `smd__user_has_organization_role` (
	`userID` Int( 11 ) UNSIGNED NOT NULL,
	`roleID` VarChar( 255 ) NOT NULL )
ENGINE = InnoDB;
-- -------------------------------------------------------------

-- CREATE INDEX "index_userID" ---------------------------------
CREATE INDEX `index_userID` ON `smd__user_has_organization_role`( `userID` );

-- CREATE LINK "lnk_smd__partner_cms__user" --------------------
ALTER TABLE `cms__user`
	ADD CONSTRAINT `lnk_smd__partner_cms__user` FOREIGN KEY ( `partnerID` )
	REFERENCES `smd__partner`( `partnerID` )
	ON DELETE Cascade
	ON UPDATE Cascade;
-- -------------------------------------------------------------

-- CREATE LINK "lnk_smd__partner_smd__group" -------------------
ALTER TABLE `smd__organization`
	ADD CONSTRAINT `lnk_smd__partner_smd__group` FOREIGN KEY ( `partnerID` )
	REFERENCES `smd__partner`( `partnerID` )
	ON DELETE Restrict
	ON UPDATE Cascade;
-- -------------------------------------------------------------

-- CREATE LINK "lnk_smd__organization_smd__organization_role" --
ALTER TABLE `smd__organization_role`
	ADD CONSTRAINT `lnk_smd__organization_smd__organization_role` FOREIGN KEY ( `organizationID` )
	REFERENCES `smd__organization`( `organizationID` )
	ON DELETE Cascade
	ON UPDATE Cascade;
-- -------------------------------------------------------------

-- CREATE LINK "lnk_smd__organization_role_smd__user_has_organization_role"
ALTER TABLE `smd__user_has_organization_role`
	ADD CONSTRAINT `lnk_smd__organization_role_smd__user_has_organization_role` FOREIGN KEY ( `roleID` )
	REFERENCES `smd__organization_role`( `roleID` )
	ON DELETE Cascade
	ON UPDATE Cascade;
-- -------------------------------------------------------------

-- CREATE LINK "lnk_cms__user_smd__user_has_organization_role" -
ALTER TABLE `smd__user_has_organization_role`
	ADD CONSTRAINT `lnk_cms__user_smd__user_has_organization_role` FOREIGN KEY ( `userID` )
	REFERENCES `cms__user`( `userID` )
	ON DELETE Cascade
	ON UPDATE Cascade;
-- -------------------------------------------------------------

-- CREATE LINK "lnk_smd__group_smd__group" ---------------------
ALTER TABLE `smd__organization`
	ADD CONSTRAINT `lnk_smd__group_smd__group` FOREIGN KEY ( `parentID` )
	REFERENCES `smd__organization`( `organizationID` )
	ON DELETE Restrict
	ON UPDATE Cascade;
-- -------------------------------------------------------------

CREATE PROCEDURE `function_validate_partnerID` (inParentID INT, inPartnerID INT)
	BEGIN
	DECLARE pID INT;
	SELECT partnerID INTO pID
		FROM `smd__organization`
		WHERE organizationID = inParentID;

	# ha nem egyezik, hibát dobunk
	IF pID <> inPartnerID THEN
		SIGNAL SQLSTATE '23000'
 		SET MESSAGE_TEXT = 'PartnerID violation error!';
	END IF;
END

CREATE
	TRIGGER `trigger_validate_partnerID_insert` BEFORE INSERT
	ON `smd__organization`
	FOR EACH ROW
   		BEGIN
   			IF NEW.partnerID IS NOT NULL THEN
   				CALL function_validate_partnerID(NEW.parentID, NEW.partnerID);
   			END IF;
   		END;

CREATE
	TRIGGER `trigger_validate_partnerID_update` BEFORE UPDATE
	ON `smd__organization`
	FOR EACH ROW
   		BEGIN
   			IF NEW.partnerID IS NOT NULL THEN
   				CALL function_validate_partnerID(NEW.parentID, NEW.partnerID);
   			END IF;
   		END;

-- CREATE TABLE "smd__oraginaziton_role_has_resources" ---------
CREATE TABLE `smd__oraginaziton_role_has_resources` (
	`roleID` VarChar( 255 ) NOT NULL,
	`resourceID` VarChar( 100 ) NOT NULL,
	PRIMARY KEY ( `roleID`, `resourceID` ) )
ENGINE = InnoDB;
-- -------------------------------------------------------------

-- CREATE INDEX "index_resourceID1" ----------------------------
CREATE INDEX `index_resourceID1` ON `smd__oraginaziton_role_has_resources`( `resourceID` );
-- -------------------------------------------------------------

-- CREATE INDEX "index_roleID2" --------------------------------
CREATE INDEX `index_roleID2` ON `smd__oraginaziton_role_has_resources`( `roleID` );
-- -------------------------------------------------------------

-- CREATE LINK "lnk_smd__organization_role_smd__oraginaziton_role_has_resources"
ALTER TABLE `smd__oraginaziton_role_has_resources`
	ADD CONSTRAINT `lnk_smd__organization_role_smd__oraginaziton_role_has_resources` FOREIGN KEY ( `roleID` )
	REFERENCES `smd__organization_role`( `roleID` )
	ON DELETE Cascade
	ON UPDATE Cascade;
-- -------------------------------------------------------------

-- CREATE LINK "lnk_cms__resource_smd__oraginaziton_role_has_resources"
ALTER TABLE `smd__oraginaziton_role_has_resources`
	ADD CONSTRAINT `lnk_cms__resource_smd__oraginaziton_role_has_resources` FOREIGN KEY ( `resourceID` )
	REFERENCES `cms__resource`( `resourceID` )
	ON DELETE Cascade
	ON UPDATE Cascade;
-- -------------------------------------------------------------
