-- Social graph: follows, blocks, reports, plus a generic notifications
-- table that backs the "notification plugin" (email today; websocket +
-- push will read the same rows later).
--
-- Follows are REQUEST-BASED. Clicking "Follow" inserts a row with
-- status='pending'; the target later accepts (status→'accepted',
-- responded_at stamped) or rejects (row deleted). A user is "following"
-- another only once the edge is 'accepted'. The UNIQUE(follower,
-- following) pair makes a repeated request idempotent, and the CHECK
-- stops anyone following themselves.
CREATE TABLE IF NOT EXISTS follows (
    id           UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    follower_id  UUID         NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    following_id UUID         NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    status       VARCHAR(16)  NOT NULL DEFAULT 'pending',  -- pending | accepted
    created_at   TIMESTAMPTZ  NOT NULL DEFAULT now(),
    responded_at TIMESTAMPTZ,                              -- set when accepted
    UNIQUE (follower_id, following_id),
    CHECK (follower_id <> following_id)
);

-- "Who follows me / who has requested me" — the incoming side. Filtered
-- by status so accepted-follower counts and pending-request lists are
-- both index-served.
CREATE INDEX IF NOT EXISTS follows_following_status_idx
    ON follows (following_id, status);

-- "Who I follow / who I've requested" — the outgoing side.
CREATE INDEX IF NOT EXISTS follows_follower_status_idx
    ON follows (follower_id, status);

-- Blocks. blocker_id has blocked blocked_id. One row per direction; the
-- service treats a block in EITHER direction as "these two can't follow
-- each other" and tears down any existing follow edges both ways.
CREATE TABLE IF NOT EXISTS user_blocks (
    id          UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    blocker_id  UUID         NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    blocked_id  UUID         NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT now(),
    UNIQUE (blocker_id, blocked_id),
    CHECK (blocker_id <> blocked_id)
);

-- Reverse lookup: "has anyone blocked me?" — used to compute blocked_by
-- when rendering a profile.
CREATE INDEX IF NOT EXISTS user_blocks_blocked_idx
    ON user_blocks (blocked_id);

-- Reports. A reporter flags another user with a category + free-text
-- reason. resolved_at is one-way (admins clear it later); the partial
-- index keeps the open-reports queue cheap to list.
CREATE TABLE IF NOT EXISTS user_reports (
    id           UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    reporter_id  UUID         NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    reported_id  UUID         NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    category     VARCHAR(32)  NOT NULL,
    reason       TEXT         NOT NULL,
    created_at   TIMESTAMPTZ  NOT NULL DEFAULT now(),
    resolved_at  TIMESTAMPTZ,
    CHECK (reporter_id <> reported_id)
);

CREATE INDEX IF NOT EXISTS user_reports_reported_idx
    ON user_reports (reported_id, created_at DESC);

CREATE INDEX IF NOT EXISTS user_reports_unresolved_idx
    ON user_reports (created_at DESC) WHERE resolved_at IS NULL;

-- Notifications back the in-app/email/push fan-out. Today the
-- NotificationService persists a row here AND sends an email; websocket
-- + push delivery will later read unread rows for a user. actor_id is
-- the user who triggered the event (nullable for system notifications);
-- kind is a short machine string ('follow_request', 'follow_accepted').
CREATE TABLE IF NOT EXISTS notifications (
    id          UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     UUID         NOT NULL REFERENCES users(id) ON DELETE CASCADE,  -- recipient
    actor_id    UUID         REFERENCES users(id) ON DELETE CASCADE,           -- who caused it
    kind        VARCHAR(32)  NOT NULL,
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT now(),
    read_at     TIMESTAMPTZ
);

-- "My notifications, newest first" + an unread filter for badge counts.
CREATE INDEX IF NOT EXISTS notifications_user_created_idx
    ON notifications (user_id, created_at DESC);

CREATE INDEX IF NOT EXISTS notifications_user_unread_idx
    ON notifications (user_id) WHERE read_at IS NULL;