#!/bin/sh

if [ x"$1" = x"-i" ]
then
	echo "check no users are monitoring projects they can't access"
	exit 0
fi

echo '--- users monitoring private projects'
echo

psql << EOF
SELECT monitors.group_id, 
       groups.unix_group_name, 
       monitors.user_id, 
       users.user_name, 
       monitors.type, 
       monitors.item, 
       monitors.item_name
  FROM (SELECT user_id, 
        'artifact' AS type, 
               artifact_monitor.artifact_id AS item, 
               artifact.summary AS item_name, 
               artifact_group_list.group_id 
          FROM artifact_monitor, artifact, artifact_group_list 
         WHERE artifact_monitor.artifact_id = artifact.artifact_id
           AND artifact.group_artifact_id 
               = artifact_group_list.group_artifact_id
        UNION
        SELECT artifact_type_monitor.user_id, 
               'tracker' AS type, 
               artifact_type_monitor.group_artifact_id AS item, 
               artifact_group_list.name AS item_name, 
               artifact_group_list.group_id 
          FROM artifact_type_monitor, artifact_group_list 
         WHERE artifact_type_monitor.group_artifact_id 
               = artifact_group_list.group_artifact_id 
        UNION
        SELECT user_id, 
               'file_module' AS type, 
               filemodule_id AS item, 
               frs_package.name AS item_name, 
               frs_package.group_id 
          FROM filemodule_monitor, frs_package 
         WHERE filemodule_monitor.filemodule_id = frs_package.package_id 
        UNION
        SELECT user_id, 
               'forum' AS type, 
               forum_id AS item, 
               forum_group_list.forum_name AS item_name, 
               forum_group_list.group_id 
          FROM forum_monitored_forums, forum_group_list 
         WHERE forum_monitored_forums.forum_id 
               = forum_group_list.group_forum_id 
           AND group_id != 3 -- allow users to monitor news item followups
        UNION
        SELECT user_id, 
               'news' AS type, 
               NULL AS item, 
               NULL AS item_name, 
               group_id 
          FROM news_monitor) AS monitors, 
       groups, 
       users 
 WHERE monitors.user_id = users.user_id 
   AND monitors.group_id = groups.group_id 
   AND groups.is_public = 0 
   AND NOT EXISTS (SELECT 1 
                     FROM user_group 
                    WHERE user_group.user_id = monitors.user_id 
                      AND user_group.group_id = monitors.group_id) 
 ORDER BY group_id, user_id, type, item
EOF

echo

exit 0

# eof
