import json

from common.accounts.models import Enterprise
from console.customer.models import Customer, Contact
from console.electricityuser.models import ElectricityUser
from common.utils.str_utils import make_uuid, name_maker
from common.utils.exceptions import ValidationError
from common.utils.models import (BaseConnection, MonitorOAConnection,
                                 PowerOAConnection)
from crm.crm_customer.models import CustomerClue, CrmCustomer

base_db = BaseConnection()
base_mo_db = BaseConnection(db_alias="monitor")
moa_db = MonitorOAConnection()
oa_db = PowerOAConnection()

class CrmCustomerRepo(object):
    def get_customers(self, enterprise_ids, user_id, **kwargs):
        page = kwargs.get("page")
        page_size = kwargs.get("page_size")
        limit = base_db.paginator(page, page_size)
        is_hub = kwargs.get("is_hub", 0)
        if isinstance(is_hub, list):
            is_hub = int(is_hub[0])
        order_map = {
            "created_time": "a.created_time",
            "status": "a.status",
            "level": "a.level",
            "id": "a.id",
            "name": "c.name",
            "address": "d.address",
        }
        order_by = base_db.order_by(kwargs.get("_sort"), map=order_map, default="a.id, f.id desc")
        if is_hub == 1:
            where = ["c.service_enterprise_id in (%s) and  a.salesman_id is null"
                     % (','.join(enterprise_ids))]
        else:
            where = ["c.service_enterprise_id in (%s) and (a.salesman_id=%s)"
                     % (','.join(enterprise_ids), str(user_id))]
        where.extend(base_db.filter(kwargs.get("keyword"), value='concat(c.name,d.address) LIKE "%%{}%%"'))
        where.extend(base_db.filter(kwargs.get("level"), value='a.level={}'))
        where.extend(base_db.filter(kwargs.get("status"), value='a.status={}'))

        _sql = """
        select
            %s
        from crm_customer a
        left join users b
        on a.salesman_id=b.id
        left join customer c
        on a.customer_id=c.id
        left join enterprise d
        on c.enterprise_id=d.id
        left join electricity_user e
        on e.customer_id=c.id
        left join crm_plan f
        on a.id=f.customer_id
        """
        select = """
            a.id,
            a.is_sign,
            c.name,
            c.id customer_id,
            a.level,
            a.status,
            d.address,
            d.adcode,
            d.city_code,
            d.district,
            d.longitude,
            d.latitude,
            count(e.id) ele_user_count,
            a.created_time,
            b.nickname salesman,
            a.salesman_id,
            concat(
                "[", 
                group_concat(
                    concat("{",'"id":',f.id,","),
                    concat('"status":"',ifnull(f.status,""),'",'),
                    concat('"name":"',ifnull(f.name,""),'"}')),
                "]") as plan
        """
        count_select = ' count(a.id) as `count` '
        count_sql, _ = base_db.sql_splice(_sql, count_select, where=where, order_by=order_by, group_by="c.id")
        sql, count = base_db.sql_splice(_sql,
                                        select,
                                        where=where,
                                        count_sql=count_sql,
                                        limit=limit,
                                        limit_idx="a.id",
                                        order_by=order_by,
                                        group_by="c.id")
        df = base_db.query_df(sql)
        df["plan"] = df['plan'].apply(lambda x: (json.loads(x) if x else []))
        return df.to_dict(orient="records"), count

    def get_customers_app(self, enterprise_ids, user_id, **kwargs):
        order_by = base_db.order_by(kwargs.get("_sort"), default="a.id")
        _lt = kwargs.get("_lt")
        _rb = kwargs.get("_rb")
        if _lt and _rb:
            if isinstance(_lt, list):
                _lt = _lt[0]
            if isinstance(_rb, list):
                _rb = _rb[0]
            lt_po = _lt.split[","]
            rb_po = _rb.split[","]
            if len(lt_po) > 1 and len(rb_po) > 1:
                lot = [float(lt_po[0]), float(rb_po[0])]
                lat = [float(lt_po[-1]), float(rb_po[-1])]
                max_longitude = max(lot)
                max_latitude = max(lat)
                min_longitude = min(lot)
                min_latitude = min(lat)
                where = ["c.service_enterprise_id in (%s)"
                         " and (a.salesman_id=%s or a.salesman_id is null) "
                         "and d.longitude>=%s and d.longitude<=%s "
                         "and d.latitude>=%s and d.latitude<=%s "
                         % (
                             ','.join(enterprise_ids), str(user_id), max_longitude,
                             min_longitude, max_latitude, min_latitude)]
            else:
                where = ["c.service_enterprise_id in (%s)"
                         "and (a.salesman_id=%s or a.salesman_id is null)"
                         % (','.join(enterprise_ids), str(user_id))]
        else:
            where = ["c.service_enterprise_id in (%s)"
                     "and (a.salesman_id=%s or a.salesman_id is null)"
                     % (','.join(enterprise_ids), str(user_id))]
        is_sign = kwargs.get("is_sign", None)
        if is_sign:
            if isinstance(is_sign, list):
                is_sign = is_sign[0]
            try:
                if int(is_sign) == 0:
                    where.append("g.id is null")
                else:
                    where.append("g.id is not null")
            except Exception:
                pass
        where.extend(base_db.filter(kwargs.get("keyword"), value='concat(c.name,d.address) LIKE "%%{}%%"'))
        where.extend(base_db.filter(kwargs.get("level"), value='a.level={}'))
        where.extend(base_db.filter(kwargs.get("status"), value='a.status={}'))

        _sql = """
        select
            %s
        from crm_customer a
        left join users b
        on a.salesman_id=b.id
        left join customer c
        on a.customer_id=c.id
        left join enterprise d
        on c.enterprise_id=d.id
        left join electricity_user e
        on e.customer_id=c.id
        left join contact f
        on f.customer_id=c.id
        left join contract g
        on c.id=g.customer_id
        left join crm_customer_clue h
        on h.customer_id=a.customer_id
        and h.crm_customer_id=a.id
        """
        select = """
            a.id,
            c.name,
            if(c.type=0,0,1) is_trusteeship,
            if(g.id,1,0) is_sign,
            c.id customer_id,
            CAST(h.id AS CHAR) crm_customer_clue_id,
            a.level,
            a.status,
            d.address,
            d.adcode,
            d.city_code,
            d.district,
            d.longitude,
            d.latitude,
            a.created_time,
            CAST(f.id AS CHAR) contact_id,
            f.phone contact_phone,
            f.tel contact_tel,
            f.name contact_name
        """
        count_select = ' count(a.id) as `count` '
        group_by = "a.id"
        count_sql, _ = base_db.sql_splice(_sql, count_select, where=where,
                                          order_by=order_by, group_by=group_by)
        sql, count = base_db.sql_splice(_sql,
                                        select,
                                        where=where,
                                        count_sql=count_sql,
                                        order_by=order_by,
                                        group_by=group_by)
        df = base_db.query_df(sql)
        return df.to_dict(orient="records"), count

    def get_customer_clue(self, enterprise_ids, **kwargs):
        page = kwargs.get("page")
        page_size = kwargs.get("page_size")
        limit = base_db.paginator(page, page_size)
        order_map = {
            "created_time": "a.created_time",
            "updated_time": "a.updated_time",
            "status": "a.status",
            "level": "a.level",
            "id": "a.id",
            "ele_user_count": "a.ele_user_count",
            "name": "a.name",
            "address": "a.address",
        }
        order_by = base_db.order_by(kwargs.get("_sort"), map=order_map, default="a.id")

        where = ["a.enterprise_id in (%s)" % ','.join(enterprise_ids)]
        where.extend(base_db.filter(kwargs.get("keyword"), value='concat(a.name,a.address) LIKE "%%{}%%"'))
        where.extend(base_db.filter(kwargs.get("level"), value='a.level={}'))
        where.extend(base_db.filter(kwargs.get("status"), value='a.status={}'))
        _sql = """
        select
            %s
        from crm_customer_clue a
        left join users b
        on a.salesman_id=b.id
        left join users c
        on a.inputer_id=c.id
        """
        select = """
            a.id,
            a.enterprise_id,
            a.name,
            a.address,
            a.level,
            a.ele_user_count,
            a.status,
            a.salesman_id,
            b.nickname salesman_name,
            a.inputer_id,
            c.nickname inputer_name,
            a.created_time,
            a.updated_time
        """
        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+" desc")
        df = base_db.query_df(sql)
        return df.to_dict(orient="records"), count

    def get_customer_clue_info(self, enterprise_ids, user_id, clue_id, **kwargs):
        page = kwargs.get("page")
        page_size = kwargs.get("page_size")
        limit = base_db.paginator(page, page_size)
        order_map = {
            "created_time": "a.created_time",
            "updated_time": "a.updated_time",
            "status": "a.status",
            "level": "a.level",
            "id": "a.id",
            "ele_user_count": "a.ele_user_count",
            "name": "a.name",
            "address": "a.address",
        }
        order_by = base_db.order_by(kwargs.get("_sort"), map=order_map, default="a.id desc")

        where = ["a.enterprise_id in (%s) and a.id=%s and a.salesman_id=%s" % (','.join(enterprise_ids), str(clue_id), str(user_id))]
        where.extend(base_db.filter(kwargs.get("keyword"), value='concat(a.name,a.address) LIKE "%%{}%%"'))
        where.extend(base_db.filter(kwargs.get("level"), value='a.level={}'))
        where.extend(base_db.filter(kwargs.get("status"), value='a.status={}'))
        where.extend(base_db.filter(kwargs.get("user_id"), value='a.user_id={}'))

        _sql = """
        select
            %s
        from crm_customer_clue a
        left join users b
        on a.salesman_id=b.id
        left join users c
        on a.inputer_id=c.id
        """
        select = """
            a.id,
            a.customer_id,
            a.crm_customer_id,
            a.name,
            a.enterprise_id,
            a.address,
            a.level,
            a.ele_user_count,
            a.status,
            a.salesman_id,
            b.nickname salesman_name,
            a.inputer_id,
            c.nickname inputer_name,
            a.created_time,
            a.updated_time,
            a.content,
            a.longitude,
            a.latitude,
            a.adcode,
            a.city_code,
            a.district
        """
        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)
        df["content"] = df["content"].apply(lambda x: json.loads(x) if x else {})
        rst = df.to_dict(orient="records")
        return rst[0] if rst else None

    def get_customer_portrait(self, enterprise_id, **kwargs):
        where = ["b.service_enterprise_id=%s" % enterprise_id]
        where.extend(base_db.filter(kwargs.get("adcode"), value='c.adcode={}'))
        where.extend(base_db.filter(kwargs.get("industry"), value='c.industry={}'))
        where.extend(base_db.filter(kwargs.get("scale"), value='c.scale={}'))
        where.extend(base_db.filter(kwargs.get("asset"), value='c.asset={}'))
        group_by = kwargs.get("_group")
        value = kwargs.get("_value")
        if not value or not group_by:
            return None
        _map = {
            "adcode": "c.adcode",
            "industry": "c.industry",
            "scale": "c.scale",
            "asset": "c.asset",
        }
        group_by = ",".join([_map[i] for i in group_by])
        _sql = """
        select 
            %s
        from crm_customer a
        left join customer b
        on a.customer_id=b.id
        left join enterprise c
        on b.enterprise_id=c.id
        """
        select = """
        count(1) count,
        {}
        """.format(value[0])
        sql, _ = base_db.sql_splice(_sql,
                                    select=select,
                                    where=where,
                                    group_by=group_by)
        return base_db.query(sql)

    def create_customer_clue(self, payload):
        if CustomerClue.objects.filter(name=payload["name"]).exists():
            raise ValidationError("客户名称已存在")
        return CustomerClue.objects.create(**payload)

    def update_customer_clue(self, payload):
        id = payload.pop("id")
        has_clue = CustomerClue.objects.filter(name=payload["name"]).first()
        if has_clue:
            if has_clue.id != id:
                raise ValidationError("客户名称已存在")
        return CustomerClue.objects.filter(id=id).update(**payload)

    def del_customer_clue(self, enterprise_ids, user_id, clue_id):
        clues = CustomerClue.objects.filter(enterprise_id__in=enterprise_ids, salesman_id=user_id, id=clue_id)
        if not clues:
            raise ValidationError("客户线索不存在")
        clues.delete()

    def approve_customer_clue(self, enterprise_ids, clue_id):
        ...

    def create_enterprise(self, **payload):
        payload["enterprise_id"] = make_uuid()
        return Enterprise.objects.create(**payload)

    def create_customer(self, **payload):
        payload["code"] = name_maker("cst", 12)
        payload["level"] = 0
        if payload["last_service_staff_id"]:
            payload["level"] = 10
        return Customer.objects.create(**payload)

    def create_crm_customer(self, **payload):
        return CrmCustomer.objects.create(**payload)

    def create_contact(self, payload):
        contact_list = []
        if payload:
            for dt in payload:
                contact_list.append(Contact(**dt))
            Contact.objects.bulk_create(contact_list)

    def create_ele_user(self, payload):
        ele_user_list = []
        if payload:
            for dt in payload:
                dt.pop("circuit_imgs", None)
                dt.pop("station_imgs", None)
                dt.pop("bill_imgs", None)
                dt.pop("ele_info", None)
                dt.pop("streetscape_img", None)
                dt["ad_code"] = dt.pop("adcode", None)
                dt["addr"] = dt.pop("address", None)
                ele_user_list.append(ElectricityUser(**dt))
            ElectricityUser.objects.bulk_create(ele_user_list)

    def distribute_customer(self, customer_id, salesman_id):
        crm_customer = CrmCustomer.objects.filter(customer_id=customer_id).first()
        if crm_customer:
            if crm_customer.customer.service_staff_id != salesman_id:
                crm_customer.customer.last_service_staff_id = crm_customer.customer.service_staff_id
                crm_customer.customer.service_staff_id = salesman_id
                crm_customer.salesman_id = salesman_id
                crm_customer.customer.save()
                crm_customer.save()

    def get_customer_remarks(self, customer_id):
        sql = """
        select
            b.content,
            b.user_id,
            c.nickname username,
            b.created_time
        from crm_customer a
        left join crm_customer_remark b
        on a.id=b.crm_customer_id
        left join users c
        on c.id=b.user_id
        where a.customer_id=%s
        """ % customer_id
        return base_db.query(sql)


crm_customer_repo = CrmCustomerRepo()
