import sys
sys.path.append("..")
from base import gps_record

import MySQLdb
import os
import logging

logger = None


column_name = ("gps_userid", "gps_latitude", "gps_longitude", "gps_code",\
       "gps_altitude", "gps_UTC_timestamp", "gps_UTC_unix_timestamp","id")
table_name = "geolife"
def log_init():
    global logger
    if(logger == None):
        logger = logging.getLogger("root.sql.dbutils")
    return logger

#grant all privileges on geolife.`*` to geolife@"%" identified by "geolife"
#FLUSH   PRIVILEGES

dbconn = None

def connect_db():
    try:
        db = MySQLdb.connect(host="localhost" ,user="geolife",passwd="geolife",db="geolife")
        return db
    except MySQLdb.Error,e:
        warnString= "Mysql Error %d: %s" % (e.args[0], e.args[1])

        log_init().warning(warnString)
        print warnString

        if(e.args[0] == 1045 or e.args[0] == 1044):
            os._exit(1)

def query_sql(query_str):
    global dbconn
    results = None
    if (dbconn == None ):
        dbconn = connect_db()
    if (dbconn) :
        try:
            cur = dbconn.cursor();
            count = cur.execute(query_str)
            results = cur.fetchall()
        except MySQLdb.Error,e:
            warnString = " Mysql Error sql = %d %s " % (e.args[0],e.args[1])
            log_init().warning(warnString)
            sys.exit(1)
    return results


def close_db(conn=None):
    global dbconn
    if (conn == None) :
        conn = dbconn
    if (conn == None) :
        return
    cursor = conn.cursor()
    if(cursor):
        cursor.close()
    conn.close()

def insert_into_db(sql,conn=None):
    global dbconn
    if (conn == None) :
        if (dbconn == None ):
            conn = connect_db()
        else:
            conn = dbconn
            
    if (conn) :
        try:
            cursor = conn.cursor()
            n = cursor.execute(sql)
            conn.commit();
            print n
        except MySQLdb.Error,e:
    #WARNING  Mysql Error sql = 1062 Duplicate entry "***" for key 'unique_key'
            if (e.args[0] == 1062):
                return 0
            warnString = " Mysql Error sql = %d %s " % (e.args[0],e.args[1])
            log_init().warning(warnString)
            if(e.args[0] == 2006):
                return 2
            else:
                return 0
    else :
        return 0

def insert_gps_record(conn, oneRecord):
    sql = "INSERT INTO geolife(gps_userid, gps_latitude, gps_longitude, gps_code, gps_altitude, gps_UTC_timestamp, gps_UTC_unix_timestamp) \
VALUES ('%d', '%f', '%f', '%d', '%f', '%s', '%s')" % \
        (oneRecord.gps_userid,oneRecord.gps_latitude,oneRecord.gps_longitude,oneRecord.gps_code,
                oneRecord.gps_altitude, oneRecord.gps_UTC_timestamp, oneRecord.gps_UTC_unix_timestamp)
        #  print sql
    return insert_into_db(sql,conn)
    
def insert_staypoint(s_point,conn=None):
    sql = "INSERT INTO staypoint(userid, arrival_timestamp, leaving_timestamp, mean_coordinate_latitude,\
mean_coordinate_longtitude, mean_coordinate_altitude, arrival_point, leaving_point )VALUES ('%d', '%d', '%d', '%f', '%f', '%f','%d','%d')" % \
        (s_point.userid, s_point.arrival_timestamp, s_point.leaving_timestamp, \
        s_point.mean_coordinate_latitude, s_point.mean_coordinate_longtitude, \
        s_point.mean_coordinate_altitude, s_point.arrival_point, s_point.leaving_point)

    return insert_into_db(sql,conn)


def query_gps( query_str):
    global dbconn
    gps_obj_list = []
    if (dbconn == None ):
        dbconn = connect_db()
    if (dbconn) :
        try:
            cur = dbconn.cursor();
            count = cur.execute(query_str)
            #print 'There is %s rows record' %count
            #result = cur.fetchone()
            #results = cur.fetchall()

            results = cur.fetchall()
            for row in results:
                gps_obj = gps_record.gps_record.__init_with_query_sql__(gps_record.gps_record(), row)
                gps_obj_list.append(gps_obj);
            return gps_obj_list
        except MySQLdb.Error,e:
            warnString = " Mysql Error sql = %d %s " % (e.args[0],e.args[1])
            log_init().warning(warnString)
            sys.exit(1)

        #print results
        #conn.commit()
        #cur.close()
        #dbconn.close()

'''
userid : user id
m: the first index
n: the number of elements
'''
def get_gps_record_time_order(userid, m, n):
    displist = ""

    for oneDisp in  column_name:
        displist = displist + oneDisp + ","
    displist = displist.strip(',')
    if(n > 0):
        sqlStr = 'select %s from geolife where gps_userId =%d order by gps_UTC_unix_timestamp limit %d,%d' %( displist, userid, m, n)
    else:
        sqlStr = 'select %s from geolife where gps_userId =%d order by gps_UTC_unix_timestamp ' %(displist, userid)
    #print sqlStr
    #log_init().debug(sqlStr)
    return query_gps(sqlStr)

def get_record_total_num(userid) :
    global dbconn
    query_str = 'select count(id) from %s where %s = %d' %(table_name, column_name[0], userid)
    #print query_str
    if (dbconn == None ):
        dbconn = connect_db()
    if (dbconn) :
        try:
            cur = dbconn.cursor();
            count = cur.execute(query_str)
            #print 'There is %s rows record' %count
            result = cur.fetchone()
            #print result
            #results = cur.fetchall()
        except MySQLdb.Error,e:
            warnString = " Mysql Error sql = %d %s " % (e.args[0],e.args[1])
            log_init().warning(warnString)
            sys.exit(1)
    return result[0]
def test():
    displist = ""
    for oneDisp in  column_name:
        displist = displist + oneDisp + ","
    displist = displist.strip(',')
    sqlStr = 'select %s from geolife where gps_userId =0 limit 10' %displist
    print sqlStr
    query_gps(sqlStr)

def get_total_users_list() :
    sqlStr = "select distinct gps_userid from %s" %table_name
    return query_sql(sqlStr)
#test()