-- Per-reshare background image overrides.
--
-- Previously every viewer of a reshare saw the canonical post_images row —
-- so when either the original author or any resharer "Updated background"
-- the change leaked across all timelines that referenced the post. This
-- table separates "the post's background" from "this user's reshare of
-- the post's background": authoring stays in post_images, resharing
-- stores its own snapshot here.
--
-- At reshare time we copy the post's current post_images into this table
-- so the resharer's feed renders identically to the author's. After that
-- moment the two are independent — the resharer's background lives only
-- here, the original author's lives only in post_images, and changes to
-- one never touch the other.
--
-- Cascade: the FK targets the (post_id, user_id) primary key on
-- post_reshares. Every path that removes a reshare relationship (the
-- author deletes the post, the resharer un-reshares, either user is
-- deleted) drops the post_reshares row, which cascades the overrides
-- away. The user's stated rule — "should not be in sync except deletion"
-- — is preserved end-to-end without any extra application code.

CREATE TABLE IF NOT EXISTS post_reshare_images (
    id          UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    post_id     UUID         NOT NULL,
    user_id     UUID         NOT NULL,
    url         TEXT         NOT NULL,
    position    INT          NOT NULL DEFAULT 0,
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT now(),
    FOREIGN KEY (post_id, user_id)
        REFERENCES post_reshares (post_id, user_id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_post_reshare_images_lookup
    ON post_reshare_images (post_id, user_id, position);