medi-wol/internal/database/database.go

package database

import (
	"database/sql"
	"log"
	"medi-wol/internal/models"
	"time"

	_ "modernc.org/sqlite"
)

// DB ist die Datenbankverbindung
type DB struct {
	*sql.DB
}

// InitDB initialisiert die Datenbank und erstellt die Tabellen
func InitDB() (*DB, error) {
	db, err := sql.Open("sqlite", "./medi-wol.db")
	if err != nil {
		return nil, err
	}

	// PC-Tabelle erstellen
	createPCTableSQL := `
	CREATE TABLE IF NOT EXISTS pcs (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		name TEXT NOT NULL,
		mac TEXT NOT NULL UNIQUE,
		ip TEXT NOT NULL,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
	);`

	_, err = db.Exec(createPCTableSQL)
	if err != nil {
		return nil, err
	}

	// Log-Tabelle erstellen
	createLogTableSQL := `
	CREATE TABLE IF NOT EXISTS wol_logs (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
		pc_id INTEGER NOT NULL,
		pc_name TEXT NOT NULL,
		mac TEXT NOT NULL,
		trigger TEXT NOT NULL,
		FOREIGN KEY (pc_id) REFERENCES pcs (id) ON DELETE CASCADE
	);`

	_, err = db.Exec(createLogTableSQL)
	if err != nil {
		return nil, err
	}

	// Füge IP-Spalte hinzu, falls sie nicht existiert
	_, err = db.Exec("ALTER TABLE pcs ADD COLUMN ip TEXT DEFAULT ''")
	if err != nil {
		// Spalte existiert bereits, ignorieren
	}

	log.Println("Datenbank erfolgreich initialisiert")
	return &DB{db}, nil
}

// GetAllPCs holt alle PCs aus der Datenbank
func (db *DB) GetAllPCs() ([]models.PC, error) {
	rows, err := db.Query("SELECT id, name, mac, ip, created_at, updated_at FROM pcs ORDER BY name")
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var pcs []models.PC
	for rows.Next() {
		var pc models.PC
		err := rows.Scan(&pc.ID, &pc.Name, &pc.MAC, &pc.IP, &pc.CreatedAt, &pc.UpdatedAt)
		if err != nil {
			return nil, err
		}
		pcs = append(pcs, pc)
	}

	return pcs, nil
}

// CreatePC erstellt einen neuen PC-Eintrag
func (db *DB) CreatePC(name, mac, ip string) (*models.PC, error) {
	now := time.Now()
	result, err := db.Exec(
		"INSERT INTO pcs (name, mac, ip, created_at, updated_at) VALUES (?, ?, ?, ?, ?)",
		name, mac, ip, now, now,
	)
	if err != nil {
		return nil, err
	}

	id, err := result.LastInsertId()
	if err != nil {
		return nil, err
	}

	return &models.PC{
		ID:        int(id),
		Name:      name,
		MAC:       mac,
		IP:        ip,
		CreatedAt: now,
		UpdatedAt: now,
	}, nil
}

// UpdatePC aktualisiert einen bestehenden PC-Eintrag
func (db *DB) UpdatePC(id int, name, mac, ip string) (*models.PC, error) {
	now := time.Now()
	_, err := db.Exec(
		"UPDATE pcs SET name = ?, mac = ?, ip = ?, updated_at = ? WHERE id = ?",
		name, mac, ip, now, id,
	)
	if err != nil {
		return nil, err
	}

	return &models.PC{
		ID:        id,
		Name:      name,
		MAC:       mac,
		IP:        ip,
		UpdatedAt: now,
	}, nil
}

// DeletePC löscht einen PC-Eintrag
func (db *DB) DeletePC(id int) error {
	_, err := db.Exec("DELETE FROM pcs WHERE id = ?", id)
	return err
}

// GetPCByID holt einen PC anhand der ID
func (db *DB) GetPCByID(id int) (*models.PC, error) {
	var pc models.PC
	err := db.QueryRow(
		"SELECT id, name, mac, ip, created_at, updated_at FROM pcs WHERE id = ?",
		id,
	).Scan(&pc.ID, &pc.Name, &pc.MAC, &pc.IP, &pc.CreatedAt, &pc.UpdatedAt)

	if err != nil {
		return nil, err
	}

	return &pc, nil
}

// CreateLog erstellt einen neuen Log-Eintrag
func (db *DB) CreateLog(pcID int, pcName, mac, trigger string) (*models.LogEvent, error) {
	now := time.Now()
	result, err := db.Exec(
		"INSERT INTO wol_logs (timestamp, pc_id, pc_name, mac, trigger) VALUES (?, ?, ?, ?, ?)",
		now, pcID, pcName, mac, trigger,
	)
	if err != nil {
		return nil, err
	}

	id, err := result.LastInsertId()
	if err != nil {
		return nil, err
	}

	return &models.LogEvent{
		ID:        int(id),
		Timestamp: now,
		PCID:      pcID,
		PCName:    pcName,
		MAC:       mac,
		Trigger:   trigger,
	}, nil
}

// GetAllLogs holt alle Log-Einträge aus der Datenbank
func (db *DB) GetAllLogs() ([]models.LogEvent, error) {
	rows, err := db.Query("SELECT id, timestamp, pc_id, pc_name, mac, trigger FROM wol_logs ORDER BY timestamp DESC")
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var logs []models.LogEvent
	for rows.Next() {
		var log models.LogEvent
		err := rows.Scan(&log.ID, &log.Timestamp, &log.PCID, &log.PCName, &log.MAC, &log.Trigger)
		if err != nil {
			return nil, err
		}
		logs = append(logs, log)
	}

	return logs, nil
}

// GetLogsByPCID holt alle Log-Einträge für einen bestimmten PC
func (db *DB) GetLogsByPCID(pcID int) ([]models.LogEvent, error) {
	rows, err := db.Query("SELECT id, timestamp, pc_id, pc_name, mac, trigger FROM wol_logs WHERE pc_id = ? ORDER BY timestamp DESC", pcID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var logs []models.LogEvent
	for rows.Next() {
		var log models.LogEvent
		err := rows.Scan(&log.ID, &log.Timestamp, &log.PCID, &log.PCName, &log.MAC, &log.Trigger)
		if err != nil {
			return nil, err
		}
		logs = append(logs, log)
	}

	return logs, nil
}

// GetRecentLogsByPCID holt die letzten 5 Log-Einträge für einen bestimmten PC
func (db *DB) GetRecentLogsByPCID(pcID int) ([]models.LogEvent, error) {
	rows, err := db.Query("SELECT id, timestamp, pc_id, pc_name, mac, trigger FROM wol_logs WHERE pc_id = ? ORDER BY timestamp DESC LIMIT 5", pcID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var logs []models.LogEvent
	for rows.Next() {
		var log models.LogEvent
		err := rows.Scan(&log.ID, &log.Timestamp, &log.PCID, &log.PCName, &log.MAC, &log.Trigger)
		if err != nil {
			return nil, err
		}
		logs = append(logs, log)
	}

	return logs, nil
}