-- TODO: Tesztelni h jó-e egyáltalán az autofeed

CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW `view__autofeed_posts` AS
select
    `post`.`postID` AS `postID`,
    `post`.`userID` AS `userID`,
    `apthp`.`postTimeID` AS `postTimeID`,
    `autoFeedSettings`.`socialSiteID` AS `socialSiteID`,
    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`.`status` AS `postStatus`,
    `post`.`type` AS `postType`,
    `apthp`.`date` AS `postDate`,
    `apt`.`time` AS `postTime`,
    `apt`.`days` AS `postDays`,
    `autoFeedSettings`.`status` AS `autoFeedStatus`,
    `autoFeedSettings`.`daysOff` AS `autoFeedDaysOff`,
    `postHasSocialSites`.`serverTime` AS `serverTime`,
    `apthp`.`serverTime` AS `serverPostTime`,
    `ss`.`timezone` AS `timezone`
from
    ((((((`smd__post` `post`
join `smd__post_has_social_sites` `postHasSocialSites` on
    ((`post`.`postID` = `postHasSocialSites`.`postID`)))
join `smd__social_site` `ss` on
    ((`ss`.`siteID` = `postHasSocialSites`.`socialSiteID`)))
left join `smd__post_has_media` `phm` on
    ((`phm`.`postID` = `post`.`postID`)))
join `smd__autofeed_post_time_has_posts` `apthp` on
    ((`post`.`postID` = `apthp`.`postID`)))
join `smd__autofeed_post_time` `apt` on
    ((`apt`.`postTimeID` = `apthp`.`postTimeID`)))
join `smd__autofeed_settings` `autoFeedSettings` on
    ((`autoFeedSettings`.`settingID` = `apt`.`settingID`)))
where
    ((`apthp`.`serverTime` <= (now() + interval '1' hour))
        and (`apthp`.`serverTime` >= (now() - interval '1' hour)))
group by
    `post`.`postID`
having
    ((`postStatus` = 'approved')
        and (`postType` = 'autoFeed')
            and (`autoFeedStatus` = 'active'))