ALTER TABLE "social_posts"
  DROP CONSTRAINT IF EXISTS "social_posts_content_required";

CREATE OR REPLACE FUNCTION "check_social_post_has_media"()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW."status" = 'PUBLISHED'
     AND NOT EXISTS (
       SELECT 1
       FROM "social_post_media"
       WHERE "post_id" = NEW."id"
     ) THEN
    RAISE EXCEPTION 'published social post must contain media'
      USING ERRCODE = '23514',
            CONSTRAINT = 'social_posts_media_required';
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER "social_posts_media_required"
AFTER INSERT OR UPDATE ON "social_posts"
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
EXECUTE FUNCTION "check_social_post_has_media"();

CREATE OR REPLACE FUNCTION "check_social_post_after_media_removal"()
RETURNS TRIGGER AS $$
BEGIN
  IF EXISTS (
       SELECT 1
       FROM "social_posts"
       WHERE "id" = OLD."post_id"
         AND "status" = 'PUBLISHED'
     )
     AND NOT EXISTS (
       SELECT 1
       FROM "social_post_media"
       WHERE "post_id" = OLD."post_id"
     ) THEN
    RAISE EXCEPTION 'published social post must contain media'
      USING ERRCODE = '23514',
            CONSTRAINT = 'social_posts_media_required';
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER "social_post_media_removal_guard"
AFTER DELETE OR UPDATE OF "post_id" ON "social_post_media"
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
EXECUTE FUNCTION "check_social_post_after_media_removal"();