import json
from common.utils.models import BaseConnection

from console.customer.apply_func import json_loads_f, split_f

from console.powerstation.models import PowerStation

base_db = BaseConnection()


class MessageSqlRepo(object):
    def get_user_message_config(self, user_id=None):
        config = []
        if not user_id:
            user_id = 0
        _sql = """
        select * from user_config where `type`=1 and user_id=%s
        """ % user_id
        rst = base_db.query(_sql)
        if rst:
            config = json.loads(rst[0]["config"]) if rst[0]["config"] else []
        print(config)
        return config

    def get_messages(self, enterprise_ids, user_id, config=None, person=False, **kwargs):
        page = kwargs.get("page")
        page_size = kwargs.get("page_size")
        limit = base_db.paginator(page, page_size)
        get_all = kwargs.get("get_all", False)
        if get_all:
            limit=None
        order_map = {
            "is_read": "a.is_read",
            "created_time": "a.created_time",
        }
        order_by = base_db.order_by(kwargs.get("_sort"), order_map)
        where = ["a.deleted=0 and b.enterprise_id in(%s)" % ','.join(enterprise_ids)]
        where.extend(base_db.filter(user_id, value='a.receive_id={}'))
        where.extend(base_db.filter(kwargs.get("is_read"), value='a.is_read={}'))
        where.extend(base_db.filter(kwargs.get("id"), value='a.id={}'))
        case_when = ""
        if person:
            if config:
                case_when = """ case """
                for i in config:
                    if i["message"]["is_receive"]:
                        case_when += " when b.type={} then {} ".format(i["type"], i["message"]["is_pop"])
                case_when = """, case when ( %s else 0 end)=1 and a.is_pop=1 then 1 else 0 end is_pop """ % case_when
        if not order_by:
            order_by = "a.created_time DESC"
        _sql = """
        select
            %s
        from messages_dispatcher a
        left join messages b
        left join users c
        on b.sender_id=c.id
        on a.message_id=b.id
        """
        select = """
            a.id,
            a.receive_id,
            b.sender_id,
            c.nickname sender_name,
            b.title,
            b.content,
            b.render_type,
            b.type,
            b.links,
            a.is_read,
            b.enterprise_id,
            a.created_time
            %s
        """ % case_when
        count_select = ' COUNT(a.id) as `count` '
        count_sql, _ = base_db.sql_splice(_sql, count_select, where=where, order_by=order_by)
        sql, count = base_db.sql_splice(_sql,
                                        select,
                                        where=where,
                                        count_sql=count_sql,
                                        limit=limit,
                                        limit_idx="A.id",
                                        order_by=order_by)
        df = base_db.query_df(sql)
        return df.to_dict(orient="records"), count

    def customers_report_info(self, electrical_user_id, year_month, old_month):
        _sql = """
        select
            B.id as electricity_user_id,
            B.customer_id,
            B.transformer_capacity,
            B.real_capacity,
            C.name,
            B.number,
            D.address,
            E.name contact_name,
            E.phone contact_phone,
            "%s" `year_month`
        from electricity_user B
        left join customer C
        on C.id=B.customer_id
        left join enterprise D
        on C.enterprise_id=D.id
        left join contact E
        on E.id=B.report_contact_id
        where FIND_IN_SET('1', C.type) and B.id=%s
        """ % (year_month, electrical_user_id)
        base_info = base_db.query(_sql)[0]
        _bill_sql = """
        select
            A.number,
            A.ep_factor as power_factor,
            B.id as electrical_info_id,
            B.power_number,
            B.meter_number,
            B.transformer_capacity capacity,
            B.real_capacity,
            IFNULL(C.peak_volume,0)+IFNULL(C.peak_volume2,0) peak_volume,
            IFNULL(C.tip_volume,0)+IFNULL(C.tip_volume2,0) tip_volume,
            IFNULL(C.usual_volume,0)+IFNULL(C.usual_volume2,0) usual_volume,
            IFNULL(C.valley_volume,0)+IFNULL(C.valley_volume2,0) valley_volume,
            C.other_volume,
            IFNULL(C.peak_volume,0)+IFNULL(C.peak_volume2,0)+
            IFNULL(C.tip_volume,0)+IFNULL(C.tip_volume2,0)+
            IFNULL(C.usual_volume,0)+IFNULL(C.usual_volume2,0)+
            IFNULL(C.valley_volume,0)+IFNULL(C.valley_volume2,0)+
            IFNULL(C.other_volume,0) as volume,
            IFNULL(C.peak_price,0)*IFNULL(C.peak_volume,0)+
            IFNULL(C.peak_price2,0)*IFNULL(C.peak_volume2,0) peak_price,
            IFNULL(C.tip_price,0)*IFNULL(C.tip_volume,0)+
            IFNULL(C.tip_price2,0)*IFNULL(C.tip_volume2,0) tip_price,
            IFNULL(C.usual_price,0)*IFNULL(C.usual_volume,0)+
            IFNULL(C.usual_price2,0)*IFNULL(C.usual_volume2,0) usual_price,
            IFNULL(C.valley_price,0)*IFNULL(C.valley_volume,0)+
            IFNULL(C.valley_price2,0)*IFNULL(C.valley_volume2,0) valley_price,
            IFNULL(C.other_price,0)*IFNULL(C.other_volume,0) other_price,
            C.levy_fee,
            # C.power_factor,
            C.power_factor_real,
            C.power_factor_adjust,
            C.report_md,
            C.max_md,
            C.basic_volume,
            C.basic_price,
            C.idle_volume,
            C.amount,
            C.amount_adjust,
            IFNULL(D.peak_volume,0)+IFNULL(D.peak_volume2,0)+
            IFNULL(D.tip_volume,0)+IFNULL(D.tip_volume2,0)+
            IFNULL(D.usual_volume,0)+IFNULL(D.usual_volume2,0)+
            IFNULL(D.valley_volume,0)+IFNULL(D.valley_volume2,0)+
            IFNULL(D.other_volume,0) as old_volume,
            D.amount old_amount
        from
        electricity_user A
        left join electrical_info B
        on A.id=B.electricity_user_id
        left join electricity_bill C
        on C.electrical_id=B.id
        left join (SELECT * FROM electricity_bill DD
                    where DD.year_month="%s") D
        on C.electrical_id=D.electrical_id
        where C.year_month="%s"
        and A.id=%s
        """ % (old_month, year_month, electrical_user_id)
        bill = base_db.query(_bill_sql)
        base_info.update({"bill": bill})
        _inspect_data = """
        SELECT
            DATE_FORMAT(B.work_date,"%%Y-%%m-%%d %%H:%%i:%%s") work_date,
            B.id task_id,
            DATE_FORMAT(B.end_time,"%%Y-%%m-%%d %%H:%%i:%%s") end_time,
            C.inspector,
            BC.id as team_headman_id,
            BC.nickname as team_headman_name,
            C.temperature,
            C.humidity,
            ifnull(C.weather,"晴") as weather,
            C.remarks,
            C.battery_voltage,
            C.direct_voltage,
            C.thermal_number,
            AA.cable_conduit_status,
            AA.frame_status,
            AA.ground_status,
            AA.window_status,
            AA.house_status,
            AA.cable_holes_status,
            AAA.electroprobe_status,
            AAA.ground_wire,
            AAA.insulating_mat,
            AAA.insulating_gloves,
            AAA.insulating_shoes,
            AAA.extinguisher,
            E.power_number,
            E.meter_number,
            E.id as electrical_info_id,
            D.multiplying_power,
            D.power_factor,
            D.total_active_power,
            D.peak,
            D.flat_1,
            D.flat_2,
            D.valley,
            D.peak_md,
            D.flat_1_md,
            D.flat_2_md,
            D.valley_md,
            D.max_md,
            D.declare_md,
            E.transformer_capacity as capacity,
            E.voltage_level,
            E.real_capacity,
            D.real_power_factor,
            D.reactive_power_1,
            D.reactive_power_2,
            F.v_ab,
            F.v_bc,
            F.v_ca,
            F.i_a,
            F.i_b,
            F.i_c,
            F.monitor_a as i_ma,
            F.monitor_b as i_mb,
            F.monitor_c as i_mc,
            G.outline,
            G.o_ia,
            G.o_ib,
            G.o_ic,
            G.cos,
            G.power,
            G.monitor_a as o_ma,
            G.monitor_b as o_mb,
            G.monitor_c as o_mc,
            G.voice,
            G.fan,
            G.temperature o_temperature,
            G.oil_leak,
            G.dry,
            G.abnormal,
            G.switch_v_ab,
            G.switch_v_bc,
            G.switch_ia,
            G.switch_ib,
            G.switch_v_ca,
            G.switch_ic,
            G.GGJ,
            G.temperature_a,
            G.temperature_b,
            G.temperature_c
        FROM
        (select id,station_id from inspection_plan
        where station_id in (select B.id from electricity_user A
        left join power_station B
        on A.id=B.electricity_user_id
        where A.id=%s)) A
        left join station_building AA
        on AA.station_id=A.station_id
        left join safety_equipment AAA
        on AAA.station_id=A.station_id
        left join
        inspection_task B
        ON B.plan_id=A.id
        left join team BB
        on BB.id=B.team_id
        left join users BC
        ON BB.team_headman_id=BC.id
        left join inspection_data C
        ON C.inspection_task_id=B.id
        left join power_inspection_data D
        on D.inspection_task_id=B.id
        LEFT JOIN electrical_info E
        on D.power_number=E.id
        LEFT JOIN inspection_in F
        on F.power_number=E.id and F.inspect_task_id=B.id
        left join inspection_out G
        on G.power_number=E.id and G.inspect_task_id=B.id
        WHERE B.status="completed" and left(B.work_date,7)="%s"
        """ % (electrical_user_id, year_month)
        df = base_db.query_df(_inspect_data)
        data = base_db.query(_inspect_data)
        inspect = []

        for task_id, task_data in df.groupby("task_id"):
            task_result = {
                "task_id": task_id,
                "work_date": task_data.work_date.values[0],
                "end_time": task_data.end_time.values[0],
                "inspector": task_data.inspector.values[0],
                "team_headman_id": task_data.team_headman_id.values[0],
                "team_headman_name": task_data.team_headman_name.values[0],
                "temperature": task_data.temperature.values[0],
                "humidity": task_data.humidity.values[0],
                "weather": task_data.weather.values[0],
                "remarks": task_data.remarks.values[0],
                "battery_voltage": task_data.battery_voltage.values[0],
                "direct_voltage": task_data.direct_voltage.values[0],
                "thermal_number": task_data.thermal_number.values[0],
                "cable_conduit_status":
                    task_data.cable_conduit_status.values[0],
                "frame_status": task_data.frame_status.values[0],
                "ground_status": task_data.ground_status.values[0],
                "window_status": task_data.window_status.values[0],
                "house_status": task_data.house_status.values[0],
                "cable_holes_status": task_data.cable_holes_status.values[0],
                "electroprobe_status": task_data.electroprobe_status.values[0],
                "ground_wire": task_data.ground_wire.values[0],
                "insulating_mat": task_data.insulating_mat.values[0],
                "insulating_gloves": task_data.insulating_gloves.values[0],
                "insulating_shoes": task_data.insulating_shoes.values[0],
                "extinguisher": task_data.extinguisher.values[0],
                "inspect_in": []
            }

            for electrical_info_id, power_data in task_data.groupby(
                    "electrical_info_id"):
                inspect_in = {
                    "power_number_id": electrical_info_id,
                    "power_number": power_data.power_number.values[0],
                    "meter_number": power_data.meter_number.values[0],
                    "magnification": power_data.multiplying_power.values[0],
                    "power_factor": power_data.power_factor.values[0],
                    "total_active_power":
                        power_data.total_active_power.values[0],
                    "peak": power_data.peak.values[0],
                    "flat_1": power_data.flat_1.values[0],
                    "flat_2": power_data.flat_2.values[0],
                    "valley": power_data.valley.values[0],
                    "peak_md": power_data.peak_md.values[0],
                    "flat_1_md": power_data.flat_1_md.values[0],
                    "flat_2_md": power_data.flat_2_md.values[0],
                    "valley_md": power_data.valley_md.values[0],
                    "max_md": power_data.max_md.values[0],
                    "declare_md": power_data.declare_md.values[0],
                    "capacity": power_data.capacity.values[0],
                    "voltage_level": power_data.voltage_level.values[0],
                    "real_capacity": power_data.real_capacity.values[0],
                    "real_power_factor":
                        power_data.real_power_factor.values[0],
                    "reactive_power_1": power_data.reactive_power_1.values[0],
                    "reactive_power_2": power_data.reactive_power_2.values[0],
                    "v_ab": power_data.v_ab.values[0],
                    "v_bc": power_data.v_bc.values[0],
                    "v_ca": power_data.v_ca.values[0],
                    "i_a": power_data.i_a.values[0],
                    "i_b": power_data.i_b.values[0],
                    "i_c": power_data.i_c.values[0],
                    "i_ma": power_data.i_ma.values[0],
                    "i_mb": power_data.i_mb.values[0],
                    "i_mc": power_data.i_mc.values[0],
                    "outline": []
                }
                for item in data:
                    if task_id == item.get("task_id"):
                        outline_result = {
                            "outline_id": item.get("outline"),
                            "o_ia": item.get("o_ia"),
                            "o_ib": item.get("o_ib"),
                            "o_ic": item.get("o_ic"),
                            "cos": item.get("cos"),
                            "power": item.get("power"),
                            "o_ma": item.get("o_ma"),
                            "o_mb": item.get("o_mb"),
                            "o_mc": item.get("o_mc"),
                            "voice": item.get("voice"),
                            "fan": item.get("fan"),
                            "o_temperature": item.get("o_temperature"),
                            "oil_leak": item.get("oil_leak"),
                            "dry": item.get("dry"),
                            "abnormal": item.get("abnormal"),
                            "switch_v_ab": item.get("switch_v_ab"),
                            "switch_v_bc": item.get("switch_v_bc"),
                            "switch_ia": item.get("switch_ia"),
                            "switch_ib": item.get("switch_ib"),
                            "switch_v_ca": item.get("switch_v_ca"),
                            "switch_ic": item.get("switch_ic"),
                            "GGJ": item.get("GGJ"),
                            "temperature_a": item.get("temperature_a"),
                            "temperature_b": item.get("temperature_b"),
                            "temperature_c": item.get("temperature_c"),
                        }

                        inspect_in["outline"].append(outline_result)

                # for outline, outline_data in power_data.groupby("outline"):

                task_result["inspect_in"].append(inspect_in)
            inspect.append(task_result)
        base_info.update({"inspect": inspect})
        return base_info

    def customers_info(self, enterprise_ids, **kwargs):
        page = kwargs.get("page")
        page_size = kwargs.get("page_size")
        customer_id = PowerStation.objects.filter(deleted=False).values_list("customer_id", flat=True)
        where = ["A.deleted=false and (A.service_enterprise_id in (%s) or B.id in (%s))" % (
            ",".join(enterprise_ids), ",".join(enterprise_ids))
                 ]
        if kwargs.get("trusteeship"):
            where = ["A.service_enterprise_id in (%s)" % ",".join(enterprise_ids) + " and A.id in {}".format(
                tuple(filter(None, customer_id)))
                     ]
        where.extend(base_db.filter(kwargs.get("id"), value='A.id={}'))
        where.extend(
            base_db.filter(kwargs.get("customer"),
                           value='A.name LIKE "%%{}%%"'))
        where.extend(
            base_db.filter(kwargs.get("name"), value='A.name LIKE "%%{}%%"'))
        where.extend(
            base_db.filter(kwargs.get("keyword"),
                           value='concat(A.name,B.address) LIKE "%%{}%%"'))
        where.extend(
            base_db.filter(kwargs.get("adcode"),
                           value='B.adcode LIKE "{}%%"',
                           func=lambda x: x.strip("0")))
        where.extend(
            base_db.filter(kwargs.get("type"),
                           value='FIND_IN_SET("{}",A.type)',
                           multi=True))
        group_by = 'A.id'
        limit = base_db.paginator(page, page_size)
        _sql = """
            SELECT
                %s
            FROM customer A
            LEFT JOIN enterprise B
            ON A.enterprise_id=B.id
            LEFT JOIN enterprise BB
            ON A.service_enterprise_id=BB.id
            LEFT JOIN organization BC
            ON A.service_organization_id=BC.id
            LEFT JOIN users C
            ON C.id=A.last_service_staff_id
            LEFT JOIN users D
            ON D.id=A.service_staff_id
            LEFT JOIN (select
                        b.id,
                        a.customer_id,
                        b.phone,
                        b.nickname,
                        b.email,
                        b.wechat,
                        c.username,
                        concat("[", ifnull(group_concat(concat(
                                "{",'"id":', e.id, ","),
                                concat('"name":"',e.name, '"}')),""),"]"
                                ) as tags
                        from customer_admin a
                        left join users b
                        on a.user_id=b.id
                        left join accounts c
                        on c.id=b.account_id
                        left join user_tags d
                        on a.user_id=d.user_id
                        left join tags e
                        on d.tag_id=e.id
                        group by a.id) E
            ON E.customer_id=A.id
            LEFT JOIN (SELECT
                    A.id, A.name, A.phone, A.tel, A.email, A.qq,
                    A.wechat, A.is_urge, A.is_quit, A.comments,
                    A.customer_id, AA.name as customer_name,
                    concat("[", ifnull(group_concat(concat(
                    "{",'"id":', C.id, ","),
                    concat('"name":"',C.name, '"}')),""),"]"
                    ) as tags
                    FROM `contact` A
                    left join customer AA
                    on A.customer_id=AA.id
                    left join
                    `contact_tags` B
                    on A.id=B.contact_id
                    left join tags C
                    on B.tag_id=C.id
                    WHERE A.`deleted` = 0
                    group by A.id) G
            ON A.id=G.customer_id
            LEFT JOIN (SELECT
                HH.id, HH.code, HH.entry_date,
                HH.effective_date, HH.end_date,
                HH.customer_id
                FROM contract HH WHERE FIND_IN_SET(HH.type,"1,2,13")
            ) H
            ON H.customer_id=A.id
            LEFT JOIN electricity_user I
            ON I.customer_id=A.id
        """
        limit_sql = """
        SELECT
            %s
        FROM customer A
        LEFT JOIN enterprise B
        ON A.enterprise_id=B.id
        """
        count_select = ' COUNT(A.id) as `count` '
        count_sql, _ = base_db.sql_splice(_sql,
                                          count_select,
                                          where=where,
                                          group_by=group_by)
        select = """
            A.id,
            A.name,
            A.code,
            A.last_service_staff_id,
            C.nickname as last_service_staff_name,
            D.nickname as service_staff_name,
            A.service_staff_id,
            A.level,
            A.type,
            A.service_enterprise_id,
            BB.name service_enterprise_name,
            A.service_organization_id,
            BC.name service_organization_name,
            COUNT(DISTINCT I.id) electricity_user_count,
            concat(group_concat(DISTINCT
                    concat('{','"id":',B.id,","),
                    concat('"name":"',IFNULL(B.name,""),'",'),
                    concat('"address":"',IFNULL(B.address,""),'",'),
                    concat('"enterprise_id":"',IFNULL(B.enterprise_id,""),'",'),
                    concat('"longitude":"',IFNULL(B.longitude,""),'",'),
                    concat('"latitude":"',IFNULL(B.latitude,""),'",'),
                    concat('"scale":"',IFNULL(B.scale,""),'",'),
                    concat('"asset":"',IFNULL(B.asset,""),'",'),
                    concat('"covered_area":"',IFNULL(B.covered_area,""),'",'),
                    concat('"legal_person":"',IFNULL(B.legal_person,""),'",'),
                    concat('"legal_person_phone":"',IFNULL(B.legal_person_phone,""),'",'),
                    concat('"nature":"',IFNULL(B.nature,""),'",'),
                    concat('"industry":"',IFNULL(B.industry,""),'",'),
                    concat('"parent_enterprise_id":"',IFNULL(B.parent_enterprise_id,""),'",'),
                    concat('"postcode":"',IFNULL(B.postcode,""),'",'),
                    concat('"tax_num":"',IFNULL(B.tax_num,""),'",'),
                    concat('"bank_account_name":"',IFNULL(B.bank_account_name,""),'",'),
                    concat('"bank_name":"',IFNULL(B.bank_name,""),'",'),
                    concat('"logo":"',IFNULL(B.logo,""),'",'),
                    concat('"adcode":"',IFNULL(B.adcode,""),'"}')
                  )
            ) enterprise,
            concat('[', group_concat(DISTINCT
                    concat('{','"id":',E.id,","),
                    concat('"phone":"',IFNULL(E.phone,""),'",'),
                    concat('"nickname":"',replace(IFNULL(E.nickname,""),'"','\\\\"'),'",'),
                    concat('"username":"',IFNULL(E.username,""),'",'),
                    concat('"email":"',IFNULL(E.email,""),'",'),
                    concat('"phone":"',IFNULL(E.phone,""),'",'),
                    concat('"wechat":"',IFNULL(E.wechat,""),'",'),
                    concat('"tags":',IFNULL(E.tags,""),""),
                    '}'),']'
                    ) customer_admin,
            concat('[', group_concat(DISTINCT concat(
                    '{','"id":', G.id, ","),
                    concat('"phone":"',IFNULL(G.phone,""),'",'),
                    concat('"tel":"',IFNULL(G.tel,""),'",'),
                    concat('"qq":"',IFNULL(G.qq,""),'",'),
                    concat('"wechat":"',IFNULL(G.wechat,""),'",'),
                    concat('"email":"', IFNULL(G.email,""),'",'),
                    concat('"is_urge":',IFNULL(G.is_urge,""),','),
                    concat('"is_quit":',IFNULL(G.is_quit,""),','),
                    concat('"tags":',IFNULL(G.tags,""),","),
                    concat('"comments":"',IFNULL(G.comments,""),'",'),
                    concat('"name":"',IFNULL(G.name,""),'"}')),']'
                    ) contact,
            concat('[', group_concat(DISTINCT concat(
                    '{','"id":', H.id, ","),
                    concat('"code":"',IFNULL(H.code,""),'",'),
                    concat('"entry_date":"',IFNULL(H.entry_date,""),'",'),
                    concat('"effective_date":"',IFNULL(H.effective_date,""),'",'),
                    concat('"end_date":"',IFNULL(H.end_date,""),'"}')),']'
                    ) contract
        """
        sql, count = base_db.sql_splice(_sql,
                                        select,
                                        where=where,
                                        count_sql=count_sql,
                                        group_by=group_by,
                                        limit=limit,
                                        limit_sql=limit_sql,
                                        limit_idx="A.id")

        df = base_db.query_df(sql)
        df["type"] = df['type'].apply(split_f, value=",")
        df["enterprise"] = df['enterprise'].apply(json_loads_f)
        df["customer_admin"] = df['customer_admin'].apply(json_loads_f)
        df["contact"] = df['contact'].apply(json_loads_f)
        df["contract"] = df['contract'].apply(json_loads_f)
        return df.to_dict(orient="records"), count


message_sql_repo = MessageSqlRepo()
