327 lines
9.5 KiB
Go
327 lines
9.5 KiB
Go
package database
|
|
|
|
import (
|
|
"database/sql"
|
|
"fmt"
|
|
"log"
|
|
"os"
|
|
"path/filepath"
|
|
|
|
_ "github.com/go-sql-driver/mysql"
|
|
_ "github.com/mattn/go-sqlite3"
|
|
|
|
"admintemplate/pkg/config"
|
|
)
|
|
|
|
type DB struct {
|
|
*sql.DB
|
|
Type string
|
|
}
|
|
|
|
func Connect(cfg *config.DatabaseConfig) (*DB, error) {
|
|
var db *sql.DB
|
|
var err error
|
|
|
|
switch cfg.Type {
|
|
case "sqlite":
|
|
// Ensure directory exists for SQLite
|
|
dir := filepath.Dir(cfg.SQLitePath)
|
|
if err := os.MkdirAll(dir, 0755); err != nil {
|
|
return nil, fmt.Errorf("failed to create data directory: %w", err)
|
|
}
|
|
|
|
db, err = sql.Open("sqlite3", cfg.SQLitePath)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("failed to connect to SQLite: %w", err)
|
|
}
|
|
|
|
case "mysql":
|
|
dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?parseTime=true",
|
|
cfg.User, cfg.Password, cfg.Host, cfg.Port, cfg.DBName)
|
|
db, err = sql.Open("mysql", dsn)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("failed to connect to MySQL: %w", err)
|
|
}
|
|
|
|
default:
|
|
return nil, fmt.Errorf("unsupported database type: %s", cfg.Type)
|
|
}
|
|
|
|
// Test connection
|
|
if err := db.Ping(); err != nil {
|
|
return nil, fmt.Errorf("failed to ping database: %w", err)
|
|
}
|
|
|
|
log.Printf("Successfully connected to %s database", cfg.Type)
|
|
|
|
return &DB{DB: db, Type: cfg.Type}, nil
|
|
}
|
|
|
|
func (db *DB) Migrate() error {
|
|
var queries []string
|
|
|
|
if db.Type == "sqlite" {
|
|
queries = []string{
|
|
`CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT UNIQUE NOT NULL,
|
|
password TEXT NOT NULL,
|
|
email TEXT UNIQUE NOT NULL,
|
|
role TEXT NOT NULL DEFAULT 'user',
|
|
active INTEGER NOT NULL DEFAULT 1,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);`,
|
|
`CREATE TABLE IF NOT EXISTS menu_items (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
parent_id INTEGER,
|
|
title TEXT NOT NULL,
|
|
icon TEXT DEFAULT '',
|
|
route TEXT DEFAULT '',
|
|
sort_order INTEGER DEFAULT 0,
|
|
active INTEGER NOT NULL DEFAULT 1,
|
|
role_required TEXT DEFAULT '',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (parent_id) REFERENCES menu_items(id) ON DELETE CASCADE
|
|
);`,
|
|
`CREATE TABLE IF NOT EXISTS user_settings (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER UNIQUE NOT NULL,
|
|
sidebar_position TEXT DEFAULT 'left',
|
|
sidebar_collapsed INTEGER DEFAULT 0,
|
|
theme TEXT DEFAULT 'light',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);`,
|
|
`CREATE TABLE IF NOT EXISTS toolbar_items (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
title TEXT NOT NULL,
|
|
icon TEXT DEFAULT '',
|
|
action TEXT NOT NULL,
|
|
shortcut TEXT DEFAULT '',
|
|
sort_order INTEGER DEFAULT 0,
|
|
active INTEGER NOT NULL DEFAULT 1,
|
|
separator INTEGER DEFAULT 0,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);`,
|
|
}
|
|
} else {
|
|
queries = []string{
|
|
`CREATE TABLE IF NOT EXISTS users (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(255) UNIQUE NOT NULL,
|
|
password VARCHAR(255) NOT NULL,
|
|
email VARCHAR(255) UNIQUE NOT NULL,
|
|
role VARCHAR(50) NOT NULL DEFAULT 'user',
|
|
active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
);`,
|
|
`CREATE TABLE IF NOT EXISTS menu_items (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|
parent_id BIGINT,
|
|
title VARCHAR(255) NOT NULL,
|
|
icon VARCHAR(100) DEFAULT '',
|
|
route VARCHAR(255) DEFAULT '',
|
|
sort_order INT DEFAULT 0,
|
|
active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
role_required VARCHAR(50) DEFAULT '',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (parent_id) REFERENCES menu_items(id) ON DELETE CASCADE
|
|
);`,
|
|
`CREATE TABLE IF NOT EXISTS user_settings (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|
user_id BIGINT UNIQUE NOT NULL,
|
|
sidebar_position VARCHAR(10) DEFAULT 'left',
|
|
sidebar_collapsed BOOLEAN DEFAULT FALSE,
|
|
theme VARCHAR(20) DEFAULT 'light',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);`,
|
|
`CREATE TABLE IF NOT EXISTS toolbar_items (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|
title VARCHAR(255) NOT NULL,
|
|
icon VARCHAR(100) DEFAULT '',
|
|
action VARCHAR(100) NOT NULL,
|
|
shortcut VARCHAR(50) DEFAULT '',
|
|
sort_order INT DEFAULT 0,
|
|
active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
separator BOOLEAN DEFAULT FALSE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
);`,
|
|
}
|
|
}
|
|
|
|
for _, query := range queries {
|
|
if _, err := db.Exec(query); err != nil {
|
|
return fmt.Errorf("failed to execute migration: %w", err)
|
|
}
|
|
}
|
|
|
|
// Seed default menu items
|
|
if err := db.seedMenuItems(); err != nil {
|
|
return fmt.Errorf("failed to seed menu items: %w", err)
|
|
}
|
|
|
|
// Seed default toolbar items
|
|
if err := db.seedToolbarItems(); err != nil {
|
|
return fmt.Errorf("failed to seed toolbar items: %w", err)
|
|
}
|
|
|
|
log.Println("Database migration completed successfully")
|
|
return nil
|
|
}
|
|
|
|
func (db *DB) seedMenuItems() error {
|
|
// Check if menu items already exist
|
|
var count int
|
|
err := db.QueryRow("SELECT COUNT(*) FROM menu_items").Scan(&count)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if count > 0 {
|
|
return nil // Already seeded
|
|
}
|
|
|
|
menuItems := []struct {
|
|
parentID *int64
|
|
title string
|
|
icon string
|
|
route string
|
|
sortOrder int
|
|
roleRequired string
|
|
}{
|
|
{nil, "Dashboard", "home", "/dashboard", 1, ""},
|
|
{nil, "Verwaltung", "settings", "", 2, "admin"},
|
|
{nil, "Berichte", "chart", "", 3, ""},
|
|
{nil, "Einstellungen", "cog", "", 4, ""},
|
|
}
|
|
|
|
// Insert root items
|
|
for _, item := range menuItems {
|
|
var result int64
|
|
if db.Type == "sqlite" {
|
|
res, err := db.Exec(`INSERT INTO menu_items (parent_id, title, icon, route, sort_order, role_required) VALUES (?, ?, ?, ?, ?, ?)`,
|
|
item.parentID, item.title, item.icon, item.route, item.sortOrder, item.roleRequired)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
result, _ = res.LastInsertId()
|
|
} else {
|
|
res, err := db.Exec(`INSERT INTO menu_items (parent_id, title, icon, route, sort_order, role_required) VALUES (?, ?, ?, ?, ?, ?)`,
|
|
item.parentID, item.title, item.icon, item.route, item.sortOrder, item.roleRequired)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
result, _ = res.LastInsertId()
|
|
}
|
|
|
|
// Add sub-items based on parent
|
|
switch item.title {
|
|
case "Verwaltung":
|
|
subItems := []struct {
|
|
title string
|
|
icon string
|
|
route string
|
|
order int
|
|
}{
|
|
{"Benutzer", "users", "/users", 1},
|
|
{"Rollen", "shield", "/roles", 2},
|
|
{"Gruppen", "folder", "/groups", 3},
|
|
}
|
|
for _, sub := range subItems {
|
|
_, err := db.Exec(`INSERT INTO menu_items (parent_id, title, icon, route, sort_order, role_required) VALUES (?, ?, ?, ?, ?, ?)`,
|
|
result, sub.title, sub.icon, sub.route, sub.order, "admin")
|
|
if err != nil {
|
|
return err
|
|
}
|
|
}
|
|
case "Berichte":
|
|
subItems := []struct {
|
|
title string
|
|
icon string
|
|
route string
|
|
order int
|
|
}{
|
|
{"Aktivitäten", "activity", "/reports/activities", 1},
|
|
{"Statistiken", "bar-chart", "/reports/statistics", 2},
|
|
{"Export", "download", "/reports/export", 3},
|
|
}
|
|
for _, sub := range subItems {
|
|
_, err := db.Exec(`INSERT INTO menu_items (parent_id, title, icon, route, sort_order, role_required) VALUES (?, ?, ?, ?, ?, ?)`,
|
|
result, sub.title, sub.icon, sub.route, sub.order, "")
|
|
if err != nil {
|
|
return err
|
|
}
|
|
}
|
|
case "Einstellungen":
|
|
subItems := []struct {
|
|
title string
|
|
icon string
|
|
route string
|
|
order int
|
|
}{
|
|
{"Profil", "user", "/profile", 1},
|
|
{"Sicherheit", "lock", "/settings/security", 2},
|
|
{"Anzeige", "monitor", "/settings/display", 3},
|
|
}
|
|
for _, sub := range subItems {
|
|
_, err := db.Exec(`INSERT INTO menu_items (parent_id, title, icon, route, sort_order, role_required) VALUES (?, ?, ?, ?, ?, ?)`,
|
|
result, sub.title, sub.icon, sub.route, sub.order, "")
|
|
if err != nil {
|
|
return err
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
return nil
|
|
}
|
|
|
|
func (db *DB) seedToolbarItems() error {
|
|
// Check if toolbar items already exist
|
|
var count int
|
|
err := db.QueryRow("SELECT COUNT(*) FROM toolbar_items").Scan(&count)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if count > 0 {
|
|
return nil // Already seeded
|
|
}
|
|
|
|
toolbarItems := []struct {
|
|
title string
|
|
icon string
|
|
action string
|
|
shortcut string
|
|
sortOrder int
|
|
separator bool
|
|
}{
|
|
{"Ausschneiden", "scissors", "cut", "Ctrl+X", 1, false},
|
|
{"Kopieren", "copy", "copy", "Ctrl+C", 2, false},
|
|
{"Einfügen", "clipboard", "paste", "Ctrl+V", 3, true},
|
|
{"Rückgängig", "rotate-ccw", "undo", "Ctrl+Z", 4, false},
|
|
{"Wiederholen", "rotate-cw", "redo", "Ctrl+Y", 5, true},
|
|
{"Speichern", "save", "save", "Ctrl+S", 6, false},
|
|
{"Drucken", "printer", "print", "Ctrl+P", 7, true},
|
|
{"Suchen", "search", "search", "Ctrl+F", 8, false},
|
|
{"Hilfe", "help-circle", "help", "F1", 9, false},
|
|
}
|
|
|
|
for _, item := range toolbarItems {
|
|
_, err := db.Exec(`INSERT INTO toolbar_items (title, icon, action, shortcut, sort_order, separator) VALUES (?, ?, ?, ?, ?, ?)`,
|
|
item.title, item.icon, item.action, item.shortcut, item.sortOrder, item.separator)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
}
|
|
|
|
return nil
|
|
}
|