package bbxt

import (
	"fmt"
	"reflect"
	"regexp"
	"sort"
	"strings"

	"github.com/go-kratos/kratos/v2/log"
	"github.com/shopspring/decimal"
	"github.com/xuri/excelize/v2"
)

func (b *BbxtTools) SimpleFillExcelWithTitle(templatePath, outputPath string, dataSlice interface{}, title string) error {
	// 打开模板

	f, err := excelize.OpenFile(templatePath)
	if err != nil {
		return err
	}
	defer f.Close()

	sheet := f.GetSheetName(0)
	startLen := 2
	if len(title) > 0 {
		// 写入标题
		f.SetCellValue(sheet, "A1", title)
		startLen = 3
	}
	// 获取模板样式
	templateRow := startLen
	styleID, err := f.GetCellStyle(sheet, fmt.Sprintf("A%d", templateRow))
	if err != nil {
		log.Errorf("获取模板样式失败: %v", err)
		styleID = 0
	}

	// 获取模板行高
	rowHeight, err := f.GetRowHeight(sheet, templateRow)
	if err != nil {
		log.Errorf("获取模板行高失败: %v", err)
		rowHeight = 31 // 默认高度
	}
	rowWidth := 25.00
	// 反射获取切片数据
	v := reflect.ValueOf(dataSlice)
	if v.Kind() != reflect.Slice {
		return fmt.Errorf("dataSlice must be a slice")
	}

	if v.Len() == 0 {
		return nil
	}

	// 从第三行开始填充数据（第二行留空或作为标题行）
	startRow := startLen
	pattern := `\$\{(.*?)\}`
	re := regexp.MustCompile(pattern)
	for i := 0; i < v.Len(); i++ {
		currentRow := startRow + i

		// 获取当前行数据
		item := v.Index(i)

		// 处理不同类型的切片
		var rowData []interface{}

		if item.Kind() == reflect.Slice || item.Kind() == reflect.Array {
			// 处理 []string 或 [][]string 中的一行
			for j := 0; j < item.Len(); j++ {
				if item.Index(j).CanInterface() {
					rowData = append(rowData, item.Index(j).Interface())
				}
			}
		} else if item.Kind() == reflect.Interface {
			// 处理 interface{} 类型
			if actualValue, ok := item.Interface().([]string); ok {
				for _, val := range actualValue {
					rowData = append(rowData, val)
				}
			} else {
				rowData = []interface{}{item.Interface()}
			}
		} else {
			rowData = []interface{}{item.Interface()}
		}

		// 4.1 设置行高
		f.SetRowHeight(sheet, currentRow, rowHeight)

		// 应用模板样式到整行（根据实际列数）
		if styleID != 0 && len(rowData) > 0 {
			startCol := "A"
			endCol := fmt.Sprintf("%c", 'A'+len(rowData)-1)
			endCell := fmt.Sprintf("%s%d", endCol, currentRow)

			f.SetCellStyle(sheet, fmt.Sprintf("%s%d", startCol, currentRow),
				endCell, styleID)
		}
		// 填充数据到Excel
		for col, value := range rowData {
			cell := fmt.Sprintf("%c%d", 'A'+col, currentRow)
			f.SetColWidth(sheet, cell, cell, rowWidth)
			switch value.(type) {
			case string:
				var style = value.(string)
				if re.MatchString(style) {
					matches := re.FindStringSubmatch(style)
					styleMap := make(map[string]string)
					//matches = strings.Replace(matches, "$", "", 1)
					if len(matches) != 2 {
						continue
					}
					for _, kv := range strings.Split(matches[1], ";") {
						kvParts := strings.Split(kv, ":")
						if len(kvParts) == 2 {
							styleMap[strings.TrimSpace(kvParts[0])] = strings.TrimSpace(kvParts[1])
						}
					}
					fontStyleID, _err := SetStyle(styleMap, f)
					if _err == nil {
						f.SetCellStyle(sheet, cell, cell, fontStyleID)
					}

					value = re.ReplaceAllString(style, "")

				}
				f.SetCellValue(sheet, cell, value)
			default:

			}
		}

	}
	// 保存
	return f.SaveAs(outputPath)
}

func SetStyle(styleMap map[string]string, f *excelize.File) (int, error) {

	var style = &excelize.Style{}
	// 设置字体颜色
	if colorHex, exists := styleMap["color"]; exists {
		style.Font = &excelize.Font{
			Color:  colorHex,
			Family: "SimHei",
			Bold:   true,
		}
	}
	// 设置水平对齐
	if horizontal, exists := styleMap["horizontal"]; exists {
		if style.Alignment == nil {
			style.Alignment = &excelize.Alignment{}
		}
		style.Alignment.Horizontal = horizontal
	}
	// 设置垂直对齐
	if vertical, exists := styleMap["vertical"]; exists {
		if style.Alignment == nil {
			style.Alignment = &excelize.Alignment{}
		}
		style.Alignment.Vertical = vertical
	}

	// 设置边框（新增）
	if borderColor, exists := styleMap["borderColor"]; exists {
		style.Border = []excelize.Border{
			{Type: "left", Color: borderColor, Style: 1},   // 左边框
			{Type: "right", Color: borderColor, Style: 1},  // 右边框
			{Type: "top", Color: borderColor, Style: 1},    // 上边框
			{Type: "bottom", Color: borderColor, Style: 1}, // 下边框
		}
	}
	return f.NewStyle(style)
}

// 分销商负利润详情填充excel-V2
// 1.使用模板文件作为输出文件，从第二行开始填充
// 2.整体为3列：1.分销商名称（以ResellerName为分组，分销商名称列使用的样式为） 2.商品名称（p.ProductName） 3.亏损金额（p.Loss）
// 3.分销商名称列使用的样式为 A2；商品名称、亏损金额使用的样式为 B2、C2；样式包括宽高、背景、颜色等
// 4.以ResellerName分组，合并单元格
// 5.在文件末尾使用“合计”，合计行样式为模板第四行
// 6.保存为新文件
func (b *BbxtTools) resellerDetailFillExcelV2(templatePath, outputPath string, dataSlice []*ResellerLoss, title string) error {
	// 1. 读取模板
	f, err := excelize.OpenFile(templatePath)
	if err != nil {
		return err
	}
	defer f.Close()

	sheet := f.GetSheetName(0)
	if len(title) > 0 {
		// 写入标题
		f.SetCellValue(sheet, "A1", title)
	}
	// ---------------- 样式获取 ----------------
	// 模板第2行：数据行样式
	tplRowData := 2
	styleA2, err := f.GetCellStyle(sheet, fmt.Sprintf("A%d", tplRowData))
	if err != nil {
		styleA2 = 0
	}
	// B2和C2通常样式一致，这里取B2作为明细列样式
	styleB2, err := f.GetCellStyle(sheet, fmt.Sprintf("B%d", tplRowData))
	if err != nil {
		styleB2 = 0
	}
	styleC2, err := f.GetCellStyle(sheet, fmt.Sprintf("C%d", tplRowData))
	if err != nil {
		styleC2 = 0
	}

	rowHeightData, err := f.GetRowHeight(sheet, tplRowData)
	if err != nil {
		rowHeightData = 20
	}

	// 模板第4行：合计行样式
	tplRowTotal := 4
	styleTotalA, err := f.GetCellStyle(sheet, fmt.Sprintf("A%d", tplRowTotal))
	if err != nil {
		styleTotalA = 0
	}
	styleTotalB, err := f.GetCellStyle(sheet, fmt.Sprintf("B%d", tplRowTotal))
	if err != nil {
		styleTotalB = 0
	}
	styleTotalC, err := f.GetCellStyle(sheet, fmt.Sprintf("C%d", tplRowTotal))
	if err != nil {
		styleTotalC = 0
	}
	rowHeightTotal, err := f.GetRowHeight(sheet, tplRowTotal)
	if err != nil {
		rowHeightTotal = 30
	}
	// ----------------------------------------

	currentRow := 2
	totalLoss := 0.0

	for _, reseller := range dataSlice {
		// 排序 ProductLoss
		var products []*ProductLoss
		for _, p := range reseller.ProductLoss {
			products = append(products, p)
		}
		sort.Slice(products, func(i, j int) bool {
			return products[i].Loss < products[j].Loss
		})

		startRow := currentRow

		// 填充该经销商的所有产品
		for _, p := range products {
			// 设置行高
			f.SetRowHeight(sheet, currentRow, rowHeightData)

			// 设置值
			f.SetCellValue(sheet, fmt.Sprintf("A%d", currentRow), reseller.ResellerName)
			f.SetCellValue(sheet, fmt.Sprintf("B%d", currentRow), p.ProductName)
			f.SetCellValue(sheet, fmt.Sprintf("C%d", currentRow), p.Loss)

			// 设置样式
			if styleA2 != 0 {
				f.SetCellStyle(sheet, fmt.Sprintf("A%d", currentRow), fmt.Sprintf("A%d", currentRow), styleA2)
			}
			if styleB2 != 0 {
				f.SetCellStyle(sheet, fmt.Sprintf("B%d", currentRow), fmt.Sprintf("B%d", currentRow), styleB2)
			}
			if styleC2 != 0 {
				f.SetCellStyle(sheet, fmt.Sprintf("C%d", currentRow), fmt.Sprintf("C%d", currentRow), styleC2)
			}

			totalLoss += p.Loss
			currentRow++
		}

		endRow := currentRow - 1
		// 合并单元格 (如果多于1行)
		if endRow > startRow {
			f.MergeCell(sheet, fmt.Sprintf("A%d", startRow), fmt.Sprintf("A%d", endRow))
		}
	}

	// ---------------- 填充合计行 ----------------
	// 四舍五入保留四位小数
	totalLoss, _ = decimal.NewFromFloat(totalLoss).Round(4).Float64()
	// 设置行高
	f.SetRowHeight(sheet, currentRow, rowHeightTotal)

	f.SetCellValue(sheet, fmt.Sprintf("A%d", currentRow), "合计")
	// B列留空，C列填充总亏损
	f.SetCellValue(sheet, fmt.Sprintf("C%d", currentRow), totalLoss)

	// 设置合计行样式
	if styleTotalA != 0 {
		f.SetCellStyle(sheet, fmt.Sprintf("A%d", currentRow), fmt.Sprintf("A%d", currentRow), styleTotalA)
	}
	if styleTotalB != 0 {
		f.SetCellStyle(sheet, fmt.Sprintf("B%d", currentRow), fmt.Sprintf("B%d", currentRow), styleTotalB)
	}
	if styleTotalC != 0 {
		f.SetCellStyle(sheet, fmt.Sprintf("C%d", currentRow), fmt.Sprintf("C%d", currentRow), styleTotalC)
	}
	// 取消合并合计行的A、B列
	// f.MergeCell(sheet, fmt.Sprintf("A%d", currentRow), fmt.Sprintf("B%d", currentRow))

	// 6. 保存
	return f.SaveAs(outputPath)
}

func (b *BbxtTools) resellerDetailFillExcelAna(templatePath, outputPath string, dataSlice []*ResellerLoss, title string) error {
	// 1. 读取模板
	f, err := excelize.OpenFile(templatePath)
	if err != nil {
		return err
	}
	defer f.Close()

	sheet := f.GetSheetName(0)
	if len(title) > 0 {
		// 写入标题
		f.SetCellValue(sheet, "A1", title)
	}
	// ---------------- 样式获取 ----------------
	// 模板第2行：数据行样式
	tplRowData := 3
	styleA3, err := f.GetCellStyle(sheet, fmt.Sprintf("A%d", tplRowData))
	if err != nil {
		styleA3 = 0
	}
	// B2和C2通常样式一致，这里取B2作为明细列样式
	styleB3, err := f.GetCellStyle(sheet, fmt.Sprintf("B%d", tplRowData))
	if err != nil {
		styleB3 = 0
	}
	styleC3, err := f.GetCellStyle(sheet, fmt.Sprintf("C%d", tplRowData))
	if err != nil {
		styleC3 = 0
	}

	styleD3, err := f.GetCellStyle(sheet, fmt.Sprintf("D%d", tplRowData))
	if err != nil {
		styleD3 = 0
	}

	styleE3, err := f.GetCellStyle(sheet, fmt.Sprintf("E%d", tplRowData))
	if err != nil {
		styleE3 = 0
	}
	styleF3, err := f.GetCellStyle(sheet, fmt.Sprintf("F%d", tplRowData))
	if err != nil {
		styleF3 = 0
	}
	rowHeightData, err := f.GetRowHeight(sheet, tplRowData)
	if err != nil {
		rowHeightData = 20
	}

	// 模板第4行：合计行样式
	tplRowTotal := 5
	styleTotalA, err := f.GetCellStyle(sheet, fmt.Sprintf("A%d", tplRowTotal))
	if err != nil {
		styleTotalA = 0
	}
	styleTotalB, err := f.GetCellStyle(sheet, fmt.Sprintf("B%d", tplRowTotal))
	if err != nil {
		styleTotalB = 0
	}
	styleTotalC, err := f.GetCellStyle(sheet, fmt.Sprintf("C%d", tplRowTotal))
	if err != nil {
		styleTotalC = 0
	}
	styleTotalD, err := f.GetCellStyle(sheet, fmt.Sprintf("D%d", tplRowTotal))
	if err != nil {
		styleTotalD = 0
	}
	styleTotalE, err := f.GetCellStyle(sheet, fmt.Sprintf("E%d", tplRowTotal))
	if err != nil {
		styleTotalE = 0
	}
	styleTotalF, err := f.GetCellStyle(sheet, fmt.Sprintf("F%d", tplRowTotal))
	if err != nil {
		styleTotalF = 0
	}
	rowHeightTotal, err := f.GetRowHeight(sheet, tplRowTotal)
	if err != nil {
		rowHeightTotal = 30
	}
	// ----------------------------------------

	currentRow := 3
	totalLoss := 0.0

	for _, reseller := range dataSlice {
		// 排序 ProductLoss
		var products []*ProductLoss
		for _, p := range reseller.ProductLoss {
			products = append(products, p)
		}
		sort.Slice(products, func(i, j int) bool {
			return products[i].Loss < products[j].Loss
		})

		startRow := currentRow

		// 填充该经销商的所有产品
		for _, p := range products {
			// 设置行高
			f.SetRowHeight(sheet, currentRow, rowHeightData)

			// 设置值
			f.SetCellValue(sheet, fmt.Sprintf("A%d", currentRow), reseller.ResellerName)
			f.SetCellValue(sheet, fmt.Sprintf("B%d", currentRow), p.ProductName)
			f.SetCellValue(sheet, fmt.Sprintf("C%d", currentRow), p.Loss)
			f.SetCellValue(sheet, fmt.Sprintf("D%d", currentRow), reseller.Total)
			f.SetCellValue(sheet, fmt.Sprintf("E%d", currentRow), reseller.Manager)
			f.SetCellValue(sheet, fmt.Sprintf("F%d", currentRow), p.LossReason)
			// 设置样式
			if styleA3 != 0 {
				f.SetCellStyle(sheet, fmt.Sprintf("A%d", currentRow), fmt.Sprintf("A%d", currentRow), styleA3)
			}
			if styleB3 != 0 {
				f.SetCellStyle(sheet, fmt.Sprintf("B%d", currentRow), fmt.Sprintf("B%d", currentRow), styleB3)
			}
			if styleC3 != 0 {
				f.SetCellStyle(sheet, fmt.Sprintf("C%d", currentRow), fmt.Sprintf("C%d", currentRow), styleC3)
			}
			if styleD3 != 0 {
				f.SetCellStyle(sheet, fmt.Sprintf("D%d", currentRow), fmt.Sprintf("D%d", currentRow), styleD3)
			}
			if styleE3 != 0 {
				f.SetCellStyle(sheet, fmt.Sprintf("E%d", currentRow), fmt.Sprintf("E%d", currentRow), styleE3)
			}
			if styleF3 != 0 {
				f.SetCellStyle(sheet, fmt.Sprintf("F%d", currentRow), fmt.Sprintf("F%d", currentRow), styleF3)
			}
			totalLoss += p.Loss
			currentRow++
		}

		endRow := currentRow - 1
		// 合并单元格 (如果多于1行)
		if endRow > startRow {
			f.MergeCell(sheet, fmt.Sprintf("A%d", startRow), fmt.Sprintf("A%d", endRow))
			f.MergeCell(sheet, fmt.Sprintf("D%d", startRow), fmt.Sprintf("D%d", endRow))
			f.MergeCell(sheet, fmt.Sprintf("E%d", startRow), fmt.Sprintf("E%d", endRow))
		}
	}

	// ---------------- 填充合计行 ----------------
	// 四舍五入保留四位小数
	totalLoss, _ = decimal.NewFromFloat(totalLoss).Round(4).Float64()
	// 设置行高
	f.SetRowHeight(sheet, currentRow, rowHeightTotal)

	f.SetCellValue(sheet, fmt.Sprintf("A%d", currentRow), "合计")
	// B列留空，C列填充总亏损
	f.SetCellValue(sheet, fmt.Sprintf("D%d", currentRow), totalLoss)

	// 设置合计行样式
	if styleTotalA != 0 {
		f.SetCellStyle(sheet, fmt.Sprintf("A%d", currentRow), fmt.Sprintf("A%d", currentRow), styleTotalA)
	}
	if styleTotalB != 0 {
		f.SetCellStyle(sheet, fmt.Sprintf("B%d", currentRow), fmt.Sprintf("B%d", currentRow), styleTotalB)
	}
	if styleTotalC != 0 {
		f.SetCellStyle(sheet, fmt.Sprintf("C%d", currentRow), fmt.Sprintf("C%d", currentRow), styleTotalC)
	}
	if styleTotalD != 0 {
		f.SetCellStyle(sheet, fmt.Sprintf("D%d", currentRow), fmt.Sprintf("D%d", currentRow), styleTotalD)
	}
	if styleTotalE != 0 {
		f.SetCellStyle(sheet, fmt.Sprintf("E%d", currentRow), fmt.Sprintf("E%d", currentRow), styleTotalE)
	}
	if styleTotalF != 0 {
		f.SetCellStyle(sheet, fmt.Sprintf("F%d", currentRow), fmt.Sprintf("F%d", currentRow), styleTotalF)
	}
	// 取消合并合计行的A、B列
	// f.MergeCell(sheet, fmt.Sprintf("A%d", currentRow), fmt.Sprintf("B%d", currentRow))

	// 6. 保存
	return f.SaveAs(outputPath)
}
