OrchestrAD/backend/internal/repository/credentials.go

// Package repository - Credentials repository
package repository

import (
	"database/sql"
	"time"

	"github.com/Grace-Solutions/OrchestrAD/internal/models"
	"github.com/google/uuid"
)

// CredentialRepository handles credential database operations
type CredentialRepository struct {
	db *sql.DB
}

// NewCredentialRepository creates a new CredentialRepository
func NewCredentialRepository(db *sql.DB) *CredentialRepository {
	return &CredentialRepository{db: db}
}

// Create creates a new credential
func (r *CredentialRepository) Create(cred *models.Credential) error {
	if cred.ID == "" {
		cred.ID = uuid.New().String()
	}
	now := time.Now().UTC()
	cred.CreatedUTC = now
	cred.UpdatedUTC = now

	_, err := r.db.Exec(`
		INSERT INTO credentials (
			id, name, description, credential_type, username,
			encrypted_secret, is_enabled, created_utc, updated_utc
		) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
	`,
		cred.ID, cred.Name, cred.Description, cred.CredentialType,
		cred.Username, cred.EncryptedSecret, boolToInt(cred.IsEnabled),
		formatTime(cred.CreatedUTC), formatTime(cred.UpdatedUTC),
	)
	return err
}

// GetByID retrieves a credential by ID
func (r *CredentialRepository) GetByID(id string) (*models.Credential, error) {
	cred := &models.Credential{}
	var isEnabled int
	var lastTested, deleted sql.NullString
	var createdStr, updatedStr string

	err := r.db.QueryRow(`
		SELECT id, name, description, credential_type, username,
			encrypted_secret, is_enabled, last_tested_utc, last_test_result,
			created_utc, updated_utc, deleted_utc
		FROM credentials WHERE id = ? AND deleted_utc IS NULL
	`, id).Scan(
		&cred.ID, &cred.Name, &cred.Description, &cred.CredentialType,
		&cred.Username, &cred.EncryptedSecret, &isEnabled,
		&lastTested, &cred.LastTestResult,
		&createdStr, &updatedStr, &deleted,
	)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}

	cred.IsEnabled = intToBool(isEnabled)
	cred.LastTestedUTC = parseNullTime(lastTested)
	cred.CreatedUTC = parseTimeOrZero(createdStr)
	cred.UpdatedUTC = parseTimeOrZero(updatedStr)
	cred.DeletedUTC = parseNullTime(deleted)
	return cred, nil
}

// List retrieves all credentials
func (r *CredentialRepository) List(offset, limit int) ([]models.Credential, int, error) {
	var total int
	err := r.db.QueryRow(`SELECT COUNT(*) FROM credentials WHERE deleted_utc IS NULL`).Scan(&total)
	if err != nil {
		return nil, 0, err
	}

	rows, err := r.db.Query(`
		SELECT id, name, description, credential_type, username,
			is_enabled, last_tested_utc, last_test_result,
			created_utc, updated_utc
		FROM credentials 
		WHERE deleted_utc IS NULL
		ORDER BY created_utc DESC
		LIMIT ? OFFSET ?
	`, limit, offset)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()

	var creds []models.Credential
	for rows.Next() {
		var cred models.Credential
		var isEnabled int
		var lastTested sql.NullString
		var createdStr, updatedStr string
		if err := rows.Scan(
			&cred.ID, &cred.Name, &cred.Description, &cred.CredentialType,
			&cred.Username, &isEnabled, &lastTested, &cred.LastTestResult,
			&createdStr, &updatedStr,
		); err != nil {
			return nil, 0, err
		}
		cred.IsEnabled = intToBool(isEnabled)
		cred.LastTestedUTC = parseNullTime(lastTested)
		cred.CreatedUTC = parseTimeOrZero(createdStr)
		cred.UpdatedUTC = parseTimeOrZero(updatedStr)
		creds = append(creds, cred)
	}

	return creds, total, rows.Err()
}

// Update updates a credential
func (r *CredentialRepository) Update(cred *models.Credential) error {
	cred.UpdatedUTC = time.Now().UTC()
	_, err := r.db.Exec(`
		UPDATE credentials SET
			name = ?, description = ?, credential_type = ?,
			username = ?, encrypted_secret = ?, is_enabled = ?,
			updated_utc = ?
		WHERE id = ?
	`,
		cred.Name, cred.Description, cred.CredentialType,
		cred.Username, cred.EncryptedSecret, boolToInt(cred.IsEnabled),
		formatTime(cred.UpdatedUTC), cred.ID,
	)
	return err
}

// UpdateTestResult updates the last test result
func (r *CredentialRepository) UpdateTestResult(id string, result string) error {
	now := time.Now().UTC()
	_, err := r.db.Exec(`
		UPDATE credentials SET 
			last_tested_utc = ?, last_test_result = ?, updated_utc = ?
		WHERE id = ?
	`, formatTime(now), result, formatTime(now), id)
	return err
}

// SoftDelete marks a credential as deleted
func (r *CredentialRepository) SoftDelete(id string) error {
	now := time.Now().UTC()
	_, err := r.db.Exec(`
		UPDATE credentials SET deleted_utc = ?, updated_utc = ?
		WHERE id = ?
	`, formatTime(now), formatTime(now), id)
	return err
}

// CheckUsage checks if a credential is in use
func (r *CredentialRepository) CheckUsage(id string) (bool, error) {
	var count int
	err := r.db.QueryRow(`
		SELECT COUNT(*) FROM ad_connections 
		WHERE credential_id = ? AND deleted_utc IS NULL
	`, id).Scan(&count)
	return count > 0, err
}

// UpdateEnabled toggles the is_enabled flag.
func (r *CredentialRepository) UpdateEnabled(id string, enabled bool) error {
	now := time.Now().UTC()
	_, err := r.db.Exec(`
UPDATE credentials SET is_enabled = ?, updated_utc = ? WHERE id = ? AND deleted_utc IS NULL
`, boolToInt(enabled), formatTime(now), id)
	return err
}