class AddSavedProjectsToExploreFilters < ActiveRecord::Migration[4.2]

  def up
    execute <<-SQL

    CREATE OR REPLACE VIEW "1"."projects" AS

    SELECT p.id AS project_id,
      p.category_id,
      p.name AS project_name,
      p.headline,
      p.permalink,
      p.mode,
      (p.state)::text AS state,
      so.so AS state_order,
      od.od AS online_date,
      p.recommended,
      thumbnail_image(p.*, 'large'::text) AS project_img,
      remaining_time_json(p.*) AS remaining_time,
      p.expires_at,
      COALESCE(((pms.data ->> 'pledged'::text))::numeric, (0)::numeric) AS pledged,
      COALESCE(((pms.data ->> 'progress'::text))::numeric, (0)::numeric) AS progress,
      s.acronym AS state_acronym,
      u.name AS owner_name,
      c.name AS city_name,
      p.full_text_index,
      is_current_and_online(p.expires_at, (p.state)::text) AS open_for_contributions,
      elapsed_time_json(p.*) AS elapsed_time,
      (pss.score)::numeric AS score,
      (EXISTS ( SELECT true AS bool
            FROM (contributions c_1
              JOIN user_follows uf ON ((uf.follow_id = c_1.user_id)))
            WHERE ((is_confirmed(c_1.*) AND (uf.user_id = current_user_id())) AND (c_1.project_id = p.id)))) AS contributed_by_friends,

      p.user_id AS project_user_id,
      p.video_embed_url,
      p.updated_at,
      u.public_name AS owner_public_name,
      zone_timestamp(p.expires_at) AS zone_expires_at,
      p.common_id,
      (p.content_rating >= 18) AS is_adult_content,
      p.content_rating,
      (
          EXISTS
          (
              SELECT true AS bool
              FROM project_reminders pr
              WHERE p.id = pr.project_id AND pr.user_id = current_user_id()
          )
      ) as saved_projects
    FROM (((((((projects p
      JOIN users u ON ((p.user_id = u.id)))
      LEFT JOIN project_score_storages pss ON ((pss.project_id = p.id)))
      LEFT JOIN project_metric_storages pms ON ((pms.project_id = p.id)))
      LEFT JOIN cities c ON ((c.id = p.city_id)))
      LEFT JOIN states s ON ((s.id = c.state_id)))
      JOIN LATERAL zone_timestamp(online_at(p.*)) od(od) ON (true))
      JOIN LATERAL state_order(p.*) so(so) ON (true));

    SQL
  end

  def down
    execute <<-SQL

    DROP VIEW "1"."projects";

    CREATE OR REPLACE VIEW "1"."projects" AS
    SELECT p.id AS project_id,
       p.category_id,
       p.name AS project_name,
       p.headline,
       p.permalink,
       p.mode,
       (p.state)::text AS state,
       so.so AS state_order,
       od.od AS online_date,
       p.recommended,
       thumbnail_image(p.*, 'large'::text) AS project_img,
       remaining_time_json(p.*) AS remaining_time,
       p.expires_at,
       COALESCE(((pms.data ->> 'pledged'::text))::numeric, (0)::numeric) AS pledged,
       COALESCE(((pms.data ->> 'progress'::text))::numeric, (0)::numeric) AS progress,
       s.acronym AS state_acronym,
       u.name AS owner_name,
       c.name AS city_name,
       p.full_text_index,
       is_current_and_online(p.expires_at, (p.state)::text) AS open_for_contributions,
       elapsed_time_json(p.*) AS elapsed_time,
       (pss.score)::numeric AS score,
       (EXISTS ( SELECT true AS bool
              FROM (contributions c_1
                JOIN user_follows uf ON ((uf.follow_id = c_1.user_id)))
             WHERE ((is_confirmed(c_1.*) AND (uf.user_id = current_user_id())) AND (c_1.project_id = p.id)))) AS contributed_by_friends,
       p.user_id AS project_user_id,
       p.video_embed_url,
       p.updated_at,
       u.public_name AS owner_public_name,
       zone_timestamp(p.expires_at) AS zone_expires_at,
       p.common_id,
       (p.content_rating >= 18) AS is_adult_content,
       p.content_rating
      FROM (((((((projects p
        JOIN users u ON ((p.user_id = u.id)))
        LEFT JOIN project_score_storages pss ON ((pss.project_id = p.id)))
        LEFT JOIN project_metric_storages pms ON ((pms.project_id = p.id)))
        LEFT JOIN cities c ON ((c.id = p.city_id)))
        LEFT JOIN states s ON ((s.id = c.state_id)))
        JOIN LATERAL zone_timestamp(online_at(p.*)) od(od) ON (true))
        JOIN LATERAL state_order(p.*) so(so) ON (true));


    grant select on "1"."projects" to anonymous;
    grant select on "1"."projects" to web_user;
    grant select on "1"."projects" to admin;

    SQL
  end
end
