# -*- coding:utf-8 -*-
"""
data：2021/7/21 09:50
装置替换：替换之前需要先做数据检测：旧装置是否可拆、新装置（如果曾经接入过系统，是否处于已拆除状态）
目前，设备管理界面不支持搜索已经拆掉、被替换掉的装置
需要如下操作：
meter_param_record：继承被替换装置mid属性，新增一条记录mid为替换装置的mid
change_meter_record：插入新记录，新记录mid为新装置mid
change_sensor_record：插入新记录，sid、field字段为新装置属性
monitor：sid字段更新为新的sid；
monitor_his_record：更新旧装置demolished, demolished_time字段；并再新增一条记录，mtid相同，新的sid，install_time

注意：
1. 本接口不允许直接替换已在系统的装置：仅允许替换已被拆掉装置 + 新装置
2. 如果是手动拆表、换表，请注意change_meter_record的start_time与install_ts时间需要严格一致

"""

import time
# from asyncio import get_event_loop
from pot_libs.mysql_util.mysql_trans_util import MysqlUtil
from pot_libs.logger import log


async def _load_o_sid_infos(cid, sid):
    # to simply load related info，load info forward
    async with MysqlUtil() as mysql_u:
        sql = "SELECT mtid FROM power_iot.monitor " \
              "WHERE cid=%s AND sid=%s AND demolished=0"
        mtids = [re["mtid"] for re in await mysql_u.fetchall(sql, (cid, sid))]
        if not mtids:
            log.error(f"no monitor to replace found, cid:{cid} sid:{sid}")
            return None
        
        lid_fields = {}
        sql = "SELECT id FROM power_iot.location WHERE mtid IN %s"
        lids = [re["id"] for re in
                await mysql_u.fetchall(sql, (tuple(mtids),))]
        for lid in lids:
            sql = "SELECT field FROM power_iot.change_sensor_record " \
                  "WHERE location_id=%s ORDER BY start_time DESC LIMIT 1;"
            field = await mysql_u.fetch_value(sql, (lid,))
            lid_fields[lid] = field
        
        sql = "SELECT pid, mtid FROM power_iot.point WHERE mtid IN %s"
        pid_mtids = await mysql_u.fetchall(sql, (tuple(mtids),))
        if not pid_mtids:
            log.error(f"no monitor in point found, cid:{cid} sid:{sid}")
            return None
        
        o_meter_infos = dict(meter_no={}, lid_fields=lid_fields)
        for item in pid_mtids:
            pid, mtid = item["pid"], item["mtid"]
            sql = "SELECT mid FROM power_iot.change_meter_record " \
                  "WHERE pid=%s ORDER BY start_time DESC LIMIT 1;"
            mid = await mysql_u.fetch_value(sql, (pid,))
            if mid is None:
                log.error(f"old sid:{sid} demolished")
                return None
            
            sql = "SELECT meter_no FROM power_iot.meter WHERE mid=%s;"
            meter_no = await mysql_u.fetch_value(sql, (mid,))
            o_meter_infos["meter_no"][meter_no] = dict(pid=pid, mid=mid,
                                                       mtid=mtid)
        return o_meter_infos


async def _check_can_replace_in(new_sid):
    meter_info, lid_fields = None, {}
    async with MysqlUtil() as mysql_u:
        # 1. check new sid is in sysyem
        sql = "SELECT 1 FROM power_iot.monitor " \
              "WHERE sid=%s AND demolished=0"
        if await mysql_u.fetchone(sql, (new_sid,)):
            log.error(f"new_sid:{new_sid} in monitor, not surpport replace")
            return False, meter_info, lid_fields
        
        # 2. # check new sid in meter
        sql = "SELECT mid, sid, meter_no FROM power_iot.meter WHERE sid=%s;"
        _meter_info = await mysql_u.fetchall(sql, (new_sid,))
        if not _meter_info:
            log.error(f"new sid:{new_sid} not in meter table")
            return False, meter_info, lid_fields
        
        # 3. check new sid in change_meter_record
        meter_info = {re['meter_no']: re['mid'] for re in _meter_info}
        for mid in meter_info.values():
            sql = "SELECT pid FROM change_meter_record " \
                  "WHERE mid=%s ORDER BY start_time DESC LIMIT 1;"
            pid = await mysql_u.fetch_value(sql, (mid,))
            if not pid:
                # new sid
                continue
            
            sql = "SELECT mid FROM change_meter_record " \
                  "WHERE pid=%s ORDER BY start_time DESC LIMIT 1;"
            if await mysql_u.fetch_value(sql, (pid,)) == mid:
                # if _mid is None or _mid != mid: # demolished or replaced
                log.error(f"{new_sid} in system, demolish first")
                return False, meter_info, lid_fields
        
        # 4. check new sid in change_sensor_record
        now_ts = int(time.time())
        sql = "SELECT * FROM change_sensor_record " \
              "WHERE sid=%s ORDER BY start_time ASC"
        for re in await mysql_u.fetchall(sql, (new_sid,)):
            if re['start_time'] < now_ts:
                lid_fields[re["field"]] = re["location_id"]
        
        for lid in lid_fields.values():
            sql = "SELECT sid FROM change_sensor_record " \
                  "WHERE location_id=%s ORDER BY start_time DESC LIMIT 1;"
            _sid = await mysql_u.fetch_value(sql, (lid,))
            if _sid is None or _sid != new_sid:
                continue
            
            log.error(f"{new_sid} in change_sensor_record, demolish first")
            return False, meter_info, lid_fields
    
    return True, meter_info, lid_fields


async def _load_new_param_sql(mid, new_mid, start_ts):
    key_str, value_str, params = "mid", "%s", [new_mid]
    sql = "SELECT * FROM power_iot.meter_param_record WHERE mid=%s;"
    async with MysqlUtil() as mysql_u:
        record = await mysql_u.fetchone(sql, (mid,))
        for k, v in record.items():
            if v is None or k == "mid":
                continue
            
            if k == "start_time":
                v = start_ts
            
            key_str += f", {k}"
            value_str += ", %s"
            params.append(v)
    
    sql = f"INSERT INTO power_iot.meter_param_record({key_str})" \
          f" VALUES ({value_str});"
    # log.info(f"meter_param_record sql:{sql}")
    return sql, params


async def dev_replace(cid, sid, new_sid):
    # check old sid in monitor
    if sid == new_sid:
        return False, "sid相同"
    
    o_meter_infos = await _load_o_sid_infos(cid, sid)
    if not o_meter_infos:
        return False, "旧装置被拆除,或配置有误,无法更换"
    
    # check new sid in meter、change_meter_record、change_sensor_record
    can_repalce_in, n_meter_infos, _ = await _check_can_replace_in(new_sid)
    if not can_repalce_in:
        return False, "新装置已存在,或配置有误,无法更换"
    
    log.info(f"begin to replace dev:cid:{cid} sid:{sid} new_sid:{new_sid}")
    async with MysqlUtil() as mysql_u:
        try:
            new_ts = int(time.time())  # demolished time，new record time
            for o_meter_no, v in o_meter_infos["meter_no"].items():
                pid, mid, mtid = v['pid'], v['mid'], v['mtid']
                
                # get install time
                sql = "SELECT start_time FROM change_meter_record " \
                      "WHERE pid=%s ORDER BY start_time DESC LIMIT 1;"
                start_ts = await mysql_u.fetch_value(sql, (pid,))
                
                # 1. insert new meter_param_record
                if o_meter_no in ['A', 'B', 'C']:
                    if o_meter_no in n_meter_infos:
                        _meter_no = o_meter_no
                    else:
                        log.error(f"new sid:{new_sid} not support sdu")
                        return False, "新装置不支持sdu"
                else:
                    _meter_no = new_sid
                new_mid = n_meter_infos[_meter_no]
                sql, params = await _load_new_param_sql(mid, new_mid, new_ts)
                await mysql_u.execute(sql, args=tuple(params))
                
                # 2. update monitor
                sql = "UPDATE power_iot.monitor SET sid=%s, meter_no=%s " \
                      "WHERE mtid=%s"
                await mysql_u.execute(sql, args=(new_sid, _meter_no, mtid))
                
                # 3. update change_meter_record：add new record with new mid
                sql = "INSERT INTO power_iot.change_meter_record " \
                      "(pid, start_time, mid) VALUES (%s, %s, %s);"
                await mysql_u.execute(sql, args=(pid, new_ts, new_mid))
                
                # 4. update old monitor_his_record
                sql = "UPDATE power_iot.monitor_his_record " \
                      "SET demolished=1, demolished_ts=%s " \
                      "WHERE mtid=%s AND sid=%s AND install_ts=%s"
                await mysql_u.execute(sql, (new_ts, mtid, sid, start_ts))
                
                # 5. insert new monitor_his_record
                sql = "INSERT INTO power_iot.monitor_his_record " \
                      "(mtid, sid, meter_no, install_ts) " \
                      "VALUES (%s, %s, %s, %s);"
                await mysql_u.execute(sql, args=(mtid, new_sid, _meter_no,
                                                 new_ts))
            
            # 2. update change_sensor_record：insert new record with new sid + field
            lid_fields = o_meter_infos["lid_fields"]
            for lid, field in lid_fields.items():
                sql = "INSERT INTO power_iot.change_sensor_record " \
                      "(location_id, start_time, sid, field) " \
                      "VALUES (%s, %s, %s, %s);"
                await mysql_u.execute(sql, args=(lid, new_ts, new_sid, field))
        except Exception as e:
            log.error(f"device demolish fail e:{e}")
            await mysql_u.rollback()
            return False, "装置更换异常"
        else:
            await mysql_u.commit()
            log.info(f"finish replace dev:cid:{cid} o_sid:{sid} "
                     f"new_sid:{new_sid} new_ts:{new_ts}")
    
    return True, "操作成功"

# if __name__ == '__main__':
#     cid = 126
#     sid = 'A2004000944'
#     new_sid = 'A2004000316'
#     get_event_loop().run_until_complete(dev_replace(cid, sid, new_sid))
