package dao

import (
	"fmt"

	"github.com/go-pg/pg/v10/orm"
	"gitlab.fjmaimaimai.com/mmm-go/partnermg/pkg/domain"
	"gitlab.fjmaimaimai.com/mmm-go/partnermg/pkg/infrastructure/pg/models"
	"gitlab.fjmaimaimai.com/mmm-go/partnermg/pkg/infrastructure/pg/transaction"
)

type OrderBaseDao struct {
	transactionContext *transaction.TransactionContext
}

func NewOrderBaseDao(transactionContext *transaction.TransactionContext) (*OrderBaseDao, error) {
	if transactionContext == nil {
		return nil, fmt.Errorf("transactionContext参数不能为nil")
	} else {
		return &OrderBaseDao{
			transactionContext: transactionContext,
		}, nil
	}
}

// CheckOrderUnique 检查订单的是否已存在
// @companyId 公司id
// @orderCode 订单号
// @deliveryCode 发货单号
// @partnerCategoryCode 合伙人类型编号
// @goodNames 货品名称列表
func (dao OrderBaseDao) CheckOrderExist(companyId int64, orderCode string,
	deliveryCode string, partnerCategory int64, partnerId int64, notId int64, goodNames []string) (bool, error) {
	tx := dao.transactionContext.GetDB()
	query := tx.Model(&models.OrderBase{}).
		Join("JOIN order_good ON order_base.id=order_good.order_id").
		Where("order_base.company_id=?", companyId).
		Where("order_base.order_code=?", orderCode).
		Where("order_base.delivery_code=?", deliveryCode).
		Where("order_base.partner_id=?", partnerId).
		Where(`order_base.partner_category @>'{"id":?}'`, partnerCategory).
		Where("order_base.id<>?", notId).
		WhereIn("order_good.good_name in(?)", goodNames)
	ok, err := query.Exists()
	return ok, err
}

// OrderBonusListByCondition 根据条件获取订单分红列表
// @param orderType 订单类型
// @param partnerOrCode 合伙人姓名或订单号或发货单号
// @param companyId 公司id
// @param partnerCategory 合伙人类型
// @param createTime 创建时间
func (dao OrderBaseDao) OrderBonusListByCondition(companyId int64, orderType int, partnerOrCode string,
	partnerCategory int, createTime [2]string, limit, offset int) ([]models.OrderBase, int, error) {
	tx := dao.transactionContext.GetDB()
	var orders []models.OrderBase
	query := tx.Model(&orders).Where("order_base.company_id=?", companyId)
	if orderType > 0 {
		query = query.Where("order_base.order_type=?", orderType)
	} else {
		query = query.Where("order_base.order_type<>?", domain.OrderIntention)
	}
	if partnerCategory > 0 {
		query = query.Where(`order_base.partner_category@>'{"id":?}'`, partnerCategory)
	}

	if len(createTime[0]) > 0 {
		query = query.Where(`order_base.create_time>=?`, createTime[0])
	}

	if len(createTime[1]) > 0 {
		query = query.Where(`order_base.create_time<=?`, createTime[1])
	}

	if len(partnerOrCode) > 0 {
		query = query.Join("LEFT JOIN partner_info as p ON order_base.partner_id=p.id").
			WhereGroup(func(q *orm.Query) (*orm.Query, error) {
				q = q.WhereOr("order_base.order_code like ? ", "%"+partnerOrCode+"%").
					WhereOr("order_base.delivery_code like ? ", "%"+partnerOrCode+"%").
					WhereOr("p.partner_name like ? ", "%"+partnerOrCode+"%")
				return q, nil
			})
	}
	query = query.Order("order_base.create_time DESC").
		Offset(offset).
		Limit(limit)

	cnt, err := query.SelectAndCount()
	return orders, cnt, err
}

// CustomOrderBonusForExcel 导出分红列表所用的结构
type CustomOrderBonusForExcel struct {
	OrderId             int64
	OrderType           int
	OrderCode           string
	BonusStatus         int
	DeliveryCode        string
	UpdateTime          string
	CreateTime          string
	SaleDate            string
	PartnerBonus        float64
	PartnerBonusHas     float64
	PartnerBonusNot     float64
	PartnerBonusExpense float64
	HasBonusPercent     int
	PartnerName         string
}

// OrderBonusListForExcel 导出分红列表所用
// @param companyId 公司id
// @param orderType 订单类型
// @param partnerOrCode 合伙人姓名或订单号或发货单号
// @param partnerCategory 合伙人类型id
// @param createTime 订单的创建时间范围"[开始时间,结束时间]" 时间格式"2006-01-02 15:04:05+07"
// @return result 查询结果数据
func (dao OrderBaseDao) OrderBonusListForExcel(companyId int64, orderType int, partnerOrCode string,
	partnerCategory int, createTime [2]string) (result []CustomOrderBonusForExcel, err error) {
	//导出分红列表所用的sql
	sqlStr := `SELECT
		t1.ID AS order_id,
		t1.order_type,
		t1.order_code,
		t1.delivery_code,
		t1.bonus_status,
		to_char(t1.update_time AT TIME ZONE 'CCT' ,'YYYY-MM-DD HH24:MI:SS') AS update_time,
		to_char(t1.create_time AT TIME ZONE 'CCT' ,'YYYY-MM-DD HH24:MI:SS') AS create_time,
		to_char(t1.sale_date AT TIME ZONE 'CCT' ,'YYYY-MM-DD HH24:MI:SS') AS sale_date,
		(CASE 
		WHEN t1.use_partner_bonus>0 
		THEN
			t1.use_partner_bonus
		ELSE
			t1.plan_partner_bonus
		END) AS partner_bonus,
		t1.partner_bonus_has,
		t1.partner_bonus_not,
		t1.partner_bonus_expense,
		tt1.has_bonus_percent,
		t2.partner_name 
	FROM
		order_base AS t1
	LEFT JOIN partner_info AS t2 ON t1.partner_id = t2."id"
	LEFT JOIN ( 
		SELECT COUNT ( * ) AS has_bonus_percent, t3.order_id 
		FROM "order_good" AS t3 
		WHERE t3.partner_bonus_percent >= 0 
		GROUP BY t3.order_id 
	) AS tt1 ON t1."id" = tt1.order_id 
	WHERE 1=1 `
	sqlStr += ` AND t1.company_id = ? `
	param := []interface{}{companyId}
	if orderType > 0 {
		param = append(param, orderType)
		sqlStr += ` AND  t1.order_type=? `
	} else {
		param = append(param, domain.OrderIntention)
		sqlStr += ` AND t1.order_type<>? `
	}
	if len(partnerOrCode) > 0 {
		sqlStr += ` AND (t1.order_code like ? OR t1.delivery_code like ? OR t2.partner_name like ? )`
		likeParam := "%" + partnerOrCode + "%"
		param = append(param, likeParam, likeParam, likeParam)
	}
	if partnerCategory > 0 {
		sqlStr += ` AND t1.partner_category @>'{"id":?}'`
		param = append(param, partnerCategory)
	}
	if len(createTime[0]) > 0 {
		param = append(param, createTime[0])
		sqlStr += ` AND t1.create_time>=? `
	}
	if len(createTime[1]) > 0 {
		param = append(param, createTime[1])
		sqlStr += ` AND t1.create_time<=? `
	}
	sqlStr += ` ORDER BY t1.create_time DESC limit 10000 `
	tx := dao.transactionContext.GetDB()
	_, err = tx.Query(&result, sqlStr, param...)

	if err != nil {
		return result, err
	}
	return result, nil
}

// OrderListByCondition 获取实际订单列表
// @param companyId 公司id
// @param orderType 订单类型
// @param partnerOrCode 合伙人姓名或订单号或发货单号
// @param partnerCategory 合伙人类型id
// @param updateTime 订单更新时间范围"[开始时间,结束时间]",时间格式"2006-01-02 15:04:05+07"
// @param createTime 订单的创建时间范围"[开始时间,结束时间]" 时间格式"2006-01-02 15:04:05+07"
func (dao OrderBaseDao) OrderListByCondition(companyId int64, orderType int, partnerName string, orderCode string, deliveryCode string,
	updateTime [2]string, createTime [2]string, saleDate [2]string, partnerCategory int, partnerCategoryName string, limit, offset int) ([]models.OrderBase, int, error) {
	tx := dao.transactionContext.GetDB()
	var orders []models.OrderBase
	query := tx.Model(&orders).Where("order_base.company_id=?", companyId)
	if orderType > 0 {
		query = query.Where("order_base.order_type=?", orderType)
	} else {
		query = query.Where("order_base.order_type<>?", domain.OrderIntention)
	}
	if partnerCategory > 0 {
		query = query.Where(`order_base.partner_category@>'{"id":?}'`, partnerCategory)
	}
	if partnerCategoryName != "" {
		query = query.Where(`order_base.partner_category->>'name' like ?`, "%"+partnerCategoryName+"%")
	}
	if len(updateTime[0]) > 0 {
		query = query.Where(`order_base.update_time>=?`, updateTime[0])
	}
	if len(updateTime[1]) > 0 {
		query = query.Where(`order_base.update_time<=?`, updateTime[1])
	}
	if len(createTime[0]) > 0 {
		query = query.Where(`order_base.create_time>=?`, createTime[0])
	}
	if len(createTime[1]) > 0 {
		query = query.Where(`order_base.create_time<=?`, createTime[1])
	}
	if len(saleDate[0]) > 0 {
		query = query.Where(`order_base.sale_date>=?`, saleDate[0])
	}
	if len(saleDate[1]) > 0 {
		query = query.Where(`order_base.sale_date<=?`, saleDate[1])
	}
	if len(partnerName) > 0 {
		query = query.Join("LEFT JOIN partner_info as p ON order_base.partner_id=p.id").
			Where("p.partner_name like ? ", "%"+partnerName+"%")
	}
	if len(orderCode) > 0 {
		query = query.Where("order_base.order_code like ? ", "%"+orderCode+"%")
	}
	if len(deliveryCode) > 0 {
		query = query.Where("order_base.delivery_code like ? ", "%"+deliveryCode+"%")
	}
	//if len(partnerOrCode) > 0 {
	//	query = query.Join("LEFT JOIN partner_info as p ON order_base.partner_id=p.id").
	//		WhereGroup(func(q *orm.Query) (*orm.Query, error) {
	//			q = q.WhereOr("order_base.order_code like ? ", "%"+partnerOrCode+"%").
	//				WhereOr("order_base.delivery_code like ? ", "%"+partnerOrCode+"%").
	//				WhereOr("p.partner_name like ? ", "%"+partnerOrCode+"%")
	//			return q, nil
	//		})
	//}
	query = query.Order("order_base.create_time DESC").
		Offset(offset).
		Limit(limit)

	cnt, err := query.SelectAndCount()
	return orders, cnt, err

}

// CustomOrderListForExcel 导出实际订单列表所用的结构
type CustomOrderListForExcel struct {
	OrderCode       string  //订单编号
	DeliveryCode    string  //发货编号
	UpdateTime      string  //更新时间
	CreateTime      string  //创建时间
	SaleDate        string  // 销售时间
	PlanOrderCount  int64   //货品总数
	UseOrderCount   int64   //货品总数调整
	RegionName      string  //订单区域
	PlanOrderAmount float64 //订单金额
	UseOrderAmount  float64 //订单金额调整
	PartnerCategory string  //合伙人类型
	BuyerName       string  //买家
	PartnerName     string  //合伙人
}

/**
 * @Author SteveChan
 * @Description // 导出到excel
 * @Date 23:45 2021/1/26
 * @Param
 * @return
 **/
//OrderListForExcel 获取实际订单列表用于excel导出
//@param companyId 公司id
//@param orderType 订单类型
//@param partnerOrCode 合伙人姓名或订单号或发货单号
//@param partnerCategory 合伙人类型id
//@param updateTime 订单更新时间范围"[开始时间,结束时间]",时间格式"2006-01-02 15:04:05+07"
//@param createTime 订单的创建时间范围"[开始时间,结束时间]" 时间格式"2006-01-02 15:04:05+07"
func (dao OrderBaseDao) OrderListForExcel(companyId int64, partnerName string, orderCode string, deliveryCode string,
	updateTime [2]string, createTime [2]string, saleDate [2]string, partnerCategory int, partnerCategoryName string) (
	result []CustomOrderListForExcel, err error) {
	sqlstr := `
			SELECT t1.order_code,t1.delivery_code
			,to_char(t1.create_time AT TIME ZONE 'CCT' ,'YYYY-MM-DD HH24:MI:SS') AS create_time
			,to_char(t1.update_time AT TIME ZONE 'CCT' ,'YYYY-MM-DD HH24:MI:SS') AS update_time
			,to_char(t1.sale_date AT TIME ZONE 'CCT' ,'YYYY-MM-DD HH24:MI:SS') AS sale_date
			,t1.plan_order_count,t1.use_order_count,t1.plan_order_amount,t1.use_order_amount
			,t1.region_info->'regionName' AS region_name,t1.buyer->'buyerName' AS buyer_name
			,t1.partner_category->'name' AS partner_category
			,t2.partner_name
			FROM "order_base" AS t1
			LEFT JOIN partner_info as t2 ON t1.partner_id=t2.id
			WHERE 1=1 AND t1.order_type = 1 AND t1.company_id=? 
			`
	params := []interface{}{companyId}
	//if len(partnerOrCode) > 0 {
	//	like := "%" + partnerOrCode + "%"
	//	params = append(params, like, like, like)
	//	sqlstr += " AND (t1.order_code like ? OR t1.delivery_code like ? OR t2.partner_name like ? ) "
	//}
	if len(partnerName) > 0 {
		like := "%" + partnerName + "%"
		params = append(params, like)
		sqlstr += ` AND t2.partner_name like ? `
	}
	if len(orderCode) > 0 {
		like := "%" + orderCode + "%"
		params = append(params, like)
		sqlstr += ` AND t1.order_code like ? `
	}
	if len(deliveryCode) > 0 {
		like := "%" + deliveryCode + "%"
		params = append(params, like)
		sqlstr += ` AND t1.delivery_code like ? `
	}
	if partnerCategory > 0 {
		params = append(params, partnerCategory)
		sqlstr += ` AND t1.partner_category@>'{"id":?}' `
	}
	if len(partnerCategoryName) > 0 {
		like := "%" + partnerCategoryName + "%"
		params = append(params, like)
		sqlstr += ` AND t1.partner_category->> 'name' like ?`
	}
	if len(updateTime[0]) > 0 {
		params = append(params, updateTime[0])
		sqlstr += ` AND t1.update_time>=? `
	}
	if len(updateTime[1]) > 0 {
		params = append(params, updateTime[1])
		sqlstr += ` AND t1.update_time<=? `
	}
	if len(createTime[0]) > 0 {
		params = append(params, createTime[0])
		sqlstr += ` AND t1.create_time>=? `
	}
	if len(createTime[1]) > 0 {
		params = append(params, createTime[1])
		sqlstr += ` AND t1.create_time<=? `
	}
	if len(saleDate[0]) > 0 {
		params = append(params, saleDate[0])
		sqlstr += ` AND t1.sale_date>=? `
	}
	if len(saleDate[1]) > 0 {
		params = append(params, saleDate[1])
		sqlstr += ` AND t1.sale_date<=? `
	}
	sqlstr += ` ORDER BY t1.create_time DESC limit 10000 `
	tx := dao.transactionContext.GetDB()
	_, err = tx.Query(&result, sqlstr, params...)
	if err != nil {
		return result, err
	}
	return result, nil
}