package service

import (
	"context"
	"encoding/csv"
	"fmt"
	"mime/multipart"
	"strconv"
	"strings"
	"time"

	"github.com/tealeg/xlsx"

	"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/errors"
	"github.com/pingcap-inc/data-ingestion-tools/pkg/log"
)

type CloudBillingSrv interface {
	ParseBillingFile(ctx context.Context, user, cloudProvider, yearMonth string, file *multipart.FileHeader) error
	Download(ctx context.Context, listReq model.CloudBillingList, fileName string) (string, error)
	GetCloudBillingList(ctx context.Context, list model.CloudBillingList) (ListData, error)
}

type cloudBillingService struct {
	store store.Factory
}

var _ CloudBillingSrv = (*cloudBillingService)(nil)

func newCloudBilling(str *service) *cloudBillingService {
	return &cloudBillingService{store: str.store}
}

const (
	AWS      = "aws"
	GCP      = "gcp"
	AWSCN    = "awscn"
	KSCLOUD  = "kscloud"
	UCLOUD   = "ucloud"
	ACCOUNT  = "account"
	OVERSEA  = 0
	DOMESTIC = 1
	ACCOUNTS = 2
	FilePath = "./data/"
)

func (cbs *cloudBillingService) ParseBillingFile(ctx context.Context, user, cloudProvider, yearMonth string, file *multipart.FileHeader) error {
	log.L(ctx).Infof("start parse cloud billing file, cloud type: %s", cloudProvider)

	switch cloudProvider {
	case AWS:
		if err := cbs.parseAws(ctx, yearMonth, file); err != nil {
			log.L(ctx).Errorf("parse aws billing file error: %s", err.Error())

			return err
		}
	case GCP:
		if err := cbs.parseGcp(ctx, yearMonth, file); err != nil {
			log.L(ctx).Errorf("parse gcp billing file error: %s", err.Error())

			return err
		}
	case AWSCN:
		if err := cbs.parseAwsCN(ctx, yearMonth, file); err != nil {
			log.L(ctx).Errorf("parse awscn billing file error: %s", err.Error())

			return err
		}
	case KSCLOUD:
		if err := cbs.parseKscloud(ctx, yearMonth, file); err != nil {
			log.L(ctx).Errorf("parse kscloud billing file error: %s", err.Error())

			return err
		}
	case UCLOUD:
		if err := cbs.parseUcloud(ctx, yearMonth, file); err != nil {
			log.L(ctx).Errorf("parse ucloud billing file error: %s", err.Error())

			return err
		}
	case ACCOUNT:
		if err := cbs.parseAccount(ctx, yearMonth, file); err != nil {
			log.L(ctx).Errorf("parse account file error: %s", err.Error())

			return err
		}
	default:
		log.L(ctx).Errorf("cloud type is not supported")

		return errors.New("cloud type is not supported")
	}

	log.L(ctx).Infof("end parse cloud billing file, cloud type: %s", cloudProvider)

	err := cbs.store.CloudBillingAudit().Create(&model.CloudBillingAudit{
		CreatedAt: time.Now(),
		User:      user,
		Action:    "upload " + cloudProvider,
	})
	if err != nil {
		log.L(ctx).Errorf("create cloud billing audit error: %s", err.Error())

		return err
	}

	return nil
}

func (cbs *cloudBillingService) Download(ctx context.Context, listReq model.CloudBillingList, fileName string) (string, error) {
	log.L(ctx).Infof("start download cloud billing file")

	// write records to xlsx
	file := xlsx.NewFile()
	sheet, err := file.AddSheet("Sheet1")
	if err != nil {
		log.L(ctx).Errorf("add sheet error: %s", err.Error())

		return "", err
	}
	switch listReq.PageType {
	case OVERSEA:
		records, _, err := cbs.store.CloudBilling().ListOverSeaByMonth(listReq.CloudProvider, listReq.Month, listReq.Page, listReq.PageSize)
		if err != nil {
			log.L(ctx).Errorf("get cloud billing list error: %s", err.Error())

			return "", err
		}

		// write header
		header := sheet.AddRow()
		header.AddCell().SetString("AccountID")
		header.AddCell().SetString("AccountName")
		header.AddCell().SetString("CloudProvider")
		header.AddCell().SetString("InvoiceAmount")
		header.AddCell().SetString("BillingMonth")
		header.AddCell().SetString("DTAZDiscount")
		header.AddCell().SetString("AwsInvoiceExcludingDtaz")

		for _, record := range records {
			row := sheet.AddRow()
			row.AddCell().SetString(record.AccountID)
			row.AddCell().SetString(record.AccountName)
			row.AddCell().SetString(record.CloudProvider)
			row.AddCell().SetFloat(record.InvoiceAmount)
			row.AddCell().SetString(record.BillingMonth)
			row.AddCell().SetFloat(record.DTAZDiscount)
			row.AddCell().SetFloat(record.AwsInvoiceExcludingDtaz)
		}

	case DOMESTIC:
	case ACCOUNTS:
	default:
		log.L(ctx).Errorf("page type not supported")

		return "", errors.New("page type not supported")
	}

	err = file.Save(FilePath + fileName)
	if err != nil {
		log.L(ctx).Errorf("save xlsx error: %s", err.Error())

		return "", err
	}
	log.L(ctx).Infof("end download cloud billing file")

	return FilePath + fileName, nil
}

func (cbs *cloudBillingService) parseAws(ctx context.Context, yearMonth string, file *multipart.FileHeader) error {
	log.L(ctx).Infof("start parse aws billing file")

	// check file name
	if !strings.Contains(strings.ToLower(file.Filename), "aws") {
		log.L(ctx).Errorf("file is not aws billing file")

		return errors.New("file is not aws billing file")
	}

	xFile, err := resolveExcel(ctx, file)
	if err != nil {
		log.L(ctx).Errorf("resolve excel error: %s", err.Error())

		return err
	}

	awsBilling := make([]*model.CloudBillingOverSea, 0)

	headers := make(map[string]int)
	var sheet *xlsx.Sheet
	for _, s := range xFile.Sheets {
		if strings.Contains(s.Name, "预估") {
			sheet = s
			break
		}
	}
	for i, row := range sheet.Rows {
		cells := row.Cells
		if i == 0 {
			for j, cell := range cells {
				col := cell.String()
				trimCol := strings.ReplaceAll(col, " ", "")
				headers[trimCol] = j
			}
			continue
		}
		if cells[0] == nil || cells[0].String() == "" {
			break
		}

		acID := trimSpace(cells[headers["关联账户"]].String())
		if acID == "13690006788" {
			acID = "013690006788"
		}
		invoice, err1 := cells[headers["最终账单金额"]].Float()
		discount, err2 := cells[headers["DTAZ折扣(PPA折扣）"]].Float()
		if err1 != nil || err2 != nil {
			log.L(ctx).Errorf("excel format error")

			return errors.New("excel format error")
		}

		awsBilling = append(awsBilling, &model.CloudBillingOverSea{
			CreatedAt:               time.Now(),
			AccountID:               acID,
			AccountName:             trimSpace(cells[headers["关联账户名称"]].String()),
			CloudProvider:           AWS,
			InvoiceAmount:           invoice,
			BillingMonth:            yearMonth,
			DTAZDiscount:            discount,
			AwsInvoiceExcludingDtaz: invoice + discount,
		})
	}

	// save db
	err = cbs.store.CloudBilling().SaveOverSeaByMonth(awsBilling, yearMonth, AWS)
	if err != nil {
		log.L(ctx).Errorf("save aws billing file error: %s", err.Error())

		return err
	}

	log.L(ctx).Infof("end parse aws billing file")

	return nil
}

func (cbs *cloudBillingService) parseGcp(ctx context.Context, yearMonth string, file *multipart.FileHeader) error {
	log.L(ctx).Infof("start parse gcp billing file")

	// check file name
	if !strings.Contains(strings.ToLower(file.Filename), "report") {
		log.L(ctx).Errorf("file is not gcp billing file")

		return errors.New("file is not gcp billing file")
	}

	f, err := file.Open()
	if err != nil {
		log.L(ctx).Errorf("open file error: %s", err.Error())

		return err
	}

	reader := csv.NewReader(f)
	records, err := reader.ReadAll()
	if err != nil {
		log.L(ctx).Errorf("read csv error: %s", err.Error())

		return err
	}

	gcpBilling := make([]*model.CloudBillingOverSea, 0)
	header := make(map[string]int)
	for i, row := range records {
		if i == 0 {
			for j, col := range row {
				trimCol := strings.ToLower(strings.ReplaceAll(col, " ", ""))
				header[trimCol] = j
			}
			continue
		}

		acID := trimSpace(row[header["projectid"]])
		invoice, err := strconv.ParseFloat(row[header["subtotal($)"]], 64)
		if err != nil {
			log.L(ctx).Errorf("parse gcp billing file error: %s", err.Error())

			return err
		}
		acName := trimSpace(row[header["projectname"]])
		if acName == "" {
			break
		}

		gcpBilling = append(gcpBilling, &model.CloudBillingOverSea{
			CreatedAt:     time.Now(),
			AccountID:     acID,
			CloudProvider: GCP,
			InvoiceAmount: invoice,
			BillingMonth:  yearMonth,
			AccountName:   acName,
		})
	}

	// save db
	err = cbs.store.CloudBilling().SaveOverSeaByMonth(gcpBilling, yearMonth, GCP)
	if err != nil {
		log.L(ctx).Errorf("save gcp billing file error: %s", err.Error())

		return err
	}

	log.L(ctx).Infof("end parse gcp billing file")

	return nil
}

func (cbs *cloudBillingService) parseAwsCN(ctx context.Context, yearMonth string, file *multipart.FileHeader) error {
	log.L(ctx).Infof("start parse awscn billing file")

	xFile, err := resolveExcel(ctx, file)
	if err != nil {
		log.L(ctx).Errorf("resolve excel error: %s", err.Error())

		return err
	}

	awsBilling := make([]*model.CloudBillingOverSea, 0)

	headers := make(map[string]int)
	for i, row := range xFile.Sheets[0].Rows {
		cells := row.Cells
		if i == 0 {
			for j, cell := range cells {
				col := cell.String()
				trimCol := strings.ReplaceAll(col, " ", "")
				headers[trimCol] = j
			}
			continue
		}
		if cells[0] == nil || cells[0].String() == "" {
			break
		}

		acID := trimSpace(cells[headers["云平台下的各个账号ID"]].String())
		invoice, err := cells[headers["最终账单金额"]].Float()
		if err != nil {
			log.L(ctx).Errorf("parse awscn billing file error: %s", err.Error())

			return err
		}

		awsBilling = append(awsBilling, &model.CloudBillingOverSea{
			CreatedAt:               time.Now(),
			AccountID:               acID,
			CloudProvider:           AWSCN,
			BillingMonth:            yearMonth,
			InvoiceAmount:           invoice,
			DTAZDiscount:            0,
			AwsInvoiceExcludingDtaz: invoice,
		})
	}

	// save db
	err = cbs.store.CloudBilling().SaveOverSeaByMonth(awsBilling, yearMonth, AWSCN)
	if err != nil {
		log.L(ctx).Errorf("save awscn billing file error: %s", err.Error())

		return err
	}

	log.L(ctx).Infof("end parse awscn billing file")

	return nil
}

func (cbs *cloudBillingService) parseKscloud(ctx context.Context, yearMonth string, file *multipart.FileHeader) error {
	log.L(ctx).Infof("start parse kscloud billing file")

	// check file name
	if !strings.Contains(strings.ToLower(file.Filename), "金山云") {
		log.L(ctx).Errorf("file is not kscloud billing file")

		return errors.New("file is not kscloud billing file")
	}

	xFile, err := resolveExcel(ctx, file)
	if err != nil {
		log.L(ctx).Errorf("resolve excel error: %s", err.Error())

		return err
	}

	kscloudBilling := make([]*model.CloudBillingDomestic, 0)
	for _, sheet := range xFile.Sheets {
		if strings.Contains(sheet.Name, "实例明细") {
			headers := make(map[string]int)
			for i, row := range sheet.Rows {
				if i == 0 {
					for j, cell := range row.Cells {
						col := cell.String()
						trimCol := strings.ReplaceAll(col, " ", "")
						headers[trimCol] = j
					}
					continue
				}
				cells := row.Cells
				if cells[0] == nil || trimSpace(cells[0].String()) == "" {
					break
				}
				proj := trimSpace(cells[headers["项目名称"]].String())
				resourProjLine := trimSpace(cells[headers["产品线"]].String())
				invoiceAmount, err := cells[headers["成交价"]].Float()
				if err != nil {
					log.L(ctx).Errorf("parse kscloud billing file error: %s", err.Error())

					return err
				}

				kscloudBilling = append(kscloudBilling, &model.CloudBillingDomestic{
					CreatedAt:           time.Now(),
					CloudProvider:       KSCLOUD,
					BillingMonth:        yearMonth,
					Project:             proj,
					ResourceProductLine: resourProjLine,
					InvoiceAmount:       invoiceAmount,
				})
			}
		}
	}

	// save db
	err = cbs.store.CloudBilling().SaveDomesticByMonth(kscloudBilling, yearMonth, KSCLOUD)
	if err != nil {
		log.L(ctx).Errorf("save kscloud billing file error: %s", err.Error())

		return err
	}

	log.L(ctx).Infof("end parse kscloud billing file")

	return nil
}

func (cbs *cloudBillingService) parseUcloud(ctx context.Context, yearMonth string, file *multipart.FileHeader) error {
	log.L(ctx).Infof("start parse ucloud billing file")

	// check file name
	if !strings.Contains(strings.ToLower(file.Filename), "ucloud") {
		log.L(ctx).Errorf("file is not ucloud billing file")

		return errors.New("file is not ucloud billing file")
	}

	xFile, err := resolveExcel(ctx, file)
	if err != nil {
		log.L(ctx).Errorf("resolve excel error: %s", err.Error())

		return err
	}

	ucloudBilling := make([]*model.CloudBillingDomestic, 0)
	for _, sheet := range xFile.Sheets {
		if strings.Contains(sheet.Name, "消费总计") {
			headers := make(map[string]int)
			for i, row := range sheet.Rows {
				if i == 0 {
					for j, cell := range row.Cells {
						col := cell.String()
						trimCol := strings.ReplaceAll(col, " ", "")
						headers[trimCol] = j
					}
					continue
				}
				cells := row.Cells
				if (cells[0] == nil || trimSpace(cells[0].String()) == "") && (cells[1] == nil || trimSpace(cells[1].String()) == "") {
					break
				}
				proj := trimSpace(cells[headers["项目"]].String())
				resourProjLine := trimSpace(cells[headers["资源类型"]].String())
				invoiceAmount, err := cells[headers["真实金额消费总计"]].Float()
				if err != nil {
					log.L(ctx).Errorf("parse ucloud billing file error: %s", err.Error())

					return err
				}

				ucloudBilling = append(ucloudBilling, &model.CloudBillingDomestic{
					CreatedAt:           time.Now(),
					CloudProvider:       UCLOUD,
					BillingMonth:        yearMonth,
					Project:             proj,
					ResourceProductLine: resourProjLine,
					InvoiceAmount:       invoiceAmount,
				})
			}
		}
	}

	// save db
	err = cbs.store.CloudBilling().SaveDomesticByMonth(ucloudBilling, yearMonth, UCLOUD)
	if err != nil {
		log.L(ctx).Errorf("save ucloud billing file error: %s", err.Error())

		return err
	}

	log.L(ctx).Infof("end parse ucloud billing file")

	return nil
}

func (cbs *cloudBillingService) parseAccount(ctx context.Context, yearMonth string, file *multipart.FileHeader) error {
	log.L(ctx).Infof("start parse account file")

	// check file name
	if !strings.Contains(strings.ToLower(file.Filename), "revenue_march") {
		log.L(ctx).Errorf("file is not account file:%s", file.Filename)

		return errors.New("file is not account file")
	}

	xFile, err := resolveExcel(ctx, file)
	if err != nil {
		log.L(ctx).Errorf("resolve excel error: %s", err.Error())

		return err
	}

	cloudAccounts := make([]*model.CloudRevrecAccount, 0)
	for _, sheet := range xFile.Sheets {
		if strings.Contains(sheet.Name, "AWS") || strings.Contains(sheet.Name, "GCP") {
			headers := make(map[string]int)
			for i, row := range sheet.Rows {
				if i == 0 {
					for j, cell := range row.Cells {
						col := cell.String()
						trimCol := strings.ReplaceAll(col, " ", "")
						headers[trimCol] = j
					}
					continue
				}
				cells := row.Cells
				if (cells[0] == nil || trimSpace(cells[0].String()) == "") && (cells[1] == nil || trimSpace(cells[1].String()) == "") {
					break
				}
				orgID := trimSpace(cells[headers["CloudOrgID"]].String())
				accountID := trimSpace(cells[headers["AccountID"]].String())
				orderNumber := trimSpace(cells[headers["OrderNumber"]].String())
				customerName := trimSpace(cells[headers["CustomerName"]].String())
				cloud := GCP
				if strings.Contains(sheet.Name, "AWS") {
					cloud = AWS
				}

				cloudAccounts = append(cloudAccounts, &model.CloudRevrecAccount{
					CreatedAt:     time.Now(),
					CloudProvider: cloud,
					BillingMonth:  yearMonth,
					OrgID:         orgID,
					AccountID:     accountID,
					OrderNumber:   orderNumber,
					AccountName:   customerName,
				})
			}
		}
	}

	// save db
	err = cbs.store.CloudBilling().SaveAccountByMonth(cloudAccounts, yearMonth)
	if err != nil {
		log.L(ctx).Errorf("save account file error: %s", err.Error())

		return err
	}

	log.L(ctx).Infof("end parse account file")

	return nil
}

func resolveExcel(ctx context.Context, file *multipart.FileHeader) (*xlsx.File, error) {
	log.L(ctx).Infof("start resolve excel")

	ofile, err := file.Open()
	if err != nil {
		log.L(ctx).Errorf("open file error: %s", err.Error())

		return nil, err
	}

	xlFile, err := xlsx.OpenReaderAt(ofile, file.Size)
	if err != nil {
		log.L(ctx).Errorf("open excel error: %s", err.Error())

		return nil, err
	}
	log.L(ctx).Infof("end resolve excel")

	return xlFile, nil
}

// 2021-01
func lastMonth() string {
	now := time.Now()
	year, month, _ := now.Date()
	if month == 1 {
		return fmt.Sprintf("%d-%02d", year-1, 12)
	}
	return fmt.Sprintf("%d-%02d", year, month-1)
}

func trimSpace(str string) string {
	return strings.Trim(str, " ")
}

type ListData struct {
	Items interface{} `json:"items"`
	Total int64       `json:"total"`
}

func (cbs *cloudBillingService) GetCloudBillingList(ctx context.Context, list model.CloudBillingList) (ListData, error) {
	log.L(ctx).Infof("start get cloud billing list")

	offset := GetPage(list.Page, list.PageSize)
	var data interface{}
	var total int64
	var err error
	switch list.PageType {
	case OVERSEA:
		data, total, err = cbs.store.CloudBilling().ListOverSeaByMonth(list.CloudProvider, list.Month, offset, list.PageSize)
		if err != nil {
			log.L(ctx).Errorf("get cloud billing list error: %s", err.Error())

			return ListData{}, err
		}
	case DOMESTIC:
		data, total, err = cbs.store.CloudBilling().ListDomesticByMonth(list.CloudProvider, list.Month, offset, list.PageSize)
		if err != nil {
			log.L(ctx).Errorf("get cloud billing list error: %s", err.Error())

			return ListData{}, err
		}
	case ACCOUNTS:
		data, total, err = cbs.store.CloudBilling().ListAccountByMonth(list.CloudProvider, list.Month, offset, list.PageSize)
		if err != nil {
			log.L(ctx).Errorf("get cloud billing list error: %s", err.Error())

			return ListData{}, err
		}
	default:
		log.L(ctx).Errorf("not supported")

		return ListData{}, errors.New("not supported")
	}

	log.L(ctx).Infof("end get cloud billing list")

	return ListData{
		Items: data,
		Total: total,
	}, nil
}

func GetPage(page int, pageSize int) int {
	result := 0
	if page > 0 && pageSize > 0 {
		result = (page - 1) * pageSize
	}

	return result
}