byop-engine/dbstore/store.go

package dbstore

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

	"git.linuxforward.com/byop/byop-engine/models"
	_ "github.com/mattn/go-sqlite3" // Import SQLite driver
)

// Store defines the interface for all database operations.
// This will include methods for all models (User, Client, App, Component, Deployment, Ticket, etc.)
type Store interface {
	// User methods
	CreateUser(ctx context.Context, user models.User) (int, error) // Updated signature
	GetUserByEmail(ctx context.Context, email string) (*models.User, error)
	GetUserByID(ctx context.Context, id int) (*models.User, error)
	GetUsers(ctx context.Context) ([]models.User, error) // Method to get all users
	UpdateUser(ctx context.Context, user *models.User) error
	DeleteUser(ctx context.Context, id int64) error // Updated signature
	// ... other user methods

	// Client methods
	CreateClient(ctx context.Context, client models.Client) (int, error) // Corrected signature
	GetClientByID(ctx context.Context, id int) (*models.Client, error)   // Changed to pointer
	GetClients(ctx context.Context) ([]models.Client, error)
	UpdateClient(ctx context.Context, client *models.Client) error // Changed to pointer
	DeleteClient(ctx context.Context, id int) error
	// ... other client methods

	// App methods
	CreateApp(ctx context.Context, app *models.App) (int, error) // Corrected signature
	GetAppByID(ctx context.Context, id int) (*models.App, error)
	GetAppsByUserID(ctx context.Context, userID int) ([]models.App, error) // Added method
	UpdateApp(ctx context.Context, app *models.App) error
	DeleteApp(ctx context.Context, id int) error
	UpdateAppStatus(ctx context.Context, appID int, status string, message string) error          // Added, changed models.AppStatus to string
	UpdateAppPreview(ctx context.Context, appID int, previewID int, previewURL string) error      // Added
	GetAllApps(ctx context.Context) ([]*models.App, error)                                        // Updated signature
	UpdateAppCurrentImage(ctx context.Context, appID int, imageTag string, imageURI string) error // Added
	// ... other app methods

	// Component methods
	CreateComponent(ctx context.Context, component *models.Component) (int, error) // Updated signature
	GetComponentByID(ctx context.Context, id int) (*models.Component, error)
	GetComponentsByUserID(ctx context.Context, userID int) ([]models.Component, error)
	UpdateComponent(ctx context.Context, component *models.Component) error
	DeleteComponent(ctx context.Context, id int) error
	// ... other component methods

	// Deployment methods
	CreateDeployment(ctx context.Context, deployment models.Deployment) (int, error) // Updated signature
	GetDeploymentByID(ctx context.Context, id int) (*models.Deployment, error)
	GetDeploymentsByAppID(ctx context.Context, appID int) ([]models.Deployment, error)
	GetDeploymentsByClientID(ctx context.Context, clientID int) ([]models.Deployment, error)
	GetDeploymentsByUserID(ctx context.Context, userID int) ([]models.Deployment, error) // Assuming deployments can be linked to users indirectly
	UpdateDeployment(ctx context.Context, deployment *models.Deployment) error
	DeleteDeployment(ctx context.Context, id int) error
	// ... other deployment methods

	// Preview methods
	CreatePreview(ctx context.Context, preview *models.Preview) (int, error) // Corrected signature
	GetPreviewByID(ctx context.Context, id int) (*models.Preview, error)
	GetPreviewByAppID(ctx context.Context, appID int) (*models.Preview, error)
	UpdatePreview(ctx context.Context, preview *models.Preview) error
	DeletePreview(ctx context.Context, id int) error
	UpdatePreviewVPS(ctx context.Context, previewID int, vpsID string, ipAddress string, previewURL string) error // Added
	UpdatePreviewStatus(ctx context.Context, previewID int, status string, errorMsg string) error                 // Added, changed models.PreviewStatus to string
	UpdatePreviewBuildLogs(ctx context.Context, previewID int, logs string) error                                 // Added
	UpdatePreviewDeployLogs(ctx context.Context, previewID int, logs string) error                                // Added
	GetPreviewsByStatus(ctx context.Context, status string) ([]models.Preview, error)                             // Added, changed models.PreviewStatus to string
	GetPreviewsByAppID(ctx context.Context, appID int) ([]models.Preview, error)                                  // Added
	// ... other preview methods

	// Ticket methods
	CreateTicket(ctx context.Context, ticket *models.Ticket) error
	GetTicketByID(ctx context.Context, id int) (*models.Ticket, error)
	GetTickets(ctx context.Context) ([]models.Ticket, error) // Add filters later (status, user, client)
	UpdateTicket(ctx context.Context, ticket *models.Ticket) error
	// DeleteTicket(ctx context.Context, id int) error // Optional

	// TicketComment methods
	CreateTicketComment(ctx context.Context, comment *models.TicketComment) error
	GetTicketComments(ctx context.Context, ticketID int) ([]models.TicketComment, error)
	// ... other ticket comment methods

	// BuildJob methods
	CreateBuildJob(ctx context.Context, job *models.BuildJob) error
	GetBuildJobByID(ctx context.Context, id uint) (*models.BuildJob, error)
	UpdateBuildJob(ctx context.Context, job *models.BuildJob) error
	UpdateBuildJobStatus(ctx context.Context, id uint, status models.BuildStatus, errorMessage string) error
	AppendBuildJobLog(ctx context.Context, id uint, logMessage string) error
	GetQueuedBuildJobs(ctx context.Context, limit int) ([]models.BuildJob, error)
	GetBuildJobsByAppID(ctx context.Context, appID uint, page, pageSize int) ([]models.BuildJob, int64, error)

	// General DB methods
	GetDB() *sql.DB
	Close() error
}

// SQLiteStore implements the Store interface for SQLite using GORM
type SQLiteStore struct {
	db  *sql.DB
	dsn string
}

// NewSQLiteStore initializes a new SQLiteStore
func NewSQLiteStore(dataSourceName string) (*SQLiteStore, error) {
	// First check if the database file exists
	isNewDb := !fileExists(dataSourceName)
	if isNewDb {
		// Create the database file if it doesn't exist
		file, err := os.Create(dataSourceName)
		if err != nil {
			return nil, fmt.Errorf("failed to create SQLite database file: %w", err)
		}
		defer file.Close()
	}

	// Open SQLite database and SQLite-specific configuration
	db, err := sql.Open("sqlite3", dataSourceName)
	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)
	}

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

	// Run migrations after table creation
	err = runMigrations(db)
	if err != nil {
		return nil, fmt.Errorf("failed to run migrations: %w", err)
	}
	// Enable foreign keys in SQLite after migrations
	_, err = db.Exec("PRAGMA foreign_keys = ON")
	if err != nil {
		return nil, fmt.Errorf("failed to enable foreign keys: %w", err)
	}

	// Check if the database is well-formed
	if isNewDb {
		// If this is a new database, we can assume it's well-formed after creating tables
	} else {
		// If the database already exists, we can run a simple query to check its integrity
		var count int
		err = db.QueryRow("SELECT COUNT(*) FROM sqlite_master WHERE type='table'").Scan(&count)
		if err != nil {
			return nil, fmt.Errorf("failed to check database integrity: %w", err)
		}
		if count == 0 {
			return nil, fmt.Errorf("database is empty or not well-formed")
		}
	}

	return &SQLiteStore{
		db:  db,
		dsn: dataSourceName,
	}, nil
}

// createTables creates all necessary tables
func createTables(db *sql.DB) error {
	queries := []string{
		`CREATE TABLE IF NOT EXISTS users (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			email TEXT UNIQUE NOT NULL,
			password TEXT NOT NULL,
			name TEXT NOT NULL,
			role TEXT DEFAULT 'user',
			active BOOLEAN DEFAULT true,
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
		)`,
		`CREATE TABLE IF NOT EXISTS clients (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			name TEXT NOT NULL,
			description TEXT,
			contact_info TEXT,
			active BOOLEAN DEFAULT true,
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
		)`,
		`CREATE TABLE IF NOT EXISTS components (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			user_id INTEGER NOT NULL,
			name TEXT NOT NULL,
			description TEXT,
			type TEXT,
			status TEXT DEFAULT 'active',
			config TEXT DEFAULT '{}',
			repository TEXT,
			branch TEXT DEFAULT 'main',
			error_msg TEXT, 
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			FOREIGN KEY (user_id) REFERENCES users(id)
		)`,
		`CREATE TABLE IF NOT EXISTS deployments (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			app_id INTEGER NOT NULL,
			client_id INTEGER NOT NULL,
			name TEXT NOT NULL,
			description TEXT,
			environment TEXT DEFAULT 'development',
			status TEXT DEFAULT 'pending',
			url TEXT,
			config TEXT DEFAULT '{}',
			deployed_at DATETIME,
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			FOREIGN KEY (app_id) REFERENCES apps(id),
			FOREIGN KEY (client_id) REFERENCES clients(id)
		)`,
		`CREATE TABLE IF NOT EXISTS apps (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			user_id INTEGER NOT NULL,
			name TEXT NOT NULL,
			description TEXT,
			status TEXT DEFAULT 'building',
			components TEXT DEFAULT '[]', -- JSON array of component IDs
			preview_id INTEGER,
			preview_url TEXT DEFAULT '',
			current_image_tag TEXT DEFAULT '',
			current_image_uri TEXT DEFAULT '',
			error_msg TEXT DEFAULT '',
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			FOREIGN KEY (user_id) REFERENCES users(id),
			FOREIGN KEY (preview_id) REFERENCES previews(id) ON DELETE SET NULL
		)`,
		`CREATE TABLE IF NOT EXISTS providers (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			name TEXT NOT NULL,
			type TEXT NOT NULL,
			config TEXT DEFAULT '{}',
			active BOOLEAN DEFAULT true,
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
		)`,
		`CREATE TABLE IF NOT EXISTS tickets (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			client_id INTEGER NOT NULL,
			title TEXT NOT NULL,
			description TEXT,
			status TEXT DEFAULT 'open',
			priority TEXT DEFAULT 'medium',
			assigned_to INTEGER,
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			FOREIGN KEY (client_id) REFERENCES clients(id),
			FOREIGN KEY (assigned_to) REFERENCES users(id)
		)`,
		`CREATE TABLE IF NOT EXISTS previews (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			app_id INTEGER NOT NULL,
			status TEXT NOT NULL DEFAULT 'building',
			url TEXT DEFAULT '',
			vps_id TEXT DEFAULT '',
			ip_address TEXT DEFAULT '',
			error_msg TEXT DEFAULT '',
			build_logs TEXT DEFAULT '',
			deploy_logs TEXT DEFAULT '',
			expires_at TEXT NOT NULL,
			created_at TEXT DEFAULT CURRENT_TIMESTAMP,
			updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
			FOREIGN KEY (app_id) REFERENCES apps(id) ON DELETE CASCADE
		);`,
		`CREATE TABLE IF NOT EXISTS ticket_comments (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			ticket_id INTEGER NOT NULL,
			user_id INTEGER NOT NULL,
			content TEXT NOT NULL,
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
			FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
		)`,
		`CREATE TABLE IF NOT EXISTS build_jobs (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			component_id INTEGER NOT NULL,
			request_id TEXT UNIQUE,
			source_url TEXT NOT NULL,
			version TEXT,
			status TEXT NOT NULL,
			image_name TEXT,
			image_tag TEXT,
			full_image_uri TEXT,
			registry_url TEXT,
			registry_user TEXT,
			registry_password TEXT,
			build_context TEXT,
			dockerfile TEXT,
			llb_definition BLOB,
			dockerfile_content TEXT,
			no_cache BOOLEAN,
			build_args TEXT,
			logs TEXT,
			error_message TEXT,
			requested_at DATETIME NOT NULL,
			started_at DATETIME,
			finished_at DATETIME,
			worker_node_id TEXT,
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			FOREIGN KEY (component_id) REFERENCES components(id) ON DELETE CASCADE
		)`,
	}

	for _, query := range queries {
		if _, err := db.Exec(query); err != nil {
			return fmt.Errorf("failed to create table: %w", err)
		}
	}

	return nil
}

// runMigrations handles database schema migrations for existing databases
func runMigrations(db *sql.DB) error {
	// Migration 1: Add image tracking columns to components table
	err := addComponentImageColumns(db)
	if err != nil {
		return fmt.Errorf("failed to add image columns to components table: %w", err)
	}

	// Migration 2: Add dockerfile_content column to build_jobs table
	err = addDockerfileContentColumn(db)
	if err != nil {
		return fmt.Errorf("failed to add dockerfile_content column to build_jobs table: %w", err)
	}

	return nil
}

// addComponentImageColumns adds current_image_tag and current_image_uri columns to components table
func addComponentImageColumns(db *sql.DB) error {
	// Check if columns already exist
	var count int
	err := db.QueryRow(`
		SELECT COUNT(*) 
		FROM pragma_table_info('components') 
		WHERE name IN ('current_image_tag', 'current_image_uri')
	`).Scan(&count)
	if err != nil {
		return fmt.Errorf("failed to check existing columns: %w", err)
	}

	// If both columns already exist, skip migration
	if count >= 2 {
		return nil
	}

	// Add the missing columns
	migrations := []string{
		`ALTER TABLE components ADD COLUMN current_image_tag TEXT DEFAULT ''`,
		`ALTER TABLE components ADD COLUMN current_image_uri TEXT DEFAULT ''`,
	}

	for _, migration := range migrations {
		_, err := db.Exec(migration)
		if err != nil {
			// Ignore "duplicate column name" errors in case the column already exists
			if err.Error() != "duplicate column name: current_image_tag" &&
				err.Error() != "duplicate column name: current_image_uri" {
				return fmt.Errorf("failed to execute migration '%s': %w", migration, err)
			}
		}
	}

	return nil
}

// addDockerfileContentColumn adds dockerfile_content column to build_jobs table
func addDockerfileContentColumn(db *sql.DB) error {
	// Check if column already exists
	var count int
	err := db.QueryRow(`
		SELECT COUNT(*) 
		FROM pragma_table_info('build_jobs') 
		WHERE name = 'dockerfile_content'
	`).Scan(&count)
	if err != nil {
		return fmt.Errorf("failed to check dockerfile_content column: %w", err)
	}

	// If column already exists, skip migration
	if count > 0 {
		return nil
	}

	// Add the missing column
	_, err = db.Exec(`ALTER TABLE build_jobs ADD COLUMN dockerfile_content TEXT`)
	if err != nil {
		// Ignore "duplicate column name" errors in case the column already exists
		if err.Error() != "duplicate column name: dockerfile_content" {
			return fmt.Errorf("failed to add dockerfile_content column: %w", err)
		}
	}

	return nil
}

// fileExists checks if a file exists
func fileExists(filename string) bool {
	_, err := os.Stat(filename)
	return !os.IsNotExist(err)
}

// GetDB returns the GORM database instance
func (m *SQLiteStore) GetDB() *sql.DB {
	return m.db
}

// Connect establishes a connection to the SQLite database
func (m *SQLiteStore) Connect() error {
	// Connection is already established in NewSQLiteStore
	return nil
}

// Disconnect closes the connection to the SQLite database
func (m *SQLiteStore) Disconnect() error {
	return m.db.Close()
}

// Close provides a more standard name for closing the database connection.
// It simply calls Disconnect.
func (m *SQLiteStore) Close() error {
	return m.Disconnect()
}