import re
import datetime
from .models import (MonitorDevice, MonitorPoints, ElectricityUser,
                     MonitorMetaData, MonitorMetaDataForTest,
                     BaseConnection, ConsoleConnection, MonitorAlarm)
from django.db.utils import ProgrammingError

console_db = ConsoleConnection()
base_db = BaseConnection()
create_monitor_tb = """
CREATE TABLE `%s` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deleted` tinyint(1) NOT NULL,
  `created_time` datetime(6) DEFAULT NULL,
  `updated_time` datetime(6) DEFAULT NULL,
  `deleted_time` datetime(6) DEFAULT NULL,
  `point_id` int(11) DEFAULT NULL,
  `ua` double DEFAULT NULL,
  `ub` double DEFAULT NULL,
  `uc` double DEFAULT NULL,
  `uab` double DEFAULT NULL,
  `ubc` double DEFAULT NULL,
  `uca` double DEFAULT NULL,
  `ia` double DEFAULT NULL,
  `ib` double DEFAULT NULL,
  `ic` double DEFAULT NULL,
  `ir` double DEFAULT NULL,
  `pa` double DEFAULT NULL,
  `pb` double DEFAULT NULL,
  `pc` double DEFAULT NULL,
  `p` double DEFAULT NULL,
  `qa` double DEFAULT NULL,
  `qb` double DEFAULT NULL,
  `qc` double DEFAULT NULL,
  `q` double DEFAULT NULL,
  `sa` double DEFAULT NULL,
  `sb` double DEFAULT NULL,
  `sc` double DEFAULT NULL,
  `s` double DEFAULT NULL,
  `pfa` double DEFAULT NULL,
  `pfb` double DEFAULT NULL,
  `pfc` double DEFAULT NULL,
  `pf` double DEFAULT NULL,
  `f` double DEFAULT NULL,
  `ept` double DEFAULT NULL,
  `eqi` double DEFAULT NULL,
  `eqe` double DEFAULT NULL,
  `p_d` double DEFAULT NULL,
  `lvur` double DEFAULT NULL,
  `iur` double DEFAULT NULL,
  `ua_thd` double DEFAULT NULL,
  `ub_thd` double DEFAULT NULL,
  `uc_thd` double DEFAULT NULL,
  `ia_thd` double DEFAULT NULL,
  `ib_thd` double DEFAULT NULL,
  `ic_thd` double DEFAULT NULL,
  `t1` double DEFAULT NULL,
  `t2` double DEFAULT NULL,
  `t3` double DEFAULT NULL,
  `t4` double DEFAULT NULL,
  `segment1` double DEFAULT NULL,
  `segment2` double DEFAULT NULL,
  `segment3` double DEFAULT NULL,
  `segment4` double DEFAULT NULL,
  `segment5` varchar(32) DEFAULT NULL,
  `segment6` varchar(32) DEFAULT NULL,
  `tm` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
"""

create_monitor_test = """
CREATE TABLE `mo_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deleted` tinyint(1) NOT NULL,
  `created_time` datetime(6) DEFAULT NULL,
  `updated_time` datetime(6) DEFAULT NULL,
  `deleted_time` datetime(6) DEFAULT NULL,
  `imei` varchar(32) DEFAULT NULL,
  `ua` double DEFAULT NULL,
  `ub` double DEFAULT NULL,
  `uc` double DEFAULT NULL,
  `uab` double DEFAULT NULL,
  `ubc` double DEFAULT NULL,
  `uca` double DEFAULT NULL,
  `ia` double DEFAULT NULL,
  `ib` double DEFAULT NULL,
  `ic` double DEFAULT NULL,
  `ir` double DEFAULT NULL,
  `pa` double DEFAULT NULL,
  `pb` double DEFAULT NULL,
  `pc` double DEFAULT NULL,
  `p` double DEFAULT NULL,
  `qa` double DEFAULT NULL,
  `qb` double DEFAULT NULL,
  `qc` double DEFAULT NULL,
  `q` double DEFAULT NULL,
  `sa` double DEFAULT NULL,
  `sb` double DEFAULT NULL,
  `sc` double DEFAULT NULL,
  `s` double DEFAULT NULL,
  `pfa` double DEFAULT NULL,
  `pfb` double DEFAULT NULL,
  `pfc` double DEFAULT NULL,
  `pf` double DEFAULT NULL,
  `f` double DEFAULT NULL,
  `ept` double DEFAULT NULL,
  `eqi` double DEFAULT NULL,
  `eqe` double DEFAULT NULL,
  `p_d` double DEFAULT NULL,
  `lvur` double DEFAULT NULL,
  `iur` double DEFAULT NULL,
  `ua_thd` double DEFAULT NULL,
  `ub_thd` double DEFAULT NULL,
  `uc_thd` double DEFAULT NULL,
  `ia_thd` double DEFAULT NULL,
  `ib_thd` double DEFAULT NULL,
  `ic_thd` double DEFAULT NULL,
  `t1` double DEFAULT NULL,
  `t2` double DEFAULT NULL,
  `t3` double DEFAULT NULL,
  `t4` double DEFAULT NULL,
  `segment1` double DEFAULT NULL,
  `segment2` double DEFAULT NULL,
  `segment3` double DEFAULT NULL,
  `segment4` double DEFAULT NULL,
  `segment5` varchar(32) DEFAULT NULL,
  `segment6` varchar(32) DEFAULT NULL,
  `tm` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
"""


class MonitorResp(object):
    def get_point_by_imei(self, imei):
        return MonitorDevice.objects.filter(imei=imei).first()

    def get_(self, point_id):
        return MonitorPoints.objects.filter(id=point_id)

    def get_electricity_user(self, id):
        return ElectricityUser.objects.filter(id=id).first()

    def get_tar_table_name(self, imei):
        sql= """
        SELECT C.number, B.id as point_id,
        A.voltage_ratio imei_voltage_ratio,
        A.current_ratio imei_current_ratio,
        B.current_ratio, B.voltage_ratio
        FROM monitor_device A
        LEFT JOIN monitor_points B
        ON A.monitor_point_id = B.id
        LEFT JOIN electricity_user C
        ON B.customer_id = C.id
        WHERE A.imei = '%s'
        """ % imei
        return console_db.query(sql)

    def insert_raw_or_create_tb(self, table, value):
        if table == "mo_test":
            try:
                MonitorMetaDataForTest.objects.create(**value)
                return
            except ProgrammingError:
                base_db.query(create_monitor_test % table)
                self.insert_raw_or_create_tb(table, value)

        class MonitorData(MonitorMetaData):
            class Meta:
                db_table = table
        try:
            MonitorData.objects.create(**value)
        except ProgrammingError as e:
            rst = re.search(r"^.*Table '(.*)' doesn't exist", str(e))
            if rst:
                table = rst.group(1).split(".")[-1]
                base_db.query(create_monitor_tb % table)
                self.insert_raw_or_create_tb(table, value)

    def update_monitor_device(self, imei, status):
        MonitorDevice.objects.using("console").filter(imei=imei).update(status=status)

    def update_monitor_iccid(self, imei, iccid):
        MonitorDevice.objects.using("console").filter(imei=imei).update(iccid=iccid)

    def update_monitor_points(self, point_id, status):
        MonitorPoints.objects.using("console").filter(id=point_id).update(status=status)

    def create_or_update_alarm(self, point_id, dt, tm):
        alarm = MonitorAlarm.objects.using(
            "console").filter(monitor_point_id=point_id).order_by("-created_time").first()
        if alarm and not alarm.is_end:
            if dt["type"] == alarm.type:
                alarm.__dict__.update(**dt)
            else:
                alarm.is_end=True
            alarm.duration = int((datetime.datetime.strptime(tm, "%Y-%m-%d %H:%M:%S") -
                              alarm.created_time
                              ).total_seconds())
            alarm.save()
        else:
            if dt["type"] != 0:
                dt["duration"] = 0
                dt["status"] = 0
                MonitorAlarm.objects.using("console").create(**dt)


monitor_resp = MonitorResp()
