-- 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.canvaId,
            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,
            null AS canvaId,
            c.systemType,
            c.status,
            c.userID,
            c.createDate,
            c.modifyDate,
            'carousel' AS mainMediaType
        FROM smd__carousel c
    );
-- -------------------------------------------------------------

-- Viewok droppolása
-- Lekérni a parancsot
SET @s:='';
SELECT @s:=concat(@s, 'DROP VIEW `', ist.TABLE_SCHEMA,'`.',  ist.TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES ist WHERE TABLE_NAME LIKE 'view__all_media_%';

-- Kiírod, ha jónak néz ki kimásolod és lecgó!
SELECT @s;