# -*- coding: utf-8 -*-
from django.db import models

from datetime import datetime

import socket,time, math

from vas.supervisor.customer import consts

from django.db import connection


#__ext_errors = None

__errcodes = { -1:u'Невідомо',
                0:u'OK',
                1:u'Внутрішня помилка черги',
                2:u'Помилка при відправленні',
                3:u'Неможливо відправити SMS',
                4:u'Маршрут відсутній',
                5:u'Таймаут',
                6:u'Чекає',
                7:u'Чекає повторної відправки',
                8:u'Знищено',
                9:u'Неможливо доставити вчасно',
               10:u'Внутрішня помилка біллінгу',
               11:u'DR для невідомого SMS',
               12:u'Невірний агент',
              100:u'Чекає',
              101:u'Зчитується з черги',
              102:u'Заплановано для доставки'}

__drstates = {  -2 : u'',
                -1 : u'n/a',
                 0 : u'n/a',
                48 : u'Доставлено',
                49 : u'Не доставлено',
                50 : u'Невідомий статус',
                51 : u'Вийшов час',
                52 : u'Немає відповіді',
                53 : u'Відмінено',
                54 : u'Видалено',
                55 : u'Буферизовано',
                56 : u'Підтверджено користувачем',
                57 : u'Неможливо доставити',
                58 : u'Неможливо доставити',
                59 : u'Передано оператору',
                60 : u'Відмовлено оператором' }

__ext_errors = {  -1 : u'n/a',
                   0 : u'n/a',
                   1 : u'Невідомий абонент',
                   5 : u'Невідомий абонент',
                   6 : u'Абонент відсутній',
                   7 : u'Несумісне обладнання',
                   8 : u'Абонент відсутній',
                   9 : u'Невідомий абонент',
                  10 : u'Сервіс заблоковано',
                  11 : u'Сервіс заблоковано',
                  12 : u'Несумісне обладнання',
                  13 : u'Сервіс заблоковано',
                  27 : u'Абонент відсутній',
                  28 : u'Несумісне обладнання',
                  31 : u'Абонент зайнятий для SMS',
                  32 : u'Помилка доставки SMS',
                  42 : u'Абонент відсутній', # насправді - ні
                  43 : u'Невідома помилка',
                  45 : u'Абонент зайнятий для SMS',
                1029 : u'Абонент відсутній',
                4367 : u'Сервіс заблоковано',
                1041 : u'Помилковий статус',
                1042 : u'Абонент відсутній',
                1043 : u'Сервіс заблоковано',
                1044 : u'Несумісне обладнання',
                8219 : u'Абонент відсутній',
                4124 : u'Несумісне обладнання'
                }

def ext_errors(id):
    global __ext_errors
    if __ext_errors == None:
        __ext_errors = {}
        ee = ExtError.objects.all()
        for err in ee:
            __ext_errors[err.id] = (err.desc, err.customer_desc)
    if id in __ext_errors:
        return __ext_errors[id]
    else:
        return None

class User(models.Model):
    name = models.CharField(max_length = 100)
    login = models.CharField(max_length = 20)
    password = models.CharField(max_length = 20)
    enabled = models.BooleanField()
    minick_account = models.IntegerField()

    def __str__(self):
        return self.name

    class Meta:
        db_table = 'users'

class UserSetting(models.Model):
    user = models.ForeignKey(User, db_column = 'id_user')
    name = models.CharField(max_length = 20)
    value = models.CharField(max_length = 50, db_column = 'val')

    def __str__(self):
        return self.user.name + "." + self.name

    def apply_settings(self, user, new_settings):
        # clear all settings
        UserSetting.objects.filter(user = user).delete()
        for key in new_settings.keys():
            us = UserSetting()
            us.user = user
            us.name = key
            us.value = new_settings[key]
            us.save()
        return None

    def modify_setting(self, user, name, value):
        UserSetting.objects.filter(user = user, name = name).delete()
        us = UserSetting()
        us.user = user
        us.name = name
        us.value = value
        us.save()

    class Meta:
        db_table = 'user_settings'

class UserSettings:
    def __init__(self, user):
        self.user = user
        self.params = {}
        settings = UserSetting.objects.filter(user = self.user)
        for option in settings:
            self.params[option.name] = option.value

    def contains(self, key):
        return key in self.params

    def get(self, key):
        if key in self.params:
            return self.params[key]
        else:
            return None

    def getd(self, key, default):
        if key in self.params:
            return self.params[key]
        else:
            return default

    def __str__(self):
        return str(self.params)


class MinickAccount(models.Model):
    user = models.ForeignKey(User, db_column = 'user_id')
    minick_account = models.IntegerField(db_column = 'minick_account_id')
    account_type = models.IntegerField(choices = ((0, "Bulk"), (1, "Notifications")))
    comment = models.CharField(max_length = 45)

    def apply_accounts(self, user, new_accounts):
        # clear all settings
        MinickAccount.objects.filter(user = user).delete()
        for key in new_accounts.keys():
            us = MinickAccount()
            us.user = user
            us.account_type = key
            us.minick_account = new_accounts[key][0]
            us.comment = new_accounts[key][1]
            us.save()

    def __str__(self):
        return self.comment  + " (" + self.user.name + " - " + str(self.minick_account) + ")"

    class Meta:
        db_table = 'minick_accounts'

class Message:
    pass

class Job(models.Model):
    name = models.CharField(max_length = 100)
    user = models.ForeignKey(User, db_column = 'id_user')
    plan_start = models.DateTimeField(db_column = 'planned_start_date')
    actual_start = models.DateTimeField(db_column = 'actual_start_date')
    rate = models.FloatField()
    run_from = models.TimeField()
    run_till = models.TimeField()
    finished = models.DateTimeField(db_column = 'finished_date')
    status = models.IntegerField()
    deadline = models.IntegerField(db_column = 'deadline_hrs')
    paused = models.BooleanField()
    sms_template = models.CharField(max_length = 320)
    originator_ton = models.IntegerField()
    originator_npi = models.IntegerField()
    originator = models.CharField(max_length = 45)
    separator = models.CharField(max_length = 3)
    reset_counter = models.IntegerField()
    last_msg_date = models.DateTimeField()
    validity_period = models.IntegerField()
    phone = models.CharField(max_length = 20)
    user_deleted = models.IntegerField()
    who_ch_st = models.IntegerField()
    who_paused = models.IntegerField()
    who_del = models.IntegerField()
    is_push = 0
    num_file_name = models.CharField(max_length = 50)
    is_exporting = models.DateTimeField()
    export_ready = models.IntegerField()

    def txt_status(self):
        if self.status <= 0:
            return "Не завантажено"
        elif self.status == 1:
            return "Завантажено"
        elif self.status == 2:
            return "Оброблюється"
        elif self.status == 3:
            return "Чекає DR"
        elif self.status == 4:
            return "Завершено"
        elif self.status == 5:
            return "Експортовано"
        else:
            return "Дивно"

    def txt_status2(self):
        if self.paused:
            return "Пауза"
        if self.status <= 0:
            return "Не завантажено"
        elif self.status == 1:
            return "Завантажено"
        elif self.status == 2:
            return "Оброблюється"
        elif self.status == 3:
            return "Чекає DR"
        elif self.status == 4:
            return "Завершено"
        elif self.status == 5:
            return "Експортовано"
        else:
            return "Дивно"


    def get_msg_count(self):
        return Message.objects.filter(job = self).count()

    def calc_msg_additional(self):
        self.sent_only = Message.objects.filter(job = self).filter(status__in = [2]).count()
        self.sent_failed = Message.objects.filter(job = self).filter(status__in = [1,3]).count()

    def calculate_msg_count(self):
        messages = Message.objects.filter(job = self)
        #messages = Message.objects.raw("SELECT * FROM messages WHERE id_job=" + self.id)
        noc_in_msgs = 0
        for message in messages:
            if consts.noc in message.dest:
                noc_in_msgs += 1
        self.msg_count = self.get_msg_count()
        if self.msg_count != 0 and noc_in_msgs:
            self.msg_count = self.msg_count-noc_in_msgs
        self.unsent_count = Message.objects.filter(job = self).filter(status = 0).count()
        self.sent_count = Message.objects.filter(job = self).filter(status__in = [1,2,3,4,5]).count()
        if self.sent_count != 0 and noc_in_msgs:
            self.sent_count = self.sent_count-noc_in_msgs
        self.dlvr_count = Message.objects.filter(job = self).filter(status = 4).count()
        if self.dlvr_count != 0 and noc_in_msgs:
            self.dlvr_count = self.dlvr_count-noc_in_msgs
        #self.udlvr_count = Message.objects.filter(job = self).filter(status = 5).count()
        self.udlvr_count = self.sent_count - self.dlvr_count

    def calculate_msg_count2(self):
        cursor = connection.cursor().cursor
        sql = "SELECT status, COUNT(id) FROM messages WHERE id_job=" +\
            str(self.id) + " AND dest!='" + consts.noc + "' GROUP BY status;"
        cursor.execute(sql)
        rows = cursor.fetchall()
        result = {}
        for row in rows:
            result[int(row[0])] = int(row[1])
        self.msg_count = result.get(0, 0) + result.get(1, 0) + result.get(2, 0) +\
            result.get(3, 0) + result.get(4, 0) + result.get(5, 0)
        self.unsent_count = result.get(0, 0)
        self.sent_count = result.get(1, 0) + result.get(2, 0) + result.get(3, 0) +\
            result.get(4, 0) + result.get(5, 0)
        self.dlvr_count = result.get(4, 0)
        self.udlvr_count = self.sent_count - self.dlvr_count


    def get_operator_distribution(self):
        from django.db import connection
        query = connection.cursor()
        query.execute('select count(id), nwc from messages where id_job = ' + str(self.id) + \
                      ' group by 2')
        rows = query.fetchall()
        result = {}
        for row in rows:
            result[row[1]] = row[0]

        return result

    def calc_sms_quan(self):
        text = self.sms_template
        if '{' not in text :
            text_len = len(text)
            isUcsFlag = False
            if (isUcs(text)):
                isUcsFlag = True
            sms_quan = 1
            if (not isUcsFlag):
                text_len += getExtGsm(text)
            if (isUcsFlag):
                if (text_len < 71):
                    sms_quan = 1
                else:
                    sms_quan = math.ceil(text_len/67.0)
            else:
                if (text_len < 161):
                    sms_quan = 1
                else:
                    sms_quan = math.ceil(text_len/153.0)
            return int(sms_quan)
        else:
            return ''

    def separator_idx(self):
        return {"\t":1, ",":2, ";":3}[self.separator]

    def editable(self):
        return self.status <= 0

    def __str__(self):
        return self.name

    class Meta:
        db_table = 'jobs'

class Message(models.Model):
    extra_id = models.CharField(max_length = 30)
    orig = models.CharField(max_length = 20)
    dest = models.CharField(max_length = 20)
    text = models.CharField(max_length = 320)
    status = models.IntegerField()
    time_in = models.DateTimeField()
    time_sent = models.DateTimeField(null = True, blank = True)
    time_dr = models.DateTimeField(null = True, blank = True)
    job = models.ForeignKey(Job, db_column = 'id_job')
    error = models.IntegerField()
    attempts = models.IntegerField()
    sequence_number = models.IntegerField()
    message_id = models.CharField(max_length = 45)
    time_scheduled = models.DateTimeField(null = True, blank = True)
    nwc = models.IntegerField(null = True, blank = True)
    ext_error = models.IntegerField(null = True, blank = True)
    id_text = models.IntegerField(null = True, blank = True)

    __nwc = {

             '38050': 25501,
             '38066': 25501,
             '38095': 25501,
             '38099': 25501,

             '38068': 25502,

             '38067': 25503,
             '38096': 25503,
             '38097': 25503,
             '38098': 25503,

             '38039': 25504,

             '38063': 25506,
             '38093': 25506,

             '38091': 25507,

             '38092': 25521,

             '38094': 25522
             }

    def find_nwc(self):
        prefix = self.dest[:5]
        if prefix in self.__nwc:
            self.nwc = self.__nwc[prefix]
        else:
            self.nwc = 0

    #def __init__(self):
    def clear(self):
        #models.Model.__init__(self)
        self.error = 0
        self.attempts = 0
        self.status = 0
        self.time_scheduled = None
        self.nwc = 0
        self.sequence_number = 0
        self.message_id = ''

    def txt_status(self):
        if self.status == 0:
            return 'Не відправлено'
        elif self.status == 1:
            return 'Відправлено, без submit_sm_resp'
        elif self.status == 2:
            return 'Відправлено'
        elif self.status == 3:
            return 'Помилка відправлення'
        elif self.status == 4:
            return 'Доставлено'
        elif self.status == 5:
            #return 'Недоставлено (' + self.txt_error() + ')'
            return 'Недоставлено'

    def txt_status2(self):
        if self.status == 0 or self.status == 1:
            return 'Не відправлено'
        elif self.status == 2:
            return 'Відправлено'
        elif self.status == 3:
            return 'Помилка відправлення'
        elif self.status == 4:
            return 'Доставлено'
        elif self.status == 5:
            #return 'Недоставлено (' + self.txt_error() + ')'
            return 'Недоставлено'

    def txt_error(self):
        if self.error == -10:
            return 'Таймаут'
        elif self.error == -11:
            return 'Invalid message'
        elif self.error == -19:
            return 'Enroute'
        elif self.error == -20:
            return 'Expired'
        elif self.error == -21:
            return 'Deleted'
        elif self.error == -22:
            return 'Undeliverable'
        elif self.error == -23:
            return 'Accepted'
        elif self.error == -24:
            return 'Unknown'
        elif self.error == -25:
            return 'Rejected'
        else:
            return 'Unknown (' + str(self.error) + ')'

    def txt_error2(self):
        if self.error == -11 or self.ext_error == -11:
            return 'Невірне повідомлення'
        elif self.error == -19 or self.ext_error == -19:
            return 'Доставляється'
        elif self.error == -20 or self.ext_error == -20:
            return 'Час вийшов'
        elif self.error == -21 or self.ext_error == -21:
            return 'Видалено'
        elif self.error == -22 or self.ext_error == -22:
            return 'Неможливо доставити'
        elif self.error == -23 or self.ext_error == -23:
            return 'Прийнято'
        elif self.error == -24 or self.ext_error == -24:
            return 'Невідомо'
        elif self.error == -25 or self.ext_error == -25:
            return 'Відкинуто'
        elif self.error == 0:
            return ''
        elif self.error == -10:
            return 'Внутрішня помилка'
        elif self.error == 53:
            return 'Відмінено'
        else:
            return 'Невідомо (' + str(self.error) + ')'

    def customer_ext_error(self):
        s = ext_errors(self.ext_error)
        if self.status == 2:
            return u'Чекає DR'
        elif self.status == 4:
            return u'Доставлено'
        elif self.status == 3 or self.status == 5:
            return u'Недоставлено'
        elif s:
            return s;
        else:
            return u''
        #elif self.error == -20:
        #    return u'Абонент відсутній'
        #if s == None:
        #    return u'Невідомо'
        #else:
        #    return s

    def compiled_text_simple(self):
        if self.job.sms_template != '':
            msg = self.job.sms_template
            if self.text != None and self.text != '':
                msg = msg + ' (' + self.text + ')'
            return msg
        else:
            return self.text

    def compiled_text(self):
        if self.job.sms_template != '':
            msg = self.job.sms_template
            if self.text != None and self.text != '':
                params = self.text.split(self.job.separator)
                for i in range(len(params)):
                    msg = msg.replace('{' + str(i) + '}', params[i])
            return msg
        else:
            return self.text

    def calc_sms_quan(self):
        text = self.compiled_text()
        text_len = len(text)
        isUcsFlag = False
        if (isUcs(text)):
            isUcsFlag = True
        sms_quan = 1
        if (not isUcsFlag):
            text_len += getExtGsm(text)
        if (isUcsFlag):
            if (text_len < 71):
                sms_quan = 1
            else:
                sms_quan = math.ceil(text_len/67.0)
        else:
            if (text_len < 161):
                sms_quan = 1
            else:
                sms_quan = math.ceil(text_len/153.0)
        return int(sms_quan)


    def __str__(self):
        return self.orig + '>' + self.dest

    class Meta:
        db_table = 'messages'

def getExtGsm(text):
    q = 0
    for i in text:
        for j in consts.extGsm:
            if i == j:
                q += 1
    return q

def getExtGsmChar(char):
    if char in consts.extGsm:
        return True
    else:
        return False

def isUcs(text):
    try:
        y = text.encode('latin1')
        return False
    except Exception:
        return True

class ParameterDef(models.Model):
    param_name = models.CharField(max_length = 45)
    description = models.CharField(max_length = 145)
    mandatory = models.BooleanField()
    allowed_values = models.CharField(max_length = 75)

    value = ''

    class Meta:
        db_table = 'parameters'

class AccountType(models.Model):
    act_type = models.IntegerField()
    description = models.CharField(max_length = 50)

    value = ''
    com_value = ''

    class Meta:
        db_table = 'account_types'

class Logging(models.Model):
    from django.contrib.auth.models import User

    action = models.CharField(max_length = 90)
    action_time = models.DateTimeField(default = datetime.now())
    user = models.ForeignKey(User)

def log_action(user, info):
    log = Logging(action = info, user = user)
    log.save()

class Operator(models.Model):
    nwc = models.IntegerField(primary_key = True)
    operator = models.CharField(max_length = 45, db_column='opname')
    active = models.BooleanField()

    class Meta:
        db_table = 'nwc'

class ExtError(models.Model):
    ext_error_val = models.IntegerField()
    desc = models.CharField(max_length = 45, db_column = 'ext_error_desc')
    customer_desc = models.CharField(max_length = 45)

    class Meta:
        db_table = 'ext_error'

class Script(models.Model):
    name = models.CharField(max_length = 50)
    ctrl_server = models.CharField(max_length = 20)
    ctrl_port = models.IntegerField()
    script_server = models.CharField(max_length = 20)
    script_port = models.IntegerField()
    script_path = models.CharField(max_length = 150)

    WEIRD_STATE = -1
    NOT_STARTED = 0
    RUNNING = 1

    status = "N/A"
    istatus = WEIRD_STATE
    connected = False

    def send_command(self, command, wait = 0.1):
        print "Sending ", command
        self.socket.send(command + "\n")
        print "Receiving"
        try:
            time.sleep(wait)
            result = self.socket.recv(1024).strip()
            print "Received: ", result
            return result
        except Exception,ex:
            print "error", ex

    def parse_status(self, status):
        st = status.lower()
        if status == "n/a":
            return self.WEIRD_STATE
        elif status == "not started":
            return self.NOT_STARTED
        elif status == "stopped":
            return self.WEIRD_STATE
        elif status == "sleeping" or status == "running":
            return self.RUNNING
        else:
            return self.WEIRD_STATE

    def init_connection(self):
        self.socket = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
        try:
            self.socket.connect((self.ctrl_server, self.ctrl_port))
            #self.socket.settimeout(15)
            self.socket.setblocking(1)
            result = self.send_command(":service " + str(self.id))
            if result == "OK":
                result = self.send_command(":connect")
                if result.strip() == "OK":
                    self.connected = True
                    self.status = "N/A"
                else:
                    print "Cannot connect, possible not started"
                    self.connected = False;
                    self.status = "not started"
                self.istatus = self.parse_status(self.status)
        except Exception, err:
            print "Failure: ", err
            self.connected = False
            self.socket = None
            self.status = 'N/A'
            self.istatus = self.WEIRD_STATE
        print "status: ", self.status, "istatus: ", self.istatus

    def get_status(self):
        self.init_connection()
        self.check_status()
        self.close_connection()
        return self.status

    def check_status(self):
        if self.connected:
            self.status = self.send_command("status", 0.5)
        else:
            self.status = "N/A"
        print "Recieved script status: ", self.status
        self.istatus = self.parse_status(self.status)

    def close_connection(self):
        if self.socket != None:
            try:
                self.send_command(":quit")
            except:
                print "Cannot close"

    def start(self):
        if self.connected:
            self.send_command(":start", 20)

    def stop(self):
        if self.connected:
            self.send_command("shutdown", 0.5)

    def restart(self):
        if self.connected:
            self.send_command("restart", 0.5)

class BlackList(models.Model):
    user = models.ForeignKey(User, db_column = 'id_user')
    alphaname = models.CharField(max_length = 20, db_column = 'alphaname')
    list = models.CharField(max_length = 10000, db_column = 'list')

    def aply_new_list(self, list_dict):
        list = ''
        for item in list_dict:
            list += item + ";"
        list = list[:-2]
        print list


    #def __str__(self):
        #return self.user.name + "." + self.name

    class Meta:
        db_table = 'black_list'