vibeproxy/internal/database/repo_logs.go

package database

import (
	"database/sql"

	"github.com/owen/vibeproxy/internal/models"
)

func InsertRequestLog(db *sql.DB, l models.RequestLog) error {
	_, err := db.Exec(`INSERT INTO request_logs (timestamp, proxy_host_id, proxy_host_name, client_ip, method, host,
		path, status, response_size, backend, response_time_ms, user_agent) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)`,
		l.Timestamp.UTC().Format(timeFmt), nullInt64(l.ProxyHostID), l.ProxyHostName, l.ClientIP, l.Method, l.Host,
		l.Path, l.Status, l.ResponseSize, l.Backend, l.ResponseTimeMS, l.UserAgent)
	return err
}

func InsertRequestLogBatch(db *sql.DB, logs []models.RequestLog) error {
	if len(logs) == 0 {
		return nil
	}
	tx, err := db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()
	stmt, err := tx.Prepare(`INSERT INTO request_logs (timestamp, proxy_host_id, proxy_host_name, client_ip, method, host,
		path, status, response_size, backend, response_time_ms, user_agent) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)`)
	if err != nil {
		return err
	}
	defer stmt.Close()
	for _, l := range logs {
		if _, err := stmt.Exec(l.Timestamp.UTC().Format(timeFmt), nullInt64(l.ProxyHostID), l.ProxyHostName, l.ClientIP,
			l.Method, l.Host, l.Path, l.Status, l.ResponseSize, l.Backend, l.ResponseTimeMS, l.UserAgent); err != nil {
			return err
		}
	}
	return tx.Commit()
}

type LogFilter struct {
	ProxyHostID *int64
	Status      int
	Search      string
	Limit       int
	Offset      int
}

func QueryRequestLogs(db *sql.DB, f LogFilter) ([]models.RequestLog, int, error) {
	where := "WHERE 1=1"
	var args []any
	if f.ProxyHostID != nil {
		where += " AND proxy_host_id = ?"
		args = append(args, *f.ProxyHostID)
	}
	if f.Status > 0 {
		where += " AND status = ?"
		args = append(args, f.Status)
	}
	if f.Search != "" {
		where += " AND (host LIKE ? OR path LIKE ? OR client_ip LIKE ?)"
		s := "%" + f.Search + "%"
		args = append(args, s, s, s)
	}

	var total int
	if err := db.QueryRow(`SELECT COUNT(*) FROM request_logs `+where, args...).Scan(&total); err != nil {
		return nil, 0, err
	}

	limit := f.Limit
	if limit <= 0 || limit > 500 {
		limit = 50
	}
	q := `SELECT id, timestamp, proxy_host_id, proxy_host_name, client_ip, method, host, path, status, response_size,
		backend, response_time_ms, user_agent FROM request_logs ` + where + ` ORDER BY id DESC LIMIT ? OFFSET ?`
	args = append(args, limit, f.Offset)
	rows, err := db.Query(q, args...)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()

	var out []models.RequestLog
	for rows.Next() {
		var l models.RequestLog
		var ts string
		var hostID sql.NullInt64
		if err := rows.Scan(&l.ID, &ts, &hostID, &l.ProxyHostName, &l.ClientIP, &l.Method, &l.Host, &l.Path, &l.Status,
			&l.ResponseSize, &l.Backend, &l.ResponseTimeMS, &l.UserAgent); err != nil {
			return nil, 0, err
		}
		l.Timestamp = parseTime(ts)
		if hostID.Valid {
			v := hostID.Int64
			l.ProxyHostID = &v
		}
		out = append(out, l)
	}
	return out, total, rows.Err()
}

func DeleteOldRequestLogs(db *sql.DB, retentionDays int) (int64, error) {
	res, err := db.Exec(`DELETE FROM request_logs WHERE timestamp < strftime('%Y-%m-%dT%H:%M:%fZ', 'now', ?)`, "-"+itoa(retentionDays)+" days")
	if err != nil {
		return 0, err
	}
	return res.RowsAffected()
}

func itoa(i int) string {
	if i < 0 {
		i = 0
	}
	digits := []byte{}
	if i == 0 {
		return "0"
	}
	for i > 0 {
		digits = append([]byte{byte('0' + i%10)}, digits...)
		i /= 10
	}
	return string(digits)
}

// Stats

func CountRequestLogsSince(db *sql.DB, sinceExpr string) (int, error) {
	var n int
	err := db.QueryRow(`SELECT COUNT(*) FROM request_logs WHERE timestamp >= strftime('%Y-%m-%dT%H:%M:%fZ', 'now', ?)`, sinceExpr).Scan(&n)
	return n, err
}

type StatusCount struct {
	Status int
	Count  int
}

func StatusBreakdownSince(db *sql.DB, sinceExpr string) ([]StatusCount, error) {
	rows, err := db.Query(`SELECT status, COUNT(*) FROM request_logs WHERE timestamp >= strftime('%Y-%m-%dT%H:%M:%fZ', 'now', ?) GROUP BY status ORDER BY status`, sinceExpr)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []StatusCount
	for rows.Next() {
		var s StatusCount
		if err := rows.Scan(&s.Status, &s.Count); err != nil {
			return nil, err
		}
		out = append(out, s)
	}
	return out, rows.Err()
}

type HostCount struct {
	Host  string
	Count int
}

func TopHostsSince(db *sql.DB, sinceExpr string, limit int) ([]HostCount, error) {
	rows, err := db.Query(`SELECT host, COUNT(*) c FROM request_logs WHERE timestamp >= strftime('%Y-%m-%dT%H:%M:%fZ', 'now', ?) GROUP BY host ORDER BY c DESC LIMIT ?`, sinceExpr, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []HostCount
	for rows.Next() {
		var h HostCount
		if err := rows.Scan(&h.Host, &h.Count); err != nil {
			return nil, err
		}
		out = append(out, h)
	}
	return out, rows.Err()
}

func TopClientsSince(db *sql.DB, sinceExpr string, limit int) ([]HostCount, error) {
	rows, err := db.Query(`SELECT client_ip, COUNT(*) c FROM request_logs WHERE timestamp >= strftime('%Y-%m-%dT%H:%M:%fZ', 'now', ?) GROUP BY client_ip ORDER BY c DESC LIMIT ?`, sinceExpr, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []HostCount
	for rows.Next() {
		var h HostCount
		if err := rows.Scan(&h.Host, &h.Count); err != nil {
			return nil, err
		}
		out = append(out, h)
	}
	return out, rows.Err()
}

func RecentErrorsSince(db *sql.DB, sinceExpr string, limit int) ([]models.RequestLog, error) {
	rows, err := db.Query(`SELECT id, timestamp, proxy_host_id, proxy_host_name, client_ip, method, host, path, status,
		response_size, backend, response_time_ms, user_agent FROM request_logs
		WHERE status >= 400 AND timestamp >= strftime('%Y-%m-%dT%H:%M:%fZ', 'now', ?) ORDER BY id DESC LIMIT ?`, sinceExpr, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []models.RequestLog
	for rows.Next() {
		var l models.RequestLog
		var ts string
		var hostID sql.NullInt64
		if err := rows.Scan(&l.ID, &ts, &hostID, &l.ProxyHostName, &l.ClientIP, &l.Method, &l.Host, &l.Path, &l.Status,
			&l.ResponseSize, &l.Backend, &l.ResponseTimeMS, &l.UserAgent); err != nil {
			return nil, err
		}
		l.Timestamp = parseTime(ts)
		if hostID.Valid {
			v := hostID.Int64
			l.ProxyHostID = &v
		}
		out = append(out, l)
	}
	return out, rows.Err()
}