-- Tracks which posts a user has reshared. Pure join table — no
-- denormalised counters, no duplicate post rows. The act of resharing
-- is a relationship between (post, user); the post itself stays in
-- one canonical row owned by its original author.
--
-- This powers the post-card swap: posts a user has authored OR
-- reshared show the "Send" (external share) button instead of
-- "Reshare", because there's nothing left to bring onto the account.
--
-- Composite PK on (post_id, user_id) makes the relationship unique
-- and gives us a covered lookup for the "did this viewer reshare this
-- post?" check used in batched feed hydration.
--
-- Index on (user_id, created_at DESC) keeps "what has this user
-- reshared, newest first?" cheap for the day we surface that on the
-- profile feed.
CREATE TABLE IF NOT EXISTS post_reshares (
    post_id    UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    user_id    UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (post_id, user_id)
);

CREATE INDEX IF NOT EXISTS idx_post_reshares_user_created
    ON post_reshares (user_id, created_at DESC);