[feature] New user sign-up via web page (#2796)

* [feature] User sign-up form and admin notifs

* add chosen + filtered languages to migration

* remove stray comment

* chosen languages schmosen schmanguages

* proper error on local account missing

9 files changed, 329 insertions, 4 deletions

diff --git a/internal/db/account.go b/internal/db/account.go
index 3de72c5a8..45276f41f 100644
--- a/internal/db/account.go
+++ b/internal/db/account.go
@@ -29,6 +29,9 @@ type Account interface {
 	// GetAccountByID returns one account with the given ID, or an error if something goes wrong.
 	GetAccountByID(ctx context.Context, id string) (*gtsmodel.Account, error)
 
+	// GetAccountsByIDs returns accounts corresponding to given IDs.
+	GetAccountsByIDs(ctx context.Context, ids []string) ([]*gtsmodel.Account, error)
+
 	// GetAccountByURI returns one account with the given URI, or an error if something goes wrong.
 	GetAccountByURI(ctx context.Context, uri string) (*gtsmodel.Account, error)
 
diff --git a/internal/db/admin.go b/internal/db/admin.go
index fcae928f6..1f24c7932 100644
--- a/internal/db/admin.go
+++ b/internal/db/admin.go
@@ -19,6 +19,7 @@ package db
 
 import (
 	"context"
+	"time"
 
 	"github.com/superseriousbusiness/gotosocial/internal/gtsmodel"
 )
@@ -36,7 +37,7 @@ type Admin interface {
 	// C) something went wrong in the db
 	IsEmailAvailable(ctx context.Context, email string) (bool, error)
 
-	// NewSignup creates a new user in the database with the given parameters.
+	// NewSignup creates a new user + account in the database with the given parameters.
 	// By the time this function is called, it should be assumed that all the parameters have passed validation!
 	NewSignup(ctx context.Context, newSignup gtsmodel.NewSignup) (*gtsmodel.User, error)
 
@@ -50,6 +51,23 @@ type Admin interface {
 	// This is needed for things like serving instance information through /api/v1/instance
 	CreateInstanceInstance(ctx context.Context) error
 
+	// CreateInstanceApplication creates an application in the database
+	// for use in processing signups etc through the sign-up form.
+	CreateInstanceApplication(ctx context.Context) error
+
+	// GetInstanceApplication gets the instance application
+	// (ie., the application owned by the instance account).
+	GetInstanceApplication(ctx context.Context) (*gtsmodel.Application, error)
+
+	// CountApprovedSignupsSince counts the number of new account
+	// sign-ups approved on this instance since the given time.
+	CountApprovedSignupsSince(ctx context.Context, since time.Time) (int, error)
+
+	// CountUnhandledSignups counts the number of account sign-ups
+	// that have not yet been approved or denied. In other words,
+	// the number of pending sign-ups sitting in the backlog.
+	CountUnhandledSignups(ctx context.Context) (int, error)
+
 	/*
 		ACTION FUNCS
 	*/
diff --git a/internal/db/bundb/admin.go b/internal/db/bundb/admin.go
index 832db1d8f..e52467b9b 100644
--- a/internal/db/bundb/admin.go
+++ b/internal/db/bundb/admin.go
@@ -27,6 +27,7 @@ import (
 	"strings"
 	"time"
 
+	"github.com/google/uuid"
 	"github.com/superseriousbusiness/gotosocial/internal/ap"
 	"github.com/superseriousbusiness/gotosocial/internal/config"
 	"github.com/superseriousbusiness/gotosocial/internal/db"
@@ -121,7 +122,6 @@ func (a *adminDB) NewSignup(ctx context.Context, newSignup gtsmodel.NewSignup) (
 
 		settings := &gtsmodel.AccountSettings{
 			AccountID: accountID,
-			Reason:    newSignup.Reason,
 			Privacy:   gtsmodel.VisibilityDefault,
 		}
 
@@ -197,6 +197,7 @@ func (a *adminDB) NewSignup(ctx context.Context, newSignup gtsmodel.NewSignup) (
 		Account:                account,
 		EncryptedPassword:      string(encryptedPassword),
 		SignUpIP:               newSignup.SignUpIP.To4(),
+		Reason:                 newSignup.Reason,
 		Locale:                 newSignup.Locale,
 		UnconfirmedEmail:       newSignup.Email,
 		CreatedByApplicationID: newSignup.AppID,
@@ -331,6 +332,113 @@ func (a *adminDB) CreateInstanceInstance(ctx context.Context) error {
 	return nil
 }
 
+func (a *adminDB) CreateInstanceApplication(ctx context.Context) error {
+	// Check if instance application already exists.
+	// Instance application client_id always = the
+	// instance account's ID so this is an easy check.
+	instanceAcct, err := a.state.DB.GetInstanceAccount(ctx, "")
+	if err != nil {
+		return err
+	}
+
+	exists, err := exists(
+		ctx,
+		a.db.
+			NewSelect().
+			Column("application.id").
+			TableExpr("? AS ?", bun.Ident("applications"), bun.Ident("application")).
+			Where("? = ?", bun.Ident("application.client_id"), instanceAcct.ID),
+	)
+	if err != nil {
+		return err
+	}
+
+	if exists {
+		log.Infof(ctx, "instance application already exists")
+		return nil
+	}
+
+	// Generate new IDs for this
+	// application and its client.
+	protocol := config.GetProtocol()
+	host := config.GetHost()
+	url := protocol + "://" + host
+
+	clientID := instanceAcct.ID
+	clientSecret := uuid.NewString()
+	appID, err := id.NewRandomULID()
+	if err != nil {
+		return err
+	}
+
+	// Generate the application
+	// to put in the database.
+	app := &gtsmodel.Application{
+		ID:           appID,
+		Name:         host + " instance application",
+		Website:      url,
+		RedirectURI:  url,
+		ClientID:     clientID,
+		ClientSecret: clientSecret,
+		Scopes:       "write:accounts",
+	}
+
+	// Store it.
+	if err := a.state.DB.PutApplication(ctx, app); err != nil {
+		return err
+	}
+
+	// Model an oauth client
+	// from the application.
+	oc := &gtsmodel.Client{
+		ID:     clientID,
+		Secret: clientSecret,
+		Domain: url,
+	}
+
+	// Store it.
+	return a.state.DB.Put(ctx, oc)
+}
+
+func (a *adminDB) GetInstanceApplication(ctx context.Context) (*gtsmodel.Application, error) {
+	// Instance app clientID == instanceAcct.ID,
+	// so get the instance account first.
+	instanceAcct, err := a.state.DB.GetInstanceAccount(ctx, "")
+	if err != nil {
+		return nil, err
+	}
+
+	app := new(gtsmodel.Application)
+	if err := a.db.
+		NewSelect().
+		Model(app).
+		Where("? = ?", bun.Ident("application.client_id"), instanceAcct.ID).
+		Scan(ctx); err != nil {
+		return nil, err
+	}
+
+	return app, nil
+}
+
+func (a *adminDB) CountApprovedSignupsSince(ctx context.Context, since time.Time) (int, error) {
+	return a.db.
+		NewSelect().
+		TableExpr("? AS ?", bun.Ident("users"), bun.Ident("user")).
+		Where("? > ?", bun.Ident("user.created_at"), since).
+		Where("? = ?", bun.Ident("user.approved"), true).
+		Count(ctx)
+}
+
+func (a *adminDB) CountUnhandledSignups(ctx context.Context) (int, error) {
+	return a.db.
+		NewSelect().
+		TableExpr("? AS ?", bun.Ident("users"), bun.Ident("user")).
+		// Approved is false by default.
+		// Explicitly rejected sign-ups end up elsewhere.
+		Where("? = ?", bun.Ident("user.approved"), false).
+		Count(ctx)
+}
+
 /*
 	ACTION FUNCS
 */
diff --git a/internal/db/bundb/instance.go b/internal/db/bundb/instance.go
index 5f96f9a26..73bbcea8b 100644
--- a/internal/db/bundb/instance.go
+++ b/internal/db/bundb/instance.go
@@ -380,3 +380,33 @@ func (i *instanceDB) GetInstanceModeratorAddresses(ctx context.Context) ([]strin
 
 	return addresses, nil
 }
+
+func (i *instanceDB) GetInstanceModerators(ctx context.Context) ([]*gtsmodel.Account, error) {
+	accountIDs := []string{}
+
+	// Select account IDs of approved, confirmed,
+	// and enabled moderators or admins.
+
+	q := i.db.
+		NewSelect().
+		TableExpr("? AS ?", bun.Ident("users"), bun.Ident("user")).
+		Column("user.account_id").
+		Where("? = ?", bun.Ident("user.approved"), true).
+		Where("? IS NOT NULL", bun.Ident("user.confirmed_at")).
+		Where("? = ?", bun.Ident("user.disabled"), false).
+		WhereGroup(" AND ", func(q *bun.SelectQuery) *bun.SelectQuery {
+			return q.
+				Where("? = ?", bun.Ident("user.moderator"), true).
+				WhereOr("? = ?", bun.Ident("user.admin"), true)
+		})
+
+	if err := q.Scan(ctx, &accountIDs); err != nil {
+		return nil, err
+	}
+
+	if len(accountIDs) == 0 {
+		return nil, db.ErrNoEntries
+	}
+
+	return i.state.DB.GetAccountsByIDs(ctx, accountIDs)
+}
diff --git a/internal/db/bundb/migrations/20240318115336_account_settings.go b/internal/db/bundb/migrations/20240318115336_account_settings.go
index 90d3ff420..25c64e826 100644
--- a/internal/db/bundb/migrations/20240318115336_account_settings.go
+++ b/internal/db/bundb/migrations/20240318115336_account_settings.go
@@ -21,7 +21,7 @@ import (
 	"context"
 
 	oldgtsmodel "github.com/superseriousbusiness/gotosocial/internal/db/bundb/migrations/20230328203024_migration_fix"
-	newgtsmodel "github.com/superseriousbusiness/gotosocial/internal/gtsmodel"
+	newgtsmodel "github.com/superseriousbusiness/gotosocial/internal/db/bundb/migrations/20240318115336_account_settings"
 	"github.com/superseriousbusiness/gotosocial/internal/log"
 	"github.com/superseriousbusiness/gotosocial/internal/util"
 
diff --git a/internal/db/bundb/migrations/20240318115336_account_settings/accountsettings.go b/internal/db/bundb/migrations/20240318115336_account_settings/accountsettings.go
new file mode 100644
index 000000000..9ce142694
--- /dev/null
+++ b/internal/db/bundb/migrations/20240318115336_account_settings/accountsettings.go
@@ -0,0 +1,38 @@
+// GoToSocial
+// Copyright (C) GoToSocial Authors admin@gotosocial.org
+// SPDX-License-Identifier: AGPL-3.0-or-later
+//
+// This program is free software: you can redistribute it and/or modify
+// it under the terms of the GNU Affero General Public License as published by
+// the Free Software Foundation, either version 3 of the License, or
+// (at your option) any later version.
+//
+// This program is distributed in the hope that it will be useful,
+// but WITHOUT ANY WARRANTY; without even the implied warranty of
+// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+// GNU Affero General Public License for more details.
+//
+// You should have received a copy of the GNU Affero General Public License
+// along with this program.  If not, see <http://www.gnu.org/licenses/>.
+
+package gtsmodel
+
+import "time"
+
+type Visibility string
+
+// AccountSettings models settings / preferences for a local, non-instance account.
+type AccountSettings struct {
+	AccountID         string     `bun:"type:CHAR(26),pk,nullzero,notnull,unique"`                    // AccountID that owns this settings.
+	CreatedAt         time.Time  `bun:"type:timestamptz,nullzero,notnull,default:current_timestamp"` // when was item created.
+	UpdatedAt         time.Time  `bun:"type:timestamptz,nullzero,notnull,default:current_timestamp"` // when was item was last updated.
+	Reason            string     `bun:",nullzero"`                                                   // What reason was given for signing up when this account was created?
+	Privacy           Visibility `bun:",nullzero"`                                                   // Default post privacy for this account
+	Sensitive         *bool      `bun:",nullzero,notnull,default:false"`                             // Set posts from this account to sensitive by default?
+	Language          string     `bun:",nullzero,notnull,default:'en'"`                              // What language does this account post in?
+	StatusContentType string     `bun:",nullzero"`                                                   // What is the default format for statuses posted by this account (only for local accounts).
+	Theme             string     `bun:",nullzero"`                                                   // Preset CSS theme filename selected by this Account (empty string if nothing set).
+	CustomCSS         string     `bun:",nullzero"`                                                   // Custom CSS that should be displayed for this Account's profile and statuses.
+	EnableRSS         *bool      `bun:",nullzero,notnull,default:false"`                             // enable RSS feed subscription for this account's public posts at [URL]/feed
+	HideCollections   *bool      `bun:",nullzero,notnull,default:false"`                             // Hide this account's followers/following collections.
+}
diff --git a/internal/db/bundb/migrations/20240401130338_sign_up.go b/internal/db/bundb/migrations/20240401130338_sign_up.go
new file mode 100644
index 000000000..51317fd9f
--- /dev/null
+++ b/internal/db/bundb/migrations/20240401130338_sign_up.go
@@ -0,0 +1,124 @@
+// GoToSocial
+// Copyright (C) GoToSocial Authors admin@gotosocial.org
+// SPDX-License-Identifier: AGPL-3.0-or-later
+//
+// This program is free software: you can redistribute it and/or modify
+// it under the terms of the GNU Affero General Public License as published by
+// the Free Software Foundation, either version 3 of the License, or
+// (at your option) any later version.
+//
+// This program is distributed in the hope that it will be useful,
+// but WITHOUT ANY WARRANTY; without even the implied warranty of
+// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+// GNU Affero General Public License for more details.
+//
+// You should have received a copy of the GNU Affero General Public License
+// along with this program.  If not, see <http://www.gnu.org/licenses/>.
+
+package migrations
+
+import (
+	"context"
+	"strings"
+
+	"github.com/superseriousbusiness/gotosocial/internal/gtsmodel"
+	"github.com/uptrace/bun"
+)
+
+func init() {
+	up := func(ctx context.Context, db *bun.DB) error {
+		// Add reason to users table.
+		_, err := db.ExecContext(ctx,
+			"ALTER TABLE ? ADD COLUMN ? TEXT",
+			bun.Ident("users"), bun.Ident("reason"),
+		)
+		if err != nil {
+			e := err.Error()
+			if !(strings.Contains(e, "already exists") ||
+				strings.Contains(e, "duplicate column name") ||
+				strings.Contains(e, "SQLSTATE 42701")) {
+				return err
+			}
+		}
+
+		return db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error {
+			// Get reasons from
+			// account settings.
+			type idReason struct {
+				AccountID string
+				Reason    string
+			}
+
+			reasons := []idReason{}
+			if err := tx.
+				NewSelect().
+				Table("account_settings").
+				Column("account_id", "reason").
+				Scan(ctx, &reasons); err != nil {
+				return err
+			}
+
+			// Add each reason to appropriate user.
+			for _, r := range reasons {
+				if _, err := tx.
+					NewUpdate().
+					Table("users").
+					Set("? = ?", bun.Ident("reason"), r.Reason).
+					Where("? = ?", bun.Ident("account_id"), r.AccountID).
+					Exec(ctx, &reasons); err != nil {
+					return err
+				}
+			}
+
+			// Remove now-unused column
+			// from account settings.
+			if _, err := tx.
+				NewDropColumn().
+				Table("account_settings").
+				Column("reason").
+				Exec(ctx); err != nil {
+				return err
+			}
+
+			// Remove now-unused columns from users.
+			for _, column := range []string{
+				"current_sign_in_at",
+				"current_sign_in_ip",
+				"last_sign_in_at",
+				"last_sign_in_ip",
+				"sign_in_count",
+				"chosen_languages",
+				"filtered_languages",
+			} {
+				if _, err := tx.
+					NewDropColumn().
+					Table("users").
+					Column(column).
+					Exec(ctx); err != nil {
+					return err
+				}
+			}
+
+			// Create new UsersDenied table.
+			if _, err := tx.
+				NewCreateTable().
+				Model(&gtsmodel.DeniedUser{}).
+				IfNotExists().
+				Exec(ctx); err != nil {
+				return err
+			}
+
+			return nil
+		})
+	}
+
+	down := func(ctx context.Context, db *bun.DB) error {
+		return db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error {
+			return nil
+		})
+	}
+
+	if err := Migrations.Register(up, down); err != nil {
+		panic(err)
+	}
+}
diff --git a/internal/db/bundb/notification_test.go b/internal/db/bundb/notification_test.go
index e27475476..83c3ef041 100644
--- a/internal/db/bundb/notification_test.go
+++ b/internal/db/bundb/notification_test.go
@@ -176,7 +176,7 @@ func (suite *NotificationTestSuite) TestDeleteNotificationsOriginatingFromAndTar
 	}
 
 	for _, n := range notif {
-		if n.OriginAccountID == originAccount.ID || n.TargetAccountID == targetAccount.ID {
+		if n.OriginAccountID == originAccount.ID && n.TargetAccountID == targetAccount.ID {
 			suite.FailNowf(
 				"",
 				"no notifications with origin account id %s and target account %s should remain",
diff --git a/internal/db/instance.go b/internal/db/instance.go
index 3b9588fef..23a2fc8dc 100644
--- a/internal/db/instance.go
+++ b/internal/db/instance.go
@@ -58,4 +58,8 @@ type Instance interface {
 	// GetInstanceModeratorAddresses returns a slice of email addresses belonging to active
 	// (as in, not suspended) moderators + admins on this instance.
 	GetInstanceModeratorAddresses(ctx context.Context) ([]string, error)
+
+	// GetInstanceModerators returns a slice of accounts belonging to active
+	// (as in, non suspended) moderators + admins on this instance.
+	GetInstanceModerators(ctx context.Context) ([]*gtsmodel.Account, error)
 }