package repository

import (
	"database/sql"
	"errors"
	"strings"

	"github.com/wmms/wmms/internal/model"
	"github.com/wmms/wmms/pkg/database"
)

// ErrWarehouseNotFound 表示仓库 id 不存在，或这条仓库不属于当前组织。
// 建库位时的 INSERT...SELECT 影响 0 行就是这种情况，接口据此回错，
// 而不是把「没写成」报成"创建成功"。
var ErrWarehouseNotFound = errors.New("仓库不存在，可能已被删除或不属于当前组织")

// ErrDuplicateWarehouse 表示该公司已经有仓库。warehouses(organization_id) 上的
// 唯一约束把一个公司限制成一个仓库，停用的仓库同样占名额，不能再建第二个。
var ErrDuplicateWarehouse = errors.New("本公司已配置仓库，请维护现有仓库")

// ErrDuplicateLocation 表示该仓库已经有库位。warehouse_locations(warehouse_id)
// 上的唯一约束把一个仓库限制成一个库位，停用的库位同样占名额。
var ErrDuplicateLocation = errors.New("本仓库已配置库位，请维护现有库位")

// translateDuplicate 把唯一键冲突翻译成可直接展示的中文错误，其余原样返回。
func translateDuplicate(err error, duplicate error) error {
	if err != nil && strings.Contains(err.Error(), "Duplicate entry") {
		return duplicate
	}
	return err
}

type WarehouseRepository struct{}

func NewWarehouseRepository() *WarehouseRepository { return &WarehouseRepository{} }

func (r *WarehouseRepository) List(organizationID int64) ([]model.Warehouse, error) {
	rows, err := database.DB.Query(`SELECT w.id, w.organization_id, w.code, w.name, w.status,
		l.id, l.code, l.name, l.status
		FROM warehouses w
		LEFT JOIN warehouse_locations l ON l.warehouse_id = w.id
		WHERE w.organization_id = ? ORDER BY w.code, l.code`, organizationID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := make([]model.Warehouse, 0)
	index := make(map[int64]int)
	for rows.Next() {
		var warehouse model.Warehouse
		var locationID sql.NullInt64
		var locationCode, locationName, locationStatus sql.NullString
		if err := rows.Scan(&warehouse.ID, &warehouse.OrganizationID, &warehouse.Code, &warehouse.Name, &warehouse.Status, &locationID, &locationCode, &locationName, &locationStatus); err != nil {
			return nil, err
		}
		position, exists := index[warehouse.ID]
		if !exists {
			warehouse.Locations = []model.WarehouseLocation{}
			items = append(items, warehouse)
			position = len(items) - 1
			index[warehouse.ID] = position
		}
		if locationID.Valid {
			items[position].Locations = append(items[position].Locations, model.WarehouseLocation{ID: locationID.Int64, WarehouseID: warehouse.ID, Code: locationCode.String, Name: locationName.String, Status: locationStatus.String})
		}
	}
	return items, rows.Err()
}

// Summary 返回公司当前唯一仓库及其唯一库位的摘要，供公司切换接口和组织初始化
// 列表共用。一个公司有多个仓库或多个库位属内部不变量错误（warehouses 与
// warehouse_locations 上的唯一约束保证不可达），此时返回错误而不是随意取第一条；
// 无仓库、无库位或已停用都不是错误，按 nil 或原状态如实返回，不回退旧公司数据。
func (r *WarehouseRepository) Summary(organizationID int64) (*model.WarehouseSummary, error) {
	warehouses, err := r.List(organizationID)
	if err != nil {
		return nil, err
	}
	if len(warehouses) > 1 {
		return nil, errors.New("公司仓库配置不唯一")
	}
	if len(warehouses) == 0 {
		return nil, nil
	}
	warehouse := warehouses[0]
	if len(warehouse.Locations) > 1 {
		return nil, errors.New("公司库位配置不唯一")
	}
	summary := &model.WarehouseSummary{ID: warehouse.ID, Name: warehouse.Name, Status: warehouse.Status}
	if len(warehouse.Locations) == 1 {
		location := warehouse.Locations[0]
		summary.Location = &model.LocationSummary{ID: location.ID, Name: location.Name, Status: location.Status}
	}
	return summary, nil
}

func (r *WarehouseRepository) Create(item *model.Warehouse) error {
	result, err := database.DB.Exec(`INSERT INTO warehouses (organization_id, code, name, status) VALUES (?, ?, ?, ?)`, item.OrganizationID, item.Code, item.Name, item.Status)
	if err != nil {
		return translateDuplicate(err, ErrDuplicateWarehouse)
	}
	item.ID, err = result.LastInsertId()
	return err
}

func (r *WarehouseRepository) Update(item *model.Warehouse) error {
	return execExpectingRow(`UPDATE warehouses SET name = ?, status = ? WHERE id = ? AND organization_id = ?`, item.Name, item.Status, item.ID, item.OrganizationID)
}

func (r *WarehouseRepository) CreateLocation(item *model.WarehouseLocation, organizationID int64) error {
	// 仓库归属条件写在 SELECT 里，避免「先查后插」之间的竞态。
	//
	// 匹配不到仓库时这条语句影响 0 行，但 MySQL 不报错：err 为 nil，LastInsertId()
	// 返回 0。此前只看 err，接口回 201「创建成功」而库里一行都没有（实测请求前后
	// warehouse_locations 行数都是 1，刷新后库位消失）。所以这里和按主键更新一样，
	// 以 RowsAffected 为准——INSERT 的 RowsAffected 就是实际插入行数，不受
	// clientFoundRows 影响。
	result, err := database.DB.Exec(`INSERT INTO warehouse_locations (warehouse_id, code, name, status)
		SELECT id, ?, ?, ? FROM warehouses WHERE id = ? AND organization_id = ?`, item.Code, item.Name, item.Status, item.WarehouseID, organizationID)
	if err != nil {
		return translateDuplicate(err, ErrDuplicateLocation)
	}
	affected, err := result.RowsAffected()
	if err != nil {
		return err
	}
	if affected == 0 {
		return ErrWarehouseNotFound
	}
	item.ID, err = result.LastInsertId()
	return err
}

func (r *WarehouseRepository) UpdateLocation(item *model.WarehouseLocation, organizationID int64) error {
	// JOIN 形式的 UPDATE 同样受 clientFoundRows 影响，影响 0 行也代表库位不存在
	// 或不属于该组织（已实测）。
	return execExpectingRow(`UPDATE warehouse_locations l JOIN warehouses w ON w.id = l.warehouse_id
		SET l.name = ?, l.status = ? WHERE l.id = ? AND w.organization_id = ?`, item.Name, item.Status, item.ID, organizationID)
}