import datetime
import json
import logging

from django.db.transaction import atomic
import pandas as pd

from common.utils.exceptions import ValidationError
from common.notify.message import client as message_client
from common.utils.models import BaseConnection
from console.monitor.repositories import monitor_repo, monitor_sql_repo
from console.monitor.models import (MonitorDevices, MonitorDevicesRecord)
from console.electrical_info.models import ElectricalInfromation

from common.notify.message import MessageObject

logger = logging.getLogger("")
base_db = BaseConnection()


class MonitorService(object):
    def get_templates(self, **kwargs):
        return monitor_sql_repo.get_templates(**kwargs)

    def get_template(self, id):
        temp = monitor_repo.get_template(id)
        if temp:
            try:
                temp.role = json.loads(temp.role)
            except Exception as e:
                logger.debug(e)
                temp.role = []
            try:
                temp.notification_type = temp.notification_type.split(",")
            except Exception as e:
                logger.debug(e)
                temp.notification_type = []
        return temp

    def create_templates(self, **kwargs):
        template = monitor_repo.create_templates(**kwargs)
        template.role = (json.loads(template.role) if template.role else [])
        template.notification_type = (template.notification_type.split(",")
                                      if template.notification_type else [])
        return template

    def update_template(self, **kwargs):
        template = monitor_repo.update_template(**kwargs)
        template.role = (json.loads(template.role) if template.role else [])
        template.notification_type = (template.notification_type.split(",")
                                      if template.notification_type else [])
        return template

    def delete_template(self, id):
        monitor_repo.delete_template(id)

    def get_monitor_device(self, device_id):
        rst = monitor_repo.get_monitor_device(device_id)
        if not rst:
            raise ValidationError("监控设备不存在")
        dt = rst.to_dict()
        dt.update({
            "monitor_point_name":
                rst.monitor_point.name if rst.monitor_point else None
        })
        return dt

    def get_monitor_devices(self, enterprise_ids, **kwargs):
        return monitor_sql_repo.get_monitor_devices(enterprise_ids, **kwargs)

    def create_monitor_device(self, enterprise_id, **kwargs):
        return monitor_repo.create_monitor_device(enterprise_id, **kwargs)

    def update_monitor_device(self, enterprise_id, **kwargs):
        return monitor_repo.update_monitor_device(enterprise_id, **kwargs)

    @atomic()
    def create_or_update_monitor_device_record(self, **kwargs):
        device_img = kwargs.pop("device_img", None)
        enterprise_id = kwargs.pop("enterprise_id", None)
        device_id = kwargs.get("device_id")
        MonitorDevices.objects.filter(id=device_id).update(
            device_img=device_img)
        has_record = MonitorDevicesRecord.objects.filter(device_id=device_id)
        if has_record:
            kwargs["updated_time"] = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            has_record.update(**kwargs)
            sql = """
            select
                a.customer_id,
                a.name point_name,
                f.imei,
                a.device_id,
                a.updated_time,
                case when a.status=0 then "待上线"
                when a.status=1 then "待审批" 
                when a.status=2 then "已通过"
                else a.status end `status`,
                a.approver_id,
                ifnull(e.nickname, "") approver_name,
                ifnull(b.name, "") as customer_name,
                a.electricity_user_id,
                ifnull(c.number, "") number,
                a.electrical_info_id,
                ifnull(d.power_number, "") power_number,
                a.worker_id,
                ifnull(e.nickname, "") worker_name
            from monitor_device_record a
            left join customer b
            on a.customer_id=b.id
            left join electricity_user c
            on a.electricity_user_id=c.id
            left join electrical_info d
            on a.electrical_info_id=d.id
            left join users e
            on e.id=a.worker_id
            left join users ee
            on ee.id=a.approver_id
            left join monitor_device f
            on a.device_id=f.id
            where a.id=%s
            """ % has_record.first().id
            rst = base_db.query(sql)
            if rst:
                message_client.send_station(
                    title="更新监控审批单信息",
                    content="""
                    新增imei: {}的监控审批单\n监控点名称: {}\n所属客户: {}
                    \n所属户号: {}\n所属电源编号: {}\n当前状态: {}\n
                    申请人{} 审批人: {}""".format(
                        rst[0]["imei"], rst[0]["point_name"],
                        rst[0]["customer_name"], rst[0]["number"],
                        rst[0]["power_number"], rst[0]["status"],
                        rst[0]["worker_name"], rst[0]["approver_name"]),
                    sender_id=rst[0]["worker_id"],
                    receive_ids=[1],
                    type=1, enterprise_id=enterprise_id)
                message_client.send_wechat_bot(
                    type="markdown",
                    content="""<font color="warning">监控审批单信息更新</font>\n<font color=\"info\">imei: %s</font>\n
                    >提交人: %s\n提交时间: %s""" % (rst[0]["imei"], rst[0]["worker_name"], rst[0]["updated_time"]))
        else:
            record = monitor_repo.create_monitor_device_record(**kwargs)
            sql = """
            select
                a.customer_id,
                a.name point_name,
                f.imei,
                a.device_id,
                a.updated_time,
                case when a.status=0 then "待上线"
                when a.status=1 then "待审批" 
                when a.status=2 then "已通过"
                else a.status end `status`,
                a.approver_id,
                ifnull(e.nickname, "") approver_name,
                ifnull(b.name, "") as customer_name,
                a.electricity_user_id,
                ifnull(c.number, "") number,
                a.electrical_info_id,
                ifnull(d.power_number, "") power_number,
                a.worker_id,
                ifnull(e.nickname, "") worker_name
            from monitor_device_record a
            left join customer b
            on a.customer_id=b.id
            left join electricity_user c
            on a.electricity_user_id=c.id
            left join electrical_info d
            on a.electrical_info_id=d.id
            left join users e
            on e.id=a.worker_id
            left join users ee
            on ee.id=a.approver_id
            left join monitor_device f
            on a.device_id=f.id
            where a.id=%s
            """ % record.id
            rst = base_db.query(sql)
            if rst:
                message_client.send_station(
                    title="新增监控审批单",
                    content="""
                    新增imei: {}的监控审批单\n监控点名称: {}\n所属客户: {}
                    \n所属户号: {}\n所属电源编号: {}\n当前状态: {}\n
                    申请人{} 审批人: {}""".format(
                        rst[0]["imei"], rst[0]["point_name"],
                        rst[0]["customer_name"], rst[0]["number"],
                        rst[0]["power_number"], rst[0]["status"],
                        rst[0]["worker_name"], rst[0]["approver_name"]),
                    sender_id=rst[0]["worker_id"],
                    receive_ids=[1],
                    type=1, enterprise_id=enterprise_id)
                message_client.send_wechat_bot(
                    type="markdown",
                    content="""<font color="warning">新增监控审批单</font>\n<font color=\"info\">imei: %s</font>\n
                    >提交人: %s\n提交时间: %s""" % (rst[0]["imei"], rst[0]["worker_name"], rst[0]["updated_time"]))
        return

    @atomic()
    def update_monitor_device_record(self, enterprise_ids, **kwargs):
        record_id = kwargs.pop("record_id", None)
        device_img = kwargs.pop("device_img", None)
        enterprise_id = kwargs.pop("enterprise_id", None)
        device_id = kwargs.get("device_id")
        electrical_info_id = kwargs.get("electrical_info_id")
        meter_number = kwargs.get("meter_number")
        kwargs.update({"approval_time": datetime.datetime.now()})
        if device_img:
            MonitorDevices.objects.filter(id=device_id).update(
                device_img=device_img)
        ElectricalInfromation.objects.filter(id=electrical_info_id).update(
            meter_number=meter_number)
        record = MonitorDevicesRecord.objects.filter(id=record_id)
        if not record:
            raise ValidationError("审批单不存在")
        record.update(**kwargs)
        sql = """
            select
                a.customer_id,
                a.name point_name,
                f.imei,
                a.device_id,
                a.updated_time,
                case when a.status=0 then "待上线"
                when a.status=1 then "待审批" 
                when a.status=2 then "已通过"
                else a.status end `status`,
                a.approver_id,
                ifnull(e.nickname, "") approver_name,
                ifnull(b.name, "") as customer_name,
                a.electricity_user_id,
                ifnull(c.number, "") number,
                a.electrical_info_id,
                ifnull(d.power_number, "") power_number,
                a.worker_id,
                ifnull(e.nickname, "") worker_name
            from monitor_device_record a
            left join customer b
            on a.customer_id=b.id
            left join electricity_user c
            on a.electricity_user_id=c.id
            left join electrical_info d
            on a.electrical_info_id=d.id
            left join users e
            on e.id=a.worker_id
            left join users ee
            on ee.id=a.approver_id
            left join monitor_device f
            on a.device_id=f.id
            where a.id=%s
            """ % record.id
        rst = base_db.query(sql)
        if rst:
            message_client.send_station(
                title="监控审批单状态变更",
                content="""
                    imei: {}的监控审批单\n监控点名称: {}\n所属客户: {}
                    \n所属户号: {}\n所属电源编号: {}\n当前状态: {}\n
                    申请人{} 审批人: {}""".format(
                    rst[0]["imei"], rst[0]["point_name"],
                    rst[0]["customer_name"], rst[0]["number"],
                    rst[0]["power_number"], rst[0]["status"],
                    rst[0]["worker_name"], rst[0]["approver_name"]),
                sender_id=rst[0]["approver_name"],
                receive_ids=[1, 90014],
                type=1, enterprise_id=enterprise_id)
        return self.get_monitor_device_records(enterprise_ids, **{"record_id": str(record_id)})

    def delete_monitor_device_record(self, record_id):
        MonitorDevicesRecord.objects.filter(device_id=record_id).delete()

    def get_monitor_device_records(self, enterprise_ids, **kwargs):
        return monitor_repo.get_monitor_device_records(enterprise_ids, **kwargs)

    def get_real_data(self, imei):
        device = monitor_repo.get_device_by_imei(imei)
        if not device:
            return None, 1, 1, True
        if device.from_oa and device.monitor_point:
            return monitor_repo.get_real_data_from_oa(imei, device.monitor_point.power)
        elif not device.from_oa and device.monitor_point:
            dt, current_ratio, voltage_ratio, is_calculated = monitor_repo.get_real_data(imei)
            return dt, current_ratio, voltage_ratio, True
        elif not device.from_oa and not device.monitor_point:
            return monitor_repo.get_real_data(imei)
        return None, 1, 1, True

    def update_real_data_parameter(self, imei, dt):
        device = monitor_repo.get_device_by_imei(imei)
        current_ratio = dt.get("current_ratio")
        voltage_ratio = dt.get("voltage_ratio")
        if device.current_ratio == current_ratio and \
                device.voltage_ratio == voltage_ratio:
            return
        else:
            device.current_ratio = current_ratio
            device.voltage_ratio = voltage_ratio
            device.save()

    def get_alarm(self, **kwargs):
        return monitor_sql_repo.get_alarm(**kwargs)

    def update_alarm(self, **kwargs):
        monitor_repo.update_alarm(**kwargs)

    def delete_alarm(self, alarm_id):
        monitor_repo.delete_alarm(alarm_id)

    def get_monitor_point(self, enterprise_ids, point_id):
        points, count = monitor_sql_repo.get_monitor_points(enterprise_ids, point_id=point_id)
        if not points:
            return None
        return points[0]

    def get_monitor_points(self, enterprise_ids, **kwargs):
        return monitor_sql_repo.get_monitor_points(enterprise_ids, **kwargs)

    def create_monitor_points(self, **kwargs):
        return monitor_repo.create_monitor_points(**kwargs)

    def update_monitor_points(self, **kwargs):
        return monitor_repo.update_monitor_points(**kwargs)

    def get_monitor_data(self, number, point_id):
        return monitor_repo.get_monitor_data(number, point_id)

    def get_range_data(self, **kwargs):
        return monitor_repo.get_range_data(**kwargs)

    def get_point_transformer_capacity(self, number, point_id):
        return monitor_repo.get_point_transformer_capacity(number, point_id)

    def get_old_station_points(self, number):
        return monitor_repo.get_old_station_points(number)

    def get_real_old_station_point_realdata(self, number, point_id):
        return monitor_repo.get_real_old_station_point_realdata(
            number, point_id)

    def get_real_old_station_point_realdata_trans(self, point_id):
        return monitor_repo.get_real_old_station_point_realdata_trans(point_id)

    def get_real_old_station_point_data_trans(self, point_id, **kwargs):

        return monitor_repo.get_real_old_station_point_data_trans(point_id, **kwargs)

    def get_real_old_station_point_data(self, number, point_id, capacity,
                                        **kwargs):
        return monitor_repo.get_real_old_station_point_data(
            number, point_id, capacity, **kwargs)

    def get_real_old_station_point_statistic_data(self, number, point_id,
                                                  start_time, end_time):
        return monitor_repo.get_real_old_station_point_statistic_data(
            number, point_id, start_time, end_time)

    def get_real_old_station_point_statistic_data_trans(self, number, point_id,
                                                  start_time, end_time):
        return monitor_repo.get_real_old_station_point_statistic_data_trans(
            number, point_id, start_time, end_time)


    def power_loss_reminder(self, **kwargs):
        '''
        失电告警短信通知
        :param kwargs: {"customer_name":"客户名称","customer_addr":"客户地址"}
        :return:
        '''
        msg_obj = MessageObject()
        params = [
            kwargs.get("customer_name"),
            kwargs.get("customer_addr"),
            datetime.datetime.now()
        ]
        msg_obj.send_sms_multi(["18930363241", "13120630827", "13501974165"], 972365, params)

    def import_devices(self, local_path):
        df=pd.read_excel(local_path, sheet_name=0, header=1)
        df = df.where(df.notnull(), None)
        devices = df.to_dict(orient="records")
        device_list = []
        for device in devices:
            device_list.append(MonitorDevices(**device))
        MonitorDevices.objects.bulk_create(device_list)

    def get_monitor_ht_points(self, enterprise_ids, **kwargs):
        return monitor_sql_repo.get_monitor_ht_points(enterprise_ids, **kwargs)

    def get_ht_real_data(self, imei):
        return monitor_sql_repo.get_ht_real_data(imei)

monitor_service = MonitorService()
