-- Reshares become real `posts` rows.
--
-- Previously a reshare was a join-table relationship between (post, user)
-- that pointed at the original author's canonical row. Likes, comments,
-- and (most recently) background overrides all had to be plumbed through
-- a parallel "per-resharer" path to avoid leaking across timelines. The
-- next set of social features (likes, comments) would multiply that
-- complexity again.
--
-- This migration collapses that model: every reshare is its own row in
-- `posts`, owned by the resharer, with a `reshared_from_post_id` pointer
-- back to the canonical original. Likes and comments will naturally hang
-- off post.id and diverge for free; backgrounds are already per-row in
-- post_images so the per-reshare override table goes away.
--
-- Deletion still cascades: the FK uses ON DELETE CASCADE on
-- reshared_from_post_id, so an author deleting their post nukes every
-- reshare of it (including their post_images via the existing FK).

-- 1. New self-referential column. Nullable: NULL on canonical posts,
--    set on reshares.
ALTER TABLE posts
    ADD COLUMN IF NOT EXISTS reshared_from_post_id UUID
        REFERENCES posts(id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS idx_posts_reshared_from
    ON posts (reshared_from_post_id)
    WHERE reshared_from_post_id IS NOT NULL;

-- Composite index used by ReshareStatesFor — given a viewer + a batch of
-- canonical post ids, "which of these has the viewer reshared?".
CREATE INDEX IF NOT EXISTS idx_posts_user_reshared_from
    ON posts (user_id, reshared_from_post_id)
    WHERE reshared_from_post_id IS NOT NULL;

-- 2. Backfill: each row in post_reshares becomes a real `posts` row,
--    copying the canonical post's textual fields and the timestamp the
--    reshare originally happened. The new row is owned by the resharer.
--
--    Skipped if post_reshares doesn't exist (clean install) — the
--    DO block uses to_regclass so a fresh database doesn't error.
DO $$
BEGIN
    IF to_regclass('public.post_reshares') IS NOT NULL THEN
        INSERT INTO posts (
            id, user_id, caption, source_url, source_title,
            created_at, updated_at, reshared_from_post_id
        )
        SELECT
            gen_random_uuid(),
            r.user_id,
            p.caption,
            p.source_url,
            p.source_title,
            r.created_at,
            r.created_at,
            p.id
        FROM post_reshares r
        JOIN posts p ON p.id = r.post_id;
    END IF;
END$$;

-- 3. Carry the per-resharer image overrides over. Each override row maps
--    to the newly-inserted post via (reshared_from_post_id, user_id).
DO $$
BEGIN
    IF to_regclass('public.post_reshare_images') IS NOT NULL THEN
        INSERT INTO post_images (post_id, url, position)
        SELECT np.id, ri.url, ri.position
        FROM post_reshare_images ri
        JOIN posts np
            ON np.reshared_from_post_id = ri.post_id
           AND np.user_id = ri.user_id;
    END IF;
END$$;

-- 4. Old tables are no longer referenced anywhere — drop them so the
--    schema mirrors the new model.
DROP TABLE IF EXISTS post_reshare_images;
DROP TABLE IF EXISTS post_reshares;