-- PostgreSQL migration for explicit Trending News support.
-- The PHP API also runs these idempotent ALTER statements automatically.
ALTER TABLE campus_news
    ADD COLUMN IF NOT EXISTS is_trending BOOLEAN NOT NULL DEFAULT FALSE;

ALTER TABLE campus_news
    ADD COLUMN IF NOT EXISTS trending_notified_at TIMESTAMPTZ NULL;

CREATE INDEX IF NOT EXISTS idx_campus_news_trending_published
    ON campus_news (published_at DESC, id DESC)
    WHERE status = 'published' AND is_trending = TRUE;

-- Extend an older notification type check to accept trending notifications.
-- Existing type values are retained, so prior notification types continue to work.
DO $$
DECLARE
    allowed_types TEXT;
BEGIN
    IF to_regclass(current_schema() || '.campus_news_notifications') IS NOT NULL THEN
        SELECT string_agg(quote_literal(type_value), ', ')
          INTO allowed_types
          FROM (
              SELECT DISTINCT type AS type_value
                FROM campus_news_notifications
               WHERE type IS NOT NULL
              UNION
              SELECT unnest(ARRAY['comment', 'reply', 'mention', 'like', 'trending'])
          ) AS known_types;

        ALTER TABLE campus_news_notifications
            DROP CONSTRAINT IF EXISTS campus_news_notifications_type_check;

        EXECUTE 'ALTER TABLE campus_news_notifications '
             || 'ADD CONSTRAINT campus_news_notifications_type_check '
             || 'CHECK (type IN (' || allowed_types || '))';
    END IF;
END $$;
