scores = %sql SELECT score, COUNT(*) FROM posts GROUP BY score ORDER BY score DESC

worst_post = %sql SELECT id FROM posts WHERE score = (SELECT MIN(score) FROM posts)

best_post = %sql SELECT id FROM posts WHERE score = (SELECT MAX(score) FROM posts)

pd_count = scores.DataFrame()["count"]
pd_count.index = scores.DataFrame()["score"]
pd_count.plot(logy=True)
plt.ylabel("count")
plt.title("Post score distribution")

"""
Explanation: Scores
First let's take a look at the ratings users can give to images. This is just for warming up since this is a feature that's not so much used on Danbooru.
End of explanation
"""


pending_posts = %sql SELECT id FROM posts WHERE is_pending = true
flagged_posts = %sql SELECT id FROM posts WHERE is_flagged = true
deleted_posts = %sql SELECT id FROM posts WHERE is_deleted = true
banned_posts = %sql SELECT id FROM posts WHERE is_banned = true

def single_column_to_set(column):
    return set(idx[0] for idx in column)
posts_with_status = map(single_column_to_set, (pending_posts, flagged_posts, deleted_posts, banned_posts))
posts_with_status = list(posts_with_status)

post_flag_count = np.array(list(map(len, posts_with_status)))
post_flag_count_log = post_flag_count.copy()
post_flag_count_log[post_flag_count == 0] = 1
post_flag_count_log = np.log10(post_flag_count_log)

rects = plt.bar(range(4), post_flag_count_log)
plt.xticks(range(4), ["pending", "flagged", "deleted", "banned"])
for idx, rect in enumerate(rects):
    height = rect.get_height()
    width = rect.get_width()
    plt.text(rect.get_x() + width / 2, height, post_flag_count[idx], va="bottom", ha="center")
plt.ylabel("count")
yticks = plt.yticks()[0][1:-1]
plt.yticks(yticks, map(lambda x: "$10^{:d}$".format(int(x)), yticks))
plt.title("Post status distribution")

print("There is a small intersection of deleted and banned posts: %d" % len(posts_with_status[2] & posts_with_status[3]))
print("What is even the difference between these two?")

"""
Explanation: Status
Now we take a look at the status of the posts since we'll most likely not use deleted or banned posts. We have the following categories:
is_pending, is_flagged, is_deleted, is_banned
End of explanation
"""


%%sql
CREATE OR REPLACE TEMPORARY VIEW filtered_posts AS
    (SELECT * FROM posts
     WHERE is_pending = false
     AND is_flagged = false
     AND is_deleted = false
     AND is_banned = false
     AND parent_id = 0
     AND (image_width >= 512 OR image_height >= 512)
     AND cast(image_width as double precision) / cast(image_height as double precision) BETWEEN 0.5 AND 2.0)

filtered_count = %sql SELECT COUNT(*) FROM filtered_posts

filtered_ext = %sql SELECT DISTINCT file_ext FROM filtered_posts

print("Now after filtering we have %d posts left with the following file extensions:" % filtered_count[0][0])
print(reduce(list_to_string, (x[0] for x in filtered_ext)))

%%sql
CREATE OR REPLACE TEMPORARY VIEW filtered_images AS
    (SELECT * FROM filtered_posts
     WHERE file_ext = 'jpeg'
     OR file_ext = 'jpg'
     OR file_ext = 'png')

%sql SELECT COUNT(*) FROM filtered_images

"""
Explanation: Wow, there are a lot of deleted posts. I think I need to check them out manually and their reason for deletion.
But first create a view filtered_posts where flagged posts are removed. Furthermore remove children and remove posts where the aspect ration is not in 1:2 to 2:1.
End of explanation
"""


rating_distribution = %sql SELECT date_trunc('month', created_at) AS month, rating, COUNT(*) FROM posts GROUP BY month, rating ORDER BY month ASC, rating DESC
rating_distribution = np.array(rating_distribution).reshape((150,3,3))
rating_distribution = pd.DataFrame(rating_distribution[:,:,2], columns=rating_distribution[0,:,1], index=rating_distribution[:,0,0], dtype=np.int)

rating_distribution.plot.area()
plt.xlim(rating_distribution.index[0], rating_distribution.index[-1])
plt.legend(["safe", "questionable", "explicit"], title="Rating")
plt.xlabel("Date of upload")
plt.ylabel("Uploads per month")
plt.title("Distribution of uploads over time, grouped by rating")

"""
Explanation: How much R18 content is there on danbooru?
There are three ratings:
* s:safe
* q:questionable
* e:explicit
End of explanation
"""


%%sql 
CREATE OR REPLACE TEMPORARY VIEW filtered_tags AS
    (SELECT tags.name, tag_count.count, tags.category
    FROM
        (SELECT tag_id, COUNT(post_id)
         FROM tagged INNER JOIN filtered_images ON filtered_images.id = tagged.post_id
         GROUP BY tag_id) tag_count
    INNER JOIN
        tags ON tags.id = tag_count.tag_id
    ORDER BY tag_count.count DESC)

"""
Explanation: How much tags
This is the first interesting thing.
End of explanation
"""


tag_count = %sql SELECT * FROM filtered_tags LIMIT 1000

def list_count(most_popular_tags):
    pop_tag_number = len(most_popular_tags)
    rank_range = list(range(1, pop_tag_number + 1))
    pop_tag_number_length = len(str(pop_tag_number))
    formatter = "{: <30}│{: >10}│{: >9}"
    formatter = "{: >%d}  " % (pop_tag_number_length) + formatter
    print(formatter.format("RANK", "NAME", "COUNT", "CATEGORY"))
    print("─" * (pop_tag_number_length + 2) + "──────────────────────────────┼──────────┼─────────")
    for rank, (name, count, category) in zip(rank_range, most_popular_tags):
        print(formatter.format(rank, name, count, category))

list_count(tag_count)

def plot_count(most_popular_tags, steps, logx=True):
    pop_tag_number = len(most_popular_tags)
    _, pop_tag_count, _ = zip(*most_popular_tags)
    rank_range = list(range(1, pop_tag_number + 1))
    if logx:
        plt.semilogy(rank_range, pop_tag_count)
    else:
        plt.plot(rank_range, pop_tag_count)
    plt.xticks(rank_range[steps-1::steps], rank_range[steps-1::steps])
    plt.xlim(0, pop_tag_number)
    plt.ylabel("count")
    plt.xlabel("rank")
    if logx:
        annotate_line = np.logspace(np.log10(pop_tag_count[steps // 4]), np.log10(pop_tag_count[-1]) * 1.1, 9)
    else:
        annotate_line = np.linspace(pop_tag_count[0] * 0.9, pop_tag_count[steps*9] * 1.1, 9)
    for logy, i in zip(annotate_line, range(0, pop_tag_number - steps, steps)):
        idx = random.randint(i, i + steps - 1)
        random_rank = rank_range[idx]
        random_count = pop_tag_count[idx]
        random_tag = most_popular_tags[idx][0]
        plt.annotate(random_tag, (random_rank, random_count), (i + steps / 2, logy),
                     arrowprops={"arrowstyle":"-|>"})
    plt.title("Distribution of %d most popular tags" % pop_tag_number)

plot_count(tag_count, 100)

"""
Explanation: Tags in general
End of explanation
"""


print("Important! These are the top 1000 tags (in descending order) where the word breast is included:")
breast_list = [tag for tag, _, _ in tag_count if tag.find("breast") >= 0]
print(reduce(list_to_string, breast_list))
print("In total these are %d tags!" % len(breast_list))

def posts_with_tag(tagname):
    tag_id = %sql SELECT id FROM tags WHERE name = :tagname
    tag_id = tag_id[0][0]
    post_id = %sql SELECT post_id FROM tagged WHERE tag_id = :tag_id
    return post_id

posts_with_tag("headphones_on_breasts")

"""
Explanation: Very interesting! Even in log10 space these tags are not really linear distributed. Let's see if we can solve this with preprocessing or if there are some fancy tricks like weighting to take these differences in the distribution into account.
End of explanation
"""


artist_count = %sql SELECT * FROM filtered_tags WHERE category = 'a' LIMIT 100

list_count(artist_count)

plot_count(artist_count, 10, logx=False)

"""
Explanation: Artists
End of explanation
"""


character_count = %sql SELECT * FROM filtered_tags WHERE category = 'c' LIMIT 100

list_count(character_count)

plot_count(character_count, 10, logx=False)

"""
Explanation: Popular anime characters
End of explanation
"""


series_count = %sql SELECT * FROM filtered_tags WHERE category = 'y' LIMIT 100

list_count(series_count)

plot_count(series_count, 10)

"""
Explanation: Popular series
Last but not least.
End of explanation
"""


%%sql
CREATE OR REPLACE TEMPORARY VIEW final_posts AS
SELECT *
FROM filtered_images
WHERE id NOT IN
    (SELECT DISTINCT post_id
     FROM tagged
     WHERE tag_id
     IN
        (SELECT id as tag_id FROM tags
         WHERE name = 'comic'
         OR name = 'no_humans'
         OR name = '4koma'
         OR name = 'photo'
         OR name = '3d'))
ORDER BY id ASC

final_posts = %sql SELECT id, rating, file_ext FROM final_posts

%%sql
SELECT tags.id, tags.name, tag_count.count
FROM
(
    SELECT tagged.tag_id, COUNT(tagged.post_id)
    FROM
    final_posts
    INNER JOIN
    tagged
    ON final_posts.id = tagged.post_id
    GROUP BY tagged.tag_id
    HAVING COUNT(tagged.post_id) >= 10000
) tag_count
INNER JOIN
tags
ON tag_count.tag_id = tags.id
WHERE tags.category = 'g'
ORDER BY tag_count.count DESC

final_tag_count = _

final_posts = np.array(list(map(tuple, final_posts)),
                       dtype=[("id", np.int32), ("rating", "<U1"), ("file_ext", "<U4")])
final_tag_count = np.array(list(map(tuple, final_tag_count)),
                           dtype=[("id", np.int32), ("name", '<U29'), ("count", np.int32)])

pd.DataFrame(final_posts).to_hdf("metadata.h5", "posts", mode="a", complevel=9, complib="bzip2")
pd.DataFrame(final_tag_count).to_hdf("metadata.h5", "tag_count", mode="a", complevel=9, complib="bzip2")

"""
Explanation: Filter out some more tags
There are some tags than can not be easily inferred from just looking on the images, that do not correspont with the use cases I have in mind or that train on useless features. Better to either remove such posts or just remove these tags.
I looked through the first 500 tags from the list above and took note of the following ones:

monochrome
comic
greyscale
alternate_costume
translated
artist_request
sketch
character_name
copyright_request
artist_name
cosplay
dated
signature
parody
twitter_username
copyright_name
alternate_hairstyle
no_humans
translation_request
english
gradient
crossover
doujinshi
genderswap
remodel_(kantai_collection)
game_cg
cover_page
official_art
scan
text
4koma
traditional_media
3d
photo
lowres

It might also be a good idea to remove the tags for series (category y) because I don't know why this would be useful. The same for artists but since these are not in the TOP 1000 it should be no problem either way.
Some more filtering
And now get a list of all the images without the bold marked tags from the last section.
Furthermore we need information about the distribution of the remaining tags etc. and we need to write them into some easily parsable format for further processing with our neural networks.
End of explanation
"""


