Skip to content

armgit / fider

Public Imported from GitHub
0

Open platform to collect and prioritize feedback

12.4 KB
Raw Blame
package postgres

import (
	"context"
	"time"

	"github.com/getfider/fider/app/pkg/bus"
	"github.com/getfider/fider/app/services/sqlstore/dbEntities"

	"github.com/getfider/fider/app/models/cmd"
	"github.com/getfider/fider/app/models/entity"
	"github.com/getfider/fider/app/models/enum"
	"github.com/getfider/fider/app/models/query"

	"github.com/getfider/fider/app/pkg/dbx"
	"github.com/getfider/fider/app/pkg/env"
	"github.com/getfider/fider/app/pkg/errors"
)

func isCNAMEAvailable(ctx context.Context, q *query.IsCNAMEAvailable) error {
	return using(ctx, func(trx *dbx.Trx, tenant *entity.Tenant, user *entity.User) error {
		tenantID := 0
		if tenant != nil {
			tenantID = tenant.ID
		}

		exists, err := trx.Exists("SELECT id FROM tenants WHERE cname = $1 AND id <> $2", q.CNAME, tenantID)
		if err != nil {
			q.Result = false
			return errors.Wrap(err, "failed to check if tenant exists with CNAME '%s'", q.CNAME)
		}
		q.Result = !exists
		return nil
	})
}

func isSubdomainAvailable(ctx context.Context, q *query.IsSubdomainAvailable) error {
	return using(ctx, func(trx *dbx.Trx, tenant *entity.Tenant, user *entity.User) error {
		exists, err := trx.Exists("SELECT id FROM tenants WHERE subdomain = $1", q.Subdomain)
		if err != nil {
			q.Result = false
			return errors.Wrap(err, "failed to check if tenant exists with subdomain '%s'", q.Subdomain)
		}
		q.Result = !exists
		return nil
	})
}

func updateTenantPrivacySettings(ctx context.Context, c *cmd.UpdateTenantPrivacySettings) error {
	return using(ctx, func(trx *dbx.Trx, tenant *entity.Tenant, user *entity.User) error {
		_, err := trx.Execute(`
			UPDATE tenants
			SET is_private = $1, is_feed_enabled = $2, is_moderation_enabled = $3, is_signup_disabled = $4
			WHERE id = $5`,
			c.IsPrivate, c.IsFeedEnabled, c.IsModerationEnabled, c.IsSignUpDisabled, tenant.ID,
		)
		if err != nil {
			return errors.Wrap(err, "failed update tenant privacy settings")
		}
		return nil
	})
}

func updateTenantEmailAuthAllowedSettings(ctx context.Context, c *cmd.UpdateTenantEmailAuthAllowedSettings) error {
	return using(ctx, func(trx *dbx.Trx, tenant *entity.Tenant, user *entity.User) error {
		_, err := trx.Execute("UPDATE tenants SET is_email_auth_allowed = $1 WHERE id = $2", c.IsEmailAuthAllowed, tenant.ID)
		if err != nil {
			return errors.Wrap(err, "failed update tenant allowing email auth settings")
		}
		return nil
	})
}

func updateTenantSettings(ctx context.Context, c *cmd.UpdateTenantSettings) error {
	return using(ctx, func(trx *dbx.Trx, tenant *entity.Tenant, user *entity.User) error {
		if c.Logo.Remove {
			c.Logo.BlobKey = ""
		}

		query := "UPDATE tenants SET name = $1, invitation = $2, welcome_message = $3, welcome_header = $4, description_template = $5, cname = $6, logo_bkey = $7, locale = $8 WHERE id = $9"
		_, err := trx.Execute(query, c.Title, c.Invitation, c.WelcomeMessage, c.WelcomeHeader, c.DescriptionTemplate, c.CNAME, c.Logo.BlobKey, c.Locale, tenant.ID)
		if err != nil {
			return errors.Wrap(err, "failed update tenant settings")
		}

		tenant.Name = c.Title
		tenant.Invitation = c.Invitation
		tenant.CNAME = c.CNAME
		tenant.WelcomeMessage = c.WelcomeMessage
		tenant.WelcomeHeader = c.WelcomeHeader
		tenant.DescriptionTemplate = c.DescriptionTemplate

		return nil
	})
}

func updateTenantAdvancedSettings(ctx context.Context, c *cmd.UpdateTenantAdvancedSettings) error {
	return using(ctx, func(trx *dbx.Trx, tenant *entity.Tenant, user *entity.User) error {
		AllowedSchemes := c.AllowedSchemes
		if !env.Config.AllowAllowedSchemes {
			AllowedSchemes = ""
		}

		query := "UPDATE tenants SET custom_css = $1, allowed_schemes = $2 WHERE id = $3"
		_, err := trx.Execute(query, c.CustomCSS, AllowedSchemes, tenant.ID)
		if err != nil {
			return errors.Wrap(err, "failed update tenant advanced settings")
		}

		tenant.CustomCSS = c.CustomCSS
		tenant.AllowedSchemes = AllowedSchemes
		return nil
	})
}

func activateTenant(ctx context.Context, c *cmd.ActivateTenant) error {
	return using(ctx, func(trx *dbx.Trx, tenant *entity.Tenant, user *entity.User) error {
		query := "UPDATE tenants SET status = $1 WHERE id = $2"
		_, err := trx.Execute(query, enum.TenantActive, c.TenantID)
		if err != nil {
			return errors.Wrap(err, "failed to activate tenant with id '%d'", c.TenantID)
		}
		return nil
	})
}

func getVerificationByKey(ctx context.Context, q *query.GetVerificationByKey) error {
	return using(ctx, func(trx *dbx.Trx, tenant *entity.Tenant, user *entity.User) error {
		verification := dbEntities.EmailVerification{}

		query := "SELECT id, email, name, key, code, created_at, verified_at, expires_at, kind, user_id, attempts FROM email_verifications WHERE key = $1 AND kind = $2 AND tenant_id = $3 LIMIT 1"
		err := trx.Get(&verification, query, q.Key, q.Kind, tenant.ID)
		if err != nil {
			return errors.Wrap(err, "failed to get email verification by its key")
		}

		q.Result = verification.ToModel()
		return nil
	})
}

func getVerificationByEmailAndCode(ctx context.Context, q *query.GetVerificationByEmailAndCode) error {
	return using(ctx, func(trx *dbx.Trx, tenant *entity.Tenant, user *entity.User) error {
		verification := dbEntities.EmailVerification{}

		query := "SELECT id, email, name, key, code, created_at, verified_at, expires_at, kind, user_id, attempts FROM email_verifications WHERE tenant_id = $1 AND email = $2 AND code = $3 AND kind = $4 LIMIT 1"
		err := trx.Get(&verification, query, tenant.ID, q.Email, q.Code, q.Kind)
		if err != nil {
			return errors.Wrap(err, "failed to get email verification by email and code")
		}

		q.Result = verification.ToModel()
		return nil
	})
}

func saveVerificationKey(ctx context.Context, c *cmd.SaveVerificationKey) error {
	return using(ctx, func(trx *dbx.Trx, tenant *entity.Tenant, user *entity.User) error {
		var userID any
		if c.Request.GetUser() != nil {
			userID = c.Request.GetUser().ID
		}

		var code any
		if c.Code != "" {
			code = c.Code
		}

		query := "INSERT INTO email_verifications (tenant_id, email, created_at, expires_at, key, name, kind, user_id, code) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)"
		_, err := trx.Execute(query, tenant.ID, c.Request.GetEmail(), time.Now(), time.Now().Add(c.Duration), c.Key, c.Request.GetName(), c.Request.GetKind(), userID, code)
		if err != nil {
			return errors.Wrap(err, "failed to save verification key for kind '%d'", c.Request.GetKind())
		}
		return nil
	})
}

func setKeyAsVerified(ctx context.Context, c *cmd.SetKeyAsVerified) error {
	return using(ctx, func(trx *dbx.Trx, tenant *entity.Tenant, user *entity.User) error {
		query := "UPDATE email_verifications SET verified_at = $1 WHERE tenant_id = $2 AND key = $3 AND verified_at IS NULL"
		_, err := trx.Execute(query, time.Now(), tenant.ID, c.Key)
		if err != nil {
			return errors.Wrap(err, "failed to update verified date of email verification request")
		}
		return nil
	})
}

func getActiveVerificationByEmail(ctx context.Context, q *query.GetActiveVerificationByEmail) error {
	return using(ctx, func(trx *dbx.Trx, tenant *entity.Tenant, user *entity.User) error {
		verification := dbEntities.EmailVerification{}

		query := `SELECT id, email, name, key, code, created_at, verified_at, expires_at, kind, user_id, attempts
		          FROM email_verifications
		          WHERE tenant_id = $1 AND email = $2 AND kind = $3
		            AND verified_at IS NULL AND expires_at > $4
		          ORDER BY created_at DESC
		          LIMIT 1`
		err := trx.Get(&verification, query, tenant.ID, q.Email, q.Kind, time.Now())
		if err != nil {
			return errors.Wrap(err, "failed to get active email verification by email")
		}

		q.Result = verification.ToModel()
		return nil
	})
}

func incrementVerificationAttempts(ctx context.Context, c *cmd.IncrementVerificationAttempts) error {
	return using(ctx, func(trx *dbx.Trx, tenant *entity.Tenant, user *entity.User) error {
		_, err := trx.Execute(
			"UPDATE email_verifications SET attempts = attempts + 1 WHERE tenant_id = $1 AND key = $2",
			tenant.ID, c.Key,
		)
		if err != nil {
			return errors.Wrap(err, "failed to increment verification attempts")
		}
		return nil
	})
}

func invalidateVerificationsByEmail(ctx context.Context, c *cmd.InvalidateVerificationsByEmail) error {
	return using(ctx, func(trx *dbx.Trx, tenant *entity.Tenant, user *entity.User) error {
		_, err := trx.Execute(
			"UPDATE email_verifications SET verified_at = $1 WHERE tenant_id = $2 AND email = $3 AND kind = $4 AND verified_at IS NULL",
			time.Now(), tenant.ID, c.Email, c.Kind,
		)
		if err != nil {
			return errors.Wrap(err, "failed to invalidate verifications for email '%s'", c.Email)
		}
		return nil
	})
}

func createTenant(ctx context.Context, c *cmd.CreateTenant) error {
	return using(ctx, func(trx *dbx.Trx, _ *entity.Tenant, _ *entity.User) error {
		now := time.Now()

		var id int
		err := trx.Get(&id,
			`INSERT INTO tenants (name, subdomain, created_at, cname, invitation, welcome_message, status, is_private, custom_css, logo_bkey, locale, is_email_auth_allowed, is_feed_enabled, prevent_indexing, is_moderation_enabled)
			 VALUES ($1, $2, $3, '', '', '', $4, false, '', '', $5, true, true, true, false)
			 RETURNING id`, c.Name, c.Subdomain, now, c.Status, env.Config.Locale)
		if err != nil {
			return err
		}

		byDomain := &query.GetTenantByDomain{Domain: c.Subdomain}
		err = bus.Dispatch(ctx, byDomain)
		c.Result = byDomain.Result
		return err
	})
}

func getFirstTenant(ctx context.Context, q *query.GetFirstTenant) error {
	return using(ctx, func(trx *dbx.Trx, _ *entity.Tenant, _ *entity.User) error {
		tenant := dbEntities.Tenant{}

	err := trx.Get(&tenant, `
		SELECT t.id, t.name, t.subdomain, t.cname, t.invitation, t.locale, t.welcome_message, t.welcome_header, t.description_template, t.status, t.is_private, t.logo_bkey, t.custom_css, t.allowed_schemes, t.is_email_auth_allowed, t.is_feed_enabled, t.is_moderation_enabled, t.is_signup_disabled, t.prevent_indexing, t.is_pro, t.scheduled_deletion_at,
			(b.paddle_subscription_id IS NOT NULL AND b.stripe_subscription_id IS NULL) AS has_paddle_subscription
		FROM tenants t
		LEFT JOIN tenants_billing b ON b.tenant_id = t.id
		ORDER BY t.id LIMIT 1
	`)
		if err != nil {
			return errors.Wrap(err, "failed to get first tenant")
		}

		q.Result = tenant.ToModel()
		return nil
	})
}

func getTenantByDomain(ctx context.Context, q *query.GetTenantByDomain) error {
	return using(ctx, func(trx *dbx.Trx, _ *entity.Tenant, _ *entity.User) error {
		tenant := dbEntities.Tenant{}

	err := trx.Get(&tenant, `
		SELECT t.id, t.name, t.subdomain, t.cname, t.invitation, t.locale, t.welcome_message, t.welcome_header, t.description_template, t.status, t.is_private, t.logo_bkey, t.custom_css, t.allowed_schemes, t.is_email_auth_allowed, t.is_feed_enabled, t.is_moderation_enabled, t.is_signup_disabled, t.prevent_indexing, t.is_pro, t.scheduled_deletion_at,
			(b.paddle_subscription_id IS NOT NULL AND b.stripe_subscription_id IS NULL) AS has_paddle_subscription
		FROM tenants t
		LEFT JOIN tenants_billing b ON b.tenant_id = t.id
		WHERE t.subdomain = $1 OR t.subdomain = $2 OR t.cname = $3
		ORDER BY t.cname DESC
	`, env.Subdomain(q.Domain), q.Domain, q.Domain)
		if err != nil {
			return errors.Wrap(err, "failed to get tenant with domain '%s'", q.Domain)
		}

		q.Result = tenant.ToModel()
		return nil
	})
}

func getPendingSignUpVerification(ctx context.Context, q *query.GetPendingSignUpVerification) error {
	return using(ctx, func(trx *dbx.Trx, tenant *entity.Tenant, user *entity.User) error {
		verification := dbEntities.EmailVerification{}

		query := `SELECT id, email, name, key, code, created_at, verified_at, expires_at, kind, user_id, attempts
		          FROM email_verifications
		          WHERE tenant_id = $1 AND kind = $2 AND verified_at IS NULL
		          ORDER BY created_at DESC
		          LIMIT 1`
		err := trx.Get(&verification, query, tenant.ID, enum.EmailVerificationKindSignUp)
		if err != nil {
			return errors.Wrap(err, "failed to get pending signup verification for tenant '%d'", tenant.ID)
		}

		q.Result = verification.ToModel()
		return nil
	})
}

func invalidatePreviousSignUpKeys(ctx context.Context, c *cmd.InvalidatePreviousSignUpKeys) error {
	return using(ctx, func(trx *dbx.Trx, tenant *entity.Tenant, user *entity.User) error {
		query := `UPDATE email_verifications 
		          SET expires_at = $1 
		          WHERE tenant_id = $2 AND kind = $3 AND verified_at IS NULL AND expires_at > $1`
		_, err := trx.Execute(query, time.Now(), tenant.ID, enum.EmailVerificationKindSignUp)
		if err != nil {
			return errors.Wrap(err, "failed to invalidate previous signup keys for tenant '%d'", tenant.ID)
		}
		return nil
	})
}