-- Optimization for posting (time intervals)
CREATE OR REPLACE
ALGORITHM = UNDEFINED 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) 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'))