dhcp-dns-manager/internal/db/database.go

package db

import (
	"time"
	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
	_ "github.com/mattn/go-sqlite3"
)

type DB struct {
	*gorm.DB
}

type DHCPLease struct {
	ID        uint   `gorm:"primaryKey"`
	MAC       string `gorm:"index"`
	IP        string
	Hostname  string
	ExpiresAt int64
}

type DHCPStaticBinding struct {
	ID          uint   `gorm:"primaryKey"`
	MAC         string `gorm:"uniqueIndex"`
	IP          string `gorm:"uniqueIndex"`
	Hostname    string
	Description string
	Enabled     bool
}

type DNSZone struct {
	ID   uint   `gorm:"primaryKey"`
	Name string `gorm:"uniqueIndex"`
	Type string // master, slave, forward
}

type DNSRecord struct {
	ID        uint   `gorm:"primaryKey"`
	ZoneID    uint
	Zone      DNSZone
	Name      string `gorm:"index"`
	Type      string // A, CNAME, MX, TXT
	Value     string
	TTL       int
	Enabled   bool
}

type DNSQueryLog struct {
	ID        uint   `gorm:"primaryKey"`
	ClientIP  string
	QueryName string
	QueryType string
	Response  string
	Timestamp int64
}

func InitDB(path string) (*DB, error) {
	db, err := gorm.Open(sqlite.Open(path), &gorm.Config{})
	if err != nil {
		return nil, err
	}

	// Auto migrate
	err = db.AutoMigrate(
		&DHCPLease{},
		&DHCPStaticBinding{},
		&DNSZone{},
		&DNSRecord{},
		&DNSQueryLog{},
	)
	if err != nil {
		return nil, err
	}

	return &DB{db}, nil
}

func (d *DB) GetActiveLeases() ([]DHCPLease, error) {
	var leases []DHCPLease
	err := d.Where("expires_at > ?", time.Now().Unix()).Find(&leases).Error
	return leases, err
}

func (d *DB) GetStaticBindings() ([]DHCPStaticBinding, error) {
	var bindings []DHCPStaticBinding
	err := d.Where("enabled = ?", true).Find(&bindings).Error
	return bindings, err
}

func (d *DB) GetDNSRecords() ([]DNSRecord, error) {
	var records []DNSRecord
	err := d.Where("enabled = ?", true).Preload("Zone").Find(&records).Error
	return records, err
}

func (d *DB) GetDNSZones() ([]DNSZone, error) {
	var zones []DNSZone
	err := d.Find(&zones).Error
	return zones, err
}

func (d *DB) CreateDNSZone(name, zoneType string) error {
	zone := DNSZone{
		Name: name,
		Type: zoneType,
	}
	return d.Create(&zone).Error
}

func (d *DB) DeleteDNSZone(id uint) error {
	return d.Delete(&DNSZone{}, id).Error
}

func (d *DB) AddQueryLog(clientIP, queryName, queryType, response string) error {
	log := DNSQueryLog{
		ClientIP:  clientIP,
		QueryName: queryName,
		QueryType: queryType,
		Response:  response,
		Timestamp: time.Now().Unix(),
	}
	return d.Create(&log).Error
}