boc/backend/handlers/inventory.go

package handlers

import (
	"database/sql"
	"encoding/json"
	"net/http"
	"time"
)

type InventoryHandler struct {
	DB *sql.DB
}

func NewInventoryHandler(db *sql.DB) *InventoryHandler {
	return &InventoryHandler{DB: db}
}

type Warehouse struct {
	ID         string                 `json:"id"`
	Name       string                 `json:"name"`
	Location   string                 `json:"location"`
	Address    map[string]interface{} `json:"address"`
	IsDefault  bool                   `json:"is_default"`
	CreatedAt  time.Time              `json:"created_at"`
}

type InventoryItem struct {
	ID           string    `json:"id"`
	ProductID    string    `json:"product_id"`
	ProductName  string    `json:"product_name"`
	WarehouseID  string    `json:"warehouse_id"`
	WarehouseName string   `json:"warehouse_name"`
	Quantity     float64   `json:"quantity"`
	ReservedQty  float64   `json:"reserved_qty"`
	AvailableQty float64   `json:"available_qty"`
	ReorderPoint float64   `json:"reorder_point"`
	ReorderQty   float64   `json:"reorder_qty"`
	UnitCost     float64   `json:"unit_cost"`
}

type InventoryMovement struct {
	ID           string    `json:"id"`
	ProductID    string    `json:"product_id"`
	ProductName  string    `json:"product_name"`
	WarehouseID  string    `json:"warehouse_id"`
	Type         string    `json:"type"`
	Quantity     float64   `json:"quantity"`
	ReferenceType string   `json:"reference_type"`
	ReferenceID  string    `json:"reference_id"`
	Notes        string    `json:"notes"`
	CreatedAt    time.Time `json:"created_at"`
}

func (h *InventoryHandler) ListWarehouses(w http.ResponseWriter, r *http.Request) {
	rows, err := h.DB.Query(`
		SELECT id, name, location, address, is_default, created_at
		FROM boc_warehouses ORDER BY name
	`)
	if err != nil {
		writeError(w, http.StatusInternalServerError, "database error")
		return
	}
	defer rows.Close()

	warehouses := []Warehouse{}
	for rows.Next() {
		var w Warehouse
		var addr []byte
		if err := rows.Scan(&w.ID, &w.Name, &w.Location, &addr, &w.IsDefault, &w.CreatedAt); err != nil {
			continue
		}
		json.Unmarshal(addr, &w.Address)
		warehouses = append(warehouses, w)
	}

	writeJSON(w, http.StatusOK, map[string]interface{}{
		"warehouses": warehouses,
		"total":      len(warehouses),
	})
}

func (h *InventoryHandler) CreateWarehouse(w http.ResponseWriter, r *http.Request) {
	var req Warehouse
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		writeError(w, http.StatusBadRequest, "invalid request")
		return
	}

	addr, _ := json.Marshal(req.Address)

	var id string
	err := h.DB.QueryRow(`
		INSERT INTO boc_warehouses (name, location, address, is_default)
		VALUES ($1, $2, $3, $4)
		RETURNING id
	`, req.Name, req.Location, addr, req.IsDefault).Scan(&id)

	if err != nil {
		writeError(w, http.StatusInternalServerError, "failed to create warehouse")
		return
	}

	writeJSON(w, http.StatusCreated, map[string]interface{}{
		"id":      id,
		"message": "Warehouse created",
	})
}

func (h *InventoryHandler) ListInventory(w http.ResponseWriter, r *http.Request) {
	warehouseID := r.URL.Query().Get("warehouse_id")

	var query string
	var args []interface{}
	if warehouseID != "" {
		query = `
			SELECT i.id, i.product_id, p.name, i.warehouse_id, w.name, i.quantity, i.reserved_qty, i.reorder_point, i.reorder_qty, i.unit_cost
			FROM boc_inventory i
			JOIN boc_products p ON i.product_id = p.id
			JOIN boc_warehouses w ON i.warehouse_id = w.id
			WHERE i.warehouse_id = $1
			ORDER BY p.name
		`
		args = append(args, warehouseID)
	} else {
		query = `
			SELECT i.id, i.product_id, p.name, i.warehouse_id, w.name, i.quantity, i.reserved_qty, i.reorder_point, i.reorder_qty, i.unit_cost
			FROM boc_inventory i
			JOIN boc_products p ON i.product_id = p.id
			JOIN boc_warehouses w ON i.warehouse_id = w.id
			ORDER BY p.name
		`
	}

	rows, err := h.DB.Query(query, args...)
	if err != nil {
		writeError(w, http.StatusInternalServerError, "database error")
		return
	}
	defer rows.Close()

	items := []InventoryItem{}
	for rows.Next() {
		var i InventoryItem
		if err := rows.Scan(&i.ID, &i.ProductID, &i.ProductName, &i.WarehouseID, &i.WarehouseName, &i.Quantity, &i.ReservedQty, &i.ReorderPoint, &i.ReorderQty, &i.UnitCost); err != nil {
			continue
		}
		i.AvailableQty = i.Quantity - i.ReservedQty
		items = append(items, i)
	}

	writeJSON(w, http.StatusOK, map[string]interface{}{
		"inventory": items,
		"total":     len(items),
	})
}

func (h *InventoryHandler) AdjustStock(w http.ResponseWriter, r *http.Request) {
	var req struct {
		ProductID   string  `json:"product_id"`
		WarehouseID string  `json:"warehouse_id"`
		Quantity    float64 `json:"quantity"`
		Reason      string  `json:"reason"`
	}
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		writeError(w, http.StatusBadRequest, "invalid request")
		return
	}

	tx, err := h.DB.Begin()
	if err != nil {
		writeError(w, http.StatusInternalServerError, "transaction error")
		return
	}
	defer tx.Rollback()

	// Update or insert inventory
	var existingID string
	err = tx.QueryRow(`
		SELECT id FROM boc_inventory WHERE product_id = $1 AND warehouse_id = $2
	`, req.ProductID, req.WarehouseID).Scan(&existingID)

	if err == sql.ErrNoRows {
		// Insert new
		_, err = tx.Exec(`
			INSERT INTO boc_inventory (product_id, warehouse_id, quantity)
			VALUES ($1, $2, $3)
		`, req.ProductID, req.WarehouseID, req.Quantity)
	} else if err == nil {
		// Update existing
		_, err = tx.Exec(`
			UPDATE boc_inventory SET quantity = $1, updated_at = NOW() WHERE id = $2
		`, req.Quantity, existingID)
	}

	if err != nil {
		writeError(w, http.StatusInternalServerError, "failed to update inventory")
		return
	}

	// Record movement
	_, err = tx.Exec(`
		INSERT INTO boc_inventory_movements (product_id, warehouse_id, type, quantity, notes)
		VALUES ($1, $2, 'adjustment', $3, $4)
	`, req.ProductID, req.WarehouseID, req.Quantity, req.Reason)
	if err != nil {
		writeError(w, http.StatusInternalServerError, "failed to record movement")
		return
	}

	if err := tx.Commit(); err != nil {
		writeError(w, http.StatusInternalServerError, "commit failed")
		return
	}

	writeJSON(w, http.StatusOK, map[string]interface{}{
		"message": "Stock adjusted",
	})
}

func (h *InventoryHandler) ListMovements(w http.ResponseWriter, r *http.Request) {
	productID := r.URL.Query().Get("product_id")

	var query string
	var args []interface{}
	if productID != "" {
		query = `
			SELECT m.id, m.product_id, p.name, m.warehouse_id, m.type, m.quantity, m.reference_type, m.reference_id, m.notes, m.created_at
			FROM boc_inventory_movements m
			JOIN boc_products p ON m.product_id = p.id
			WHERE m.product_id = $1
			ORDER BY m.created_at DESC
			LIMIT 100
		`
		args = append(args, productID)
	} else {
		query = `
			SELECT m.id, m.product_id, p.name, m.warehouse_id, m.type, m.quantity, m.reference_type, m.reference_id, m.notes, m.created_at
			FROM boc_inventory_movements m
			JOIN boc_products p ON m.product_id = p.id
			ORDER BY m.created_at DESC
			LIMIT 100
		`
	}

	rows, err := h.DB.Query(query, args...)
	if err != nil {
		writeError(w, http.StatusInternalServerError, "database error")
		return
	}
	defer rows.Close()

	movements := []InventoryMovement{}
	for rows.Next() {
		var m InventoryMovement
		if err := rows.Scan(&m.ID, &m.ProductID, &m.ProductName, &m.WarehouseID, &m.Type, &m.Quantity, &m.ReferenceType, &m.ReferenceID, &m.Notes, &m.CreatedAt); err != nil {
			continue
		}
		movements = append(movements, m)
	}

	writeJSON(w, http.StatusOK, map[string]interface{}{
		"movements": movements,
		"total":     len(movements),
	})
}

func (h *InventoryHandler) GetLowStock(w http.ResponseWriter, r *http.Request) {
	rows, err := h.DB.Query(`
		SELECT i.id, i.product_id, p.name, i.warehouse_id, w.name, i.quantity, i.reserved_qty, i.reorder_point, i.reorder_qty
		FROM boc_inventory i
		JOIN boc_products p ON i.product_id = p.id
		JOIN boc_warehouses w ON i.warehouse_id = w.id
		WHERE i.quantity <= i.reorder_point
		ORDER BY (i.quantity / NULLIF(i.reorder_point, 0))
	`)
	if err != nil {
		writeError(w, http.StatusInternalServerError, "database error")
		return
	}
	defer rows.Close()

	items := []InventoryItem{}
	for rows.Next() {
		var i InventoryItem
		if err := rows.Scan(&i.ID, &i.ProductID, &i.ProductName, &i.WarehouseID, &i.WarehouseName, &i.Quantity, &i.ReservedQty, &i.ReorderPoint, &i.ReorderQty); err != nil {
			continue
		}
		i.AvailableQty = i.Quantity - i.ReservedQty
		items = append(items, i)
	}

	writeJSON(w, http.StatusOK, map[string]interface{}{
		"low_stock": items,
		"total":     len(items),
	})
}