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>

4 files changed, 13 insertions, 10 deletions

diff --git a/services/convert/issue.go b/services/convert/issue.go
index 62d0a3b3e6..7f386e6293 100644
--- a/services/convert/issue.go
+++ b/services/convert/issue.go
@@ -41,6 +41,9 @@ func toIssue(ctx context.Context, doer *user_model.User, issue *issues_model.Iss
 	if err := issue.LoadAttachments(ctx); err != nil {
 		return &api.Issue{}
 	}
+	if err := issue.LoadPinOrder(ctx); err != nil {
+		return &api.Issue{}
+	}
 
 	apiIssue := &api.Issue{
 		ID:          issue.ID,
@@ -55,7 +58,7 @@ func toIssue(ctx context.Context, doer *user_model.User, issue *issues_model.Iss
 		Comments:    issue.NumComments,
 		Created:     issue.CreatedUnix.AsTime(),
 		Updated:     issue.UpdatedUnix.AsTime(),
-		PinOrder:    issue.PinOrder,
+		PinOrder:    util.Iif(issue.PinOrder == -1, 0, issue.PinOrder), // -1 means loaded with no pin order
 	}
 
 	if issue.Repo != nil {
@@ -122,6 +125,7 @@ func toIssue(ctx context.Context, doer *user_model.User, issue *issues_model.Iss
 // ToIssueList converts an IssueList to API format
 func ToIssueList(ctx context.Context, doer *user_model.User, il issues_model.IssueList) []*api.Issue {
 	result := make([]*api.Issue, len(il))
+	_ = il.LoadPinOrder(ctx)
 	for i := range il {
 		result[i] = ToIssue(ctx, doer, il[i])
 	}
@@ -131,6 +135,7 @@ func ToIssueList(ctx context.Context, doer *user_model.User, il issues_model.Iss
 // ToAPIIssueList converts an IssueList to API format
 func ToAPIIssueList(ctx context.Context, doer *user_model.User, il issues_model.IssueList) []*api.Issue {
 	result := make([]*api.Issue, len(il))
+	_ = il.LoadPinOrder(ctx)
 	for i := range il {
 		result[i] = ToAPIIssue(ctx, doer, il[i])
 	}
diff --git a/services/convert/pull.go b/services/convert/pull.go
index 209d2bd79d..ad4f08fa91 100644
--- a/services/convert/pull.go
+++ b/services/convert/pull.go
@@ -93,7 +93,7 @@ func ToAPIPullRequest(ctx context.Context, pr *issues_model.PullRequest, doer *u
 		Deadline:       apiIssue.Deadline,
 		Created:        pr.Issue.CreatedUnix.AsTimePtr(),
 		Updated:        pr.Issue.UpdatedUnix.AsTimePtr(),
-		PinOrder:       apiIssue.PinOrder,
+		PinOrder:       util.Iif(apiIssue.PinOrder == -1, 0, apiIssue.PinOrder),
 
 		// output "[]" rather than null to align to github outputs
 		RequestedReviewers:      []*api.User{},
@@ -304,6 +304,9 @@ func ToAPIPullRequests(ctx context.Context, baseRepo *repo_model.Repository, prs
 	if err := issueList.LoadAssignees(ctx); err != nil {
 		return nil, err
 	}
+	if err = issueList.LoadPinOrder(ctx); err != nil {
+		return nil, err
+	}
 
 	reviews, err := prs.LoadReviews(ctx)
 	if err != nil {
@@ -368,7 +371,7 @@ func ToAPIPullRequests(ctx context.Context, baseRepo *repo_model.Repository, prs
 			Deadline:       apiIssue.Deadline,
 			Created:        pr.Issue.CreatedUnix.AsTimePtr(),
 			Updated:        pr.Issue.UpdatedUnix.AsTimePtr(),
-			PinOrder:       apiIssue.PinOrder,
+			PinOrder:       util.Iif(apiIssue.PinOrder == -1, 0, apiIssue.PinOrder),
 
 			AllowMaintainerEdit: pr.AllowMaintainerEdit,
 
diff --git a/services/issue/issue.go b/services/issue/issue.go
index 091b7c02d7..586b6031c8 100644
--- a/services/issue/issue.go
+++ b/services/issue/issue.go
@@ -197,13 +197,6 @@ func DeleteIssue(ctx context.Context, doer *user_model.User, gitRepo *git.Reposi
 		}
 	}
 
-	// If the Issue is pinned, we should unpin it before deletion to avoid problems with other pinned Issues
-	if issue.IsPinned() {
-		if err := issue.Unpin(ctx, doer); err != nil {
-			return err
-		}
-	}
-
 	notify_service.DeleteIssue(ctx, doer, issue)
 
 	return nil
@@ -319,6 +312,7 @@ func deleteIssue(ctx context.Context, issue *issues_model.Issue) error {
 		&issues_model.Comment{RefIssueID: issue.ID},
 		&issues_model.IssueDependency{DependencyID: issue.ID},
 		&issues_model.Comment{DependentIssueID: issue.ID},
+		&issues_model.IssuePin{IssueID: issue.ID},
 	); err != nil {
 		return err
 	}
diff --git a/services/repository/delete.go b/services/repository/delete.go
index 2166b4dd5c..fb3fffdca7 100644
--- a/services/repository/delete.go
+++ b/services/repository/delete.go
@@ -158,6 +158,7 @@ func DeleteRepositoryDirectly(ctx context.Context, doer *user_model.User, repoID
 		&actions_model.ActionSchedule{RepoID: repoID},
 		&actions_model.ActionArtifact{RepoID: repoID},
 		&actions_model.ActionRunnerToken{RepoID: repoID},
+		&issues_model.IssuePin{RepoID: repoID},
 	); err != nil {
 		return fmt.Errorf("deleteBeans: %w", err)
 	}