class FixStatisticsView < ActiveRecord::Migration
  def up
    execute <<-SQL
 CREATE OR REPLACE VIEW statistics AS
   SELECT ( SELECT count(*) AS count
           FROM users
          WHERE users.primary_user_id IS NULL) AS total_users, backers_totals.total_backs, backers_totals.total_backers, backers_totals.total_backed, projects_totals.total_projects, projects_totals.total_projects_success, projects_totals.total_projects_online
   FROM ( SELECT count(*) AS total_backs, count(DISTINCT backers.user_id) AS total_backers, sum(backers.value) AS total_backed
           FROM backers
          WHERE backers.confirmed) backers_totals, ( SELECT count(*) AS total_projects, count(
                CASE
                    WHEN projects.state::text = 'successful'::text THEN 1
                    ELSE NULL::integer
                END) AS total_projects_success, count(
                CASE
                    WHEN projects.state IN ('online', 'successful') AND projects.expires_at >= current_timestamp THEN 1
                    ELSE NULL::integer
                END) AS total_projects_online
           FROM projects
          WHERE projects.state::text <> ALL (ARRAY['draft'::character varying, 'rejected'::character varying]::text[])) projects_totals;
SQL
  end

  def down
    execute <<-SQL
 CREATE OR REPLACE VIEW statistics AS
   SELECT ( SELECT count(*) AS count
           FROM users
          WHERE users.primary_user_id IS NULL) AS total_users, backers_totals.total_backs, backers_totals.total_backers, backers_totals.total_backed, projects_totals.total_projects, projects_totals.total_projects_success, projects_totals.total_projects_online
   FROM ( SELECT count(*) AS total_backs, count(DISTINCT backers.user_id) AS total_backers, sum(backers.value) AS total_backed
           FROM backers
          WHERE backers.confirmed) backers_totals, ( SELECT count(*) AS total_projects, count(
                CASE
                    WHEN projects.state::text = 'successful'::text THEN 1
                    ELSE NULL::integer
                END) AS total_projects_success, count(
                CASE
                    WHEN projects.state::text = 'online'::text THEN 1
                    ELSE NULL::integer
                END) AS total_projects_online
           FROM projects
          WHERE projects.state::text <> ALL (ARRAY['draft'::character varying, 'rejected'::character varying]::text[])) projects_totals;
SQL
  end
end