package db_management

import (
	"database/sql"
	"log"
	"os"
	"time"

	_ "modernc.org/sqlite"
)

var DB *sql.DB

func InitDB() {
	var err error
	// Öffnet bot.db im aktuellen Verzeichnis
	dbPath := os.Getenv("DB_PATH")
	if dbPath == "" {
		dbPath = "./bot.db"
	}

	DB, err = sql.Open("sqlite", dbPath)
	if err != nil {
		panic(err)
	}

	// Tabelle für einzelne Sessions
	statement := `
    CREATE TABLE IF NOT EXISTS voice_sessions (
        user_id TEXT,
        seconds INTEGER,
        created_at TIMESTAMP
    );`
	_, err = DB.Exec(statement)
	if err != nil {
		panic(err)
	}
}

func SaveSession(userID string, seconds int) {
	query := `
	INSERT INTO voice_sessions (user_id, seconds, created_at)
	VALUES (?, ?, ?)
	`

	DB.Exec(query, userID, seconds, time.Now())
}

func GetUserStats(userID string) int {
	var total sql.NullInt64 // NullInt64 fängt NULL-Ergebnisse ab
	query := `SELECT SUM(seconds) FROM voice_sessions 
              WHERE user_id = ? AND created_at > datetime('now', '-30 days')`

	err := DB.QueryRow(query, userID).Scan(&total)
	if err != nil || !total.Valid {
		return 0
	}
	return int(total.Int64)
}

type UserStats struct {
	UserID  string
	Seconds int
}

func GetTopUsers(limit int) []UserStats {
	query := `
	SELECT user_id, SUM(seconds) as total
	FROM voice_sessions
	WHERE created_at > datetime('now', '-30 days')
	GROUP BY user_id
	ORDER BY total DESC
	LIMIT ?
	`

	rows, err := DB.Query(query, limit)
	if err != nil {
		return nil
	}
	defer rows.Close()

	var result []UserStats

	for rows.Next() {
		var u UserStats
		err := rows.Scan(&u.UserID, &u.Seconds)
		if err != nil {
			continue
		}
		result = append(result, u)
	}

	return result
}

func CleanupOldEntries() {
	query := `DELETE FROM voice_sessions WHERE created_at < datetime('now', '-40 days)`

	result, err := DB.Exec(query)
	if err != nil {
		log.Printf("Fehler beim DB-Cleanup: %v", err)
		return
	}

	rowsAffected, _ := result.RowsAffected()
	if rowsAffected > 0 {
		log.Printf("DB Cleanup erfolgreich: %d alte Einträge gelöscht.", rowsAffected)
	}
}