USE dev;
CREATE TABLE `portal_events` (
          `Id` char(36) NOT NULL,
          `Title` longtext NULL,
          `Text` longtext NULL,
          `Poster` longtext NULL,
          `Place` longtext NULL,
          `Date` datetime(6) NOT NULL,
          CONSTRAINT `PK_portal_events` PRIMARY KEY (`Id`)
      );

      CREATE TABLE `portal_news` (
          `Id` char(36) NOT NULL,
          `Title` longtext NULL,
          `Text` longtext NULL,
          `Published` datetime(6) NOT NULL,
          CONSTRAINT `PK_portal_news` PRIMARY KEY (`Id`)
      );

      CREATE TABLE `portal_offices` (
          `Id` char(36) NOT NULL,
          `Name` longtext NULL,
          `Photo` longtext NULL,
          `IpRange` longtext NULL,
          `Address` longtext NULL,
          CONSTRAINT `PK_portal_offices` PRIMARY KEY (`Id`)
      );

      CREATE TABLE `portal_tags` (
          `Id` char(36) NOT NULL,
          `Name` longtext NULL,
          CONSTRAINT `PK_portal_tags` PRIMARY KEY (`Id`)
      );

      CREATE TABLE `portal_topic` (
          `Id` char(36) NOT NULL,
          `Name` longtext NULL,
          `Icon` longtext NULL,
          `EventEntityId` char(36) NULL,
          `NewsEntityId` char(36) NULL,
          CONSTRAINT `PK_portal_topic` PRIMARY KEY (`Id`),
          CONSTRAINT `FK_portal_topic_portal_events_EventEntityId` FOREIGN KEY (`EventEntityId`) REFERENCES `portal_events` (`Id`) ON DELETE CASCADE,
          CONSTRAINT `FK_portal_topic_portal_news_NewsEntityId` FOREIGN KEY (`NewsEntityId`) REFERENCES `portal_news` (`Id`) ON DELETE CASCADE
      );

      CREATE TABLE `portal_eventoffice` (
          `EventId` char(36) NOT NULL,
          `OfficeId` char(36) NOT NULL,
          CONSTRAINT `PK_portal_eventoffice` PRIMARY KEY (`EventId`, `OfficeId`),
          CONSTRAINT `FK_portal_eventoffice_portal_events_EventId` FOREIGN KEY (`EventId`) REFERENCES `portal_events` (`Id`) ON DELETE CASCADE,
          CONSTRAINT `FK_portal_eventoffice_portal_offices_OfficeId` FOREIGN KEY (`OfficeId`) REFERENCES `portal_offices` (`Id`) ON DELETE CASCADE
      );

      CREATE TABLE `portal_hr` (
          `Id` char(36) NOT NULL,
          `Name` longtext NULL,
          `Photo` longtext NULL,
          `Skype` longtext NULL,
          `Phone` longtext NULL,
          `Email` longtext NULL,
          `OfficeId` char(36) NOT NULL,
          CONSTRAINT `PK_portal_hr` PRIMARY KEY (`Id`),
          CONSTRAINT `FK_portal_hr_portal_offices_OfficeId` FOREIGN KEY (`OfficeId`) REFERENCES `portal_offices` (`Id`) ON DELETE CASCADE
      );

      CREATE TABLE `portal_newstag` (
          `NewsId` char(36) NOT NULL,
          `TagId` char(36) NOT NULL,
          CONSTRAINT `PK_portal_newstag` PRIMARY KEY (`NewsId`, `TagId`),
          CONSTRAINT `FK_portal_newstag_portal_news_NewsId` FOREIGN KEY (`NewsId`) REFERENCES `portal_news` (`Id`) ON DELETE CASCADE,
          CONSTRAINT `FK_portal_newstag_portal_tags_TagId` FOREIGN KEY (`TagId`) REFERENCES `portal_tags` (`Id`) ON DELETE CASCADE
      );

      CREATE INDEX `IX_portal_eventoffice_OfficeId` ON `portal_eventoffice` (`OfficeId`);

      CREATE INDEX `IX_portal_hr_OfficeId` ON `portal_hr` (`OfficeId`);

      CREATE INDEX `IX_portal_newstag_TagId` ON `portal_newstag` (`TagId`);

      CREATE INDEX `IX_portal_topic_EventEntityId` ON `portal_topic` (`EventEntityId`);

      CREATE INDEX `IX_portal_topic_NewsEntityId` ON `portal_topic` (`NewsEntityId`);