import json
from MySQLdb import escape_string
from common.accounts.models import (Users, Roles, UserRoles, Tags, UserTags,
                                    UserOrganizations, UserEnterprise,
                                    RolePerms, Perms, Accounts, Enterprise)
from common.utils.exceptions import UserNotFound, ValidationError
from common.utils.models import BaseConnection
from common.utils.str_utils import name_maker
from crm.crm_users.models import CrmUsers

base_db = BaseConnection()


class UserQuerySet(object):
    @property
    def set_password(self):
        return Users.objects.filter(deleted=False, account__is_active=True)

    @property
    def users_lc(self):
        return Users.objects.filter(deleted=False, account__is_active=True)

    @property
    def users_c(self):
        return Users.objects.filter(deleted=False)

    @property
    def roles_lc(self):
        return Roles.objects.filter(deleted=False)

    @property
    def user_roles_lc(self):
        return UserRoles.objects.filter(deleted=False)

    @property
    def tags_lc(self):
        return Tags.objects.filter(deleted=False)

    @property
    def user_tags_lc(self):
        return UserTags.objects.filter(deleted=False)


class UserRepo(object):
    def get_account_by_name(self, username, account_id=None):
        if account_id:
            return Accounts.objects.filter(username=username).exclude(
                id=account_id)
        return Accounts.objects.filter(username=username)

    def get_user_by_email(self, email, user_id=None):
        if user_id:
            return Users.objects.filter(email=email).exclude(id=user_id)
        return Users.objects.filter(email=email)

    def get_user_by_phone(self, phone, user_id=None):
        if user_id:
            return Users.objects.filter(phone=phone).exclude(id=user_id)
        return Users.objects.filter(phone=phone)

    def create_user_roles(self, user_id, role_ids):
        user_roles = map(lambda x: UserRoles(user_id=user_id, role_id=x),
                         role_ids)
        return UserRoles.objects.bulk_create(user_roles)

    def get_user_roles(self, user_id):
        return UserRoles.objects.filter(user_id=user_id)

    def get_user_tags(self, user_id):
        return UserTags.objects.filter(user_id=user_id)

    def create_user_tags(self, user_id, tag_ids):
        user_tags = map(lambda x: UserTags(user_id=user_id, tag_id=x), tag_ids)
        return UserTags.objects.bulk_create(user_tags)

    def get_user_organizations(self, user_id):
        return UserOrganizations.objects.filter(user_id=user_id)

    def get_tag(self, tag_id):
        return Tags.objects.filter(id=tag_id).first()

    def get_tag_users(self, tag_id, enterprise_id):
        user_tags = UserTags.objects.filter(tag_id=tag_id)
        user_ids = user_tags.values_list("user_id", flat=True)
        if user_ids:
            users = Users.objects.filter(
                id__in=user_ids).prefetch_related("ent_user").filter(
                    ent_user__enterprise_id=enterprise_id)
            return users.values("id", "nickname")
        return []

    def create_user_organizations(self, user_id, organization_ids):
        user_organizations = map(
            lambda x: UserOrganizations(user_id=user_id, organization_id=x),
            organization_ids)
        return UserOrganizations.objects.bulk_create(user_organizations)

    def delete_user(self, user_id, enterprise_ids):
        user = Users.objects.filter(
            id=user_id, deleted=False,
            account__is_active=True).prefetch_related("ent_user").filter(
                ent_user__deleted=False,
                ent_user__enterprise_id__in=enterprise_ids).first()
        if user:
            user.account.is_active = False
            user.deleted = True
            user.save()
            user.account.save()
        else:
            raise UserNotFound

    def get_users_info(self, enterprise_id, **kwargs):
        page = kwargs.get("page")
        page_size = kwargs.get("page_size")
        where = ["A.service_enterprise_id=%s" % enterprise_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))

    def search_users(self, enterprise_id, organization_id, role_id, tag_id,
                     team_headman, service_staff, value, choices):
        where = [
            "A.deleted=0", "D.id in ({})".format(",".join(enterprise_id))
        ]
        order_by = "A.created_time DESC"
        if value:
            where.append(
                """CONCAT(ifnull(A.`nickname`,''), ifnull(A.`phone`,''),
                ifnull(A.`email`,'')) like "%{}%" """.format(
                    escape_string(value).decode()))
        if organization_id:
            where.append("A.id in (select user_id from user_organizations "
                         "where organization_id={})".format(
                             escape_string(organization_id).decode()))
        if role_id:
            where.append(
                "A.id in (select user_id from user_roles where role_id={})".
                format(escape_string(role_id).decode()))
        if tag_id:
            where.append(
                "A.id in (select user_id from user_tags where tag_id={})".
                format(escape_string(tag_id).decode()))
        if team_headman:
            where.append("A.id in (SELECT distinct team_headman_id FROM team where deleted=0)")
        if service_staff:
            where.append(
                "(A.id in (SELECT distinct service_staff_id as id FROM"
                " customer union SELECT distinct last_service_staff_id"
                " as id FROM customer) or E.tag_id in (2,11))")
        sql = """
        SELECT
            distinct
            A.id,
            A.nickname,
            A.wechat,
            A.phone,
            A.email,
            B.username,
            B.certification_status,
            B.account_type,
            A.account_id,
            A.created_time
        FROM users A
        LEFT JOIN accounts B
        ON B.id=A.account_id
        LEFT JOIN user_enterprise C
        ON A.id = C.user_id
        left join enterprise D on C.enterprise_id=D.enterprise_id
        LEFT JOIN user_tags E on E.user_id=A.id
        """
        if len(where) == 1:
            raise ValidationError("缺少搜索参数")
        sql = ' WHERE '.join([sql, ' and '.join(where)])
        if order_by:
            sql = ' ORDER BY '.join([sql, order_by])
        print(sql)
        rst = base_db.query(sql)
        if rst and not choices:
            user_ids = [str(i["id"]) for i in rst]
            # join roles
            sql = """
            SELECT A.user_id, B.id, B.name FROM user_roles A
            LEFT JOIN roles B
            ON A.role_id=B.id
            WHERE A.user_id in (%s)
            """ % ",".join(user_ids)
            roles = base_db.query(sql)
            # tags
            sql = """
            SELECT A.user_id, B.id, B.name FROM user_tags A
            LEFT JOIN tags B
            ON A.tag_id=B.id
            WHERE A.user_id in (%s)
            """ % ",".join(user_ids)
            tags = base_db.query(sql)
            # organizations
            sql = """
            SELECT A.user_id, B.id, B.name FROM user_organizations A
            LEFT JOIN organization B
            ON A.organization_id=B.id
            WHERE A.user_id in (%s)
            """ % ",".join(user_ids)
            organizations = base_db.query(sql)

            role_dt = {}
            tag_dt = {}
            org_dt = {}

            for role in roles:
                role_dt.setdefault(role["user_id"], list()).append({
                    "id":
                    role["id"],
                    "name":
                    role["name"]
                })

            for tag in tags:
                tag_dt.setdefault(tag["user_id"], list()).append({
                    "id":
                    tag["id"],
                    "name":
                    tag["name"]
                })

            for org in organizations:
                org_dt.setdefault(org["user_id"], list()).append({
                    "id":
                    org["id"],
                    "name":
                    org["name"]
                })

            for i in rst:
                i["account"] = {
                    "username": i["username"],
                    "certification_status": i["certification_status"],
                    "account_type": i["account_type"],
                    "account_id": i["account_id"]
                }
                i["roles"] = role_dt.get(i["id"], [])
                i["tags"] = tag_dt.get(i["id"], [])
                i["organizations"] = org_dt.get(i["id"], [])
                del i["username"]
                del i["certification_status"]
                del i["account_type"]
                del i["account_id"]
                # add team info
                sql = """
                            select
                             a.name,a.id,a.type
                            from
                             team a
                            left join
                             team_member b
                            on a.id=b.team_id
                            where (a.team_headman_id={} or b.users_id= {})
                             and a.deleted=FALSE
                            """.format(i.get("id"), i.get("id"))
                teams = base_db.query(sql)
                i["team"] = teams
        return rst

    def create_user_enterprises(self, user_id, enterprise_id):
        return UserEnterprise.objects.create(user_id=user_id,
                                             enterprise_id=enterprise_id)

    def create_multi_user_enterprises(self, user_ids, enterprise_id):
        UserEnterprise.objects.filter(enterprise_id=enterprise_id,
                                      user_id__in=user_ids).delete()
        users_ent = []
        for user_id in user_ids:
            users_ent.append(
                UserEnterprise(user_id=user_id, enterprise_id=enterprise_id))

        return UserEnterprise.objects.bulk_create(users_ent)

    def delete_user_enterprises(self, user_id, enterprise_ids):
        user_enterprises = UserEnterprise.objects.filter(user_id=user_id)
        if user_enterprises.count() > 1:
            user_enterprises.filter(enterprise_id__in=enterprise_ids).delete()
            return True
        return False

    def disabled_account(self, user_id):
        user = Users.objects.filter(id=user_id).first()
        user.account.is_active = False
        user.account.save()

    def enabled_account(self, user_id):
        user = Users.objects.filter(id=user_id)
        user.account.is_active = True
        user.account.save()

    def enabled_accounts(self, user_ids):
        users = Users.objects.filter(id__in=user_ids)
        account_ids = users.values_list("account_id", flat=True)
        Accounts.objects.filter(id__in=account_ids).update(is_active=True)

    def clean_orphan_accounts(self):
        users = Users.objects.filter(account__is_active=False,
                                     account__account_type="customer",
                                     deleted=False)
        users.delete()
        account_ids = users.values_list("account_id", flat=True)
        Accounts.objects.filter(id__in=account_ids).delete()

    def get_role_perms(self, role_id):
        return RolePerms.objects.filter(
            role_id=role_id).prefetch_related("role_perms")

    def get_union_roles_perms(self, role_ids):
        return RolePerms.objects.filter(
            role_id__in=role_ids).prefetch_related("role_perms")

    def get_perms_by_codes(self, code_list):
        if code_list:
            return Perms.objects.filter(code__in=code_list)
        return []

    def create_role_perms(self, role_id, perms_codes):
        perms = self.get_perms_by_codes(perms_codes)
        RolePerms.objects.filter(role_id=role_id).delete()
        perm_list = []
        if perms:
            for perm in perms:
                perm_list.append(RolePerms(perms=perm, role_id=role_id))
            RolePerms.objects.bulk_create(perm_list)
            return perms.values_list("code", flat=True)
        return perms

    def delete_role_perms(self, role_id):
        RolePerms.objects.filter(role_id=role_id).delete()

    def get_enterprise(self, id):
        return Enterprise.objects.filter(id=id).first()


class UserSqlRepo():
    def get_user(self, enterprise_id, **kwargs):
        page = kwargs.get("page")
        page_size = kwargs.get("page_size")
        limit = base_db.paginator(page, page_size)
        order_by = base_db.order_by(kwargs.get("_sort"), default="a.id desc")
        where = ['e.enterprise_id="%s"' % str(enterprise_id)]
        where.extend(base_db.filter(kwargs.get("keyword"), value='concat(ifnull(a.nickname,""),ifnull(a.email,""),'
                                                                 'ifnull(a.phone,""),'
                                                                 'ifnull(b.username,"")) LIKE "%%{}%%"'))
        where.extend(base_db.filter(kwargs.get("role_id"), value='h.role_id={}'))
        where.extend(base_db.filter(kwargs.get("organization_id"), value='f.organization_id={}'))
        _sql = """
        select
            %s
        from users a
        left join accounts b
        on a.account_id=b.id
        left join user_tags c
        on c.user_id=a.id
        left join tags d
        on d.id=c.tag_id
        left join user_enterprise e
        on a.id=e.user_id
        left join user_organizations f
        on a.id=f.user_id
        left join organization g
        on g.id=f.organization_id
        left join user_roles h
        on a.id=h.user_id
        left join roles i
        on h.role_id=i.id and i.kind='enterprise' and i.kind_id=e.enterprise_id
        """
        select = """
            a.id,
            a.nickname,
            a.wechat,
            a.phone,
            a.email,
            b.certification_status,
            b.username,
            a.account_id,
            b.account_type,
            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
        """
        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 []))
        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 []))
        return df.to_dict(orient="records"), count

    def get_user_info(self, enterprise_id, user_id):
        where = ['e.enterprise_id="%s" and a.id="%s"' % (str(enterprise_id), str(user_id))]
        _sql = """
        select
            %s
        from users a
        left join accounts b
        on a.account_id=b.id
        left join user_tags c
        on c.user_id=a.id
        left join tags d
        on d.id=c.tag_id
        left join user_enterprise e
        on a.id=e.user_id
        left join user_organizations f
        on a.id=f.user_id
        left join organization g
        on g.id=f.organization_id
        left join user_roles h
        on a.id=h.user_id
        left join roles i
        on h.role_id=i.id and i.kind='enterprise' and i.kind_id=e.enterprise_id
        """
        select = """
            a.id,
            a.nickname,
            a.wechat,
            a.phone,
            a.email,
            a.code,
            a.gender,
            a.cert,
            a.cert_number,
            a.order_rec_setting,
            a.order_rec_status,
            a.is_manager,
            a.status,
            b.certification_status,
            b.username,
            a.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 get_user_center_info(self, enterprise_id, user_id):
        where = ['e.enterprise_id="%s" and a.id="%s"' % (str(enterprise_id), str(user_id))]
        _sql = """
        select
            %s
        from users a
        left join accounts b
        on a.account_id=b.id
        left join user_enterprise e
        on a.id=e.user_id
        """
        select = """
            a.id,
            a.nickname,
            a.wechat,
            a.phone,
            a.email,
            a.code,
            a.gender,
            a.is_manager,
            b.username,
            a.account_id,
            b.account_type,
            b.head_img
        """
        sql, _ = base_db.sql_splice(_sql,
                                    select,
                                    where=where,
                                    limit_idx="a.id",
                                    group_by="a.id")
        df = base_db.query_df(sql)
        rst = df.to_dict(orient="records")
        if rst:
            return rst[0]
        return None

    def create_user(self, enterprise, login_user, payload):
        account = {
            "username": payload.pop("username"),
            "password": payload.pop("password"),
            "account_type": login_user.account.account_type,
            "head_img": payload.pop("head_img"),
        }
        type = payload.pop("type", None)
        role_ids = payload.pop("role_ids")
        tag_ids = payload.pop("tag_ids")
        join_date = payload.pop("join_date")
        organization_ids = payload.pop("organization_ids")
        if Accounts.objects.filter(username=account["username"]).first():
            raise ValidationError(msg_show="账号名重复")
        account = Accounts.objects.create_user(**account)
        payload["account_id"] = account.id
        payload["code"] = name_maker("U", num=8)
        user = Users.objects.create(**payload)
        self.create_user_enterprises(user.id, enterprise.enterprise_id, join_date)
        self.create_user_tags(user.id, tag_ids)
        self.create_user_roles(user.id, role_ids)
        self.create_user_organizations(user.id, organization_ids)
        if type == "crm":
            CrmUsers.objects.create(enterprise_id=enterprise.id, user_id=user.id)
        return user

    def create_user_enterprises(self, user_id, enterprise_id, join_date):
        return UserEnterprise.objects.create(user_id=user_id,
                                             enterprise_id=enterprise_id,
                                             join_date=join_date)

    def create_user_tags(self, user_id, tag_ids):
        if tag_ids:
            user_tags = map(lambda x: UserTags(user_id=user_id, tag_id=x), tag_ids)
            UserTags.objects.bulk_create(user_tags)

    def create_user_roles(self, user_id, role_ids):
        if role_ids:
            user_roles = map(lambda x: UserRoles(user_id=user_id, role_id=x),
                             role_ids)
            UserRoles.objects.bulk_create(user_roles)

    def create_user_organizations(self, user_id, organization_ids):
        if organization_ids:
            user_organizations = map(
                lambda x: UserOrganizations(user_id=user_id, organization_id=x),
                organization_ids)
            UserOrganizations.objects.bulk_create(user_organizations)

    def update_user(self, enterprise, user_id, payload):
        user = Users.objects.filter(id=user_id).first()
        if not user:
            raise ValidationError(msg_show="用户不存在")
        account = {
            "username": payload.pop("username"),
            "head_img": payload.pop("head_img"),
        }
        role_ids = payload.pop("role_ids", None)
        tag_ids = payload.pop("tag_ids", None)
        join_date = payload.pop("join_date", None)
        organization_ids = payload.pop("organization_ids", None)
        if Accounts.objects.exclude(id=user.account_id).filter(username=account["username"]):
            raise ValidationError(msg_show="账号名重复")
        Accounts.objects.filter(id=user.account_id).update(**account)
        user.__dict__.update(**payload)
        user.save()

        user_ent = UserEnterprise.objects.filter(user_id=user.id, enterprise_id=enterprise.enterprise_id).first()
        if user_ent.join_date != join_date:
            user_ent.join_date = join_date
            user_ent.save()
        self.update_user_tags(user.id, tag_ids)
        self.update_user_roles(enterprise.id, user.id, role_ids)
        self.update_user_organizations(enterprise.id, user.id, organization_ids)

    def update_user_tags(self, user_id, tag_ids):
        if tag_ids:
            sql = """
            select
                a.tag_id
            from user_tags a
            left join tags b
            on a.tag_id=b.id
            where a.user_id="%s"
            """ % (user_id)
            rst = base_db.query(sql)
            has_ids = [i["tag_id"] for i in rst]
            del_ids = list(set(has_ids) & (set(has_ids) ^ set(tag_ids)))
            add_ids = list(set(has_ids) ^ set(tag_ids) ^ set(del_ids))
            if del_ids:
                UserTags.objects.filter(user_id=user_id, tag_id__in=del_ids).delete()
            if add_ids:
                user_tags = map(lambda x: UserTags(user_id=user_id, tag_id=x), add_ids)
                UserTags.objects.bulk_create(user_tags)
        else:
            if tag_ids is None:
                return
            sql = """
            select
                a.tag_id
            from user_tags a
            left join tags b
            on a.tag_id=b.id
            where a.user_id="%s"
            """ % (user_id)
            rst = base_db.query(sql)
            has_ids = [i["tag_id"] for i in rst]
            UserTags.objects.filter(user_id=user_id, tag_id__in=has_ids).delete()

    def update_user_roles(self, enterprise_id, user_id, role_ids):
        if role_ids:
            sql = """
            select
                a.role_id
            from user_roles a
            left join roles b
            on a.role_id=b.id
            where a.user_id="%s" and b.kind_id="%s" and b.kind="enterprise"
            """ % (user_id, enterprise_id)
            rst = base_db.query(sql)
            has_ids = [i["role_id"] for i in rst]
            del_ids = list(set(has_ids) & (set(has_ids) ^ set(role_ids)))
            add_ids = list(set(has_ids) ^ set(role_ids) ^ set(del_ids))
            if del_ids:
                UserRoles.objects.filter(user_id=user_id, role_id__in=del_ids).delete()
            if add_ids:
                user_roles = map(lambda x: UserRoles(user_id=user_id, role_id=x), add_ids)
                UserRoles.objects.bulk_create(user_roles)
        else:
            if role_ids is None:
                return
            sql = """
            select
                a.role_id
            from user_roles a
            left join roles b
            on a.role_id=b.id
            where a.user_id="%s" and b.kind_id="%s" and b.kind="enterprise"
            """ % (user_id, enterprise_id)
            rst = base_db.query(sql)
            has_ids = [i["role_id"] for i in rst]
            UserRoles.objects.filter(user_id=user_id, role_id__in=has_ids).delete()
    def update_user_organizations(self, enterprise_id, user_id, organization_ids):
        if organization_ids:
            sql = """
            select
                a.organization_id
            from user_organizations a
            left join organization b
            on a.organization_id=b.id
            where a.user_id="%s" and b.enterprise_id="%s"
            """ % (user_id, enterprise_id)
            rst = base_db.query(sql)
            has_ids = [i["organization_id"] for i in rst]
            del_ids = list(set(has_ids) & (set(has_ids) ^ set(organization_ids)))
            add_ids = list(set(has_ids) ^ set(organization_ids) ^ set(del_ids))
            if del_ids:
                UserOrganizations.objects.filter(user_id=user_id, organization_id__in=del_ids).delete()
            if add_ids:
                user_organizations = map(
                    lambda x: UserOrganizations(user_id=user_id, organization_id=x), add_ids)
                UserOrganizations.objects.bulk_create(user_organizations)
        else:
            if organization_ids is None:
                return
            sql = """
            select
                a.organization_id
            from user_organizations a
            left join organization b
            on a.organization_id=b.id
            where a.user_id="%s" and b.enterprise_id="%s"
            """ % (user_id, enterprise_id)
            rst = base_db.query(sql)
            has_ids = [i["organization_id"] for i in rst]
            UserOrganizations.objects.filter(user_id=user_id, organization_id__in=has_ids).delete()

    def set_password(self, user_id, password):
        user = Users.objects.filter(id=user_id).first()
        if user and user.account:
            user.account.set_password(password)
            user.account.save()

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

user_queryset = UserQuerySet()
user_repo = UserRepo()
user_sql_repo = UserSqlRepo()
