#!/usr/bin/env python
"""
This module provides routines to calculate rating of users in tripster.ru project.

Main publick functions:
    calc_all_cities_rating()
    calc_cities_per_continent_rating()
    calc_cities_per_country_rating()
    calc_all_countries_rating()
    calc_countries_per_continent_rating()
"""

from settings import settings
from lib.models import engine
import types_

cursor = engine.connect().connection.cursor()

def userid_sql(userid):
    """
    Build sql expression for where clause.
    """

    if userid is None:
        where = ''
    else:
        where = ' AND user = %d' % userid
    return where


def clear_rating(type, userid=None):
    """
    Delete all records with given type from ratings table.
    """

    cursor = engine.connect().connection.cursor()

    where = userid_sql(userid) 
    cursor.execute("""DELETE FROM ratings
                      WHERE type = %%s %s""" % where, (type))


def insert_rating(user_id, position, count, type, object=None):
    """
    Create record in ratings table.
    """

    cursor.execute("""INSERT INTO ratings
                        SET user = %s, position = %s,
                        count = %s, type= %s, object=%s""",
                        (user_id, position, count, type, object))


def calc_all_cities_rating(userid=None):
    """
    Calculate rating of visited cities for each user.
    """

    cursor.execute("""SELECT u.id, COUNT(*) as count
                   FROM users u
                   LEFT JOIN cities_in_users ciu ON ciu.user = u.id
                   WHERE u.fraud_user = 0
                   GROUP BY u.id
                   ORDER BY count DESC""")

    clear_rating(types_.RATING_ALL_CITIES, userid)

    for position, row in enumerate(cursor.fetchall()):
        iter_userid, count = row
        if userid and userid != iter_userid:
            continue
        insert_rating(iter_userid, position + 1, count, types_.RATING_ALL_CITIES) 


def calc_cities_per_continent_rating(userid=None):
    """
    Calculate rating of visited cities on each continent for each user
    """

    cursor.execute("""SELECT u.id, countries.continent, COUNT(*) AS count
                   FROM users u
                   LEFT JOIN cities_in_users ciu ON ciu.user = u.id
                   LEFT JOIN cities ON cities.id = ciu.city
                   LEFT JOIN countries ON countries.id = cities.country
                   WHERE countries.continent != 'RU'
                         AND u.fraud_user = 0
                   GROUP BY u.id, countries.continent
                   ORDER BY count DESC""")

    clear_rating(types_.RATING_CITIES_PER_CONTINENT)

    for position, row in enumerate(cursor.fetchall()):
        iter_userid, continent, count = row
        if userid and userid != iter_userid:
            continue
        insert_rating(iter_userid, position + 1, count,
                      types_.RATING_CITIES_PER_CONTINENT, continent)


def calc_cities_per_country_rating(userid=None):
    """
    Calculate rating of visited cities in each country for each user
    """

    cursor.execute("""SELECT u.id, countries.id, COUNT(*) AS count
                   FROM users u
                   LEFT JOIN cities_in_users ciu ON ciu.user = u.id
                   LEFT JOIN cities ON cities.id = ciu.city
                   LEFT JOIN countries ON countries.id = cities.country
                   WHERE u.fraud_user = 0 
                   GROUP BY u.id, countries.id
                   ORDER BY count DESC""")

    clear_rating(types_.RATING_CITIES_PER_COUNTRY)

    for position, row in enumerate(cursor.fetchall()):
        iter_userid, country, count = row
        if userid and userid != iter_userid:
            continue
        insert_rating(iter_userid, position + 1, count,
                      types_.RATING_CITIES_PER_COUNTRY, country)


def calc_all_countries_rating(userid=None):
    """
    Calculate rating of visited countries for each user.
    """

    cursor.execute("""SELECT u.id, COUNT(DISTINCT countries.id) as count
                   FROM users u
                   LEFT JOIN cities_in_users ciu ON ciu.user = u.id
                   LEFT JOIN cities ON cities.id = ciu.city
                   LEFT JOIN countries ON countries.id = cities.country
                   WHERE u.fraud_user = 0
                   GROUP BY u.id
                   ORDER BY count DESC""")

    clear_rating(types_.RATING_ALL_COUNTRIES)

    for position, row in enumerate(cursor.fetchall()):
        iter_userid, count = row
        if userid and userid != iter_userid:
            continue
        insert_rating(iter_userid, position + 1, count, types_.RATING_ALL_COUNTRIES) 


def calc_countries_per_continent_rating(userid=None):
    """
    Calculate rating of visited countries on each continent for each user
    """

    cursor.execute("""SELECT u.id, countries.continent, COUNT(DISTINCT countries.id) AS count
                   FROM users u
                   LEFT JOIN cities_in_users ciu ON ciu.user = u.id
                   LEFT JOIN cities ON cities.id = ciu.city
                   LEFT JOIN countries ON countries.id = cities.country
                   WHERE u.fraud_user = 0
                   GROUP BY u.id, countries.continent
                   ORDER BY count DESC""")

    clear_rating(types_.RATING_COUNTRIES_PER_CONTINENT)

    for position, row in enumerate(cursor.fetchall()):
        iter_userid, continent, count = row
        if userid and userid != iter_userid:
            continue
        insert_rating(iter_userid, position + 1, count,
                      types_.RATING_COUNTRIES_PER_CONTINENT, continent)


def update(login=None):
    """
    Rebuild all ratings.
    """

    if login is None:
        userid = None
    else:
        cursor.execute("SELECT id FROM users WHERE login = %s", [login])
        userid = cursor.fetchone()[0]

    print 'Cities rating'
    calc_all_cities_rating(userid)
    print 'Cities per continent rating'
    calc_cities_per_continent_rating(userid)
    print 'Cities per country rating'
    calc_cities_per_country_rating(userid)
    print 'Countries rating'
    calc_all_countries_rating(userid)
    print 'Countries per continet rating'
    calc_countries_per_continent_rating(userid)

    cursor.execute("COMMIT")


if __name__ == '__main__':
    print 'Calculating ratings...'
    update()