INSERT INTO `cms__module`(`name`) VALUES ('module.background');
SET @moduleID = LAST_INSERT_ID();

-- CREATE TABLE "smd__partner_config" --------------------------
CREATE TABLE `smd__partner_config` (
   `partnerID` Int( 11 ) UNSIGNED NOT NULL,
   `subscriptionID` Int( 11 ) UNSIGNED NULL,
   `config` JSON NULL,
   `status` Enum( 'active', 'inactive' ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'active',
   `partnerConfigID` Int( 11 ) UNSIGNED AUTO_INCREMENT NOT NULL,
   PRIMARY KEY ( `partnerConfigID` ) )
CHARACTER SET = utf8
COLLATE = utf8_general_ci
ENGINE = InnoDB;
-- -------------------------------------------------------------

-- CREATE INDEX "index_status1" --------------------------------
CREATE INDEX `index_status1` USING BTREE ON `smd__partner_config`( `status` );
-- -------------------------------------------------------------

-- CREATE INDEX "index_subscriptionID2" ------------------------
CREATE INDEX `index_subscriptionID2` USING BTREE ON `smd__partner_config`( `subscriptionID` );
-- -------------------------------------------------------------

-- CREATE INDEX "partnerID_subscriptionID_u" -------------------
CREATE UNIQUE INDEX `partnerID_subscriptionID_u` ON `smd__partner_config`( `partnerID`, `subscriptionID` );
-- -------------------------------------------------------------

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

-- CREATE TABLE "smd__default_role" ----------------------------
CREATE TABLE `smd__default_role` (
	`defaultRoleID` VarChar( 64 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
	`name` VarChar( 64 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
	`description` VarChar( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
	`status` Enum( 'active', 'inactive' ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'inactive',
	`admin` Enum( 'yes', 'no' ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'no',
	PRIMARY KEY ( `defaultRoleID` ) )
CHARACTER SET = utf8
COLLATE = utf8_general_ci
ENGINE = InnoDB;
-- -------------------------------------------------------------

-- CREATE INDEX "index_admin" ----------------------------------
CREATE INDEX `index_admin` USING BTREE ON `smd__default_role`( `admin` );
-- -------------------------------------------------------------

-- CREATE INDEX "index_name3" ----------------------------------
CREATE INDEX `index_name3` USING BTREE ON `smd__default_role`( `name` );
-- -------------------------------------------------------------

-- CREATE INDEX "index_status10" -------------------------------
CREATE INDEX `index_status10` USING BTREE ON `smd__default_role`( `status` );
-- -------------------------------------------------------------

-- CREATE TABLE "smd__default_role_has_resources" --------------
CREATE TABLE `smd__default_role_has_resources` (
	`defaultRoleID` VarChar( 64 ) NOT NULL,
	`resourceID` VarChar( 100 ) NOT NULL,
	PRIMARY KEY ( `defaultRoleID`, `resourceID` ) )
ENGINE = InnoDB;
-- -------------------------------------------------------------

-- CREATE INDEX "index_resourceID2" ----------------------------
CREATE INDEX `index_resourceID2` ON `smd__default_role_has_resources`( `resourceID` );
-- -------------------------------------------------------------

-- CREATE INDEX "index_defaultRoleID" --------------------------
CREATE INDEX `index_defaultRoleID` ON `smd__default_role_has_resources`( `defaultRoleID` );
-- -------------------------------------------------------------

-- CREATE LINK "smd__default_role_smd__default_role_has_resources_defaultRoleID"
ALTER TABLE `smd__default_role_has_resources`
	ADD CONSTRAINT `smd__default_role_smd__default_role_has_resources_defaultRoleID` FOREIGN KEY ( `defaultRoleID` )
	REFERENCES `smd__default_role`( `defaultRoleID` )
	ON DELETE Cascade
	ON UPDATE Cascade;
-- -------------------------------------------------------------

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

-- CREATE TABLE "smd__plan" ------------------------------------
CREATE TABLE `smd__plan` (
	`planID` Int( 11 ) UNSIGNED AUTO_INCREMENT NOT NULL,
	`name` VarChar( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
	`description` Text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
	`config` JSON NULL,
	`zohoPlanID` VarChar( 32 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
	PRIMARY KEY ( `planID` ),
	CONSTRAINT `unique_zohoPlanID` UNIQUE( `zohoPlanID` ) )
CHARACTER SET = utf8
COLLATE = utf8_general_ci
ENGINE = InnoDB
AUTO_INCREMENT = 1;
-- -------------------------------------------------------------

-- CREATE TABLE "smd__addon" -----------------------------------
CREATE TABLE `smd__addon` (
	`addonID` Int( 11 ) UNSIGNED AUTO_INCREMENT NOT NULL,
	`name` VarChar( 255 ) NOT NULL,
	`description` Text NULL,
	`config` JSON NULL,
	`zohoAddonID` VarChar( 32 ) NOT NULL,
	PRIMARY KEY ( `addonID` ),
	CONSTRAINT `unique_addonID` UNIQUE( `addonID` ),
	CONSTRAINT `unique_zohoAddonID` UNIQUE( `zohoAddonID` ) )
ENGINE = InnoDB;
-- -------------------------------------------------------------

-- CREATE TABLE "smd__subscription" ----------------------------
CREATE TABLE `smd__subscription` (
	`subscriptionID` Int( 11 ) UNSIGNED AUTO_INCREMENT NOT NULL,
	`name` VarChar( 255 ) NOT NULL,
	`createDate` Timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`modifyDate` Timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	`status` Enum( 'live', 'trial', 'dunning', 'unpaid', 'non_renewing', 'cancelled', 'creation_failed', 'cancelled_from_dunning', 'expired', 'trial_expired', 'future' ) NOT NULL,
	`planID` Int( 11 ) UNSIGNED NULL,
	`zohoSubscriptionID` VarChar( 32 ) NOT NULL,
	`expiryDate` Timestamp NULL,
	PRIMARY KEY ( `subscriptionID` ),
	CONSTRAINT `unique_zohoSubscriptionID` UNIQUE( `zohoSubscriptionID` ) )
ENGINE = InnoDB;
-- -------------------------------------------------------------

-- CREATE INDEX "index_createDate" -----------------------------
CREATE INDEX `index_createDate` ON `smd__subscription`( `createDate` );
-- -------------------------------------------------------------

-- CREATE INDEX "index_modifyDate" -----------------------------
CREATE INDEX `index_modifyDate` ON `smd__subscription`( `modifyDate` );
-- -------------------------------------------------------------

-- CREATE INDEX "index_productID" ------------------------------
CREATE INDEX `index_productID` ON `smd__subscription`( `planID` );
-- -------------------------------------------------------------

-- CREATE INDEX "index_status" ---------------------------------
CREATE INDEX `index_status` ON `smd__subscription`( `status` );
-- -------------------------------------------------------------

-- CREATE INDEX "index_subscriptionID" -------------------------
CREATE INDEX `index_subscriptionID` ON `smd__subscription`( `subscriptionID` );
-- -------------------------------------------------------------

-- CREATE INDEX "index_expiryDate" -----------------------------
CREATE INDEX `index_expiryDate` ON `smd__subscription`( `expiryDate` );
-- -------------------------------------------------------------

-- CREATE TABLE "smd__subscription_has_addons" -----------------
CREATE TABLE `smd__subscription_has_addons` (
	`subscriptionID` Int( 11 ) UNSIGNED NOT NULL,
	`addonID` Int( 11 ) UNSIGNED NOT NULL,
	PRIMARY KEY ( `subscriptionID`, `addonID` ) )
ENGINE = InnoDB;
-- -------------------------------------------------------------

-- CREATE INDEX "index_addonID" --------------------------------
CREATE INDEX `index_addonID` ON `smd__subscription_has_addons`( `addonID` );
-- -------------------------------------------------------------

-- CREATE INDEX "index_productID1" -----------------------------
CREATE INDEX `index_productID1` ON `smd__subscription_has_addons`( `subscriptionID` );
-- -------------------------------------------------------------

-- CREATE LINK "smd_addon_smd__product_has_addons_addonID_addonID"
ALTER TABLE `smd__subscription_has_addons`
	ADD CONSTRAINT `smd_addon_smd__product_has_addons_addonID_addonID` FOREIGN KEY ( `addonID` )
	REFERENCES `smd__addon`( `addonID` )
	ON DELETE Cascade
	ON UPDATE Cascade;
-- -------------------------------------------------------------

-- CREATE LINK "smd__subscription_smd__subscription_has_addons_subscriptionID"
ALTER TABLE `smd__subscription_has_addons`
	ADD CONSTRAINT `smd__subscription_smd__subscription_has_addons_subscriptionID` FOREIGN KEY ( `subscriptionID` )
	REFERENCES `smd__subscription`( `subscriptionID` )
	ON DELETE Cascade
	ON UPDATE Cascade;
-- -------------------------------------------------------------

-- CREATE LINK "smd__subscription_smd_plan_planID_planID" ------
ALTER TABLE `smd__subscription`
	ADD CONSTRAINT `smd__subscription_smd_plan_planID_planID` FOREIGN KEY ( `planID` )
	REFERENCES `smd__plan`( `planID` )
	ON DELETE Set NULL
	ON UPDATE Cascade;
-- -------------------------------------------------------------

-- CREATE LINK "smd__subscription_smd__partner_config_subscriptionID"
ALTER TABLE `smd__partner_config`
	ADD CONSTRAINT `smd__subscription_smd__partner_config_subscriptionID` FOREIGN KEY ( `subscriptionID` )
	REFERENCES `smd__subscription`( `subscriptionID` )
	ON DELETE Set NULL
	ON UPDATE Cascade;
-- -------------------------------------------------------------

-- CREATE TABLE "smd__transaction_log" -------------------------
CREATE TABLE `smd__transaction_log` (
	`transactionID` Int( 11 ) UNSIGNED AUTO_INCREMENT NOT NULL,
	`transactionData` JSON NOT NULL,
	`createDate` Timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`zohoEventID` VarChar( 32 ) NOT NULL,
	PRIMARY KEY ( `transactionID` ),
	CONSTRAINT `unique_transactionID` UNIQUE( `transactionID` ),
	CONSTRAINT `unique_zohoEventID` UNIQUE( `zohoEventID` ) )
ENGINE = InnoDB;
-- -------------------------------------------------------------

-- CREATE INDEX "index_createDate6" ----------------------------
CREATE INDEX `index_createDate6` ON `smd__transaction_log`( `createDate` );
-- -------------------------------------------------------------

-- CREATE TABLE "smd__subscription_has_transaction_logs" -------
CREATE TABLE `smd__subscription_has_transaction_logs` (
	`subscriptionID` Int( 11 ) UNSIGNED NOT NULL,
	`transactionID` Int( 11 ) UNSIGNED NOT NULL,
	PRIMARY KEY ( `subscriptionID`, `transactionID` ) )
ENGINE = InnoDB;
-- -------------------------------------------------------------

-- CREATE INDEX "index_transactionID" --------------------------
CREATE INDEX `index_transactionID` ON `smd__subscription_has_transaction_logs`( `transactionID` );
-- -------------------------------------------------------------

-- CREATE INDEX "index_subscriptionID1" ------------------------
CREATE INDEX `index_subscriptionID1` ON `smd__subscription_has_transaction_logs`( `subscriptionID` );
-- -------------------------------------------------------------

-- CREATE LINK "smd__transaction_log_smd__subscription_has_transaction_logs"
ALTER TABLE `smd__subscription_has_transaction_logs`
	ADD CONSTRAINT `smd__transaction_log_smd__subscription_has_transaction_logs` FOREIGN KEY ( `transactionID` )
	REFERENCES `smd__transaction_log`( `transactionID` )
	ON DELETE Cascade
	ON UPDATE Cascade;
-- -------------------------------------------------------------

-- CREATE LINK "smd__subscription_smd__subscription_has_transaction_logs"
ALTER TABLE `smd__subscription_has_transaction_logs`
	ADD CONSTRAINT `smd__subscription_smd__subscription_has_transaction_logs` FOREIGN KEY ( `subscriptionID` )
	REFERENCES `smd__subscription`( `subscriptionID` )
	ON DELETE Cascade
	ON UPDATE Cascade;
-- -------------------------------------------------------------

-- CREATE TABLE "smd__transaction_queue" -----------------------
CREATE TABLE `smd__transaction_queue` (
	`transactionQueueID` Int( 11 ) UNSIGNED AUTO_INCREMENT NOT NULL,
	`queueData` JSON NULL,
	`createDate` Timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`notifyEmailSent` Timestamp NULL,
	PRIMARY KEY ( `transactionQueueID` ) )
ENGINE = InnoDB;
-- -------------------------------------------------------------

-- CREATE INDEX "index_createDate7" ----------------------------
CREATE INDEX `index_createDate7` ON `smd__transaction_queue`( `createDate` );
-- -------------------------------------------------------------

-- CREATE INDEX "index_notifyEmailSent" ------------------------
CREATE INDEX `index_notifyEmailSent` ON `smd__transaction_queue`( `notifyEmailSent` );
-- -------------------------------------------------------------

-- CREATE INDEX "index_transactionQueueID" ---------------------
CREATE INDEX `index_transactionQueueID` ON `smd__transaction_queue`( `transactionQueueID` );
-- -------------------------------------------------------------

INSERT INTO `cms__resource`(
    `resourceID`,
    `moduleID`,
    `name`,
    `description`
) VALUES (
    'auth.partner.list',
    3,
    'auth.partner.list.name',
    'auth.partner.list.name'
 );

 INSERT INTO `cms__resource`(
    `resourceID`,
    `moduleID`,
    `name`,
    `description`
) VALUES (
    'background.administration.access',
    @moduleID,
    'background.administration.access.name',
    'background.administration.access.description'
 );

 INSERT INTO `cms__resource`(
    `resourceID`,
    `moduleID`,
    `name`,
    `description`
) VALUES (
    'auth.partner.create',
    3,
    'auth.partner.create.name',
    'auth.partner.create.description'
 );

INSERT INTO `cms__resource`(
    `resourceID`,
    `moduleID`,
    `name`,
    `description`
) VALUES (
    'auth.partner.update',
    3,
    'auth.partner.update.name',
    'auth.partner.update.description'
 );

INSERT INTO `cms__resource`(
    `resourceID`,
    `moduleID`,
    `name`,
    `description`
) VALUES (
    'auth.partner.remove',
    3,
    'auth.partner.remove.name',
    'auth.partner.remove.description'
 );

 INSERT INTO `cms__resource`(
    `resourceID`,
    `moduleID`,
    `name`,
    `description`
) VALUES (
    'auth.partner.groupDelete',
    3,
    'auth.partner.groupDelete.name',
    'auth.partner.groupDelete.description'
 );

 INSERT INTO `cms__resource`(
    `resourceID`,
    `moduleID`,
    `name`,
    `description`
) VALUES (
    'auth.partner.status',
    3,
    'auth.partner.status.name',
    'auth.partner.status.description'
 );

INSERT INTO `cms__resource`(
    `resourceID`,
    `moduleID`,
    `name`,
    `description`
) VALUES (
    'background.plan.list',
    @moduleID,
    'background.plan.list.name',
    'background.plan.list.description'
 );

 INSERT INTO `cms__resource`(
    `resourceID`,
    `moduleID`,
    `name`,
    `description`
) VALUES (
    'background.plan.synchronize',
    @moduleID,
    'background.plan.synchronize.name',
    'background.plan.synchronize.description'
 );

INSERT INTO `cms__resource`(
    `resourceID`,
    `moduleID`,
    `name`,
    `description`
) VALUES (
    'background.plan.update',
    @moduleID,
    'background.plan.update.name',
    'background.plan.update.description'
 );

INSERT INTO `cms__resource`(
    `resourceID`,
    `moduleID`,
    `name`,
    `description`
) VALUES (
    'background.addon.list',
    @moduleID,
    'background.addon.list.name',
    'background.addon.list.description'
 );

 INSERT INTO `cms__resource`(
    `resourceID`,
    `moduleID`,
    `name`,
    `description`
) VALUES (
    'background.addon.synchronize',
    @moduleID,
    'background.addon.synchronize.name',
    'background.addon.synchronize.description'
 );

INSERT INTO `cms__resource`(
    `resourceID`,
    `moduleID`,
    `name`,
    `description`
) VALUES (
    'background.addon.update',
    @moduleID,
    'background.addon.update.name',
    'background.addon.update.description'
 );


-- CREATE FIELD "meta" -----------------------------------------
ALTER TABLE `smd__activity` ADD COLUMN `meta` JSON NULL;
-- -------------------------------------------------------------

-- CREATE TABLE "smd__limit" -----------------------------------
CREATE TABLE `smd__limit` (
	`date` Date NOT NULL,
	`partnerID` Int( 11 ) UNSIGNED NOT NULL,
	`postFromRssNumber` Int( 4 ) UNSIGNED NOT NULL,
	`postFromTemplateNumber` Int( 4 ) UNSIGNED NOT NULL,
	`postFromMediaNumber` Int( 4 ) UNSIGNED NOT NULL,
	PRIMARY KEY ( `date`, `partnerID` ) )
CHARACTER SET = utf8
COLLATE = utf8_general_ci
ENGINE = InnoDB;
-- -------------------------------------------------------------

-- CREATE INDEX "index_date1" ----------------------------------
CREATE INDEX `index_date1` USING BTREE ON `smd__limit`( `date` );
-- -------------------------------------------------------------

-- CREATE INDEX "index_partnerID4" -----------------------------
CREATE INDEX `index_partnerID4` USING BTREE ON `smd__limit`( `partnerID` );
-- -------------------------------------------------------------

-- CREATE INDEX "index_postFromMediaNumber" --------------------
CREATE INDEX `index_postFromMediaNumber` USING BTREE ON `smd__limit`( `postFromMediaNumber` );
-- -------------------------------------------------------------

-- CREATE INDEX "index_postLimit" ------------------------------
CREATE INDEX `index_postLimit` USING BTREE ON `smd__limit`( `postFromTemplateNumber` );
-- -------------------------------------------------------------

-- CREATE INDEX "index_rssLimit" -------------------------------
CREATE INDEX `index_rssLimit` USING BTREE ON `smd__limit`( `postFromRssNumber` );
-- -------------------------------------------------------------

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

-- CREATE FIELD "deleteRequested" ------------------------------
ALTER TABLE `smd__partner` ADD COLUMN `deleteRequested` Timestamp NULL COMMENT 'Törlést kérvényezték és mikor';
-- -------------------------------------------------------------

-- CREATE INDEX "index_deleteRequested" ------------------------
CREATE INDEX `index_deleteRequested` ON `smd__partner`( `deleteRequested` );
-- -------------------------------------------------------------


-- CHANGE "LENGTH" OF "FIELD "username" ------------------------
ALTER TABLE `cms__user` MODIFY `username` VarChar( 128 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Felhasználónév';
-- -------------------------------------------------------------

-- CHANGE "LENGTH" OF "FIELD "email" ---------------------------
ALTER TABLE `cms__user` MODIFY `email` VarChar( 128 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'E-mail cím';
-- -------------------------------------------------------------

INSERT INTO `cms__resource`(
    `resourceID`,
    `moduleID`,
    `name`,
    `description`
) VALUES (
    'background.administration.partnerDelete',
    @moduleID,
    'background.administration.partnerDelete.name',
    'background.administration.partnerDelete.description'
 );

 INSERT INTO `cms__resource`(
    `resourceID`,
    `moduleID`,
    `name`,
    `description`
) VALUES (
    'background.administration.bitlyTokenEdit',
    @moduleID,
    'background.administration.bitlyTokenEdit.name',
    'background.administration.bitlyTokenEdit.description'
 );

 INSERT INTO `cms__resource`(
    `resourceID`,
    `moduleID`,
    `name`,
    `description`
) VALUES (
    'background.administration.partnerLogoEdit',
    @moduleID,
    'background.administration.partnerLogoEdit.name',
    'background.administration.partnerLogoEdit.description'
 );


 INSERT INTO `cms__resource`(
    `resourceID`,
    `moduleID`,
    `name`,
    `description`
) VALUES (
    'post.template.import',
    7,
    'post.template.import.name',
    'post.template.import.description'
 );

 INSERT INTO `cms__resource`(
    `resourceID`,
    `moduleID`,
    `name`,
    `description`
) VALUES (
    'post.template.changeVisibility',
    7,
    'post.template.changeVisibility.name',
    'post.template.changeVisibility.description'
 );

 INSERT INTO `cms__resource`(
    `resourceID`,
    `moduleID`,
    `name`,
    `description`
) VALUES (
    'auth.partner.analyticsExport',
    3,
    'auth.partner.analyticsExport.name',
    'auth.partner.analyticsExport.description'
 );

INSERT INTO `cms__resource`(
    `resourceID`,
    `moduleID`,
    `name`,
    `description`
) VALUES (
    'auth.media.changeVisibility',
    3,
    'auth.media.changeVisibility.name',
    'auth.media.changeVisibility.description'
 );

 INSERT INTO `cms__resource`(
    `resourceID`,
    `moduleID`,
    `name`,
    `description`
) VALUES (
    'auth.media.status',
    3,
    'auth.media.status.name',
    'auth.media.status.description'
 );

 INSERT INTO `cms__resource`(
    `resourceID`,
    `moduleID`,
    `name`,
    `description`
) VALUES (
    'auth.partner.analytics',
    3,
    'auth.partner.analytics.name',
    'auth.partner.analytics.description'
 );

 INSERT INTO `cms__resource`(
    `resourceID`,
    `moduleID`,
    `name`,
    `description`
) VALUES (
    'auth.partner.addSubscription',
    3,
    'auth.partner.addSubscription.name',
    'auth.partner.addSubscription.description'
 );

INSERT INTO `cms__resource`(
    `resourceID`,
    `moduleID`,
    `name`,
    `description`
) VALUES (
    'auth.partner.editSubscription',
    3,
    'auth.partner.editSubscription.name',
    'auth.partner.editSubscription.description'
 );

insert into smd__organization_role_has_resources (roleID, resourceID) select roleID, 'background.administration.access' as resourceID from smd__organization_role WHERE roleID LIKE 'esemdee1HeadOffice.superAdmin.%';
insert into smd__organization_role_has_resources (roleID, resourceID) select roleID, 'auth.partner.list' as resourceID from smd__organization_role WHERE roleID LIKE 'esemdee1HeadOffice.superAdmin.%';
insert into smd__organization_role_has_resources (roleID, resourceID) select roleID, 'auth.partner.create' as resourceID from smd__organization_role WHERE roleID LIKE 'esemdee1HeadOffice.superAdmin.%';
insert into smd__organization_role_has_resources (roleID, resourceID) select roleID, 'auth.partner.update' as resourceID from smd__organization_role WHERE roleID LIKE 'esemdee1HeadOffice.superAdmin.%';
insert into smd__organization_role_has_resources (roleID, resourceID) select roleID, 'auth.partner.remove' as resourceID from smd__organization_role WHERE roleID LIKE 'esemdee1HeadOffice.superAdmin.%';
insert into smd__organization_role_has_resources (roleID, resourceID) select roleID, 'auth.partner.groupDelete' as resourceID from smd__organization_role WHERE roleID LIKE 'esemdee1HeadOffice.superAdmin.%';
insert into smd__organization_role_has_resources (roleID, resourceID) select roleID, 'auth.partner.status' as resourceID from smd__organization_role WHERE roleID LIKE 'esemdee1HeadOffice.superAdmin.%';
insert into smd__organization_role_has_resources (roleID, resourceID) select roleID, 'background.plan.list' as resourceID from smd__organization_role WHERE roleID LIKE 'esemdee1HeadOffice.superAdmin.%';
insert into smd__organization_role_has_resources (roleID, resourceID) select roleID, 'background.plan.synchronize' as resourceID from smd__organization_role WHERE roleID LIKE 'esemdee1HeadOffice.superAdmin.%';
insert into smd__organization_role_has_resources (roleID, resourceID) select roleID, 'background.plan.update' as resourceID from smd__organization_role WHERE roleID LIKE 'esemdee1HeadOffice.superAdmin.%';
insert into smd__organization_role_has_resources (roleID, resourceID) select roleID, 'background.addon.list' as resourceID from smd__organization_role WHERE roleID LIKE 'esemdee1HeadOffice.superAdmin.%';
insert into smd__organization_role_has_resources (roleID, resourceID) select roleID, 'background.addon.synchronize' as resourceID from smd__organization_role WHERE roleID LIKE 'esemdee1HeadOffice.superAdmin.%';
insert into smd__organization_role_has_resources (roleID, resourceID) select roleID, 'background.addon.update' as resourceID from smd__organization_role WHERE roleID LIKE 'esemdee1HeadOffice.superAdmin.%';
insert into smd__organization_role_has_resources (roleID, resourceID) select roleID, 'post.template.import' as resourceID from smd__organization_role WHERE roleID LIKE 'esemdee1HeadOffice.superAdmin.%';
insert into smd__organization_role_has_resources (roleID, resourceID) select roleID, 'post.template.changeVisibility' as resourceID from smd__organization_role WHERE roleID LIKE 'esemdee1HeadOffice.superAdmin.%';
insert into smd__organization_role_has_resources (roleID, resourceID) select roleID, 'auth.partner.analytics' as resourceID from smd__organization_role WHERE roleID LIKE 'esemdee1HeadOffice.superAdmin.%';
insert into smd__organization_role_has_resources (roleID, resourceID) select roleID, 'auth.partner.analyticsExport' as resourceID from smd__organization_role WHERE roleID LIKE 'esemdee1HeadOffice.superAdmin.%';
insert into smd__organization_role_has_resources (roleID, resourceID) select roleID, 'auth.media.changeVisibility' as resourceID from smd__organization_role WHERE roleID LIKE 'esemdee1HeadOffice.superAdmin.%';
insert into smd__organization_role_has_resources (roleID, resourceID) select roleID, 'auth.media.status' as resourceID from smd__organization_role WHERE roleID LIKE 'esemdee1HeadOffice.superAdmin.%';
insert into smd__organization_role_has_resources (roleID, resourceID) select roleID, 'auth.partner.addSubscription' as resourceID from smd__organization_role WHERE roleID LIKE 'esemdee1HeadOffice.superAdmin.%';
insert into smd__organization_role_has_resources (roleID, resourceID) select roleID, 'auth.partner.editSubscription' as resourceID from smd__organization_role WHERE roleID LIKE 'esemdee1HeadOffice.superAdmin.%';

insert into smd__organization_role_has_resources (roleID, resourceID) select roleID, 'background.administration.partnerDelete' as resourceID from smd__organization_role WHERE roleID LIKE '%.superAdmin.%';
insert into smd__organization_role_has_resources (roleID, resourceID) select roleID, 'background.administration.bitlyTokenEdit' as resourceID from smd__organization_role WHERE roleID LIKE '%.superAdmin.%';
insert into smd__organization_role_has_resources (roleID, resourceID) select roleID, 'background.administration.partnerLogoEdit' as resourceID from smd__organization_role WHERE roleID LIKE '%.superAdmin.%';

-- CREATE FIELD "systemType" -----------------------------------
ALTER TABLE `smd__post_template` ADD COLUMN `systemType` Enum( 'generic', 'branded', 'custom' ) NOT NULL DEFAULT 'custom';
-- -------------------------------------------------------------

-- CREATE INDEX "index_type7" ----------------------------------
CREATE INDEX `index_type7` ON `smd__post_template`( `systemType` );
-- -------------------------------------------------------------

-- CREATE TABLE "smd__partner_has_post_templates" --------------
CREATE TABLE `smd__partner_has_post_templates` (
	`templateID` Int( 11 ) UNSIGNED NOT NULL,
	`partnerID` Int( 11 ) UNSIGNED NOT NULL,
	`allowed` Enum( 'yes', 'no' ) NOT NULL,
	PRIMARY KEY ( `templateID`, `partnerID` ) )
ENGINE = InnoDB;
-- -------------------------------------------------------------

-- CREATE INDEX "index_partnerID4" -----------------------------
CREATE INDEX `index_partnerID4` ON `smd__partner_has_post_templates`( `partnerID` );
-- -------------------------------------------------------------

-- CREATE INDEX "index_templateID2" ----------------------------
CREATE INDEX `index_templateID2` ON `smd__partner_has_post_templates`( `templateID` );
-- -------------------------------------------------------------

-- CREATE LINK "smd__post_template_smd__partner_has_post_templates_templateID"
ALTER TABLE `smd__partner_has_post_templates`
	ADD CONSTRAINT `smd__post_template_smd__partner_has_post_templates_templateID` FOREIGN KEY ( `templateID` )
	REFERENCES `smd__post_template`( `templateID` )
	ON DELETE Cascade
	ON UPDATE Cascade;
-- -------------------------------------------------------------

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

-- CHANGE "NULLABLE" OF "FIELD "partnerID" ---------------------
ALTER TABLE `cms__tag` MODIFY `partnerID` Int( 11 ) UNSIGNED NULL;
-- -------------------------------------------------------------

-- CHANGE "NULLABLE" OF "FIELD "partnerID" ---------------------
ALTER TABLE `smd__category` MODIFY `partnerID` Int( 11 ) UNSIGNED NULL;
-- -------------------------------------------------------------

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

-- CREATE INDEX "index_imageFileID" ----------------------------
CREATE INDEX `index_imageFileID` ON `smd__partner`( `imageFileID` );
-- -------------------------------------------------------------

 -- CREATE LINK "cms__file_smd__partner_fileID_imageFileID" -----
ALTER TABLE `smd__partner`
	ADD CONSTRAINT `cms__file_smd__partner_fileID_imageFileID` FOREIGN KEY ( `imageFileID` )
	REFERENCES `cms__file`( `fileID` )
	ON DELETE Set NULL
	ON UPDATE Cascade;
-- -------------------------------------------------------------

-- CREATE FIELD "zohoCustomerID" -------------------------------
ALTER TABLE `smd__partner` ADD COLUMN `zohoCustomerID` VarChar( 32 ) NULL;
-- -------------------------------------------------------------

-- CREATE INDEX "index_zohoCustomerID" -------------------------
CREATE INDEX `index_zohoCustomerID` ON `smd__partner`( `zohoCustomerID` );
-- -------------------------------------------------------------

-- CHANGE "NULLABLE" OF "FIELD "zohoSubscriptionID" ------------
ALTER TABLE `smd__subscription` MODIFY `zohoSubscriptionID` VarChar( 32 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL;
-- -------------------------------------------------------------

UPDATE cms__tag SET name = LOWER(name);
UPDATE smd__category SET name = CONCAT(UCASE(LEFT(name, 1)), SUBSTRING(name, 2));

INSERT INTO cms__resource (resourceID, moduleID, name, description) VALUES ('background.role.list', @moduleID, 'background.role.list.name', 'background.role.list.description');
INSERT INTO cms__resource (resourceID, moduleID, name, description) VALUES ('background.role.create', @moduleID, 'background.role.create.name', 'background.role.create.description');
INSERT INTO cms__resource (resourceID, moduleID, name, description) VALUES ('background.role.update', @moduleID, 'background.role.update.name', 'background.role.update.description');
INSERT INTO cms__resource (resourceID, moduleID, name, description) VALUES ('background.role.remove', @moduleID, 'background.role.remove.name', 'background.role.remove.description');

INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('testingHeadOffice.superAdmin', 'background.administration.access');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('testingHeadOffice.superAdmin', 'auth.partner.list');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('testingHeadOffice.superAdmin', 'auth.partner.create');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('testingHeadOffice.superAdmin', 'auth.partner.update');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('testingHeadOffice.superAdmin', 'auth.partner.remove');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('testingHeadOffice.superAdmin', 'auth.partner.groupDelete');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('testingHeadOffice.superAdmin', 'auth.partner.status');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('testingHeadOffice.superAdmin', 'background.plan.list');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('testingHeadOffice.superAdmin', 'background.plan.synchronize');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('testingHeadOffice.superAdmin', 'background.plan.update');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('testingHeadOffice.superAdmin', 'background.addon.list');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('testingHeadOffice.superAdmin', 'background.addon.synchronize');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('testingHeadOffice.superAdmin', 'background.addon.update');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('testingHeadOffice.superAdmin', 'post.template.import');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('testingHeadOffice.superAdmin', 'post.template.changeVisibility');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('testingHeadOffice.superAdmin', 'auth.partner.analytics');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('testingHeadOffice.superAdmin', 'auth.partner.analyticsExport');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('testingHeadOffice.superAdmin', 'auth.media.changeVisibility');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('testingHeadOffice.superAdmin', 'auth.media.status');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('testingHeadOffice.superAdmin', 'background.role.list');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('testingHeadOffice.superAdmin', 'background.role.create');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('testingHeadOffice.superAdmin', 'background.role.update');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('testingHeadOffice.superAdmin', 'background.role.remove');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('testingHeadOffice.superAdmin', 'auth.partner.addSubscription');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('testingHeadOffice.superAdmin', 'auth.partner.editSubscription');

INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('esemdee1HeadOffice.superAdmin.1554306920', 'background.administration.access');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('esemdee1HeadOffice.superAdmin.1554306920', 'auth.partner.list');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('esemdee1HeadOffice.superAdmin.1554306920', 'auth.partner.create');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('esemdee1HeadOffice.superAdmin.1554306920', 'auth.partner.update');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('esemdee1HeadOffice.superAdmin.1554306920', 'auth.partner.remove');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('esemdee1HeadOffice.superAdmin.1554306920', 'auth.partner.groupDelete');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('esemdee1HeadOffice.superAdmin.1554306920', 'auth.partner.status');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('esemdee1HeadOffice.superAdmin.1554306920', 'background.plan.list');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('esemdee1HeadOffice.superAdmin.1554306920', 'background.plan.synchronize');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('esemdee1HeadOffice.superAdmin.1554306920', 'background.plan.update');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('esemdee1HeadOffice.superAdmin.1554306920', 'background.addon.list');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('esemdee1HeadOffice.superAdmin.1554306920', 'background.addon.synchronize');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('esemdee1HeadOffice.superAdmin.1554306920', 'background.addon.update');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('esemdee1HeadOffice.superAdmin.1554306920', 'post.template.import');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('esemdee1HeadOffice.superAdmin.1554306920', 'post.template.changeVisibility');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('esemdee1HeadOffice.superAdmin.1554306920', 'auth.partner.analytics');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('esemdee1HeadOffice.superAdmin.1554306920', 'auth.partner.analyticsExport');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('esemdee1HeadOffice.superAdmin.1554306920', 'auth.media.changeVisibility');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('esemdee1HeadOffice.superAdmin.1554306920', 'auth.media.status');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('esemdee1HeadOffice.superAdmin.1554306920', 'background.role.list');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('esemdee1HeadOffice.superAdmin.1554306920', 'background.role.create');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('esemdee1HeadOffice.superAdmin.1554306920', 'background.role.update');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('esemdee1HeadOffice.superAdmin.1554306920', 'background.role.remove');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('esemdee1HeadOffice.superAdmin.1554306920', 'auth.partner.addSubscription');
INSERT INTO smd__organization_role_has_resources (roleID, resourceID) VALUES ('esemdee1HeadOffice.superAdmin.1554306920', 'auth.partner.editSubscription');