skip_e2e_duplicate_message_push.sql

-- E2EE messages use ciphertext in NEW.content; the mobile client sends a separate
-- authenticated push with message_preview (plaintext). Skipping the DB trigger push
-- for e2e:* avoids a duplicate notification whose body is only "Tap to view message".

CREATE OR REPLACE FUNCTION notify_new_message_push()
RETURNS TRIGGER AS $$
DECLARE
    recipient_user_id UUID;
    sender_name TEXT;
    preview_body TEXT;
    supabase_url TEXT;
    service_role_key TEXT;
    recipient_pref_enabled BOOLEAN;
BEGIN
    SELECT u.id::uuid
      INTO recipient_user_id
      FROM chats ch
      JOIN connections c ON c.id = ch.connection_id
      JOIN LATERAL unnest(c.user_ids) AS u(id) ON TRUE
     WHERE ch.id = NEW.chat_id
       AND u.id <> NEW.user_id::text
     LIMIT 1;

    IF recipient_user_id IS NULL THEN
        RETURN NEW;
    END IF;

    SELECT COALESCE(np.message_push_enabled, TRUE)
      INTO recipient_pref_enabled
      FROM notification_preferences np
     WHERE np.user_id = recipient_user_id;

    IF recipient_pref_enabled = FALSE THEN
        RETURN NEW;
    END IF;

    -- Client-issued push carries a safe preview; do not send a second generic notification.
    IF LEFT(COALESCE(NEW.content, ''), 4) = 'e2e:' THEN
        RETURN NEW;
    END IF;

    supabase_url := current_setting('app.settings.supabase_url', true);
    service_role_key := current_setting('app.settings.service_role_key', true);

    IF supabase_url IS NULL OR supabase_url = '' OR service_role_key IS NULL OR service_role_key = '' THEN
        RAISE LOG 'Skipping push notification for message % because required database settings are missing', NEW.id;
        RETURN NEW;
    END IF;

    SELECT COALESCE(NULLIF(name, ''), 'Someone')
      INTO sender_name
      FROM users
     WHERE id = NEW.user_id
     LIMIT 1;

    preview_body := LEFT(COALESCE(NULLIF(NEW.content, ''), 'Open Click to view the latest message'), 120);

    BEGIN
        PERFORM net.http_post(
            url := supabase_url || '/functions/v1/send-push-notification',
            headers := jsonb_build_object(
                'Authorization', 'Bearer ' || service_role_key,
                'Content-Type', 'application/json'
            ),
            body := jsonb_build_object(
                'recipient_user_id', recipient_user_id,
                'title', 'New message from ' || sender_name,
                'body', preview_body,
                'data', jsonb_build_object(
                    'type', 'chat_message',
                    'connection_id', (SELECT connection_id FROM chats WHERE id = NEW.chat_id),
                    'chat_id', NEW.chat_id,
                    'message_id', NEW.id,
                    'sender_user_id', NEW.user_id
                )
            )
        );
    EXCEPTION
        WHEN undefined_function THEN
            RAISE LOG 'Skipping push notification for message % because pg_net is unavailable', NEW.id;
        WHEN OTHERS THEN
            RAISE LOG 'Push notification dispatch failed for message %: %', NEW.id, SQLERRM;
    END;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;