 -- VIEWS
 
 CREATE VIEW media_extractor_training_downloads_count AS
-    SELECT media.media_id, COALESCE(foo.extractor_training_downloads_for_media_id, (0)::bigint) AS extractor_training_download_count FROM (media LEFT JOIN (SELECT stories.media_id, count(stories.media_id) AS extractor_training_downloads_for_media_id FROM extractor_training_lines, downloads, stories WHERE ((extractor_training_lines.downloads_id = downloads.downloads_id) AND (downloads.stories_id = stories.stories_id)) GROUP BY stories.media_id ORDER BY stories.media_id) foo ON ((media.media_id = foo.media_id)));
+    SELECT media.media_id,
+           COALESCE(foo.extractor_training_downloads_for_media_id, (0)::bigint) AS extractor_training_download_count
+    FROM media
+        LEFT JOIN (
+            SELECT stories.media_id,
+                   COUNT(stories.media_id) AS extractor_training_downloads_for_media_id
+            FROM extractor_training_lines,
+                 downloads,
+                 stories
+            WHERE extractor_training_lines.downloads_id = downloads.downloads_id
+              AND downloads.stories_id = stories.stories_id
+            GROUP BY stories.media_id
+            ORDER BY stories.media_id
+        ) AS foo ON media.media_id = foo.media_id;
 
 CREATE VIEW yahoo_top_political_2008_media AS
-    SELECT DISTINCT media_tags_map.media_id FROM media_tags_map, (SELECT tags.tags_id FROM tags, (SELECT DISTINCT media_tags_map.tags_id FROM media_tags_map ORDER BY media_tags_map.tags_id) media_tags WHERE ((tags.tags_id = media_tags.tags_id) AND ((tags.tag)::text ~~ 'yahoo_top_political_2008'::text))) interesting_media_tags WHERE (media_tags_map.tags_id = interesting_media_tags.tags_id) ORDER BY media_tags_map.media_id;
+    SELECT DISTINCT media_tags_map.media_id
+    FROM media_tags_map,
+         (
+            SELECT tags.tags_id
+            FROM tags,
+                 (
+                    SELECT DISTINCT media_tags_map.tags_id
+                    FROM media_tags_map
+                    ORDER BY media_tags_map.tags_id
+                 ) AS media_tags
+            WHERE tags.tags_id = media_tags.tags_id
+              AND (tags.tag)::text ~~ 'yahoo_top_political_2008'::text
+         ) AS interesting_media_tags
+    WHERE media_tags_map.tags_id = interesting_media_tags.tags_id
+    ORDER BY media_tags_map.media_id;
 
 CREATE VIEW technorati_top_political_2008_media AS
-    SELECT DISTINCT media_tags_map.media_id FROM media_tags_map, (SELECT tags.tags_id FROM tags, (SELECT DISTINCT media_tags_map.tags_id FROM media_tags_map ORDER BY media_tags_map.tags_id) media_tags WHERE ((tags.tags_id = media_tags.tags_id) AND ((tags.tag)::text ~~ 'technorati_top_political_2008'::text))) interesting_media_tags WHERE (media_tags_map.tags_id = interesting_media_tags.tags_id) ORDER BY media_tags_map.media_id;
+    SELECT DISTINCT media_tags_map.media_id
+    FROM media_tags_map,
+         (
+            SELECT tags.tags_id
+            FROM tags,
+                 (
+                    SELECT DISTINCT media_tags_map.tags_id
+                    FROM media_tags_map
+                    ORDER BY media_tags_map.tags_id
+                 ) AS media_tags
+            WHERE tags.tags_id = media_tags.tags_id
+              AND (tags.tag)::text ~~ 'technorati_top_political_2008'::text
+         ) AS interesting_media_tags
+    WHERE media_tags_map.tags_id = interesting_media_tags.tags_id
+    ORDER BY media_tags_map.media_id;
 
 CREATE VIEW media_extractor_training_downloads_count_adjustments AS
-    SELECT yahoo.media_id, yahoo.yahoo_count_adjustment, tech.technorati_count_adjustment FROM (SELECT media_extractor_training_downloads_count.media_id, COALESCE(foo.yahoo_count_adjustment, 0) AS yahoo_count_adjustment FROM (media_extractor_training_downloads_count LEFT JOIN (SELECT yahoo_top_political_2008_media.media_id, 1 AS yahoo_count_adjustment FROM yahoo_top_political_2008_media) foo ON ((foo.media_id = media_extractor_training_downloads_count.media_id)))) yahoo, (SELECT media_extractor_training_downloads_count.media_id, COALESCE(foo.count_adjustment, 0) AS technorati_count_adjustment FROM (media_extractor_training_downloads_count LEFT JOIN (SELECT technorati_top_political_2008_media.media_id, 1 AS count_adjustment FROM technorati_top_political_2008_media) foo ON ((foo.media_id = media_extractor_training_downloads_count.media_id)))) tech WHERE (tech.media_id = yahoo.media_id);
+    SELECT yahoo.media_id,
+           yahoo.yahoo_count_adjustment,
+           tech.technorati_count_adjustment
+    FROM (
+            SELECT media_extractor_training_downloads_count.media_id,
+                   COALESCE(foo.yahoo_count_adjustment, 0) AS yahoo_count_adjustment
+            FROM (media_extractor_training_downloads_count
+                LEFT JOIN (
+                    SELECT yahoo_top_political_2008_media.media_id,
+                           1 AS yahoo_count_adjustment
+                    FROM yahoo_top_political_2008_media
+                ) AS foo ON foo.media_id = media_extractor_training_downloads_count.media_id
+        )) AS yahoo,
+        (
+            SELECT media_extractor_training_downloads_count.media_id,
+                   COALESCE(foo.count_adjustment, 0) AS technorati_count_adjustment
+            FROM (media_extractor_training_downloads_count
+                LEFT JOIN (
+                    SELECT technorati_top_political_2008_media.media_id,
+                           1 AS count_adjustment
+                    FROM technorati_top_political_2008_media
+                ) AS foo ON foo.media_id = media_extractor_training_downloads_count.media_id
+        )) AS tech
+    WHERE tech.media_id = yahoo.media_id;
 
 CREATE VIEW media_adjusted_extractor_training_downloads_count AS
-    SELECT media_extractor_training_downloads_count.media_id, ((media_extractor_training_downloads_count.extractor_training_download_count - (2 * media_extractor_training_downloads_count_adjustments.yahoo_count_adjustment)) - (2 * media_extractor_training_downloads_count_adjustments.technorati_count_adjustment)) AS count FROM (media_extractor_training_downloads_count JOIN media_extractor_training_downloads_count_adjustments ON ((media_extractor_training_downloads_count.media_id = media_extractor_training_downloads_count_adjustments.media_id))) ORDER BY ((media_extractor_training_downloads_count.extractor_training_download_count - (2 * media_extractor_training_downloads_count_adjustments.yahoo_count_adjustment)) - (2 * media_extractor_training_downloads_count_adjustments.technorati_count_adjustment));
+    SELECT media_extractor_training_downloads_count.media_id,
+           (
+               (media_extractor_training_downloads_count.extractor_training_download_count -
+               (2 * media_extractor_training_downloads_count_adjustments.yahoo_count_adjustment)) -
+               (2 * media_extractor_training_downloads_count_adjustments.technorati_count_adjustment)
+           ) AS count
+    FROM media_extractor_training_downloads_count
+        JOIN media_extractor_training_downloads_count_adjustments
+            ON media_extractor_training_downloads_count.media_id = media_extractor_training_downloads_count_adjustments.media_id
+    ORDER BY (
+        (media_extractor_training_downloads_count.extractor_training_download_count -
+        (2 * media_extractor_training_downloads_count_adjustments.yahoo_count_adjustment)) -
+        (2 * media_extractor_training_downloads_count_adjustments.technorati_count_adjustment)
+    );
 
 CREATE TABLE extractor_results_cache (
     extractor_results_cache_id integer NOT NULL,

);

DROP TRIGGER IF EXISTS stories_tags_map_last_updated_trigger on stories_tags_map CASCADE;
CREATE TRIGGER stories_tags_map_last_updated_trigger BEFORE INSERT OR UPDATE ON stories_tags_map FOR EACH ROW EXECUTE PROCEDURE last_updated_trigger() ;
DROP TRIGGER IF EXISTS stories_tags_map_update_stories_last_updated_trigger on stories_tags_map;
CREATE TRIGGER stories_tags_map_update_stories_last_updated_trigger AFTER INSERT OR UPDATE OR DELETE ON stories_tags_map FOR EACH ROW EXECUTE PROCEDURE update_stories_updated_time_by_stories_id_trigger();

CREATE index stories_tags_map_db_row_last_updated on stories_tags_map ( db_row_last_updated );
create unique index stories_tags_map_story on stories_tags_map (stories_id, tags_id);

CREATE INDEX media_sets_name_trgm on media_sets USING gin (name gin_trgm_ops);
CREATE INDEX media_sets_description_trgm on media_sets USING gin (description gin_trgm_ops);

CREATE VIEW media_sets_tt2_locale_format AS
    SELECT '[% c.loc("' || COALESCE( name, '') || '") %]' || E'\n' ||  '[% c.loc("' || COALESCE (description, '') || '") %] ' AS tt2_value
    FROM media_sets
    WHERE set_type = 'collection'
    ORDER BY media_sets_id;

alter table media_sets add constraint dashboard_media_sets_type
check ( ( ( set_type = 'medium' ) and ( media_id is not null ) )
