-- CREATE 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' ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
                                          CONSTRAINT `unique_partnerID_mediaID_mediaType` UNIQUE( `partnerID`, `mediaID`, `mediaType` ) )
    ENGINE = InnoDB;
-- -------------------------------------------------------------

-- CREATE INDEX "index_partnerID5" -----------------------------
CREATE INDEX `index_partnerID5` ON `smd__partner_has_media`( `partnerID` );
-- -------------------------------------------------------------

-- CREATE INDEX "index_mediaID3" -------------------------------
CREATE INDEX `index_mediaID3` ON `smd__partner_has_media`( `mediaID` );
-- -------------------------------------------------------------

-- CREATE INDEX "index_mediaType4" -----------------------------
CREATE INDEX `index_mediaType4` ON `smd__partner_has_media`( `mediaType` );
-- -------------------------------------------------------------




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




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

-- CREATE INDEX "index_systemType" -----------------------------
CREATE INDEX `index_systemType` ON `smd__carousel`( `systemType` );
-- -------------------------------------------------------------




-- CREATE FIELD "systemType" -----------------------------------
ALTER TABLE `cms__file_static` ADD COLUMN `systemType` Enum( 'custom', 'generic', 'branded' ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'custom';
-- -------------------------------------------------------------

-- CREATE INDEX "index_systemType1" ----------------------------
CREATE INDEX `index_systemType1` ON `cms__file_static`( `systemType` );
-- -------------------------------------------------------------

ALTER TABLE cms__file_static ADD data_width INT(5)
    AS (JSON_UNQUOTE(data->"$.width")) STORED;


ALTER TABLE cms__file_static ADD data_height INT(5)
    AS (JSON_UNQUOTE(data->"$.height")) STORED;


ALTER TABLE cms__file_static ADD data_ratio VARCHAR(20)
    AS (JSON_UNQUOTE(data->"$.ratio")) STORED;

-- CREATE VIEW "view__all_media" --------------------------------
CREATE OR REPLACE VIEW `view__all_media`
AS
    (
        SELECT
            f.fileID AS mediaID,
            fs.type AS mediaType,
            fs.socialChannel,
            fs.mime,
            f.name AS mediaName,
            f.url,
            fp.url AS previewFileUrl,
            fs.size,
            fs.data_width AS width,
            fs.data_height AS height,
            fs.data_ratio AS ratio,
            fs.systemType,
            f.status,
            f.userID,
            f.createDate,
            f.modifyDate,
            '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,
            'carousel' AS mime,
            c.name AS mediaName,
            c.url,
            null AS previewFileUrl,
            null AS size,
            null AS width,
            null AS height,
            null AS ratio,
            c.systemType,
            c.status,
            c.userID,
            c.createDate,
            c.modifyDate,
            'carousel' AS mainMediaType
        FROM smd__carousel c
    );
-- -------------------------------------------------------------


-- CREATE FIELD "allowed" --------------------------------------
ALTER TABLE `smd__partner_has_media` ADD COLUMN `allowed` Enum( 'yes', 'no' ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL 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';
-- -------------------------------------------------------------

-- CREATE INDEX "index_allowed" --------------------------------
CREATE INDEX `index_allowed` ON `smd__partner_has_media`( `allowed` );
-- -------------------------------------------------------------

