package service

import (
	"encoding/json"
	"psi/constant"

	"fmt"
	"psi/config"

	"psi/database"
	"psi/models"
	systemReq "psi/models/request"
	systemRes "psi/models/response"

	"psi/utils"
	"time"

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

type PurchaseService struct{}

// ExportPurchaseOrderToWDT 导出采购单到旺店通
func (s *PurchaseService) ExportPurchaseOrderToWDT(req systemReq.ExportPurchaseOrderToWDTRequest, creatorID int64, role int64, db ...*gorm.DB) (*systemRes.ExportProductResponse, error) {
	databaseConn := database.OptionalDB(db...)

	query := databaseConn.Model(&models.PurchaseOrder{}).Where("purchase_order.is_del = ?", 0)

	if role == 128 {
		query = query.Where("purchase_order.creator_id = ?", creatorID)
	}
	if len(req.IDs) > 0 {
		query = query.Where("purchase_order.id IN ?", req.IDs)
	}
	if req.Status > 0 {
		query = query.Where("purchase_order.status = ?", req.Status)
	}
	if req.SupplierID > 0 {
		query = query.Where("purchase_order.supplier_id = ?", req.SupplierID)
	}
	if req.WarehouseID > 0 {
		query = query.Where("purchase_order.warehouse_id = ?", req.WarehouseID)
	}
	if req.PoNo != "" {
		query = query.Where("purchase_order.po_no LIKE ?", "%"+req.PoNo+"%")
	}
	if req.StartDate > 0 {
		query = query.Where("purchase_order.created_at >= ?", req.StartDate)
	}
	if req.EndDate > 0 {
		query = query.Where("purchase_order.created_at <= ?", req.EndDate)
	}

	var total int64
	if err := query.Count(&total).Error; err != nil {
		return nil, utils.NewError("查询总数失败")
	}

	if total == 0 {
		return nil, fmt.Errorf("没有符合条件的采购单数据")
	}

	type PurchaseOrderExportData struct {
		PoNo                string `gorm:"column:po_no"`
		SupplierName        string `gorm:"column:supplier_name"`
		WarehouseName       string `gorm:"column:warehouse_name"`
		ProductName         string `gorm:"column:product_name"`
		Barcode             string `gorm:"column:barcode"`
		Quantity            int64  `gorm:"column:quantity"`
		UnitPrice           int64  `gorm:"column:unit_price"`
		Amount              int64  `gorm:"column:amount"`
		OrderDate           int64  `gorm:"column:order_date"`
		ExpectedArrivalDate int64  `gorm:"column:expected_arrival_date"`
		Status              int8   `gorm:"column:status"`
		Creator             string `gorm:"column:creator"`
		Remark              string `gorm:"column:remark"`
	}

	var orders []PurchaseOrderExportData
	if err := query.Select(`purchase_order.po_no, 
			s.name as supplier_name, 
			w.name as warehouse_name,
			p.name as product_name,
			p.barcode,
			poi.quantity,
			poi.unit_price,
			poi.amount,
			purchase_order.order_date,
			purchase_order.expected_arrival_date,
			purchase_order.status,
			purchase_order.creator,
			purchase_order.remark`).
		Joins("LEFT JOIN supplier s ON purchase_order.supplier_id = s.id AND s.is_del = 0").
		Joins("LEFT JOIN warehouse w ON purchase_order.warehouse_id = w.id AND w.is_del = 0").
		Joins("LEFT JOIN purchase_order_item poi ON poi.purchase_order_id = purchase_order.id AND poi.is_del = 0").
		Joins("LEFT JOIN product p ON poi.product_id = p.id AND p.is_del = 0").
		Order("purchase_order.created_at DESC").
		Find(&orders).Error; err != nil {
		return nil, utils.NewError("查询采购单数据失败")
	}

	f := excelize.NewFile()
	defer func() {
		if err := f.Close(); err != nil {
			utils.ErrorLog(constant.LoggerChannelWork, map[string]interface{}{
				"source": "关闭Excel文件",
				"error":  fmt.Sprintf("关闭失败: %v", err),
			})
		}
	}()

	sheetName := "Sheet1"
	f.SetSheetName("Sheet1", sheetName)

	headers := []string{"采购单号", "供应商", "仓库", "商品名称", "ISBN/条码", "采购数量", "单价(元)", "金额(元)", "订单日期", "预计到货日期", "状态", "创建人", "备注"}
	for i, header := range headers {
		cell, _ := excelize.CoordinatesToCellName(i+1, 1)
		f.SetCellValue(sheetName, cell, header)
	}

	headerStyle, _ := f.NewStyle(&excelize.Style{
		Font: &excelize.Font{
			Bold: true,
			Size: 12,
		},
		Alignment: &excelize.Alignment{
			Horizontal: "center",
			Vertical:   "center",
		},
		Fill: excelize.Fill{
			Type:    "pattern",
			Color:   []string{"#E0E0E0"},
			Pattern: 1,
		},
	})
	f.SetCellStyle(sheetName, "A1", "M1", headerStyle)

	statusMap := map[int8]string{
		1: "草稿",
		2: "已提交",
		3: "已审核",
		4: "部分收货",
		5: "已收货",
		6: "已取消",
	}

	for idx, order := range orders {
		row := idx + 2

		f.SetCellValue(sheetName, fmt.Sprintf("A%d", row), order.PoNo)
		f.SetCellValue(sheetName, fmt.Sprintf("B%d", row), order.SupplierName)
		f.SetCellValue(sheetName, fmt.Sprintf("C%d", row), order.WarehouseName)
		f.SetCellValue(sheetName, fmt.Sprintf("D%d", row), order.ProductName)
		f.SetCellValue(sheetName, fmt.Sprintf("E%d", row), order.Barcode)
		f.SetCellValue(sheetName, fmt.Sprintf("F%d", row), order.Quantity)
		f.SetCellValue(sheetName, fmt.Sprintf("G%d", row), float64(order.UnitPrice)/100.0)
		f.SetCellValue(sheetName, fmt.Sprintf("H%d", row), float64(order.Amount)/100.0)

		orderDateStr := time.Unix(order.OrderDate, 0).Format("2006-01-02")
		f.SetCellValue(sheetName, fmt.Sprintf("I%d", row), orderDateStr)

		expectedDateStr := time.Unix(order.ExpectedArrivalDate, 0).Format("2006-01-02")
		f.SetCellValue(sheetName, fmt.Sprintf("J%d", row), expectedDateStr)

		statusText := statusMap[order.Status]
		if statusText == "" {
			statusText = "未知"
		}
		f.SetCellValue(sheetName, fmt.Sprintf("K%d", row), statusText)

		f.SetCellValue(sheetName, fmt.Sprintf("L%d", row), order.Creator)
		f.SetCellValue(sheetName, fmt.Sprintf("M%d", row), order.Remark)
	}

	colWidths := map[string]float64{
		"A": 20,
		"B": 15,
		"C": 15,
		"D": 30,
		"E": 15,
		"F": 12,
		"G": 12,
		"H": 12,
		"I": 15,
		"J": 15,
		"K": 12,
		"L": 12,
		"M": 30,
	}
	for col, width := range colWidths {
		f.SetColWidth(sheetName, col, col, width)
	}

	now := time.Now()
	fileName := fmt.Sprintf("purchase_order_wdt_%s.xlsx", now.Format("20060102150405"))
	filePath := fmt.Sprintf("excel/%s", fileName)

	if err := f.SaveAs(filePath); err != nil {
		return nil, fmt.Errorf("保存Excel文件失败: %v", err)
	}

	return &systemRes.ExportProductResponse{
		Total:    total,
		FileName: fileName,
		FilePath: config.AppConfig.Server.Host + filePath,
	}, nil
}

// GetPurchaseOrderList 获取采购订单列表
func (s *PurchaseService) GetPurchaseOrderList(req systemReq.GetPurchaseOrderListRequest, creatorID int64, role int64, db ...*gorm.DB) (*systemRes.PurchaseOrderListResponse, error) {
	databaseConn := database.OptionalDB(db...)

	if req.Page < 1 {
		req.Page = 1
	}
	if req.PageSize < 1 || req.PageSize > 100 {
		req.PageSize = 20
	}

	query := databaseConn.Model(&models.PurchaseOrder{}).Where("purchase_order.is_del = ?", 0)
	if role == 128 {
		query = query.Where("purchase_order.creator_id = ?", creatorID)
	}
	if req.Status > 0 {
		query = query.Where("purchase_order.status = ?", req.Status)
	}
	if req.SupplierID > 0 {
		query = query.Where("purchase_order.supplier_id = ?", req.SupplierID)
	}
	if req.WarehouseID > 0 {
		query = query.Where("purchase_order.warehouse_id = ?", req.WarehouseID)
	}
	if req.PoNo != "" {
		query = query.Where("purchase_order.po_no LIKE ?", "%"+req.PoNo+"%")
	}
	if req.StartDate > 0 {
		query = query.Where("purchase_order.created_at >= ?", req.StartDate)
	}
	if req.EndDate > 0 {
		query = query.Where("purchase_order.created_at <= ?", req.EndDate)
	}

	var total int64
	if err := query.Count(&total).Error; err != nil {
		return nil, utils.NewError("查询总数失败")
	}

	if total == 0 {
		return &systemRes.PurchaseOrderListResponse{
			List:     []systemRes.PurchaseOrderItem{},
			Total:    0,
			Page:     req.Page,
			PageSize: req.PageSize,
		}, nil
	}

	var orders []systemRes.OrderWithInfo
	offset := (req.Page - 1) * req.PageSize
	if err := query.Select("purchase_order.*, s.name as supplier_name, w.name as warehouse_name").
		Joins("LEFT JOIN supplier s ON purchase_order.supplier_id = s.id AND s.is_del = 0").
		Joins("LEFT JOIN warehouse w ON purchase_order.warehouse_id = w.id AND w.is_del = 0").
		Order("purchase_order.created_at DESC").
		Offset(offset).
		Limit(req.PageSize).
		Find(&orders).Error; err != nil {
		return nil, utils.NewError("查询采购订单列表失败")
	}

	orderItems := make([]systemRes.PurchaseOrderItem, 0, len(orders))
	for _, order := range orders {
		orderItems = append(orderItems, systemRes.ConvertPurchaseOrderToItem(
			order.PurchaseOrder,
			order.SupplierName,
			order.WarehouseName,
		))
	}

	return &systemRes.PurchaseOrderListResponse{
		List:     orderItems,
		Total:    total,
		Page:     req.Page,
		PageSize: req.PageSize,
	}, nil
}

// GetPurchaseOrderDetail 获取采购订单详情
func (s *PurchaseService) GetPurchaseOrderDetail(id int64, creatorID int64, role int64, db ...*gorm.DB) (*systemRes.PurchaseOrderDetailResponse, error) {
	databaseConn := database.OptionalDB(db...)

	query := databaseConn.Model(&models.PurchaseOrder{}).
		Select("purchase_order.*, s.name as supplier_name, w.name as warehouse_name").
		Joins("LEFT JOIN supplier s ON purchase_order.supplier_id = s.id AND s.is_del = 0").
		Joins("LEFT JOIN warehouse w ON purchase_order.warehouse_id = w.id AND w.is_del = 0").
		Where("purchase_order.id = ? AND purchase_order.is_del = ?", id, 0)

	if role == 128 {
		query = query.Where("purchase_order.creator_id = ?", creatorID)
	}

	var order systemRes.OrderWithInfo
	result := query.First(&order)

	if result.Error != nil {
		return nil, utils.NewError("采购订单不存在")
	}

	var items []systemRes.ItemWithProduct
	databaseConn.Model(&models.PurchaseOrderItem{}).
		Select("purchase_order_item.*, p.name as product_name, p.barcode as product_code, p.category_id,p.sale_price, c.name as category_name, p.live_image, roi.location_id, l.code as location_code").
		Joins("LEFT JOIN product p ON purchase_order_item.product_id = p.id AND p.is_del = 0").
		Joins("LEFT JOIN product_category c ON p.category_id = c.id AND c.is_del = 0").
		Joins("LEFT JOIN receiving_order ro ON ro.purchase_order_id = purchase_order_item.purchase_order_id AND ro.is_del = 0").
		Joins("LEFT JOIN receiving_order_item roi ON roi.receiving_order_id = ro.id AND roi.product_id = purchase_order_item.product_id AND roi.is_del = 0").
		Joins("LEFT JOIN location l ON roi.location_id = l.id AND l.is_del = 0").
		Where("purchase_order_item.purchase_order_id = ? AND purchase_order_item.is_del = ?", order.ID, 0).
		Find(&items)

	detailItems := make([]systemRes.PurchaseOrderDetailItem, 0, len(items))
	for _, item := range items {
		var imageList []string
		if len(item.LiveImage) > 0 {
			json.Unmarshal(item.LiveImage, &imageList)
		}

		detailItems = append(detailItems, systemRes.PurchaseOrderDetailItem{
			ID:               item.ID,
			PurchaseOrderID:  item.PurchaseOrderID,
			ProductID:        item.ProductID,
			ProductName:      item.ProductName,
			ProductCode:      item.ProductCode,
			SalePrice:        item.SalePrice,
			CategoryID:       item.CategoryID,
			CategoryName:     item.CategoryName,
			LiveImage:        imageList,
			Quantity:         item.Quantity,
			ReceivedQuantity: item.ReceivedQuantity,
			UnitPrice:        item.UnitPrice,
			Amount:           item.Amount,
			LocationID:       item.LocationID,
			LocationCode:     item.LocationCode,
			CreatedAt:        item.CreatedAt,
			UpdatedAt:        item.UpdatedAt,
		})
	}

	detail := systemRes.ConvertPurchaseOrderToDetail(order.PurchaseOrder, order.SupplierName, order.WarehouseName, detailItems)
	return &detail, nil
}