package cmd

import (
	"fmt"
	"github.com/mizuki1412/go-core-kit/init/initkit"
	"github.com/mizuki1412/go-core-kit/service/configkit"
	"github.com/spf13/cobra"
	"github.com/xuri/excelize/v2"
	"regexp"
	"strconv"
	"strings"
	"time"
)

func init() {
	rootCmd.AddCommand(gcpCmd)
	defFlagsGcp(gcpCmd)
}

var gcpCmd = &cobra.Command{
	Use:   "gcp",
	Short: "Generate construction plan",
	Run: func(cmd *cobra.Command, args []string) {
		initkit.BindFlags(cmd)
		templatePath := configkit.GetString("gcp.template", "/Users/leo/Desktop/归档/公众扩容/在途工单通报/模板.xlsx")
		filePath1 := configkit.GetString("gcp.file1", "/Users/leo/Downloads/宽带在途工单进展反馈表.xlsx")
		filePath2 := configkit.GetString("gcp.file2", "/Users/leo/Downloads/扩容需求表.xlsx")
		_ = ProcessExcelData(filePath1, filePath2, templatePath)
	},
}

func defFlagsGcp(cmd *cobra.Command) {
	cmd.Flags().String("gcp.template", "", "*Specify the template used to generate construction plan")
	cmd.Flags().String("gcp.file1", "", "*Specify the file1 used to generate construction plan")
	cmd.Flags().String("gcp.file2", "", "*Specify the file2 used to generate construction plan")
}

// ====================== 对外入口 ======================

func ProcessExcelData(broadbandPath string, expansionPath string, templatePath string) error {
	template, err := excelize.OpenFile(templatePath)
	if err != nil {
		return err
	}
	defer template.Close()
	dateFmt := "yyyy年mm月dd日"
	dateStyle, err := template.NewStyle(&excelize.Style{
		CustomNumFmt: &dateFmt,
	})
	if err != nil {
		return err
	}
	templateSheet := template.GetSheetName(0)
	writeRow := 2 // 模板从第2行写
	today := time.Now()

	// ---------------- 宽带在途工单 ----------------
	if err := processBroadband(template, templateSheet, broadbandPath, &writeRow, today, dateStyle); err != nil {
		return err
	}

	// ---------------- 扩容需求表 ----------------
	if err := processExpansion(template, templateSheet, expansionPath, &writeRow, today, dateStyle); err != nil {
		return err
	}
	filename := time.Now().Format("20060102") + ".xlsx"
	return template.SaveAs("/Users/leo/Desktop/归档/公众扩容/在途工单通报/通报表格/" + filename)
}

// ====================== 宽带在途 ======================

func processBroadband(template *excelize.File, templateSheet string, path string, writeRow *int, today time.Time, dateStyle int) error {

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

	sheet := f.GetSheetName(0)
	rows, err := f.GetRows(sheet)
	if err != nil {
		return err
	}

	reDay := regexp.MustCompile(`(\d+)`)

	for i := 1; i < len(rows); i++ {
		row := rows[i]

		// A列 == 西湖
		if getCell(row, 0) != "西湖" {
			continue
		}
		// O列 == 需建设处理
		if getCell(row, 14) != "需建设处理" {
			continue
		}

		// D列 → 地址
		address := getCell(row, 3)
		if address == "" {
			continue
		}

		// H列 → 填报日期
		dateStr, err := f.GetCellValue(sheet, fmt.Sprintf("H%d", i+1))
		if err != nil || dateStr == "" {
			continue
		}
		reportTime, err := parseExcelTime(dateStr)
		if err != nil {
			continue
		}
		// 写模板
		template.SetCellValue(templateSheet, fmt.Sprintf("A%d", *writeRow), address)
		template.SetCellValue(templateSheet, fmt.Sprintf("F%d", *writeRow), reportTime)
		template.SetCellStyle(
			templateSheet,
			fmt.Sprintf("F%d", *writeRow),
			fmt.Sprintf("F%d", *writeRow),
			dateStyle,
		)
		// S列：X日通
		colS := getCell(row, 18)
		if m := reDay.FindStringSubmatch(colS); len(m) == 2 {
			days, _ := strconv.Atoi(m[1])

			// C列：几日通
			template.SetCellValue(templateSheet, fmt.Sprintf("C%d", *writeRow), days)

			// D列：需完成日期
			finishDate := reportTime.AddDate(0, 0, days)
			template.SetCellValue(templateSheet, fmt.Sprintf("D%d", *writeRow), finishDate)
			template.SetCellStyle(
				templateSheet,
				fmt.Sprintf("D%d", *writeRow),
				fmt.Sprintf("D%d", *writeRow),
				dateStyle,
			)
			// E列：是否超时
			if today.After(finishDate) {
				template.SetCellValue(templateSheet, fmt.Sprintf("E%d", *writeRow), "是")
			} else {
				template.SetCellValue(templateSheet, fmt.Sprintf("E%d", *writeRow), "否")
			}
		}

		// G列：工单历时
		duration := int(today.Sub(reportTime).Hours() / 24)
		if duration < 0 {
			duration = 0
		}
		template.SetCellValue(templateSheet, fmt.Sprintf("G%d", *writeRow), duration)

		*writeRow++
	}

	return nil
}

// ====================== 扩容需求 ======================

func processExpansion(template *excelize.File, templateSheet string, path string, writeRow *int, today time.Time, dateStyle int) error {

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

	sheet := f.GetSheetName(0)
	rows, err := f.GetRows(sheet)
	if err != nil {
		return err
	}

	for i := 1; i < len(rows); i++ {
		row := rows[i]

		// P列为空
		if strings.TrimSpace(getCell(row, 15)) != "" {
			continue
		}
		// X列 == 1
		if getCell(row, 23) != "1" {
			continue
		}

		//D列 → 地址
		address := getCell(row, 3)
		if address == "" {
			continue
		}

		// N列 → 填报日期
		dateStr, err := f.GetCellValue(sheet, fmt.Sprintf("N%d", i+1))
		if err != nil || dateStr == "" {
			continue
		}

		reportTime, err := parseExcelTime(dateStr)
		if err != nil {
			continue
		}

		template.SetCellValue(templateSheet, fmt.Sprintf("A%d", *writeRow), address)
		template.SetCellValue(templateSheet, fmt.Sprintf("F%d", *writeRow), reportTime)
		template.SetCellStyle(
			templateSheet,
			fmt.Sprintf("F%d", *writeRow),
			fmt.Sprintf("F%d", *writeRow),
			dateStyle,
		)
		// G列：工单历时
		duration := int(today.Sub(reportTime).Hours() / 24)
		if duration < 0 {
			duration = 0
		}
		template.SetCellValue(templateSheet, fmt.Sprintf("G%d", *writeRow), duration)

		*writeRow++
	}

	return nil
}

// ====================== 工具函数 ======================

func parseExcelTime(val string) (time.Time, error) {
	val = strings.TrimSpace(val)
	if val == "" {
		return time.Time{}, fmt.Errorf("empty time")
	}

	// 1️⃣ Excel 原生数值（最优先）
	if f, err := strconv.ParseFloat(val, 64); err == nil {
		return excelize.ExcelDateToTime(f, false)
	}

	// 2️⃣ 中文日期：11月23日 / 11月23号
	reCN := regexp.MustCompile(`^(\d{1,2})月(\d{1,2})([日号])?$`)
	if m := reCN.FindStringSubmatch(val); len(m) > 0 {
		month, _ := strconv.Atoi(m[1])
		day, _ := strconv.Atoi(m[2])
		//todo
		year := 0
		if month >= 11 {
			year = 2025 // 默认用当前年
		}
		year = time.Now().Year() // 默认用当前年
		return time.Date(year, time.Month(month), day, 0, 0, 0, 0, time.Local), nil
	}

	// 3️⃣ 常见字符串格式
	layouts := []string{
		"2006-01-02",
		"2006/01/02",
		"2006-01-02 15:04",
		"2006/01/02 15:04",
		"2006-01-02 15:04:05",
		"2006/01/02 15:04:05",
	}

	for _, layout := range layouts {
		if t, err := time.ParseInLocation(layout, val, time.Local); err == nil {
			return t, nil
		}
	}

	return time.Time{}, fmt.Errorf("cannot parse time: %s", val)
}

// 安全取单元格
func getCell(row []string, idx int) string {
	if idx >= len(row) {
		return ""
	}
	return strings.TrimSpace(row[idx])
}