package importer

import (
	"bytes"
	"context"
	"encoding/json"
	"fmt"
	"io"
	"mime/multipart"
	"strconv"
	"strings"
	"time"

	"cockpit/internal/domain"

	"github.com/shopspring/decimal"
	"github.com/xuri/excelize/v2"
	"gorm.io/gorm"
)

const OrdersSheetName = "订单导入"

// 生成 Excel 模板（xlsx bytes）
func BuildOrdersTemplate() ([]byte, error) {
	f := excelize.NewFile()
	defer func() { _ = f.Close() }()

	sheet := OrdersSheetName
	f.SetSheetName("Sheet1", sheet)

	headers := []string{"订单号", "下单日期", "出货日期", "下单金额", "订单金额", "客户名", "备注", "状态"}
	for i, h := range headers {
		cell, _ := excelize.CoordinatesToCellName(i+1, 1)
		_ = f.SetCellValue(sheet, cell, h)
	}
	// 示例行（可删）
	example := []any{"A20260401001", "2026-04-01", "2026-04-05", "1234.50", "1200.00", "示例客户", "备注示例", "已出货"}
	for i, v := range example {
		cell, _ := excelize.CoordinatesToCellName(i+1, 2)
		_ = f.SetCellValue(sheet, cell, v)
	}

	_ = f.SetColWidth(sheet, "A", "H", 18)

	buf, err := f.WriteToBuffer()
	if err != nil {
		return nil, err
	}
	return buf.Bytes(), nil
}

type OrderInput struct {
	OrderNo      string `json:"order_no"`
	OrderDate    string `json:"order_date"`
	ShipDate     string `json:"ship_date"`
	AmountA      string `json:"amount_a"`
	AmountB      string `json:"amount_b"`
	CustomerName string `json:"customer_name"`
	Remark       string `json:"remark"`
	StatusName   string `json:"status_name"`
}

type OrderClean struct {
	OrderNo      string
	OrderDate    time.Time
	ShipDate     *time.Time
	AmountA      decimal.Decimal
	AmountB      decimal.Decimal
	CustomerID   uint64
	StatusID     uint64
	Remark       string
	CustomerName string // 仅用于预览回显
	StatusName   string // 仅用于预览回显
}

type RowError struct {
	RowNo   int    `json:"rowNo"`
	Field   string `json:"field"`
	Message string `json:"message"`
	RawData any    `json:"rawData,omitempty"`
}

func ParseOrdersExcel(ctx context.Context, db *gorm.DB, file multipart.File, previewLimit int) (total int, preview []OrderClean, errors []RowError, err error) {
	data, err := io.ReadAll(file)
	if err != nil {
		return 0, nil, nil, err
	}
	f, err := excelize.OpenReader(bytes.NewReader(data))
	if err != nil {
		return 0, nil, nil, err
	}
	defer func() { _ = f.Close() }()

	rows, err := f.GetRows(OrdersSheetName)
	if err != nil || len(rows) == 0 {
		// 兼容用户把 sheet 名改掉：取第一个 sheet
		names := f.GetSheetList()
		if len(names) == 0 {
			return 0, nil, nil, fmt.Errorf("Excel 无可用 sheet")
		}
		rows, err = f.GetRows(names[0])
		if err != nil {
			return 0, nil, nil, err
		}
	}
	if len(rows) < 2 {
		return 0, nil, nil, fmt.Errorf("Excel 至少需要表头+1行数据")
	}

	header := rows[0]
	idx := headerIndex(header)

	// 缓存客户/状态映射
	customerMap, err := loadCustomerMap(ctx, db)
	if err != nil {
		return 0, nil, nil, err
	}
	statusMap, err := loadStatusMap(ctx, db)
	if err != nil {
		return 0, nil, nil, err
	}

	for r := 1; r < len(rows); r++ {
		total++
		raw := rowToOrderInput(rows[r], idx)
		clean, rowErrs := cleanOneOrder(raw, r+1, customerMap, statusMap)
		if len(rowErrs) > 0 {
			errors = append(errors, rowErrs...)
		} else if len(preview) < previewLimit {
			preview = append(preview, clean)
		}
	}
	return total, preview, errors, nil
}

func ParseOrdersJSON(ctx context.Context, db *gorm.DB, body []byte, previewLimit int) (total int, preview []OrderClean, errors []RowError, err error) {
	var inputs []OrderInput
	if err := json.Unmarshal(body, &inputs); err != nil {
		return 0, nil, nil, err
	}
	customerMap, err := loadCustomerMap(ctx, db)
	if err != nil {
		return 0, nil, nil, err
	}
	statusMap, err := loadStatusMap(ctx, db)
	if err != nil {
		return 0, nil, nil, err
	}
	for i, in := range inputs {
		total++
		clean, rowErrs := cleanOneOrder(in, i+1, customerMap, statusMap)
		if len(rowErrs) > 0 {
			errors = append(errors, rowErrs...)
		} else if len(preview) < previewLimit {
			preview = append(preview, clean)
		}
	}
	return total, preview, errors, nil
}

func CommitOrders(ctx context.Context, db *gorm.DB, inputs []OrderClean, createdBy uint64, inputFmt, fileName string) (*domain.ImportJob, error) {
	now := time.Now()
	job := domain.ImportJob{
		Type:      "orders",
		InputFmt:  inputFmt,
		Status:    "running",
		FileName:  fileName,
		TotalRows: len(inputs),
		StartedAt: &now,
		CreatedBy: createdBy,
	}
	if err := db.WithContext(ctx).Create(&job).Error; err != nil {
		return nil, err
	}

	ok := 0
	fail := 0
	for _, in := range inputs {
		order := domain.Order{
			OrderNo:    in.OrderNo,
			OrderDate:  in.OrderDate,
			ShipDate:   in.ShipDate,
			AmountA:    in.AmountA,
			AmountB:    in.AmountB,
			CustomerID: in.CustomerID,
			StatusID:   in.StatusID,
			Remark:     in.Remark,
			Source:     "import",
			CreatedBy:  createdBy,
		}
		if err := db.WithContext(ctx).Create(&order).Error; err != nil {
			fail++
			_ = db.WithContext(ctx).Create(&domain.ImportJobError{
				JobID:   job.ID,
				RowNo:   0,
				Field:   "order_no",
				Message: "入库失败：" + err.Error(),
				RawData: "",
			}).Error
			continue
		}
		ok++
	}

	job.OKRows = ok
	job.FailRows = fail
	finished := time.Now()
	job.FinishedAt = &finished
	if fail == 0 {
		job.Status = "success"
	} else if ok == 0 {
		job.Status = "failed"
	} else {
		job.Status = "partial"
	}
	_ = db.WithContext(ctx).Save(&job).Error
	return &job, nil
}

// CommitOrdersFromExcel：解析 Excel 并直接落库，返回 import_job
func CommitOrdersFromExcel(ctx context.Context, db *gorm.DB, file multipart.File, createdBy uint64, fileName string) (*domain.ImportJob, error) {
	data, err := io.ReadAll(file)
	if err != nil {
		return nil, err
	}
	f, err := excelize.OpenReader(bytes.NewReader(data))
	if err != nil {
		return nil, err
	}
	defer func() { _ = f.Close() }()

	rows, err := f.GetRows(OrdersSheetName)
	if err != nil || len(rows) == 0 {
		names := f.GetSheetList()
		if len(names) == 0 {
			return nil, fmt.Errorf("Excel 无可用 sheet")
		}
		rows, err = f.GetRows(names[0])
		if err != nil {
			return nil, err
		}
	}
	if len(rows) < 2 {
		return nil, fmt.Errorf("Excel 至少需要表头+1行数据")
	}

	customerMap, err := loadCustomerMap(ctx, db)
	if err != nil {
		return nil, err
	}
	statusMap, err := loadStatusMap(ctx, db)
	if err != nil {
		return nil, err
	}

	now := time.Now()
	job := domain.ImportJob{
		Type:      "orders",
		InputFmt:  "excel",
		Status:    "running",
		FileName:  fileName,
		StartedAt: &now,
		CreatedBy: createdBy,
	}
	if err := db.WithContext(ctx).Create(&job).Error; err != nil {
		return nil, err
	}

	idx := headerIndex(rows[0])
	seen := map[string]bool{}

	total := 0
	ok := 0
	fail := 0
	for r := 1; r < len(rows); r++ {
		total++
		raw := rowToOrderInput(rows[r], idx)
		rawErrs := []RowError{}
		if strings.TrimSpace(raw.OrderNo) != "" && seen[strings.TrimSpace(raw.OrderNo)] {
			rawErrs = append(rawErrs, RowError{RowNo: r + 1, Field: "order_no", Message: "文件内订单号重复", RawData: raw})
		}
		seen[strings.TrimSpace(raw.OrderNo)] = true

		clean, errs := cleanOneOrder(raw, r+1, customerMap, statusMap)
		errs = append(errs, rawErrs...)
		if len(errs) > 0 {
			fail++
			for _, e := range errs {
				b, _ := json.Marshal(e.RawData)
				_ = db.WithContext(ctx).Create(&domain.ImportJobError{
					JobID:   job.ID,
					RowNo:   e.RowNo,
					Field:   e.Field,
					Message: e.Message,
					RawData: string(b),
				}).Error
			}
			continue
		}

		// DB 去重
		var exists int64
		_ = db.WithContext(ctx).Model(&domain.Order{}).Where("order_no = ?", clean.OrderNo).Count(&exists).Error
		if exists > 0 {
			fail++
			_ = db.WithContext(ctx).Create(&domain.ImportJobError{
				JobID:   job.ID,
				RowNo:   r + 1,
				Field:   "order_no",
				Message: "订单号已存在（与数据库冲突）",
				RawData: "",
			}).Error
			continue
		}

		order := domain.Order{
			OrderNo:    clean.OrderNo,
			OrderDate:  clean.OrderDate,
			ShipDate:   clean.ShipDate,
			AmountA:    clean.AmountA,
			AmountB:    clean.AmountB,
			CustomerID: clean.CustomerID,
			StatusID:   clean.StatusID,
			Remark:     clean.Remark,
			Source:     "import",
			CreatedBy:  createdBy,
		}
		if err := db.WithContext(ctx).Create(&order).Error; err != nil {
			fail++
			_ = db.WithContext(ctx).Create(&domain.ImportJobError{
				JobID:   job.ID,
				RowNo:   r + 1,
				Field:   "order_no",
				Message: "入库失败：" + err.Error(),
				RawData: "",
			}).Error
			continue
		}
		ok++
	}

	job.TotalRows = total
	job.OKRows = ok
	job.FailRows = fail
	finished := time.Now()
	job.FinishedAt = &finished
	if fail == 0 {
		job.Status = "success"
	} else if ok == 0 {
		job.Status = "failed"
	} else {
		job.Status = "partial"
	}
	_ = db.WithContext(ctx).Save(&job).Error
	return &job, nil
}

// CommitOrdersFromJSON：解析 JSON 并直接落库，返回 import_job
func CommitOrdersFromJSON(ctx context.Context, db *gorm.DB, body []byte, createdBy uint64) (*domain.ImportJob, error) {
	var inputs []OrderInput
	if err := json.Unmarshal(body, &inputs); err != nil {
		return nil, err
	}
	customerMap, err := loadCustomerMap(ctx, db)
	if err != nil {
		return nil, err
	}
	statusMap, err := loadStatusMap(ctx, db)
	if err != nil {
		return nil, err
	}

	now := time.Now()
	job := domain.ImportJob{
		Type:      "orders",
		InputFmt:  "json",
		Status:    "running",
		StartedAt: &now,
		CreatedBy: createdBy,
		TotalRows: len(inputs),
	}
	if err := db.WithContext(ctx).Create(&job).Error; err != nil {
		return nil, err
	}

	seen := map[string]bool{}
	ok := 0
	fail := 0
	for i, raw := range inputs {
		rowNo := i + 1
		rawErrs := []RowError{}
		if strings.TrimSpace(raw.OrderNo) != "" && seen[strings.TrimSpace(raw.OrderNo)] {
			rawErrs = append(rawErrs, RowError{RowNo: rowNo, Field: "order_no", Message: "JSON 内订单号重复", RawData: raw})
		}
		seen[strings.TrimSpace(raw.OrderNo)] = true

		clean, errs := cleanOneOrder(raw, rowNo, customerMap, statusMap)
		errs = append(errs, rawErrs...)
		if len(errs) > 0 {
			fail++
			for _, e := range errs {
				b, _ := json.Marshal(e.RawData)
				_ = db.WithContext(ctx).Create(&domain.ImportJobError{
					JobID:   job.ID,
					RowNo:   e.RowNo,
					Field:   e.Field,
					Message: e.Message,
					RawData: string(b),
				}).Error
			}
			continue
		}
		var exists int64
		_ = db.WithContext(ctx).Model(&domain.Order{}).Where("order_no = ?", clean.OrderNo).Count(&exists).Error
		if exists > 0 {
			fail++
			_ = db.WithContext(ctx).Create(&domain.ImportJobError{
				JobID:   job.ID,
				RowNo:   rowNo,
				Field:   "order_no",
				Message: "订单号已存在（与数据库冲突）",
				RawData: "",
			}).Error
			continue
		}

		order := domain.Order{
			OrderNo:    clean.OrderNo,
			OrderDate:  clean.OrderDate,
			ShipDate:   clean.ShipDate,
			AmountA:    clean.AmountA,
			AmountB:    clean.AmountB,
			CustomerID: clean.CustomerID,
			StatusID:   clean.StatusID,
			Remark:     clean.Remark,
			Source:     "import",
			CreatedBy:  createdBy,
		}
		if err := db.WithContext(ctx).Create(&order).Error; err != nil {
			fail++
			_ = db.WithContext(ctx).Create(&domain.ImportJobError{
				JobID:   job.ID,
				RowNo:   rowNo,
				Field:   "order_no",
				Message: "入库失败：" + err.Error(),
				RawData: "",
			}).Error
			continue
		}
		ok++
	}

	job.OKRows = ok
	job.FailRows = fail
	finished := time.Now()
	job.FinishedAt = &finished
	if fail == 0 {
		job.Status = "success"
	} else if ok == 0 {
		job.Status = "failed"
	} else {
		job.Status = "partial"
	}
	_ = db.WithContext(ctx).Save(&job).Error
	return &job, nil
}

// ---- helpers ----

func headerIndex(header []string) map[string]int {
	idx := map[string]int{}
	for i, h := range header {
		h = strings.TrimSpace(h)
		if h == "" {
			continue
		}
		idx[h] = i
	}
	return idx
}

func rowToOrderInput(row []string, idx map[string]int) OrderInput {
	get := func(name string) string {
		i, ok := idx[name]
		if !ok || i >= len(row) {
			return ""
		}
		return strings.TrimSpace(row[i])
	}
	return OrderInput{
		OrderNo:      get("订单号"),
		OrderDate:    get("下单日期"),
		ShipDate:     get("出货日期"),
		AmountA:      get("下单金额"),
		AmountB:      get("订单金额"),
		CustomerName: get("客户名"),
		Remark:       get("备注"),
		StatusName:   get("状态"),
	}
}

func cleanOneOrder(in OrderInput, rowNo int, customerMap map[string]domain.Customer, statusMap map[string]domain.Status) (OrderClean, []RowError) {
	var errs []RowError
	trim := func(s string) string { return strings.TrimSpace(strings.ReplaceAll(s, "\u3000", " ")) }

	in.OrderNo = trim(in.OrderNo)
	in.CustomerName = trim(in.CustomerName)
	in.StatusName = trim(in.StatusName)
	in.AmountA = trim(in.AmountA)
	in.AmountB = trim(in.AmountB)
	in.OrderDate = trim(in.OrderDate)
	in.ShipDate = trim(in.ShipDate)

	if in.OrderNo == "" {
		errs = append(errs, RowError{RowNo: rowNo, Field: "order_no", Message: "订单号必填", RawData: in})
	}
	if in.OrderDate == "" {
		errs = append(errs, RowError{RowNo: rowNo, Field: "order_date", Message: "下单日期必填", RawData: in})
	}
	if in.CustomerName == "" {
		errs = append(errs, RowError{RowNo: rowNo, Field: "customer_name", Message: "客户名必填", RawData: in})
	}
	if in.StatusName == "" {
		errs = append(errs, RowError{RowNo: rowNo, Field: "status_name", Message: "状态必填", RawData: in})
	}

	orderDate, err := parseDate(in.OrderDate)
	if err != nil {
		errs = append(errs, RowError{RowNo: rowNo, Field: "order_date", Message: "下单日期格式错误", RawData: in})
	}
	var shipDate *time.Time
	if in.ShipDate != "" {
		t, err := parseDate(in.ShipDate)
		if err != nil {
			errs = append(errs, RowError{RowNo: rowNo, Field: "ship_date", Message: "出货日期格式错误", RawData: in})
		} else {
			shipDate = &t
		}
	}

	a, err := parseDecimal(in.AmountA)
	if err != nil || a.IsNegative() {
		errs = append(errs, RowError{RowNo: rowNo, Field: "amount_a", Message: "下单金额格式错误", RawData: in})
	}
	b, err := parseDecimal(in.AmountB)
	if err != nil || b.IsNegative() {
		errs = append(errs, RowError{RowNo: rowNo, Field: "amount_b", Message: "订单金额格式错误", RawData: in})
	}

	cust, ok := customerMap[in.CustomerName]
	if !ok {
		errs = append(errs, RowError{RowNo: rowNo, Field: "customer_name", Message: "客户不存在，请先在系统维护客户："+in.CustomerName, RawData: in})
	}
	st, ok := statusMap[in.StatusName]
	if !ok {
		errs = append(errs, RowError{RowNo: rowNo, Field: "status_name", Message: "状态不存在，请先在系统维护状态："+in.StatusName, RawData: in})
	}

	if len(errs) > 0 {
		return OrderClean{}, errs
	}
	return OrderClean{
		OrderNo:      in.OrderNo,
		OrderDate:    orderDate,
		ShipDate:     shipDate,
		AmountA:      a.Round(2),
		AmountB:      b.Round(2),
		CustomerID:   cust.ID,
		StatusID:     st.ID,
		Remark:       in.Remark,
		CustomerName: cust.Name,
		StatusName:   st.Name,
	}, nil
}

func parseDecimal(s string) (decimal.Decimal, error) {
	s = strings.ReplaceAll(s, ",", "")
	s = strings.TrimSpace(s)
	if s == "" {
		return decimal.Zero, fmt.Errorf("empty")
	}
	return decimal.NewFromString(s)
}

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

	// 支持 Excel serial（整数字符串）
	if i, err := strconv.Atoi(s); err == nil && i > 20000 {
		return excelize.ExcelDateToTime(float64(i), false)
	}

	layouts := []string{"2006-01-02", "2006/01/02", "2006.01.02"}
	for _, layout := range layouts {
		if t, err := time.Parse(layout, s); err == nil {
			return t, nil
		}
	}
	return time.Time{}, fmt.Errorf("invalid date")
}

func loadCustomerMap(ctx context.Context, db *gorm.DB) (map[string]domain.Customer, error) {
	var list []domain.Customer
	if err := db.WithContext(ctx).Find(&list).Error; err != nil {
		return nil, err
	}
	m := make(map[string]domain.Customer, len(list))
	for _, c := range list {
		m[strings.TrimSpace(c.Name)] = c
		if strings.TrimSpace(c.Code) != "" {
			m[strings.TrimSpace(c.Code)] = c
		}
	}
	return m, nil
}

func loadStatusMap(ctx context.Context, db *gorm.DB) (map[string]domain.Status, error) {
	var list []domain.Status
	if err := db.WithContext(ctx).Find(&list).Error; err != nil {
		return nil, err
	}
	m := make(map[string]domain.Status, len(list))
	for _, s := range list {
		m[strings.TrimSpace(s.Name)] = s
	}
	return m, nil
}