# encoding: utf8
import logging, ConfigParser
from random import randint
from spyne import Application, rpc, ServiceBase, Iterable, Integer, Unicode, ComplexModel, Array
from lxml import etree
from base64 import b64decode, b64encode
from spyne.protocol.soap import Soap11
from spyne.protocol.json import JsonDocument

from spyne.server.wsgi import WsgiApplication
import psycopg2

config = ConfigParser.RawConfigParser(allow_no_value=True)
config.read('/etc/imei/imeidb.conf')
db_host = config.get('db', 'host')
db_user = config.get('db', 'user')
db_password = config.get('db', 'password')
db_dbname = config.get('db', 'dbname')
logfile = config.get('log', 'logfile')
print 'logfile = %s' %logfile
logging.basicConfig(filename=logfile, format='%(asctime)s %(levelname)s %(message)s', level=logging.INFO)
logging.info('Json Service started')
logging.getLogger('spyne.protocol.xml').setLevel(logging.DEBUG)


class Auth(ComplexModel):
    username = Unicode
    password = Unicode

class Request(ComplexModel):
    requestId = Integer
    imeiId = Integer
    dublicateCount = Integer

class Answer(ComplexModel):
    answer = Unicode

class Service2(ServiceBase):
    @rpc(_returns=Unicode)
    def ImeiWithDublicate(ctx):
        r = randint(1000000, 9999999)
        #print ctx
        in_query_b64 = b64encode(str(ctx.in_body_doc))
        logging.info('ID:%s Input query (base64) %s' %(r, in_query_b64))
        d = ctx.in_body_doc['ImeiWithDublicate']
        try:
            username = d['username']
            password = d['password']
            requestId = d['requestId']
            imeiId = d['imeiId']
            dublicateCount = d['dublicateCount']
            in_data = str(ctx.in_body_doc)
            in_data_b64 = b64encode(in_data)
            print 'in_data_b64 = %s' %in_data_b64
            print 'username = %s, password = %s, requestId = %s, imeiId = %s, dublicateCount = %s' % (
            username, password, requestId, imeiId, dublicateCount)
            eventInfo = u'Получено от %s следующие данные: requestId=%s, imei=%s,dublicateCount=%s' % (
            username, requestId, imeiId, dublicateCount)
        except:
            return 'Unvalid parameters'

        print 'd = %s' %d
        try:
            conn = psycopg2.connect(
            "host = %s dbname = %s user = %s password = %s" % (db_host, db_dbname, db_user, db_password))
            cur = conn.cursor()
        except psycopg2.DatabaseError, err:
            logging.error('ID:%s %s' %(r, err))
            return 'Database connection error!'
        print 'After DB connection'
        cur.execute(
            "select users.id,   users.role_id ,  users.organization_id from users where users.username = %s and users.password = md5(%s)",
            (username, password))
        c = cur.fetchall()
        if c:
            for i in c:
                orgId = i[2]
                userId = i[0]
            try:
                logging.info('ID:%s search relate requestId: %s and imeiId: %s' %(r, requestId, imeiId))
                cur.execute("select imei from imei_received where request_id = %s and imei = %s", (requestId, imeiId))
                logging.info('ID:%s search relate requestId: %s and imeiId: %s executed successfully' % (r, requestId, imeiId))
                if not cur.fetchall():
                    logging.warn('ID:%s Not related request ID or imei. Sent this warning to client.' %r)
                    ## insert into catalog_change_log (users_id, event_info) values (?, ?)
                    ### такой то такой то не правильно отправил
                    return 'Not related request ID or imei'
            except psycopg2.DatabaseError, err:
                logging.error('ID:%s %s' %(r, err))
                return 'Not related request ID or imei'
            try:
                request_status_id = 2

                logging.info(
                    'ID:%s try insert to imei_processed : imeiId = %s, orgId = %s, userId = %s, requestId = %s, dublicateCount  = %s, request_status_id = %s' % (r,
                    imeiId, orgId, userId, requestId, dublicateCount, request_status_id))

                cur.execute(
                    "insert into imei_processed(imei,organization_id,user_id,request_id,response_code,response_body) values(%s,%s,%s,%s,%s,%s) "
                    "ON CONFLICT ON CONSTRAINT imei_processed_imei_org_req_unique "
                    "DO UPDATE SET last_date_update = 'now'::timestamp, user_id = %s, response_code = %s, response_body = %s where imei_processed.request_id = %s and "
                    "imei_processed.organization_id = %s and imei_processed.imei = %s returning id",
                    (imeiId, orgId, userId, requestId, dublicateCount, in_data_b64,userId,dublicateCount,in_data_b64,requestId,orgId,imeiId))
                c = cur.fetchall()
                print 'c = %s' %c
                for i in c:

                    i = i[0]
                    print 'i = %s' % i
                logging.info('ID:%s try execute: insert into catalog_change_log (users_id, event_info, imei_processed_id) values (%s, %s, %s)"' %(r, userId, eventInfo, i))
                cur.execute("insert into catalog_change_log (users_id, event_info, imei_processed_id) values (%s, %s, %s)", (userId, eventInfo,i))

                ## insert into catalog_change_log (users_id, event_info) values (?, ?);
                #event_info - получит от того этот (reqId, imei, orgId, dubl)
                #

                # select count(imei) from imei_received where request_id = ?

                # select count(imei) from imei_processed where request_id = ?
                # UPDATE request_proccess SET request_status_id = ?, lastupdate = 'now'::timestamp, user_id = ?, request_body = ?, descriptions = ?  where request_id = ? organization_id = ?
                #request_status_id = 6
                description = 'Json query'
                cur.execute("select count(imei) from imei_received where request_id = %s", (requestId,))
                c_received = cur.fetchall()
                for c1 in c_received:
                    c1=c1
                    print 'c1 = %s' %c1
                cur.execute("select count(imei) from imei_processed where request_id = %s", (requestId,))
                c_processed = cur.fetchall()
                for c2 in c_processed:
                    c2=c2
                    print 'c2 = %s' % c2
                if c1 == c2:
                    print 'equal'
                    try:
                        logging.info('ID:%s try execute: UPDATE request_proccess SET request_status_id = 6, lastupdate = \'now\'::timestamp, user_id = %s, request_body = %s, descriptions = %s '
                                 'where request_id = %s organization_id = %s returning id' %(r, userId, in_data_b64, description, requestId, orgId))
                        cur.execute("insert into request_proccess(request_id,organization_id,request_status_id,user_id,request_body,descriptions) "
                                    "values(%s,%s,6, %s,%s,%s) ON CONFLICT ON CONSTRAINT request_status_by_org_unique DO UPDATE SET "
                                    "request_status_id = 6, lastupdate = 'now'::timestamp, user_id = %s, request_body = %s, descriptions = %s "
                                    "where request_proccess.request_id = %s and request_proccess.organization_id = %s returning id", (requestId, orgId, userId, in_data_b64, description, userId, in_data_b64, description, requestId, orgId, ))

                        c_update = cur.fetchall()

                        for c_id in c_update:
                            request_proccessed_id = c_id
                            print 'request_proccessed_id = %s' % request_proccessed_id
                        cur.execute("insert into catalog_change_log (users_id, event_info, request_proccessed_id) values (%s, %s, %s)", (userId, eventInfo, request_proccessed_id))
                    except psycopg2.DatabaseError, err:
                        logging.error('ID:%s Error while execute' %r)
                        logging.error('ID:%s %s' %(r, err))
                        a = 'Query error'
                        logging.warn('ID:%s Sent Query error' % r)
                        return a


                logging.info('ID:%s insert finished' %r)
                print 'Before write log to db'

                #try:

                    #cur.execute(
                    #"insert into request_proccess(request_id,organization_id,request_status_id,user_id,request_body,descriptions) values(%s,%s,%s,%s,%s,%s)",
                    #(requestId, orgId, request_status_id, userId, in_data, 'Json query'))
                    #logging.info('ID:%s inserted successfully' %r)
                #except psycopg2.DatabaseError, err:
                    #logging.error('ID:%s insert error' %r)
                    #logging.error('ID:%s %s' %(r, err))



                logging.info('ID:%s try to commit' %r)
                conn.commit()
                a = 'Success'

                logging.info('ID:%s Sent success answer' %r)
                return a

            except:
                a = 'Query error'
                logging.warn('ID:%s Sent Query error' %r)
                return a
        else:
            a = 'Authentication error!'
            logging.warn('ID:%s Sent Authentication error' %r)
            return a



app = Application([Service2], 'ImeiAndDublicate',
                          in_protocol=JsonDocument(validator='soft'),
                          out_protocol=JsonDocument())


application = WsgiApplication(app)

if __name__ == '__main__':
    import logging
    from wsgiref.simple_server import make_server
    server = make_server('0.0.0.0', 8000, application)
    server.serve_forever()