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
from crm.crm_users.models import CrmUsers

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

class CrmUserRepo(object):
    def get_users(self, enterprise_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",
            "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 desc")
        where = ['a.enterprise_id="%s"' % str(enterprise_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_users a
        left join users aa
        on a.user_id=aa.id
        left join accounts b
        on aa.account_id=b.id
        left join user_tags c
        on c.user_id=aa.id
        left join tags d
        on d.id=c.tag_id
        left join user_enterprise e
        on aa.id=e.user_id
        """
        select = """
            aa.id,
            aa.nickname,
            aa.wechat,
            aa.phone,
            aa.email,
            b.certification_status,
            b.username,
            aa.account_id,
            b.account_type,
            ifnull(concat("[",
                group_concat(
                distinct
                '{',
                concat('"tag_id":',d.id),
                concat(',"name":"', d.name,'"'),
                '}'
                ),
                "]"
            ),"[]") tags,
            e.join_date
        """
        count_select = ' count(a.id) as `count` '
        count_sql, _ = base_db.sql_splice(_sql, count_select, where=where, order_by=order_by, group_by="a.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="a.id")
        df = base_db.query_df(sql)
        df["tags"] = df['tags'].apply(lambda x: (json.loads(x) if x else []))
        return df.to_dict(orient="records"), count

    def get_users_simple(self, enterprise_id, **kwargs):
        order_by = base_db.order_by(kwargs.get("_sort"), default="a.id desc")
        where = ['a.enterprise_id="%s"' % str(enterprise_id)]
        where.extend(base_db.filter(kwargs.get("keyword"), value='aa.nickname LIKE "%%{}%%"'))
        _sql = """
        select
            %s
        from crm_users a
        left join users aa
        on a.user_id=aa.id
        """
        select = """
            aa.id,
            aa.nickname
        """
        if len(where) == 1:
            page = kwargs.get("page", 1)
            page_size = kwargs.get("page_size", 20)
            limit = base_db.paginator(page, page_size)
            sql, count = base_db.sql_splice(_sql,
                                            select,
                                            limit=limit,
                                            limit_idx="a.id",
                                            where=where,
                                            order_by=order_by)
        else:
            sql, _ = base_db.sql_splice(_sql,
                                            select,
                                            where=where,
                                            order_by=order_by)
        df = base_db.query_df(sql)
        return df.to_dict(orient="records")

    def get_user_info(self, enterprise_id, user_id):
        where = ['a.enterprise_id="%s" and a.user_id="%s"' % (str(enterprise_id), str(user_id))]
        _sql = """
        select
            %s
        from crm_users a
        left join users aa
        on a.user_id=aa.id
        left join accounts b
        on aa.account_id=b.id
        left join user_tags c
        on c.user_id=aa.id
        left join tags d
        on d.id=c.tag_id
        left join user_enterprise e
        on aa.id=e.user_id
        left join enterprise ee
        on e.enterprise_id=ee.enterprise_id
        left join user_organizations f
        on aa.id=f.user_id
        left join organization g
        on g.id=f.organization_id
        left join user_roles h
        on aa.id=h.user_id
        left join roles i
        on h.role_id=i.id and i.kind='enterprise' and i.kind_id=ee.enterprise_id and ee.id=a.enterprise_id
        """
        select = """
            aa.id,
            aa.nickname,
            aa.wechat,
            aa.phone,
            aa.email,
            aa.code,
            aa.gender,
            aa.cert,
            aa.cert_number,
            aa.status,
            b.certification_status,
            b.username,
            aa.account_id,
            b.account_type,
            b.head_img,
            ifnull(concat("[",
                group_concat(
                distinct
                '{',
                concat('"tag_id":',d.id),
                concat(',"name":"', d.name,'"'),
                '}'
                ),
                "]"
            ),"[]") tags,
            ifnull(concat("[",
                group_concat(
                distinct
                '{',
                concat('"organization_id":',f.organization_id),
                concat(',"name":"', g.name,'"'),
                '}'
                ),
                "]"
            ),"[]") organization,
            ifnull(concat("[",
                group_concat(
                distinct
                '{',
                concat('"role_id":',h.role_id),
                concat(',"name":"', i.name,'"'),
                '}'
                ),
                "]"
            ),"[]") role,
            e.join_date
        """
        sql, _ = base_db.sql_splice(_sql,
                                        select,
                                        where=where,
                                        limit_idx="a.id",
                                        group_by="a.id")
        df = base_db.query_df(sql)
        df["tags"] = df['tags'].apply(lambda x: (json.loads(x) if x else []))
        df["organization"] = df['organization'].apply(lambda x: (json.loads(x) if x else []))
        df["role"] = df['role'].apply(lambda x: (json.loads(x) if x else []))
        rst = df.to_dict(orient="records")
        if rst:
            return rst[0]
        return None

    def delete_user(self, enterprise_id, user_id):
        CrmUsers.objects.filter(enterprise_id=enterprise_id, user_id=user_id).delete()

    def import_users(self, enterprise_id, user_list):
        if user_list:
            crm_user_l = []
            user_list = set(user_list)
            for i in user_list:
                crm_user_l.append(CrmUsers(enterprise_id=enterprise_id, user_id=i))
            CrmUsers.objects.bulk_create(crm_user_l)



crm_user_repo = CrmUserRepo()
