web-giao-ban/db/db.go

package db

import (
	"database/sql"
	"fmt"
	"regexp"
	"strings"
	"sync"
	"time"

	_ "github.com/lib/pq"
	"web-giao-ban-go/config"
	"web-giao-ban-go/logger"
)

var (
	poolMu sync.RWMutex
	pools  = make(map[string]*sql.DB)
)

func GetConnectionString(cfg *config.AppConfig, dbName string) string {
	if dbName == "" {
		dbName = cfg.Database.DefaultDB
	}
	pass := cfg.DecryptedDBPassword
	if pass == "" {
		pass, _ = config.Decrypt(cfg.Database.Password)
	}

	return fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable connect_timeout=10 statement_timeout=15000",
		cfg.Database.Host,
		cfg.Database.Port,
		cfg.Database.User,
		pass,
		dbName,
	)
}

func GetDB(dbName string) (*sql.DB, error) {
	cfg := config.Get()
	if dbName == "" {
		dbName = cfg.Database.DefaultDB
	}

	poolMu.RLock()
	if conn, ok := pools[dbName]; ok {
		poolMu.RUnlock()
		return conn, nil
	}
	poolMu.RUnlock()

	poolMu.Lock()
	defer poolMu.Unlock()

	// Double check
	if conn, ok := pools[dbName]; ok {
		return conn, nil
	}

	connStr := GetConnectionString(cfg, dbName)
	conn, err := sql.Open("postgres", connStr)
	if err != nil {
		logger.Error("Lỗi kết nối PostgreSQL [%s]: %v", dbName, err)
		return nil, err
	}

	conn.SetMaxOpenConns(15)
	conn.SetMaxIdleConns(5)
	conn.SetConnMaxLifetime(15 * time.Minute)

	if err := conn.Ping(); err != nil {
		logger.Error("Ping thất bại tới CSDL [%s]: %v", dbName, err)
		return nil, err
	}

	pools[dbName] = conn
	logger.Info("Khởi tạo connection pool tới CSDL: %s", dbName)
	return conn, nil
}

func CloseAll() {
	poolMu.Lock()
	defer poolMu.Unlock()
	for name, conn := range pools {
		_ = conn.Close()
		delete(pools, name)
	}
}

func TestConnection(host string, port int, user, pass, dbName string) error {
	connStr := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable connect_timeout=5",
		host, port, user, pass, dbName)
	testConn, err := sql.Open("postgres", connStr)
	if err != nil {
		return err
	}
	defer testConn.Close()
	return testConn.Ping()
}

func EnsureTable(dbName string) {
	conn, err := GetDB(dbName)
	if err != nil {
		return
	}

	safeName := regexp.MustCompile(`[^a-zA-Z0-9]`).ReplaceAllString(dbName, "_")
	sqlCreate := fmt.Sprintf(`
		CREATE TABLE IF NOT EXISTS current.giaoban_ghichu (
			id SERIAL PRIMARY KEY,
			maba VARCHAR(50) NOT NULL,
			mabn VARCHAR(20) NOT NULL,
			loai_danhsach VARCHAR(20) NOT NULL,
			ghichu TEXT,
			ngay_capnhat TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
			CONSTRAINT unq_giaoban_item_%s UNIQUE(maba, loai_danhsach)
		);
	`, safeName)

	_, _ = conn.Exec(sqlCreate)
}

func CleanRTF(s string) string {
	if s == "" {
		return ""
	}
	if !strings.HasPrefix(s, "{\\rtf") && !strings.Contains(s, "\\rtf") {
		return s
	}

	cleaned := s
	// Remove {\*...} blocks
	re1 := regexp.MustCompile(`\{\\\*[^}]*\}`)
	cleaned = re1.ReplaceAllString(cleaned, "")

	// Remove general {...} groups with tags
	re2 := regexp.MustCompile(`\{[^{}]*\}`)
	cleaned = re2.ReplaceAllString(cleaned, "")

	// Remove RTF control words (\b, \par, \fs20...)
	re3 := regexp.MustCompile(`\\[a-zA-Z]+(-?\d+)? ?`)
	cleaned = re3.ReplaceAllString(cleaned, " ")

	// Remove stray brackets and backslashes
	re4 := regexp.MustCompile(`[\\{}]`)
	cleaned = re4.ReplaceAllString(cleaned, "")

	// Collapse multiple spaces
	re5 := regexp.MustCompile(`\s+`)
	cleaned = strings.TrimSpace(re5.ReplaceAllString(cleaned, " "))

	if cleaned == "" {
		return "Có kết quả (Định dạng RTF)"
	}
	return cleaned
}