// Package db provides database access for the IMC application. // It uses sqlc for type-safe SQL queries. package db import ( "database/sql" "fmt" "time" "git.workaround.org/chaas/imc/backend/internal/config" imcdb "git.workaround.org/chaas/imc/backend/internal/db/sqlc" _ "github.com/go-sql-driver/mysql" ) type DB struct { *imcdb.Queries db *sql.DB } type DomainStats struct { ID uint32 `json:"id"` Name string `json:"name"` UserCount int64 `json:"userCount"` AliasCount int64 `json:"aliasCount"` } type AliasWithDomain struct { imcdb.VirtualAlias DomainName string `json:"domainName"` } func Connect(cfg *config.Config) (*DB, error) { dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?parseTime=true&charset=utf8mb4", cfg.DBUser, cfg.DBPassword, cfg.DBHost, cfg.DBPort, cfg.DBName) mysqlDB, err := sql.Open("mysql", dsn) if err != nil { return nil, fmt.Errorf("failed to open database: %w", err) } mysqlDB.SetMaxOpenConns(25) mysqlDB.SetMaxIdleConns(5) mysqlDB.SetConnMaxLifetime(5 * time.Minute) if err := mysqlDB.Ping(); err != nil { return nil, fmt.Errorf("failed to ping database: %w", err) } return &DB{ Queries: imcdb.New(mysqlDB), db: mysqlDB, }, nil } func (d *DB) InitSchema() error { imcUsersSQL := ` CREATE TABLE IF NOT EXISTS imc_users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(100) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, role ENUM('admin','user') DEFAULT 'user', domain_id INT UNSIGNED NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_username (username), INDEX idx_role (role), INDEX idx_domain_id (domain_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ` if _, err := d.db.Exec(imcUsersSQL); err != nil { return err } loginAttemptsSQL := ` CREATE TABLE IF NOT EXISTS imc_login_attempts ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(100) NOT NULL, ip_address VARCHAR(45) NOT NULL, attempted_at DATETIME DEFAULT CURRENT_TIMESTAMP, successful BOOLEAN DEFAULT FALSE, INDEX idx_username_time (username, attempted_at), INDEX idx_ip_time (ip_address, attempted_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ` if _, err := d.db.Exec(loginAttemptsSQL); err != nil { return err } users2DomainsSQL := ` CREATE TABLE IF NOT EXISTS imc_users2domains ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, domain_id INT UNSIGNED NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY unique_user_domain (user_id, domain_id), INDEX idx_user_id (user_id), INDEX idx_domain_id (domain_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ` if _, err := d.db.Exec(users2DomainsSQL); err != nil { return err } return nil } func (d *DB) Close() error { return d.db.Close() }