codit/backend/internal/db/acme.go

package db

import "database/sql"
import "encoding/json"
import "strings"
import "time"

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

func (s *Store) ListACMEProfiles() ([]models.ACMEProfile, error) {
	var rows *sql.Rows
	var err error
	var items []models.ACMEProfile
	var item models.ACMEProfile
	rows, err = s.Query(`SELECT public_id, name, directory_url, email, account_url, account_key_pem, solver_type, acme_dns_api_url, acme_dns_user, acme_dns_key, acme_dns_subdomain, acme_dns_full_domain, enabled, last_error, created_at, updated_at
		FROM acme_profiles
		ORDER BY name`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(&item.ID, &item.Name, &item.DirectoryURL, &item.Email, &item.AccountURL, &item.AccountKeyPEM, &item.SolverType, &item.ACMEDNSAPIURL, &item.ACMEDNSUser, &item.ACMEDNSKey, &item.ACMEDNSSubdomain, &item.ACMEDNSFullDomain, &item.Enabled, &item.LastError, &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) GetACMEProfile(id string) (models.ACMEProfile, error) {
	var row *sql.Row
	var err error
	var item models.ACMEProfile
	row = s.QueryRow(`SELECT public_id, name, directory_url, email, account_url, account_key_pem, solver_type, acme_dns_api_url, acme_dns_user, acme_dns_key, acme_dns_subdomain, acme_dns_full_domain, enabled, last_error, created_at, updated_at
		FROM acme_profiles
		WHERE public_id = ?`, strings.TrimSpace(id))
	err = row.Scan(&item.ID, &item.Name, &item.DirectoryURL, &item.Email, &item.AccountURL, &item.AccountKeyPEM, &item.SolverType, &item.ACMEDNSAPIURL, &item.ACMEDNSUser, &item.ACMEDNSKey, &item.ACMEDNSSubdomain, &item.ACMEDNSFullDomain, &item.Enabled, &item.LastError, &item.CreatedAt, &item.UpdatedAt)
	if err != nil {
		return item, err
	}
	return item, nil
}

func (s *Store) CreateACMEProfile(item models.ACMEProfile) (models.ACMEProfile, error) {
	var err error
	var now int64
	if strings.TrimSpace(item.ID) == "" {
		item.ID, err = util.NewID()
		if err != nil {
			return item, err
		}
	}
	if strings.TrimSpace(item.SolverType) == "" {
		item.SolverType = "manual"
	}
	now = time.Now().UTC().Unix()
	item.CreatedAt = now
	item.UpdatedAt = now
	_, err = s.Exec(`INSERT INTO acme_profiles (public_id, name, directory_url, email, account_url, account_key_pem, solver_type, acme_dns_api_url, acme_dns_user, acme_dns_key, acme_dns_subdomain, acme_dns_full_domain, enabled, last_error, created_at, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		item.ID, item.Name, item.DirectoryURL, item.Email, item.AccountURL, item.AccountKeyPEM, item.SolverType, item.ACMEDNSAPIURL, item.ACMEDNSUser, item.ACMEDNSKey, item.ACMEDNSSubdomain, item.ACMEDNSFullDomain, item.Enabled, item.LastError, item.CreatedAt, item.UpdatedAt)
	if err != nil {
		return item, err
	}
	return item, nil
}

func (s *Store) UpdateACMEProfile(item models.ACMEProfile) (models.ACMEProfile, error) {
	var err error
	if strings.TrimSpace(item.SolverType) == "" {
		item.SolverType = "manual"
	}
	item.UpdatedAt = time.Now().UTC().Unix()
	_, err = s.Exec(`UPDATE acme_profiles
		SET name = ?, directory_url = ?, email = ?, solver_type = ?, acme_dns_api_url = ?, acme_dns_user = ?, acme_dns_key = ?, acme_dns_subdomain = ?, acme_dns_full_domain = ?, enabled = ?, updated_at = ?
		WHERE public_id = ?`,
		item.Name, item.DirectoryURL, item.Email, item.SolverType, item.ACMEDNSAPIURL, item.ACMEDNSUser, item.ACMEDNSKey, item.ACMEDNSSubdomain, item.ACMEDNSFullDomain, item.Enabled, item.UpdatedAt, item.ID)
	if err != nil {
		return item, err
	}
	item, err = s.GetACMEProfile(item.ID)
	if err != nil {
		return item, err
	}
	return item, nil
}

func (s *Store) UpdateACMEProfileAccount(id string, accountURL string, accountKeyPEM string) error {
	var err error
	_, err = s.Exec(`UPDATE acme_profiles SET account_url = ?, account_key_pem = ?, updated_at = ?, last_error = '' WHERE public_id = ?`,
		strings.TrimSpace(accountURL), strings.TrimSpace(accountKeyPEM), time.Now().UTC().Unix(), strings.TrimSpace(id))
	return err
}

func (s *Store) UpdateACMEProfileLastError(id string, msg string) error {
	var err error
	_, err = s.Exec(`UPDATE acme_profiles SET last_error = ?, updated_at = ? WHERE public_id = ?`, strings.TrimSpace(msg), time.Now().UTC().Unix(), strings.TrimSpace(id))
	return err
}

func (s *Store) DeleteACMEProfile(id string) error {
	var err error
	_, err = s.Exec(`DELETE FROM acme_profiles WHERE public_id = ?`, strings.TrimSpace(id))
	return err
}

func (s *Store) ListACMEOrders(profileID string) ([]models.ACMEOrder, error) {
	var rows *sql.Rows
	var err error
	var items []models.ACMEOrder
	var item models.ACMEOrder
	var challengesJSON string
	if strings.TrimSpace(profileID) == "" {
		rows, err = s.Query(`SELECT public_id, profile_public_id, common_name, san_dns, order_url, finalize_url, status, last_error, cert_public_id, challenges_json, created_at, updated_at
			FROM acme_orders
			ORDER BY created_at DESC`)
	} else {
		rows, err = s.Query(`SELECT public_id, profile_public_id, common_name, san_dns, order_url, finalize_url, status, last_error, cert_public_id, challenges_json, created_at, updated_at
			FROM acme_orders
			WHERE profile_public_id = ?
			ORDER BY created_at DESC`, strings.TrimSpace(profileID))
	}
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(&item.ID, &item.ProfileID, &item.CommonName, &item.SANDNS, &item.OrderURL, &item.FinalizeURL, &item.Status, &item.LastError, &item.CertID, &challengesJSON, &item.CreatedAt, &item.UpdatedAt)
		if err != nil {
			return nil, err
		}
		item.Challenges, err = decodeACMEChallenges(challengesJSON)
		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) GetACMEOrder(id string) (models.ACMEOrder, error) {
	var row *sql.Row
	var err error
	var item models.ACMEOrder
	var challengesJSON string
	row = s.QueryRow(`SELECT public_id, profile_public_id, common_name, san_dns, order_url, finalize_url, status, last_error, cert_public_id, challenges_json, csr_pem, key_pem, created_at, updated_at
		FROM acme_orders
		WHERE public_id = ?`, strings.TrimSpace(id))
	err = row.Scan(&item.ID, &item.ProfileID, &item.CommonName, &item.SANDNS, &item.OrderURL, &item.FinalizeURL, &item.Status, &item.LastError, &item.CertID, &challengesJSON, &item.CSRPEM, &item.KeyPEM, &item.CreatedAt, &item.UpdatedAt)
	if err != nil {
		return item, err
	}
	item.Challenges, err = decodeACMEChallenges(challengesJSON)
	if err != nil {
		return item, err
	}
	return item, nil
}

func (s *Store) CreateACMEOrder(item models.ACMEOrder) (models.ACMEOrder, error) {
	var err error
	var now int64
	var challengesJSON string
	if strings.TrimSpace(item.ID) == "" {
		item.ID, err = util.NewID()
		if err != nil {
			return item, err
		}
	}
	challengesJSON, err = encodeACMEChallenges(item.Challenges)
	if err != nil {
		return item, err
	}
	now = time.Now().UTC().Unix()
	item.CreatedAt = now
	item.UpdatedAt = now
	_, err = s.Exec(`INSERT INTO acme_orders (public_id, profile_public_id, common_name, san_dns, order_url, finalize_url, status, last_error, cert_public_id, challenges_json, csr_pem, key_pem, created_at, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		item.ID, item.ProfileID, item.CommonName, item.SANDNS, item.OrderURL, item.FinalizeURL, item.Status, item.LastError, item.CertID, challengesJSON, item.CSRPEM, item.KeyPEM, item.CreatedAt, item.UpdatedAt)
	if err != nil {
		return item, err
	}
	return item, nil
}

func (s *Store) UpdateACMEOrder(item models.ACMEOrder) (models.ACMEOrder, error) {
	var err error
	var challengesJSON string
	challengesJSON, err = encodeACMEChallenges(item.Challenges)
	if err != nil {
		return item, err
	}
	item.UpdatedAt = time.Now().UTC().Unix()
	_, err = s.Exec(`UPDATE acme_orders
		SET status = ?, last_error = ?, cert_public_id = ?, challenges_json = ?, updated_at = ?
		WHERE public_id = ?`,
		item.Status, item.LastError, item.CertID, challengesJSON, item.UpdatedAt, item.ID)
	if err != nil {
		return item, err
	}
	item, err = s.GetACMEOrder(item.ID)
	if err != nil {
		return item, err
	}
	return item, nil
}

func (s *Store) DeleteACMEOrder(id string) error {
	var err error
	_, err = s.Exec(`DELETE FROM acme_orders WHERE public_id = ?`, strings.TrimSpace(id))
	return err
}

func (s *Store) GetLatestACMEOrderByCertID(certID string) (models.ACMEOrder, error) {
	var row *sql.Row
	var item models.ACMEOrder
	var err error
	row = s.QueryRow(`SELECT public_id, profile_public_id, common_name, san_dns
		FROM acme_orders
		WHERE cert_public_id = ?
		ORDER BY updated_at DESC, created_at DESC
		LIMIT 1`, strings.TrimSpace(certID))
	err = row.Scan(&item.ID, &item.ProfileID, &item.CommonName, &item.SANDNS)
	if err != nil {
		return item, err
	}
	return item, nil
}

func (s *Store) HasACMEOrderForCert(certID string) (bool, error) {
	var row *sql.Row
	var found int
	var err error

	row = s.QueryRow(`SELECT 1
		FROM acme_orders
		WHERE cert_public_id = ?
		LIMIT 1`, strings.TrimSpace(certID))
	err = row.Scan(&found)
	if err == sql.ErrNoRows {
		return false, nil
	}
	if err != nil {
		return false, err
	}
	return true, nil
}

func encodeACMEChallenges(items []models.ACMEDNSChallenge) (string, error) {
	var b []byte
	var err error
	if items == nil {
		items = []models.ACMEDNSChallenge{}
	}
	b, err = json.Marshal(items)
	if err != nil {
		return "", err
	}
	return string(b), nil
}

func decodeACMEChallenges(raw string) ([]models.ACMEDNSChallenge, error) {
	var out []models.ACMEDNSChallenge
	var err error
	if strings.TrimSpace(raw) == "" {
		return []models.ACMEDNSChallenge{}, nil
	}
	err = json.Unmarshal([]byte(raw), &out)
	if err != nil {
		return nil, err
	}
	if out == nil {
		out = []models.ACMEDNSChallenge{}
	}
	return out, nil
}