sftrading/db.go

package main

import (
	"database/sql"
	"errors"
	"fmt"
	"math"
	"sort"
	"strings"
	"time"

	_ "github.com/glebarez/sqlite"
)

type Store struct{ db *sql.DB }

func NewStore(path string) (*Store, error) {
	db, err := sql.Open("sqlite", path)
	if err != nil {
		return nil, err
	}
	if _, err := db.Exec(`PRAGMA foreign_keys = ON; PRAGMA busy_timeout = 5000; PRAGMA journal_mode = WAL;`); err != nil {
		return nil, err
	}
	s := &Store{db: db}
	return s, s.migrate()
}

func (s *Store) Close() error { return s.db.Close() }

func (s *Store) migrate() error {
	_, err := s.db.Exec(`
CREATE TABLE IF NOT EXISTS orders (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  submitter_id TEXT NOT NULL,
  submitter_name TEXT NOT NULL,
  acceptor_id TEXT DEFAULT '',
  acceptor_name TEXT DEFAULT '',
  commodity TEXT NOT NULL,
  quality INTEGER NOT NULL CHECK(quality >= 0 AND quality <= 1000),
  quantity TEXT NOT NULL DEFAULT '',
  quantity_amount REAL NOT NULL DEFAULT 0,
  quantity_unit TEXT NOT NULL DEFAULT '',
  deadline TEXT NOT NULL,
  delivery_place TEXT NOT NULL,
  max_budget_auec INTEGER NOT NULL,
  status TEXT NOT NULL,
  channel_id TEXT DEFAULT '',
  message_id TEXT DEFAULT '',
  public_channel_id TEXT DEFAULT '',
  public_message_id TEXT DEFAULT '',
  internal_channel_id TEXT DEFAULT '',
  internal_message_id TEXT DEFAULT '',
  thread_id TEXT DEFAULT '',
  last_feedback TEXT DEFAULT '',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status);
CREATE INDEX IF NOT EXISTS idx_orders_submitter ON orders(submitter_id);
CREATE INDEX IF NOT EXISTS idx_orders_acceptor ON orders(acceptor_id);

CREATE TABLE IF NOT EXISTS inventory (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  normalized_name TEXT NOT NULL,
  quality INTEGER NOT NULL CHECK(quality >= 0 AND quality <= 1000),
  quantity_amount REAL NOT NULL CHECK(quantity_amount >= 0),
  quantity_unit TEXT NOT NULL,
  location TEXT DEFAULT '',
  note TEXT DEFAULT '',
  updated_by_id TEXT NOT NULL,
  updated_by_name TEXT NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  UNIQUE(normalized_name, quality, quantity_unit, location)
);
CREATE INDEX IF NOT EXISTS idx_inventory_lookup ON inventory(normalized_name, quality, quantity_unit);

CREATE TABLE IF NOT EXISTS storage_locations (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  normalized_name TEXT NOT NULL UNIQUE,
  created_by_id TEXT NOT NULL DEFAULT '',
  created_by_name TEXT NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_storage_locations_name ON storage_locations(normalized_name);

CREATE TABLE IF NOT EXISTS order_events (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  order_id INTEGER,
  actor_id TEXT NOT NULL,
  actor_name TEXT NOT NULL,
  event_type TEXT NOT NULL,
  message TEXT NOT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_order_events_order ON order_events(order_id);
`)
	if err != nil {
		return err
	}
	if err := s.createTradingSchema(); err != nil {
		return err
	}
	if err := s.createAppSettingsSchema(); err != nil {
		return err
	}
	if _, err := s.db.Exec(`INSERT OR IGNORE INTO storage_locations(name, normalized_name, created_by_id, created_by_name, created_at, updated_at)
SELECT DISTINCT TRIM(location), lower(TRIM(location)), 'migration', 'migration', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
FROM inventory
WHERE TRIM(location) <> ''`); err != nil {
		return err
	}
	return s.ensureColumns()
}

func (s *Store) ensureColumns() error {
	columns := map[string]string{
		"quantity_amount":     "ALTER TABLE orders ADD COLUMN quantity_amount REAL NOT NULL DEFAULT 0",
		"quantity_unit":       "ALTER TABLE orders ADD COLUMN quantity_unit TEXT NOT NULL DEFAULT ''",
		"public_channel_id":   "ALTER TABLE orders ADD COLUMN public_channel_id TEXT DEFAULT ''",
		"public_message_id":   "ALTER TABLE orders ADD COLUMN public_message_id TEXT DEFAULT ''",
		"internal_channel_id": "ALTER TABLE orders ADD COLUMN internal_channel_id TEXT DEFAULT ''",
		"internal_message_id": "ALTER TABLE orders ADD COLUMN internal_message_id TEXT DEFAULT ''",
		"thread_id":           "ALTER TABLE orders ADD COLUMN thread_id TEXT DEFAULT ''",
	}
	for col, stmt := range columns {
		if !s.columnExists("orders", col) {
			if _, err := s.db.Exec(stmt); err != nil {
				return err
			}
		}
	}
	return nil
}

func (s *Store) columnExists(table, column string) bool {
	rows, err := s.db.Query(`PRAGMA table_info(` + table + `)`)
	if err != nil {
		return false
	}
	defer rows.Close()
	for rows.Next() {
		var cid int
		var name, typ string
		var notnull int
		var dflt any
		var pk int
		if rows.Scan(&cid, &name, &typ, &notnull, &dflt, &pk) == nil && name == column {
			return true
		}
	}
	return false
}

func normalizeName(v string) string { return strings.ToLower(strings.TrimSpace(v)) }

func (s *Store) CreateOrder(o *Order, actorID, actorName string) (int64, error) {
	now := time.Now().UTC()
	tx, err := s.db.Begin()
	if err != nil {
		return 0, err
	}
	defer tx.Rollback()
	res, err := tx.Exec(`INSERT INTO orders
(submitter_id, submitter_name, commodity, quality, quantity, quantity_amount, quantity_unit, deadline, delivery_place, max_budget_auec, status, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		o.SubmitterID, o.SubmitterName, o.Commodity, o.Quality, quantityString(o.QuantityAmount, o.QuantityUnit), o.QuantityAmount, o.QuantityUnit, o.Deadline, o.DeliveryPlace, o.MaxBudgetAUEC, StatusOpen, now, now)
	if err != nil {
		return 0, err
	}
	id, err := res.LastInsertId()
	if err != nil {
		return 0, err
	}
	if err := addEventTx(tx, id, actorID, actorName, EventOrderCreated, "Auftrag erstellt"); err != nil {
		return 0, err
	}
	return id, tx.Commit()
}

func (s *Store) SetMessages(id int64, publicChannelID, publicMessageID, internalChannelID, internalMessageID, threadID string) error {
	_, err := s.db.Exec(`UPDATE orders SET public_channel_id=?, public_message_id=?, internal_channel_id=?, internal_message_id=?, thread_id=?, channel_id=?, message_id=?, updated_at=? WHERE id=?`, publicChannelID, publicMessageID, internalChannelID, internalMessageID, threadID, internalChannelID, internalMessageID, time.Now().UTC(), id)
	return err
}

func (s *Store) GetOrder(id int64) (*Order, error) {
	row := s.db.QueryRow(`SELECT id, submitter_id, submitter_name, acceptor_id, acceptor_name, commodity, quality, quantity_amount, quantity_unit, deadline, delivery_place, max_budget_auec, status, public_channel_id, public_message_id, internal_channel_id, internal_message_id, thread_id, created_at, updated_at, last_feedback FROM orders WHERE id=?`, id)
	return scanOrder(row)
}

func scanOrder(scanner interface{ Scan(dest ...any) error }) (*Order, error) {
	o := &Order{}
	if err := scanner.Scan(&o.ID, &o.SubmitterID, &o.SubmitterName, &o.AcceptorID, &o.AcceptorName, &o.Commodity, &o.Quality, &o.QuantityAmount, &o.QuantityUnit, &o.Deadline, &o.DeliveryPlace, &o.MaxBudgetAUEC, &o.Status, &o.PublicChannelID, &o.PublicMessageID, &o.InternalChannelID, &o.InternalMessageID, &o.ThreadID, &o.CreatedAt, &o.UpdatedAt, &o.LastFeedback); err != nil {
		return nil, err
	}
	return o, nil
}

func (s *Store) ListOrders(status string, limit int) ([]Order, error) {
	return s.listOrders(status, limit, false)
}

func (s *Store) ListArchivedOrders(status string, limit int) ([]Order, error) {
	return s.listOrders(status, limit, true)
}

func (s *Store) listOrders(status string, limit int, archived bool) ([]Order, error) {
	if limit <= 0 || limit > 50 {
		limit = 10
	}
	query := `SELECT id, submitter_id, submitter_name, acceptor_id, acceptor_name, commodity, quality, quantity_amount, quantity_unit, deadline, delivery_place, max_budget_auec, status, public_channel_id, public_message_id, internal_channel_id, internal_message_id, thread_id, created_at, updated_at, last_feedback FROM orders`
	args := []any{}

	if status != "" {
		query += ` WHERE status=?`
		args = append(args, status)
	} else if archived {
		query += ` WHERE status IN (?, ?, ?)`
		args = append(args, StatusCompleted, StatusDeclined, StatusCancelled)
	} else {
		query += ` WHERE status NOT IN (?, ?, ?)`
		args = append(args, StatusCompleted, StatusDeclined, StatusCancelled)
	}

	query += ` ORDER BY updated_at DESC LIMIT ?`
	args = append(args, limit)
	rows, err := s.db.Query(query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Order
	for rows.Next() {
		o, err := scanOrder(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, *o)
	}
	return out, rows.Err()
}

func (s *Store) ListOrdersWithDeletableThreads(cutoff time.Time, limit int) ([]Order, error) {
	if limit <= 0 || limit > 100 {
		limit = 50
	}
	rows, err := s.db.Query(`SELECT id, submitter_id, submitter_name, acceptor_id, acceptor_name, commodity, quality, quantity_amount, quantity_unit, deadline, delivery_place, max_budget_auec, status, public_channel_id, public_message_id, internal_channel_id, internal_message_id, thread_id, created_at, updated_at, last_feedback FROM orders WHERE thread_id <> '' AND status IN (?, ?, ?) AND updated_at <= ? ORDER BY updated_at ASC LIMIT ?`, StatusCompleted, StatusDeclined, StatusCancelled, cutoff.UTC(), limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Order
	for rows.Next() {
		o, err := scanOrder(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, *o)
	}
	return out, rows.Err()
}

func (s *Store) ClearThreadID(orderID int64) error {
	_, err := s.db.Exec(`UPDATE orders SET thread_id='', updated_at=? WHERE id=?`, time.Now().UTC(), orderID)
	return err
}

func (s *Store) AcceptOrder(id int64, userID, userName string) (*Order, error) {
	tx, err := s.db.Begin()
	if err != nil {
		return nil, err
	}
	defer tx.Rollback()
	res, err := tx.Exec(`UPDATE orders SET status=?, acceptor_id=?, acceptor_name=?, updated_at=? WHERE id=? AND status=?`, StatusAccepted, userID, userName, time.Now().UTC(), id, StatusOpen)
	if err != nil {
		return nil, err
	}
	n, _ := res.RowsAffected()
	if n == 0 {
		return nil, errors.New("auftrag ist nicht mehr offen")
	}
	if err := addEventTx(tx, id, userID, userName, EventOrderAccepted, "Auftrag angenommen"); err != nil {
		return nil, err
	}
	if err := tx.Commit(); err != nil {
		return nil, err
	}
	return s.GetOrder(id)
}

func (s *Store) DeclineOrder(id int64, userID, userName, feedback string) (*Order, error) {
	tx, err := s.db.Begin()
	if err != nil {
		return nil, err
	}
	defer tx.Rollback()
	res, err := tx.Exec(`UPDATE orders SET status=?, last_feedback=?, updated_at=? WHERE id=? AND status=?`, StatusDeclined, feedback, time.Now().UTC(), id, StatusOpen)
	if err != nil {
		return nil, err
	}
	n, _ := res.RowsAffected()
	if n == 0 {
		return nil, errors.New("auftrag ist nicht mehr offen")
	}
	if err := addEventTx(tx, id, userID, userName, EventOrderDeclined, feedback); err != nil {
		return nil, err
	}
	if err := tx.Commit(); err != nil {
		return nil, err
	}
	return s.GetOrder(id)
}

func (s *Store) SetStatus(id int64, status, feedback, actorID, actorName string) (*Order, error) {
	tx, err := s.db.Begin()
	if err != nil {
		return nil, err
	}
	defer tx.Rollback()
	res, err := tx.Exec(`UPDATE orders SET status=?, last_feedback=?, updated_at=? WHERE id=?`, status, feedback, time.Now().UTC(), id)
	if err != nil {
		return nil, err
	}
	n, _ := res.RowsAffected()
	if n == 0 {
		return nil, sql.ErrNoRows
	}
	if err := addEventTx(tx, id, actorID, actorName, EventOrderStatus, fmt.Sprintf("Status auf %s gesetzt: %s", statusLabel(status), feedback)); err != nil {
		return nil, err
	}
	if err := tx.Commit(); err != nil {
		return nil, err
	}
	return s.GetOrder(id)
}

func (s *Store) AddEvent(orderID int64, actorID, actorName, eventType, message string) error {
	_, err := s.db.Exec(`INSERT INTO order_events(order_id, actor_id, actor_name, event_type, message, created_at) VALUES (?, ?, ?, ?, ?, ?)`, nullableOrderID(orderID), actorID, actorName, eventType, message, time.Now().UTC())
	return err
}

func addEventTx(tx *sql.Tx, orderID int64, actorID, actorName, eventType, message string) error {
	_, err := tx.Exec(`INSERT INTO order_events(order_id, actor_id, actor_name, event_type, message, created_at) VALUES (?, ?, ?, ?, ?, ?)`, nullableOrderID(orderID), actorID, actorName, eventType, message, time.Now().UTC())
	return err
}

func nullableOrderID(id int64) any {
	if id <= 0 {
		return nil
	}
	return id
}

func (s *Store) UpsertInventory(item InventoryItem, delta bool) (*InventoryItem, error) {
	now := time.Now().UTC()
	item.Name = strings.TrimSpace(item.Name)
	item.Location = strings.TrimSpace(item.Location)
	item.NormalizedName = normalizeName(item.Name)
	if item.Location == "" {
		return nil, errors.New("lagerort ist ein pflichtfeld")
	}
	exists, err := s.StorageLocationExists(item.Location)
	if err != nil {
		return nil, err
	}
	if !exists {
		return nil, errors.New("lagerort existiert nicht. Bitte zuerst mit /lagerort_add anlegen")
	}

	tx, err := s.db.Begin()
	if err != nil {
		return nil, err
	}
	defer tx.Rollback()

	var existing InventoryItem
	err = tx.QueryRow(`SELECT id, name, normalized_name, quality, quantity_amount, quantity_unit, location, note, updated_by_id, updated_by_name, created_at, updated_at FROM inventory WHERE normalized_name=? AND quality=? AND quantity_unit=? AND location=?`, item.NormalizedName, item.Quality, item.QuantityUnit, item.Location).Scan(&existing.ID, &existing.Name, &existing.NormalizedName, &existing.Quality, &existing.QuantityAmount, &existing.QuantityUnit, &existing.Location, &existing.Note, &existing.UpdatedByID, &existing.UpdatedByName, &existing.CreatedAt, &existing.UpdatedAt)
	if err != nil && !errors.Is(err, sql.ErrNoRows) {
		return nil, err
	}

	var result InventoryItem
	if errors.Is(err, sql.ErrNoRows) {
		if item.QuantityAmount <= 0 {
			return nil, errors.New("kein ausreichender Lagerbestand vorhanden")
		}
		res, err := tx.Exec(`INSERT INTO inventory(name, normalized_name, quality, quantity_amount, quantity_unit, location, note, updated_by_id, updated_by_name, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`, item.Name, item.NormalizedName, item.Quality, item.QuantityAmount, item.QuantityUnit, item.Location, item.Note, item.UpdatedByID, item.UpdatedByName, now, now)
		if err != nil {
			return nil, err
		}
		id, _ := res.LastInsertId()
		result = item
		result.ID = id
		result.CreatedAt = now
		result.UpdatedAt = now
	} else {
		newQty := item.QuantityAmount
		if delta {
			newQty = existing.QuantityAmount + item.QuantityAmount
		}
		if newQty < 0 {
			return nil, errors.New("lagerbestand darf nicht negativ werden")
		}

		result = existing
		result.Name = item.Name
		result.QuantityAmount = newQty
		result.Note = item.Note
		result.UpdatedByID = item.UpdatedByID
		result.UpdatedByName = item.UpdatedByName
		result.UpdatedAt = now

		if newQty == 0 {
			if _, err := tx.Exec(`DELETE FROM inventory WHERE id=?`, existing.ID); err != nil {
				return nil, err
			}
		} else {
			if _, err := tx.Exec(`UPDATE inventory SET name=?, quantity_amount=?, note=?, updated_by_id=?, updated_by_name=?, updated_at=? WHERE id=?`, item.Name, newQty, item.Note, item.UpdatedByID, item.UpdatedByName, now, existing.ID); err != nil {
				return nil, err
			}
		}
	}

	if err := addEventTx(tx, 0, item.UpdatedByID, item.UpdatedByName, EventInventoryChanged, fmt.Sprintf("Lager %s Q%d %s %s", item.Name, item.Quality, formatAmount(item.QuantityAmount), item.QuantityUnit)); err != nil {
		return nil, err
	}
	if err := tx.Commit(); err != nil {
		return nil, err
	}
	return &result, nil
}

func (s *Store) GetInventoryByKey(normalized string, quality int, unit, location string) (*InventoryItem, error) {
	row := s.db.QueryRow(`SELECT id, name, normalized_name, quality, quantity_amount, quantity_unit, location, note, updated_by_id, updated_by_name, created_at, updated_at FROM inventory WHERE normalized_name=? AND quality=? AND quantity_unit=? AND location=?`, normalized, quality, unit, location)
	return scanInventory(row)
}

func scanInventory(scanner interface{ Scan(dest ...any) error }) (*InventoryItem, error) {
	it := &InventoryItem{}
	if err := scanner.Scan(&it.ID, &it.Name, &it.NormalizedName, &it.Quality, &it.QuantityAmount, &it.QuantityUnit, &it.Location, &it.Note, &it.UpdatedByID, &it.UpdatedByName, &it.CreatedAt, &it.UpdatedAt); err != nil {
		return nil, err
	}
	return it, nil
}

func (s *Store) ListInventory(query string, limit int) ([]InventoryItem, error) {
	if limit <= 0 || limit > 50 {
		limit = 20
	}
	q := `SELECT id, name, normalized_name, quality, quantity_amount, quantity_unit, location, note, updated_by_id, updated_by_name, created_at, updated_at FROM inventory WHERE quantity_amount > 0`
	args := []any{}
	if strings.TrimSpace(query) != "" {
		q += ` AND (normalized_name LIKE ? OR location LIKE ?)`
		like := "%" + normalizeName(query) + "%"
		args = append(args, like, like)
	}
	q += ` ORDER BY normalized_name, quality DESC LIMIT ?`
	args = append(args, limit)
	rows, err := s.db.Query(q, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []InventoryItem
	for rows.Next() {
		it, err := scanInventory(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, *it)
	}
	return out, rows.Err()
}

func (s *Store) FindInventoryForOrder(o *Order) ([]InventoryMatch, float64, error) {
	rows, err := s.db.Query(`SELECT id, name, normalized_name, quality, quantity_amount, quantity_unit, location, note, updated_by_id, updated_by_name, created_at, updated_at FROM inventory WHERE normalized_name=? AND quality>=? AND quantity_unit=? AND quantity_amount > 0 ORDER BY quality DESC, quantity_amount DESC`, normalizeName(o.Commodity), o.Quality, o.QuantityUnit)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()
	var matches []InventoryMatch
	var total float64
	for rows.Next() {
		it, err := scanInventory(rows)
		if err != nil {
			return nil, 0, err
		}
		total += it.QuantityAmount
		matches = append(matches, InventoryMatch{Item: *it, RequestedQuantity: o.QuantityAmount, Available: it.QuantityAmount >= o.QuantityAmount})
	}
	return matches, total, rows.Err()
}

func scanStorageLocation(scanner interface{ Scan(dest ...any) error }) (*StorageLocation, error) {
	loc := &StorageLocation{}
	if err := scanner.Scan(&loc.ID, &loc.Name, &loc.NormalizedName, &loc.CreatedByID, &loc.CreatedByName, &loc.CreatedAt, &loc.UpdatedAt); err != nil {
		return nil, err
	}
	return loc, nil
}

func (s *Store) CreateStorageLocation(name, actorID, actorName string) (*StorageLocation, error) {
	name = strings.TrimSpace(name)
	if name == "" {
		return nil, errors.New("lagerort darf nicht leer sein")
	}
	now := time.Now().UTC()
	normalized := normalizeName(name)
	_, err := s.db.Exec(`INSERT INTO storage_locations(name, normalized_name, created_by_id, created_by_name, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?)`, name, normalized, actorID, actorName, now, now)
	if err != nil {
		if strings.Contains(strings.ToLower(err.Error()), "unique") {
			return nil, errors.New("lagerort existiert bereits")
		}
		return nil, err
	}
	return s.GetStorageLocationByName(name)
}

func (s *Store) GetStorageLocationByName(name string) (*StorageLocation, error) {
	row := s.db.QueryRow(`SELECT id, name, normalized_name, created_by_id, created_by_name, created_at, updated_at FROM storage_locations WHERE normalized_name=?`, normalizeName(name))
	return scanStorageLocation(row)
}

func (s *Store) StorageLocationExists(name string) (bool, error) {
	if strings.TrimSpace(name) == "" {
		return false, nil
	}
	var exists int
	err := s.db.QueryRow(`SELECT 1 FROM storage_locations WHERE normalized_name=? LIMIT 1`, normalizeName(name)).Scan(&exists)
	if errors.Is(err, sql.ErrNoRows) {
		return false, nil
	}
	if err != nil {
		return false, err
	}
	return true, nil
}

func (s *Store) DeleteStorageLocationIfEmpty(name string) (*StorageLocation, error) {
	name = strings.TrimSpace(name)
	if name == "" {
		return nil, errors.New("lagerort darf nicht leer sein")
	}
	tx, err := s.db.Begin()
	if err != nil {
		return nil, err
	}
	defer tx.Rollback()

	row := tx.QueryRow(`SELECT id, name, normalized_name, created_by_id, created_by_name, created_at, updated_at FROM storage_locations WHERE normalized_name=?`, normalizeName(name))
	loc, err := scanStorageLocation(row)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, errors.New("lagerort nicht gefunden")
		}
		return nil, err
	}

	var used int
	if err := tx.QueryRow(`SELECT COUNT(*) FROM inventory WHERE lower(TRIM(location))=? AND quantity_amount > 0`, loc.NormalizedName).Scan(&used); err != nil {
		return nil, err
	}
	if used > 0 {
		return nil, fmt.Errorf("lagerort ist nicht leer (%d aktive Lagerpositionen)", used)
	}

	if _, err := tx.Exec(`DELETE FROM storage_locations WHERE id=?`, loc.ID); err != nil {
		return nil, err
	}
	if err := tx.Commit(); err != nil {
		return nil, err
	}
	return loc, nil
}

func (s *Store) ListStorageLocations(query string, limit int) ([]StorageLocation, error) {
	if limit <= 0 || limit > 25 {
		limit = 25
	}
	q := `SELECT id, name, normalized_name, created_by_id, created_by_name, created_at, updated_at FROM storage_locations`
	args := []any{}
	if strings.TrimSpace(query) != "" {
		q += ` WHERE normalized_name LIKE ?`
		args = append(args, "%"+normalizeName(query)+"%")
	}
	q += ` ORDER BY name COLLATE NOCASE LIMIT ?`
	args = append(args, limit)
	rows, err := s.db.Query(q, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []StorageLocation
	for rows.Next() {
		loc, err := scanStorageLocation(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, *loc)
	}
	return out, rows.Err()
}

func quantityString(amount float64, unit string) string {
	return fmt.Sprintf("%s %s", formatAmount(amount), unit)
}
func formatAmount(v float64) string {
	if math.Abs(v-math.Round(v)) < 0.00001 {
		return fmt.Sprintf("%.0f", v)
	}
	return fmt.Sprintf("%.2f", v)
}

func (s *Store) createTradingSchema() error {
	_, err := s.db.Exec(`
CREATE TABLE IF NOT EXISTS trading_runs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id TEXT NOT NULL,
  user_name TEXT NOT NULL,
  purpose TEXT NOT NULL,
  ship TEXT NOT NULL,
  buy_value INTEGER NOT NULL DEFAULT 0,
  sell_value INTEGER NOT NULL DEFAULT 0,
  costs INTEGER NOT NULL DEFAULT 0,
  repetitions INTEGER NOT NULL DEFAULT 1,
  scu REAL NOT NULL DEFAULT 0,
  distance REAL NOT NULL DEFAULT 0,
  scu_grade TEXT NOT NULL DEFAULT '',
  commodity TEXT NOT NULL,
  post_a TEXT NOT NULL DEFAULT '',
  post_b TEXT NOT NULL DEFAULT '',
  note TEXT NOT NULL DEFAULT '',
  month INTEGER NOT NULL CHECK(month >= 1 AND month <= 12),
  year INTEGER NOT NULL CHECK(year >= 2000 AND year <= 2200),
  org_percent REAL NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  deleted_at DATETIME,
  deleted_by_id TEXT NOT NULL DEFAULT ''
);
CREATE INDEX IF NOT EXISTS idx_trading_month_year ON trading_runs(year, month);
CREATE INDEX IF NOT EXISTS idx_trading_user ON trading_runs(user_id);
CREATE INDEX IF NOT EXISTS idx_trading_deleted ON trading_runs(deleted_at);
`)
	return err
}

func (s *Store) CreateTradingRun(t *TradingRun) (int64, error) {
	now := time.Now().UTC()
	if t.Month < 1 || t.Month > 12 {
		return 0, errors.New("monat muss zwischen 1 und 12 liegen")
	}
	if t.Year < 2000 || t.Year > 2200 {
		return 0, errors.New("jahr muss zwischen 2000 und 2200 liegen")
	}
	if t.Repetitions <= 0 {
		t.Repetitions = 1
	}
	res, err := s.db.Exec(`INSERT INTO trading_runs
(user_id, user_name, purpose, ship, buy_value, sell_value, costs, repetitions, scu, distance, scu_grade, commodity, post_a, post_b, note, month, year, org_percent, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		t.UserID, t.UserName, t.Purpose, t.Ship, t.BuyValue, t.SellValue, t.Costs, t.Repetitions, t.SCU, t.Distance, t.SCUGrade, t.Commodity, t.PostA, t.PostB, t.Note, t.Month, t.Year, t.OrgPercent, now, now)
	if err != nil {
		return 0, err
	}
	return res.LastInsertId()
}

func scanTradingRun(scanner interface{ Scan(dest ...any) error }) (*TradingRun, error) {
	t := &TradingRun{}
	if err := scanner.Scan(&t.ID, &t.UserID, &t.UserName, &t.Purpose, &t.Ship, &t.BuyValue, &t.SellValue, &t.Costs, &t.Repetitions, &t.SCU, &t.Distance, &t.SCUGrade, &t.Commodity, &t.PostA, &t.PostB, &t.Note, &t.Month, &t.Year, &t.OrgPercent, &t.CreatedAt, &t.UpdatedAt, &t.DeletedAt, &t.DeletedByID); err != nil {
		return nil, err
	}
	return t, nil
}

func (s *Store) GetTradingRun(id int64) (*TradingRun, error) {
	row := s.db.QueryRow(`SELECT id, user_id, user_name, purpose, ship, buy_value, sell_value, costs, repetitions, scu, distance, scu_grade, commodity, post_a, post_b, note, month, year, org_percent, created_at, updated_at, deleted_at, deleted_by_id FROM trading_runs WHERE id=?`, id)
	return scanTradingRun(row)
}

func (s *Store) DeleteTradingRun(id int64, actorID string) (*TradingRun, error) {
	t, err := s.GetTradingRun(id)
	if err != nil {
		return nil, err
	}
	if t.DeletedAt.Valid {
		return nil, errors.New("trading-run ist bereits gelöscht")
	}
	_, err = s.db.Exec(`UPDATE trading_runs SET deleted_at=?, deleted_by_id=?, updated_at=? WHERE id=? AND deleted_at IS NULL`, time.Now().UTC(), actorID, time.Now().UTC(), id)
	if err != nil {
		return nil, err
	}
	return s.GetTradingRun(id)
}

func (s *Store) ListTradingRuns(f TradingListFilter) ([]TradingRun, error) {
	if f.Limit <= 0 {
		f.Limit = 20
	}
	if f.Limit > 5000 {
		f.Limit = 5000
	}
	q := `SELECT id, user_id, user_name, purpose, ship, buy_value, sell_value, costs, repetitions, scu, distance, scu_grade, commodity, post_a, post_b, note, month, year, org_percent, created_at, updated_at, deleted_at, deleted_by_id FROM trading_runs WHERE deleted_at IS NULL`
	args := []any{}
	if f.Month > 0 {
		q += ` AND month=?`
		args = append(args, f.Month)
	}
	if f.Year > 0 {
		q += ` AND year=?`
		args = append(args, f.Year)
	}
	if strings.TrimSpace(f.UserID) != "" {
		q += ` AND user_id=?`
		args = append(args, f.UserID)
	}
	q += ` ORDER BY year DESC, month DESC, created_at DESC LIMIT ?`
	args = append(args, f.Limit)
	rows, err := s.db.Query(q, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []TradingRun
	for rows.Next() {
		t, err := scanTradingRun(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, *t)
	}
	return out, rows.Err()
}

func (s *Store) SummarizeTradingRuns(month, year int) (*TradingSummary, error) {
	runs, err := s.ListTradingRuns(TradingListFilter{Month: month, Year: year, Limit: 100000})
	if err != nil {
		return nil, err
	}
	sum := &TradingSummary{Month: month, Year: year, Count: len(runs)}
	traderMap := map[string]*TradingTraderSummary{}
	purposeMap := map[string]*TradingPurposeSummary{}
	for _, r := range runs {
		profit := tradingProfit(r)
		share := tradingOrgShare(r)
		scu := r.SCU * float64(r.Repetitions)
		costs := r.Costs * r.Repetitions
		distance := r.Distance * float64(r.Repetitions)
		sum.GrossProfit += profit
		sum.OrgShare += share
		sum.TotalSCU += scu
		sum.TotalCosts += costs
		sum.TotalDistance += distance

		key := r.UserID
		if key == "" {
			key = r.UserName
		}
		tr := traderMap[key]
		if tr == nil {
			tr = &TradingTraderSummary{UserID: r.UserID, UserName: r.UserName}
			traderMap[key] = tr
		}
		tr.Profit += profit
		tr.OrgShare += share
		tr.SCU += scu
		tr.Runs++

		ps := purposeMap[r.Purpose]
		if ps == nil {
			ps = &TradingPurposeSummary{Purpose: r.Purpose}
			purposeMap[r.Purpose] = ps
		}
		ps.Profit += profit
		ps.OrgShare += share
		ps.SCU += scu
		ps.Runs++
	}
	for _, tr := range traderMap {
		sum.TopTraders = append(sum.TopTraders, *tr)
	}
	for _, ps := range purposeMap {
		sum.PurposeSummary = append(sum.PurposeSummary, *ps)
	}
	sortTradingSummary(sum)
	return sum, nil
}

func tradingProfit(r TradingRun) int64 {
	return (r.SellValue - r.BuyValue - r.Costs) * r.Repetitions
}

func tradingOrgShare(r TradingRun) int64 {
	profit := tradingProfit(r)
	if profit <= 0 || r.OrgPercent <= 0 {
		return 0
	}
	return int64(math.Round(float64(profit) * r.OrgPercent))
}

func sortTradingSummary(sum *TradingSummary) {
	sort.Slice(sum.TopTraders, func(i, j int) bool {
		if sum.TopTraders[i].Profit == sum.TopTraders[j].Profit {
			return sum.TopTraders[i].UserName < sum.TopTraders[j].UserName
		}
		return sum.TopTraders[i].Profit > sum.TopTraders[j].Profit
	})
	if len(sum.TopTraders) > 3 {
		sum.TopTraders = sum.TopTraders[:3]
	}
	sort.Slice(sum.PurposeSummary, func(i, j int) bool { return sum.PurposeSummary[i].Purpose < sum.PurposeSummary[j].Purpose })
}

func (s *Store) SummarizeTradingByUser(month, year int) ([]TradingTraderSummary, error) {
	runs, err := s.ListTradingRuns(TradingListFilter{Month: month, Year: year, Limit: 100000})
	if err != nil {
		return nil, err
	}
	traderMap := map[string]*TradingTraderSummary{}
	for _, r := range runs {
		key := r.UserID
		if key == "" {
			key = r.UserName
		}
		tr := traderMap[key]
		if tr == nil {
			tr = &TradingTraderSummary{UserID: r.UserID, UserName: r.UserName}
			traderMap[key] = tr
		}
		tr.Profit += tradingProfit(r)
		tr.OrgShare += tradingOrgShare(r)
		tr.SCU += r.SCU * float64(r.Repetitions)
		tr.Runs++
	}
	out := make([]TradingTraderSummary, 0, len(traderMap))
	for _, tr := range traderMap {
		out = append(out, *tr)
	}
	sort.Slice(out, func(i, j int) bool {
		if out[i].OrgShare == out[j].OrgShare {
			return strings.ToLower(out[i].UserName) < strings.ToLower(out[j].UserName)
		}
		return out[i].OrgShare > out[j].OrgShare
	})
	return out, nil
}

func (s *Store) createAppSettingsSchema() error {
	_, err := s.db.Exec(`
CREATE TABLE IF NOT EXISTS app_settings (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL DEFAULT '',
  updated_at DATETIME NOT NULL
);`)
	return err
}

func (s *Store) SetSetting(key, value string) error {
	_, err := s.db.Exec(`INSERT INTO app_settings(key, value, updated_at) VALUES(?, ?, ?)
ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`, key, value, time.Now().UTC())
	return err
}

func (s *Store) GetSetting(key string) (string, error) {
	var value string
	err := s.db.QueryRow(`SELECT value FROM app_settings WHERE key=?`, key).Scan(&value)
	return value, err
}