panelhosting/internal/repository/site.go

package repository

import (
	"context"
	"errors"
	"fmt"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
	"github.com/panelhosting/panel/internal/models"
	"github.com/panelhosting/panel/internal/phpversions"
)

type SiteRepository struct {
	pool *pgxpool.Pool
}

func NewSiteRepository(pool *pgxpool.Pool) *SiteRepository {
	return &SiteRepository{pool: pool}
}

type SiteWithDomain struct {
	models.Site
	PrimaryDomain string `json:"primary_domain"`
	DomainID      int64  `json:"domain_id,omitempty"`
	SSLID         int64  `json:"ssl_id,omitempty"`
	SSLStatus     string `json:"ssl_status,omitempty"`
	SSLError      string `json:"ssl_error,omitempty"`
	SSLEmail      string `json:"ssl_email,omitempty"`
}

func (r *SiteRepository) Create(ctx context.Context, serverID, ownerID int64, name, documentRoot, phpVersion, domain, sslEmail string, issueSSL bool) (*SiteWithDomain, error) {
	tx, err := r.pool.Begin(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback(ctx)

	const siteQ = `
		INSERT INTO sites (server_id, owner_id, name, document_root, php_version, ssl_email, status)
		VALUES ($1, $2, $3, $4, $5, NULLIF($6, ''), 'active')
		RETURNING id, uuid, server_id, owner_id, name, document_root, php_version, status,
		          settings, disk_quota_mb, created_at, updated_at
	`
	var s models.Site
	var phpVer *string
	if phpVersion != "" {
		phpVer = &phpVersion
	}
	err = tx.QueryRow(ctx, siteQ, serverID, ownerID, name, documentRoot, phpVer, sslEmail).Scan(
		&s.ID, &s.UUID, &s.ServerID, &s.OwnerID, &s.Name, &s.DocumentRoot, &s.PHPVersion, &s.Status,
		&s.Settings, &s.DiskQuotaMB, &s.CreatedAt, &s.UpdatedAt,
	)
	if err != nil {
		return nil, fmt.Errorf("create site: %w", err)
	}

	const domainQ = `
		INSERT INTO domains (site_id, domain, is_primary, ssl_enabled)
		VALUES ($1, $2, true, $3)
		RETURNING id
	`
	var domainID int64
	if err = tx.QueryRow(ctx, domainQ, s.ID, domain, issueSSL).Scan(&domainID); err != nil {
		return nil, fmt.Errorf("create domain: %w", err)
	}

	result := &SiteWithDomain{Site: s, PrimaryDomain: domain, DomainID: domainID, SSLEmail: sslEmail}

	if issueSSL {
		const sslQ = `
			INSERT INTO ssl_certificates (domain_id, type, status, auto_renew)
			VALUES ($1, 'letsencrypt', 'pending', true)
			RETURNING id
		`
		if err = tx.QueryRow(ctx, sslQ, domainID).Scan(&result.SSLID); err != nil {
			return nil, fmt.Errorf("create ssl cert: %w", err)
		}
		result.SSLStatus = "pending"
	}

	if err = tx.Commit(ctx); err != nil {
		return nil, err
	}

	return result, nil
}

func (r *SiteRepository) ListForUser(ctx context.Context, userID int64, isAdmin bool) ([]SiteWithDomain, error) {
	var q string
	var args []any
	if isAdmin {
		q = siteListQuery + ` ORDER BY s.created_at DESC`
	} else {
		q = siteListQuery + ` WHERE s.owner_id = $1 ORDER BY s.created_at DESC`
		args = append(args, userID)
	}

	rows, err := r.pool.Query(ctx, q, args...)
	if err != nil {
		return nil, fmt.Errorf("list sites: %w", err)
	}
	defer rows.Close()

	return scanSiteList(rows)
}

const siteListQuery = `
	SELECT s.id, s.uuid, s.server_id, s.owner_id, s.name, s.document_root, s.php_version, s.status,
	       s.settings, s.disk_quota_mb, s.created_at, s.updated_at,
	       COALESCE(d.domain::text, ''),
	       COALESCE(d.id, 0),
	       COALESCE(ssl.status::text, 'none'),
	       COALESCE(ssl.error_message, ''),
	       COALESCE(s.ssl_email::text, '')
	FROM sites s
	LEFT JOIN domains d ON d.site_id = s.id AND d.is_primary = true
	LEFT JOIN LATERAL (
		SELECT status, error_message FROM ssl_certificates
		WHERE domain_id = d.id ORDER BY id DESC LIMIT 1
	) ssl ON true
`

func scanSiteList(rows pgx.Rows) ([]SiteWithDomain, error) {
	var list []SiteWithDomain
	for rows.Next() {
		var item SiteWithDomain
		err := rows.Scan(
			&item.ID, &item.UUID, &item.ServerID, &item.OwnerID, &item.Name, &item.DocumentRoot,
			&item.PHPVersion, &item.Status, &item.Settings, &item.DiskQuotaMB,
			&item.CreatedAt, &item.UpdatedAt, &item.PrimaryDomain,
			&item.DomainID, &item.SSLStatus, &item.SSLError, &item.SSLEmail,
		)
		if err != nil {
			return nil, err
		}
		list = append(list, item)
	}
	return list, rows.Err()
}

func (r *SiteRepository) UpdateSSLEmail(ctx context.Context, siteID int64, email string) error {
	_, err := r.pool.Exec(ctx, `UPDATE sites SET ssl_email = $2, updated_at = now() WHERE id = $1`, siteID, email)
	return err
}

func (r *SiteRepository) GetSSLEmail(ctx context.Context, siteID int64) (string, error) {
	var email *string
	err := r.pool.QueryRow(ctx, `SELECT ssl_email::text FROM sites WHERE id = $1`, siteID).Scan(&email)
	if err != nil {
		return "", err
	}
	if email == nil {
		return "", nil
	}
	return *email, nil
}

type PHPVersionRepository struct {
	pool *pgxpool.Pool
}

func NewPHPVersionRepository(pool *pgxpool.Pool) *PHPVersionRepository {
	return &PHPVersionRepository{pool: pool}
}

func (r *PHPVersionRepository) ListActive(ctx context.Context) ([]string, error) {
	rows, err := r.pool.Query(ctx, `
		SELECT version FROM php_versions WHERE is_active = true ORDER BY sort_order
	`)
	if err != nil {
		return phpversions.Defaults, nil
	}
	defer rows.Close()

	var versions []string
	for rows.Next() {
		var v string
		if err := rows.Scan(&v); err != nil {
			return phpversions.Defaults, nil
		}
		versions = append(versions, v)
	}
	if err := rows.Err(); err != nil || len(versions) == 0 {
		return phpversions.Defaults, nil
	}
	return versions, nil
}

func (r *PHPVersionRepository) IsActive(ctx context.Context, version string) (bool, error) {
	var active bool
	err := r.pool.QueryRow(ctx, `SELECT is_active FROM php_versions WHERE version = $1`, version).Scan(&active)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return phpversions.Contains(version), nil
		}
		return phpversions.Contains(version), nil
	}
	return active, nil
}