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
}