package service

import (
	"bytes"
	"context"
	"encoding/json"
	"fmt"
	"math"
	"strings"
	"time"

	"cloud.google.com/go/bigquery"
	"github.com/google/uuid"
	"google.golang.org/api/iterator"

	"github.com/pingcap-inc/data-ingestion-tools/internal/itcronjob/store"
	"github.com/pingcap-inc/data-ingestion-tools/model"
	"github.com/pingcap-inc/data-ingestion-tools/pkg/log"
)

type OrgOrderSrv interface {
	StartOrgOrder(ctx context.Context)
	OrgOrder(ctx context.Context) error
}

type orgOrderService struct {
	store store.Factory
}

var _ OrgOrderSrv = (*orgOrderService)(nil)

func newOrgOrder(str *service) *orgOrderService {
	return &orgOrderService{store: str.store}
}

const (
	GcpProject          = "gcp-digital-bi"
	ApprovalCode        = "AF5C45B6-6394-441B-B0E8-8853EC4F04C4"
	defaultUserLarkID   = "61c9b9df"
	defaultUserOpLarkID = "ou_df0ff4197ab5d3098fa52ffc2a8a6ad7"
	bitableUrl          = "https://pingcap.feishu.cn/base/PyEFbxv38aQomcs9PcxcgdEgnpf?table=tblXrhy8ihhlMwMA&view=vewEdAW0nH"
	bucketName          = "digitalplatform"
	fileName            = "org_order/org_order.csv"
	fakeNumber          = "fake_"
	apToken             = "PyEFbxv38aQomcs9PcxcgdEgnpf"
	orderTableId        = "tbl3pMs0ILDUOFjC"
	revrecTableId       = "tblcvNdfA7ijv9Rb"
	collectionTableId   = "tblXrhy8ihhlMwMA"
	resTableId          = "tblobOmqIkoZyTOK"
)

var (
	comSrv    *commonService
	larkToken string
)

func (oos *orgOrderService) StartOrgOrder(ctx context.Context) {
	log.L(ctx).Info("StartOrgOrder Called.")

	err := oos.OrgOrder(ctx)
	if err != nil {
		log.L(ctx).Errorf("Failed to run org order: %v", err)

		_ = FreshRunStatus(ctx, 1, err)

		return
	}

	_ = FreshRunStatus(ctx, 0, nil)

	return
}

func (oos *orgOrderService) OrgOrder(ctx context.Context) error {
	log.L(ctx).Info("OrgOrder Called.")

	// prod
	// saf := viper.GetString("bq.sa")
	// f, err := os.Stat(saf)
	// if err != nil || f.IsDir() {
	// 	log.L(ctx).Error("Failed to get bg sa file: " + err.Error())
	//
	// 	return err
	// }
	//
	// bqSrv, err := bigquery.NewClient(ctx, GcpProject, option.WithCredentialsFile(saf))
	// debug
	bqSrv, err := bigquery.NewClient(ctx, GcpProject)
	if err != nil {
		return err
	}

	comSrv = &commonService{store: oos.store}
	larkToken, _ = comSrv.GetLarkToken(ctx)

	// debug
	err = oos.saveOrderData(ctx, bqSrv)
	if err != nil {
		return err
	}

	err = oos.saveAccountData(ctx, bqSrv)
	if err != nil {
		return err
	}

	records, err := GetBq(ctx, bqSrv, true)
	if err != nil {
		return err
	}

	// filter unique records
	uniqueRds := filterUniqueRecord(records)
	orders, revrecs, collections, err := oos.getCreatedInfo(ctx, uniqueRds)
	if err != nil {
		return err
	}

	err = WriteBqData(ctx, bqSrv, records)
	if err != nil {
		return err
	}

	err = oos.freshBitable(ctx, orders, revrecs, collections)
	if err != nil {
		return err
	}

	apprCode, err := oos.SendLarkAlert(ctx)
	if err != nil {
		return err
	}
	if apprCode != nil {
		err = oos.store.Approval().Save(&model.Approval{
			CreatedAt:        time.Now(),
			ApprovalInstance: apprCode.InstanceCode,
			Status:           0,
		})
	}
	if err != nil {
		log.L(ctx).Errorf("Failed to save approval: %v", err)

		return err
	}

	return nil
}

func (oos *orgOrderService) getCreatedInfo(ctx context.Context, records []*model.OrgBq) ([]model.CreatedOrder, []model.CreatedRevrec, []model.CreatedCollection, error) {
	createOrders := make([]model.CreatedOrder, 0)
	createRevrecs := make([]model.CreatedRevrec, 0)
	createCollections := make([]model.CreatedCollection, 0)

	orderLists, _ := oos.store.BqOrder().List(ctx)
	accountList, _ := oos.store.BqAccount().List(ctx)
	orderMap := make(map[string]string)
	accountMap := make(map[string]string)
	for _, order := range orderLists {
		orderMap[order.OrderName] = order.OrderID
	}
	for _, account := range accountList {
		accountMap[account.AccountName] = account.AccountID
	}

	for _, record := range records {
		autoType := ""
		fkNum, _ := uuid.NewUUID()
		if record.AccountName.StringVal == "InnoCraft" {
			fmt.Println("Tyler's Org")
		}
		if record.NeedNewOrder.StringVal == "yes" && record.NewOrderNumber.StringVal == "" {
			createOrders = append(createOrders, model.CreatedOrder{
				CreatedAt:        time.Now(),
				OrderStartDate:   DateToString(record.NewOrderStartDate),
				OrderEndDate:     DateToString(record.NewOrderEndDate),
				NetFee:           fmt.Sprintf("%f", record.NewOrderAmount.Float64),
				AccountID:        accountMap[record.AccountID.StringVal],
				Tax:              TrimSplit(record.StripeTaxRate.StringVal)[0],
				Status:           "Activated",
				BillMonth:        record.BillMonth.StringVal,
				OriginalTenantID: record.OriginalTenantID.StringVal,
				AdjustTenantID:   record.AdjustTenantID.StringVal,
				AccountNumber:    record.AccountID.StringVal,
				AccountName:      record.AccountName.StringVal,
				Sync:             0,
				FakeNumber:       fkNum.String(),
			})
			createRevrecs = append(createRevrecs, conRevrec(0, "", fkNum.String(), record))

			autoType += "1"
		}
		if record.RevenueAmount.Float64 != 0 && record.OrderFormRevrecNumber.StringVal == "" {
			createRevrecs = append(createRevrecs, conRevrec(1, record.OrderNumber.StringVal, orderMap[record.OrderNumber.StringVal], record))

			autoType += "2"
		}
		if record.RevenueAmount2.Float64 != 0 && record.OrderForm2RevrecNumber.StringVal == "" {
			createRevrecs = append(createRevrecs, conRevrec(2, record.OrderNumber2.StringVal, orderMap[record.OrderNumber2.StringVal], record))

			autoType += "3"
		}

		if record.CollectionsFrom.StringVal == "Stripe" && record.CollectionsNumber.StringVal == "" && strings.Contains(autoType, "1") {
			createCollections = append(createCollections, conCollections(0, "", fkNum.String(), record)...)

			autoType += "4"
		}
		if record.CollectionsFrom.StringVal == "Stripe" && record.CollectionsNumber.StringVal == "" && strings.Contains(autoType, "2") {
			createCollections = append(createCollections, conCollections(1, record.OrderNumber.StringVal, orderMap[record.OrderNumber.StringVal], record)...)

			autoType += "5"
		}
		if record.CollectionsFrom.StringVal == "Stripe" && record.CollectionsNumber.StringVal == "" && strings.Contains(autoType, "3") {
			createCollections = append(createCollections, conCollections(2, record.OrderNumber2.StringVal, orderMap[record.OrderNumber2.StringVal], record)...)

			autoType += "6"
		}

		record.AutoMateType = bigquery.NullString{StringVal: autoType, Valid: true}
	}

	createOrders, err := oos.store.CreatedOrder().DeleteAndSave(ctx, createOrders)
	if err != nil {
		log.L(ctx).Errorf("Failed to save created orders: %v", err)

		return nil, nil, nil, err
	}

	createRevrecs, err = oos.store.CreatedRevrec().DeleteAndSave(ctx, createRevrecs)
	if err != nil {
		log.L(ctx).Errorf("Failed to save created revrecs: %v", err)

		return nil, nil, nil, err
	}

	createCollections, err = oos.store.CreatedCollection().DeleteAndSave(ctx, createCollections)
	if err != nil {
		log.L(ctx).Errorf("Failed to save created collections: %v", err)

		return nil, nil, nil, err
	}
	// err := oos.store.CreatedRevrec().UpdateRevrecs(createRevrecs)
	// if err != nil {
	// 	log.L(ctx).Errorf("Failed to update created revrecs: %v", err)
	//
	// 	return nil, nil, nil, err
	// }

	return createOrders, createRevrecs, createCollections, nil
}

func conRevrec(typ int, odNum, order string, record *model.OrgBq) model.CreatedRevrec {

	var amount, serverlessAmount, dedicatedAmount, supportAmount bigquery.NullFloat64
	amount = record.NewOrderAmount
	serverlessAmount = record.NewOrderServerlessAmount
	dedicatedAmount = record.NewOrderDedicatedAmount
	supportAmount = record.NewOrderSupportAmount
	if typ == 1 {
		amount = record.RevenueAmount
		serverlessAmount = record.ServerlessRevenueAmount
		dedicatedAmount = record.DedicatedRevenueAmount
		supportAmount = record.SupportRevenueAmount
	}
	if typ == 2 {
		amount = record.RevenueAmount2
		serverlessAmount = record.ServerlessRevenueAmount2
		dedicatedAmount = record.DedicatedRevenueAmount2
		supportAmount = record.SupportRevenueAmount2
	}

	// 四舍五入函数，保留两位小数
	roundToTwoDecimals := func(n float64) float64 {
		return math.Round(n*100) / 100
	}

	// 进行四舍五入
	amount.Float64 = roundToTwoDecimals(amount.Float64)
	serverlessAmount.Float64 = roundToTwoDecimals(serverlessAmount.Float64)
	dedicatedAmount.Float64 = roundToTwoDecimals(dedicatedAmount.Float64)
	supportAmount.Float64 = roundToTwoDecimals(supportAmount.Float64)

	return model.CreatedRevrec{
		CreatedAt:                time.Now(),
		Date:                     DateToString(record.EndDate),
		BillingEndDate:           DateToString(record.EndDate),
		Amount:                   fmt.Sprintf("%.2f", amount.Float64),
		Status:                   "Confirmed",
		BillMonth:                record.BillMonth.StringVal,
		OriginalTenantID:         record.OriginalTenantID.StringVal,
		AdjustTenantID:           record.AdjustTenantID.StringVal,
		AccountNumber:            record.AccountID.StringVal,
		AccountName:              record.AccountName.StringVal,
		Sync:                     0,
		Type:                     typ,
		Order:                    order,
		OrderNumber:              odNum,
		NewOrderServerlessAmount: fmt.Sprintf("%.2f", serverlessAmount.Float64),
		NewOrderDedicatedAmount:  fmt.Sprintf("%.2f", dedicatedAmount.Float64),
		NewOrderSupportAmount:    fmt.Sprintf("%.2f", supportAmount.Float64),
	}
}

func conCollections(typ int, odNum, order string, record *model.OrgBq) []model.CreatedCollection {
	createdCollections := make([]model.CreatedCollection, 0)

	// stripAmounts := TrimSplit(record.StripeAmount.StringVal)
	StripeAmountIncludingTaxs := TrimSplit(record.StripeAmountIncludingTax.StringVal)
	StripeInvoiceNumbers := TrimSplit(record.StripeInvoiceNumber.StringVal)
	StripeInvoiceFinalizedDates := TrimSplit(record.StripeInvoiceFinalizedDate.StringVal)
	StripeInvoicePaids := TrimSplit(record.StripeInvoicePaid.StringVal)
	StripeInvoicePaidDates := TrimSplit(record.StripeInvoicePaidDate.StringVal)
	StripeInvoices := TrimSplit(record.StripeInvoice.StringVal)

	for index, invoiceNumber := range StripeInvoiceNumbers {

		createdCollections = append(createdCollections, model.CreatedCollection{
			CreatedAt:         time.Now(),
			PayableAmount:     StripeAmountIncludingTaxs[index],
			InvoiceNumber:     invoiceNumber,
			PayableDate:       StripeInvoiceFinalizedDates[index],
			ActualBillingDate: StripeInvoiceFinalizedDates[index],
			CollectionsDate:   TripleExpression(StripeInvoicePaids[index], "true", StripeInvoicePaidDates[index], ""),
			CollectionStatus:  TripleExpression(StripeInvoicePaids[index], "true", "Received", "Uncollected"),
			CollectionsAmount: TripleExpression(StripeInvoicePaids[index], "true", StripeAmountIncludingTaxs[index], ""),
			Files:             StripeInvoices[index],
			InvoiceStatus:     "Issued",
			BillMonth:         record.BillMonth.StringVal,
			OriginalTenantID:  record.OriginalTenantID.StringVal,
			AdjustTenantID:    record.AdjustTenantID.StringVal,
			AccountNumber:     record.AccountID.StringVal,
			AccountName:       record.AccountName.StringVal,
			StripePaid:        StripeInvoicePaids[index],
			Sync:              0,
			Type:              typ,
			Order:             order,
			OrderNumber:       odNum,
		})
	}

	return createdCollections
}

func (oos *orgOrderService) freshBitable(ctx context.Context, cOrders []model.CreatedOrder, cRevrecs []model.CreatedRevrec, cCollections []model.CreatedCollection) error {
	log.L(ctx).Info("freshBitable Called.")

	lastMonth := GetLastMonth()
	co, _ := oos.store.CreatedOrder().ListCurrentMonth(ctx, lastMonth)
	cr, _ := oos.store.CreatedRevrec().ListCurrentMonth(ctx, lastMonth)
	cc, _ := oos.store.CreatedCollection().ListCurrentMonth(ctx, lastMonth)
	cOrders = make([]model.CreatedOrder, 0)
	cRevrecs = make([]model.CreatedRevrec, 0)
	cCollections = make([]model.CreatedCollection, 0)
	for _, order := range co {
		cOrders = append(cOrders, *order)
	}
	for _, revrec := range cr {
		cRevrecs = append(cRevrecs, *revrec)
	}
	for _, collection := range cc {
		cCollections = append(cCollections, *collection)
	}

	err := getAndDelBiRecords(ctx, orderTableId)
	if err != nil {
		return err
	}

	err = getAndDelBiRecords(ctx, revrecTableId)
	if err != nil {
		return err
	}

	err = getAndDelBiRecords(ctx, collectionTableId)
	if err != nil {
		return err
	}

	err = writeBiOrders(ctx, cOrders)
	if err != nil {
		return err
	}

	list, err := oos.store.BqOrder().List(ctx)
	if err != nil {
		log.L(ctx).Errorf("Failed to get order data from db: %v", err)

		return err
	}
	orderMap := make(map[string]string)
	for _, order := range list {
		orderMap[order.OrderID] = order.OrderName
	}
	err = writeBiRevrecs(ctx, cRevrecs, orderMap)
	if err != nil {
		return err
	}

	err = writeBiCollections(ctx, cCollections, orderMap)
	if err != nil {
		return err
	}

	log.L(ctx).Info("freshBitable success")

	return nil
}

type Item struct {
	RecordId string `json:"record_id"`
}
type respData struct {
	Items     []Item `json:"items"`
	PageToken string `json:"page_token"`
	Total     int    `json:"total"`
	HasMore   bool   `json:"has_more"`
}

func getAndDelBiRecords(ctx context.Context, tableId string) error {
	url := "/bitable/v1/apps/" + apToken + "/tables/" + tableId + "/records"
	pageToken := ""
	delRecords := make([]string, 0)
	delRecords = append(delRecords, "")
	for {
		params := map[string]string{
			"page_size":  "500",
			"page_token": pageToken,
		}

		resp, err := comSrv.GetDataByReqLarkAPI(ctx, "GET", LarkOpenAPIBase+url, nil, params, larkToken)
		if err != nil {
			return err
		}

		var resData respData
		err = json.Unmarshal(resp, &resData)
		if err != nil {
			log.L(ctx).Errorf("unmarshal response error: %v", err)

			return err
		}

		for _, item := range resData.Items {
			delRecords = append(delRecords, item.RecordId)
		}

		if resData.PageToken == "" {
			break
		} else {
			pageToken = resData.PageToken
		}
	}

	url = "/bitable/v1/apps/" + apToken + "/tables/" + tableId + "/records/batch_delete"
	body := map[string]interface{}{
		"records": delRecords,
	}

	marshalBody, _ := json.Marshal(body)

	_, err := comSrv.GetDataByReqLarkAPI(ctx, "POST", LarkOpenAPIBase+url, bytes.NewReader(marshalBody), nil, larkToken)
	if err != nil {
		return err
	}

	return nil
}

type BiItem struct {
	Fields   interface{} `json:"fields"`
	ID       string      `json:"id,omitempty" `
	RecordID string      `json:"record_id,omitempty"`
}

func writeBiOrders(ctx context.Context, orders []model.CreatedOrder) error {
	log.L(ctx).Info("writeBiOrders Called.")
	if len(orders) == 0 {
		return nil
	}

	url := "/bitable/v1/apps/" + apToken + "/tables/" + orderTableId + "/records/batch_create"
	records := make([]BiItem, 0)
	for _, order := range orders {
		records = append(records, BiItem{
			Fields: model.BiOrder{
				OrderStartDate:   order.OrderStartDate,
				OrderEndDate:     order.OrderEndDate,
				ServiceStartDate: order.OrderStartDate,
				ServiceEndDate:   order.OrderEndDate,
				Tax:              order.Tax,
				Status:           order.Status,
				NetFees:          order.NetFee,
				BillMonth:        order.BillMonth,
				OriginalTenantID: order.OriginalTenantID,
				AdjustTenantID:   order.AdjustTenantID,
				AccountNumber:    order.AccountNumber,
				AccountName:      order.AccountName,
				OrderNumber:      order.NewOrderNumber,
				CreatedAt:        order.CreatedAt.Format("2006-01-02 15:04:05"),
			},
		})
	}
	body := map[string]interface{}{
		"records": records,
	}

	marshalBody, _ := json.Marshal(body)

	_, err := comSrv.GetDataByReqLarkAPI(ctx, "POST", LarkOpenAPIBase+url, bytes.NewReader(marshalBody), nil, larkToken)
	if err != nil {
		return err
	}

	log.L(ctx).Info("writeBiOrders success")

	return nil
}

func writeBiRevrecs(ctx context.Context, revrecs []model.CreatedRevrec, orderMap map[string]string) error {
	log.L(ctx).Info("writeBiRevrecs Called.")
	if len(revrecs) == 0 {
		return nil
	}

	url := "/bitable/v1/apps/" + apToken + "/tables/" + revrecTableId + "/records/batch_create"
	records := make([]BiItem, 0)
	for _, revrec := range revrecs {
		if revrec.Type != 0 {
			revrec.Order = orderMap[revrec.Order]
		}
		records = append(records, BiItem{
			Fields: model.BiRevrec{
				Date:                    revrec.Date,
				TiDBCloudBillingEndDate: revrec.BillingEndDate,
				Amount:                  revrec.Amount,
				Status:                  revrec.Status,
				BillMonth:               revrec.BillMonth,
				OriginalTenantID:        revrec.OriginalTenantID,
				AdjustTenantID:          revrec.AdjustTenantID,
				AccountNumber:           revrec.AccountNumber,
				AccountName:             revrec.AccountName,
				OrderNumber:             revrec.OrderNumber,
				RevrecNumber:            revrec.RevrecNumber,
				CreatedAt:               revrec.CreatedAt.Format("2006-01-02 15:04:05"),
				SupportAmount:           revrec.NewOrderSupportAmount,
				ServerlessAmount:        revrec.NewOrderServerlessAmount,
				DedicatedAmount:         revrec.NewOrderDedicatedAmount,
			},
		})
	}
	body := map[string]interface{}{
		"records": records,
	}

	marshalBody, _ := json.Marshal(body)

	_, err := comSrv.GetDataByReqLarkAPI(ctx, "POST", LarkOpenAPIBase+url, bytes.NewReader(marshalBody), nil, larkToken)
	if err != nil {
		return err
	}

	log.L(ctx).Infof("writeBiRevrecs success: %v")

	return nil
}

func writeBiCollections(ctx context.Context, collections []model.CreatedCollection, orderMap map[string]string) error {
	log.L(ctx).Info("writeBiCollections Called.")
	if len(collections) == 0 {
		return nil
	}

	url := "/bitable/v1/apps/" + apToken + "/tables/" + collectionTableId + "/records/batch_create"
	records := make([]BiItem, 0)
	for _, collection := range collections {
		if collection.Type != 0 {
			collection.Order = orderMap[collection.Order]
		}
		records = append(records, BiItem{
			Fields: model.BiCollection{
				BillMonth:         collection.BillMonth,
				OriginalTenantID:  collection.OriginalTenantID,
				AdjustTenantID:    collection.AdjustTenantID,
				AccountNumber:     collection.AccountNumber,
				AccountName:       collection.AccountName,
				OrderNumber:       collection.OrderNumber,
				CollectionNumber:  collection.CollectionNumber,
				InvoiceNumber:     collection.InvoiceNumber,
				PayableAmount:     collection.PayableAmount,
				PayableDate:       collection.PayableDate,
				ActualBillingDate: collection.ActualBillingDate,
				CollectionsDate:   collection.CollectionsDate,
				CollectionStatus:  collection.CollectionStatus,
				CollectionsAmount: collection.CollectionsAmount,
				Files:             collection.Files,
				InvoiceStatus:     collection.InvoiceStatus,
				StripePaid:        collection.StripePaid,
				CreatedAt:         collection.CreatedAt.Format("2006-01-02 15:04:05"),
			},
		})
	}
	body := map[string]interface{}{
		"records": records,
	}

	marshalBody, _ := json.Marshal(body)

	_, err := comSrv.GetDataByReqLarkAPI(ctx, "POST", LarkOpenAPIBase+url, bytes.NewReader(marshalBody), nil, larkToken)
	if err != nil {
		return err
	}

	log.L(ctx).Infof("writeBiCollections success: %v")

	return nil
}

func FreshRunStatus(ctx context.Context, tp int, err error) error {
	log.L(ctx).Info("FreshRunStatus Called.")

	var sus, msg string
	if tp == 0 {
		sus = "成功"
	} else {
		sus = "失败"
	}
	if err != nil {
		msg = err.Error()
	}

	url := "/bitable/v1/apps/" + apToken + "/tables/" + resTableId + "/records"

	params := map[string]string{
		"page_size":  "10",
		"page_token": "",
	}

	recordID := ""

	resp, err := comSrv.GetDataByReqLarkAPI(ctx, "GET", LarkOpenAPIBase+url, nil, params, larkToken)
	if err != nil {
		return err
	}

	var resData respData
	err = json.Unmarshal(resp, &resData)
	if err != nil {
		log.L(ctx).Errorf("unmarshal response error: %v", err)

		return err
	}

	for _, item := range resData.Items {
		recordID = item.RecordId
	}

	url += "/" + recordID
	loc, _ := time.LoadLocation("Asia/Shanghai")
	body := BiItem{
		Fields: model.RunStatus{
			RunStatus: sus,
			RunTime:   time.Now().In(loc).Format("2006-01-02 15:04:05"),
			Message:   msg,
		},
	}

	marshalBody, _ := json.Marshal(body)

	_, err = comSrv.GetDataByReqLarkAPI(ctx, "PUT", LarkOpenAPIBase+url, bytes.NewReader(marshalBody), nil, larkToken)
	if err != nil {
		log.L(ctx).Errorf("error when update run status, err: %s", err.Error())

		return err
	}

	return nil
}

func filterUniqueRecord(records []*model.OrgBq) []*model.OrgBq {
	recordMap := make(map[string]int)
	for _, record := range records {
		recordMap[record.AdjustTenantID.StringVal] += 1
	}

	uniqueRecords := make([]*model.OrgBq, 0)
	for _, record := range records {
		if recordMap[record.AdjustTenantID.StringVal] == 2 && record.AdjustTenantID.StringVal == record.OriginalTenantID.StringVal {
			uniqueRecords = append(uniqueRecords, record)
			fmt.Println(record.AdjustTenantID.StringVal)
		} else if recordMap[record.AdjustTenantID.StringVal] == 1 {
			uniqueRecords = append(uniqueRecords, record)
		}
	}

	return uniqueRecords
}

func (oos *orgOrderService) saveOrderData(ctx context.Context, bqSrv *bigquery.Client) error {
	sql := "SELECT id,name,is_deleted FROM `gcp-digital-bi.salesforce.order` "

	query := bqSrv.Query(sql)
	it, err := query.Read(ctx)
	if err != nil {
		log.L(ctx).Errorf("Failed to read query: %v", err)

		return err
	}
	orderBqr := make([]model.OrderBq, 0)

	for {
		var value model.OrderBq
		err := it.Next(&value)
		if err == iterator.Done {
			break
		}
		if err != nil {
			log.L(ctx).Errorf("Failed to read row: %v", err)

			return err
		}

		if !value.IsDeleted.Bool {
			orderBqr = append(orderBqr, value)
		}
	}

	ordeDb, err := oos.store.BqOrder().List(ctx)
	if err != nil {
		log.L(ctx).Errorf("Failed to get order data from db: %v", err)

		return err
	}

	orderMap := make(map[string]model.OrderBq)
	for _, order := range orderBqr {
		orderMap[order.ID.StringVal] = order
	}

	addRecords := make([]model.BqOrder, 0)
	upRecords := make([]model.BqOrder, 0)
	delRecords := make([]model.BqOrder, 0)
	for _, order := range ordeDb {
		v, ok := orderMap[order.OrderID]
		if !ok {
			delRecords = append(delRecords, order)
		}
		if ok && v.Name.StringVal != order.OrderName {
			order.OrderName = v.Name.StringVal
			upRecords = append(upRecords, order)
		}
		delete(orderMap, order.OrderID)
	}

	for _, order := range orderMap {
		addRecords = append(addRecords, model.BqOrder{
			OrderID:   order.ID.StringVal,
			OrderName: order.Name.StringVal,
		})
	}

	err = oos.store.BqOrder().Save(addRecords, upRecords, delRecords)
	if err != nil {
		log.L(ctx).Errorf("Failed to save order data: %v", err)

		return err
	}

	return nil
}

func (oos *orgOrderService) saveAccountData(ctx context.Context, bqSrv *bigquery.Client) error {
	sql := "SELECT id,account_id_c FROM `gcp-digital-bi.salesforce.account` "

	query := bqSrv.Query(sql)
	it, err := query.Read(ctx)
	if err != nil {
		log.L(ctx).Errorf("Failed to read query: %v", err)

		return err
	}
	accountBqr := make([]model.AccountBq, 0)

	for {
		var value model.AccountBq
		err := it.Next(&value)
		if err == iterator.Done {
			break
		}
		if err != nil {
			log.L(ctx).Errorf("Failed to read row: %v", err)

			return err
		}

		accountBqr = append(accountBqr, value)
	}

	accountDb, err := oos.store.BqAccount().List(ctx)
	if err != nil {
		log.L(ctx).Errorf("Failed to get order data from db: %v", err)

		return err
	}

	accountMap := make(map[string]model.AccountBq)
	for _, account := range accountBqr {
		accountMap[account.ID.StringVal] = account
	}

	addRecords := make([]model.BqAccount, 0)
	upRecords := make([]model.BqAccount, 0)
	delRecords := make([]model.BqAccount, 0)
	for _, account := range accountDb {
		v, ok := accountMap[account.AccountID]
		if !ok {
			delRecords = append(delRecords, account)
		}
		if ok && v.AccountName.StringVal != account.AccountName {
			account.AccountName = v.AccountName.StringVal
			upRecords = append(upRecords, account)
		}
		delete(accountMap, account.AccountID)
	}

	for _, account := range accountMap {
		addRecords = append(addRecords, model.BqAccount{
			AccountID:   account.ID.StringVal,
			AccountName: account.AccountName.StringVal,
		})
	}

	err = oos.store.BqAccount().Save(addRecords, upRecords, delRecords)
	if err != nil {
		log.L(ctx).Errorf("Failed to save order data: %v", err)

		return err
	}

	return nil
}

func (oos *orgOrderService) SendLarkAlert(ctx context.Context) (*model.ApprovalLarkResData, error) {

	record, err := oos.store.Approval().GetByCreatedTime(ctx)
	if err != nil && !strings.Contains(err.Error(), "record not found") {
		log.L(ctx).Errorf("Failed to get approval: %v", err)

		return nil, err
	}

	if record != nil && record.ApprovalInstance != "" {
		status, err := oos.checkApprStatus(ctx, record.ApprovalInstance)
		if err != nil {
			return nil, err
		}

		if !status {
			log.L(ctx).Infof("approval has sent already, approval id: %s", record.ApprovalInstance)

			return nil, nil
		}
	}

	comSrv := &commonService{store: oos.store}

	larkToken, err := comSrv.GetLarkToken(ctx)

	log.L(ctx).Info("SendLarkAlert Called.")

	reqBody := model.LarkCreateApprovalBody{
		ApprovalCode: ApprovalCode,
	}

	reqBody.UserId = defaultUserLarkID
	reqBody.OpenId = defaultUserOpLarkID

	currentTime := time.Now()
	formattedTime := currentTime.Format("2006-01-02 15:04:05")

	confirmPerson := []string{
		"9c7ca79f", // 朱晓鹏
	}
	AssistPeople := []string{
		"9c7ca79f", // 朱晓鹏
		"61c9b9df", // 霍瑶
	}
	var resForm []model.FormItem
	resForm = append(resForm,
		model.FormItem{
			Id:    "widget16986496545250001",
			Type:  "input",
			Value: formattedTime,
		},
		model.FormItem{
			Id:    "widget16986532634990001",
			Type:  "input",
			Value: "请及时确认上个 revrec_month 需要新建的 Order,RevRec和Collection",
		},
		model.FormItem{
			Id:    "widget16986532247470001",
			Type:  "textarea",
			Value: bitableUrl,
		},
		model.FormItem{
			Id:    "widget16986491062310001",
			Type:  "contact",
			Value: confirmPerson,
		},
		model.FormItem{
			Id:    "widget16986492248820001",
			Type:  "contact",
			Value: AssistPeople,
		},
	)

	jsonForm, _ := json.Marshal(resForm)
	reqBody.Form = string(jsonForm)

	marshalBody, _ := json.Marshal(reqBody)

	approvalR, err := comSrv.GetDataByReqLarkAPI(ctx, "POST", LarkOpenAPIBase+"/approval/v4/instances", bytes.NewReader(marshalBody), nil, larkToken)
	if err != nil {
		log.L(ctx).Errorf("error when submit lark approval, err: %s", err.Error())

		return nil, err
	}

	var approvalRes model.ApprovalLarkResData
	err = json.Unmarshal(approvalR, &approvalRes)
	if err != nil {
		log.L(ctx).Errorf("error when unmarshal lark approval response, err: %s", err.Error())

		return nil, err
	}

	log.L(ctx).Infof("submit lark approval success, approval id: %s", approvalRes.InstanceCode)

	return &approvalRes, err
}

type apprResp struct {
	Status string `json:"status"`
}

func (oos *orgOrderService) checkApprStatus(ctx context.Context, apprCode string) (bool, error) {

	url := "/approval/v4/instances/"
	resp, err := comSrv.GetDataByReqLarkAPI(ctx, "GET", LarkOpenAPIBase+url+apprCode, nil, nil, larkToken)
	if err != nil {
		log.L(ctx).Errorf("error when get lark approval, err: %s", err.Error())

		return false, err
	}

	var approvalRes apprResp
	err = json.Unmarshal(resp, &approvalRes)
	if err != nil {
		log.L(ctx).Errorf("error when unmarshal lark approval response, err: %s", err.Error())

		return false, err
	}

	if approvalRes.Status != "PENDING" {
		err = oos.store.Approval().Update(apprCode)
		if err != nil {
			log.L(ctx).Errorf("error when save wiki approval, err: %s", err.Error())

			return false, err
		}

		return true, nil
	}

	log.L(ctx).Infof("check approval status success")

	return false, nil
}