// ❌ BAD: Hardcoded credentials
dsn := "duckdb://user:password@host/db"

// ✅ GOOD: Environment variables
dsn := fmt.Sprintf("duckdb://%s:%s@%s/%s", 
    os.Getenv("DB_USER"),
    os.Getenv("DB_PASS"), 
    os.Getenv("DB_HOST"),
    os.Getenv("DB_NAME"))

// ✅ BETTER: Use configuration struct (v0.5.2+)
config := duckdb.Config{
    DSN: os.Getenv("DUCKDB_DSN"),
    DefaultStringSize: 256,
}
db, err := gorm.Open(duckdb.New(config), &gorm.Config{})

// ❌ BAD: Shared memory database in production
db, err := gorm.Open(duckdb.Open(":memory:"), &gorm.Config{})

// ✅ GOOD: Temporary file with proper cleanup
tmpFile, err := os.CreateTemp("", "app_*.db")
if err != nil {
    return err
}
defer os.Remove(tmpFile.Name()) // Clean up

db, err := gorm.Open(duckdb.Open(tmpFile.Name()), &gorm.Config{})

// ✅ BEST: Production configuration with extensions (v0.5.2+)
db, err := gorm.Open(duckdb.OpenWithExtensions("production.db", &duckdb.ExtensionConfig{
    AutoInstall:       true,
    PreloadExtensions: []string{"json", "parquet"}, // Only trusted extensions
    Timeout:           30 * time.Second,
}), &gorm.Config{})

// ✅ GOOD: Use GORM's built-in parameterization
var users []User
db.Where("name = ? AND age > ?", userInput, ageLimit).Find(&users)

// ✅ GOOD: Named parameters
db.Where("name = @name AND age > @age", 
    sql.Named("name", userInput), 
    sql.Named("age", ageLimit)).Find(&users)

// ❌ BAD: String concatenation
query := fmt.Sprintf("SELECT * FROM users WHERE name = '%s'", userInput)
db.Raw(query).Scan(&users)

// ✅ GOOD: Validate array inputs (v0.5.2+ Advanced Types)
func validateStringArray(arr duckdb.StringArray) error {
    if len(arr) > 1000 { // Prevent large arrays
        return errors.New("array too large")
    }
    
    for _, item := range arr {
        if len(item) > 255 {
            return errors.New("string too long")
        }
        if strings.Contains(item, "'") || strings.Contains(item, ";") {
            return errors.New("invalid characters")
        }
        // Additional validation for SQL injection patterns
        if strings.Contains(strings.ToLower(item), "drop ") ||
           strings.Contains(strings.ToLower(item), "delete ") ||
           strings.Contains(strings.ToLower(item), "update ") {
            return errors.New("potentially dangerous SQL keywords")
        }
    }
    return nil
}

// ✅ GOOD: Validate advanced DuckDB types (v0.5.2+)
func validateAdvancedTypes(data interface{}) error {
    switch v := data.(type) {
    case duckdb.JSONType:
        // Validate JSON structure and size
        if len(v.Data) > 1024*1024 { // 1MB limit
            return errors.New("JSON payload too large")
        }
    case duckdb.DecimalType:
        // Validate decimal precision
        if v.Precision > 38 || v.Scale > 38 {
            return errors.New("decimal precision/scale out of range")
        }
    case duckdb.UUIDType:
        // Validate UUID format
        if !isValidUUID(string(v)) {
            return errors.New("invalid UUID format")
        }
    }
    return nil
}

// ✅ GOOD: Restrict file permissions
dbFile := "app.db"
if err := os.Chmod(dbFile, 0600); err != nil { // Owner read/write only
    return fmt.Errorf("failed to set db permissions: %w", err)
}

// ❌ BAD: Loading arbitrary extensions
db.Exec("LOAD '/path/to/unknown/extension.so'")

// ✅ GOOD: Validate extension paths (v0.5.2+ Extension Management)
allowedExtensions := map[string]bool{
    "json":         true,
    "parquet":      true,
    "autocomplete": true,
    "fts":          true,
    "httpfs":       false, // Disable network access in production
    "spatial":      true,
}

func loadExtension(db *gorm.DB, ext string) error {
    allowed, exists := allowedExtensions[ext]
    if !exists || !allowed {
        return fmt.Errorf("extension %s not allowed", ext)
    }
    
    // Use the extension manager for secure loading (v0.5.2+)
    manager, err := duckdb.GetExtensionManager(db)
    if err != nil {
        return fmt.Errorf("failed to get extension manager: %w", err)
    }
    
    return manager.LoadExtension(ext)
}

// ✅ BEST: Use extension helpers (v0.5.2+)
func setupSecureExtensions(db *gorm.DB) error {
    manager, err := duckdb.GetExtensionManager(db)
    if err != nil {
        return err
    }
    
    helper := duckdb.NewExtensionHelper(manager)
    
    // Load only required extension groups
    if err := helper.EnableAnalytics(); err != nil { // json, parquet, fts
        return err
    }
    
    // Skip cloud access in production
    // if err := helper.EnableCloudAccess(); err != nil {
    //     return err
    // }
    
    return nil
}

// ✅ GOOD: Configure connection limits (v0.5.2+ Production Config)
db, err := gorm.Open(duckdb.OpenWithExtensions("production.db", &duckdb.ExtensionConfig{
    AutoInstall:       true,
    PreloadExtensions: []string{"json", "parquet"}, // Only trusted extensions
    Timeout:           30 * time.Second,
}), &gorm.Config{
    Logger: logger.New(
        log.New(os.Stdout, "\r\n", log.LstdFlags),
        logger.Config{
            SlowThreshold:             time.Second,
            LogLevel:                  logger.Warn, // Avoid leaking sensitive data
            IgnoreRecordNotFoundError: true,
            Colorful:                  false, // Production setting
        },
    ),
})

if err != nil {
    return err
}

sqlDB, err := db.DB()
if err != nil {
    return err
}

// DuckDB-optimized security settings
sqlDB.SetMaxOpenConns(25)                   // Prevent connection exhaustion
sqlDB.SetMaxIdleConns(5)                    // Reduce attack surface
sqlDB.SetConnMaxLifetime(5 * time.Minute)   // Force connection refresh
sqlDB.SetConnMaxIdleTime(1 * time.Minute)   // Quick cleanup of idle connections

// ✅ GOOD: Enforce TLS for network connections
config := &tls.Config{
    MinVersion: tls.VersionTLS12,
    CipherSuites: []uint16{
        tls.TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384,
        tls.TLS_ECDHE_RSA_WITH_CHACHA20_POLY1305,
    },
}

// ✅ GOOD: Secure GORM configuration
config := &gorm.Config{
    Logger: logger.New(
        log.New(logOutput, "\r\n", log.LstdFlags),
        logger.Config{
            SlowThreshold:             time.Second,
            LogLevel:                  logger.Warn, // Don't log sensitive data
            IgnoreRecordNotFoundError: true,
            Colorful:                  false,
        },
    ),
    NamingStrategy: schema.NamingStrategy{
        SingularTable: false, // Use consistent naming
    },
    DisableForeignKeyConstraintWhenMigrating: false, // Enforce referential integrity
    PrepareStmt:                              true,  // Use prepared statements
}

// Enhanced error handling with security considerations
db.Callback().Query().Before("gorm:query").Register("security_check", func(db *gorm.DB) {
    // Implement query validation, rate limiting, etc.
    if isRateLimited(db.Statement.Context) {
        db.AddError(errors.New("rate limit exceeded"))
        return
    }
})

// Log security-relevant events (Enhanced for v0.5.2)
func auditQuery(query string, user string, metadata map[string]interface{}) {
    // Sanitize sensitive data before logging
    sanitized := sanitizeForLogging(query)
    
    log.Printf("AUDIT: User %s executed query: %s, metadata: %v", 
        user, sanitized, metadata)
    
    // Enhanced logging for advanced features (v0.5.2+)
    if strings.Contains(strings.ToLower(query), "information_schema") {
        log.Printf("SCHEMA_ACCESS: User %s accessed schema information", user)
    }
    
    if strings.Contains(strings.ToLower(query), "load ") {
        log.Printf("EXTENSION_LOAD: User %s attempted extension loading", user)
    }
}

// Advanced type access logging (v0.5.2+)
func auditAdvancedTypeAccess(typeUsed string, user string, operation string) {
    log.Printf("ADVANCED_TYPE: User %s used %s for %s operation", 
        user, typeUsed, operation)
}

// GORM interface access logging (v0.5.2+)
func auditGORMOperation(operation string, table string, user string) {
    log.Printf("GORM_AUDIT: User %s performed %s on table %s", 
        user, operation, table)
}