--
-- Host: 35.246.20.38    Database: esemdee_prod_db
-- ------------------------------------------------------


--
-- Table structure for table `cms__api_key`
--

CREATE TABLE `cms__api_key` (
  `userID` int(11) unsigned NOT NULL COMMENT 'Felhasználói azonosító',
  `apiKey` text NOT NULL COMMENT 'API kulcs',
  PRIMARY KEY (`userID`),
  CONSTRAINT `cms__user_cms__api_key_userID` FOREIGN KEY (`userID`) REFERENCES `cms__user` (`userID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `cms__column`
--

CREATE TABLE `cms__column` (
  `columnID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Oszlop azonosító',
  `layoutID` int(11) unsigned NOT NULL COMMENT 'Layout azonosító',
  `name` varchar(64) NOT NULL COMMENT 'Név',
  PRIMARY KEY (`columnID`),
  UNIQUE KEY `unique_name` (`name`),
  KEY `index_layoutID` (`layoutID`) USING BTREE,
  CONSTRAINT `cms__layout_cms__column_layoutID` FOREIGN KEY (`layoutID`) REFERENCES `cms__layout` (`layoutID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `cms__column_has_panels`
--

CREATE TABLE `cms__column_has_panels` (
  `columnID` int(11) unsigned NOT NULL COMMENT 'Oszlop azonosító',
  `panelID` int(11) unsigned NOT NULL COMMENT 'Panel azonosító',
  `maxPerColumn` int(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`columnID`,`panelID`),
  KEY `index_columnID1` (`columnID`) USING BTREE,
  KEY `index_maxPerColumn` (`maxPerColumn`) USING BTREE,
  KEY `index_panelID1` (`panelID`) USING BTREE,
  CONSTRAINT `cms__column_cms__column_has_panels_columnID` FOREIGN KEY (`columnID`) REFERENCES `cms__column` (`columnID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `cms__panel_cms__column_has_panels_panelID` FOREIGN KEY (`panelID`) REFERENCES `cms__panel` (`panelID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `cms__domain`
--

CREATE TABLE `cms__domain` (
  `domainID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Domain azonosító',
  `name` varchar(32) NOT NULL COMMENT 'Domain név',
  `defaultSiteID` int(11) unsigned NOT NULL COMMENT 'Alapértelmezett oldal azonosítója',
  `gaTrackingID` varchar(45) DEFAULT NULL COMMENT 'Analytics Tracking ID',
  `gaSiteVerificationID` varchar(64) DEFAULT NULL COMMENT 'Google Site Verification ID',
  PRIMARY KEY (`domainID`),
  UNIQUE KEY `unique_name` (`name`),
  KEY `index_defaultSiteID` (`defaultSiteID`) USING BTREE,
  CONSTRAINT `cms__site_cms__domain_defaultSiteID_siteID` FOREIGN KEY (`defaultSiteID`) REFERENCES `cms__site` (`siteID`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `cms__file`
--

CREATE TABLE `cms__file` (
  `fileID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Fájl azonosító',
  `folderID` int(11) unsigned NOT NULL COMMENT 'Mappa azonosító',
  `name` varchar(128) NOT NULL COMMENT 'Fájlnév',
  `alias` varchar(64) DEFAULT NULL COMMENT 'Alias (fejlesztőknek)',
  `url` varchar(255) NOT NULL COMMENT 'Fájl url',
  `className` varchar(64) DEFAULT NULL COMMENT 'Class név (fejlesztőknek))',
  `status` enum('active','inactive') NOT NULL DEFAULT 'active' COMMENT 'Státusz',
  `conversionStatus` enum('in-queue','in-progress','success','failed') DEFAULT NULL,
  `activeFrom` datetime DEFAULT NULL COMMENT 'Aktív időtartam kezdete',
  `activeTo` datetime DEFAULT NULL COMMENT 'Aktív időtartam vége',
  `type` enum('static','page','link','rest','json') NOT NULL DEFAULT 'page' COMMENT 'Fájltipus',
  `createDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Létrehozás időpontja',
  `modifyDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Utolsó módosítás időpontja',
  `position` int(3) unsigned NOT NULL DEFAULT '0' COMMENT 'Mappán belüli pozíció',
  `userID` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`fileID`),
  UNIQUE KEY `unique_url` (`url`),
  UNIQUE KEY `unique_alias` (`alias`),
  KEY `index_createDate1` (`createDate`) USING BTREE,
  KEY `index_folderID` (`folderID`) USING BTREE,
  KEY `index_modifyDate1` (`modifyDate`) USING BTREE,
  KEY `index_name` (`name`) USING BTREE,
  KEY `index_position` (`position`) USING BTREE,
  KEY `index_status3` (`status`) USING BTREE,
  KEY `index_type` (`type`) USING BTREE,
  KEY `index_userID2` (`userID`) USING BTREE,
  CONSTRAINT `cms__file_cms__folder_folderID` FOREIGN KEY (`folderID`) REFERENCES `cms__folder` (`folderID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `cms__user_cms__file_userID_userID` FOREIGN KEY (`userID`) REFERENCES `cms__user` (`userID`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 */ /*!50003 TRIGGER `trigger__set_empty_file_alias_to_null` BEFORE INSERT  ON `cms__file` FOR EACH ROW
  BEGIN
    IF TRIM(NEW.alias) = '' THEN
      SET NEW.alias = NULL;
    END IF;
  END */;;
DELIMITER ;

--
-- Table structure for table `cms__file_has_files`
--

CREATE TABLE `cms__file_has_files` (
  `fileID` int(11) unsigned NOT NULL COMMENT 'Fájl azonosító (birtokló)',
  `otherFileID` int(11) unsigned NOT NULL COMMENT 'Birtokolt fájl azonosító',
  `type` enum('lang','related') NOT NULL DEFAULT 'related' COMMENT 'Kapcsolat típusa',
  PRIMARY KEY (`fileID`,`otherFileID`,`type`),
  KEY `index_fileID` (`fileID`) USING BTREE,
  KEY `index_otherFileID` (`otherFileID`) USING BTREE,
  KEY `index_type1` (`type`) USING BTREE,
  CONSTRAINT `cms__file_cms__file_has_files_fileID` FOREIGN KEY (`fileID`) REFERENCES `cms__file` (`fileID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `cms__file_cms__file_has_files_otherFileID_fileID` FOREIGN KEY (`otherFileID`) REFERENCES `cms__file` (`fileID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `cms__file_link`
--

CREATE TABLE `cms__file_link` (
  `fileID` int(11) unsigned NOT NULL COMMENT 'Fájl azonosító',
  `linkFileID` int(11) unsigned DEFAULT NULL COMMENT 'Linkelt fájl azonosító',
  `linkFolderID` int(11) unsigned DEFAULT NULL COMMENT 'Linkelt mappa azonosító',
  `linkUrl` varchar(255) DEFAULT NULL COMMENT 'Külső hivatkozás url-je',
  `target` enum('_self','_blank') NOT NULL DEFAULT '_self' COMMENT 'Megnyitás módja (új lapon, stb)',
  `linkParams` json DEFAULT NULL COMMENT 'Paraméterek',
  PRIMARY KEY (`fileID`),
  KEY `index_linkFileID` (`linkFileID`) USING BTREE,
  KEY `index_linkFolderID` (`linkFolderID`) USING BTREE,
  KEY `index_linkUrl` (`linkUrl`) USING BTREE,
  KEY `index_target` (`target`) USING BTREE,
  CONSTRAINT `cms__file_cms__file_link_fileID` FOREIGN KEY (`fileID`) REFERENCES `cms__file` (`fileID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `cms__file_cms__file_link_linkFileID_fileID` FOREIGN KEY (`linkFileID`) REFERENCES `cms__file` (`fileID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `cms__file_page`
--

CREATE TABLE `cms__file_page` (
  `fileID` int(11) unsigned NOT NULL COMMENT 'Fájl azonosító',
  `layoutID` int(11) unsigned NOT NULL COMMENT 'Layout azonosító',
  `title` varchar(70) DEFAULT NULL COMMENT 'Meta cím',
  `keywords` text COMMENT 'Meta kulcsszavak',
  `description` text COMMENT 'Meta leírás',
  `editMode` enum('published','edit','review') NOT NULL DEFAULT 'published' COMMENT 'Szerkesztési állapot',
  `editData` json DEFAULT NULL COMMENT 'Szerkesztett adatok',
  PRIMARY KEY (`fileID`),
  KEY `index_editMode` (`editMode`) USING BTREE,
  KEY `index_layoutID1` (`layoutID`) USING BTREE,
  CONSTRAINT `cms__file_cms__file_page_fileID` FOREIGN KEY (`fileID`) REFERENCES `cms__file` (`fileID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `cms__layout_cms__file_page_layoutID` FOREIGN KEY (`layoutID`) REFERENCES `cms__layout` (`layoutID`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `cms__file_page_has_panels`
--

CREATE TABLE `cms__file_page_has_panels` (
  `phpID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'PHP azonosító',
  `fileID` int(11) unsigned NOT NULL COMMENT 'Fájl azonosító',
  `columnID` int(11) unsigned NOT NULL COMMENT 'Oszlop azonosító',
  `panelVariantID` int(11) unsigned NOT NULL COMMENT 'Panel változat azonosító',
  `position` int(3) unsigned NOT NULL DEFAULT '0' COMMENT 'Pozíció',
  PRIMARY KEY (`phpID`),
  KEY `index_columnID` (`columnID`) USING BTREE,
  KEY `index_fileID2` (`fileID`) USING BTREE,
  KEY `index_panelVariantID` (`panelVariantID`) USING BTREE,
  KEY `index_position2` (`position`) USING BTREE,
  CONSTRAINT `cms__column_cms__file_page_has_panels_columnID` FOREIGN KEY (`columnID`) REFERENCES `cms__column` (`columnID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `cms__file_page_cms__file_page_has_panels_fileID` FOREIGN KEY (`fileID`) REFERENCES `cms__file_page` (`fileID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `cms__panel_variant_cms__file_page_has_panels_panelVariantID` FOREIGN KEY (`panelVariantID`) REFERENCES `cms__panel_variant` (`panelVariantID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 */ /*!50003 TRIGGER `trigger__remove_unused_panel_variants` AFTER DELETE ON `cms__file_page_has_panels` FOR EACH ROW
  BEGIN
    CALL func__remove_unused_panel_variants();
  END */;;
DELIMITER ;

--
-- Table structure for table `cms__file_static`
--

CREATE TABLE `cms__file_static` (
  `fileID` int(11) unsigned NOT NULL COMMENT 'Fájl azonosító',
  `mime` varchar(128) NOT NULL COMMENT 'MIME típus',
  `size` int(10) unsigned NOT NULL COMMENT 'Méret',
  `realPath` varchar(255) NOT NULL,
  `data` json DEFAULT NULL COMMENT 'Speciális adatok',
  `type` varchar(128) NOT NULL,
  `organizationID` int(10) unsigned DEFAULT NULL,
  `previewFileID` int(11) unsigned DEFAULT NULL,
  `socialChannel` set('facebook','linkedIn','twitter','instagram','gmb') DEFAULT NULL,
  `systemType` enum('custom','generic','branded') NOT NULL DEFAULT 'custom',
  `data_width` int(5) GENERATED ALWAYS AS (json_unquote(json_extract(`data`,'$.width'))) STORED,
  `data_height` int(5) GENERATED ALWAYS AS (json_unquote(json_extract(`data`,'$.height'))) STORED,
  `data_ratio` varchar(20) GENERATED ALWAYS AS (json_unquote(json_extract(`data`,'$.ratio'))) STORED,
  `canvaId` varchar(128) DEFAULT NULL,
  `data_synced` tinyint(1) GENERATED ALWAYS AS (json_unquote(json_extract(`data`,'$.synced'))) STORED,
  PRIMARY KEY (`fileID`),
  UNIQUE KEY `unique_realPath` (`realPath`),
  KEY `index_mime` (`mime`) USING BTREE,
  KEY `index_organizationID1` (`organizationID`) USING BTREE,
  KEY `index_previewFileID` (`previewFileID`) USING BTREE,
  KEY `index_type5` (`type`) USING BTREE,
  KEY `index_systemType1` (`systemType`),
  CONSTRAINT `cms__file_cms__file_static_fileID` FOREIGN KEY (`fileID`) REFERENCES `cms__file` (`fileID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `cms__file_cms__file_static_fileID_previewFileID` FOREIGN KEY (`previewFileID`) REFERENCES `cms__file` (`fileID`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `cms__folder`
--

CREATE TABLE `cms__folder` (
  `folderID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Mappa azonosító',
  `parentID` int(11) unsigned DEFAULT NULL COMMENT 'Szülő mappa azonosító',
  `name` varchar(64) NOT NULL COMMENT 'Mappa neve',
  `url` varchar(128) NOT NULL COMMENT 'Mappa url-je',
  `alias` varchar(64) DEFAULT NULL COMMENT 'Alias',
  `defaultFileID` int(11) unsigned DEFAULT NULL COMMENT 'Alapértelmezett fájl azonosító',
  `position` int(3) unsigned NOT NULL DEFAULT '0' COMMENT 'Mappán belüli pozíció',
  `createDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modifyDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`folderID`),
  UNIQUE KEY `unique_url` (`url`),
  UNIQUE KEY `unique_alias` (`alias`),
  KEY `index_createDate2` (`createDate`) USING BTREE,
  KEY `index_defaultFileID` (`defaultFileID`) USING BTREE,
  KEY `index_modifyDate2` (`modifyDate`) USING BTREE,
  KEY `index_name1` (`name`) USING BTREE,
  KEY `index_parentID` (`parentID`) USING BTREE,
  KEY `index_position1` (`position`) USING BTREE,
  CONSTRAINT `cms__file_cms__folder_defaultFileID_fileID` FOREIGN KEY (`defaultFileID`) REFERENCES `cms__file` (`fileID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `cms__folder_cms__folder_parentID_folderID` FOREIGN KEY (`parentID`) REFERENCES `cms__folder` (`folderID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 */ /*!50003 TRIGGER `trigger__set_empty_folder_alias_to_null` BEFORE INSERT  ON `cms__folder` FOR EACH ROW
  BEGIN
    IF TRIM(NEW.alias) = '' THEN
      SET NEW.alias = NULL;
    END IF;
  END */;;
DELIMITER ;

--
-- Table structure for table `cms__group`
--

CREATE TABLE `cms__group` (
  `groupID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Csoport azonosító',
  `name` varchar(32) NOT NULL COMMENT 'Név',
  `description` text COMMENT 'Leírás',
  `status` enum('active','inactive') NOT NULL DEFAULT 'active' COMMENT 'Státusz',
  `guest` enum('true','false') NOT NULL DEFAULT 'false' COMMENT 'Vendég csoport',
  PRIMARY KEY (`groupID`),
  KEY `index_guest` (`guest`) USING BTREE,
  KEY `index_status1` (`status`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `cms__group_has_files`
--

CREATE TABLE `cms__group_has_files` (
  `groupID` int(11) unsigned NOT NULL COMMENT 'Csoport azonosító',
  `fileID` int(11) unsigned NOT NULL COMMENT 'Fájl azonosító',
  `permissions` set('dispatch','view','edit','publish','move','remove') NOT NULL DEFAULT 'dispatch,view' COMMENT 'Jogosultságok',
  PRIMARY KEY (`groupID`,`fileID`),
  KEY `index_fileID1` (`fileID`) USING BTREE,
  KEY `index_groupID4` (`groupID`) USING BTREE,
  CONSTRAINT `cms__file_cms__group_has_files_fileID` FOREIGN KEY (`fileID`) REFERENCES `cms__file` (`fileID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `cms__group_cms__group_has_files_groupID` FOREIGN KEY (`groupID`) REFERENCES `cms__group` (`groupID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `cms__group_has_folders`
--

CREATE TABLE `cms__group_has_folders` (
  `groupID` int(11) unsigned NOT NULL COMMENT 'Csoport azonosító',
  `folderID` int(11) unsigned NOT NULL COMMENT 'Mappa azonosító',
  `permissions` set('view','edit','createFile','createFolder','move','remove') NOT NULL DEFAULT 'view' COMMENT 'Jogosultságok',
  PRIMARY KEY (`groupID`,`folderID`),
  KEY `index_folderID1` (`folderID`) USING BTREE,
  KEY `index_groupID3` (`groupID`) USING BTREE,
  CONSTRAINT `cms__folder_cms__group_has_folders_folderID` FOREIGN KEY (`folderID`) REFERENCES `cms__folder` (`folderID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `cms__group_cms__group_has_folders_groupID` FOREIGN KEY (`groupID`) REFERENCES `cms__group` (`groupID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `cms__group_has_groups`
--

CREATE TABLE `cms__group_has_groups` (
  `groupID` int(11) unsigned NOT NULL COMMENT 'Csoport azonosító (birtokló)',
  `otherGroupID` int(11) unsigned NOT NULL COMMENT 'Birtokolt csoport azonosító',
  PRIMARY KEY (`groupID`,`otherGroupID`),
  KEY `index_groupID1` (`groupID`) USING BTREE,
  KEY `index_otherGroupID` (`otherGroupID`) USING BTREE,
  CONSTRAINT `cms__group_cms__group_has_groups_groupID` FOREIGN KEY (`groupID`) REFERENCES `cms__group` (`groupID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `cms__group_cms__group_has_groups_otherGroupID_groupID` FOREIGN KEY (`otherGroupID`) REFERENCES `cms__group` (`groupID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `cms__group_has_roles`
--

CREATE TABLE `cms__group_has_roles` (
  `groupID` int(11) unsigned NOT NULL COMMENT 'Csoport azonosító',
  `roleID` varchar(64) NOT NULL COMMENT 'Szerepkör azonosító',
  PRIMARY KEY (`groupID`,`roleID`),
  KEY `index_groupID2` (`groupID`) USING BTREE,
  KEY `index_roleID1` (`roleID`) USING BTREE,
  CONSTRAINT `cms__group_cms__group_has_roles_groupID` FOREIGN KEY (`groupID`) REFERENCES `cms__group` (`groupID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `cms__role_cms__group_has_roles_roleID` FOREIGN KEY (`roleID`) REFERENCES `cms__role` (`roleID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `cms__language`
--

CREATE TABLE `cms__language` (
  `languageID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Nyelvi azonosító',
  `name` varchar(32) NOT NULL COMMENT 'Név',
  `dir` varchar(16) NOT NULL COMMENT 'Mappa',
  `locale` varchar(255) NOT NULL COMMENT 'Lokációk',
  `status` enum('active','inactive') NOT NULL DEFAULT 'active' COMMENT 'Státusz',
  `type` enum('front','admin','both') NOT NULL DEFAULT 'front' COMMENT 'Típus',
  PRIMARY KEY (`languageID`),
  UNIQUE KEY `unique_dir` (`dir`),
  UNIQUE KEY `unique_name` (`name`),
  KEY `index_locale` (`locale`) USING BTREE,
  KEY `index_status2` (`status`) USING BTREE,
  KEY `index_type4` (`type`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `cms__layout`
--

CREATE TABLE `cms__layout` (
  `layoutID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Layout azonosító',
  `name` varchar(32) NOT NULL COMMENT 'Név',
  `fileName` varchar(64) NOT NULL COMMENT 'Fájlnév',
  `icon` varchar(32) DEFAULT NULL COMMENT 'Ikon',
  PRIMARY KEY (`layoutID`),
  UNIQUE KEY `unique_fileName` (`fileName`),
  UNIQUE KEY `unique_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `cms__module`
--

CREATE TABLE `cms__module` (
  `moduleID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Modul azonosító',
  `name` varchar(32) NOT NULL COMMENT 'Név',
  PRIMARY KEY (`moduleID`),
  UNIQUE KEY `unique_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `cms__panel`
--

CREATE TABLE `cms__panel` (
  `panelID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Panel azonosító',
  `moduleID` int(11) unsigned NOT NULL COMMENT 'Modul azonosító',
  `name` varchar(64) NOT NULL COMMENT 'Név',
  `exclusive` enum('true','false') NOT NULL DEFAULT 'false' COMMENT 'Exclusive panel',
  `status` enum('active','inactive') NOT NULL DEFAULT 'active' COMMENT 'Státusz',
  `parameters` json DEFAULT NULL COMMENT 'Paraméterek',
  PRIMARY KEY (`panelID`),
  UNIQUE KEY `unique_name` (`name`),
  KEY `index_exclusive` (`exclusive`) USING BTREE,
  KEY `index_moduleID2` (`moduleID`) USING BTREE,
  KEY `index_status4` (`status`) USING BTREE,
  CONSTRAINT `cms__module_cms__panel_moduleID` FOREIGN KEY (`moduleID`) REFERENCES `cms__module` (`moduleID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `cms__panel_variant`
--

CREATE TABLE `cms__panel_variant` (
  `panelVariantID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Panel változat azonosító',
  `panelID` int(11) unsigned NOT NULL COMMENT 'Panel azonosító',
  `name` varchar(128) DEFAULT NULL COMMENT 'Név (globális esetén)',
  `parameters` json DEFAULT NULL COMMENT 'Paraméterek',
  `type` enum('local','global') NOT NULL DEFAULT 'local' COMMENT 'Típus',
  `status` enum('active','inactive') NOT NULL DEFAULT 'active' COMMENT 'Státusz',
  PRIMARY KEY (`panelVariantID`),
  KEY `index_panelID` (`panelID`) USING BTREE,
  KEY `index_status5` (`status`) USING BTREE,
  KEY `index_type2` (`type`) USING BTREE,
  CONSTRAINT `cms__panel_cms__panel_variant_panelID` FOREIGN KEY (`panelID`) REFERENCES `cms__panel` (`panelID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `cms__resource`
--

CREATE TABLE `cms__resource` (
  `resourceID` varchar(100) NOT NULL,
  `moduleID` int(11) unsigned NOT NULL COMMENT 'Modul azonosító',
  `name` varchar(64) DEFAULT NULL COMMENT 'Erőforrás neve',
  `description` varchar(100) DEFAULT NULL COMMENT 'Rövid leírás',
  PRIMARY KEY (`resourceID`),
  KEY `index_moduleID` (`moduleID`) USING BTREE,
  CONSTRAINT `cms__module_cms__resource_moduleID` FOREIGN KEY (`moduleID`) REFERENCES `cms__module` (`moduleID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `cms__role`
--

CREATE TABLE `cms__role` (
  `roleID` varchar(64) NOT NULL COMMENT 'Szerepkör azonosító',
  `moduleID` int(11) unsigned NOT NULL COMMENT 'Modul azonosító',
  `name` varchar(64) DEFAULT NULL COMMENT 'Név',
  `description` varchar(100) DEFAULT NULL COMMENT 'Rövid leírás',
  PRIMARY KEY (`roleID`),
  UNIQUE KEY `unique_name` (`name`),
  KEY `index_moduleID1` (`moduleID`) USING BTREE,
  CONSTRAINT `cms__module_cms__role_moduleID` FOREIGN KEY (`moduleID`) REFERENCES `cms__module` (`moduleID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `cms__role_has_resources`
--

CREATE TABLE `cms__role_has_resources` (
  `roleID` varchar(64) NOT NULL COMMENT 'Szerepkör azonosító',
  `resourceID` varchar(100) NOT NULL COMMENT 'Erőforrás azonosító',
  PRIMARY KEY (`roleID`,`resourceID`),
  KEY `index_resourceID` (`resourceID`) USING BTREE,
  KEY `index_roleID` (`roleID`) USING BTREE,
  CONSTRAINT `cms__resource_cms__role_has_resources_resourceID` FOREIGN KEY (`resourceID`) REFERENCES `cms__resource` (`resourceID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `cms__role_cms__role_has_resources_roleID` FOREIGN KEY (`roleID`) REFERENCES `cms__role` (`roleID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `cms__site`
--

CREATE TABLE `cms__site` (
  `siteID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Website azonosító',
  `name` varchar(32) NOT NULL COMMENT 'Website neve',
  `languageID` int(11) unsigned NOT NULL COMMENT 'Nyelv azonosító',
  `rootFolderID` int(11) unsigned NOT NULL COMMENT 'Alapértelmezett mappa azonosító',
  `loginFileID` int(11) unsigned DEFAULT NULL COMMENT 'Bejelentkezés fájl azonosító',
  `registerFileID` int(11) unsigned DEFAULT NULL COMMENT 'Regisztrációs fájl azonosító',
  `errorFileID` int(11) unsigned NOT NULL COMMENT 'Hiba fájl azonosító',
  `registerGroupID` int(11) unsigned DEFAULT NULL COMMENT 'Regisztrációs csoport azonosító',
  PRIMARY KEY (`siteID`),
  KEY `index_errorFileID` (`errorFileID`) USING BTREE,
  KEY `index_languageID` (`languageID`) USING BTREE,
  KEY `index_loginFileID` (`loginFileID`) USING BTREE,
  KEY `index_registerFileID` (`registerFileID`) USING BTREE,
  KEY `index_registerGroupID` (`registerGroupID`) USING BTREE,
  KEY `index_rootFolderID` (`rootFolderID`) USING BTREE,
  CONSTRAINT `cms__file_cms__site_errorFileID_fileID` FOREIGN KEY (`errorFileID`) REFERENCES `cms__file` (`fileID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `cms__file_cms__site_loginFileID_fileID` FOREIGN KEY (`loginFileID`) REFERENCES `cms__file` (`fileID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `cms__file_cms__site_registerFileID_fileID` FOREIGN KEY (`registerFileID`) REFERENCES `cms__file` (`fileID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `cms__folder_cms__site_rootFolderID_folderID` FOREIGN KEY (`rootFolderID`) REFERENCES `cms__folder` (`folderID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `cms__group_cms__site_registerGroupID_groupID` FOREIGN KEY (`registerGroupID`) REFERENCES `cms__group` (`groupID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `cms__language_cms__site_languageID` FOREIGN KEY (`languageID`) REFERENCES `cms__language` (`languageID`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `cms__site_has_domains`
--

CREATE TABLE `cms__site_has_domains` (
  `siteID` int(11) unsigned NOT NULL COMMENT 'Website azonosító',
  `domainID` int(11) unsigned NOT NULL COMMENT 'Domain azonosító',
  PRIMARY KEY (`siteID`,`domainID`),
  KEY `index_domainID` (`domainID`) USING BTREE,
  KEY `index_siteID` (`siteID`) USING BTREE,
  CONSTRAINT `cms__domain_cms__site_has_domains_domainID` FOREIGN KEY (`domainID`) REFERENCES `cms__domain` (`domainID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `cms__site_cms__site_has_domains_siteID` FOREIGN KEY (`siteID`) REFERENCES `cms__site` (`siteID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `cms__tag`
--

CREATE TABLE `cms__tag` (
  `tagID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Tag azonosító',
  `name` varchar(64) NOT NULL COMMENT 'Tag név',
  `type` varchar(32) NOT NULL DEFAULT 'core' COMMENT 'Típus',
  `partnerID` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`tagID`),
  UNIQUE KEY `unique_name_type` (`name`,`type`,`partnerID`),
  KEY `index_name2` (`name`) USING BTREE,
  KEY `index_organizationID2` (`partnerID`) USING BTREE,
  KEY `index_tagID` (`tagID`) USING BTREE,
  KEY `index_type3` (`type`) USING BTREE,
  CONSTRAINT `smd__partner_cms__tag_partnerID_partnerID` FOREIGN KEY (`partnerID`) REFERENCES `smd__partner` (`partnerID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `cms__user`
--

CREATE TABLE `cms__user` (
  `userID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Felhasználói azonosító',
  `groupID` int(11) unsigned NOT NULL COMMENT 'Csoport azonosító',
  `username` varchar(128) NOT NULL COMMENT 'Felhasználónév',
  `password` varchar(60) NOT NULL COMMENT 'Jelszó',
  `tempPassword` varchar(60) DEFAULT NULL COMMENT 'Ideiglenes jelszó',
  `email` varchar(128) NOT NULL COMMENT 'E-mail cím',
  `status` enum('active','inactive') NOT NULL DEFAULT 'active' COMMENT 'Státusz',
  `createDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Létrehozás időpontja',
  `modifyDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `activateHash` varchar(64) DEFAULT NULL COMMENT 'Aktivációs kód',
  `partnerID` int(11) unsigned DEFAULT NULL,
  `resetHash` varchar(64) DEFAULT NULL,
  `firstName` varchar(64) NOT NULL,
  `lastName` varchar(64) NOT NULL,
  `lastLogin` timestamp NULL DEFAULT NULL,
  `imageFileID` int(11) unsigned DEFAULT NULL,
  `settings` json DEFAULT NULL,
  `secret` varchar(64) DEFAULT NULL,
  `lastActiveDate` timestamp NULL DEFAULT NULL,
  `privateData` json DEFAULT NULL,
  PRIMARY KEY (`userID`),
  UNIQUE KEY `unique_email` (`email`),
  UNIQUE KEY `unique_username` (`username`),
  KEY `index_activateHash` (`activateHash`) USING BTREE,
  KEY `index_createDate` (`createDate`) USING BTREE,
  KEY `index_groupID` (`groupID`) USING BTREE,
  KEY `index_imageFileID1` (`imageFileID`) USING BTREE,
  KEY `index_lastActiveDate` (`lastActiveDate`) USING BTREE,
  KEY `index_modifyDate` (`modifyDate`) USING BTREE,
  KEY `index_partnerID1` (`partnerID`) USING BTREE,
  KEY `index_resetHash` (`resetHash`) USING BTREE,
  KEY `index_status` (`status`) USING BTREE,
  CONSTRAINT `cms__file_cms__user_fileID_imageFIleID` FOREIGN KEY (`imageFileID`) REFERENCES `cms__file` (`fileID`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `cms__group_cms__user_groupID` FOREIGN KEY (`groupID`) REFERENCES `cms__group` (`groupID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `lnk_smd__partner_cms__user` FOREIGN KEY (`partnerID`) REFERENCES `smd__partner` (`partnerID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `cron__jobs`
--

CREATE TABLE `cron__jobs` (
  `taskName` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `timing` mediumint(9) unsigned NOT NULL COMMENT 'Hány percenként fusson',
  `status` enum('started','finished') COLLATE utf8_unicode_ci DEFAULT NULL,
  `lastRun` datetime DEFAULT NULL,
  `started` datetime DEFAULT NULL,
  `timeout` smallint(6) unsigned NOT NULL COMMENT 'Hány percig futhat',
  `active` enum('yes','no') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`taskName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Cronjobok';

--
-- Table structure for table `smd__activity`
--

CREATE TABLE `smd__activity` (
  `activityID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `userID` int(10) unsigned NOT NULL,
  `objectType` varchar(45) NOT NULL,
  `objectID` int(10) unsigned NOT NULL,
  `summary` text,
  `activityDate` datetime NOT NULL,
  `meta` json DEFAULT NULL,
  PRIMARY KEY (`activityID`),
  KEY `activityDate` (`activityDate`) USING BTREE,
  KEY `fk_smd__activity_cms__user1` (`userID`) USING BTREE,
  KEY `objectID` (`objectID`) USING BTREE,
  KEY `objectType` (`objectType`) USING BTREE,
  CONSTRAINT `pn__activity_ibfk_2` FOREIGN KEY (`userID`) REFERENCES `cms__user` (`userID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='tevekenysegi tortenet';

--
-- Table structure for table `smd__addon`
--

CREATE TABLE `smd__addon` (
  `addonID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text,
  `config` json DEFAULT NULL,
  `zohoAddonID` varchar(32) NOT NULL,
  PRIMARY KEY (`addonID`),
  UNIQUE KEY `unique_addonID` (`addonID`),
  UNIQUE KEY `unique_zohoAddonID` (`zohoAddonID`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__api_keys`
--

CREATE TABLE `smd__api_keys` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  `value` text,
  `type` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `smd__autofeed_post_time`
--

CREATE TABLE `smd__autofeed_post_time` (
  `postTimeID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `days` set('monday','tuesday','wednesday','thursday','friday','saturday','sunday','weekend','weekdays','everyday') NOT NULL,
  `time` time NOT NULL,
  `categoryID` int(11) unsigned DEFAULT NULL,
  `settingID` int(11) unsigned NOT NULL,
  `multiple` int(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`postTimeID`),
  KEY `index_categoryID` (`categoryID`) USING BTREE,
  KEY `index_day` (`days`) USING BTREE,
  KEY `index_multiple` (`multiple`) USING BTREE,
  KEY `index_organizationID1` (`settingID`) USING BTREE,
  KEY `index_time` (`time`) USING BTREE,
  CONSTRAINT `smd__autofeed_settings_smd__autofeed_post_times_settingID` FOREIGN KEY (`settingID`) REFERENCES `smd__autofeed_settings` (`settingID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `smd__category_smd__autofeed_post_times_categoryID_categoryID` FOREIGN KEY (`categoryID`) REFERENCES `smd__category` (`categoryID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__autofeed_post_time_has_posts`
--

CREATE TABLE `smd__autofeed_post_time_has_posts` (
  `postTimeID` int(11) unsigned NOT NULL,
  `postID` int(255) unsigned NOT NULL,
  `date` date NOT NULL,
  `serverTime` datetime DEFAULT NULL,
  PRIMARY KEY (`postID`),
  KEY `index_date` (`date`) USING BTREE,
  KEY `index_postID3` (`postID`) USING BTREE,
  KEY `index_postTimeID` (`postTimeID`) USING BTREE,
  CONSTRAINT `lnk_smd__post_smd__autofeed_post_time_has_posts` FOREIGN KEY (`postID`) REFERENCES `smd__post` (`postID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `smd__autofeed_post_times_smd__autofeed_post_time_has_post` FOREIGN KEY (`postTimeID`) REFERENCES `smd__autofeed_post_time` (`postTimeID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__autofeed_preset`
--

CREATE TABLE `smd__autofeed_preset` (
  `presetID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `settings` json NOT NULL,
  `createDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `name` varchar(128) NOT NULL,
  `autoFeedCategoryID` int(11) GENERATED ALWAYS AS (json_unquote(json_extract(`settings`,'$.categoryID'))) VIRTUAL,
  `autoFeedPeriod` varchar(32) GENERATED ALWAYS AS (json_unquote(json_extract(`settings`,'$.period'))) VIRTUAL,
  PRIMARY KEY (`presetID`),
  KEY `index_presetID` (`presetID`),
  KEY `index_createDate13` (`createDate`),
  KEY `index_name4` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__autofeed_settings`
--

CREATE TABLE `smd__autofeed_settings` (
  `settingID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `organizationID` int(11) unsigned NOT NULL,
  `socialSiteID` int(11) unsigned NOT NULL,
  `timezone` varchar(64) NOT NULL,
  `status` enum('paused','active') NOT NULL DEFAULT 'active',
  `daysOff` set('monday','tuesday','wednesday','thursday','friday','saturday','sunday') DEFAULT NULL,
  PRIMARY KEY (`settingID`),
  KEY `index_organizationID` (`organizationID`) USING BTREE,
  KEY `index_status` (`status`) USING BTREE,
  CONSTRAINT `smd__organization_smd__autofeed_settings_organizationID` FOREIGN KEY (`organizationID`) REFERENCES `smd__organization` (`organizationID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__carousel`
--

CREATE TABLE `smd__carousel` (
  `carouselID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Carousel azonosító',
  `name` varchar(128) NOT NULL COMMENT 'Carousel neve',
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `createDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modifyDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `url` varchar(255) NOT NULL,
  `userID` int(11) unsigned DEFAULT NULL,
  `socialChannel` set('facebook','linkedIn') DEFAULT NULL,
  `systemType` enum('custom','generic','branded') NOT NULL DEFAULT 'custom',
  PRIMARY KEY (`carouselID`),
  KEY `index_createDate4` (`createDate`) USING BTREE,
  KEY `index_modifyDate4` (`modifyDate`) USING BTREE,
  KEY `index_status8` (`status`) USING BTREE,
  KEY `index_userID3` (`userID`) USING BTREE,
  KEY `index_systemType` (`systemType`),
  CONSTRAINT `cms__user_smd__carousel_userID_userID` FOREIGN KEY (`userID`) REFERENCES `cms__user` (`userID`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__carousel_item`
--

CREATE TABLE `smd__carousel_item` (
  `itemID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Carousel elem azonosító',
  `carouselID` int(11) unsigned NOT NULL COMMENT 'Carousel azonosító',
  `imageFileID` int(11) unsigned DEFAULT NULL COMMENT 'Kép fájl azonosító',
  `url` varchar(255) DEFAULT NULL COMMENT 'Link',
  `status` enum('active','inactive') NOT NULL DEFAULT 'active' COMMENT 'Státusz',
  `position` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Megjelenési pozició',
  `name` varchar(64) DEFAULT NULL,
  `description` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`itemID`),
  KEY `index_carouselID1` (`carouselID`) USING BTREE,
  KEY `index_imageFileID` (`imageFileID`) USING BTREE,
  KEY `index_position3` (`position`) USING BTREE,
  KEY `index_status9` (`status`) USING BTREE,
  CONSTRAINT `cms__file_smd__carousel_item_fileID_imageFileID` FOREIGN KEY (`imageFileID`) REFERENCES `cms__file` (`fileID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `smd__carousel_smd__carousel_item_carouselID_carouselID` FOREIGN KEY (`carouselID`) REFERENCES `smd__carousel` (`carouselID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__category`
--

CREATE TABLE `smd__category` (
  `categoryID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `partnerID` int(11) unsigned DEFAULT NULL,
  `color` varchar(16) NOT NULL,
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `userID` int(11) unsigned DEFAULT NULL,
  `organizationID` int(11) unsigned DEFAULT NULL,
  `global` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`categoryID`),
  UNIQUE KEY `unique_categoryID` (`categoryID`),
  KEY `index_name1` (`name`) USING BTREE,
  KEY `index_partnerID3` (`partnerID`) USING BTREE,
  KEY `FK_userID` (`userID`),
  KEY `FK_organization` (`organizationID`),
  CONSTRAINT `FK_organization` FOREIGN KEY (`organizationID`) REFERENCES `smd__organization` (`organizationID`),
  CONSTRAINT `FK_userID` FOREIGN KEY (`userID`) REFERENCES `cms__user` (`userID`),
  CONSTRAINT `lnk_smd__partner_smd__category` FOREIGN KEY (`partnerID`) REFERENCES `smd__partner` (`partnerID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__cloud_storage_migration_backup`
--

CREATE TABLE `smd__cloud_storage_migration_backup` (
  `fileID` int(10) unsigned NOT NULL,
  `local_path` varchar(255) DEFAULT NULL,
  `status` varchar(100) DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `fail_reason` text,
  PRIMARY KEY (`fileID`),
  CONSTRAINT `smd__files_path_backup_FK` FOREIGN KEY (`fileID`) REFERENCES `cms__file` (`fileID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `smd__comment`
--

CREATE TABLE `smd__comment` (
  `commentID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `objectID` int(11) unsigned NOT NULL,
  `objectType` enum('post','postTemplate') NOT NULL,
  `userID` int(11) unsigned DEFAULT NULL,
  `text` text NOT NULL,
  `createDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`commentID`),
  UNIQUE KEY `unique_commentID` (`commentID`),
  KEY `index_createDate5` (`createDate`) USING BTREE,
  KEY `index_objectID` (`objectID`) USING BTREE,
  KEY `index_objectType` (`objectType`) USING BTREE,
  KEY `index_userID` (`userID`) USING BTREE,
  CONSTRAINT `cms__user_smd__comment_userID_userID` FOREIGN KEY (`userID`) REFERENCES `cms__user` (`userID`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__dashboard`
--

CREATE TABLE `smd__dashboard` (
  `dashboardID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `data` json DEFAULT NULL,
  PRIMARY KEY (`dashboardID`),
  UNIQUE KEY `unique_dashboardID` (`dashboardID`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__dashboard_has_users`
--

CREATE TABLE `smd__dashboard_has_users` (
  `dashboardID` int(11) unsigned NOT NULL,
  `userID` int(11) unsigned NOT NULL,
  `type` enum('owner','user') NOT NULL DEFAULT 'user',
  `permissions` set('view','edit','delete') NOT NULL DEFAULT 'view',
  PRIMARY KEY (`dashboardID`,`userID`),
  KEY `index_dashboardID` (`dashboardID`) USING BTREE,
  KEY `index_userID1` (`userID`) USING BTREE,
  CONSTRAINT `cms__user_smd__dashboard_has_users_userID_userID` FOREIGN KEY (`userID`) REFERENCES `cms__user` (`userID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `smd__dashboard_smd__dashboard_has_users_dashboardID_dashboardID` FOREIGN KEY (`dashboardID`) REFERENCES `smd__dashboard` (`dashboardID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__default_role`
--

CREATE TABLE `smd__default_role` (
  `defaultRoleID` varchar(64) NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  `description` varchar(100) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'inactive',
  `admin` enum('yes','no') NOT NULL DEFAULT 'no',
  `partnerID` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`defaultRoleID`),
  KEY `index_admin` (`admin`) USING BTREE,
  KEY `index_name3` (`name`) USING BTREE,
  KEY `index_status10` (`status`) USING BTREE,
  KEY `fk.partner` (`partnerID`),
  CONSTRAINT `fk.partner` FOREIGN KEY (`partnerID`) REFERENCES `smd__partner` (`partnerID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for 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`),
  KEY `index_resourceID2` (`resourceID`),
  KEY `index_defaultRoleID` (`defaultRoleID`),
  CONSTRAINT `cms__resource_smd__default_role_has_resources_resourceID` FOREIGN KEY (`resourceID`) REFERENCES `cms__resource` (`resourceID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `smd__default_role_smd__default_role_has_resources_defaultRoleID` FOREIGN KEY (`defaultRoleID`) REFERENCES `smd__default_role` (`defaultRoleID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__email_queue`
--

CREATE TABLE `smd__email_queue` (
  `emailQueueID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `from` varchar(64) DEFAULT NULL,
  `fromName` varchar(64) DEFAULT NULL,
  `to` varchar(64) NOT NULL,
  `toName` varchar(64) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `content` longtext CHARACTER SET utf8mb4 NOT NULL,
  `createDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `sent` timestamp NULL DEFAULT NULL,
  `attempt` int(11) unsigned DEFAULT NULL,
  `maxAttempts` int(11) unsigned NOT NULL DEFAULT '3',
  `lastAttempt` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`emailQueueID`),
  UNIQUE KEY `unique_emailQueueID` (`emailQueueID`),
  KEY `index_timestamp` (`createDate`),
  KEY `index_sent` (`sent`),
  KEY `index_attempt` (`attempt`),
  KEY `index_maxAttempts` (`maxAttempts`),
  KEY `index_lastAttempt` (`lastAttempt`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__events`
--

CREATE TABLE `smd__events` (
  `eventID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(64) NOT NULL,
  `eventTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `params` json DEFAULT NULL,
  `status` enum('done','waiting') NOT NULL DEFAULT 'waiting',
  `nextProcessTime` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`eventID`),
  UNIQUE KEY `unique_eventID` (`eventID`),
  KEY `index_eventTime` (`eventTime`),
  KEY `index_status1` (`status`),
  KEY `index_type8` (`type`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__hashtag_collection`
--

CREATE TABLE `smd__hashtag_collection` (
  `collectionID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `tags` text NOT NULL,
  `userID` int(11) unsigned DEFAULT NULL,
  `orgOfUser` int(11) unsigned DEFAULT NULL,
  `createDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`collectionID`),
  UNIQUE KEY `unique_collectionID` (`collectionID`),
  KEY `index_name` (`name`) USING BTREE,
  KEY `index_createDate8` (`createDate`),
  KEY `smd__hashtag_collection_FK` (`userID`),
  KEY `smd__hashtag_collection_FK_1` (`orgOfUser`),
  CONSTRAINT `smd__hashtag_collection_FK` FOREIGN KEY (`userID`) REFERENCES `cms__user` (`userID`),
  CONSTRAINT `smd__hashtag_collection_FK_1` FOREIGN KEY (`orgOfUser`) REFERENCES `smd__organization` (`organizationID`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__hashtag_tag`
--

CREATE TABLE `smd__hashtag_tag` (
  `tagID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `tag` varchar(255) NOT NULL,
  `userID` int(11) unsigned DEFAULT NULL,
  `orgOfUser` int(11) unsigned DEFAULT NULL,
  `createDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`tagID`),
  UNIQUE KEY `unique_tagID` (`tagID`),
  KEY `index_tag` (`tag`) USING BTREE,
  KEY `index_createDate9` (`createDate`),
  KEY `smd__hashtag_tag_FK` (`userID`),
  KEY `smd__hashtag_tag_FK_1` (`orgOfUser`),
  CONSTRAINT `smd__hashtag_tag_FK` FOREIGN KEY (`userID`) REFERENCES `cms__user` (`userID`),
  CONSTRAINT `smd__hashtag_tag_FK_1` FOREIGN KEY (`orgOfUser`) REFERENCES `smd__organization` (`organizationID`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for 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`),
  KEY `index_date1` (`date`) USING BTREE,
  KEY `index_partnerID4` (`partnerID`) USING BTREE,
  KEY `index_postFromMediaNumber` (`postFromMediaNumber`) USING BTREE,
  KEY `index_postLimit` (`postFromTemplateNumber`) USING BTREE,
  KEY `index_rssLimit` (`postFromRssNumber`) USING BTREE,
  CONSTRAINT `smd__partner_smd__limit_partnerID` FOREIGN KEY (`partnerID`) REFERENCES `smd__partner` (`partnerID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__media_has_categories`
--

CREATE TABLE `smd__media_has_categories` (
  `mediaID` int(11) unsigned NOT NULL,
  `categoryID` int(11) unsigned NOT NULL,
  `mediaType` enum('file','carousel') NOT NULL,
  PRIMARY KEY (`mediaID`,`categoryID`,`mediaType`),
  KEY `index_categoryID2` (`categoryID`) USING BTREE,
  KEY `index_mediaID2` (`mediaID`) USING BTREE,
  KEY `index_mediaType3` (`mediaType`) USING BTREE,
  CONSTRAINT `smd__category_smd__category_categoryID_categoryID` FOREIGN KEY (`categoryID`) REFERENCES `smd__category` (`categoryID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__media_has_tags`
--

CREATE TABLE `smd__media_has_tags` (
  `mediaID` int(11) unsigned NOT NULL,
  `tagID` int(11) unsigned NOT NULL,
  `mediaType` enum('file','carousel') NOT NULL,
  PRIMARY KEY (`mediaID`,`tagID`,`mediaType`),
  KEY `index_mediaID` (`mediaID`) USING BTREE,
  KEY `index_mediaType` (`mediaType`) USING BTREE,
  KEY `index_tagID1` (`tagID`) USING BTREE,
  CONSTRAINT `cms__tag_smd__media_has_tags_tagID_tagID` FOREIGN KEY (`tagID`) REFERENCES `cms__tag` (`tagID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__messaging_feed`
--

CREATE TABLE `smd__messaging_feed` (
  `feedID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `pageID` varchar(32) NOT NULL,
  `entityID` varchar(32) NOT NULL,
  `status` enum('active','archived') NOT NULL DEFAULT 'active',
  `createdTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `lastInteractionTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `data` json NOT NULL,
  `fromID` varchar(32) DEFAULT NULL,
  `type` enum('post','conversation') NOT NULL DEFAULT 'post',
  `socialType` enum('facebook') NOT NULL,
  `message` text CHARACTER SET utf8mb4 GENERATED ALWAYS AS (ifnull(json_unquote(json_extract(`data`,'$.message')),json_unquote(json_extract(`data`,'$.story')))) STORED,
  PRIMARY KEY (`feedID`),
  UNIQUE KEY `unique_feedID` (`feedID`),
  KEY `index_pageID2` (`pageID`),
  KEY `index_entityID` (`entityID`),
  KEY `index_status12` (`status`),
  KEY `index_createdTime` (`createdTime`),
  KEY `index_lastInteractionTime` (`lastInteractionTime`),
  KEY `index_fromID` (`fromID`),
  KEY `index_type9` (`type`),
  KEY `index_socialType2` (`socialType`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__messaging_webhook_events`
--

CREATE TABLE `smd__messaging_webhook_events` (
  `eventID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `object` enum('post','page') NOT NULL,
  `entryID` varchar(32) NOT NULL,
  `entryTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `changes` json NOT NULL,
  `processed` enum('yes','no') NOT NULL DEFAULT 'no',
  `socialType` enum('facebook') NOT NULL,
  PRIMARY KEY (`eventID`),
  UNIQUE KEY `unique_eventID` (`eventID`),
  KEY `index_object` (`object`),
  KEY `index_entryID` (`entryID`),
  KEY `index_entryTime` (`entryTime`),
  KEY `index_processed` (`processed`),
  KEY `index_socialType1` (`socialType`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__notifications`
--

CREATE TABLE `smd__notifications` (
  `notificationID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(255) NOT NULL,
  `group` varchar(255) DEFAULT NULL,
  `createDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `notificationData` json DEFAULT NULL,
  `actorID` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`notificationID`),
  KEY `FK_actor` (`actorID`),
  CONSTRAINT `FK_actor` FOREIGN KEY (`actorID`) REFERENCES `cms__user` (`userID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__organization`
--

CREATE TABLE `smd__organization` (
  `organizationID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `parentID` int(11) unsigned DEFAULT NULL,
  `partnerID` int(11) unsigned NOT NULL,
  `organizationData` json DEFAULT NULL,
  PRIMARY KEY (`organizationID`),
  KEY `index_parentID` (`parentID`) USING BTREE,
  KEY `index_partnerID` (`partnerID`) USING BTREE,
  CONSTRAINT `lnk_smd__group_smd__group` FOREIGN KEY (`parentID`) REFERENCES `smd__organization` (`organizationID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `lnk_smd__partner_smd__group` FOREIGN KEY (`partnerID`) REFERENCES `smd__partner` (`partnerID`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 */ /*!50003 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 */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 */ /*!50003 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 */;;
DELIMITER ;

--
-- Table structure for table `smd__organization_has_autofeed_preset`
--

CREATE TABLE `smd__organization_has_autofeed_preset` (
  `organizationID` int(11) unsigned NOT NULL,
  `presetID` int(11) unsigned NOT NULL,
  PRIMARY KEY (`organizationID`,`presetID`),
  UNIQUE KEY `unique` (`organizationID`,`presetID`),
  KEY `index_presetID1` (`presetID`),
  KEY `index_organizationID10` (`organizationID`),
  CONSTRAINT `smd__autofeed_preset_smd__organization_has_autofeed_preset` FOREIGN KEY (`presetID`) REFERENCES `smd__autofeed_preset` (`presetID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `smd__organization_smd__organization_has_autofeed_preset` FOREIGN KEY (`organizationID`) REFERENCES `smd__organization` (`organizationID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__organization_has_category`
--

CREATE TABLE `smd__organization_has_category` (
  `organizationID` int(11) unsigned NOT NULL,
  `categoryID` int(11) unsigned NOT NULL,
  KEY `FK_organization_ID` (`organizationID`),
  KEY `FK_category_ID` (`categoryID`),
  CONSTRAINT `FK_category_ID` FOREIGN KEY (`categoryID`) REFERENCES `smd__category` (`categoryID`),
  CONSTRAINT `FK_organization_ID` FOREIGN KEY (`organizationID`) REFERENCES `smd__organization` (`organizationID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `smd__organization_has_hashtag_collection`
--

CREATE TABLE `smd__organization_has_hashtag_collection` (
  `organizationID` int(11) unsigned NOT NULL,
  `collectionID` int(11) unsigned NOT NULL,
  PRIMARY KEY (`collectionID`,`organizationID`),
  KEY `index_organizationID` (`organizationID`),
  KEY `index_collectionID` (`collectionID`),
  CONSTRAINT `smd__hashtag_collection_smd__organization_has_hashtag_collection` FOREIGN KEY (`collectionID`) REFERENCES `smd__hashtag_collection` (`collectionID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `smd__organization_smd__organization_has_hashtag_collection` FOREIGN KEY (`organizationID`) REFERENCES `smd__organization` (`organizationID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__organization_has_hashtag_tag`
--

CREATE TABLE `smd__organization_has_hashtag_tag` (
  `organizationID` int(11) unsigned NOT NULL,
  `tagID` int(11) unsigned NOT NULL,
  PRIMARY KEY (`organizationID`,`tagID`),
  KEY `index_tagID2` (`tagID`) USING BTREE,
  KEY `index_organizationID7` (`organizationID`) USING BTREE,
  CONSTRAINT `smd__hashtag_tag_smd__organization_has_hashtag_tag` FOREIGN KEY (`tagID`) REFERENCES `smd__hashtag_tag` (`tagID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `smd__organization_smd__organization_has_hashtag_tag` FOREIGN KEY (`organizationID`) REFERENCES `smd__organization` (`organizationID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__organization_has_media`
--

CREATE TABLE `smd__organization_has_media` (
  `mediaID` int(11) unsigned NOT NULL,
  `organizationID` int(11) unsigned NOT NULL,
  `mediaType` enum('file','carousel') NOT NULL,
  PRIMARY KEY (`mediaID`,`mediaType`,`organizationID`),
  KEY `index_mediaID1` (`mediaID`) USING BTREE,
  KEY `index_mediaType2` (`mediaType`) USING BTREE,
  KEY `index_organizationID3` (`organizationID`) USING BTREE,
  CONSTRAINT `smd__organization_smd__organization_has_media_organizationID` FOREIGN KEY (`organizationID`) REFERENCES `smd__organization` (`organizationID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__organization_has_rss_feeds`
--

CREATE TABLE `smd__organization_has_rss_feeds` (
  `organizationID` int(11) unsigned NOT NULL,
  `feedID` int(11) unsigned NOT NULL,
  PRIMARY KEY (`organizationID`,`feedID`),
  KEY `index_feedID2` (`feedID`),
  KEY `index_organizationID9` (`organizationID`),
  CONSTRAINT `smd__organization_smd__organization_has_rss_feeds_organizationID` FOREIGN KEY (`organizationID`) REFERENCES `smd__organization` (`organizationID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `smd__rss_feed_smd__organization_has_rss_feeds_feedID_feedID` FOREIGN KEY (`feedID`) REFERENCES `smd__rss_feed` (`feedID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__organization_has_rss_groups`
--

CREATE TABLE `smd__organization_has_rss_groups` (
  `organizationID` int(11) unsigned NOT NULL,
  `groupID` int(11) unsigned NOT NULL,
  PRIMARY KEY (`organizationID`,`groupID`),
  KEY `index_groupID5` (`groupID`),
  KEY `index_organizationID8` (`organizationID`),
  CONSTRAINT `smd__organization_smd__organization_has_rss_groups` FOREIGN KEY (`organizationID`) REFERENCES `smd__organization` (`organizationID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `smd__rss_group_smd__organization_has_rss_groups_groupID_groupID` FOREIGN KEY (`groupID`) REFERENCES `smd__rss_group` (`groupID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__organization_has_social_site`
--

CREATE TABLE `smd__organization_has_social_site` (
  `organizationID` int(11) unsigned NOT NULL,
  `siteID` int(11) unsigned NOT NULL,
  PRIMARY KEY (`organizationID`,`siteID`),
  KEY `index_organizationID4` (`organizationID`) USING BTREE,
  KEY `index_siteID1` (`siteID`) USING BTREE,
  CONSTRAINT `smd__organization_smd__organization_has_social_site` FOREIGN KEY (`organizationID`) REFERENCES `smd__organization` (`organizationID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `smd__social_site_smd__organization_has_social_site_siteID_siteID` FOREIGN KEY (`siteID`) REFERENCES `smd__social_site` (`siteID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__organization_role`
--

CREATE TABLE `smd__organization_role` (
  `roleID` varchar(500) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `organizationID` int(11) unsigned NOT NULL,
  `admin` enum('yes','no') NOT NULL DEFAULT 'no',
  PRIMARY KEY (`roleID`),
  KEY `index_organizationID` (`organizationID`) USING BTREE,
  CONSTRAINT `lnk_smd__organization_smd__organization_role` FOREIGN KEY (`organizationID`) REFERENCES `smd__organization` (`organizationID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__organization_role_has_resources`
--

CREATE TABLE `smd__organization_role_has_resources` (
  `roleID` varchar(255) NOT NULL,
  `resourceID` varchar(100) NOT NULL,
  PRIMARY KEY (`roleID`,`resourceID`),
  KEY `index_resourceID1` (`resourceID`) USING BTREE,
  KEY `index_roleID2` (`roleID`) USING BTREE,
  CONSTRAINT `lnk_cms__resource_smd__oraginaziton_role_has_resources` FOREIGN KEY (`resourceID`) REFERENCES `cms__resource` (`resourceID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `lnk_smd__organization_role_smd__oraginaziton_role_has_resources` FOREIGN KEY (`roleID`) REFERENCES `smd__organization_role` (`roleID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__partner`
--

CREATE TABLE `smd__partner` (
  `partnerID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `partnerData` json DEFAULT NULL,
  `deleteRequested` timestamp NULL DEFAULT NULL COMMENT 'Törlést kérvényezték és mikor',
  `imageFileID` int(11) unsigned DEFAULT NULL,
  `zohoCustomerID` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`partnerID`),
  KEY `index_deleteRequested` (`deleteRequested`),
  KEY `index_imageFileID` (`imageFileID`),
  KEY `index_zohoCustomerID` (`zohoCustomerID`),
  CONSTRAINT `cms__file_smd__partner_fileID_imageFileID` FOREIGN KEY (`imageFileID`) REFERENCES `cms__file` (`fileID`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__partner_config`
--

CREATE TABLE `smd__partner_config` (
  `partnerID` int(11) unsigned NOT NULL,
  `subscriptionID` int(11) unsigned DEFAULT NULL,
  `config` json DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `partnerConfigID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`partnerConfigID`),
  UNIQUE KEY `partnerID_subscriptionID_u` (`partnerID`,`subscriptionID`),
  KEY `index_status1` (`status`) USING BTREE,
  KEY `index_subscriptionID2` (`subscriptionID`) USING BTREE,
  CONSTRAINT `smd__partner_smd__partner_config_partnerID_partnerID` FOREIGN KEY (`partnerID`) REFERENCES `smd__partner` (`partnerID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `smd__subscription_smd__partner_config_subscriptionID` FOREIGN KEY (`subscriptionID`) REFERENCES `smd__subscription` (`subscriptionID`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__partner_has_media`
--

CREATE TABLE `smd__partner_has_media` (
  `partnerID` int(11) unsigned NOT NULL,
  `mediaID` int(11) unsigned NOT NULL,
  `mediaType` enum('file','carousel') NOT NULL,
  `allowed` enum('yes','no') NOT NULL DEFAULT 'yes' COMMENT 'Engedélyezve van-e a partner számára. Brandidnél csak az kerül be akinek engedélyezve van, genericnél csak az akinél le van tiltva',
  UNIQUE KEY `unique_partnerID_mediaID_mediaType` (`partnerID`,`mediaID`,`mediaType`),
  KEY `index_partnerID5` (`partnerID`),
  KEY `index_mediaID3` (`mediaID`),
  KEY `index_mediaType4` (`mediaType`),
  KEY `index_allowed` (`allowed`),
  CONSTRAINT `smd__partner_smd__partner_has_media_partnerID` FOREIGN KEY (`partnerID`) REFERENCES `smd__partner` (`partnerID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for 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`),
  KEY `index_partnerID4` (`partnerID`),
  KEY `index_templateID2` (`templateID`),
  CONSTRAINT `smd__partner_smd__partner_has_post_templates_partnerID_partnerID` FOREIGN KEY (`partnerID`) REFERENCES `smd__partner` (`partnerID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `smd__post_template_smd__partner_has_post_templates_templateID` FOREIGN KEY (`templateID`) REFERENCES `smd__post_template` (`templateID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__plan`
--

CREATE TABLE `smd__plan` (
  `planID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text,
  `config` json DEFAULT NULL,
  `zohoPlanID` varchar(32) NOT NULL,
  PRIMARY KEY (`planID`),
  UNIQUE KEY `unique_zohoPlanID` (`zohoPlanID`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__post`
--

CREATE TABLE `smd__post` (
  `postID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `templateID` int(11) unsigned DEFAULT NULL,
  `userID` int(11) unsigned NOT NULL,
  `headline` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
  `subHeadline` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
  `mainCopy` text CHARACTER SET utf8mb4,
  `activeFrom` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `activeTo` timestamp NULL DEFAULT NULL,
  `status` enum('inactive','notApproved','waitingForApproval','approved','posted','underPost','failed','partiallyFailed') NOT NULL DEFAULT 'waitingForApproval',
  `signature` text CHARACTER SET utf8mb4,
  `organizationID` int(11) unsigned NOT NULL,
  `type` enum('customFeed','autoFeed','draft') NOT NULL DEFAULT 'customFeed',
  `feedTargetingOptions` json DEFAULT NULL,
  `parentID` int(11) unsigned DEFAULT NULL,
  `archive` json DEFAULT NULL,
  `expiryDate` timestamp NULL DEFAULT NULL,
  `expiryPeriod` int(11) unsigned DEFAULT NULL,
  `socialNetworkID` varchar(128) DEFAULT NULL,
  `isThirdParty` enum('yes','no') NOT NULL DEFAULT 'no',
  `instagramOptions` json DEFAULT NULL,
  `instantPosted` tinyint(1) DEFAULT NULL,
  `containerID` json DEFAULT NULL,
  `containerStatus` json DEFAULT NULL,
  `statusChangedBy` int(11) DEFAULT NULL,
  `previousStatus` enum('inactive','notApproved','waitingForApproval','approved','posted','underPost','failed','partiallyFailed') DEFAULT NULL,
  `statusChangedOn` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`postID`),
  UNIQUE KEY `unique_postID` (`postID`),
  KEY `index_activeTo` (`activeFrom`) USING BTREE,
  KEY `index_activeTo1` (`activeTo`) USING BTREE,
  KEY `index_organizationID5` (`organizationID`) USING BTREE,
  KEY `index_parentID1` (`parentID`) USING BTREE,
  KEY `index_postExpiryDate` (`expiryDate`) USING BTREE,
  KEY `index_postExpiryPeriod` (`expiryPeriod`) USING BTREE,
  KEY `index_status7` (`status`) USING BTREE,
  KEY `index_type6` (`type`) USING BTREE,
  KEY `index_userID` (`userID`) USING BTREE,
  KEY `smd__post_template_smd__post_templateID_templateID` (`templateID`) USING BTREE,
  CONSTRAINT `cms__user_smd__post_userID_userID` FOREIGN KEY (`userID`) REFERENCES `cms__user` (`userID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `smd__post_smd__post_postID_parentID` FOREIGN KEY (`parentID`) REFERENCES `smd__post` (`postID`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `smd__post_template_smd__post_templateID_templateID` FOREIGN KEY (`templateID`) REFERENCES `smd__post_template` (`templateID`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__post_has_categories`
--

CREATE TABLE `smd__post_has_categories` (
  `postID` int(11) unsigned NOT NULL,
  `categoryID` int(11) unsigned NOT NULL,
  PRIMARY KEY (`postID`,`categoryID`),
  KEY `index_categoryID1` (`categoryID`) USING BTREE,
  KEY `index_postID4` (`postID`) USING BTREE,
  CONSTRAINT `lnk_smd__post_smd__post_has_categories` FOREIGN KEY (`postID`) REFERENCES `smd__post` (`postID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `smd__category_smd__post_has_categories_categoryID_categoryI` FOREIGN KEY (`categoryID`) REFERENCES `smd__category` (`categoryID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__post_has_media`
--

CREATE TABLE `smd__post_has_media` (
  `mediaID` int(11) unsigned NOT NULL,
  `postID` int(11) unsigned NOT NULL,
  `mediaType` enum('file','carousel') NOT NULL,
  `mediaNo` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`mediaID`,`mediaType`,`postID`),
  KEY `index_mediaType1` (`mediaType`) USING BTREE,
  KEY `index_postID2` (`postID`) USING BTREE,
  CONSTRAINT `lnk_smd__post_smd__post_has_media` FOREIGN KEY (`postID`) REFERENCES `smd__post` (`postID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__post_has_social_sites`
--

CREATE TABLE `smd__post_has_social_sites` (
  `postID` int(11) unsigned NOT NULL,
  `socialSiteID` int(11) unsigned NOT NULL,
  `serverTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `postStatus` enum('posted','waiting','failedToPost') NOT NULL DEFAULT 'waiting',
  `repeat` int(11) DEFAULT NULL,
  `sitePostID` varchar(100) DEFAULT NULL,
  `postedAt` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`postID`,`socialSiteID`,`serverTime`),
  KEY `index_postID5` (`postID`) USING BTREE,
  KEY `index_serverTime1` (`serverTime`) USING BTREE,
  KEY `index_socialSiteID` (`socialSiteID`) USING BTREE,
  CONSTRAINT `smd__post_smd__post_has_social_sites_postID_postID` FOREIGN KEY (`postID`) REFERENCES `smd__post` (`postID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `smd__social_site_smd__post_has_social_sites_siteID_socialSiteID` FOREIGN KEY (`socialSiteID`) REFERENCES `smd__social_site` (`siteID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__post_search`
--

CREATE TABLE `smd__post_search` (
  `postID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `content` text,
  PRIMARY KEY (`postID`),
  FULLTEXT KEY `idx` (`content`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__post_template`
--

CREATE TABLE `smd__post_template` (
  `templateID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `headline` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
  `subHeadline` varchar(500) CHARACTER SET utf8mb4 DEFAULT NULL,
  `mainCopy` text CHARACTER SET utf8mb4,
  `userID` int(11) unsigned NOT NULL,
  `organizationID` int(11) unsigned DEFAULT NULL,
  `socialType` enum('facebook','twitter','instagram','linkedIn','gmb') NOT NULL,
  `createDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modifyDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` enum('active','inactive','waitingForApproval','notApproved','draft') NOT NULL DEFAULT 'waitingForApproval',
  `signature` text CHARACTER SET utf8mb4,
  `feedTargetingOptions` json DEFAULT NULL,
  `archive` json DEFAULT NULL,
  `systemType` enum('generic','branded','custom') NOT NULL DEFAULT 'custom',
  `instagramOptions` json DEFAULT NULL,
  `statusChangedBy` int(11) DEFAULT NULL,
  `previousStatus` enum('active','inactive','waitingForApproval','notApproved','draft') DEFAULT NULL,
  `statusChangedOn` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`templateID`),
  UNIQUE KEY `unique_templateID` (`templateID`),
  KEY `index_createDate3` (`createDate`) USING BTREE,
  KEY `index_modifyDate3` (`modifyDate`) USING BTREE,
  KEY `index_partnerID2` (`organizationID`) USING BTREE,
  KEY `index_socialType` (`socialType`) USING BTREE,
  KEY `index_status6` (`status`) USING BTREE,
  KEY `index_userID1` (`userID`) USING BTREE,
  KEY `index_type7` (`systemType`),
  CONSTRAINT `cms__user_smd__post_template_userID_userID` FOREIGN KEY (`userID`) REFERENCES `cms__user` (`userID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__post_template_has_categories`
--

CREATE TABLE `smd__post_template_has_categories` (
  `templateID` int(11) unsigned NOT NULL,
  `categoryID` int(11) unsigned NOT NULL,
  PRIMARY KEY (`templateID`,`categoryID`),
  KEY `index_categoryID` (`categoryID`) USING BTREE,
  KEY `index_postID1` (`templateID`) USING BTREE,
  CONSTRAINT `smd__category_smd__post_has_categories_categoryID_categoryID` FOREIGN KEY (`categoryID`) REFERENCES `smd__category` (`categoryID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `smd__post_template_smd__post_template_has_categories_templateID` FOREIGN KEY (`templateID`) REFERENCES `smd__post_template` (`templateID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__post_template_has_media`
--

CREATE TABLE `smd__post_template_has_media` (
  `mediaID` int(11) unsigned NOT NULL,
  `templateID` int(11) unsigned NOT NULL,
  `mediaType` enum('file','carousel') NOT NULL,
  `mediaNo` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`mediaID`,`mediaType`,`templateID`),
  KEY `index_mediaID` (`mediaID`) USING BTREE,
  KEY `index_mediaType` (`mediaType`) USING BTREE,
  KEY `index_templateID` (`templateID`) USING BTREE,
  CONSTRAINT `smd__post_template_smd__post_template_has_media_templateID` FOREIGN KEY (`templateID`) REFERENCES `smd__post_template` (`templateID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__post_template_has_organizations`
--

CREATE TABLE `smd__post_template_has_organizations` (
  `templateID` int(11) unsigned NOT NULL,
  `organizationID` int(11) unsigned NOT NULL,
  PRIMARY KEY (`templateID`,`organizationID`),
  KEY `index_organizationID6` (`organizationID`) USING BTREE,
  KEY `index_templateID1` (`templateID`) USING BTREE,
  CONSTRAINT `smd__organization_smd__post_template_has_organizations` FOREIGN KEY (`organizationID`) REFERENCES `smd__organization` (`organizationID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `smd__post_template_smd__post_template_has_organizations` FOREIGN KEY (`templateID`) REFERENCES `smd__post_template` (`templateID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__post_template_has_tags`
--

CREATE TABLE `smd__post_template_has_tags` (
  `templateID` int(11) unsigned NOT NULL,
  `tagID` int(11) unsigned NOT NULL,
  PRIMARY KEY (`templateID`,`tagID`),
  KEY `index_postID` (`templateID`) USING BTREE,
  KEY `index_tagID1` (`tagID`) USING BTREE,
  CONSTRAINT `cms__tag_smd__post_has_tags_tagID_tagID` FOREIGN KEY (`tagID`) REFERENCES `cms__tag` (`tagID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `smd__post_template_smd__post_template_has_tags_templateID` FOREIGN KEY (`templateID`) REFERENCES `smd__post_template` (`templateID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__report`
--

CREATE TABLE `smd__report` (
  `reportID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `image` varchar(255) DEFAULT NULL,
  `organizationID` int(11) unsigned NOT NULL,
  `updatedBy` int(11) unsigned DEFAULT NULL,
  `updatedAt` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `createdBy` int(11) unsigned NOT NULL,
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` enum('active','archived') NOT NULL DEFAULT 'active',
  `organizationType` enum('single','multi') NOT NULL,
  `reportType` enum('social','inhouse') NOT NULL,
  `start` date NOT NULL,
  `end` date NOT NULL,
  `compareStart` date DEFAULT NULL,
  `compareEnd` date DEFAULT NULL,
  `dataModifiers` set('aggregated') DEFAULT NULL,
  `coverImage` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`reportID`),
  KEY `smd__report_FK_createdBy` (`createdBy`),
  KEY `smd__report_FK_organization` (`organizationID`),
  KEY `smd__report_FK_updatedBy` (`updatedBy`),
  CONSTRAINT `smd__report_FK_createdBy` FOREIGN KEY (`createdBy`) REFERENCES `cms__user` (`userID`),
  CONSTRAINT `smd__report_FK_organization` FOREIGN KEY (`organizationID`) REFERENCES `smd__organization` (`organizationID`),
  CONSTRAINT `smd__report_FK_updatedBy` FOREIGN KEY (`updatedBy`) REFERENCES `cms__user` (`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `smd__report_has_social_sites`
--

CREATE TABLE `smd__report_has_social_sites` (
  `reportID` int(11) unsigned DEFAULT NULL,
  `siteID` int(11) unsigned DEFAULT NULL,
  KEY `smd__report_has_social_site_FK_site` (`siteID`),
  KEY `smd__report_has_social_site_FK_report` (`reportID`),
  CONSTRAINT `smd__report_has_social_site_FK_report` FOREIGN KEY (`reportID`) REFERENCES `smd__report` (`reportID`),
  CONSTRAINT `smd__report_has_social_site_FK_site` FOREIGN KEY (`siteID`) REFERENCES `smd__social_site` (`siteID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `smd__report_shared_with_users`
--

CREATE TABLE `smd__report_shared_with_users` (
  `reportID` int(11) unsigned DEFAULT NULL,
  `userID` int(11) unsigned DEFAULT NULL,
  KEY `smd__report_shared_with_users_FK_user` (`userID`),
  KEY `smd__report_shared_with_users_FK_report` (`reportID`),
  CONSTRAINT `smd__report_shared_with_users_FK_report` FOREIGN KEY (`reportID`) REFERENCES `smd__report` (`reportID`),
  CONSTRAINT `smd__report_shared_with_users_FK_user` FOREIGN KEY (`userID`) REFERENCES `cms__user` (`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `smd__rss_feed`
--

CREATE TABLE `smd__rss_feed` (
  `feedID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `feedUrl` varchar(255) NOT NULL,
  `createDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `userID` int(11) unsigned DEFAULT NULL,
  `orgOfUser` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`feedID`),
  UNIQUE KEY `unique_feedID` (`feedID`),
  KEY `index_feedUrl` (`feedUrl`),
  KEY `index_createDate11` (`createDate`),
  KEY `FK_feedUser` (`userID`),
  CONSTRAINT `FK_feedUser` FOREIGN KEY (`userID`) REFERENCES `cms__user` (`userID`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__rss_group`
--

CREATE TABLE `smd__rss_group` (
  `groupID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `createDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `userID` int(11) unsigned DEFAULT NULL,
  `orgOfUser` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`groupID`),
  UNIQUE KEY `unique_groupID` (`groupID`),
  KEY `index_name` (`name`),
  KEY `index_createDate10` (`createDate`),
  KEY `FK_groupUser` (`userID`),
  CONSTRAINT `FK_groupUser` FOREIGN KEY (`userID`) REFERENCES `cms__user` (`userID`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__rss_group_has_feed`
--

CREATE TABLE `smd__rss_group_has_feed` (
  `groupID` int(11) unsigned NOT NULL,
  `feedID` int(11) unsigned NOT NULL,
  PRIMARY KEY (`groupID`,`feedID`),
  KEY `index_feedID` (`feedID`),
  KEY `index_groupID` (`groupID`),
  CONSTRAINT `smd__rss_feed_smd__rss_group_has_feed_feedID_feedID` FOREIGN KEY (`feedID`) REFERENCES `smd__rss_feed` (`feedID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `smd__rss_group_smd__rss_group_has_feed_groupID_groupID` FOREIGN KEY (`groupID`) REFERENCES `smd__rss_group` (`groupID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__rss_saved_post`
--

CREATE TABLE `smd__rss_saved_post` (
  `postID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `feedID` int(11) unsigned DEFAULT NULL,
  `userID` int(11) unsigned NOT NULL,
  `content` json NOT NULL,
  `createDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`postID`),
  UNIQUE KEY `unique_postID` (`postID`),
  KEY `index_feedID1` (`feedID`),
  KEY `index_userID4` (`userID`),
  KEY `index_createDate12` (`createDate`),
  CONSTRAINT `cms__user_smd__rss_saved_post_userID_userID` FOREIGN KEY (`userID`) REFERENCES `cms__user` (`userID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `smd__rss_feed_smd__rss_saved_post_feedID_feedID` FOREIGN KEY (`feedID`) REFERENCES `smd__rss_feed` (`feedID`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__social_site`
--

CREATE TABLE `smd__social_site` (
  `siteID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `socialType` enum('facebook','linkedIn','twitter','gmb','instagram') NOT NULL,
  `pageID` varchar(64) NOT NULL,
  `pageAccessToken` varchar(1000) NOT NULL,
  `name` varchar(75) NOT NULL,
  `timezone` varchar(64) NOT NULL DEFAULT 'Europe/London',
  `pageType` enum('page','group','user') NOT NULL DEFAULT 'page',
  `pageAccessTokenExpire` timestamp NULL DEFAULT NULL,
  `notifyEmailSent` timestamp NULL DEFAULT NULL,
  `pageAccessTokenSecret` varchar(255) DEFAULT NULL,
  `webhooksEnabled` set('feed','messages','story_insights') DEFAULT NULL,
  `settings` json DEFAULT NULL,
  `userID` int(11) unsigned DEFAULT NULL,
  `createDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modifyDate` timestamp NULL DEFAULT NULL,
  `historicDataFetchStarted` tinyint(1) NOT NULL DEFAULT '0',
  `userAccessToken` varchar(1000) DEFAULT NULL,
  `historicDataFetchDay` timestamp NULL DEFAULT NULL,
  `connectedFacebookPageID` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`siteID`),
  UNIQUE KEY `unique_siteID` (`siteID`),
  KEY `index_pageID` (`socialType`) USING BTREE,
  KEY `index_pageID1` (`pageID`) USING BTREE,
  KEY `index_pageAccessTokenExpire` (`pageAccessTokenExpire`),
  KEY `index_notifyEmailSent1` (`notifyEmailSent`),
  KEY `FK_creatorUser` (`userID`),
  CONSTRAINT `FK_creatorUser` FOREIGN KEY (`userID`) REFERENCES `cms__user` (`userID`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__subscription`
--

CREATE TABLE `smd__subscription` (
  `subscriptionID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `createDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modifyDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 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 DEFAULT NULL,
  `zohoSubscriptionID` varchar(32) DEFAULT NULL,
  `expiryDate` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`subscriptionID`),
  UNIQUE KEY `unique_zohoSubscriptionID` (`zohoSubscriptionID`),
  KEY `index_createDate` (`createDate`),
  KEY `index_modifyDate` (`modifyDate`),
  KEY `index_productID` (`planID`),
  KEY `index_status` (`status`),
  KEY `index_subscriptionID` (`subscriptionID`),
  KEY `index_expiryDate` (`expiryDate`),
  CONSTRAINT `smd__subscription_smd_plan_planID_planID` FOREIGN KEY (`planID`) REFERENCES `smd__plan` (`planID`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for 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`),
  KEY `index_addonID` (`addonID`),
  KEY `index_productID1` (`subscriptionID`),
  CONSTRAINT `smd__subscription_smd__subscription_has_addons_subscriptionID` FOREIGN KEY (`subscriptionID`) REFERENCES `smd__subscription` (`subscriptionID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `smd_addon_smd__product_has_addons_addonID_addonID` FOREIGN KEY (`addonID`) REFERENCES `smd__addon` (`addonID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for 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`),
  KEY `index_transactionID` (`transactionID`),
  KEY `index_subscriptionID1` (`subscriptionID`),
  CONSTRAINT `smd__subscription_smd__subscription_has_transaction_logs` FOREIGN KEY (`subscriptionID`) REFERENCES `smd__subscription` (`subscriptionID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `smd__transaction_log_smd__subscription_has_transaction_logs` FOREIGN KEY (`transactionID`) REFERENCES `smd__transaction_log` (`transactionID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__task_queue`
--

CREATE TABLE `smd__task_queue` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `type` set('consecutive','concurrent') NOT NULL DEFAULT 'consecutive',
  `priority` int(11) NOT NULL DEFAULT '1000',
  `createDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modifyDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` set('waiting','processing','processed','failed') NOT NULL DEFAULT 'waiting',
  `foreignData` json DEFAULT NULL,
  `foreignObjectId` int(11) DEFAULT NULL,
  `executionTime` timestamp NULL DEFAULT NULL,
  `retryTime` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `smd__template_folder`
--

CREATE TABLE `smd__template_folder` (
  `folderID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `organizationID` int(11) unsigned NOT NULL,
  `userID` int(11) unsigned NOT NULL,
  `type` enum('user','organization') NOT NULL DEFAULT 'user',
  `status` enum('active','archived') NOT NULL DEFAULT 'active',
  `editable` tinyint(1) NOT NULL DEFAULT '0',
  `assignTemplates` tinyint(1) NOT NULL DEFAULT '0',
  `smart` tinyint(1) NOT NULL DEFAULT '0',
  `pinned` tinyint(1) NOT NULL DEFAULT '0',
  `childOrgSharing` tinyint(1) NOT NULL DEFAULT '0',
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` timestamp NULL DEFAULT NULL,
  `lastUpdateAction` enum('edit','share','archive') DEFAULT NULL,
  `lastUpdatedBy` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`folderID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `smd__template_folder_has_organizations`
--

CREATE TABLE `smd__template_folder_has_organizations` (
  `folderID` int(11) unsigned NOT NULL,
  `organizationID` int(11) unsigned NOT NULL,
  PRIMARY KEY (`folderID`,`organizationID`),
  KEY `smd__template_folder_has_organizations_FK` (`organizationID`),
  CONSTRAINT `smd__template_folder_has_organizations_FK` FOREIGN KEY (`organizationID`) REFERENCES `smd__organization` (`organizationID`),
  CONSTRAINT `smd__template_folder_has_organizations_FK_1` FOREIGN KEY (`folderID`) REFERENCES `smd__template_folder` (`folderID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `smd__template_folder_has_tags`
--

CREATE TABLE `smd__template_folder_has_tags` (
  `folderID` int(11) unsigned NOT NULL,
  `tagID` int(11) unsigned NOT NULL,
  PRIMARY KEY (`folderID`,`tagID`),
  KEY `smd__template_folder_has_tags_FK_1` (`tagID`),
  CONSTRAINT `smd__template_folder_has_tags_FK` FOREIGN KEY (`folderID`) REFERENCES `smd__template_folder` (`folderID`),
  CONSTRAINT `smd__template_folder_has_tags_FK_1` FOREIGN KEY (`tagID`) REFERENCES `cms__tag` (`tagID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `smd__template_folder_has_templates`
--

CREATE TABLE `smd__template_folder_has_templates` (
  `folderID` int(11) unsigned NOT NULL,
  `templateID` int(11) unsigned NOT NULL,
  `addedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`folderID`,`templateID`),
  KEY `smd__template_folder_has_templates_FK` (`templateID`),
  CONSTRAINT `smd__template_folder_has_templates_FK` FOREIGN KEY (`templateID`) REFERENCES `smd__post_template` (`templateID`),
  CONSTRAINT `smd__template_folder_has_templates_FK_1` FOREIGN KEY (`folderID`) REFERENCES `smd__template_folder` (`folderID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `smd__thumb`
--

CREATE TABLE `smd__thumb` (
  `fileID` int(10) unsigned NOT NULL,
  `size` int(11) NOT NULL,
  `thumbType` varchar(30) NOT NULL,
  `width` smallint(6) NOT NULL,
  `height` smallint(6) NOT NULL,
  `url` varchar(255) NOT NULL,
  PRIMARY KEY (`fileID`,`thumbType`),
  KEY `index_thumbType` (`thumbType`) USING BTREE,
  CONSTRAINT `thumb__file_static` FOREIGN KEY (`fileID`) REFERENCES `cms__file_static` (`fileID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `smd__transaction_log`
--

CREATE TABLE `smd__transaction_log` (
  `transactionID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `transactionData` json NOT NULL,
  `createDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `zohoEventID` varchar(32) NOT NULL,
  PRIMARY KEY (`transactionID`),
  UNIQUE KEY `unique_transactionID` (`transactionID`),
  UNIQUE KEY `unique_zohoEventID` (`zohoEventID`),
  KEY `index_createDate6` (`createDate`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__transaction_queue`
--

CREATE TABLE `smd__transaction_queue` (
  `transactionQueueID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `queueData` json DEFAULT NULL,
  `createDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `notifyEmailSent` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`transactionQueueID`),
  KEY `index_createDate7` (`createDate`),
  KEY `index_notifyEmailSent` (`notifyEmailSent`),
  KEY `index_transactionQueueID` (`transactionQueueID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__user_has_events`
--

CREATE TABLE `smd__user_has_events` (
  `userID` int(11) unsigned NOT NULL,
  `eventID` int(11) unsigned NOT NULL,
  `emailQueueID` int(11) unsigned DEFAULT NULL,
  `processedOn` timestamp NULL DEFAULT NULL,
  `status` enum('done','waiting') NOT NULL DEFAULT 'waiting',
  `affectedOrganizationID` text,
  PRIMARY KEY (`userID`,`eventID`),
  KEY `index_eventID` (`eventID`),
  KEY `index_userID` (`userID`),
  KEY `index_processTime` (`processedOn`),
  KEY `index_status11` (`status`),
  KEY `index_emailQueueID` (`emailQueueID`),
  CONSTRAINT `smd__email_queue_smd__user_has_events_queueID_emailQueueID` FOREIGN KEY (`emailQueueID`) REFERENCES `smd__email_queue` (`emailQueueID`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `smd__events_smd__user_has_events_eventID_eventID` FOREIGN KEY (`eventID`) REFERENCES `smd__events` (`eventID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__user_has_notifications`
--

CREATE TABLE `smd__user_has_notifications` (
  `userID` int(11) unsigned NOT NULL,
  `notificationID` int(11) unsigned NOT NULL,
  `seenAt` timestamp NULL DEFAULT NULL,
  `sentAt` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`userID`,`notificationID`),
  KEY `index_userID` (`userID`),
  KEY `index_notificationID` (`notificationID`),
  KEY `index_seenAt` (`seenAt`),
  CONSTRAINT `FK_notification` FOREIGN KEY (`notificationID`) REFERENCES `smd__notifications` (`notificationID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_user` FOREIGN KEY (`userID`) REFERENCES `cms__user` (`userID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `smd__user_has_organization_role`
--

CREATE TABLE `smd__user_has_organization_role` (
  `userID` int(11) unsigned NOT NULL,
  `roleID` varchar(255) NOT NULL,
  `main` enum('yes','no') NOT NULL DEFAULT 'no',
  PRIMARY KEY (`userID`,`roleID`),
  KEY `index_userID` (`userID`) USING BTREE,
  KEY `lnk_smd__organization_role_smd__user_has_organization_role` (`roleID`) USING BTREE,
  CONSTRAINT `lnk_cms__user_smd__user_has_organization_role` FOREIGN KEY (`userID`) REFERENCES `cms__user` (`userID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `lnk_smd__organization_role_smd__user_has_organization_role` FOREIGN KEY (`roleID`) REFERENCES `smd__organization_role` (`roleID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Temporary table structure for view `view__all_media`
--

/*!50001 DROP VIEW IF EXISTS `view__all_media`*/;
/*!50001 CREATE VIEW `view__all_media` AS SELECT
 1 AS `mediaID`,
  1 AS `mediaType`,
  1 AS `socialChannel`,
  1 AS `mime`,
  1 AS `mediaName`,
  1 AS `url`,
  1 AS `previewFileUrl`,
  1 AS `size`,
  1 AS `width`,
  1 AS `height`,
  1 AS `ratio`,
  1 AS `synced`,
  1 AS `canvaId`,
  1 AS `systemType`,
  1 AS `status`,
  1 AS `userID`,
  1 AS `createDate`,
  1 AS `modifyDate`,
  1 AS `realPath`,
  1 AS `mainMediaType` */;

--
-- Temporary table structure for view `view__autofeed_posts`
--

/*!50001 DROP VIEW IF EXISTS `view__autofeed_posts`*/;
/*!50001 CREATE VIEW `view__autofeed_posts` AS SELECT
 1 AS `postID`,
  1 AS `userID`,
  1 AS `postTimeID`,
  1 AS `socialSiteID`,
  1 AS `message`,
  1 AS `fileIDs`,
  1 AS `carouselID`,
  1 AS `postStatus`,
  1 AS `postType`,
  1 AS `postDate`,
  1 AS `postTime`,
  1 AS `postDays`,
  1 AS `autoFeedStatus`,
  1 AS `autoFeedDaysOff`,
  1 AS `serverTime`,
  1 AS `serverPostTime`,
  1 AS `timezone` */;

--
-- Temporary table structure for view `view__cron`
--

/*!50001 DROP VIEW IF EXISTS `view__cron`*/;
/*!50001 CREATE VIEW `view__cron` AS SELECT
 1 AS `taskName`,
  1 AS `timing`,
  1 AS `status`,
  1 AS `lastRun`,
  1 AS `started`,
  1 AS `timeout`,
  1 AS `isMonitor`,
  1 AS `isGarbage`,
  1 AS `isNeedToRun` */;

--
-- Temporary table structure for view `view__panel`
--

/*!50001 DROP VIEW IF EXISTS `view__panel`*/;
/*!50001 CREATE VIEW `view__panel` AS SELECT
 1 AS `moduleID`,
  1 AS `panelID`,
  1 AS `panelVariantID`,
  1 AS `fileID`,
  1 AS `columnID`,
  1 AS `phpID`,
  1 AS `type`,
  1 AS `name`,
  1 AS `variantName`,
  1 AS `exclusive`,
  1 AS `status`,
  1 AS `parameters`,
  1 AS `position` */;

--
-- Temporary table structure for view `view__posts`
--

/*!50001 DROP VIEW IF EXISTS `view__posts`*/;
/*!50001 CREATE VIEW `view__posts` AS SELECT
 1 AS `postID`,
  1 AS `userID`,
  1 AS `socialSiteID`,
  1 AS `socialType`,
  1 AS `message`,
  1 AS `fileIDs`,
  1 AS `carouselID`,
  1 AS `activeFrom`,
  1 AS `serverTime`,
  1 AS `postType`,
  1 AS `postStatus`,
  1 AS `postExpiryDate`,
  1 AS `postExpiryPeriod` */;

--
-- Temporary table structure for view `view__roles`
--

/*!50001 DROP VIEW IF EXISTS `view__roles`*/;
/*!50001 CREATE VIEW `view__roles` AS SELECT
 1 AS `roleID`,
  1 AS `roleName`,
  1 AS `userID`,
  1 AS `main`,
  1 AS `organizationID`,
  1 AS `admin`,
  1 AS `partnerID` */;

--
-- Temporary table structure for view `view__template_administration`
--

/*!50001 DROP VIEW IF EXISTS `view__template_administration`*/;
/*!50001 CREATE VIEW `view__template_administration` AS SELECT
 1 AS `templateID`,
  1 AS `headline`,
  1 AS `subHeadline`,
  1 AS `mainCopy`,
  1 AS `userID`,
  1 AS `organizationID`,
  1 AS `socialType`,
  1 AS `createDate`,
  1 AS `modifyDate`,
  1 AS `status`,
  1 AS `signature`,
  1 AS `feedTargetingOptions`,
  1 AS `archive`,
  1 AS `systemType`,
  1 AS `instagramOptions`,
  1 AS `statusChangedBy`,
  1 AS `previousStatus`,
  1 AS `statusChangedOn`,
  1 AS `partnerIDs`,
  1 AS `disallowedPartnerIDs` */;


--
-- Dumping events for database 'esemdee_dev_db'
--

--
-- Dumping routines for database 'esemdee_dev_db'
--
/*!50003 DROP PROCEDURE IF EXISTS `function_validate_partnerID` */;
DELIMITER ;;
CREATE  PROCEDURE `function_validate_partnerID`(inParentID INT, inPartnerID INT)
BEGIN
	DECLARE pID INT;
	SELECT partnerID INTO pID
		FROM `smd__organization`
		WHERE organizationID = inParentID;

	
	IF pID <> inPartnerID THEN
		SIGNAL SQLSTATE '23000'
 		SET MESSAGE_TEXT = 'PartnerID violation error!';
	END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `func__remove_unused_panel_variants` */;
DELIMITER ;;
CREATE  PROCEDURE `func__remove_unused_panel_variants`(  )
    COMMENT 'Removes unused panel variants.'
BEGIN
    DELETE cms__panel_variant
    FROM cms__panel_variant
      LEFT JOIN cms__file_page_has_panels ON cms__file_page_has_panels.panelVariantID = cms__panel_variant.panelVariantID
    WHERE cms__panel_variant.type = 'local' AND cms__file_page_has_panels.phpID IS NULL;
  END ;;
DELIMITER ;
--
-- Final view structure for view `view__all_media`
--

/*!50001 DROP VIEW IF EXISTS `view__all_media`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 */
/*!50001 VIEW `view__all_media` AS (select `f`.`fileID` AS `mediaID`,`fs`.`type` AS `mediaType`,`fs`.`socialChannel` AS `socialChannel`,`fs`.`mime` AS `mime`,`f`.`name` AS `mediaName`,`f`.`url` AS `url`,`fp`.`url` AS `previewFileUrl`,`fs`.`size` AS `size`,`fs`.`data_width` AS `width`,`fs`.`data_height` AS `height`,`fs`.`data_ratio` AS `ratio`,`fs`.`data_synced` AS `synced`,`fs`.`canvaId` AS `canvaId`,`fs`.`systemType` AS `systemType`,`f`.`status` AS `status`,`f`.`userID` AS `userID`,`f`.`createDate` AS `createDate`,`f`.`modifyDate` AS `modifyDate`,`fs`.`realPath` AS `realPath`,'file' AS `mainMediaType` from ((`cms__file` `f` join `cms__file_static` `fs` on(((`fs`.`fileID` = `f`.`fileID`) and (`fs`.`type` <> 'preview')))) left join `cms__file` `fp` on((`fp`.`fileID` = `fs`.`previewFileID`)))) union (select `c`.`carouselID` AS `mediaID`,'carousel' AS `mediaType`,`c`.`socialChannel` AS `socialChannel`,'carousel' AS `mime`,`c`.`name` AS `mediaName`,`c`.`url` AS `url`,NULL AS `previewFileUrl`,NULL AS `size`,NULL AS `width`,NULL AS `height`,NULL AS `ratio`,NULL AS `synced`,NULL AS `canvaId`,`c`.`systemType` AS `systemType`,`c`.`status` AS `status`,`c`.`userID` AS `userID`,`c`.`createDate` AS `createDate`,`c`.`modifyDate` AS `modifyDate`,NULL AS `realPath`,'carousel' AS `mainMediaType` from `smd__carousel` `c`) */;

--
-- Final view structure for view `view__autofeed_posts`
--

/*!50001 DROP VIEW IF EXISTS `view__autofeed_posts`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 */
/*!50001 VIEW `view__autofeed_posts` AS select `post`.`postID` AS `postID`,`post`.`userID` AS `userID`,`apthp`.`postTimeID` AS `postTimeID`,`autoFeedSettings`.`socialSiteID` AS `socialSiteID`,concat_ws('\n\n',`post`.`headline`,`post`.`subHeadline`,`post`.`mainCopy`,`post`.`signature`) AS `message`,group_concat((case when (`phm`.`mediaType` = 'file') then `phm`.`mediaID` else NULL end) separator ',') AS `fileIDs`,(case when (`phm`.`mediaType` = 'carousel') then `phm`.`mediaID` end) AS `carouselID`,`post`.`status` AS `postStatus`,`post`.`type` AS `postType`,`apthp`.`date` AS `postDate`,`apt`.`time` AS `postTime`,`apt`.`days` AS `postDays`,`autoFeedSettings`.`status` AS `autoFeedStatus`,`autoFeedSettings`.`daysOff` AS `autoFeedDaysOff`,`postHasSocialSites`.`serverTime` AS `serverTime`,`apthp`.`serverTime` AS `serverPostTime`,`ss`.`timezone` AS `timezone` from ((((((`smd__post` `post` join `smd__post_has_social_sites` `postHasSocialSites` on((`post`.`postID` = `postHasSocialSites`.`postID`))) join `smd__social_site` `ss` on((`ss`.`siteID` = `postHasSocialSites`.`socialSiteID`))) left join `smd__post_has_media` `phm` on((`phm`.`postID` = `post`.`postID`))) join `smd__autofeed_post_time_has_posts` `apthp` on((`post`.`postID` = `apthp`.`postID`))) join `smd__autofeed_post_time` `apt` on((`apt`.`postTimeID` = `apthp`.`postTimeID`))) join `smd__autofeed_settings` `autoFeedSettings` on((`autoFeedSettings`.`settingID` = `apt`.`settingID`))) where ((`apthp`.`serverTime` <= (now() + interval '1' hour)) and (`apthp`.`serverTime` >= (now() - interval '1' hour))) group by `post`.`postID` having ((`postStatus` = 'approved') and (`postType` = 'autoFeed') and (`autoFeedStatus` = 'active')) */;

--
-- Final view structure for view `view__cron`
--

/*!50001 DROP VIEW IF EXISTS `view__cron`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 */
/*!50001 VIEW `view__cron` AS select `cron__jobs`.`taskName` AS `taskName`,`cron__jobs`.`timing` AS `timing`,`cron__jobs`.`status` AS `status`,`cron__jobs`.`lastRun` AS `lastRun`,`cron__jobs`.`started` AS `started`,`cron__jobs`.`timeout` AS `timeout`,if((`cron__jobs`.`taskName` = 'Monitor'),1,NULL) AS `isMonitor`,if(((((unix_timestamp() - unix_timestamp(`cron__jobs`.`started`)) / 60) > `cron__jobs`.`timeout`) and (`cron__jobs`.`status` = 'started')),1,NULL) AS `isGarbage`,(case when (`cron__jobs`.`status` = 'started') then NULL when (isnull(`cron__jobs`.`lastRun`) or isnull(`cron__jobs`.`status`) or isnull(`cron__jobs`.`started`)) then 1 when (((unix_timestamp() - unix_timestamp(`cron__jobs`.`started`)) / 60) >= `cron__jobs`.`timing`) then 1 else NULL end) AS `isNeedToRun` from `cron__jobs` where (`cron__jobs`.`active` = 'yes') */;

--
-- Final view structure for view `view__panel`
--

/*!50001 DROP VIEW IF EXISTS `view__panel`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 */
/*!50001 VIEW `view__panel` AS select `p`.`moduleID` AS `moduleID`,`p`.`panelID` AS `panelID`,`pv`.`panelVariantID` AS `panelVariantID`,`php`.`fileID` AS `fileID`,`php`.`columnID` AS `columnID`,`php`.`phpID` AS `phpID`,`pv`.`type` AS `type`,`p`.`name` AS `name`,`pv`.`name` AS `variantName`,`p`.`exclusive` AS `exclusive`,`p`.`status` AS `status`,if((`pv`.`parameters` is not null),`pv`.`parameters`,`p`.`parameters`) AS `parameters`,`php`.`position` AS `position` from ((`cms__panel` `p` left join `cms__panel_variant` `pv` on((`p`.`panelID` = `pv`.`panelID`))) left join `cms__file_page_has_panels` `php` on((`pv`.`panelVariantID` = `php`.`panelVariantID`))) */;

--
-- Final view structure for view `view__posts`
--

/*!50001 DROP VIEW IF EXISTS `view__posts`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 */
/*!50001 VIEW `view__posts` AS select `post`.`postID` AS `postID`,`post`.`userID` AS `userID`,`socialSite`.`siteID` AS `socialSiteID`,`socialSite`.`socialType` AS `socialType`,concat_ws('\n\n',`post`.`headline`,`post`.`subHeadline`,`post`.`mainCopy`,`post`.`signature`) AS `message`,group_concat((case when (`phm`.`mediaType` = 'file') then `phm`.`mediaID` else NULL end) order by `phm`.`mediaNo` ASC separator ',') AS `fileIDs`,(case when (`phm`.`mediaType` = 'carousel') then `phm`.`mediaID` end) AS `carouselID`,`post`.`activeFrom` AS `activeFrom`,`postHasSocialSites`.`serverTime` AS `serverTime`,`post`.`type` AS `postType`,`post`.`status` AS `postStatus`,`post`.`expiryDate` AS `postExpiryDate`,`post`.`expiryPeriod` AS `postExpiryPeriod` from (((`smd__post` `post` join `smd__post_has_social_sites` `postHasSocialSites` on((`post`.`postID` = `postHasSocialSites`.`postID`))) left join `smd__social_site` `socialSite` on((`socialSite`.`siteID` = `postHasSocialSites`.`socialSiteID`))) left join `smd__post_has_media` `phm` on((`phm`.`postID` = `post`.`postID`))) where ((`postHasSocialSites`.`serverTime` <= (now() + interval '1' hour)) and (`postHasSocialSites`.`serverTime` >= (now() - interval '1' minute)) and (`postHasSocialSites`.`postStatus` = 'waiting')) group by `postHasSocialSites`.`postID`,`postHasSocialSites`.`socialSiteID`,`postHasSocialSites`.`serverTime` having (((`postStatus` = 'approved') or (`postStatus` = 'underPost')) and (`postType` = 'customFeed')) */;

--
-- Final view structure for view `view__roles`
--

/*!50001 DROP VIEW IF EXISTS `view__roles`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 */
/*!50001 VIEW `view__roles` AS select `uhor`.`roleID` AS `roleID`,`or`.`name` AS `roleName`,`uhor`.`userID` AS `userID`,`uhor`.`main` AS `main`,`or`.`organizationID` AS `organizationID`,`or`.`admin` AS `admin`,`o`.`partnerID` AS `partnerID` from ((`smd__user_has_organization_role` `uhor` join `smd__organization_role` `or` on((`or`.`roleID` = `uhor`.`roleID`))) join `smd__organization` `o` on((`or`.`organizationID` = `o`.`organizationID`))) */;

--
-- Final view structure for view `view__template_administration`
--

/*!50001 DROP VIEW IF EXISTS `view__template_administration`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 */
/*!50001 VIEW `view__template_administration` AS (select `smd__post_template`.`templateID` AS `templateID`,`smd__post_template`.`headline` AS `headline`,`smd__post_template`.`subHeadline` AS `subHeadline`,`smd__post_template`.`mainCopy` AS `mainCopy`,`smd__post_template`.`userID` AS `userID`,`smd__post_template`.`organizationID` AS `organizationID`,`smd__post_template`.`socialType` AS `socialType`,`smd__post_template`.`createDate` AS `createDate`,`smd__post_template`.`modifyDate` AS `modifyDate`,`smd__post_template`.`status` AS `status`,`smd__post_template`.`signature` AS `signature`,`smd__post_template`.`feedTargetingOptions` AS `feedTargetingOptions`,`smd__post_template`.`archive` AS `archive`,`smd__post_template`.`systemType` AS `systemType`,`smd__post_template`.`instagramOptions` AS `instagramOptions`,`smd__post_template`.`statusChangedBy` AS `statusChangedBy`,`smd__post_template`.`previousStatus` AS `previousStatus`,`smd__post_template`.`statusChangedOn` AS `statusChangedOn`,NULL AS `partnerIDs`,group_concat(`sphpt`.`partnerID` separator ',') AS `disallowedPartnerIDs` from (`smd__post_template` left join `smd__partner_has_post_templates` `sphpt` on(((`sphpt`.`templateID` = `smd__post_template`.`templateID`) and (`sphpt`.`allowed` = 'no')))) where (`smd__post_template`.`systemType` = 'generic') group by `smd__post_template`.`templateID`) union all (select `smd__post_template`.`templateID` AS `templateID`,`smd__post_template`.`headline` AS `headline`,`smd__post_template`.`subHeadline` AS `subHeadline`,`smd__post_template`.`mainCopy` AS `mainCopy`,`smd__post_template`.`userID` AS `userID`,`smd__post_template`.`organizationID` AS `organizationID`,`smd__post_template`.`socialType` AS `socialType`,`smd__post_template`.`createDate` AS `createDate`,`smd__post_template`.`modifyDate` AS `modifyDate`,`smd__post_template`.`status` AS `status`,`smd__post_template`.`signature` AS `signature`,`smd__post_template`.`feedTargetingOptions` AS `feedTargetingOptions`,`smd__post_template`.`archive` AS `archive`,`smd__post_template`.`systemType` AS `systemType`,`smd__post_template`.`instagramOptions` AS `instagramOptions`,`smd__post_template`.`statusChangedBy` AS `statusChangedBy`,`smd__post_template`.`previousStatus` AS `previousStatus`,`smd__post_template`.`statusChangedOn` AS `statusChangedOn`,group_concat(`sphpt`.`partnerID` separator ',') AS `partnerIDs`,NULL AS `disallowedPartnerIDs` from (`smd__post_template` left join `smd__partner_has_post_templates` `sphpt` on(((`sphpt`.`templateID` = `smd__post_template`.`templateID`) and (`sphpt`.`allowed` = 'yes')))) where (`smd__post_template`.`systemType` = 'branded') group by `smd__post_template`.`templateID`) */;


