class FixViewBug < ActiveRecord::Migration
  def change
    drop_view :participant_challenges
    create_view :participant_challenges, "
    SELECT p.id,
           pc.challenge_id,    -- fk to challenges
           pc.participant_id,  -- fk to participants
           c.organizer_id,
           c.challenge,
           c.description,
           c.rules,
           c.prizes,
           c.resources,
           c.tagline,
           p.name,
           p.email,
           p.last_sign_in_at,
           p.bio,
           p.github,
           p.linkedin,
           p.twitter
    FROM participants p,
         challenges c,
        (
        SELECT c_1.id,
               c_1.id AS challenge_id,   -- count submissions
               p.id AS participant_id
          FROM challenges c_1,
               participants p,
               submissions s_1
         WHERE s_1.challenge_id = c_1.id
           AND s_1.participant_id = p.id
        UNION
        SELECT c_1.id,
               c_1.id AS challenge_id,   -- count topics
               p.id AS participant_id
          FROM challenges c_1,
               participants p,
               topics t
         WHERE t.challenge_id = c_1.id
           AND t.participant_id = p.id
        UNION
        SELECT t.challenge_id AS id,     -- count posts
               t.challenge_id,
               ps.id AS participant_id
          FROM posts ps,
               topics t
         WHERE t.id = ps.topic_id
        UNION
        SELECT df.challenge_id AS id,    -- count downloads
               df.challenge_id,
               dfd.participant_id
        FROM dataset_file_downloads dfd,
             dataset_files df
        WHERE dfd.dataset_file_id = df.id) pc
    WHERE pc.participant_id = p.id
      AND pc.challenge_id = c.id
    "
  end
end
