-- Display derivative: a big-screen-quality image (~2048px long edge) for the diashow.
-- The 800px `preview_path` is sized for phone feeds (data saver); upscaled on a projector
-- it looks soft. The diashow uses `display_path` instead — bounded in size (safe to decode
-- on weak kiosk hardware) yet sharp on 1080p/4K. NULL until the compression worker (or the
-- one-time backfill) generates it; consumers fall back to the original when absent.
ALTER TABLE upload ADD COLUMN display_path TEXT;

-- Recreate (not CREATE OR REPLACE, which only allows appending columns at the end) so the
-- new column can sit alongside preview_path/thumbnail_path.
DROP VIEW IF EXISTS v_feed;
CREATE 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.display_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;