codit/backend/internal/db/principals.go

package db

import "database/sql"
import "strings"
import "time"

import "codit/internal/models"
import "codit/internal/util"

func (s *Store) ListServicePrincipals() ([]models.ServicePrincipal, error) {
	var rows *sql.Rows
	var items []models.ServicePrincipal
	var item models.ServicePrincipal
	var err error
	rows, err = s.DB.Query(`SELECT public_id, name, description, is_admin, disabled, created_at, updated_at FROM service_principals ORDER BY name`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(&item.ID, &item.Name, &item.Description, &item.IsAdmin, &item.Disabled, &item.CreatedAt, &item.UpdatedAt)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	err = rows.Err()
	if err != nil {
		return nil, err
	}
	return items, nil
}

func (s *Store) GetServicePrincipal(id string) (models.ServicePrincipal, error) {
	var row *sql.Row
	var item models.ServicePrincipal
	var err error
	row = s.DB.QueryRow(`SELECT public_id, name, description, is_admin, disabled, created_at, updated_at FROM service_principals WHERE public_id = ?`, id)
	err = row.Scan(&item.ID, &item.Name, &item.Description, &item.IsAdmin, &item.Disabled, &item.CreatedAt, &item.UpdatedAt)
	return item, err
}

func (s *Store) CreateServicePrincipal(item models.ServicePrincipal) (models.ServicePrincipal, error) {
	var id string
	var now int64
	var err error
	if item.ID == "" {
		id, err = util.NewID()
		if err != nil {
			return item, err
		}
		item.ID = id
	}
	now = time.Now().UTC().Unix()
	item.CreatedAt = now
	item.UpdatedAt = now
	_, err = s.DB.Exec(`INSERT INTO service_principals (public_id, name, description, is_admin, disabled, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?)`,
		item.ID, item.Name, item.Description, item.IsAdmin, item.Disabled, item.CreatedAt, item.UpdatedAt)
	if err != nil {
		return item, err
	}
	return item, nil
}

func (s *Store) UpdateServicePrincipal(item models.ServicePrincipal) error {
	var now int64
	var err error
	now = time.Now().UTC().Unix()
	item.UpdatedAt = now
	_, err = s.DB.Exec(`UPDATE service_principals SET name = ?, description = ?, is_admin = ?, disabled = ?, updated_at = ? WHERE public_id = ?`,
		item.Name, item.Description, item.IsAdmin, item.Disabled, item.UpdatedAt, item.ID)
	return err
}

func (s *Store) DeleteServicePrincipal(id string) error {
	var err error
	_, err = s.DB.Exec(`DELETE FROM service_principals WHERE public_id = ?`, id)
	return err
}

func (s *Store) ListCertPrincipalBindings() ([]models.CertPrincipalBinding, error) {
	var rows *sql.Rows
	var items []models.CertPrincipalBinding
	var item models.CertPrincipalBinding
	var err error
	rows, err = s.DB.Query(`SELECT b.fingerprint, p.public_id, b.enabled, b.created_at, b.updated_at
		FROM cert_principal_bindings b
		JOIN service_principals p ON p.id = b.principal_id
		ORDER BY b.fingerprint`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(&item.Fingerprint, &item.PrincipalID, &item.Enabled, &item.CreatedAt, &item.UpdatedAt)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	err = rows.Err()
	if err != nil {
		return nil, err
	}
	return items, nil
}

func (s *Store) UpsertCertPrincipalBinding(item models.CertPrincipalBinding) (models.CertPrincipalBinding, error) {
	var now int64
	var err error
	now = time.Now().UTC().Unix()
	item.Fingerprint = strings.ToLower(strings.TrimSpace(item.Fingerprint))
	item.UpdatedAt = now
	_, err = s.DB.Exec(`INSERT INTO cert_principal_bindings (fingerprint, principal_id, enabled, created_at, updated_at)
		VALUES (?, (SELECT id FROM service_principals WHERE public_id = ?), ?, ?, ?)
		ON CONFLICT(fingerprint) DO UPDATE SET principal_id = excluded.principal_id, enabled = excluded.enabled, updated_at = excluded.updated_at`,
		item.Fingerprint, item.PrincipalID, item.Enabled, now, now)
	if err != nil {
		return item, err
	}
	item.CreatedAt = now
	return item, nil
}

func (s *Store) DeleteCertPrincipalBinding(fingerprint string) error {
	var err error
	fingerprint = strings.ToLower(strings.TrimSpace(fingerprint))
	_, err = s.DB.Exec(`DELETE FROM cert_principal_bindings WHERE fingerprint = ?`, fingerprint)
	return err
}

func (s *Store) GetPrincipalByCertFingerprint(fingerprint string) (models.ServicePrincipal, bool, error) {
	var row *sql.Row
	var item models.ServicePrincipal
	var enabled bool
	var err error
	fingerprint = strings.ToLower(strings.TrimSpace(fingerprint))
	row = s.DB.QueryRow(`SELECT p.public_id, p.name, p.description, p.is_admin, p.disabled, p.created_at, p.updated_at, b.enabled
		FROM cert_principal_bindings b
		INNER JOIN service_principals p ON p.id = b.principal_id
		WHERE b.fingerprint = ?`, fingerprint)
	err = row.Scan(&item.ID, &item.Name, &item.Description, &item.IsAdmin, &item.Disabled, &item.CreatedAt, &item.UpdatedAt, &enabled)
	if err != nil {
		if err == sql.ErrNoRows {
			return item, false, nil
		}
		return item, false, err
	}
	if item.Disabled || !enabled {
		return item, false, nil
	}
	return item, true, nil
}

func (s *Store) ListPrincipalProjectRoles(principalID string) ([]models.PrincipalProjectRole, error) {
	var rows *sql.Rows
	var items []models.PrincipalProjectRole
	var item models.PrincipalProjectRole
	var err error
	rows, err = s.DB.Query(`SELECT sp.public_id, p.public_id, r.role, r.created_at
		FROM principal_project_roles r
		JOIN service_principals sp ON sp.id = r.principal_id
		JOIN projects p ON p.id = r.project_id
		WHERE r.principal_id = (SELECT id FROM service_principals WHERE public_id = ?)
		ORDER BY p.public_id`, principalID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(&item.PrincipalID, &item.ProjectID, &item.Role, &item.CreatedAt)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	err = rows.Err()
	if err != nil {
		return nil, err
	}
	return items, nil
}

func (s *Store) UpsertPrincipalProjectRole(item models.PrincipalProjectRole) (models.PrincipalProjectRole, error) {
	var now int64
	var err error
	now = time.Now().UTC().Unix()
	item.CreatedAt = now
	_, err = s.DB.Exec(`INSERT INTO principal_project_roles (principal_id, project_id, role, created_at)
		VALUES ((SELECT id FROM service_principals WHERE public_id = ?), (SELECT id FROM projects WHERE public_id = ?), ?, ?)
		ON CONFLICT(principal_id, project_id) DO UPDATE SET role = excluded.role`,
		item.PrincipalID, item.ProjectID, item.Role, item.CreatedAt)
	if err != nil {
		return item, err
	}
	return item, nil
}

func (s *Store) DeletePrincipalProjectRole(principalID string, projectID string) error {
	var err error
	_, err = s.DB.Exec(`DELETE FROM principal_project_roles
		WHERE principal_id = (SELECT id FROM service_principals WHERE public_id = ?)
		  AND project_id = (SELECT id FROM projects WHERE public_id = ?)`, principalID, projectID)
	return err
}

func (s *Store) GetPrincipalProjectRole(principalID string, projectID string) (string, error) {
	var row *sql.Row
	var role string
	var err error
	row = s.DB.QueryRow(`SELECT role FROM principal_project_roles
		WHERE principal_id = (SELECT id FROM service_principals WHERE public_id = ?)
		  AND project_id = (SELECT id FROM projects WHERE public_id = ?)`, principalID, projectID)
	err = row.Scan(&role)
	return role, err
}