import ujson
import logging

from common.utils.exceptions import ValidationError
from common.utils.models import BaseConnection

from console.electricityuser.models import ElectricityUser

base_db = BaseConnection()
logger = logging.getLogger("")


class ElectricityUserQuerySet(object):
    @property
    def get_all_electricityuser(self):
        return ElectricityUser.objects.filter(deleted=False).order_by("-id")


class ElectricityUserRepo(object):
    def get_electricity_user(self, pk):
        queryset = ElectricityUser.objects.filter(deleted=False, id=pk)
        if not queryset.exists():
            raise ValidationError("没有查到该户号")
        return queryset.first()

    def create_electricity_users(self, data):
        return ElectricityUser.objects.bulk_create(data)


class ElectricityUserSqlRepo(object):
    def get_electricity_user(self, enterprise_ids, **kwargs):
        page = kwargs.get("page")
        page_size = kwargs.get("page_size")
        order_by = "A.id"
        where = [" A.deleted=false and B.service_enterprise_id in (%s)" % ','.join(enterprise_ids)]
        where.extend(
            base_db.filter(kwargs.get("customer"),
                           value='B.name like "%%{}%%"'))
        where.extend(
            base_db.filter(kwargs.get("postcode"), value='A.postcode="{}"'))
        where.extend(
            base_db.filter(kwargs.get("number"), value='A.number like "{}%%"'))
        where.extend(
            base_db.filter(kwargs.get("province"),
                           value='A.ad_code like "{}%%"',
                           func=lambda x: x.strip("0")))
        where.extend(
            base_db.filter(kwargs.get("city"), value='A.city_code="{}"'))
        where.extend(
            base_db.filter(kwargs.get("area"), value='A.addr like "{}%%"'))
        where.extend(
            base_db.filter(kwargs.get("keyword"),
                           value='concat(B.name,A.addr) like "%%{}%%"'))
        limit = base_db.paginator(page, page_size)
        _sql = """
        SELECT
            %s
        FROM
        (SELECT
            AA.id,
            AA.number,
            AA.addr,
            AA.type,
            AA.trusteeship_num,
            AA.created_time,
            AA.customer_id,
            AA.ad_code,
            AA.city_code,
            AA.deleted
        FROM electricity_user AA WHERE 1=1 ORDER BY AA.`id` DESC) A
        LEFT JOIN customer B
        ON A.customer_id=B.id
        LEFT JOIN users C
        ON B.service_staff_id=C.id
        """
        limit_sql = """
        SELECT
            %s
        FROM
        (SELECT
            AA.id,
        	AA.deleted,
            AA.customer_id
        FROM electricity_user AA ORDER BY AA.`id` DESC) A
        LEFT JOIN customer B
        ON A.customer_id=B.id
        """
        count_select = ' COUNT(A.id) as `count` '
        count_sql, _ = base_db.sql_splice(_sql, count_select, where=where)
        select = """
            A.id,
            A.number,
            A.addr,
            A.type,
            A.trusteeship_num,
            A.created_time,
            concat('{','"id":',B.id,",",'"name":"',
            replace(B.name,'"','\\\\"'),'"}') customer,
            C.nickname as service_staff
        """
        sql, count = base_db.sql_splice(_sql,
                                        select,
                                        where=where,
                                        count_sql=count_sql,
                                        limit=limit,
                                        limit_sql=limit_sql,
                                        limit_idx="A.id",
                                        order_by=order_by)
        df = base_db.query_df(sql)
        df["customer"] = df['customer'].apply(lambda x: (ujson.loads(x)
                                                         if x else []))
        return df.to_dict(orient="records"), count


electricityuser_queryset = ElectricityUserQuerySet()
electricityuser_repo = ElectricityUserRepo()
electricityuser_sql_repo = ElectricityUserSqlRepo()
