db.go

package main

import (
	"database/sql"
	"embed"
	"fmt"
	"log"
	"os"
	"path/filepath"
	"runtime"
	"strconv"
	"strings"
	"sync"
	"time"
	"sort"

	_ "github.com/denisenkom/go-mssqldb"
	"golang.org/x/crypto/bcrypt"
	_ "modernc.org/sqlite"
)

//---------------------------------------------------------------------
// eingebettete Schema-Dateien
//---------------------------------------------------------------------

//go:embed timetrack_init.sql
var embeddedSQLiteSchema string

//go:embed timetrack_init.mssql.sql
var embeddedMSSQLSchema string
var _ = embeddedMSSQLSchema

//go:embed sql/sqlite/*.sql sql/mssql/*.sql
var embeddedSQLFiles embed.FS

//---------------------------------------------------------------------
// globale Konfiguration
//---------------------------------------------------------------------

var (
	dbBackend            string // "sqlite" | "mssql"
	sqlitePath           string
	mssqlServer, mssqlDB string
	mssqlUser, mssqlPass string
	mssqlPort            int
)

// request-bound host mapping for SQLite multi-tenant support
var currentHostByGID sync.Map // gid -> host
var initializedDBs sync.Map   // sqlite dsn/path -> bool

// SetRequestHost binds the current goroutine to a host for DB selection
func SetRequestHost(host string) {
	currentHostByGID.Store(getGID(), host)
}

// ClearRequestHost clears the host binding for the current goroutine
func ClearRequestHost() {
	currentHostByGID.Delete(getGID())
}

// GetRequestHost returns the host bound to the current goroutine via SetRequestHost.
// It is useful in helper/preview functions where the http.Request isn't directly available.
func GetRequestHost() string {
	if v, ok := currentHostByGID.Load(getGID()); ok {
		return fmt.Sprintf("%v", v)
	}
	return ""
}

func getGID() int64 {
	// Hacky but sufficient: parse goroutine id from runtime.Stack
	var buf [64]byte
	n := runtime.Stack(buf[:], false)
	// Stack format: "goroutine 12345 [running]:\n"
	s := strings.Fields(strings.TrimPrefix(string(buf[:n]), "goroutine "))
	if len(s) > 0 {
		if id, err := strconv.ParseInt(s[0], 10, 64); err == nil {
			return id
		}
	}
	return 0
}

func resolveSQLitePath() string {
	// Prefer request-bound host-specific DB path when available
	if v, ok := currentHostByGID.Load(getGID()); ok {
		host := fmt.Sprintf("%v", v)
		// sanitize host for filesystem
		safe := strings.ToLower(host)
		safe = strings.ReplaceAll(safe, "/", "-")
		// ensure tenant dir exists: tenant/<host>
		dir := filepath.Join("tenant", safe)
		_ = os.MkdirAll(dir, 0o755)
		return filepath.Join(dir, "time_tracking.db")
	}
	// fallback to configured path
	return sqlitePath
}

// EnsureSchemaCurrent ensures that for the current DB target (considering
// the request-bound host for SQLite) the schema exists. It runs at most once
// per SQLite file path.
func EnsureSchemaCurrent() {
	if dbBackend != "sqlite" {
		return
	}
	path := resolveSQLitePath()
	if _, done := initializedDBs.Load(path); done {
		return
	}
	// Try to run schema creation idempotently for this path
	log.Printf("[DB] Ensuring schema for SQLite at %s", path)
	execBatches(embeddedSQLiteSchema, ";\n")
	// Execute additional SQL files from sql/sqlite (e.g., view definitions)
	execSQLFilesInDir(filepath.Join("sql", "sqlite"))
	ensureUserPasswordColumn()
	ensureUserRoleColumn()
	ensureUserAutoCheckoutColumn()
	initializedDBs.Store(path, true)
}

// execSQLFilesInDir reads all .sql files in the given directory and executes their contents
// against the current DB. Files are executed in alphabetical order to allow deterministic setup.
func execSQLFilesInDir(dir string) {
	// First try embedded files
	entries, _ := embeddedSQLFiles.ReadDir(dir)
	var files []string
	if len(entries) > 0 {
		for _, e := range entries {
			if !e.IsDir() && strings.HasSuffix(e.Name(), ".sql") {
				files = append(files, filepath.Join(dir, e.Name()))
			}
		}
	} else {
		// Fallback to disk
		files, _ = filepath.Glob(filepath.Join(dir, "*.sql"))
	}
	if len(files) == 0 {
		return
	}
	sort.Strings(files)
	db := getDB()
	defer db.Close()
	for _, f := range files {
		var data []byte
		var err error
		// If embedded FS has the file, read from it
		rel := strings.TrimPrefix(f, "sql/")
		if b, err2 := embeddedSQLFiles.ReadFile(filepath.Join("sql", rel)); err2 == nil {
			data = b
		} else {
			data, err = os.ReadFile(f)
			if err != nil {
				log.Printf("execSQLFilesInDir: read %s failed: %v", f, err)
				continue
			}
		}
		s := strings.TrimSpace(string(data))
		if s == "" {
			continue
		}
		if _, err := db.Exec(s); err != nil {
			// log but continue with other files
			log.Printf("execSQLFilesInDir: exec %s failed: %v", f, err)
		}
	}
}

// Hilfsfunktionen
func getenv(key, def string) string {
	if v := os.Getenv(key); v != "" {
		return v
	}
	return def
}
func atoiDefault(s string, def int) int {
	if i, err := strconv.Atoi(s); err == nil {
		return i
	}
	return def
}

//---------------------------------------------------------------------
// init: Konfiguration einlesen + ggf. Schema anlegen
//---------------------------------------------------------------------

func init() {
	// 1. Backend bestimmen
	dbBackend = strings.ToLower(getenv("DB_BACKEND", "mssql"))

	// 2. Backend-spezifische Defaults
	switch dbBackend {
	case "mssql":
		mssqlServer = getenv("MSSQL_SERVER", "sql-cluster-05")
		mssqlDB = getenv("MSSQL_DATABASE", "wtm")
		mssqlUser = getenv("MSSQL_USER", `johndoe`)
		mssqlPass = getenv("MSSQL_PASSWORD", "secret")
		mssqlPort = atoiDefault(getenv("MSSQL_PORT", "1433"), 1433)
		log.Printf("[DB] Backend=mssql server=%s db=%s user=%s port=%d", mssqlServer, mssqlDB, mssqlUser, mssqlPort)
	default: // sqlite
		sqlitePath = getenv("SQLITE_PATH", "time_tracking.db")
		log.Printf("[DB] Backend=sqlite defaultPath=%s (will switch per-host if set)", sqlitePath)
	}

	// 3. Tabellen / Views anlegen (nur wenn sinnvoll)
	createDatabaseAndTables()
}

//---------------------------------------------------------------------
// DB-Verbindung
//---------------------------------------------------------------------

func getDB() *sql.DB {
	var (
		driver string
		dsn    string
	)

	switch dbBackend {
	case "mssql":
		driver = "sqlserver"
		dsn = fmt.Sprintf(
			"server=%s;database=%s;user id=%s;password=%s;port=%d;encrypt=disable",
			mssqlServer, mssqlDB, mssqlUser, mssqlPass, mssqlPort,
		)
	default: // sqlite
		driver = "sqlite"
		dsn = resolveSQLitePath()
		log.Printf("[DB] Opening SQLite dsn=%s", dsn)
	}

	db, err := sql.Open(driver, dsn)
	if err != nil {
		// don't crash the server; return a dummy DB that will fail later
		log.Printf("[DB] Open failed driver=%s dsn=%s err=%v", driver, dsn, err)
		return db
	}
	return db
}

//---------------------------------------------------------------------
// Hilfskürzel: richtiger Tabellen/Vie­w-Name abhängig vom Backend
//---------------------------------------------------------------------

func tbl(name string) string {
	if dbBackend == "mssql" {
		return "wtm.wtm." + name // alle Tabellen liegen dort
	}
	return name
}

// loadSQL loads a SQL file from sql/<backend>/<name>.sql and replaces {{placeholders}} with actual table/view names.
func loadSQL(name string) string {
	backend := dbBackend
	if backend != "mssql" {
		backend = "sqlite"
	}
	path := filepath.Join("sql", backend, name+".sql")
	// Try embedded FS first
	var s string
	if b, err := embeddedSQLFiles.ReadFile(path); err == nil {
		s = string(b)
	} else {
		data, err := os.ReadFile(path)
		if err != nil {
			log.Printf("loadSQL failed for %s: %v", path, err)
			return ""
		}
		s = string(data)
	}
	// Replace placeholders like {{entries}} with concrete names via tbl()
	replacer := strings.NewReplacer(
		"{{entries}}", tbl("entries"),
		"{{users}}", tbl("users"),
		"{{departments}}", tbl("departments"),
		"{{type}}", tbl("type"),
		"{{work_hours}}", tbl("work_hours"),
	)
	return replacer.Replace(s)
}

//---------------------------------------------------------------------
// Schema anlegen
//---------------------------------------------------------------------

func createDatabaseAndTables() {
	switch dbBackend {
	case "sqlite":
		execBatches(embeddedSQLiteSchema, ";\n")
		ensureUserPasswordColumn()
		ensureUserRoleColumn()
		ensureUserAutoCheckoutColumn()
	case "mssql":
		if os.Getenv("DB_AUTO_MIGRATE") == "1" {
			//execBatches(embeddedMSSQLSchema, "\nGO")
		}
		ensureUserPasswordColumn()
		ensureUserRoleColumn()
		ensureUserAutoCheckoutColumn()
	}
}

// ensureUserPasswordColumn adds the password column if it does not exist
func ensureUserPasswordColumn() {
	db := getDB()
	defer db.Close()
	switch dbBackend {
	case "sqlite":
		rows, err := db.Query("PRAGMA table_info(users)")
		if err != nil {
			log.Printf("PRAGMA table_info failed: %v", err)
			return
		}
		defer rows.Close()
		hasPwd := false
		for rows.Next() {
			var cid int
			var name, ctype string
			var notnull, pk int
			var dflt sql.NullString
			if err := rows.Scan(&cid, &name, &ctype, &notnull, &dflt, &pk); err == nil {
				if strings.EqualFold(name, "password") {
					hasPwd = true
					break
				}
			}
		}
		if !hasPwd {
			if _, err := db.Exec("ALTER TABLE users ADD COLUMN password TEXT"); err != nil {
				log.Printf("add users.password failed: %v", err)
			}
		}
	case "mssql":
		var exists int
		err := db.QueryRow("SELECT 1 FROM sys.columns WHERE Name = 'password' AND Object_ID = Object_ID('dbo.users')").Scan(&exists)
		if err == sql.ErrNoRows {
			if _, err2 := db.Exec("ALTER TABLE dbo.users ADD password NVARCHAR(255) NULL"); err2 != nil {
				log.Printf("add users.password failed: %v", err2)
			}
		}
	}
}

// ensureUserRoleColumn adds the role column if missing
func ensureUserRoleColumn() {
	db := getDB()
	defer db.Close()
	switch dbBackend {
	case "sqlite":
		rows, err := db.Query("PRAGMA table_info(users)")
		if err != nil {
			return
		}
		defer rows.Close()
		has := false
		for rows.Next() {
			var cid int
			var name, ctype string
			var notnull, pk int
			var dflt sql.NullString
			if err := rows.Scan(&cid, &name, &ctype, &notnull, &dflt, &pk); err == nil {
				if strings.EqualFold(name, "role") {
					has = true
					break
				}
			}
		}
		if !has {
			_, _ = db.Exec("ALTER TABLE users ADD COLUMN role TEXT DEFAULT 'user'")
		}
	case "mssql":
		var exists int
		err := db.QueryRow("SELECT 1 FROM sys.columns WHERE Name = 'role' AND Object_ID = Object_ID('dbo.users')").Scan(&exists)
		if err == sql.ErrNoRows {
			_, _ = db.Exec("ALTER TABLE dbo.users ADD role NVARCHAR(50) NULL DEFAULT 'user'")
		}
	}
}

// ensureUserAutoCheckoutColumn adds the auto_checkout_midnight column if missing
func ensureUserAutoCheckoutColumn() {
	db := getDB()
	defer db.Close()
	switch dbBackend {
	case "sqlite":
		rows, err := db.Query("PRAGMA table_info(users)")
		if err != nil {
			return
		}
		defer rows.Close()
		has := false
		for rows.Next() {
			var cid int
			var name, ctype string
			var notnull, pk int
			var dflt sql.NullString
			if err := rows.Scan(&cid, &name, &ctype, &notnull, &dflt, &pk); err == nil {
				if strings.EqualFold(name, "auto_checkout_midnight") {
					has = true
					break
				}
			}
		}
		if !has {
			_, _ = db.Exec("ALTER TABLE users ADD COLUMN auto_checkout_midnight INTEGER DEFAULT 0")
		}
	case "mssql":
		var exists int
		err := db.QueryRow("SELECT 1 FROM sys.columns WHERE Name = 'auto_checkout_midnight' AND Object_ID = Object_ID('dbo.users')").Scan(&exists)
		if err == sql.ErrNoRows {
			_, _ = db.Exec("ALTER TABLE dbo.users ADD auto_checkout_midnight INT NOT NULL DEFAULT 0")
		}
	}
}

func execBatches(script, sep string) {
	db := getDB()
	defer db.Close()

	for _, stmt := range strings.Split(script, sep) {
		stmt = strings.TrimSpace(stmt)
		if stmt == "" {
			continue
		}
		if _, err := db.Exec(stmt); err != nil {
			log.Printf("[DB] Schema exec failed: %v; stmt: %s", err, stmt)
		}
	}
}

//---------------------------------------------------------------------
// Daten-Structs – identisch zu vorher, damit main.go unverändert bleibt
//---------------------------------------------------------------------

type User struct {
	ID                   int
	Stampkey             string
	Name                 string
	Email                string
	Password             string
	Role                 string
	Position             string
	DepartmentID         int
	AutoCheckoutMidnight int
}

type Activity struct {
	ID      int
	Status  string
	Work    int
	Comment string
}

type Department struct {
	ID   int
	Name string
}

//---------------------------------------------------------------------
// CRUD-Funktionen
//---------------------------------------------------------------------

// ----------- SELECT-Listen ------------------------------------------

func getUsers() []User {
	db := getDB()
	defer db.Close()

	query := loadSQL("get_users")
	rows, err := db.Query(query)
	if err != nil {
		log.Printf("getUsers query failed: %v", err)
		return nil
	}
	defer rows.Close()

	var list []User
	for rows.Next() {
		var u User
		if err := rows.Scan(&u.ID, &u.Name, &u.Email, &u.Password, &u.Role, &u.Position, &u.DepartmentID, &u.Stampkey, &u.AutoCheckoutMidnight); err != nil {
			log.Printf("getUsers scan failed: %v", err)
			continue
		}
		list = append(list, u)
	}
	return list
}

func getActivities() []Activity {
	db := getDB()
	defer db.Close()

	query := loadSQL("get_activities")
	rows, err := db.Query(query)
	if err != nil {
		log.Printf("getActivities query failed: %v", err)
		return nil
	}
	defer rows.Close()

	var list []Activity
	for rows.Next() {
		var a Activity
		if err := rows.Scan(&a.ID, &a.Status, &a.Work, &a.Comment); err != nil {
			log.Printf("getActivities scan failed: %v", err)
			continue
		}
		list = append(list, a)
	}
	return list
}

func getDepartments() []Department {
	db := getDB()
	defer db.Close()

	query := loadSQL("get_departments")
	rows, err := db.Query(query)
	if err != nil {
		log.Printf("getDepartments query failed: %v", err)
		return nil
	}
	defer rows.Close()

	var list []Department
	for rows.Next() {
		var d Department
		if err := rows.Scan(&d.ID, &d.Name); err != nil {
			log.Printf("getDepartments scan failed: %v", err)
			continue
		}
		list = append(list, d)
	}
	return list
}

func getEntries() []Entry {
	db := getDB()
	defer db.Close()

	query := loadSQL("get_entries_simple")
	rows, err := db.Query(query)
	if err != nil {
		log.Printf("getEntries query failed: %v", err)
		return nil
	}
	defer rows.Close()

	var list []Entry
	for rows.Next() {
		var e Entry
		var dateAny interface{}
		if err := rows.Scan(&e.ID, &e.UserID, &e.ActivityID, &dateAny); err != nil {
			log.Printf("getEntries scan failed: %v", err)
			continue
		}
		switch v := dateAny.(type) {
		case int64:
			e.Date = time.Unix(v, 0).Format("2006-01-02 15:04:05")
		case []byte:
			// if backend returns bytes, try to parse int
			s := string(v)
			if n, err := strconv.ParseInt(s, 10, 64); err == nil {
				e.Date = time.Unix(n, 0).Format("2006-01-02 15:04:05")
			} else {
				e.Date = s
			}
		case string:
			e.Date = v
		default:
			e.Date = fmt.Sprintf("%v", v)
		}
		list = append(list, e)
	}
	return list
}

// ----------- SELECT-Einzelne ----------------------------------------

func getUser(id string) User {
	db := getDB()
	defer db.Close()

	query := fmt.Sprintf("SELECT id, name, stampkey, email, COALESCE(password,''), COALESCE(role,'user'), position, department_id, COALESCE(auto_checkout_midnight,0) FROM %s WHERE id=@id", tbl("users"))
	var u User
	if err := db.QueryRow(query, sql.Named("id", id)).
		Scan(&u.ID, &u.Name, &u.Stampkey, &u.Email, &u.Password, &u.Role, &u.Position, &u.DepartmentID, &u.AutoCheckoutMidnight); err != nil {
		log.Printf("getUser failed: %v", err)
		return User{}
	}
	return u
}

func getAllUsers() []User {
	db := getDB()
	defer db.Close()

	query := fmt.Sprintf("SELECT id, name, stampkey, email, COALESCE(password,''), COALESCE(role,'user'), position, department_id, COALESCE(auto_checkout_midnight,0) FROM %s", tbl("users"))
	rows, err := db.Query(query)
	if err != nil {
		log.Printf("getAllUsers query failed: %v", err)
		return nil
	}
	defer rows.Close()

	var users []User
	for rows.Next() {
		var u User
		if err := rows.Scan(&u.ID, &u.Name, &u.Stampkey, &u.Email, &u.Password, &u.Role, &u.Position, &u.DepartmentID, &u.AutoCheckoutMidnight); err != nil {
			log.Printf("getAllUsers scan failed: %v", err)
			continue
		}
		users = append(users, u)
	}
	return users
}

func getAllActivities() []Activity {
	db := getDB()
	defer db.Close()

	query := fmt.Sprintf("SELECT id, status, work, comment FROM %s", tbl("type"))
	rows, err := db.Query(query)
	if err != nil {
		log.Printf("getAllActivities query failed: %v", err)
		return nil
	}
	defer rows.Close()

	var activities []Activity
	for rows.Next() {
		var a Activity
		if err := rows.Scan(&a.ID, &a.Status, &a.Work, &a.Comment); err != nil {
			log.Printf("getAllActivities scan failed: %v", err)
			continue
		}
		activities = append(activities, a)
	}
	return activities
}

func getActivity(id string) Activity {
	db := getDB()
	defer db.Close()

	query := fmt.Sprintf("SELECT id, status, work, comment FROM %s WHERE id=@id", tbl("type"))
	var a Activity
	if err := db.QueryRow(query, sql.Named("id", id)).
		Scan(&a.ID, &a.Status, &a.Work, &a.Comment); err != nil {
		log.Printf("getActivity failed: %v", err)
		return Activity{}
	}
	return a
}

func getDepartment(id string) Department {
	db := getDB()
	defer db.Close()

	query := fmt.Sprintf("SELECT id, name FROM %s WHERE id=@id", tbl("departments"))
	var d Department
	if err := db.QueryRow(query, sql.Named("id", id)).
		Scan(&d.ID, &d.Name); err != nil {
		log.Printf("getDepartment failed: %v", err)
		return Department{}
	}
	return d
}

func getUserIDFromStampKey(stampKey string) string {
	db := getDB()
	defer db.Close()

	query := fmt.Sprintf("SELECT id FROM %s WHERE stampkey=@sk", tbl("users"))
	var id string
	if err := db.QueryRow(query, sql.Named("sk", stampKey)).Scan(&id); err != nil {
		// kein fatal – kann vorkommen, wenn Karte unbekannt
		return ""
	}
	return id
}

// ----------- INSERT --------------------------------------------------

func createUniqueStampKey() int {
	db := getDB()
	defer db.Close()

	// Generiere einen eindeutigen Stampkey (hier einfach eine Zufallszahl)
	// In der Praxis sollte dies robuster sein, z.B. durch UUIDs oder andere Mechanismen
	for {
		//stampKey := time.Now().UnixNano() + int64(os.Getpid())
		// stampkey sollte eindeutig sein und zwischen 100000 und 999999999999 liegen
		stampKey := time.Now().UnixNano()%900000000000 + 100000000000 // 12-stellig

		// Überprüfen, ob der Stampkey bereits existiert
		query := fmt.Sprintf("SELECT COUNT(*) FROM %s WHERE stampkey=@sk", tbl("users"))
		var count int
		if err := db.QueryRow(query, sql.Named("sk", stampKey)).Scan(&count); err != nil {
			log.Printf("createUniqueStampKey check failed: %v", err)
			continue
		}
		if count == 0 {
			return int(stampKey)
		}
	}
}

func createUser(name, stampkey, email, password, role, position, departmentID string) {
	db := getDB()
	defer db.Close()

	// Überprüfen, ob der Stampkey bereits existiert
	if stampkey == "" {
		// Generiere einen neuen eindeutigen Stampkey
		stampkey = strconv.Itoa(createUniqueStampKey())
	} else {
		// Überprüfen, ob der Stampkey bereits existiert
		query := fmt.Sprintf("SELECT COUNT(*) FROM %s WHERE stampkey=@sk", tbl("users"))
		var count int
		if err := db.QueryRow(query, sql.Named("sk", stampkey)).Scan(&count); err != nil {
			log.Printf("createUser check sk failed: %v", err)
			count = 0
		}

		if count > 0 {
			log.Printf("Stampkey %s already exists. Please use a different one.", stampkey)
			return
		}
	}

	dept, _ := strconv.Atoi(departmentID)
	// hash password if provided
	var hashed string
	if password != "" {
		b, err := bcrypt.GenerateFromPassword([]byte(password), bcrypt.DefaultCost)
		if err != nil {
			log.Printf("hash password failed: %v", err)
		} else {
			hashed = string(b)
		}
	}
	query := fmt.Sprintf(`INSERT INTO %s (name, stampkey, email, password, role, position, department_id)
                           VALUES (@name,@sk,@mail,@pwd,@role,@pos,@dept)`, tbl("users"))
	_, err := db.Exec(query,
		sql.Named("name", name),
		sql.Named("sk", stampkey),
		sql.Named("mail", email),
		sql.Named("pwd", hashed),
		sql.Named("role", role),
		sql.Named("pos", position),
		sql.Named("dept", dept),
	)
	if err != nil {
		log.Printf("createUser insert failed: %v", err)
	}
}

// setUserAutoCheckout updates the per-user auto checkout flag (0/1)
func setUserAutoCheckout(id string, enabled bool) {
	db := getDB()
	defer db.Close()
	val := 0
	if enabled {
		val = 1
	}
	query := fmt.Sprintf("UPDATE %s SET auto_checkout_midnight=@auto WHERE id=@id", tbl("users"))
	if _, err := db.Exec(query, sql.Named("auto", val), sql.Named("id", id)); err != nil {
		log.Printf("update auto_checkout_midnight failed: %v", err)
	}
}

func createActivity(status, work, comment string) {
	db := getDB()
	defer db.Close()

	workInt, _ := strconv.Atoi(work)
	query := fmt.Sprintf(`INSERT INTO %s (status, work, comment)
	                       VALUES (@status,@work,@comment)`, tbl("type"))
	_, err := db.Exec(query,
		sql.Named("status", status),
		sql.Named("work", workInt),
		sql.Named("comment", comment),
	)
	if err != nil {
		log.Printf("updateDepartment failed: %v", err)
	}
}

func createDepartment(name string) {
	db := getDB()
	defer db.Close()

	query := fmt.Sprintf("INSERT INTO %s (name) VALUES (@name)", tbl("departments"))
	if _, err := db.Exec(query, sql.Named("name", name)); err != nil {
		log.Printf("createDepartment failed: %v", err)
	}
}

// createEntry creates a new time entry for a user
func createEntry(userID, activityID string, entrydate time.Time) {
	db := getDB()
	defer db.Close()

	// Ensure midnight auto-checkout if enabled and last working entry is on a previous day
	ensureMidnightAutoCheckoutWithDB(db, atoiDefault(userID, 0), entrydate)

	// store as unix epoch seconds (INTEGER)
	epoch := entrydate.Unix()
	query := fmt.Sprintf(`INSERT INTO %s (user_id, type_id, date)
							VALUES (@uid, @aid, @date)`, tbl("entries"))
	_, err := db.Exec(query,
		sql.Named("uid", userID),
		sql.Named("aid", activityID),
		sql.Named("date", epoch),
	)
	if err != nil {
		log.Printf("createEntry failed: %v", err)
	}
}

// setUserPasswordByID updates the password hash for the given user ID
func setUserPasswordByID(userID int, newPassword string) error {
	db := getDB()
	defer db.Close()
	if strings.TrimSpace(newPassword) == "" {
		return fmt.Errorf("password empty")
	}
	b, err := bcrypt.GenerateFromPassword([]byte(newPassword), bcrypt.DefaultCost)
	if err != nil {
		return fmt.Errorf("hash password failed: %w", err)
	}
	hashed := string(b)
	query := fmt.Sprintf("UPDATE %s SET password=@pwd WHERE id=@id", tbl("users"))
	if _, err := db.Exec(query, sql.Named("pwd", hashed), sql.Named("id", userID)); err != nil {
		return fmt.Errorf("update password failed: %w", err)
	}
	return nil
}

// ensureMidnightAutoCheckoutWithDB inserts a non-work entry at 23:59:59 of the day of the
// user's last working entry if auto checkout is enabled and the last entry is from a previous day.
func ensureMidnightAutoCheckoutWithDB(db *sql.DB, userID int, now time.Time) {
	if userID <= 0 {
		return
	}
	var auto int
	if err := db.QueryRow("SELECT COALESCE(auto_checkout_midnight,0) FROM "+tbl("users")+" WHERE id=?", userID).Scan(&auto); err != nil {
		return
	}
	if auto == 0 {
		return
	}
	// get last entry and whether it was a working type
	var lastEpoch int64
	var work int
	q := fmt.Sprintf("SELECT date, (SELECT work FROM %s t WHERE t.id = e.type_id) FROM %s e WHERE user_id=? ORDER BY date DESC LIMIT 1", tbl("type"), tbl("entries"))
	if err := db.QueryRow(q, userID).Scan(&lastEpoch, &work); err != nil {
		return
	}
	last := time.Unix(lastEpoch, 0).In(now.Location())
	if work != 1 {
		return
	}
	ly, lm, ld := last.Date()
	ny, nm, nd := now.Date()
	if ly == ny && lm == nm && ld == nd {
		return
	}
	midnight := time.Date(ly, lm, ld, 23, 59, 59, 0, last.Location())
	// find non-work activity (prefer Break)
	var nonWorkID int
	if err := db.QueryRow("SELECT id FROM " + tbl("type") + " WHERE work=0 ORDER BY CASE WHEN status='Break' THEN 0 ELSE 1 END, id LIMIT 1").Scan(&nonWorkID); err != nil {
		return
	}
	_, _ = db.Exec("INSERT INTO "+tbl("entries")+"(user_id, type_id, date) VALUES (?,?,?)", userID, nonWorkID, midnight.Unix())
}

// getUserEntriesDetailed returns detailed entries for a user within an optional date range [from, to]
func getUserEntriesDetailed(userID int, from, to string) []EntryDetail {
	db := getDB()
	defer db.Close()
	where := "WHERE e.user_id = @uid"
	if strings.TrimSpace(from) != "" {
		where += " AND DATE(DATETIME(e.date, 'unixepoch')) >= DATE(@from)"
	}
	if strings.TrimSpace(to) != "" {
		where += " AND DATE(DATETIME(e.date, 'unixepoch')) <= DATE(@to)"
	}
	// load SQL from file and append WHERE clause
	baseSQL := loadSQL("get_entries_with_details")
	// Replace placeholder for where/limit
	if strings.Contains(baseSQL, "--WHERE_PLACEHOLDER--") {
		baseSQL = strings.ReplaceAll(baseSQL, "--WHERE_PLACEHOLDER--", where)
	} else {
		baseSQL = baseSQL + "\n" + where
	}
	query := baseSQL
	args := []interface{}{sql.Named("uid", userID)}
	if strings.TrimSpace(from) != "" {
		args = append(args, sql.Named("from", from))
	}
	if strings.TrimSpace(to) != "" {
		args = append(args, sql.Named("to", to))
	}
	rows, err := db.Query(query, args...)
	if err != nil {
		log.Printf("Query user entries failed: %v", err)
		return nil
	}
	defer rows.Close()
	var list []EntryDetail
	for rows.Next() {
		var e EntryDetail
		if err := rows.Scan(&e.ID, &e.UserID, &e.UserName, &e.Department, &e.ActivityID, &e.Activity, &e.Date, &e.Start, &e.End, &e.Duration, &e.Comment); err != nil {
			log.Printf("Scan user entry failed: %v", err)
			continue
		}
		list = append(list, e)
	}
	return list
}

// ----------- UPDATE --------------------------------------------------

func updateUser(id, name, stampkey, email, password, role, position, departmentID string) {
	db := getDB()
	defer db.Close()

	dept, _ := strconv.Atoi(departmentID)
	if password != "" {
		b, err := bcrypt.GenerateFromPassword([]byte(password), bcrypt.DefaultCost)
		var hashed string
		if err != nil {
			log.Printf("hash password failed: %v", err)
		} else {
			hashed = string(b)
		}
		query := fmt.Sprintf(`UPDATE %s
			  SET name=@name, stampkey=@sk, email=@mail, password=@pwd, role=@role, position=@pos, department_id=@dept
						  WHERE id=@id`, tbl("users"))
		_, err = db.Exec(query,
			sql.Named("name", name),
			sql.Named("sk", stampkey),
			sql.Named("mail", email),
			sql.Named("pwd", hashed),
			sql.Named("role", role),
			sql.Named("pos", position),
			sql.Named("dept", dept),
			sql.Named("id", id),
		)
		if err != nil {
			log.Printf("updateUser with password failed: %v", err)
		}
		return
	}
	query := fmt.Sprintf(`UPDATE %s
						  SET name=@name, stampkey=@sk, email=@mail, role=@role, position=@pos, department_id=@dept
						  WHERE id=@id`, tbl("users"))
	_, err := db.Exec(query,
		sql.Named("name", name),
		sql.Named("sk", stampkey),
		sql.Named("mail", email),
		sql.Named("role", role),
		sql.Named("pos", position),
		sql.Named("dept", dept),
		sql.Named("id", id),
	)
	if err != nil {
		log.Printf("updateUser failed: %v", err)
	}
}

// Lookup user by email
func getUserByEmail(email string) (User, bool) {
	db := getDB()
	defer db.Close()
	query := fmt.Sprintf("SELECT id, name, email, COALESCE(password,''), COALESCE(role,'user'), stampkey, position, COALESCE(department_id,0), COALESCE(auto_checkout_midnight,0) FROM %s WHERE email=@mail", tbl("users"))
	var u User
	if err := db.QueryRow(query, sql.Named("mail", email)).Scan(&u.ID, &u.Name, &u.Email, &u.Password, &u.Role, &u.Stampkey, &u.Position, &u.DepartmentID, &u.AutoCheckoutMidnight); err != nil {
		return User{}, false
	}
	return u, true
}

// Lookup user by name
func getUserByName(name string) (User, bool) {
	db := getDB()
	defer db.Close()
	query := fmt.Sprintf("SELECT id, name, stampkey, email, COALESCE(password,''), COALESCE(role,'user'), position, COALESCE(department_id,0), COALESCE(auto_checkout_midnight,0) FROM %s WHERE name=@name", tbl("users"))
	var u User
	if err := db.QueryRow(query, sql.Named("name", name)).Scan(&u.ID, &u.Name, &u.Stampkey, &u.Email, &u.Password, &u.Role, &u.Position, &u.DepartmentID, &u.AutoCheckoutMidnight); err != nil {
		return User{}, false
	}
	return u, true
}

// Return current status and timestamp for a user, if any
func getCurrentStatusForUserID(userID int) (status string, at time.Time, ok bool) {
	db := getDB()
	defer db.Close()
	row := db.QueryRow(fmt.Sprintf("SELECT status, date FROM %s WHERE user_id=@id", tbl("current_status")), sql.Named("id", userID))
	var s string
	var raw interface{}
	if err := row.Scan(&s, &raw); err != nil {
		return "", time.Time{}, false
	}
	host := GetRequestHost()
	loc := getTenantLocation(host)
	// normalize raw date to string for parser
	var tstr string
	switch v := raw.(type) {
	case nil:
		tstr = ""
	case string:
		tstr = v
	case []byte:
		tstr = string(v)
	case time.Time:
		tstr = v.Format(time.RFC3339)
	default:
		tstr = fmt.Sprint(v)
	}
	return s, parseDBTimeInLoc(tstr, loc), true
}

// Work hours filtered for a single user (by user name as in view)
func getWorkHoursDataForUser(userName string) []WorkHoursData {
	db := getDB()
	defer db.Close()
	var rows *sql.Rows
	var err error
	if dbBackend == "sqlite" {
		base := loadSQL("get_work_hours_direct")
		// add WHERE clause for the user
		query := base + "\nWHERE user_name = @u\nORDER BY work_date DESC"
		rows, err = db.Query(query, sql.Named("u", userName))
	} else {
		rows, err = db.Query(
			fmt.Sprintf(
				"SELECT user_name, work_date, work_hours FROM %s WHERE user_name=@u ORDER BY work_date DESC",
				tbl("work_hours"),
			),
			sql.Named("u", userName),
		)
	}
	if err != nil {
		log.Printf("Query work_hours (user) failed: %v", err)
		return nil
	}
	defer rows.Close()
	var list []WorkHoursData
	for rows.Next() {
		var w WorkHoursData
		if err := rows.Scan(&w.UserName, &w.WorkDate, &w.WorkHours); err != nil {
			log.Printf("getWorkHoursDataForUser scan failed: %v", err)
			continue
		}
		list = append(list, w)
	}
	return list
}

func updateActivity(id, status, work, comment string) {
	db := getDB()
	defer db.Close()

	workInt, _ := strconv.Atoi(work)
	query := fmt.Sprintf(`UPDATE %s
	                      SET status=@status, work=@work, comment=@comment
	                      WHERE id=@id`, tbl("type"))
	_, err := db.Exec(query,
		sql.Named("status", status),
		sql.Named("work", workInt),
		sql.Named("comment", comment),
		sql.Named("id", id),
	)
	if err != nil {
		log.Printf("updateActivity failed: %v", err)
	}
}

// Additional CRUD functions for editing
func updateDepartment(id, name string) {
	db := getDB()
	defer db.Close()

	query := fmt.Sprintf(`UPDATE %s SET name=@name WHERE id=@id`, tbl("departments"))
	_, err := db.Exec(query,
		sql.Named("name", name),
		sql.Named("id", id),
	)
	if err != nil {
		log.Printf("updateEntry failed: %v", err)
	}
}

func updateEntry(id, userID, activityID, date, comment string) {
	db := getDB()
	defer db.Close()

	// Parse incoming date to INTEGER epoch seconds (SQLite) using tenant time zone
	ds := strings.TrimSpace(date)
	var epochSec int64
	if ds == "" {
		// Keep current behavior (form requires date, so this is unlikely); default to now in tenant TZ
		host := GetRequestHost()
		loc := getTenantLocation(host)
		epochSec = time.Now().In(loc).Unix()
	} else {
		// Numeric epoch string
		if num, err := strconv.ParseInt(ds, 10, 64); err == nil {
			epochSec = num
		} else {
			host := GetRequestHost()
			loc := getTenantLocation(host)
			parsed := false
			// RFC3339 with offset
			if t, err := time.Parse(time.RFC3339, ds); err == nil {
				epochSec = t.Unix()
				parsed = true
			}
			if !parsed {
				// Local formats without TZ
				if t, err := time.ParseInLocation("2006-01-02T15:04:05", ds, loc); err == nil {
					epochSec = t.Unix()
					parsed = true
				}
			}
			if !parsed {
				if t, err := time.ParseInLocation("2006-01-02T15:04", ds, loc); err == nil {
					epochSec = t.Unix()
					parsed = true
				}
			}
			if !parsed {
				if t, err := time.ParseInLocation("2006-01-02 15:04:05", ds, loc); err == nil {
					epochSec = t.Unix()
					parsed = true
				}
			}
			if !parsed {
				if t, err := time.ParseInLocation("2006-01-02", ds, loc); err == nil {
					epochSec = t.Unix()
					parsed = true
				}
			}
			if !parsed {
				// Last resort: now
				epochSec = time.Now().In(loc).Unix()
			}
		}
	}
	query := fmt.Sprintf(`UPDATE %s
						  SET user_id=@uid, type_id=@aid, date=@date, comment=@comment
	                      WHERE id=@id`, tbl("entries"))
	_, err := db.Exec(query,
		sql.Named("uid", userID),
		sql.Named("aid", activityID),
		sql.Named("date", epochSec),
		sql.Named("comment", comment),
		sql.Named("id", id),
	)
	if err != nil {
		log.Printf("updateEntry failed: %v", err)
	}
}

func getEntry(id string) EntryDetail {
	db := getDB()
	defer db.Close()

	// Return human-readable timestamps; compute duration in Go to be tenant-TZ aware
	query := loadSQL("get_entry")

	var e EntryDetail
	var end sql.NullString
	if err := db.QueryRow(query, sql.Named("id", id)).
		Scan(&e.ID, &e.UserID, &e.UserName, &e.Department, &e.ActivityID, &e.Activity, &e.Date, &e.Start, &end, &e.Comment); err != nil {
		log.Printf("Get entry failed: %v", err)
		return EntryDetail{}
	}
	// Compute duration in tenant TZ
	host := GetRequestHost()
	tenantLoc := getTenantLocation(host)
	nowTenant := time.Now().In(tenantLoc)
	startTs := parseDBTimeInLoc(e.Start, tenantLoc)
	var endTs time.Time
	if end.Valid && strings.TrimSpace(end.String) != "" {
		endTs = parseDBTimeInLoc(end.String, tenantLoc)
		e.End = end.String
	} else {
		// open-ended; cap at EOD or now
		dayEnd := time.Date(startTs.Year(), startTs.Month(), startTs.Day(), 23, 59, 59, 0, tenantLoc)
		endTs = dayEnd
		if nowTenant.Before(dayEnd) {
			endTs = nowTenant
		}
		e.End = ""
	}
	dur := endTs.Sub(startTs).Hours()
	if dur < 0 {
		dur = 0
	}
	dur = applyLunchSubtraction(startTs, endTs, dur)
	e.Duration = dur
	return e
}

// applyLunchSubtraction subtracts tenant-configured lunch minutes when the
// interval intersects the tenant's lunch window. The function keeps the
// same signature (start/end as time.Time) so existing call sites remain valid.
func applyLunchSubtraction(startTs, endTs time.Time, hours float64) float64 {
	// Load tenant config for current request/goroutine (fallbacks inside loader)
	host := GetRequestHost()
	cfg := loadTenantConfig(host)
	lunchStart := strings.TrimSpace(cfg.LunchStart)
	lunchEnd := strings.TrimSpace(cfg.LunchEnd)
	lunchMinutes := cfg.LunchMinutes
	if lunchMinutes <= 0 || lunchStart == "" || lunchEnd == "" {
		return hours
	}

	tenantLoc := startTs.Location()

	// helper: parse HH:MM into a time on the given reference day
	parseHM := func(hm string, ref time.Time) (time.Time, error) {
		parts := strings.Split(hm, ":")
		if len(parts) != 2 {
			return time.Time{}, fmt.Errorf("invalid lunch time: %s", hm)
		}
		hh, err := strconv.Atoi(parts[0])
		if err != nil {
			return time.Time{}, err
		}
		mm, err := strconv.Atoi(parts[1])
		if err != nil {
			return time.Time{}, err
		}
		return time.Date(ref.Year(), ref.Month(), ref.Day(), hh, mm, 0, 0, tenantLoc), nil
	}

	// If start and end are on same day, check that day's lunch window
	if startTs.Year() == endTs.Year() && startTs.YearDay() == endTs.YearDay() {
		ls, err1 := parseHM(lunchStart, startTs)
		le, err2 := parseHM(lunchEnd, startTs)
		if err1 != nil || err2 != nil {
			return hours
		}
		if startTs.Before(le) && endTs.After(ls) {
			sub := float64(lunchMinutes) / 60.0
			hours -= sub
			if hours < 0 {
				hours = 0
			}
		}
		return hours
	}

	// Multi-day: check start day and end day lunch windows; if either intersects, subtract once.
	intersected := false
	ls, err1 := parseHM(lunchStart, startTs)
	le, err2 := parseHM(lunchEnd, startTs)
	if err1 == nil && err2 == nil && startTs.Before(le) && endTs.After(ls) {
		intersected = true
	}
	if !intersected {
		ls2, err3 := parseHM(lunchStart, endTs)
		le2, err4 := parseHM(lunchEnd, endTs)
		if err3 == nil && err4 == nil && startTs.Before(le2) && endTs.After(ls2) {
			intersected = true
		}
	}
	if intersected {
		sub := float64(lunchMinutes) / 60.0
		hours -= sub
		if hours < 0 {
			hours = 0
		}
	}
	return hours
}

// Delete functions
func deleteEntry(id string) {
	db := getDB()
	defer db.Close()

	query := fmt.Sprintf("DELETE FROM %s WHERE id=@id", tbl("entries"))
	_, err := db.Exec(query, sql.Named("id", id))
	if err != nil {
		log.Printf("deleteActivity failed: %v", err)
	}
}

func deleteActivity(id string) {
	db := getDB()
	defer db.Close()

	query := fmt.Sprintf("DELETE FROM %s WHERE id=@id", tbl("type"))
	_, err := db.Exec(query, sql.Named("id", id))
	if err != nil {
		log.Printf("deleteDepartment failed: %v", err)
	}
}

func deleteDepartment(id string) {
	db := getDB()
	defer db.Close()

	query := fmt.Sprintf("DELETE FROM %s WHERE id=@id", tbl("departments"))
	_, err := db.Exec(query, sql.Named("id", id))
	if err != nil {
		log.Printf("deleteUser entries failed: %v", err)
	}
}

func deleteUser(id string) {
	db := getDB()
	defer db.Close()

	// First delete all entries for this user
	query := fmt.Sprintf("DELETE FROM %s WHERE user_id=@id", tbl("entries"))
	_, err := db.Exec(query, sql.Named("id", id))
	if err != nil {
		log.Printf("deleteUser failed: %v", err)
	}

	// Then delete the user
	query = fmt.Sprintf("DELETE FROM %s WHERE id=@id", tbl("users"))
	_, err = db.Exec(query, sql.Named("id", id))
	if err != nil {
		log.Fatal(err)
	}
}

//---------------------------------------------------------------------
// Sichten für Auswertungen
//---------------------------------------------------------------------

func getWorkHoursData() []WorkHoursData {
	if dbBackend == "sqlite" {
		return getWorkHoursDataDirect()
	}
	db := getDB()
	defer db.Close()

	rows, err := db.Query(fmt.Sprintf("SELECT user_name, work_date, work_hours FROM %s ORDER BY work_date DESC, user_name ASC", tbl("work_hours")))
	if err != nil {
		log.Printf("Query work_hours failed: %v", err)
		return nil
	}
	defer rows.Close()

	var list []WorkHoursData
	for rows.Next() {
		var w WorkHoursData
		if err := rows.Scan(&w.UserName, &w.WorkDate, &w.WorkHours); err != nil {
			log.Printf("getWorkHoursData scan failed: %v", err)
			continue
		}
		list = append(list, w)
	}
	return list
}

// getWorkHoursDataDirect computes work hours per user and date directly from entries to avoid view inconsistencies (SQLite only)
func getWorkHoursDataDirect() []WorkHoursData {
	if dbBackend != "sqlite" {
		return getWorkHoursData()
	}
	db := getDB()
	defer db.Close()

	// Load all entries ordered by user and date
	query := fmt.Sprintf(`SELECT u.name AS user_name, e.date AS epoch, t.work AS is_work FROM %s e JOIN %s u ON u.id = e.user_id JOIN %s t ON t.id = e.type_id ORDER BY u.name ASC, e.date ASC`, tbl("entries"), tbl("users"), tbl("type"))
	rows, err := db.Query(query)
	if err != nil {
		log.Printf("Query entries for direct work hours failed: %v", err)
		return nil
	}
	defer rows.Close()

	type rawEntry struct {
		User   string
		Epoch  int64
		IsWork int
	}
	var entries []rawEntry
	for rows.Next() {
		var r rawEntry
		if err := rows.Scan(&r.User, &r.Epoch, &r.IsWork); err != nil {
			log.Printf("scan raw entry failed: %v", err)
			continue
		}
		entries = append(entries, r)
	}

	// Aggregate per user/date applying tenant-local day boundaries and lunch subtraction
	host := GetRequestHost()
	tenantLoc := getTenantLocation(host)
	nowTenant := time.Now().In(tenantLoc)

	// Map key user|date -> hours
	sums := make(map[string]float64)

	// Process entries per user
	i := 0
	for i < len(entries) {
		user := entries[i].User
		// collect all entries for this user
		j := i
		for j < len(entries) && entries[j].User == user {
			j++
		}
		// process entries[i:j]
		for k := i; k < j; k++ {
			startEpoch := entries[k].Epoch
			startTs := time.Unix(startEpoch, 0).In(tenantLoc)
			var endTs time.Time
			if k+1 < j {
				endTs = time.Unix(entries[k+1].Epoch, 0).In(tenantLoc)
			} else {
				dayEnd := time.Date(startTs.Year(), startTs.Month(), startTs.Day(), 23, 59, 59, 0, tenantLoc)
				endTs = dayEnd
				if nowTenant.Before(dayEnd) {
					endTs = nowTenant
				}
			}
			dur := endTs.Sub(startTs).Hours()
			if dur < 0 {
				dur = 0
			}
			dur = applyLunchSubtraction(startTs, endTs, dur)
			if entries[k].IsWork != 1 {
				dur = 0
			}
			dateKey := startTs.Format("2006-01-02")
			sums[user+"|"+dateKey] += dur
		}
		i = j
	}

	// Convert map to slice
	var out []WorkHoursData
	for k, v := range sums {
		parts := strings.SplitN(k, "|", 2)
		if len(parts) != 2 {
			continue
		}
		// Round to 2 decimals
		rounded, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", v), 64)
		out = append(out, WorkHoursData{UserName: parts[0], WorkDate: parts[1], WorkHours: rounded})
	}
	// Sort results by date desc then user asc
	sort.Slice(out, func(i, j int) bool {
		if out[i].WorkDate == out[j].WorkDate {
			return out[i].UserName < out[j].UserName
		}
		return out[i].WorkDate > out[j].WorkDate
	})
	return out
}

func getCurrentStatusData() []CurrentStatusData {
	db := getDB()
	defer db.Close()
	var rows *sql.Rows
	var err error
	if dbBackend == "sqlite" {
		q := loadSQL("current_status_view")
		rows, err = db.Query(q)
	} else {
		rows, err = db.Query(fmt.Sprintf("SELECT user_name, status, date FROM %s", tbl("current_status")))
	}
	if err != nil {
		log.Printf("Query current_status failed: %v", err)
		return nil
	}
	defer rows.Close()

	var list []CurrentStatusData
	for rows.Next() {
		var c CurrentStatusData
		var raw interface{}
		if err := rows.Scan(&c.UserName, &c.Status, &raw); err != nil {
			log.Printf("getCurrentStatusData scan failed: %v", err)
			continue
		}
		// Normalize date to string; tolerate NULLs and backend-specific types
		switch v := raw.(type) {
		case nil:
			c.Date = ""
		case string:
			c.Date = v
		case []byte:
			c.Date = string(v)
		case time.Time:
			// Use RFC3339 for robust downstream parsing
			c.Date = v.Format(time.RFC3339)
		default:
			c.Date = fmt.Sprint(v)
		}
		list = append(list, c)
	}
	return list
}

// Enhanced statistics data structures
type DepartmentSummary struct {
	DepartmentName  string
	TotalUsers      int
	TotalHours      float64
	AvgHoursPerUser float64
}

type TimeTrackingTrend struct {
	Date         string
	TotalHours   float64
	ActiveUsers  int
	WorkEntries  int
	BreakEntries int
}

type UserActivitySummary struct {
	UserName        string
	Department      string
	TotalWorkHours  float64
	TotalBreakHours float64
	LastActivity    string
	Status          string
}

// Daily per-user activity used for dashboard drill-down
type UserDailyActivity struct {
	UserName     string
	Department   string
	WorkHours    float64
	BreakHours   float64
	LastActivity string
	Status       string
}

type EntryDetail struct {
	ID         int
	UserID     int
	UserName   string
	Department string
	ActivityID int
	Activity   string
	Date       string
	Start      string
	End        string
	Duration   float64
	Comment    string
}

// Enhanced statistics functions
func getDepartmentSummary() []DepartmentSummary {
	db := getDB()
	defer db.Close()

	query := fmt.Sprintf(`
		SELECT 
			d.name as department_name,
			COUNT(DISTINCT u.id) as total_users,
			COALESCE(SUM(wh.work_hours), 0) as total_hours,
			CASE 
				WHEN COUNT(DISTINCT u.id) > 0 
				THEN COALESCE(SUM(wh.work_hours), 0) / COUNT(DISTINCT u.id)
				ELSE 0 
			END as avg_hours_per_user
		FROM %s d
		LEFT JOIN %s u ON d.id = u.department_id
		LEFT JOIN %s wh ON u.name = wh.user_name
		GROUP BY d.id, d.name
		ORDER BY total_hours DESC
	`, tbl("departments"), tbl("users"), tbl("work_hours"))

	rows, err := db.Query(query)
	if err != nil {
		log.Printf("Query department summary failed: %v", err)
		return nil
	}
	defer rows.Close()

	var list []DepartmentSummary
	for rows.Next() {
		var d DepartmentSummary
		if err := rows.Scan(&d.DepartmentName, &d.TotalUsers, &d.TotalHours, &d.AvgHoursPerUser); err != nil {
			log.Printf("Scan department summary failed: %v", err)
			continue
		}
		list = append(list, d)
	}
	return list
}

func getTimeTrackingTrends(days int) []TimeTrackingTrend {
	db := getDB()
	defer db.Close()

	base := loadSQL("get_time_tracking_trends")
	query := fmt.Sprintf(base, days)

	rows, err := db.Query(query)
	if err != nil {
		log.Printf("Query time tracking trends failed: %v", err)
		return nil
	}
	defer rows.Close()

	var list []TimeTrackingTrend
	for rows.Next() {
		var t TimeTrackingTrend
		var date sql.NullString
		if err := rows.Scan(&date, &t.TotalHours, &t.ActiveUsers, &t.WorkEntries, &t.BreakEntries); err != nil {
			log.Printf("Scan time tracking trend failed: %v", err)
			continue
		}
		if date.Valid {
			t.Date = date.String
		} else {
			t.Date = ""
		}
		list = append(list, t)
	}
	return list
}

func getUserActivitySummary() []UserActivitySummary {
	db := getDB()
	defer db.Close()

	query := loadSQL("get_user_activity_summary")

	rows, err := db.Query(query)
	if err != nil {
		log.Printf("Query user activity summary failed: %v", err)
		return nil
	}
	defer rows.Close()

	var list []UserActivitySummary
	for rows.Next() {
		var u UserActivitySummary
		if err := rows.Scan(&u.UserName, &u.Department, &u.TotalWorkHours, &u.TotalBreakHours, &u.LastActivity, &u.Status); err != nil {
			log.Printf("Scan user activity summary failed: %v", err)
			continue
		}
		list = append(list, u)
	}
	return list
}

// getUsersByDepartmentOnDay returns users in a department with their work/break hours on a specific day (YYYY-MM-DD)
func getUsersByDepartmentOnDay(deptName, day string) []UserDailyActivity {
	db := getDB()
	defer db.Close()

	query := loadSQL("get_users_by_department_on_day")

	rows, err := db.Query(query, day, day, deptName)
	if err != nil {
		log.Printf("Query users by department/day failed: %v", err)
		return nil
	}
	defer rows.Close()

	var list []UserDailyActivity
	for rows.Next() {
		var u UserDailyActivity
		if err := rows.Scan(&u.UserName, &u.Department, &u.WorkHours, &u.BreakHours, &u.LastActivity, &u.Status); err != nil {
			log.Printf("Scan user daily activity failed: %v", err)
			continue
		}
		list = append(list, u)
	}
	return list
}

// getUserActivitySummaryByDepartment filters overall user activity by department name
func getUserActivitySummaryByDepartment(deptName string) []UserActivitySummary {
	all := getUserActivitySummary()
	if deptName == "" {
		return all
	}
	out := make([]UserActivitySummary, 0, len(all))
	for _, u := range all {
		if u.Department == deptName {
			out = append(out, u)
		}
	}
	return out
}

// getDepartmentSummaryOnDay computes per-department hours for a specific day (YYYY-MM-DD)
func getDepartmentSummaryOnDay(day string) []DepartmentSummary {
	db := getDB()
	defer db.Close()

	query := loadSQL("get_department_summary_on_day")

	rows, err := db.Query(query, day)
	if err != nil {
		log.Printf("Query department summary on day failed: %v", err)
		return nil
	}
	defer rows.Close()

	var list []DepartmentSummary
	for rows.Next() {
		var d DepartmentSummary
		if err := rows.Scan(&d.DepartmentName, &d.TotalUsers, &d.TotalHours, &d.AvgHoursPerUser); err != nil {
			log.Printf("Scan department summary on day failed: %v", err)
			continue
		}
		list = append(list, d)
	}
	return list
}

func getEntriesWithDetails() []EntryDetail {
	db := getDB()
	defer db.Close()

	// Select next event end_time without doing duration math in SQL to avoid
	// timezone differences between SQLite datetime('now') (UTC) and local times.
	query := loadSQL("get_entries_with_details")

	rows, err := db.Query(query)
	if err != nil {
		log.Printf("Query entries with details failed: %v", err)
		return nil
	}
	defer rows.Close()

	host := GetRequestHost()
	tenantLoc := getTenantLocation(host)
	nowTenant := time.Now().In(tenantLoc)
	var list []EntryDetail
	for rows.Next() {
		var e EntryDetail
		var end sql.NullString
		if err := rows.Scan(&e.ID, &e.UserID, &e.UserName, &e.Department, &e.ActivityID, &e.Activity, &e.Date, &e.Start, &end, &e.Comment); err != nil {
			log.Printf("Scan entry detail failed: %v", err)
			continue
		}
		// Compute duration in Go using tenant location and avoid SQLite now()/UTC quirks
		startTs := parseDBTimeInLoc(e.Start, tenantLoc)
		// default end is min(next_e, end-of-day, now)
		dayEnd := time.Date(startTs.Year(), startTs.Month(), startTs.Day(), 23, 59, 59, 0, tenantLoc)
		var endTs time.Time
		if end.Valid && strings.TrimSpace(end.String) != "" {
			endTs = parseDBTimeInLoc(end.String, tenantLoc)
			e.End = end.String
		} else {
			// no next entry: cap at end of day; if same day, also cap by now
			endTs = dayEnd
			if nowTenant.Before(dayEnd) {
				endTs = nowTenant
			}
			e.End = ""
		}
		dur := endTs.Sub(startTs).Hours()
		if dur < 0 {
			dur = 0
		}
		dur = applyLunchSubtraction(startTs, endTs, dur)
		e.Duration = dur
		list = append(list, e)
	}
	return list
}

// getEntriesForDepartmentOnDay returns entry details for a department on a specific day (YYYY-MM-DD)
func getEntriesForDepartmentOnDay(deptName, day string) []EntryDetail {
	db := getDB()
	defer db.Close()

	query := loadSQL("get_entries_for_dept_day")

	rows, err := db.Query(query, day, deptName)
	if err != nil {
		log.Printf("Query entries for dept/day failed: %v", err)
		return nil
	}
	defer rows.Close()

	var list []EntryDetail
	for rows.Next() {
		var e EntryDetail
		if err := rows.Scan(&e.ID, &e.UserID, &e.UserName, &e.Department, &e.ActivityID, &e.Activity, &e.Date, &e.Start, &e.End, &e.Duration, &e.Comment); err != nil {
			log.Printf("Scan entry detail (dept/day) failed: %v", err)
			continue
		}
		list = append(list, e)
	}

	return list
}

// getEntriesWithDetailsFiltered returns filtered time entries with details
func getEntriesWithDetailsFiltered(fromDate, toDate, department, user, activity, limit string) []EntryDetail {
	db := getDB()
	defer db.Close()

	// Build dynamic query with filters; compute potential end_time via subquery
	query := loadSQL("get_entries_with_details_filtered_base")

	var args []interface{}

	// Add date range filters against e.date
	if fromDate != "" {
		query += " AND DATE(e.date) >= ?"
		args = append(args, fromDate)
	}
	if toDate != "" {
		query += " AND DATE(e.date) <= ?"
		args = append(args, toDate)
	}

	// Add department filter (expects department id)
	if department != "" && department != "0" {
		query += " AND u.department_id = ?"
		args = append(args, department)
	}

	// Add user filter (expects user id)
	if user != "" && user != "0" {
		query += " AND e.user_id = ?"
		args = append(args, user)
	}

	// Add activity filter (expects type id)
	if activity != "" && activity != "0" {
		query += " AND e.type_id = ?"
		args = append(args, activity)
	}

	query += " ORDER BY e.date DESC"

	// Add limit for preview
	if limit != "" && limit != "0" {
		query += " LIMIT ?"
		if limitInt, err := strconv.Atoi(limit); err == nil {
			args = append(args, limitInt)
		}
	}

	rows, err := db.Query(query, args...)
	if err != nil {
		log.Printf("Query filtered entries failed: %v", err)
		return nil
	}
	defer rows.Close()

	host := GetRequestHost()
	tenantLoc := getTenantLocation(host)
	nowTenant := time.Now().In(tenantLoc)
	var list []EntryDetail
	for rows.Next() {
		var e EntryDetail
		var end sql.NullString
		if err := rows.Scan(&e.ID, &e.UserID, &e.UserName, &e.Department, &e.ActivityID, &e.Activity, &e.Date, &e.Start, &end, &e.Comment); err != nil {
			log.Printf("Scan filtered entry detail failed: %v", err)
			continue
		}
		// Compute duration in tenant time
		startTs := parseDBTimeInLoc(e.Start, tenantLoc)
		var endTs time.Time
		if end.Valid && strings.TrimSpace(end.String) != "" {
			endTs = parseDBTimeInLoc(end.String, tenantLoc)
			e.End = end.String
		} else {
			endTs = nowTenant
			e.End = ""
		}
		dur := endTs.Sub(startTs).Hours()
		if dur < 0 {
			dur = 0
		}
		dur = applyLunchSubtraction(startTs, endTs, dur)
		e.Duration = dur
		list = append(list, e)
	}
	return list
}

// getWorkHoursDataFiltered returns work hours from the work_hours view with optional filters
func getWorkHoursDataFiltered(fromDate, toDate, user, limit string) []WorkHoursData {
	db := getDB()
	defer db.Close()

	query := loadSQL("get_work_hours_filtered_base")
	var args []interface{}

	if fromDate != "" {
		query += " AND work_date >= ?"
		args = append(args, fromDate)
	}
	if toDate != "" {
		query += " AND work_date <= ?"
		args = append(args, toDate)
	}
	if user != "" {
		query += " AND user_name = ?"
		args = append(args, user)
	}
	query += " ORDER BY work_date DESC, user_name ASC"
	if limit != "" && limit != "0" {
		if lim, err := strconv.Atoi(limit); err == nil && lim > 0 {
			query += " LIMIT ?"
			args = append(args, lim)
		}
	}

	rows, err := db.Query(query, args...)
	if err != nil {
		log.Printf("Query filtered work hours failed: %v", err)
		return nil
	}
	defer rows.Close()

	var list []WorkHoursData
	for rows.Next() {
		var wh WorkHoursData
		if err := rows.Scan(&wh.UserName, &wh.WorkDate, &wh.WorkHours); err != nil {
			log.Printf("Scan filtered work hours failed: %v", err)
			continue
		}
		list = append(list, wh)
	}
	return list
}

// getCalendarEntries returns raw entry events and computes per-event duration hours up to the next event or day-end
func getCalendarEntries(start, end time.Time, userFilter, activityFilter string) []CalendarEntry {
	db := getDB()
	defer db.Close()

	// Build base query portable across backends using >= and <= with timestamps
	query := fmt.Sprintf(`
		SELECT u.name as user_name, t.status as activity, t.work as is_work, e.date
		FROM %s e
		JOIN %s u ON e.user_id = u.id
		JOIN %s t ON e.type_id = t.id
		WHERE e.date >= strftime('%%s', @from) AND e.date <= strftime('%%s', @to)
	`, tbl("entries"), tbl("users"), tbl("type"))

	args := []interface{}{sql.Named("from", start), sql.Named("to", end)}
	if strings.TrimSpace(userFilter) != "" && userFilter != "0" {
		query += " AND e.user_id = @uid"
		args = append(args, sql.Named("uid", userFilter))
	}
	if strings.TrimSpace(activityFilter) != "" && activityFilter != "0" {
		query += " AND e.type_id = @aid"
		args = append(args, sql.Named("aid", activityFilter))
	}
	query += " ORDER BY u.name ASC, e.date ASC"

	rows, err := db.Query(query, args...)
	if err != nil {
		log.Printf("Query calendar entries failed: %v", err)
		return nil
	}
	defer rows.Close()

	// Read all events then compute hours relative to next event or end-of-day
	type ev struct {
		DateStr  string
		User     string
		Activity string
		IsWork   bool
	}
	var events []ev
	for rows.Next() {
		var userName, activity string
		var isWork int
		var dateVal int64
		if err := rows.Scan(&userName, &activity, &isWork, &dateVal); err != nil {
			log.Printf("Scan calendar ev failed: %v", err)
			continue
		}
		events = append(events, ev{DateStr: time.Unix(dateVal, 0).Format("2006-01-02 15:04:05"), User: userName, Activity: activity, IsWork: isWork == 1})
	}
	// Compute durations
	var out []CalendarEntry
	host := GetRequestHost()
	tenantLoc := getTenantLocation(host)
	for i := 0; i < len(events); i++ {
		cur := events[i]
		startTs := parseDBTimeInLoc(cur.DateStr, tenantLoc)
		// Determine end as next event of same user or end of day, also clamp by overall end
		endTs := time.Date(startTs.Year(), startTs.Month(), startTs.Day(), 23, 59, 59, 0, tenantLoc)
		// If the overall end bound is earlier (same day boundary), clamp
		if end.Before(endTs) {
			endTs = end
		}
		for j := i + 1; j < len(events); j++ {
			nxt := events[j]
			if nxt.User != cur.User {
				continue
			}
			nextTs := parseDBTimeInLoc(nxt.DateStr, tenantLoc)
			if nextTs.After(startTs) {
				// cap to same day
				dayEnd := time.Date(startTs.Year(), startTs.Month(), startTs.Day(), 23, 59, 59, 0, tenantLoc)
				if nextTs.Before(dayEnd) {
					endTs = nextTs
				} else {
					endTs = dayEnd
				}
				break
			}
		}
		// create entry
		dur := endTs.Sub(startTs).Hours()
		if dur < 0 {
			dur = 0
		}
		dur = applyLunchSubtraction(startTs, endTs, dur)
		out = append(out, CalendarEntry{
			Date:     cur.DateStr,
			UserName: cur.User,
			Activity: cur.Activity,
			Hours:    dur,
			IsWork:   cur.IsWork,
		})
	}
	return out
}