package service

import (
	"encoding/json"
	"fmt"
	"github.com/shopspring/decimal"
	"github.com/xuri/excelize/v2"
	"strconv"
	"time"
	"wms/constvar"
	"wms/models"
	"wms/request"
	"wms/response"
)

type Detail struct {
	ProductId     string                   `json:"productId"`
	Amount        decimal.Decimal          `json:"amount"`
	Status        constvar.OperationStatus `json:"status"`
	MoreUnitValue string                   `json:"-" gorm:"type:varchar(255);comment:多单位值"`
	MoreUnitList  []models.UnitItems       `json:"moreUnitList" gorm:"-"`
}

type InventoryReportFormsService struct{}

func NewInventoryReportFormsService() *InventoryReportFormsService {
	return &InventoryReportFormsService{}
}

func (slf *InventoryReportFormsService) Query(params request.GetInventoryForms) (result []*response.InventoryForms, err error) {
	var (
		materials = make([]*models.Material, 0)
	)
	search, productIds, productAmounts, locationIds, err := slf.BuildSearch(params)
	if err != nil {
		return nil, err
	}

	if params.Page*params.PageSize > 0 {
		search.Orm = search.Orm.Offset((params.Page - 1) * params.PageSize).Limit(params.PageSize)
	}
	if err := search.Orm.Find(&materials).Error; err != nil {
		return nil, err
	}
	//查询在库数量
	if len(productIds) == 0 {
		for _, material := range materials {
			productIds = append(productIds, material.ID)
		}
		productAmounts, err = models.NewLocationProductAmountSearch().SetProductIds(productIds).SetLocationIds(locationIds).Find()
		if err != nil {
			return nil, err
		}
	}

	//查询出入库就绪数量
	var inHouse []Detail
	var outHouse []Detail
	dbIn := models.NewOperationDetailsSearch().Orm.Model(&models.OperationDetails{}).
		Select("wms_operation_details.product_id,wms_operation_details.amount,wms_operation.status as status, more_unit_value").
		Joins("left join wms_operation ON wms_operation_details.operation_id=wms_operation.id").
		Where("wms_operation.base_operation_type in (?)", []constvar.BaseOperationType{constvar.BaseOperationTypeIncoming, constvar.BaseOperationTypeInternal}).
		Where("wms_operation.status in (?)", []constvar.OperationStatus{constvar.OperationStatus_Finish})
	dbOut := models.NewOperationDetailsSearch().Orm.Model(&models.OperationDetails{}).
		Select("wms_operation_details.product_id,wms_operation_details.amount,wms_operation.status as status, more_unit_value").
		Joins("left join wms_operation ON wms_operation_details.operation_id=wms_operation.id").
		Where("wms_operation.base_operation_type in (?)", []constvar.BaseOperationType{constvar.BaseOperationTypeOutgoing, constvar.BaseOperationTypeInternal, constvar.BaseOperationTypeDisuse}).
		Where("wms_operation.status in (?)", []constvar.OperationStatus{constvar.OperationStatus_Ready, constvar.OperationStatus_Finish})
	if len(locationIds) > 0 {
		dbIn.Where("wms_operation_details.to_location_id in (?)", locationIds)
		dbOut.Where("wms_operation_details.from_location_id in (?)", locationIds)
	}
	if len(productIds) > 0 {
		dbIn.Where("wms_operation_details.product_id in (?)", productIds)
		dbOut.Where("wms_operation_details.product_id in (?)", productIds)
	}
	err = dbIn.Find(&inHouse).Error
	if err != nil {
		return
	}

	for _, in := range inHouse {
		var arr []models.UnitItems
		if len(in.MoreUnitValue) != 0 {
			err := json.Unmarshal([]byte(in.MoreUnitValue), &arr)
			if err != nil {
				return nil, err
			}
			in.MoreUnitList = arr
		}
	}

	err = dbOut.Find(&outHouse).Error
	if err != nil {
		return
	}

	for _, out := range outHouse {
		var arr []models.UnitItems
		if len(out.MoreUnitValue) != 0 {
			err := json.Unmarshal([]byte(out.MoreUnitValue), &arr)
			if err != nil {
				return nil, err
			}
			out.MoreUnitList = arr
		}
	}

	for _, material := range materials {
		data := new(response.InventoryForms)
		data.ProduceId = material.ID
		data.ProductName = material.Name
		data.Cost = material.Cost
		data.Unit = material.Unit
		data.Value = material.Amount.Mul(material.Cost)
		data.ProductCategory = material.CategoryName
		data.ProductType = material.Type
		data.ProductSpecs = material.Specs
		for _, amount := range productAmounts {
			if material.ID == amount.ProductId {
				data.Amount = data.Amount.Add(amount.Amount)
				data.AmountMoreUnits = AddMoreUnit(data.AmountMoreUnits, amount.MoreUnitList)
			}
		}
		for _, details := range inHouse {
			if material.ID == details.ProductId {
				data.In = data.In.Add(details.Amount)
				data.InMoreUnits = AddMoreUnit(data.InMoreUnits, details.MoreUnitList)
			}
		}
		readyOutAmount := decimal.NewFromInt(0)
		readyOutAmountMoreUnitList := make([]models.UnitItems, 0)
		for _, details := range outHouse {
			if material.ID == details.ProductId {
				if details.Status == constvar.OperationStatus_Ready {
					readyOutAmount = readyOutAmount.Add(details.Amount)
					readyOutAmountMoreUnitList = AddMoreUnit(readyOutAmountMoreUnitList, details.MoreUnitList)
				} else {
					data.Out = data.Out.Add(details.Amount)
					data.OutMoreUnits = AddMoreUnit(data.OutMoreUnits, details.MoreUnitList)
				}
			}
		}
		data.AvailableNumber = data.Amount.Sub(readyOutAmount)                                        //可用数量等于库存数量减待出库数量
		data.AvailableNumberMoreUnits = SubMoreUnit(data.AmountMoreUnits, readyOutAmountMoreUnitList) //可用多单位等于剩余数量减待出库数量

		result = append(result, data)
	}
	return
}

func (slf *InventoryReportFormsService) BuildSearch(params request.GetInventoryForms) (
	search *models.MaterialSearch,
	productIds []string,
	productAmounts []*models.LocationProductAmount,
	locationIds []int,
	err error) {
	productAmounts = make([]*models.LocationProductAmount, 0)
	locationSearch := models.NewLocationSearch()
	//if params.WarehouseCode != "" {
	//	locationSearch.SetJointName(params.WarehouseCode)
	//}
	if params.WarehouseId > 0 {
		locationSearch.SetWarehouseId(params.WarehouseId)
	}

	locations, err := locationSearch.FindNotTotal()
	if err != nil {
		return nil, nil, nil, nil, err
	}

	for _, location := range locations {
		locationIds = append(locationIds, location.Id)
	}
	productAmounts, err = models.NewLocationProductAmountSearch().
		SetLocationIds(locationIds).SetQuery("amount > 0").
		Find()
	if err != nil {
		return nil, nil, nil, nil, err
	}
	for _, amount := range productAmounts {
		productIds = append(productIds, amount.ProductId)
	}

	//查询产品
	search = models.NewMaterialSearch()
	search.Orm = search.Orm.Model(&models.Material{}). // material
								Select(`material.id, material.name, material.cost, material.amount, material.unit, material.more_unit, material.more_unit_value, wms_product_category.name as category_name `).
								Joins("left join wms_product_category on material.category_id = wms_product_category.id")
	if len(params.CategoryIds) > 0 {
		search.Orm.Where("material.category_id in (?)", params.CategoryIds)
	}
	if params.KeyWord != "" {
		search.Orm.Where("material.name like ?", "%"+params.KeyWord+"%").Or("wms_product_category.name like ?", "%"+params.KeyWord+"%")
	}
	if len(productIds) > 0 {
		search.Orm.Where("material.id in (?)", productIds)
	}

	return search, productIds, productAmounts, locationIds, nil
}

func (slf *InventoryReportFormsService) Count(params request.GetInventoryForms) (total int64, err error) {
	search, _, _, _, err := slf.BuildSearch(params)
	if err != nil {
		return 0, err
	}
	if err := search.Orm.Count(&total).Error; err != nil {
		return 0, err
	}
	return total, nil
}

func (slf *InventoryReportFormsService) FetchAll(params request.GetInventoryForms) (list []*response.InventoryForms, err error) {
	total, err := slf.Count(params)
	if err != nil {
		return nil, err
	}
	list = make([]*response.InventoryForms, 0, total)
	params.PageSize = 500
	page := 1
	for {
		params.Page = page
		data, err := slf.Query(params)
		if err != nil {
			return nil, err
		}
		if len(data) == 0 {
			break
		}
		list = append(list, data...)
		page++
	}
	return
}

func (slf *InventoryReportFormsService) Export(dataList []*response.InventoryForms) (filename string, err error) {
	var fileName string
	f := excelize.NewFile()

	unitData, unitIndexMap := GetAllUnits()
	// 自定义表头
	headers := []interface{}{"产品", "产品类别", "单位成本", "总价值",
		map[string][]string{"在库": unitData},
		map[string][]string{"可用库存": unitData},
		map[string][]string{"入库": unitData},
		map[string][]string{"出库": unitData}}

	// 设置表头
	lastColumnNumber, err := SetExcelHeaders(headers, f)
	if err != nil {
		return
	}

	titleStyle, err := SetHeaderStyle(f)
	if err != nil {
		return
	}

	lastColumn := getColumnAlphabet(lastColumnNumber)
	f.SetCellStyle("Sheet1", "A1", lastColumn+"2", titleStyle)
	// 设置列宽
	f.SetColWidth("Sheet1", "A", "D", 30)
	f.SetColWidth("Sheet1", "E", lastColumn, 15)

	for i := 1; i <= 4; i++ {
		column := getColumnAlphabet(i)
		f.MergeCell("Sheet1", column+"1", column+"2") // 合并单元格
	}

	for i, v := range dataList {
		rowNumber := i + 3
		column := strconv.Itoa(rowNumber)
		f.SetCellValue("Sheet1", getColumnAlphabet(1)+column, v.ProductName)
		f.SetCellValue("Sheet1", getColumnAlphabet(2)+column, v.ProductType)
		f.SetCellValue("Sheet1", getColumnAlphabet(3)+column, v.Cost)
		f.SetCellValue("Sheet1", getColumnAlphabet(4)+column, v.Value)

		v.AmountMoreUnits = append(v.AmountMoreUnits, models.UnitItems{Amount: v.Amount, Unit: v.Unit})
		FillMoreUnitToExcel(v.AmountMoreUnits, 5, rowNumber, unitIndexMap, f)

		v.AvailableNumberMoreUnits = append(v.AvailableNumberMoreUnits, models.UnitItems{Amount: v.AvailableNumber, Unit: v.Unit})
		FillMoreUnitToExcel(v.AvailableNumberMoreUnits, 5+len(unitData), rowNumber, unitIndexMap, f)

		v.InMoreUnits = append(v.InMoreUnits, models.UnitItems{Amount: v.In, Unit: v.Unit})
		FillMoreUnitToExcel(v.InMoreUnits, 5+len(unitData)*2, rowNumber, unitIndexMap, f)

		v.OutMoreUnits = append(v.OutMoreUnits, models.UnitItems{Amount: v.Out, Unit: v.Unit})
		FillMoreUnitToExcel(v.OutMoreUnits, 5+len(unitData)*3, rowNumber, unitIndexMap, f)
	}

	fileName = fmt.Sprintf("库存报表%s.xlsx", time.Now().Format("2006-01-02-1504"))
	if err := f.SaveAs(fileName); err != nil {
		return fileName, err
	}

	return fileName, nil
}

// 根据索引获取 Excel 列名（A、B、C...）
func getColumnAlphabet(index int) string {
	const alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
	if index <= 26 {
		return string(alphabet[index-1])
	}
	quotient := (index - 1) / 26
	remainder := (index - 1) % 26
	return getColumnAlphabet(quotient) + getColumnAlphabet(remainder+1)
}