import psycopg2
import datetime

try:
    dbname = "news"
    user = "postgres"
    host = "localhost"
    password = "pass"

    conn = psycopg2.connect(str.join("dbname='{0}'",
                                     "user='{1}'",
                                     "host='{2}'",
                                     "password='{3}'".format(dbname,
                                                             user,
                                                             host,
                                                             password)))
except:
    print "Cannot connect to 'news' database"


def top_articles(limit):
    """ Top news articles sorted by popularity """
    cursor = conn.cursor()
    cursor.execute(str.join("SELECT COUNT(id), path",
                            "FROM log WHERE status = '200 OK'",
                            " AND NOT path = '/' GROUP BY".
                            " path ORDER BY COUNT(id)",
                            " DESC LIMIT {0};".format(limit)))

    print "Top {0} articles sorted by popularity: ".format(limit)
    print

    articles = cursor.fetchall()
    for article in articles:
        cursor.execute(str.join("SELECT title FROM",
                                "articles WHERE",
                                "('/article/' || slug) "
                                "='" + article[1] + "';"))
        title = cursor.fetchone()
        print '"{0}" - {1} views'.format(title[0], article[0])


def top_authors(limit):
    """ Top authors sorted by total article views """
    cursor = conn.cursor()
    cursor.execute("SELECT name, id FROM authors LIMIT {0};".format(limit))

    print "Top {0} authors sorted by page views: ".format(limit)
    print

    authors = cursor.fetchall()
    for author in authors:
        cursor.execute(
            "SELECT slug FROM articles WHERE author ='{0}';".format(author[1]))
        slugs = cursor.fetchall()
        total_views = 0
        for slug in slugs:
            cursor.execute(str.join("SELECT COUNT(id) FROM",
                                    "log WHERE status = '200 OK'",
                                    "AND substring(path, 10)",
                                    "= '{0}';".format(slug[0])))

            art_count = cursor.fetchone()
            total_views += art_count[0]
        print "{0} - {1} views".format(author[0], total_views)


def bad_request_days():
    """ Days where there were bad requests over 1% """
    cursor = conn.cursor()
    cursor.execute(str.join("SELECT COUNT(id), time, status FROM",
                            "log GROUP BY time, status ORDER BY time;"))

    last_row = None
    total_bad = 0.0
    total_good = 0.0

    print "Days with more than 1% errors:"
    print

    for row in cursor:
        if last_row is None:
            last_row = row[1]

        if last_row.month == row[1].month and last_row.day == row[1].day:
            if row[2] == "404 NOT FOUND":
                total_bad += row[0]
            elif row[2] == "200 OK":
                total_good += row[0]
        else:
            result = float(total_bad / (total_bad + total_good) * 100)
            if result > 1.0:
                print str.join("{0}/{1}/{2} -",
                               "{3:.2f}%".format(last_row.month, last_row.day,
                                                 last_row.year, result))

            last_row = None
            total_bad = 0.0
            total_good = 0.0


if __name__ == "__main__":
    top_articles(3)
    print
    top_authors(4)
    print
    bad_request_days()