OwOrganizer/backend/database.go

package main

import (
	"database/sql"
	"fmt"
	"os"

	_ "github.com/go-sql-driver/mysql"
)

// DB is the shared database connection pool used by the application.
var DB *sql.DB

// InitDB opens a connection to the MariaDB/MySQL database using credentials
// from the environment, creates the database if it does not exist, and ensures
// the required tables are present.
func InitDB() (*sql.DB, error) {
	password := os.Getenv("DB_PASSWORD")
	if password == "" {
		password = "secret"
	}

	host := os.Getenv("DB_HOST")
	if host == "" {
		// Default to the Docker Compose service name.
		host = "db"
	}

	// Build the DSN. The default host "db" matches the service name in docker-compose.yml.
	dsn := fmt.Sprintf("oworganizer:%s@tcp(%s:3306)/?parseTime=true", password, host)

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return nil, fmt.Errorf("failed to open database: %w", err)
	}

	if err := db.Ping(); err != nil {
		return nil, fmt.Errorf("failed to ping database: %w", err)
	}

	// Ensure the application database exists.
	if _, err := db.Exec("CREATE DATABASE IF NOT EXISTS oworganizer CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"); err != nil {
		return nil, fmt.Errorf("failed to create database: %w", err)
	}

	// Switch to the application database.
	if _, err := db.Exec("USE oworganizer"); err != nil {
		return nil, fmt.Errorf("failed to select database: %w", err)
	}

	// Ensure required tables exist.
	if err := createTables(db); err != nil {
		return nil, fmt.Errorf("failed to create tables: %w", err)
	}

	return db, nil
}

// tableExists reports whether a table with the given name exists in the current
// database.
func tableExists(db *sql.DB, name string) (bool, error) {
	var exists bool
	query := `
		SELECT EXISTS (
			SELECT 1
			FROM INFORMATION_SCHEMA.TABLES
			WHERE TABLE_SCHEMA = DATABASE()
			  AND TABLE_NAME = ?
		)
	`
	err := db.QueryRow(query, name).Scan(&exists)
	if err != nil {
		return false, err
	}
	return exists, nil
}

// createTables creates the application's tables if they do not already exist.
func createTables(db *sql.DB) error {
	tables := map[string]string{
		"users": `
			CREATE TABLE IF NOT EXISTS users (
				userId VARCHAR(255) PRIMARY KEY,
				name VARCHAR(255) NOT NULL,
				rootObjectId VARCHAR(255) NOT NULL
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
		`,
		"objects": `
			CREATE TABLE IF NOT EXISTS objects (
				objectId VARCHAR(255) PRIMARY KEY,
				userId VARCHAR(255) NOT NULL,
				objectData TEXT NOT NULL
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
		`,
	}

	for name, ddl := range tables {
		exists, err := tableExists(db, name)
		if err != nil {
			return fmt.Errorf("failed to check %s table: %w", name, err)
		}
		if exists {
			continue
		}

		if _, err := db.Exec(ddl); err != nil {
			return fmt.Errorf("failed to create %s table: %w", name, err)
		}
	}

	return nil
}