jiu/backend/cmd/import-inventory/main.go

// import-inventory —— 一次性：用 Excel 库存表【全量覆盖】某门店的当前库存。
//
// 口径（已与用户确认）：
//   - 以【商品编号】为唯一键，用文件为准 reconcile 当前库存：
//       文件有·库无      → 新增（按编号建/复用 product + 建 inventory 行）
//       文件有·库有(非sold) → 以文件刷新数量与快照字段（有变才更新，全同不动）
//       文件无·库有(非sold) → 软删（deleted_at，可回滚），并记一笔 out 流水
//       sold（已售留痕）    → 一律排除、保留不动（文件是当前盘点，本就不含已售）
//   - 仓库列忽略：所有行统一落门店默认仓（如「鼎晟酒行」）。
//   - 只动 inventories / inventory_logs / products（新增缺失商品），不碰出入库单据。
//   - 覆盖仅作用于 import 来源库存（stock_in_item_id IS NULL），不动出入库派生的库存。
//
// 用法（在 ali 上执行，--env-file 提供 DATABASE_DSN）：
//
//	./import-inventory --shop-code S000008 --data-file 库存20260901.xls --env-file import.env --dry-run
//	./import-inventory --shop-code S000008 --data-file 库存20260901.xls --env-file import.env
package main

import (
	"flag"
	"fmt"
	"log"
	"os"
	"strconv"
	"strings"
	"time"

	"github.com/google/uuid"
	"github.com/shakinm/xlsReader/xls"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"

	"github.com/wangjia/jiu/backend/config"
	"github.com/wangjia/jiu/backend/internal/model"
)

func main() {
	var (
		shopCode = flag.String("shop-code", "", "目标门店 code")
		dataFile = flag.String("data-file", "", "库存 .xls 文件路径")
		whName   = flag.String("warehouse-name", "", "统一落入的仓库名（空=用门店默认仓）")
		dryRun   = flag.Bool("dry-run", false, "只统计不写库")
		envFile  = flag.String("env-file", "", "systemd 风格 KEY=VALUE 环境文件（提供 DATABASE_DSN）")
	)
	flag.Parse()

	if *shopCode == "" || *dataFile == "" {
		log.Fatal("必须提供 --shop-code 和 --data-file")
	}
	if *envFile != "" {
		loadEnvFile(*envFile)
	}
	config.Load()
	db, err := gorm.Open(mysql.Open(config.C.Database.DSN), &gorm.Config{
		Logger: logger.Default.LogMode(logger.Silent),
	})
	if err != nil {
		log.Fatalf("连接数据库失败: %v", err)
	}

	// ── 门店 ──
	var shop model.Shop
	if err := db.Where("code = ?", *shopCode).First(&shop).Error; err != nil {
		log.Fatalf("门店 code=%s 不存在: %v", *shopCode, err)
	}
	// ── 目标仓库（统一落入）──
	var wh model.Warehouse
	q := db.Where("shop_id = ? AND deleted_at IS NULL", shop.ID)
	if *whName != "" {
		q = q.Where("name = ?", *whName)
	} else {
		q = q.Where("is_default = ?", true)
	}
	if err := q.First(&wh).Error; err != nil {
		// 回退：该店任意仓
		if db.Where("shop_id = ? AND deleted_at IS NULL", shop.ID).Order("id ASC").First(&wh).Error != nil {
			log.Fatalf("门店 %d 无可用仓库", shop.ID)
		}
	}
	log.Printf("目标门店: %s (code=%s id=%d) | 统一仓库: %s (id=%d) | dry-run=%v",
		shop.Name, shop.Code, shop.ID, wh.Name, wh.ID, *dryRun)

	// ── 读文件 ──
	fileRows := readInventoryXls(*dataFile)
	log.Printf("文件有效数据行（有商品名称）: %d", len(fileRows))

	// ── 预加载 DB 现有有效 import 库存 ──
	var dbInvs []model.Inventory
	db.Where("shop_id = ? AND stock_in_item_id IS NULL AND deleted_at IS NULL", shop.ID).Find(&dbInvs)
	// 非 sold 行按编号索引（同一编号取一行；正常每编号一行）
	dbByCode := map[string]*model.Inventory{}
	soldCodes := map[string]bool{}
	var soldKept int
	for i := range dbInvs {
		inv := &dbInvs[i]
		if inv.Status == "sold" {
			soldKept++
			soldCodes[inv.ProductCode] = true
			continue // 已售留痕：排除在覆盖之外
		}
		if inv.ProductCode != "" {
			dbByCode[inv.ProductCode] = inv
		}
	}

	ic := &ctx{db: db, shopID: shop.ID, whID: wh.ID, whName: wh.Name, dryRun: *dryRun}

	fileCodes := map[string]bool{}
	for _, fr := range fileRows {
		if fr.code != "" {
			fileCodes[fr.code] = true
		}
		existing := dbByCode[fr.code]
		if fr.code == "" {
			// 无编号行：按名称|系列|规格 无法可靠 reconcile，稳妥当作新增
			existing = nil
		}
		if existing != nil {
			ic.updateIfChanged(existing, fr)
		} else {
			ic.insertNew(fr)
		}
	}

	// ── 软删：库有(非sold)·文件无 ──
	for code, inv := range dbByCode {
		if !fileCodes[code] {
			ic.softDelete(inv)
		}
	}

	ic.report(soldKept)
}

// ── 文件解析 ──

type fileRow struct {
	code, name, series, spec, unit string
	qty, price                     float64
	prodDate, batch, supplier      string
	remark, stockInDate            string
}

func readInventoryXls(path string) []fileRow {
	wb, err := xls.OpenFile(path)
	if err != nil {
		log.Fatalf("打开 %s 失败: %v", path, err)
	}
	sheet, err := wb.GetSheet(0)
	if err != nil || sheet == nil {
		log.Fatalf("读取 sheet 失败: %v", err)
	}
	numRows := sheet.GetNumberRows()
	header, _ := sheet.GetRow(0)
	numCols := len(header.GetCols())
	if numCols == 0 {
		numCols = 20
	}
	cell := func(r, c int) string {
		row, _ := sheet.GetRow(r)
		if row == nil {
			return ""
		}
		cd, e := row.GetCol(c)
		if e != nil || cd == nil {
			return ""
		}
		return strings.TrimSpace(cd.GetString())
	}
	// 动态列检测（镜像 handler.ImportInventory）
	col := map[string]int{
		"code": 0, "name": 1, "series": 2, "spec": 3, "unit": 4,
		"qty": 5, "price": 6, "prod": 8, "batch": 9, "wh": 11, "sin": 12, "sup": 13, "remark": 15,
	}
	for j := 0; j < numCols; j++ {
		switch cell(0, j) {
		case "商品编号", "商品编码", "编号", "编码", "商品条码":
			col["code"] = j
		case "商品名称", "品名", "名称", "货品名称":
			col["name"] = j
		case "系列", "品牌系列":
			col["series"] = j
		case "规格", "规格型号":
			col["spec"] = j
		case "单位":
			col["unit"] = j
		case "库存数量", "数量", "库存":
			col["qty"] = j
		case "单价", "进价", "采购单价":
			col["price"] = j
		case "生产日期", "生产年月":
			col["prod"] = j
		case "批次", "批次号", "批次/编号/物流码":
			col["batch"] = j
		case "入库日期", "入库时间", "入库日":
			col["sin"] = j
		case "供应商", "供应商名称":
			col["sup"] = j
		case "备注":
			col["remark"] = j
		}
	}
	get := func(r int, k string) string { return cell(r, col[k]) }

	var out []fileRow
	for r := 1; r < numRows; r++ {
		name := get(r, "name")
		if name == "" {
			continue // 空行
		}
		qty := parseFloatLoose(get(r, "qty"))
		if qty <= 0 {
			qty = 1
		}
		out = append(out, fileRow{
			code: get(r, "code"), name: name, series: get(r, "series"), spec: get(r, "spec"),
			unit: get(r, "unit"), qty: qty, price: parseFloatLoose(get(r, "price")),
			prodDate: get(r, "prod"), batch: get(r, "batch"), supplier: get(r, "sup"),
			remark: get(r, "remark"), stockInDate: get(r, "sin"),
		})
	}
	return out
}

func parseFloatLoose(s string) float64 {
	s = strings.TrimSpace(strings.ReplaceAll(s, ",", ""))
	if s == "" {
		return 0
	}
	f, _ := strconv.ParseFloat(s, 64)
	return f
}

var dateLayouts = []string{"2006-01-02", "2006/01/02", "2006-01-02 15:04:05", "2006/01/02 15:04:05"}

func parseDatePtr(s string) *model.Date {
	s = strings.TrimSpace(s)
	if s == "" {
		return nil
	}
	for _, l := range dateLayouts {
		if t, err := time.ParseInLocation(l, s, time.Local); err == nil {
			return &model.Date{Time: t}
		}
	}
	if serial, err := strconv.ParseFloat(s, 64); err == nil && serial > 0 && serial < 100000 {
		base := time.Date(1899, 12, 30, 0, 0, 0, 0, time.Local)
		return &model.Date{Time: base.AddDate(0, 0, int(serial))}
	}
	return nil
}

func parseTimeOrNow(s string) time.Time {
	if d := parseDatePtr(s); d != nil {
		return d.Time
	}
	return time.Now()
}

// ── reconcile 上下文 ──

type ctx struct {
	db                             *gorm.DB
	shopID, whID                   uint64
	whName                         string
	dryRun                         bool
	statInsert, statUpdate         int
	statUnchanged, statDelete      int
}

// findOrCreateProduct 按编号匹配/创建（编号即特有产品序列号，绝不按名称合并）。
func (ic *ctx) findOrCreateProduct(fr fileRow) uint64 {
	var p model.Product
	if fr.code != "" {
		if ic.db.Where("shop_id = ? AND code = ? AND deleted_at IS NULL", ic.shopID, fr.code).First(&p).Error == nil {
			return p.ID
		}
	} else {
		if ic.db.Where("shop_id = ? AND name = ? AND series = ? AND spec = ? AND deleted_at IS NULL",
			ic.shopID, fr.name, fr.series, fr.spec).First(&p).Error == nil {
			return p.ID
		}
	}
	if ic.dryRun {
		return 0
	}
	unit := fr.unit
	if unit == "" {
		unit = "瓶"
	}
	p = model.Product{
		TenantBase: model.TenantBase{ShopID: ic.shopID},
		PublicID:   uuid.New().String(),
		Code:       fr.code, Name: fr.name, Series: fr.series, Spec: fr.spec, Unit: unit,
	}
	if err := ic.db.Create(&p).Error; err != nil {
		log.Fatalf("建商品 %s(%s) 失败: %v", fr.name, fr.code, err)
	}
	return p.ID
}

func (ic *ctx) insertNew(fr fileRow) {
	ic.statInsert++
	if ic.dryRun {
		return
	}
	prodID := ic.findOrCreateProduct(fr)
	whID := ic.whID
	var pricePtr *float64
	if fr.price != 0 {
		pricePtr = &fr.price
	}
	inv := model.Inventory{
		ShopID: ic.shopID, WarehouseID: &whID, ProductID: &prodID,
		Quantity: fr.qty, ProductCode: fr.code, ProductName: fr.name,
		Series: fr.series, Spec: fr.spec, Unit: fr.unit, WarehouseName: ic.whName,
		UnitPrice: pricePtr, ProductionDate: parseDatePtr(fr.prodDate), BatchNo: fr.batch,
		SupplierName: fr.supplier, Remark: fr.remark, CreatedAt: parseTimeOrNow(fr.stockInDate),
	}
	if err := ic.db.Create(&inv).Error; err != nil {
		log.Fatalf("建库存 %s 失败: %v", fr.code, err)
	}
	ic.db.Create(&model.InventoryLog{
		ShopID: ic.shopID, WarehouseID: whID, ProductID: prodID,
		Direction: "in", Quantity: fr.qty, QtyBefore: 0, QtyAfter: fr.qty,
		RefType: "import_replace", RefID: 0,
	})
}

func (ic *ctx) updateIfChanged(inv *model.Inventory, fr fileRow) {
	updates := map[string]interface{}{}
	if inv.Quantity != fr.qty {
		updates["quantity"] = fr.qty
	}
	if inv.ProductName != fr.name {
		updates["product_name"] = fr.name
	}
	if inv.Series != fr.series {
		updates["series"] = fr.series
	}
	if inv.Spec != fr.spec {
		updates["spec"] = fr.spec
	}
	if fr.unit != "" && inv.Unit != fr.unit {
		updates["unit"] = fr.unit
	}
	if inv.WarehouseName != ic.whName {
		updates["warehouse_name"] = ic.whName
		updates["warehouse_id"] = ic.whID
	}
	if inv.BatchNo != fr.batch {
		updates["batch_no"] = fr.batch
	}
	if inv.SupplierName != fr.supplier {
		updates["supplier_name"] = fr.supplier
	}
	if inv.Remark != fr.remark {
		updates["remark"] = fr.remark
	}
	if fr.price != 0 && (inv.UnitPrice == nil || *inv.UnitPrice != fr.price) {
		updates["unit_price"] = fr.price
	}
	fd := parseDatePtr(fr.prodDate)
	if fd != nil && (inv.ProductionDate == nil || !inv.ProductionDate.Time.Equal(fd.Time)) {
		updates["production_date"] = fd
	}

	if len(updates) == 0 {
		ic.statUnchanged++
		return
	}
	ic.statUpdate++
	if ic.dryRun {
		return
	}
	qtyBefore := inv.Quantity
	if err := ic.db.Model(inv).Updates(updates).Error; err != nil {
		log.Fatalf("更新库存 %s 失败: %v", inv.ProductCode, err)
	}
	if _, ok := updates["quantity"]; ok && inv.ProductID != nil {
		ic.db.Create(&model.InventoryLog{
			ShopID: ic.shopID, WarehouseID: ic.whID, ProductID: *inv.ProductID,
			Direction: "in", Quantity: fr.qty, QtyBefore: qtyBefore, QtyAfter: fr.qty,
			RefType: "import_replace", RefID: 0,
		})
	}
}

func (ic *ctx) softDelete(inv *model.Inventory) {
	ic.statDelete++
	if ic.dryRun {
		return
	}
	now := time.Now()
	if err := ic.db.Model(inv).Update("deleted_at", now).Error; err != nil {
		log.Fatalf("软删库存 %s 失败: %v", inv.ProductCode, err)
	}
	if inv.ProductID != nil {
		ic.db.Create(&model.InventoryLog{
			ShopID: ic.shopID, WarehouseID: ic.whID, ProductID: *inv.ProductID,
			Direction: "out", Quantity: inv.Quantity, QtyBefore: inv.Quantity, QtyAfter: 0,
			RefType: "import_replace", RefID: 0,
		})
	}
}

func (ic *ctx) report(soldKept int) {
	fmt.Println("═══════════════════════════════════════════")
	fmt.Println("  库存覆盖导入汇总")
	if ic.dryRun {
		fmt.Println("  *** DRY-RUN（未写库）***")
	}
	fmt.Println("═══════════════════════════════════════════")
	fmt.Printf("  新增(文件有·库无)        : %d\n", ic.statInsert)
	fmt.Printf("  更新(库有·字段有变)      : %d\n", ic.statUpdate)
	fmt.Printf("  不变(库有·全同)          : %d\n", ic.statUnchanged)
	fmt.Printf("  软删(文件无·库有非sold)  : %d\n", ic.statDelete)
	fmt.Printf("  保留不动(sold 已售留痕)  : %d\n", soldKept)
	fmt.Printf("  ── 覆盖后在库/在售行 = 新增+更新+不变 = %d\n", ic.statInsert+ic.statUpdate+ic.statUnchanged)
	fmt.Println("═══════════════════════════════════════════")
}

// loadEnvFile 解析 systemd EnvironmentFile 风格 KEY=VALUE，写入进程环境。
func loadEnvFile(path string) {
	data, err := os.ReadFile(path)
	if err != nil {
		log.Fatalf("读取 env 文件 %s 失败: %v", path, err)
	}
	for _, line := range strings.Split(string(data), "\n") {
		line = strings.TrimSpace(line)
		if line == "" || strings.HasPrefix(line, "#") {
			continue
		}
		eq := strings.IndexByte(line, '=')
		if eq <= 0 {
			continue
		}
		key := strings.TrimSpace(line[:eq])
		val := strings.TrimSpace(line[eq+1:])
		if len(val) >= 2 && (val[0] == '"' || val[0] == '\'') && val[len(val)-1] == val[0] {
			val = val[1 : len(val)-1]
		}
		_ = os.Setenv(key, val)
	}
}