startrading/internal/handlers/app.go

package handlers

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"html/template"
	"net/http"
	"strconv"
	"strings"
	"time"

	"trading-tool/internal/auth"
)

type App struct {
	DB *sql.DB
	T  *template.Template
}
type Row map[string]any

func New(db *sql.DB) *App {
	return &App{DB: db, T: template.Must(template.ParseGlob("web/templates/*.html"))}
}
func (a *App) LoginPage(w http.ResponseWriter, r *http.Request) {
	a.T.ExecuteTemplate(w, "login.html", nil)
}

func (a *App) Home(w http.ResponseWriter, r *http.Request) {
	requests := a.requestRows(r.Context(), ``, 50)
	depts := a.departmentRows(r.Context())
	quests := a.questRows(r.Context(), 0, 80)
	a.T.ExecuteTemplate(w, "home.html", map[string]any{"Requests": requests, "Quests": quests, "Departments": depts})
}

func (a *App) NewRequest(w http.ResponseWriter, r *http.Request) {
	if r.Method == "GET" {
		a.T.ExecuteTemplate(w, "request.html", map[string]any{"Items": a.itemRows(r.Context()), "Departments": a.departmentRows(r.Context())})
		return
	}
	if err := r.ParseForm(); err != nil {
		http.Error(w, err.Error(), 400)
		return
	}
	item, _ := strconv.Atoi(r.FormValue("item_id"))
	qty, _ := strconv.Atoi(r.FormValue("quantity"))
	price, _ := strconv.ParseFloat(r.FormValue("price_limit"), 64)
	source := nullInt(r.FormValue("source_department_id"))
	target := nullInt(r.FormValue("target_department_id"))
	kind := r.FormValue("kind")
	if kind != "sell" {
		kind = "buy"
	}
	if item <= 0 || qty <= 0 {
		http.Error(w, "Item und Menge sind Pflicht", 400)
		return
	}
	tx, err := a.DB.BeginTx(r.Context(), nil)
	if err != nil {
		http.Error(w, err.Error(), 500)
		return
	}
	defer tx.Rollback()
	res, err := tx.ExecContext(r.Context(), `insert into trade_requests(kind,item_id,quantity,price_limit,source_department_id,target_department_id,status,created_by,notes) values(?,?,?,?,?,?, 'new',?,?)`, kind, item, qty, price, source, target, nullInt(auth.UserID(r)), r.FormValue("notes"))
	if err != nil {
		http.Error(w, err.Error(), 500)
		return
	}
	id, _ := res.LastInsertId()
	if err := a.generateQuestsTx(r.Context(), tx, id, kind, item, qty, price, source, target); err != nil {
		http.Error(w, err.Error(), 500)
		return
	}
	if err := tx.Commit(); err != nil {
		http.Error(w, err.Error(), 500)
		return
	}
	http.Redirect(w, r, "/", 303)
}

type workflowRule struct {
	ID                  int64
	Name, Cond, Actions string
}
type condition map[string]any

func (a *App) generateQuestsTx(ctx context.Context, tx *sql.Tx, reqID int64, kind string, itemID, qty int, price float64, source, target sql.NullInt64) error {
	var item, category string
	if err := tx.QueryRowContext(ctx, `select name,category from items where id=?`, itemID).Scan(&item, &category); err != nil {
		return err
	}
	var auto string
	_ = tx.QueryRowContext(ctx, `select setting_value from app_settings where setting_key='auto_generate_quests'`).Scan(&auto)
	if auto == "false" || auto == "0" || auto == "no" {
		return nil
	}
	rule, templateTypes := a.matchWorkflow(ctx, tx, kind, category, qty, price, source, target)
	rows, err := queryTemplates(ctx, tx, templateTypes)
	if err != nil {
		return err
	}
	defer rows.Close()
	var prev sql.NullInt64
	created := 0
	for rows.Next() {
		var typ, title string
		var dept sql.NullInt64
		var percent float64
		var order int
		if err := rows.Scan(&typ, &title, &dept, &percent, &order); err != nil {
			return err
		}
		title = strings.ReplaceAll(title, "{{item}}", item)
		title = strings.ReplaceAll(title, "{{quantity}}", strconv.Itoa(qty))
		status := "open"
		if created > 0 {
			status = "waiting"
		}
		res, err := tx.ExecContext(ctx, `insert into quests(parent_request_id,depends_on_quest_id,type,title,department_id,reward,status,sequence_order) values(?,?,?,?,?,?,?,?)`, reqID, prev, typ, title, dept, price*percent, status, order)
		if err != nil {
			return err
		}
		qid, _ := res.LastInsertId()
		if prev.Valid {
			_, _ = tx.ExecContext(ctx, `insert ignore into quest_dependencies(quest_id,depends_on_quest_id) values(?,?)`, qid, prev.Int64)
		}
		prev = sql.NullInt64{Int64: qid, Valid: true}
		created++
	}
	st := "in_progress"
	if created == 0 {
		st = "fulfilled"
	}
	_, err = tx.ExecContext(ctx, `update trade_requests set status=?, workflow_rule_id=? where id=?`, st, rule, reqID)
	if err != nil {
		return err
	}
	_, _ = tx.ExecContext(ctx, `insert into events(type,payload) values('request.created', JSON_OBJECT('request_id',?,'quests_created',?,'workflow_rule_id',?))`, reqID, created, rule)
	return nil
}

func (a *App) matchWorkflow(ctx context.Context, tx *sql.Tx, kind, category string, qty int, price float64, source, target sql.NullInt64) (sql.NullInt64, []string) {
	rows, err := tx.QueryContext(ctx, `select id,name,condition_json,actions_json from workflow_rules where enabled=true and trigger_name='request.created' order by id`)
	if err != nil {
		return sql.NullInt64{}, nil
	}
	defer rows.Close()
	for rows.Next() {
		var wr workflowRule
		rows.Scan(&wr.ID, &wr.Name, &wr.Cond, &wr.Actions)
		if conditionMatches(wr.Cond, kind, category, qty, price, source, target) {
			var actions []string
			_ = json.Unmarshal([]byte(wr.Actions), &actions)
			return sql.NullInt64{Int64: wr.ID, Valid: true}, actions
		}
	}
	return sql.NullInt64{}, nil
}

func conditionMatches(raw, kind, cat string, qty int, price float64, source, target sql.NullInt64) bool {
	if strings.TrimSpace(raw) == "" || strings.TrimSpace(raw) == "{}" {
		return true
	}
	var c map[string]any
	if json.Unmarshal([]byte(raw), &c) != nil {
		return false
	}
	eqs := map[string]string{"kind": kind, "item_category": cat}
	for k, v := range eqs {
		if want, ok := c[k].(string); ok && want != "" && want != v {
			return false
		}
	}
	if v, ok := num(c["quantity_min"]); ok && float64(qty) < v {
		return false
	}
	if v, ok := num(c["quantity_max"]); ok && float64(qty) > v {
		return false
	}
	if v, ok := num(c["price_min"]); ok && price < v {
		return false
	}
	if v, ok := num(c["price_max"]); ok && price > v {
		return false
	}
	return true
}
func num(v any) (float64, bool) {
	switch t := v.(type) {
	case float64:
		return t, true
	case int:
		return float64(t), true
	default:
		return 0, false
	}
}

func queryTemplates(ctx context.Context, tx *sql.Tx, types []string) (*sql.Rows, error) {
	if len(types) == 0 {
		return tx.QueryContext(ctx, `select type,title_template,department_id,reward_percent,sort_order from quest_templates where enabled=true order by sort_order,id`)
	}
	qs := strings.TrimRight(strings.Repeat("?,", len(types)), ",")
	args := make([]any, len(types))
	for i, t := range types {
		args[i] = t
	}
	return tx.QueryContext(ctx, fmt.Sprintf(`select type,title_template,department_id,reward_percent,sort_order from quest_templates where enabled=true and type in (%s) order by field(type,%s)`, qs, qs), append(args, args...)...)
}

func (a *App) UpdateQuest(w http.ResponseWriter, r *http.Request) {
	if err := r.ParseForm(); err != nil {
		http.Error(w, err.Error(), 400)
		return
	}
	id, _ := strconv.Atoi(r.FormValue("id"))
	status := r.FormValue("status")
	note := r.FormValue("note")
	if !validQuestStatus(status) {
		http.Error(w, "Ungültiger Status", 400)
		return
	}
	tx, err := a.DB.BeginTx(r.Context(), nil)
	if err != nil {
		http.Error(w, err.Error(), 500)
		return
	}
	defer tx.Rollback()
	var old string
	var reqID sql.NullInt64
	if err := tx.QueryRowContext(r.Context(), `select status,parent_request_id from quests where id=? for update`, id).Scan(&old, &reqID); err != nil {
		http.Error(w, err.Error(), 404)
		return
	}
	if old == "waiting" && status != "cancelled" {
		http.Error(w, "Quest wartet noch auf vorherige Abhängigkeiten", 409)
		return
	}
	uid := nullInt(auth.UserID(r))
	completed := sql.NullTime{}
	if status == "done" {
		completed = sql.NullTime{Time: time.Now(), Valid: true}
	}
	_, err = tx.ExecContext(r.Context(), `update quests set status=?, contractor_note=?, blocked_reason=case when ?='blocked' then ? else blocked_reason end, claimed_by=coalesce(claimed_by,?), completed_at=? where id=?`, status, note, status, note, uid, completed, id)
	if err != nil {
		http.Error(w, err.Error(), 500)
		return
	}
	_, _ = tx.ExecContext(r.Context(), `insert into quest_updates(quest_id,actor_user_id,status,note) values(?,?,?,?)`, id, uid, status, note)
	if status == "done" {
		_ = a.unlockDependents(r.Context(), tx, int64(id))
	}
	if reqID.Valid {
		_ = a.recalculateRequestStatus(r.Context(), tx, reqID.Int64)
	}
	if err := tx.Commit(); err != nil {
		http.Error(w, err.Error(), 500)
		return
	}
	back := r.Referer()
	if back == "" {
		back = "/"
	}
	http.Redirect(w, r, back, 303)
}

func validQuestStatus(s string) bool {
	switch s {
	case "open", "accepted", "in_progress", "blocked", "change_requested", "done", "cancelled":
		return true
	}
	return false
}
func (a *App) unlockDependents(ctx context.Context, tx *sql.Tx, doneID int64) error {
	rows, err := tx.QueryContext(ctx, `select q.id from quests q where q.status='waiting' and q.depends_on_quest_id=?`, doneID)
	if err != nil {
		return err
	}
	defer rows.Close()
	for rows.Next() {
		var qid int64
		rows.Scan(&qid)
		var blockers int
		tx.QueryRowContext(ctx, `select count(*) from quest_dependencies d join quests q2 on q2.id=d.depends_on_quest_id where d.quest_id=? and q2.status <> 'done'`, qid).Scan(&blockers)
		if blockers == 0 {
			tx.ExecContext(ctx, `update quests set status='open' where id=?`, qid)
		}
	}
	return nil
}
func (a *App) recalculateRequestStatus(ctx context.Context, tx *sql.Tx, reqID int64) error {
	var total, done, active, blocked, cancelled int
	tx.QueryRowContext(ctx, `select count(*), coalesce(sum(status='done'),0), coalesce(sum(status in ('open','accepted','in_progress','change_requested','waiting')),0), coalesce(sum(status='blocked'),0), coalesce(sum(status='cancelled'),0) from quests where parent_request_id=?`, reqID).Scan(&total, &done, &active, &blocked, &cancelled)
	status := "new"
	fulfilledAt := sql.NullTime{}
	switch {
	case total > 0 && done == total:
		status = "fulfilled"
		fulfilledAt = sql.NullTime{Time: time.Now(), Valid: true}
	case blocked > 0:
		status = "blocked"
	case cancelled == total && total > 0:
		status = "cancelled"
	case active > 0 || done > 0:
		status = "in_progress"
	}
	_, err := tx.ExecContext(ctx, `update trade_requests set status=?, fulfilled_at=? where id=?`, status, fulfilledAt, reqID)
	return err
}

func (a *App) Departments(w http.ResponseWriter, r *http.Request) {
	a.T.ExecuteTemplate(w, "departments.html", map[string]any{"Departments": a.departmentRows(r.Context())})
}
func (a *App) DepartmentQuests(w http.ResponseWriter, r *http.Request) {
	idStr := strings.TrimPrefix(r.URL.Path, "/departments/")
	idStr = strings.TrimSuffix(strings.TrimSuffix(idStr, "/quests"), "/")
	id, _ := strconv.Atoi(idStr)
	var name string
	a.DB.QueryRowContext(r.Context(), `select name from departments where id=?`, id).Scan(&name)
	a.T.ExecuteTemplate(w, "department_quests.html", map[string]any{"DepartmentID": id, "DepartmentName": name, "Quests": a.questRows(r.Context(), id, 200)})
}

func (a *App) requestRows(ctx context.Context, where string, limit int) []Row {
	rows, err := a.DB.QueryContext(ctx, `select tr.id,tr.kind,i.name,tr.quantity,tr.price_limit,tr.status,tr.created_at,coalesce(tr.notes,'') from trade_requests tr join items i on i.id=tr.item_id order by tr.id desc limit ?`, limit)
	if err != nil {
		return nil
	}
	defer rows.Close()
	var out []Row
	for rows.Next() {
		var id, qty int
		var kind, item, status, note string
		var created time.Time
		var price float64
		rows.Scan(&id, &kind, &item, &qty, &price, &status, &created, &note)
		out = append(out, Row{"ID": id, "Kind": kind, "Item": item, "Qty": qty, "Price": price, "Status": status, "Created": created.Format("2006-01-02 15:04"), "Note": note})
	}
	return out
}
func (a *App) questRows(ctx context.Context, deptID int, limit int) []Row {
	q := `select q.id,q.type,q.title,coalesce(d.name,''),q.status,coalesce(q.contractor_note,''),coalesce(q.blocked_reason,''),q.sequence_order,coalesce(tr.id,0),coalesce(i.name,'') from quests q left join departments d on d.id=q.department_id left join trade_requests tr on tr.id=q.parent_request_id left join items i on i.id=tr.item_id`
	args := []any{}
	if deptID > 0 {
		q += ` where q.department_id=?`
		args = append(args, deptID)
	}
	q += ` order by q.status='waiting', q.sequence_order, q.id desc limit ?`
	args = append(args, limit)
	rows, err := a.DB.QueryContext(ctx, q, args...)
	if err != nil {
		return nil
	}
	defer rows.Close()
	var out []Row
	for rows.Next() {
		var id, order, reqID int
		var typ, title, dept, status, note, block, item string
		rows.Scan(&id, &typ, &title, &dept, &status, &note, &block, &order, &reqID, &item)
		out = append(out, Row{"ID": id, "Type": typ, "Title": title, "Dept": dept, "Status": status, "Note": note, "Block": block, "Order": order, "RequestID": reqID, "Item": item})
	}
	return out
}
func (a *App) departmentRows(ctx context.Context) []Row {
	rows, err := a.DB.QueryContext(ctx, `select id,name from departments order by name`)
	if err != nil {
		return nil
	}
	defer rows.Close()
	var out []Row
	for rows.Next() {
		var id int
		var name string
		rows.Scan(&id, &name)
		out = append(out, Row{"ID": id, "Name": name})
	}
	return out
}
func (a *App) itemRows(ctx context.Context) []Row {
	rows, err := a.DB.QueryContext(ctx, `select id,name,category from items order by name`)
	if err != nil {
		return nil
	}
	defer rows.Close()
	var out []Row
	for rows.Next() {
		var id int
		var name, cat string
		rows.Scan(&id, &name, &cat)
		out = append(out, Row{"ID": id, "Name": name, "Category": cat})
	}
	return out
}
func nullInt(s string) sql.NullInt64 {
	i, err := strconv.ParseInt(strings.TrimSpace(s), 10, 64)
	if err != nil || i <= 0 {
		return sql.NullInt64{}
	}
	return sql.NullInt64{Int64: i, Valid: true}
}