backend/internal/database/database.go

package database

import (
	"fmt"
	"log"
	"os"
	"path/filepath"
	"time"

	"restaurant-pos/internal/models"

	"github.com/glebarez/sqlite"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

var DB *gorm.DB

func Initialize(dbPath string) (*gorm.DB, error) {
	// Ensure directory exists
	dir := filepath.Dir(dbPath)
	if err := os.MkdirAll(dir, 0755); err != nil {
		return nil, fmt.Errorf("failed to create database directory: %w", err)
	}

	newLogger := logger.New(
		log.New(os.Stdout, "\r\n", log.LstdFlags),
		logger.Config{
			SlowThreshold:             200 * time.Millisecond,
			LogLevel:                  logger.Warn,
			IgnoreRecordNotFoundError: true,
			Colorful:                  true,
		},
	)

	db, err := gorm.Open(sqlite.Open(dbPath+"?_journal_mode=WAL&_busy_timeout=5000&_foreign_keys=ON"), &gorm.Config{
		Logger:                 newLogger,
		SkipDefaultTransaction: true, // Performance: skip wrapping single queries in transactions
	})
	if err != nil {
		return nil, fmt.Errorf("failed to connect to database: %w", err)
	}

	sqlDB, err := db.DB()
	if err != nil {
		return nil, fmt.Errorf("failed to get underlying sql.DB: %w", err)
	}

	// SQLite connection pool settings
	sqlDB.SetMaxOpenConns(1) // SQLite only supports one writer
	sqlDB.SetMaxIdleConns(1)
	sqlDB.SetConnMaxLifetime(time.Hour)

	// Enable WAL mode for better concurrent read performance
	db.Exec("PRAGMA journal_mode=WAL")
	db.Exec("PRAGMA synchronous=NORMAL")
	db.Exec("PRAGMA cache_size=-64000") // 64MB cache
	db.Exec("PRAGMA temp_store=MEMORY")
	db.Exec("PRAGMA mmap_size=268435456") // 256MB mmap

	DB = db

	// Run migrations
	if err := runMigrations(db); err != nil {
		return nil, fmt.Errorf("migration failed: %w", err)
	}

	// Create indexes
	createIndexes(db)

	log.Println("[DB] SQLite database initialized successfully at", dbPath)
	return db, nil
}

func runMigrations(db *gorm.DB) error {
	log.Println("[DB] Running auto-migrations...")
	return db.AutoMigrate(models.AllModels()...)
}

func createIndexes(db *gorm.DB) {
	// Order indexes for fast lookups
	db.Exec("CREATE INDEX IF NOT EXISTS idx_orders_source ON orders(source)")
	db.Exec("CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status)")
	db.Exec("CREATE INDEX IF NOT EXISTS idx_orders_created_at ON orders(created_at)")
	db.Exec("CREATE INDEX IF NOT EXISTS idx_orders_payment_status ON orders(payment_status)")
	db.Exec("CREATE INDEX IF NOT EXISTS idx_orders_table_id ON orders(table_id)")
	db.Exec("CREATE INDEX IF NOT EXISTS idx_orders_customer_id ON orders(customer_id)")

	// KOT indexes
	db.Exec("CREATE INDEX IF NOT EXISTS idx_kots_order_id ON kots(order_id)")
	db.Exec("CREATE INDEX IF NOT EXISTS idx_kots_status ON kots(status)")
	db.Exec("CREATE INDEX IF NOT EXISTS idx_kots_kitchen_station ON kots(kitchen_station)")
	db.Exec("CREATE INDEX IF NOT EXISTS idx_kots_created_at ON kots(created_at)")

	// Inventory
	db.Exec("CREATE INDEX IF NOT EXISTS idx_stock_movements_item ON stock_movements(inventory_item_id)")
	db.Exec("CREATE INDEX IF NOT EXISTS idx_stock_movements_type ON stock_movements(type)")
	db.Exec("CREATE INDEX IF NOT EXISTS idx_stock_movements_date ON stock_movements(created_at)")

	// Audit
	db.Exec("CREATE INDEX IF NOT EXISTS idx_audit_logs_user ON audit_logs(user_id)")
	db.Exec("CREATE INDEX IF NOT EXISTS idx_audit_logs_module ON audit_logs(module)")
	db.Exec("CREATE INDEX IF NOT EXISTS idx_audit_logs_date ON audit_logs(created_at)")

	// Products
	db.Exec("CREATE INDEX IF NOT EXISTS idx_products_category ON products(category_id)")
	db.Exec("CREATE INDEX IF NOT EXISTS idx_products_station ON products(kitchen_station)")
	db.Exec("CREATE INDEX IF NOT EXISTS idx_products_active ON products(is_active, is_available)")

	// Payments
	db.Exec("CREATE INDEX IF NOT EXISTS idx_payments_order ON payments(order_id)")
	db.Exec("CREATE INDEX IF NOT EXISTS idx_payments_method ON payments(method)")

	// Expenses
	db.Exec("CREATE INDEX IF NOT EXISTS idx_expenses_category ON expenses(category)")
	db.Exec("CREATE INDEX IF NOT EXISTS idx_expenses_date ON expenses(date)")

	log.Println("[DB] Indexes created successfully")
}