kitcheninv/main.go

// main.go
// Einfaches Kücheninventar mit Weboberfläche und persistenter SQLite-Datenbank.
// Features:
// - Produkte: Name, Hersteller, Größe (Inhaltsmenge), Bild (Upload oder URL), bevorzugter Händler, Mindestbestand
// - Bestände entstehen aus Einheiten (Chargen) mit je einem Ablaufdatum
// - Automatischer Bestand je Produkt = Anzahl Einheiten mit Status "in"
// - Vorschlag: Nächste ablaufende Einheit (global und je Produkt) mit Button "Ausbuchen"
// - Warnungen bei Unterschreitung Mindestbestand
// - Einkaufslisten pro Händler: Welche Artikel und Menge sind zu beschaffen (Min - Ist, falls > 0)
//
// Start:
//   go mod init kitcheninv
//   go get modernc.org/sqlite
//   go run .
//
// Danach im Browser: http://localhost:8080
// Uploads liegen im Ordner ./uploads

package main

import (
	"database/sql"
	"embed"
	"fmt"
	"html/template"
	"io"
	"log"
	"net/http"
	"os"
	"path/filepath"
	"strconv"
	"strings"
	"time"

	_ "modernc.org/sqlite"
)

//go:embed templates/* assets/*
var embeddedFS embed.FS

type App struct {
	DB        *sql.DB
	Templates *template.Template // (legacy – nicht genutzt für Rendering)
	UploadDir string
}

type Product struct {
	ID              int
	Name            string
	Manufacturer    string
	Size            string
	ImagePath       string // lokal gespeicherter Pfad oder externe URL
	PreferredVendor string
	MinStock        int
	CurrentStock    int // berechnet
}

type Unit struct {
	ID         int
	ProductID  int
	ExpiryDate string // YYYY-MM-DD
	Status     string // "in" | "out"
	AddedAt    string
	Product    *Product // optional befüllt bei JOINs
}

func main() {
	if err := os.MkdirAll("uploads", 0o755); err != nil {
		log.Fatalf("kann Upload-Verzeichnis nicht erstellen: %v", err)
	}

	dsn := "file:kueche.db?_pragma=busy_timeout(5000)&cache=shared"
	db, err := sql.Open("sqlite", dsn)
	if err != nil {
		log.Fatal(err)
	}
	if err := initDB(db); err != nil {
		log.Fatal(err)
	}

	// Templates werden pro View on-demand zusammengebaut, um Mehrfach-Definitionen zu vermeiden.
	tmpl := template.New("legacy") // Platzhalter – global ungenutzt

	app := &App{DB: db, Templates: tmpl /* legacy */, UploadDir: "uploads"}

	mux := http.NewServeMux()

	// Routen
	mux.HandleFunc("/", app.handleDashboard)
	mux.HandleFunc("/products", app.handleProducts)
	mux.HandleFunc("/products/create", app.handleCreateProduct)
	mux.HandleFunc("/products/", app.handleProductDetail) // /products/{id}
	mux.HandleFunc("/units/", app.handleUnitActions)      // /units/{id}/checkout
	mux.HandleFunc("/alerts", app.handleAlerts)
	mux.HandleFunc("/shopping-list", app.handleShoppingList)

	// Statische Assets (CSS)
	mux.Handle("/assets/", http.FileServer(http.FS(embeddedFS)))
	// Uploads
	uploadFS := http.FileServer(http.Dir(app.UploadDir))
	mux.Handle("/uploads/", http.StripPrefix("/uploads/", uploadFS))

	addr := ":8080"
	log.Printf("Server läuft auf http://localhost%v", addr)
	if err := http.ListenAndServe(addr, securityHeaders(mux)); err != nil {
		log.Fatal(err)
	}
}

func securityHeaders(next http.Handler) http.Handler {
	return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
		w.Header().Set("X-Content-Type-Options", "nosniff")
		w.Header().Set("X-Frame-Options", "DENY")
		w.Header().Set("Referrer-Policy", "no-referrer-when-downgrade")
		w.Header().Set("Content-Security-Policy", "default-src 'self'; img-src 'self' data: https:; style-src 'self' 'unsafe-inline'; script-src 'self'")
		next.ServeHTTP(w, r)
	})
}

// --- DB ---

func initDB(db *sql.DB) error {
	if _, err := db.Exec("PRAGMA foreign_keys = ON;"); err != nil {
		return err
	}
	// Schema
	schema := `
CREATE TABLE IF NOT EXISTS products (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  manufacturer TEXT,
  size TEXT,
  image_path TEXT,
  preferred_vendor TEXT,
  min_stock INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS units (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  expiry_date TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'in',
  added_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_units_product_status ON units(product_id, status);
CREATE INDEX IF NOT EXISTS idx_units_expiry_in ON units(expiry_date) WHERE status = 'in';
`
	_, err := db.Exec(schema)
	return err
}

// Hilfsfunktionen

func atoiDefault(s string, def int) int {
	i, err := strconv.Atoi(strings.TrimSpace(s))
	if err != nil {
		return def
	}
	return i
}

func (a *App) render(w http.ResponseWriter, name string, data any) {
	w.Header().Set("Content-Type", "text/html; charset=utf-8")
	files := []string{"templates/base.gohtml", "templates/partials.gohtml"}
	switch name {
	case "dashboard.gohtml":
		files = append(files, "templates/dashboard.gohtml")
	case "products.gohtml":
		files = append(files, "templates/products.gohtml")
	case "product_create.gohtml":
		files = append(files, "templates/product_create.gohtml")
	case "product_detail.gohtml":
		files = append(files, "templates/product_detail.gohtml")
	case "alerts.gohtml":
		files = append(files, "templates/alerts.gohtml")
	case "shopping_list.gohtml":
		files = append(files, "templates/shopping_list.gohtml")
	default:
		http.Error(w, "Unbekannte Ansicht", http.StatusInternalServerError)
		return
	}
	tmpl := template.Must(template.New("base").Funcs(template.FuncMap{
		"dateHuman": func(iso string) string {
			if iso == "" {
				return ""
			}
			t, err := time.Parse("2006-01-02", iso)
			if err != nil {
				return iso
			}
			return t.Format("02.01.2006")
		},
		"now": time.Now,
	}).ParseFS(embeddedFS, files...))
	if err := tmpl.ExecuteTemplate(w, "base", data); err != nil {
		log.Println("template error:", err)
		http.Error(w, "Template-Fehler", http.StatusInternalServerError)
	}
}

func (a *App) loadProduct(id int) (*Product, error) {
	row := a.DB.QueryRow(`SELECT id, name, manufacturer, size, image_path, preferred_vendor, min_stock FROM products WHERE id=?`, id)
	var p Product
	if err := row.Scan(&p.ID, &p.Name, &p.Manufacturer, &p.Size, &p.ImagePath, &p.PreferredVendor, &p.MinStock); err != nil {
		return nil, err
	}
	stock, _ := a.productStock(p.ID)
	p.CurrentStock = stock
	return &p, nil
}

func (a *App) productStock(productID int) (int, error) {
	row := a.DB.QueryRow(`SELECT COUNT(1) FROM units WHERE product_id=? AND status='in'`, productID)
	var c int
	return c, row.Scan(&c)
}

func (a *App) nextExpiringGlobal() (*Unit, error) {
	row := a.DB.QueryRow(`
SELECT u.id, u.product_id, u.expiry_date, u.status, u.added_at,
       p.id, p.name, p.manufacturer, p.size, p.image_path, p.preferred_vendor, p.min_stock
FROM units u
JOIN products p ON p.id = u.product_id
WHERE u.status='in'
ORDER BY u.expiry_date ASC
LIMIT 1
`)
	var u Unit
	var p Product
	if err := row.Scan(&u.ID, &u.ProductID, &u.ExpiryDate, &u.Status, &u.AddedAt,
		&p.ID, &p.Name, &p.Manufacturer, &p.Size, &p.ImagePath, &p.PreferredVendor, &p.MinStock); err != nil {
		return nil, err
	}
	stock, _ := a.productStock(p.ID)
	p.CurrentStock = stock
	u.Product = &p
	return &u, nil
}

// --- Handler ---

func (a *App) handleDashboard(w http.ResponseWriter, r *http.Request) {
	if r.URL.Path != "/" {
		http.NotFound(w, r)
		return
	}
	// alle Produkte laden
	rows, err := a.DB.Query(`SELECT id, name, manufacturer, size, image_path, preferred_vendor, min_stock FROM products ORDER BY name COLLATE NOCASE`)
	if err != nil {
		http.Error(w, err.Error(), 500)
		return
	}
	defer rows.Close()
	var products []Product
	for rows.Next() {
		var p Product
		if err := rows.Scan(&p.ID, &p.Name, &p.Manufacturer, &p.Size, &p.ImagePath, &p.PreferredVendor, &p.MinStock); err == nil {
			p.CurrentStock, _ = a.productStock(p.ID)
			products = append(products, p)
		}
	}
	var next *Unit
	if u, err := a.nextExpiringGlobal(); err == nil {
		next = u
	}
	// Warnungen zählen
	alerts := 0
	for _, p := range products {
		if p.MinStock > 0 && p.CurrentStock < p.MinStock {
			alerts++
		}
	}
	data := map[string]any{
		"Products": products,
		"Next":     next,
		"Alerts":   alerts,
	}
	a.render(w, "dashboard.gohtml", data)
}

func (a *App) handleProducts(w http.ResponseWriter, r *http.Request) {
	if r.Method == http.MethodGet {
		rows, err := a.DB.Query(`SELECT id, name, manufacturer, size, image_path, preferred_vendor, min_stock FROM products ORDER BY name COLLATE NOCASE`)
		if err != nil {
			http.Error(w, err.Error(), 500)
			return
		}
		defer rows.Close()
		var list []Product
		for rows.Next() {
			var p Product
			if err := rows.Scan(&p.ID, &p.Name, &p.Manufacturer, &p.Size, &p.ImagePath, &p.PreferredVendor, &p.MinStock); err == nil {
				p.CurrentStock, _ = a.productStock(p.ID)
				list = append(list, p)
			}
		}
		data := map[string]any{
			"Products": list,
		}
		a.render(w, "products.gohtml", data)
		return
	}
	if r.Method == http.MethodPost {
		http.Redirect(w, r, "/products/create", http.StatusSeeOther)
		return
	}
	w.WriteHeader(http.StatusMethodNotAllowed)
}

func (a *App) handleCreateProduct(w http.ResponseWriter, r *http.Request) {
	if r.Method == http.MethodGet {
		a.render(w, "product_create.gohtml", nil)
		return
	}
	if r.Method == http.MethodPost {
		if err := r.ParseMultipartForm(20 << 20); err != nil { // 20MB
			http.Error(w, "Ungültiges Formular", 400)
			return
		}
		name := strings.TrimSpace(r.FormValue("name"))
		if name == "" {
			http.Error(w, "Name ist erforderlich", 400)
			return
		}
		manufacturer := strings.TrimSpace(r.FormValue("manufacturer"))
		size := strings.TrimSpace(r.FormValue("size"))
		preferredVendor := strings.TrimSpace(r.FormValue("preferred_vendor"))
		minStock := atoiDefault(r.FormValue("min_stock"), 0)

		imagePath := strings.TrimSpace(r.FormValue("image_url"))
		// Datei-Upload hat Priorität, falls vorhanden
		file, header, err := r.FormFile("image_file")
		if err == nil && header != nil {
			defer file.Close()
			ext := strings.ToLower(filepath.Ext(header.Filename))
			if extOK(ext) {
				fname := fmt.Sprintf("%d_%s", time.Now().UnixNano(), sanitizeFilename(header.Filename))
				outPath := filepath.Join(a.UploadDir, fname)
				out, err := os.Create(outPath)
				if err != nil {
					http.Error(w, "Upload fehlgeschlagen", 500)
					return
				}
				defer out.Close()
				if _, err := io.Copy(out, file); err != nil {
					http.Error(w, "Upload fehlgeschlagen", 500)
					return
				}
				imagePath = "/uploads/" + fname
			}
		}
		res, err := a.DB.Exec(`INSERT INTO products(name, manufacturer, size, image_path, preferred_vendor, min_stock) VALUES(?,?,?,?,?,?)`,
			name, manufacturer, size, imagePath, preferredVendor, minStock)
		if err != nil {
			http.Error(w, err.Error(), 500)
			return
		}
		id64, _ := res.LastInsertId()
		http.Redirect(w, r, fmt.Sprintf("/products/%d", id64), http.StatusSeeOther)
		return
	}
	w.WriteHeader(http.StatusMethodNotAllowed)
}

func sanitizeFilename(s string) string {
	s = strings.ReplaceAll(s, " ", "_")
	s = strings.ReplaceAll(s, ":", "-")
	return s
}

func extOK(ext string) bool {
	switch ext {
	case ".jpg", ".jpeg", ".png", ".gif", ".webp":
		return true
	default:
		return false
	}
}

func (a *App) handleProductDetail(w http.ResponseWriter, r *http.Request) {
	// erwartetes Muster: /products/{id} oder Unterpfade /add-units
	parts := strings.Split(strings.TrimPrefix(r.URL.Path, "/products/"), "/")
	if len(parts) == 0 || parts[0] == "" {
		http.NotFound(w, r)
		return
	}
	id := atoiDefault(parts[0], 0)
	if id == 0 {
		http.NotFound(w, r)
		return
	}

	if len(parts) == 2 && parts[1] == "add-units" {
		if r.Method == http.MethodPost {
			if err := r.ParseForm(); err != nil {
				http.Error(w, "Ungültiges Formular", 400)
				return
			}
			qty := atoiDefault(r.FormValue("quantity"), 0)
			exp := strings.TrimSpace(r.FormValue("expiry_date")) // YYYY-MM-DD
			if qty <= 0 || len(exp) != 10 {
				http.Error(w, "Menge und Datum erforderlich", 400)
				return
			}
			now := time.Now().Format(time.RFC3339)
			tx, err := a.DB.Begin()
			if err != nil {
				http.Error(w, err.Error(), 500)
				return
			}
			stmt, err := tx.Prepare(`INSERT INTO units(product_id, expiry_date, status, added_at) VALUES(?, ?, 'in', ?)`)
			if err != nil {
				tx.Rollback()
				http.Error(w, err.Error(), 500)
				return
			}
			for i := 0; i < qty; i++ {
				if _, err := stmt.Exec(id, exp, now); err != nil {
					tx.Rollback()
					http.Error(w, err.Error(), 500)
					return
				}
			}
			stmt.Close()
			if err := tx.Commit(); err != nil {
				http.Error(w, err.Error(), 500)
				return
			}
			http.Redirect(w, r, fmt.Sprintf("/products/%d", id), http.StatusSeeOther)
			return
		}
		w.WriteHeader(http.StatusMethodNotAllowed)
		return
	}

	// Details anzeigen, optional POST zur Produktbearbeitung (Min-Bestand etc.)
	if r.Method == http.MethodPost {
		if err := r.ParseForm(); err == nil {
			min := atoiDefault(r.FormValue("min_stock"), 0)
			pv := strings.TrimSpace(r.FormValue("preferred_vendor"))
			size := strings.TrimSpace(r.FormValue("size"))
			manufacturer := strings.TrimSpace(r.FormValue("manufacturer"))
			_, err := a.DB.Exec(`UPDATE products SET min_stock=?, preferred_vendor=?, size=?, manufacturer=? WHERE id=?`, min, pv, size, manufacturer, id)
			if err != nil {
				http.Error(w, err.Error(), 500)
				return
			}
			http.Redirect(w, r, fmt.Sprintf("/products/%d", id), http.StatusSeeOther)
			return
		}
	}

	p, err := a.loadProduct(id)
	if err != nil {
		http.Error(w, "Produkt nicht gefunden", 404)
		return
	}

	// Einheiten laden (nur "in")
	rows, err := a.DB.Query(`SELECT id, product_id, expiry_date, status, added_at FROM units WHERE product_id=? AND status='in' ORDER BY expiry_date ASC, id ASC`, id)
	if err != nil {
		http.Error(w, err.Error(), 500)
		return
	}
	defer rows.Close()
	var units []Unit
	for rows.Next() {
		var u Unit
		if err := rows.Scan(&u.ID, &u.ProductID, &u.ExpiryDate, &u.Status, &u.AddedAt); err == nil {
			units = append(units, u)
		}
	}
	var next *Unit
	if len(units) > 0 {
		n := units[0]
		n.Product = p
		next = &n
	}
	data := map[string]any{
		"Product": p,
		"Units":   units,
		"Next":    next,
	}
	a.render(w, "product_detail.gohtml", data)
}

func (a *App) handleUnitActions(w http.ResponseWriter, r *http.Request) {
	// /units/{id}/checkout
	parts := strings.Split(strings.TrimPrefix(r.URL.Path, "/units/"), "/")
	if len(parts) < 2 {
		http.NotFound(w, r)
		return
	}
	unitID := atoiDefault(parts[0], 0)
	action := parts[1]
	if unitID == 0 {
		http.NotFound(w, r)
		return
	}
	if action == "checkout" && r.Method == http.MethodPost {
		// hole Produkt-ID für Redirect
		var productID int
		row := a.DB.QueryRow(`SELECT product_id FROM units WHERE id=?`, unitID)
		if err := row.Scan(&productID); err != nil {
			http.Error(w, "Einheit nicht gefunden", 404)
			return
		}
		_, err := a.DB.Exec(`UPDATE units SET status='out' WHERE id=?`, unitID)
		if err != nil {
			http.Error(w, err.Error(), 500)
			return
		}
		// Entscheide Redirect: falls "from" übergeben, dorthin zurück
		from := r.URL.Query().Get("from")
		if from != "" {
			http.Redirect(w, r, from, http.StatusSeeOther)
			return
		}
		http.Redirect(w, r, fmt.Sprintf("/products/%d", productID), http.StatusSeeOther)
		return
	}
	w.WriteHeader(http.StatusMethodNotAllowed)
}

func (a *App) handleAlerts(w http.ResponseWriter, r *http.Request) {
	rows, err := a.DB.Query(`SELECT id, name, manufacturer, size, image_path, preferred_vendor, min_stock FROM products ORDER BY name COLLATE NOCASE`)
	if err != nil {
		http.Error(w, err.Error(), 500)
		return
	}
	defer rows.Close()
	var below []Product
	for rows.Next() {
		var p Product
		if err := rows.Scan(&p.ID, &p.Name, &p.Manufacturer, &p.Size, &p.ImagePath, &p.PreferredVendor, &p.MinStock); err == nil {
			p.CurrentStock, _ = a.productStock(p.ID)
			if p.MinStock > 0 && p.CurrentStock < p.MinStock {
				below = append(below, p)
			}
		}
	}
	data := map[string]any{
		"Below": below,
	}
	a.render(w, "alerts.gohtml", data)
}

func (a *App) handleShoppingList(w http.ResponseWriter, r *http.Request) {
	// Lade alle Produkte mit aktuellem Bestand, gruppiere in Go nach Händler
	rows, err := a.DB.Query(`
SELECT p.id, p.name, p.manufacturer, p.size, p.image_path, p.preferred_vendor, p.min_stock,
       COALESCE(SUM(CASE WHEN u.status='in' THEN 1 ELSE 0 END), 0) AS stock
FROM products p
LEFT JOIN units u ON u.product_id = p.id
GROUP BY p.id
ORDER BY p.preferred_vendor, p.name COLLATE NOCASE
`)
	if err != nil {
		http.Error(w, err.Error(), 500)
		return
	}
	defer rows.Close()
	type Item struct {
		Product
		Needed int
	}
	group := map[string][]Item{}
	for rows.Next() {
		var p Product
		var stock int
		if err := rows.Scan(&p.ID, &p.Name, &p.Manufacturer, &p.Size, &p.ImagePath, &p.PreferredVendor, &p.MinStock, &stock); err == nil {
			p.CurrentStock = stock
			need := p.MinStock - stock
			if p.MinStock > 0 && need > 0 {
				key := p.PreferredVendor
				if strings.TrimSpace(key) == "" {
					key = "(Kein Händler)"
				}
				group[key] = append(group[key], Item{Product: p, Needed: need})
			}
		}
	}
	// Für stabile Ausgabe auch Liste der Keys bauen
	var vendors []string
	for v := range group {
		vendors = append(vendors, v)
	}
	// einfache Sortierung der Vendor-Namen
	for i := 0; i < len(vendors); i++ {
		for j := i + 1; j < len(vendors); j++ {
			if strings.ToLower(vendors[j]) < strings.ToLower(vendors[i]) {
				vendors[i], vendors[j] = vendors[j], vendors[i]
			}
		}
	}
	data := map[string]any{
		"Groups":  group,
		"Vendors": vendors,
	}
	a.render(w, "shopping_list.gohtml", data)
}