# This view uses the powerful GROUP_CONCAT function in MySQL to aggregate
# all of hashtags and user tags into single fields for display purposes
# Note the differences between the NULL values for hashtags (expected) and
# user tags (not expected).
#
# Files of inactive users are not searched

CREATE OR REPLACE VIEW photo_view AS
SELECT    DISTINCT file_id, upload_date, upload_name, caption, file_location, 
            security, narrative, pf.user_id, 
            IFNULL(GROUP_CONCAT(DISTINCT tag ORDER BY tag SEPARATOR ' '),
                 '(no hashtags assigned)') as hashtags,
            IFNULL(GROUP_CONCAT(DISTINCT pfu.user_id ORDER BY pfu.user_id 
                 SEPARATOR ' '), '') as usertags
FROM      photo_files pf
          LEFT JOIN photo_files_tags USING (file_id)
          LEFT JOIN photo_tags USING (tag_id)
          LEFT JOIN photo_files_users pfu USING (file_id)
          JOIN photo_users pu ON (pfu.user_id = pu.user_id)
WHERE     pf.user_id > 0   # inactive users have negative user_ids
GROUP BY file_id

# This view is of photo_users and adds the user's profile pic (if there is one)

CREATE OR REPLACE VIEW user_view AS
SELECT pu.user_id, first_name, last_name, join_date, username, profile_pic_id, 
       admin,
       IF(pu.user_id < 0, '../images/questionmark.gif',
          IFNULL(file_location, '../images/unisexuser.gif')) as profile_pic
FROM   photo_users pu
       LEFT JOIN photo_files pf on (pu.profile_pic_id = pf.file_id)

# This view of friendships retrieves usernames for the friends

CREATE OR REPLACE VIEW friendships_view AS
SELECT u1.user_id, u1.username, 'friended' as relationship,
       IFNULL(u2.username, 'nobody') AS friend_name, pf.friend_id AS friend_id
FROM   photo_users u1
       LEFT JOIN photo_friendships pf ON (u1.user_id = pf.user_id)
       LEFT JOIN photo_users u2 ON (pf.friend_id = u2.user_id)
UNION
SELECT u1.user_id, u1.username, 'friended by' as relationship,
       IFNULL(u2.username, 'nobody') AS friend_name, pf.user_id AS friend_id
FROM   photo_users u1
       LEFT JOIN photo_friendships pf ON (u1.user_id = pf.friend_id)
       LEFT JOIN photo_users u2 ON (pf.user_id = u2.user_id)
ORDER BY SIGN(user_id) DESC, username ASC, relationship, friend_name

