CREATE TABLE IF NOT EXISTS `gallery` (
    `id`        INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `galleryId` INT UNSIGNED NOT NULL,
    `createdAt` TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `name`      VARCHAR(64)  NULL,
    PRIMARY KEY (`id`),
    KEY `galleryId` (`galleryId`)
)
    ENGINE = InnoDB
    DEFAULT CHARSET = `utf8`;

CREATE TABLE IF NOT EXISTS `photo` (
    `id`        INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `publicId`  BINARY(16)   NOT NULL,
    `galleryId` INT UNSIGNED NOT NULL,
    `createdAt` TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `galleryId` (`galleryId`)
)
    ENGINE = InnoDB
    DEFAULT CHARSET = `utf8`;


CREATE TABLE IF NOT EXISTS `permission` (
    `permissionOwnerId`   INT        UNSIGNED NOT NULL,
    `permissionOwnerType` TINYINT    UNSIGNED NOT NULL,
    `permission`          TINYINT    UNSIGNED NOT NULL,
    `isEnabled`           TINYINT    UNSIGNED NOT NULL,
    `resourceOwnerId`     INT        UNSIGNED NOT NULL,
    `resourceOwnerType`   TINYINT    UNSIGNED NOT NULL,
    `resourceType`        TINYINT    UNSIGNED NOT NULL,
    `resourceId`          INT        UNSIGNED NOT NULL,
    PRIMARY KEY (`permissionOwnerId`, `permissionOwnerType`, `permission`, `isEnabled`, `resourceOwnerId`, `resourceOwnerType`, `resourceType`, `resourceId`)
)
    ENGINE = InnoDB
    DEFAULT CHARSET = `utf8`;

SELECT `resourceOwnerId`
FROM `permission`
WHERE `permissionOwnerId` = 'PERMISSION_OWNER_ID'
AND `permission` IN('PERMISSION', 'ANY')
AND `isEnabled` = 1
GROUP BY `resourceOwnerId`;

SELECT
    p.*
FROM `photo` AS `p`
INNER JOIN `gallery` AS `g` ON `g`.`id` = `p`.`galleryId`
INNER JOIN `permission`
ON `permission`.`permissionOwnerId` IN ('PERMISSION_OWNER_ID', 'ANY') -- PERMISSION_OWNER_ID - это id пользователя которым запрашиваем, а так же его групп
    AND `permission`.`permission` IN('PERMISSION', 'ANY')
    AND `permission`.`isEnabled` = 1
    AND `permission`.`resourceOwnerId` IN ('RESOURCE_OWNER_ID', 'ANY')
    AND `permission`.`resourceType` IN('GALLERY', 'ANY')
    AND `permission`.`resourceId` IN (`g`.`id`, 'ANY');