CREATE OR REPLACE 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) 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 AS post
INNER JOIN
    smd__post_has_social_sites AS postHasSocialSites
ON
    post.postID = postHasSocialSites.postID
JOIN
  smd__social_site as ss
ON
  ss.siteID = postHasSocialSites.socialSiteID

LEFT JOIN
    smd__post_has_media AS phm
ON
    phm.postID = post.postID
JOIN
    smd__autofeed_post_time_has_posts AS apthp
ON
    post.postID = apthp.postID
JOIN
    smd__autofeed_post_time AS apt
ON
    apt.postTimeID = apthp.postTimeID
JOIN
    smd__autofeed_settings AS autoFeedSettings
ON
    autoFeedSettings.settingID = apt.settingID
GROUP BY
    post.postID
HAVING
    postStatus = 'approved' AND postType = 'autoFeed' AND autoFeedStatus = 'active'