Move issue pin to an standalone table for querying performance (#33452)

Noticed a SQL in gitea.com has a bigger load. It seems both `is_pull`
and `pin_order` are not indexed columns in the database.

```SQL
SELECT `id`, `repo_id`, `index`, `poster_id`, `original_author`, `original_author_id`, `name`, `content`, `content_version`, `milestone_id`, `priority`, `is_closed`, `is_pull`, `num_comments`, `ref`, `pin_order`, `deadline_unix`, `created_unix`, `updated_unix`, `closed_unix`, `is_locked`, `time_estimate` FROM `issue` WHERE (repo_id =?) AND (is_pull = 0) AND (pin_order > 0) ORDER BY pin_order
```

I came across a comment
https://github.com/go-gitea/gitea/pull/24406#issuecomment-1527747296
from @delvh , which presents a more reasonable approach. Based on this,
this PR will migrate all issue and pull request pin data from the
`issue` table to the `issue_pin` table. This change benefits larger
Gitea instances by improving scalability and performance.

---------

Co-authored-by: wxiaoguang <wxiaoguang@gmail.com>

2 files changed, 33 insertions, 20 deletions

diff --git a/routers/web/repo/issue_pin.go b/routers/web/repo/issue_pin.go
index d7d3205c37..8d3de90d25 100644
--- a/routers/web/repo/issue_pin.go
+++ b/routers/web/repo/issue_pin.go
@@ -6,6 +6,7 @@ package repo
 import (
 	"net/http"
 
+	"code.gitea.io/gitea/models/db"
 	issues_model "code.gitea.io/gitea/models/issues"
 	"code.gitea.io/gitea/modules/json"
 	"code.gitea.io/gitea/modules/log"
@@ -22,15 +23,29 @@ func IssuePinOrUnpin(ctx *context.Context) {
 	// If we don't do this, it will crash when trying to add the pin event to the comment history
 	err := issue.LoadRepo(ctx)
 	if err != nil {
-		ctx.Status(http.StatusInternalServerError)
-		log.Error(err.Error())
+		ctx.ServerError("LoadRepo", err)
 		return
 	}
 
-	err = issue.PinOrUnpin(ctx, ctx.Doer)
+	// PinOrUnpin pins or unpins a Issue
+	_, err = issues_model.GetIssuePin(ctx, issue)
+	if err != nil && !db.IsErrNotExist(err) {
+		ctx.ServerError("GetIssuePin", err)
+		return
+	}
+
+	if db.IsErrNotExist(err) {
+		err = issues_model.PinIssue(ctx, issue, ctx.Doer)
+	} else {
+		err = issues_model.UnpinIssue(ctx, issue, ctx.Doer)
+	}
+
 	if err != nil {
-		ctx.Status(http.StatusInternalServerError)
-		log.Error(err.Error())
+		if issues_model.IsErrIssueMaxPinReached(err) {
+			ctx.JSONError(ctx.Tr("repo.issues.max_pinned"))
+		} else {
+			ctx.ServerError("Pin/Unpin failed", err)
+		}
 		return
 	}
 
@@ -41,23 +56,20 @@ func IssuePinOrUnpin(ctx *context.Context) {
 func IssueUnpin(ctx *context.Context) {
 	issue, err := issues_model.GetIssueByIndex(ctx, ctx.Repo.Repository.ID, ctx.PathParamInt64("index"))
 	if err != nil {
-		ctx.Status(http.StatusInternalServerError)
-		log.Error(err.Error())
+		ctx.ServerError("GetIssueByIndex", err)
 		return
 	}
 
 	// If we don't do this, it will crash when trying to add the pin event to the comment history
 	err = issue.LoadRepo(ctx)
 	if err != nil {
-		ctx.Status(http.StatusInternalServerError)
-		log.Error(err.Error())
+		ctx.ServerError("LoadRepo", err)
 		return
 	}
 
-	err = issue.Unpin(ctx, ctx.Doer)
+	err = issues_model.UnpinIssue(ctx, issue, ctx.Doer)
 	if err != nil {
-		ctx.Status(http.StatusInternalServerError)
-		log.Error(err.Error())
+		ctx.ServerError("UnpinIssue", err)
 		return
 	}
 
@@ -78,15 +90,13 @@ func IssuePinMove(ctx *context.Context) {
 
 	form := &movePinIssueForm{}
 	if err := json.NewDecoder(ctx.Req.Body).Decode(&form); err != nil {
-		ctx.Status(http.StatusInternalServerError)
-		log.Error(err.Error())
+		ctx.ServerError("Decode", err)
 		return
 	}
 
 	issue, err := issues_model.GetIssueByID(ctx, form.ID)
 	if err != nil {
-		ctx.Status(http.StatusInternalServerError)
-		log.Error(err.Error())
+		ctx.ServerError("GetIssueByID", err)
 		return
 	}
 
@@ -96,10 +106,9 @@ func IssuePinMove(ctx *context.Context) {
 		return
 	}
 
-	err = issue.MovePin(ctx, form.Position)
+	err = issues_model.MovePin(ctx, issue, form.Position)
 	if err != nil {
-		ctx.Status(http.StatusInternalServerError)
-		log.Error(err.Error())
+		ctx.ServerError("MovePin", err)
 		return
 	}
 
diff --git a/routers/web/repo/issue_view.go b/routers/web/repo/issue_view.go
index 06a906b494..37e1b27931 100644
--- a/routers/web/repo/issue_view.go
+++ b/routers/web/repo/issue_view.go
@@ -543,7 +543,11 @@ func preparePullViewDeleteBranch(ctx *context.Context, issue *issues_model.Issue
 
 func prepareIssueViewSidebarPin(ctx *context.Context, issue *issues_model.Issue) {
 	var pinAllowed bool
-	if !issue.IsPinned() {
+	if err := issue.LoadPinOrder(ctx); err != nil {
+		ctx.ServerError("LoadPinOrder", err)
+		return
+	}
+	if issue.PinOrder == 0 {
 		var err error
 		pinAllowed, err = issues_model.IsNewPinAllowed(ctx, issue.RepoID, issue.IsPull)
 		if err != nil {