package service

import (
	"bytes"
	"context"
	"database/sql"
	"encoding/csv"
	"fmt"
	"io"
	"math"
	"os"
	"reflect"
	"strconv"
	"strings"
	"sync"
	"time"

	"github.com/spf13/viper"
	"go.uber.org/zap"
	"gorm.io/gorm"

	"finance-portal/internal/dataplatform/store"
	"finance-portal/model"
	"finance-portal/pkg/errors"
	"finance-portal/pkg/log"
)

type CloudSrv interface {
	HandleARTable(ctx context.Context)
	HandleCloudBreakdown(ctx context.Context) error
	HandleDownload(ctx context.Context, req model.SfReqBody) (*bytes.Buffer, error)
	HandleUpload(ctx context.Context, body model.UploadBody) error
}

type cloudService struct {
	store store.Factory
}

var _ CloudSrv = (*cloudService)(nil)

const (
	dataPlatformBucket = "data-platform-s"
	projectID          = "gcp-digital-bi"
	datasetID          = "google_sheets"

	infoMsg    = 1001
	errorMsg   = 1002
	successMsg = 1003
)

func newCloud(srv *service) *cloudService {
	return &cloudService{
		store: srv.store,
	}
}

func (c *cloudService) HandleARTable(ctx context.Context) {
	log.L(ctx).Info("start HandleARTable")

	cmSrv := newCommon(c.store)

	go cmSrv.saveLarkOps(ctx, infoMsg, "start gererate HandleARTable")

	err := c.ARTable(ctx)
	if err != nil {
		log.L(ctx).Errorf("error ARTable: %v", err)

		go cmSrv.saveLarkOps(ctx, errorMsg, err.Error())

		return
	}

	go cmSrv.saveLarkOps(ctx, successMsg, "Generate HandleARTable success!")

	log.L(ctx).Info("end HandleARTable")

	return
}

func (c *cloudService) ARTable(ctx context.Context) error {
	log.L(ctx).Info("start ARTable")

	// 读取并刷新表
	arRecords, err := c.readAndFreshTable(ctx)
	if err != nil {
		log.L(ctx).Errorf("error readAndFreshTable: %v", err)

		return err
	}

	// 写入数据库
	var billMonth string

	if len(arRecords) > 0 {
		billMonth = *arRecords[0].BillMonth
	} else {
		log.L(ctx).Info("no data to handle")

		return nil
	}

	arStrInvoices, err := genStripeInvoice(ctx, arRecords)
	if err != nil {
		log.L(ctx).Errorf("error genStripeInvoice: %v", err)

		return err
	}

	err = c.store.ARTable().DeleteAndCreate(ctx, arRecords, arStrInvoices, billMonth)
	if err != nil {
		log.L(ctx).Errorf("error DeleteAndCreate: %v", err)

		return err
	}

	log.L(ctx).Info("end ARTable")

	return nil
}

func (c *cloudService) readAndFreshTable(ctx context.Context) ([]model.ARTable, error) {
	log.L(ctx).Info("start readAndFreshTable")

	crPath := viper.GetString("cr-table.path") + "cr-table" + GetLastMonth() + ".csv"

	if overReq3Times(ctx) {
		err := downCRTable(ctx)
		if err != nil {
			log.L(ctx).Errorf("error downCRTable: %v", err)

			return nil, err
		}
	}

	// 是否存在cr-table
	if _, err := os.Stat(crPath); err != nil {
		if strings.Contains(err.Error(), "no such file") {
			err := downCRTable(ctx)
			if err != nil {
				log.L(ctx).Errorf("error downCRTable: %v", err)

				return nil, err
			}
		} else {
			log.L(ctx).Errorf("error os.Stat: %v", err)

			return nil, err
		}
	}

	file, err := os.Open(crPath)
	if err != nil {
		log.L(ctx).Error("Error opening CR table file", zap.String("path", crPath), zap.Error(err))

		return nil, fmt.Errorf("failed to open CR table file at %s: %w", crPath, err)
	}
	defer func() {
		if cerr := file.Close(); cerr != nil {
			log.L(ctx).Error("Error closing file", zap.Error(cerr))
		}
	}()

	reader := csv.NewReader(file)
	headers, err := reader.Read() // 读取CSV头部
	if err != nil {
		log.L(ctx).Errorf("error reading CSV headers: %v", err)
		return nil, err
	}

	arRecords := make([]model.ARTable, 0)
	for {
		// 逐行读取
		record, err := reader.Read()
		if err == io.EOF {
			break
		}
		if err != nil {
			log.L(ctx).Errorf("error reading record: %v", err)
		}

		// 创建结构体实例
		arRecord := &model.ARTable{}

		// 将CSV记录映射到结构体
		if err := mapCSVToStruct(ctx, record, headers, arRecord); err != nil {
			log.L(ctx).Errorf("error mapping CSV to struct: %v", err)
			continue
		}

		arRecord.CreatedAt = time.Now()
		arRecord.Valid = true
		arRecords = append(arRecords, *arRecord)
	}

	arRecords, err = c.freshTable(ctx, arRecords)
	if err != nil {
		log.L(ctx).Errorf("error fresh table: %v", err)

		return nil, err
	}

	// 检查金额是否平衡
	err = checkARAmountBalance(ctx, arRecords)
	if err != nil {
		return nil, err
	}

	log.L(ctx).Info("end readAndFreshTable")

	return arRecords, nil
}

var (
	// 定义一个全局变量来存储请求计数
	requestCount = 0
	// 使用互斥锁来保证线程安全
	mu sync.Mutex
)

func overReq3Times(ctx context.Context) bool {
	log.L(ctx).Info("start overReq3Times")

	// 获取锁
	mu.Lock()
	defer mu.Unlock()

	// 更新请求计数
	requestCount++

	// 检查请求计数
	if requestCount > 3 {
		// 超过3次请求，重置计数
		requestCount = 0

		log.L(ctx).Info("end overReq3Times")

		return true // 返回 true 表示请求超过3次
	}

	log.L(ctx).Infof("request count: %d", requestCount)
	return false // 返回 false 表示请求未超过3次
}

func mapCSVToStruct(ctx context.Context, record []string, headers []string, target interface{}) error {
	targetValue := reflect.ValueOf(target).Elem()
	targetType := targetValue.Type()

	// 遍历结构体字段
	for i := 0; i < targetType.NumField(); i++ {
		field := targetType.Field(i)
		fieldValue := targetValue.Field(i)

		// 获取CSV列名
		csvTag := field.Tag.Get("csv")
		if csvTag == "" {
			continue // 如果没有csv标签，跳过该字段
		}

		// 找到CSV列名对应的索引
		colIndex := -1
		for idx, header := range headers {
			if header == csvTag {
				colIndex = idx
				break
			}
		}
		if colIndex == -1 {
			log.L(ctx).Errorf("csv column not found: %s", csvTag)
			return errors.New("csv column not found: " + csvTag)
		}

		// 获取CSV值
		value := record[colIndex]

		// 如果值为空，跳过该字段
		if value == "" {
			continue
		}

		// 处理指针类型
		if field.Type.Kind() == reflect.Ptr {
			// 获取指针指向的类型
			elemType := field.Type.Elem()

			// 根据指针指向的类型进行解析
			switch elemType.Kind() {
			case reflect.String:
				strValue := value
				fieldValue.Set(reflect.ValueOf(&strValue))
			case reflect.Int, reflect.Int64:
				intValue, err := strconv.Atoi(value)
				if err != nil {
					return err
				}
				fieldValue.Set(reflect.ValueOf(&intValue))
			case reflect.Float64:
				floatValue, err := strconv.ParseFloat(value, 64)
				if err != nil {
					return err
				}
				fieldValue.Set(reflect.ValueOf(&floatValue))
			case reflect.Struct:
				if elemType == reflect.TypeOf(time.Time{}) {
					// 解析时间字符串
					timeValue, err := time.Parse("2006-01-02", value)
					if err != nil {
						return err
					}
					fieldValue.Set(reflect.ValueOf(&timeValue))
				}
			default:
				log.L(ctx).Errorf("unsupported pointer field type: %s", elemType.Kind().String())
				return errors.New("unsupported pointer field type: " + elemType.Kind().String())
			}
		} else {
			// 非指针类型的处理逻辑
			switch field.Type.Kind() {
			case reflect.String:
				fieldValue.SetString(value)
			case reflect.Int, reflect.Int64:
				intValue, err := strconv.Atoi(value)
				if err != nil {
					return err
				}
				fieldValue.SetInt(int64(intValue))
			case reflect.Float64:
				floatValue, err := strconv.ParseFloat(value, 64)
				if err != nil {
					return err
				}
				fieldValue.SetFloat(floatValue)
			case reflect.Struct:
				if field.Type == reflect.TypeOf(time.Time{}) {
					// 解析时间字符串
					timeValue, err := time.Parse("2006-01-02", value)
					if err != nil {
						return err
					}
					fieldValue.Set(reflect.ValueOf(timeValue))
				}
			default:
				log.L(ctx).Errorf("unsupported field type: %s", field.Type.Kind().String())
				return errors.New("unsupported field type: " + field.Type.Kind().String())
			}
		}
	}

	return nil
}

func (c *cloudService) freshTable(ctx context.Context, arRecords []model.ARTable) ([]model.ARTable, error) {
	log.L(ctx).Info("start freshTable")

	orgs, err := c.store.Salesforce().ListOrg(ctx)
	if err != nil {
		log.L(ctx).Errorf("error list org: %v", err)

		return nil, err
	}

	orgMaps := make(map[string]bool)
	for _, org := range orgs {
		orgMaps[org.OrgID] = true
	}

	resTable := make([]model.ARTable, 0)
	for i := 0; i < len(arRecords); i++ {
		arRecord := &arRecords[i]

		if *arRecord.TenantID == "1372813089209214865" {
			fmt.Println("TenantID:", arRecord.TenantID)
		}

		// rule 1
		arRecord, err := mapRules1(ctx, orgMaps, arRecord)
		if err != nil {
			log.L(ctx).Errorf("error mapRules1: %v", err)

			return nil, err
		}

		// rule 2
		arRecord = mapRules2(ctx, arRecord)

		if arRecord.Valid {
			resTable = append(resTable, *arRecord)
		}
	}

	return resTable, nil
}

func mapRules1(ctx context.Context, orgMaps map[string]bool, arRecord *model.ARTable) (*model.ARTable, error) {
	// rule 1.a
	var tenantID string
	if arRecord.TenantID != nil {
		tenantID = *arRecord.TenantID
	}

	if _, ok := orgMaps[tenantID]; !ok {
		arRecord.Valid = false

		return arRecord, nil
	}

	// rule 1.b

	var marketplaceAmount, invoiceAmount float64
	if arRecord.MarketplaceAmount != nil {
		marketplaceAmount = *arRecord.MarketplaceAmount
	}
	if arRecord.InvoiceAmount != nil {
		invoiceAmount = *arRecord.InvoiceAmount
	}
	if marketplaceAmount < 1 && invoiceAmount < 1 && marketplaceAmount == invoiceAmount {
		arRecord.Valid = false

		return arRecord, nil
	}

	// rule 1.c
	var wireTransferAmount float64
	if arRecord.WireTransferAmount != nil {
		wireTransferAmount = *arRecord.WireTransferAmount
	}
	var StripeAmount string
	if arRecord.StripeAmount != nil {
		StripeAmount = *arRecord.StripeAmount
	}
	strSum, err := stripeSum(StripeAmount)
	if err != nil {
		log.L(ctx).Errorf("error stripeSum: %v", err)

		return nil, err
	}

	sum := wireTransferAmount + strSum + marketplaceAmount
	var prepaymentCredits, totalAmount float64
	if arRecord.PrepaymentCredits != nil {
		prepaymentCredits = *arRecord.PrepaymentCredits
	}
	if arRecord.TotalAmount != nil {
		totalAmount = *arRecord.TotalAmount
	}
	if sum != invoiceAmount {
		if sum > 0 {
			arRecord.InvoiceAmount = &sum
		} else if sum == 0 {
			subSum := prepaymentCredits + totalAmount
			if subSum > 0 && subSum != invoiceAmount {
				arRecord.InvoiceAmount = &subSum
			}
		}
	}

	// rule 1.d
	if totalAmount != invoiceAmount {
		arRecord.TotalAmount = &invoiceAmount
	}

	return arRecord, nil
}

func mapRules2(_ context.Context, arRecord *model.ARTable) *model.ARTable {
	// rule 2.a
	var invoiceAmount, channelLossAmount float64
	if arRecord.InvoiceAmount != nil {
		invoiceAmount = *arRecord.InvoiceAmount
	}
	if arRecord.ChannelLossAmount != nil {
		channelLossAmount = *arRecord.ChannelLossAmount
	}
	res := roundToTwoDecimals(invoiceAmount - channelLossAmount)
	arRecord.InvoiceAmount = &res

	// rule 2.b
	var rawStarterAmount, starterAllocatedLoss float64
	if arRecord.RawStarterAmount != nil {
		rawStarterAmount = *arRecord.RawStarterAmount
	}
	if arRecord.StarterAllocatedLoss != nil {
		starterAllocatedLoss = *arRecord.StarterAllocatedLoss
	}
	res2 := roundToTwoDecimals(rawStarterAmount - starterAllocatedLoss)
	arRecord.RawStarterAmount = &res2

	// rule 2.c
	var rawDedicatedAmount, dedicatedAllocatedLoss float64
	if arRecord.RawDedicatedAmount != nil {
		rawDedicatedAmount = *arRecord.RawDedicatedAmount
	}
	if arRecord.DedicatedAllocatedLoss != nil {
		dedicatedAllocatedLoss = *arRecord.DedicatedAllocatedLoss
	}
	res3 := roundToTwoDecimals(rawDedicatedAmount - dedicatedAllocatedLoss)
	arRecord.RawDedicatedAmount = &res3

	// rule 2.d
	var rawSupportAmount, supportAllocatedLoss float64
	if arRecord.RawSupportAmount != nil {
		rawSupportAmount = *arRecord.RawSupportAmount
	}
	if arRecord.SupportAllocatedLoss != nil {
		supportAllocatedLoss = *arRecord.SupportAllocatedLoss
	}
	res4 := roundToTwoDecimals(rawSupportAmount - supportAllocatedLoss)
	arRecord.RawSupportAmount = &res4

	return arRecord
}

func stripeSum(stripeAmount string) (float64, error) {
	stripeAmount = strings.ReplaceAll(stripeAmount, "\"", "")
	// 去掉字符串两端的方括号
	stripeAmount = strings.Trim(stripeAmount, "[]")

	// 按逗号分割字符串
	strValues := strings.Split(stripeAmount, ",")

	// 转换为 float64 切片
	var sum float64
	for _, str := range strValues {
		// 去掉字符串两端的空格
		str = strings.TrimSpace(str)
		if str == "" {
			continue
		}
		// 将字符串转换为 float64
		value, err := strconv.ParseFloat(str, 64)
		if err != nil {
			return 0, fmt.Errorf("error parsing float: %v", err)
		}

		sum += value
	}

	return sum, nil
}

func (c *cloudService) HandleCloudBreakdown(ctx context.Context) error {
	log.L(ctx).Info("start HandleCloudBreakdown")

	cmSrv := newCommon(c.store)

	go cmSrv.saveLarkOps(ctx, infoMsg, "start gererate cloud revrec")

	err := c.cloudBreakdown(ctx)
	if err != nil {
		log.L(ctx).Errorf("error cloudBreakdown: %v", err)

		go cmSrv.saveLarkOps(ctx, errorMsg, err.Error())

		return err
	}

	go cmSrv.saveLarkOps(ctx, successMsg, "Generate cloud revrec success!")

	return nil
}

func (c *cloudService) cloudBreakdown(ctx context.Context) error {
	log.L(ctx).Info("start cloudBreakdown")

	//err := c.loadGsheetBq(ctx)
	//if err != nil {
	//	return err
	//}
	//
	//err = dbtRun(ctx)
	//if err != nil {
	//	log.L(ctx).Errorf("error dbtRun: %v", err)
	//
	//	return err
	//}

	orgService := orgOrderService{store: c.store}
	err := orgService.OrgOrder(ctx)
	if err != nil {
		log.L(ctx).Errorf("error OrgOrder: %v", err)

		return err
	}

	log.L(ctx).Info("end cloudBreakdown")

	return nil
}

func (c *cloudService) syncBigquery(ctx context.Context) error {
	log.L(ctx).Info("start syncBigquery")

	billMonth := GetLastMonth()

	arRecords, err := c.store.ARTable().ListByMonth(ctx, billMonth)
	if err != nil {
		log.L(ctx).Errorf("error list HandleARTable: %v", err)
		return err
	}

	err = uploadARTable(ctx, arRecords)
	if err != nil {
		return err
	}

	err = loadBqARTable(ctx)
	if err != nil {
		return err
	}

	err = insertARTableSnapshot(ctx)
	if err != nil {
		log.L(ctx).Errorf("error insertARTableSnapshot: %v", err)

		return err
	}

	log.L(ctx).Info("end syncBigquery")

	return nil
}

func (c *cloudService) loadGsheetBq(ctx context.Context) error {
	log.L(ctx).Info("start loadGsheetBq")

	err := triggerOrgRevrec(ctx)
	if err != nil {
		log.L(ctx).Errorf("error triggerOrgRevrec: %v", err)

		return err
	}

	err = insertARTableSnapshot(ctx)
	if err != nil {
		log.L(ctx).Errorf("error insertARTableSnapshot: %v", err)

		return err
	}

	log.L(ctx).Info("end syncBigquery")

	return nil
}
func checkARAmountBalance(ctx context.Context, arRecords []model.ARTable) error {
	log.L(ctx).Info("start checkAmountBalance")

	for i := 0; i < len(arRecords); i++ {
		if arRecords[i].ChannelLossAmount == nil {
			arRecords[i].ChannelLossAmount = new(float64)
		}
		if arRecords[i].RawByocAmount == nil {
			arRecords[i].RawByocAmount = new(float64)
		}
		sum := roundToTwoDecimals(*arRecords[i].RawDedicatedAmount + *arRecords[i].RawSupportAmount + *arRecords[i].RawStarterAmount + *arRecords[i].RawByocAmount + *arRecords[i].ChannelLossAmount)
		// 误差<0.02
		diff := math.Abs(*arRecords[i].TotalAmount - sum)
		if diff > 0.02 {
			log.L(ctx).Errorf("error checkAmountBalance: %s", *arRecords[i].TenantID)

			return errors.New("error checkAmountBalance:" + *arRecords[i].TenantID)
		}
	}

	log.L(ctx).Info("end checkAmountBalance")

	return nil
}

func genStripeInvoice(ctx context.Context, arRecords []model.ARTable) (strInvoices []model.ARStripeInvoice, err error) {
	log.L(ctx).Info("start genStripeInvoice")

	for i := 0; i < len(arRecords); i++ {
		if arRecords[i].StripeInvoice == nil {
			continue
		}

		strInvoice := *arRecords[i].StripeInvoice

		// 去除字符串两端的方括号
		invoiceStr := strings.Trim(strInvoice, "[]")
		if invoiceStr == "" {
			continue
		}

		// 按逗号分割字符串
		invoices := strings.Split(invoiceStr, ",")

		// 处理每个发票
		for _, invoice := range invoices {
			// 去除空格
			invoice = strings.TrimSpace(invoice)
			// 去除引号
			invoice = strings.ReplaceAll(invoice, "\"", "")
			if invoice == "" {
				continue
			}

			strInvoices = append(strInvoices, model.ARStripeInvoice{
				TenantID:      *arRecords[i].TenantID,
				BillMonth:     *arRecords[i].BillMonth,
				StripeInvoice: invoice,
				CreatedAt:     time.Now(),
				UpdatedAt:     sql.NullTime{Time: time.Now(), Valid: true},
			})
		}
	}

	return strInvoices, nil
}

func (c *cloudService) HandleUpload(ctx context.Context, body model.UploadBody) error {
	log.L(ctx).Info("start handleUpload")

	var err error
	switch body.Type {
	case "invoice":
		err = c.HandleUploadInvoice(ctx, body)
	default:
		log.L(ctx).Errorf("unsupported upload type: %s", body.Type)

		return fmt.Errorf("unsupported upload type: %s", body.Type)
	}

	if err != nil {
		log.L(ctx).Errorf("error handleUpload: %v", err)

		return err
	}

	log.L(ctx).Info("end handleUpload")

	return nil
}

func (c *cloudService) HandleUploadInvoice(ctx context.Context, body model.UploadBody) error {
	log.L(ctx).Info("start handleUploadInvoice")

	// Process each file
	for _, file := range body.Files {
		pbID, _ := strconv.Atoi(body.ID)
		data := file.Base64Data
		// save to mysql blob
		invoiceByte := []byte(data)

		// Check if record exists
		existingRecord, err := c.store.InvoiceRewrite().GetInvoiceContentBytesByInvoiceID(ctx, pbID)
		if err != nil && err != gorm.ErrRecordNotFound {
			log.L(ctx).Errorf("failed to check existing invoice: %v", err)
			return fmt.Errorf("failed to check existing invoice: %w", err)
		}

		invoice := model.InvoiceRewriteContentByte{
			InvoiceID:      pbID,
			PdfContentByte: invoiceByte,
		}

		if err == gorm.ErrRecordNotFound {
			// Record does not exist, create new one
			log.L(ctx).Infof("creating new invoice record for pbID: %d", pbID)
			err = c.store.InvoiceRewrite().SaveInvoiceContentBytes(ctx, invoice)
			if err != nil {
				log.L(ctx).Errorf("failed to create invoice: %v", err)
				return fmt.Errorf("failed to create invoice: %w", err)
			}
		} else {
			// Record exists, update it
			log.L(ctx).Infof("updating existing invoice record for pbID: %d", pbID)
			invoice.ID = existingRecord.ID // Preserve the existing ID
			err = c.store.InvoiceRewrite().UpdateInvoiceContentBytes(ctx, invoice)
			if err != nil {
				log.L(ctx).Errorf("failed to update invoice: %v", err)
				return fmt.Errorf("failed to update invoice: %w", err)
			}
		}
	}

	log.L(ctx).Info("end handleUploadInvoice")
	return nil
}