from django.db import models
from django.utils.translation import gettext_lazy as _
from django.contrib.postgres.fields import ArrayField

# Create your models here.


class Category(models.Model):
    company = models.ForeignKey('company.Company', verbose_name=_(
        '公司'), on_delete=models.CASCADE, related_name='banquet_company')
    name = models.CharField(_('分类名称'), max_length=100)
    remark = models.CharField(_('备注说明'), max_length=100, blank=True, null=True)
    tm = models.DateTimeField(_('生成时间'), auto_now_add=True)

    class Meta:
        verbose_name = _('宴会分类')
        verbose_name_plural = _('宴会分类')




# 2024-06-26
class Policy(models.Model):
    company = models.ForeignKey('company.Company', verbose_name=_('公司'), on_delete=models.CASCADE, related_name='policy_company')
    name = models.CharField(_('政策名称'), max_length=100)          
    bonus_type=models.SmallIntegerField(_('奖励类型'),choices=(
            (1,_('现金')),
            #(2,_('积分')),
        ),default=1)
    package_basis=models.SmallIntegerField(_('扫码记数'),choices=(
            (3,_('扫码箱数')),
            (1,_('扫码瓶数')),
        ),default=3)    
    location_constrain=models.SmallIntegerField(_('扫码销售区域限制'),choices=(
            (1,_('限制')),
            (0,_('不限制')),
        ),default=1)
    dt_start=models.DateField(_('开始日期'),blank=True,null=True)
    dt_end=models.DateField(_('结束日期'),blank=True,null=True)
    products=models.JSONField(_('产品限定'),default=list) 

    # 2024-07-05
    criteria=models.SmallIntegerField(_('等级原则'),choices=(
        (1,_('以扫码箱数为准')),
        (2,_('以宴席桌数为准')),
        (3,_('严格的箱与桌公式设定')),
        (4,_('箱数为主要条件，桌数为次要条件')),
        (5,_('桌数为主要条件，箱数为次要条件'))),default=3)
    
    grades=models.JSONField(_('奖励等级'),default=list,blank=True,null=True)
    # [{
    #    'qty':'equivlence',
    #    'products':{'id':'0','name':'','value':0}}
    # }]  
    
    # 2024-07-04
    bonus_grades=models.JSONField(_('奖励等级'),default=list,blank=True,null=True) 

    depend_on_product=models.SmallIntegerField(_('奖励区分产品'),choices=(
            (0,_('不区分产品')),
            (1,_('区分产品'))
        ),default=0)
    sql=models.CharField(_('条件语句'),max_length=5000,blank=True,null=True)
    sql1=models.CharField(_('条件语句test'),max_length=5000,blank=True,null=True)


    dt = models.DateField(_('生成日期'),auto_now_add=True,  blank=True, null=True, db_index=True)
    tm = models.DateTimeField(_('生成时间'), auto_now_add=True)
    remark = models.CharField(_('备注说明'), max_length=100, blank=True, null=True)
    consumer_scan_strategy = models.JSONField(_('消费者扫码策略'), default=list, blank=True, null=True)

    class Meta:
        verbose_name = _('奖励政策')
        verbose_name_plural = _('奖励政策')
        unique_together=('company','name')

    # 2024-06-27
    def save(self, *args, **kwargs):
        # 根据扫码(x:箱，p:瓶)数，宴席桌数，产品(product_id),计算奖励
        s,sVal=('','')
        if self.bonus_grades:
            if self.package_basis==3:                   # 扫码箱数
                U="X"
            elif self.package_basis==1:                 # 扫码瓶数
                U="P"                             
            for g in self.bonus_grades: 
                if self.depend_on_product:   
                    # 同一等级根据产品确定具体奖励                               
                    sVal=""
                    for v in g["vals"]:
                        sVal=sVal+" when product_id="+v["product_id"]+" then "+str(v["value"])
                    # 不是指定产品的，返回0
                    sVal='case '+sVal+" else 0 end"
                else:
                    sVal=str(g['vals'][0]['value'])
                exp_x=g['exp_x'].replace('＜','<').replace('≤','<=').replace('＞','>').replace('≥','>=')
                # 确定表单保存时，一定会有键名 exp_z 
                exp_z=g['exp_z'].replace('＜','<').replace('≤','<=').replace('＞','>').replace('≥','>=')                
                if self.criteria==1:
                    # 以扫码数量为准
                    s=s+ " when  "+exp_x+" then "+sVal 
                elif self.criteria==2:
                    # 以宴席桌数为准
                    s=s+ " when  "+exp_z+" then "+sVal 
                elif self.criteria==3:
                    # 严格的扫码数与桌公式设定
                    s=s+ " when  ("+exp_x+ ") and ("+exp_z+") then "+sVal
                elif self.criteria==4:
                    # 扫码数为主要条件，桌数为次要条件
                    aa=[999999]
                    for z in g['exp_z'].split('and'):
                        a=z.replace('Z','999999').replace('＜',',').replace('≤',',').replace('＞',',').replace('≥',',').split(',')
                        for i in a:
                            if i.strip().isdigit():
                                aa.append(int(i.strip())) 
                    s=s+ " when ("+exp_x+") and (Z>="+str(min(aa))+") then "+sVal
                elif self.criteria==5:
                    # 桌数为主要条件，扫码数为次要条件
                    aa=[999999]
                    for x in g['exp_x'].split('and'):
                        a=x.replace(U,'999999').replace('＜',',').replace('≤',',').replace('＞',',').replace('≥',',').split(',')
                        for i in a:                            
                            if i.strip().isdigit():
                                aa.append(int(i.strip()))                        
                    s=s+ " when ("+exp_z+") and ("+U+">="+str(min(aa))+") then "+sVal
            # 不在指定等级里的，返回0
            self.sql="case "+s+" else 0 end"
        super(Policy, self).save(*args, **kwargs)




class Apply(models.Model):
    # 2024-06-27 原模型已有一个字符串类型的policy字段，外键名改作strategy
    strategy=models.ForeignKey(Policy,verbose_name=_('奖励政策'),related_name='apply_policy',on_delete=models.CASCADE, blank=True,null=True)

    ancestor = models.ForeignKey('company.Company', verbose_name=_(
        '体系名称'), on_delete=models.CASCADE, related_name='apply_ancestor')
    company = models.ForeignKey('company.Company', verbose_name=_(
        '申请公司'), on_delete=models.DO_NOTHING)
    applyer = models.ForeignKey('company.User', verbose_name=_(
        '申请人'), on_delete=models.DO_NOTHING)
    category = models.ForeignKey(Category, verbose_name=_(
        '宴会类别'), on_delete=models.DO_NOTHING) 
    award = models.ForeignKey('sales.Award', verbose_name=_(
        '奖品'), on_delete=models.SET_NULL, blank=True, null=True)
    product = models.ForeignKey('product.Product', verbose_name=_(
        '产品'), on_delete=models.SET_NULL, blank=True, null=True)
    policy = models.CharField(      
        _('宴会政策'), max_length=300, blank=True, null=True)
    address = models.CharField(_('宴会地址'), max_length=500)
    lng_lat = ArrayField(models.DecimalField(
        _('经纬度'), max_digits=12, decimal_places=8), blank=True, null=True)
    table_num = models.SmallIntegerField(_('宴席桌数'))
    user_name = models.CharField(_('宴会人名称'), max_length=20)
    user_phone = models.CharField(_('宴会人手机'), max_length=20)
    use_tm = models.DateTimeField(_('宴会时间'))
    tm = models.DateTimeField(_('申请时间'), auto_now_add=True)
    businessman = models.ForeignKey('company.User', verbose_name=_(
        '业务员'), blank=True, null=True, on_delete=models.DO_NOTHING, related_name='apply_businessman')
    permit_tm = models.DateTimeField(_('审核时间'), blank=True, null=True)
    permiter = models.ForeignKey('company.User', verbose_name=_(
        '审核人'), on_delete=models.DO_NOTHING, related_name='apply_permiter', blank=True, null=True)
    finish_tm = models.DateTimeField(_('完成时间'), blank=True, null=True)
    status = models.SmallIntegerField(_('状态'), choices=(
        (0, _('未审核')),
        (1, _('已通过')),
        (2, _('未通过')), 
        (3, _('结算审核中')),
        (4, _('结算中')),
        (5, _('已结算')),
        (6, _('开瓶数量不足，未通过结算奖励')),
        (7, _('宴席资料异常')),
    ), default=0)
    reason = models.CharField(_('原因'), max_length=500, blank=True, null=True)
    annotation = models.CharField(_('批注'), max_length=500, blank=True, null=True)
    # 冗余，为提高查询速度
    channelpromotions = models.ForeignKey('sales.ChannelPromotions', verbose_name=_('所属渠道返利'),
                                          on_delete=models.CASCADE, related_name='apply_activity',
                                          blank=True, null=True)
    # 2024-06-28
    is_closed=models.BooleanField(_('是否已关闭'),default=False,blank=True,null=True,db_index=True)
    tm_closed=models.DateTimeField(_('关闭时间'), blank=True, null=True)
    closed_by=models.ForeignKey('company.User',verbose_name=_('关闭人'),related_name='apply_closed_by',on_delete=models.CASCADE,blank=True,null=True)
    end_tm = models.DateTimeField(_('宴会结束时间'), blank=True, null=True)
    actual_table_num = models.SmallIntegerField(_('实际宴席桌数'), blank=True, null=True, default=0)
    actual_qty_x=models.IntegerField(_('实际箱数'),default=0)
    actual_qty_p=models.IntegerField(_('实际瓶数'),default=0)
    scan_num=models.IntegerField(_('扫码数量'),default=0)
    bottle_num=models.IntegerField(_('瓶数'),default=0)
    is_ck=models.BooleanField(_('是否出库'),default=False,db_index=True)

    class Meta:
        verbose_name = _('宴会申请')
        verbose_name_plural = _('宴会申请')


class Apply_Codes(models.Model):
    apply = models.ForeignKey(Apply, verbose_name=_(
        '宴会申请记录'),  on_delete=models.CASCADE)
    code = models.CharField(_('标签序号'), max_length=20)
    product = models.ForeignKey('product.Product', verbose_name=_(
        '产品名称'), on_delete=models.SET_NULL, blank=True, null=True)
    package_id = models.SmallIntegerField(_('包装单位ID'), default=1)
    label_id = models.BigIntegerField(_('标签ID'), db_index=True,null=True,blank=True)
    label_pk_id = models.BigIntegerField(_('标签PKID'), db_index=True, null=True,blank=True)
    dt = models.DateField(_('生成日期'),auto_now_add=True,  blank=True, null=True, db_index=True)

    class Meta:
        verbose_name = _('宴会申请标签序号')
        verbose_name_plural = _('宴会申请标签序号')


class Apply_Pictures(models.Model):
    apply = models.ForeignKey(Apply, verbose_name=_(
        '宴会申请记录'),  on_delete=models.CASCADE)
    photo = models.CharField(_('标签序号'), max_length=300)

    class Meta:
        verbose_name = _('宴会申请图片')
        verbose_name_plural = _('宴会申请图片')




# 2024-06-27 宴会（消费者）扫码记录/申请人扫码核销
class Writeoff(models.Model):
    policy=models.ForeignKey(Policy,verbose_name=_('奖励政策'),related_name='writeoff_policy',on_delete=models.CASCADE,blank=True,null=True)
    apply = models.ForeignKey(Apply,verbose_name=_('宴会申请'),related_name='writeoff_apply', on_delete=models.CASCADE,blank=True,null=True)
    ancestor = models.ForeignKey('company.Company', verbose_name=_('所属企业'), related_name='writeoff_ancestor',on_delete=models.CASCADE)
    agent=models.ForeignKey('company.Company', verbose_name=_('经销商'),related_name='writeoff_agent',on_delete=models.CASCADE)
    user=models.ForeignKey('company.User',verbose_name=_('扫码人'),related_name='writeoff_user',on_delete=models.CASCADE)
    product=models.ForeignKey('product.Product',verbose_name=_('产品名称'),related_name='writeoff_product',on_delete=models.CASCADE)
    lng_lat = ArrayField(models.DecimalField(_('经纬度'), max_digits=12, decimal_places=8), blank=True, null=True)
    province=models.CharField(_('省份'),max_length=100,blank=True,null=True)
    city=models.CharField(_('城市'),max_length=100,blank=True,null=True)    
    label_id = models.BigIntegerField(_('标签ID'), db_index=True)
    label_pk_id = models.BigIntegerField(_('标签PKID'), db_index=True)
    label_code = models.CharField(_('标签号码'), max_length=20, db_index=True)
    label_pkg = models.SmallIntegerField(_('标签类型'), db_index=True) 
    dt = models.DateField(_('扫码日期'),auto_now_add=True, db_index=True)
    tm = models.DateTimeField(_('扫码时间'), auto_now_add=True)
    remark = models.CharField(_('备注说明'), max_length=100, blank=True, null=True)    

    class Meta:
        verbose_name = _('扫码核销记录')
        verbose_name_plural = _('扫码核销记录')


# 2024-06-27 结算后，宴会申请剩余的实物明细
class Surplus(models.Model):
    ancestor = models.ForeignKey('company.Company',verbose_name=_('所属企业'), related_name='surplus_ancestor',on_delete=models.CASCADE)
    agent=models.ForeignKey('company.Company', verbose_name=_('经销商'),related_name='surplus_agent',on_delete=models.CASCADE)
    policy=models.ForeignKey(Policy,verbose_name=_('奖励政策'),related_name='surplus_policy',on_delete=models.CASCADE,blank=True,null=True)
    apply = models.ForeignKey(Apply,verbose_name=_('宴会申请'),related_name='surplus_apply', on_delete=models.CASCADE,blank=True,null=True)    
    product=models.ForeignKey('product.Product',verbose_name=_('产品名称'),related_name='surplus_product',on_delete=models.CASCADE)
    qty_x=models.IntegerField(_('剩余箱数'),default=0)
    qty_p=models.IntegerField(_('剩余瓶数'),default=0)    
    dt = models.DateField(_('生成日期'),auto_now_add=True, db_index=True)
    tm = models.DateTimeField(_('生成时间'), auto_now_add=True)
    remark = models.CharField(_('备注说明'), max_length=100, blank=True, null=True)   

    class Meta:
        verbose_name = _('宴会结余')
        verbose_name_plural = _('宴会结余')



# 2024-06-27 结算后，经销商奖励
class Bonus(models.Model):
    ancestor = models.ForeignKey('company.Company',verbose_name=_('所属企业'), related_name='bonus_ancestor',on_delete=models.CASCADE)
    agent=models.ForeignKey('company.Company', verbose_name=_('经销商'),related_name='bonus_agent',on_delete=models.CASCADE)
    policy=models.ForeignKey(Policy,verbose_name=_('奖励政策'),related_name='bonus_policy',on_delete=models.CASCADE,blank=True,null=True)    
    apply=models.ForeignKey(Apply,verbose_name=_('宴会申请'),related_name='bonus_appley',on_delete=models.CASCADE,blank=True,null=True)
    product=models.ForeignKey('product.Product',verbose_name=_('产品名称'),related_name='bonus_product',on_delete=models.CASCADE)
    qty_x=models.IntegerField(_('箱数'),default=0)
    qty_p=models.IntegerField(_('瓶数'),default=0,blank=True,null=True)
    # 2024-07-04
    table_num = models.SmallIntegerField(_('宴席桌数'))
    amt=models.DecimalField(_('奖励值'), max_digits=12, decimal_places=2, default=0)
    dt = models.DateField(_('生成日期'),auto_now_add=True, db_index=True)
    tm = models.DateTimeField(_('生成时间'), auto_now_add=True)
    remark = models.CharField(_('备注说明'), max_length=100, blank=True, null=True)

    class Meta:
        verbose_name = _('奖励结算')
        verbose_name_plural = _('奖励结算')

class BonusRedeemed(models.Model):
    ancestor_id=models.IntegerField(_('族系'))
    company = models.ForeignKey('company.Company', verbose_name=_('公司名称'), on_delete=models.CASCADE)
    user = models.ForeignKey('company.User', verbose_name=_('操作人'), on_delete=models.CASCADE, blank=True, null=True)   
    val=models.DecimalField(_('返利总金额（点值）'), max_digits=12, decimal_places=2) 
    tm=models.DateTimeField(_('兑付时间'), auto_now_add=True)
    dt=models.DateField(_('兑付日期'), auto_now_add=True, db_index=True)
    remark=models.CharField(_('备注说明'),max_length=50,blank=True,null=True)
    
    # 2023-06-12 微信红包、网上银行等在线实时提现第三方回单信息
    online_receipt=models.JSONField(_('在线应兑付回单信息'),default=dict,blank=True,null=True)
    request=models.JSONField(_('兑付请求内容'),default=dict,blank=True,null=True)

    class Meta:
        verbose_name = _('奖励结算提现记录')
        verbose_name_plural = _('奖励结算提现记录')

class SettleRecord(models.Model):
    ancestor = models.ForeignKey('company.Company',verbose_name=_('所属企业'), on_delete=models.CASCADE)
    user = models.ForeignKey('company.User', verbose_name=_('操作人'), on_delete=models.CASCADE)
    apply = models.ForeignKey(Apply,verbose_name=_('宴会申请'), on_delete=models.CASCADE)
    amt = models.DecimalField(_('奖励值'), max_digits=12, decimal_places=2, default=0)
    tm = models.DateTimeField(_('生成时间'), auto_now_add=True)
    remark = models.CharField(_('备注说明'), max_length=100, blank=True, null=True)

    class Meta:
        verbose_name = _('手动结算记录')
        verbose_name_plural = _('手动结算记录')