-- Ban now ALWAYS hides: exclude banned uploaders from the feed view. Previously ban and
-- hide were decoupled (a host could ban without hiding), leaving a banned user's photos on
-- the feed and baked into the export. `find_visible_media` and the export query get the
-- same `is_banned = FALSE` filter in code.
CREATE OR REPLACE VIEW v_feed AS
SELECT
    u.id,
    u.event_id,
    u.user_id,
    usr.display_name             AS uploader_name,
    usr.is_banned,
    usr.uploads_hidden,
    u.preview_path,
    u.thumbnail_path,
    u.mime_type,
    u.caption,
    u.created_at,
    COUNT(DISTINCT l.user_id)    AS like_count,
    COUNT(DISTINCT c.id)         AS comment_count
FROM upload u
JOIN "user" usr ON u.user_id = usr.id
LEFT JOIN "like" l  ON l.upload_id = u.id
LEFT JOIN comment c ON c.upload_id = u.id AND c.deleted_at IS NULL
WHERE u.deleted_at IS NULL
  AND usr.uploads_hidden = FALSE
  AND usr.is_banned = FALSE
GROUP BY u.id, usr.display_name, usr.is_banned, usr.uploads_hidden;

-- pin_reset_request: a guest who forgot their PIN (localStorage was the only copy) can ask
-- a host to reset it in-app, instead of being permanently orphaned. One pending request per
-- user; cleared when the host resets the PIN or dismisses it, and cascades if the user/event
-- is removed.
CREATE TABLE pin_reset_request (
    id          UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    event_id    UUID        NOT NULL REFERENCES event(id) ON DELETE CASCADE,
    user_id     UUID        NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (user_id)
);