package store

import (
	"context"
	"database/sql"
	"errors"
	"regexp"
	"sort"
	"strconv"
	"strings"
	"time"
	"unicode"

	"gitlab.com/Timfa/bluegit/internal/i18n"
)

// Label is een label binnen één repository.
type Label struct {
	ID     int64
	RepoID int64
	Name   string
	Color  string
}

// Milestone is een mijlpaal binnen één repository.
type Milestone struct {
	ID     int64
	RepoID int64
	Title  string
	DueOn  string
	State  string
}

// Issue is één issue, genummerd per repository.
type Issue struct {
	ID           int64
	RepoID       int64
	Number       int64
	Title        string
	Description  string
	AuthorID     int64
	Author       string
	Status       string
	Confidential bool
	MilestoneID  int64
	Milestone    string
	DueOn        string
	CreatedAt    time.Time
	UpdatedAt    time.Time
	ClosedAt     time.Time
	Labels       []Label
	AssigneeIDs  []int64
	Assignees    []string
}

// IssueNote is een reactie op een issue.
type IssueNote struct {
	ID        int64
	RepoID    int64
	IssueID   int64
	AuthorID  int64
	Author    string
	Body      string
	CreatedAt time.Time
}

// IssueFilter beperkt de lijst. State is open, closed of all.
type IssueFilter struct {
	State    string
	Query    string
	Label    string
	Assignee string
	ViewerID int64
	Triage   bool
}

// NewIssue is de invoer voor een nieuw issue.
type NewIssue struct {
	Title        string
	Description  string
	Confidential bool
	MilestoneID  int64
	DueOn        string
	LabelIDs     []int64
	AssigneeIDs  []int64
}

var labelColor = regexp.MustCompile(`^#[0-9a-fA-F]{6}$`)

// ValidDue eist leeg of YYYY-MM-DD.
func ValidDue(s string) error {
	s = strings.TrimSpace(s)
	if s == "" {
		return nil
	}
	if _, err := time.Parse("2006-01-02", s); err != nil {
		return i18n.E("err.issueDue")
	}
	return nil
}

// ValidLabelName eist letters, cijfers, spaties, streepjes of underscores.
func ValidLabelName(s string) error {
	s = strings.TrimSpace(s)
	if s == "" || len([]rune(s)) > 50 {
		return i18n.E("err.labelName")
	}
	for _, r := range s {
		if r == ' ' || r == '-' || r == '_' || unicode.IsLetter(r) || unicode.IsDigit(r) {
			continue
		}
		return i18n.E("err.labelName")
	}
	return nil
}

// NormalizeColor vult de standaardkleur aan of keurt een hexkleur goed.
func NormalizeColor(s string) (string, error) {
	s = strings.TrimSpace(s)
	if s == "" {
		return "#428fdc", nil
	}
	if !labelColor.MatchString(s) {
		return "", i18n.E("err.labelColor")
	}
	return strings.ToLower(s), nil
}

// ValidMilestoneTitle eist een korte titel.
func ValidMilestoneTitle(s string) error {
	s = strings.TrimSpace(s)
	if s == "" || len([]rune(s)) > 80 {
		return i18n.E("err.milestoneName")
	}
	for _, r := range s {
		if r == ' ' || r == '-' || r == '_' || r == '.' || unicode.IsLetter(r) || unicode.IsDigit(r) {
			continue
		}
		return i18n.E("err.milestoneName")
	}
	return nil
}

func limitIssueText(s string) error {
	if len([]rune(s)) > 20000 {
		return i18n.E("err.issueBody")
	}
	return nil
}

// UpsertLabel maakt een label of werkt de kleur bij.
func (s *Store) UpsertLabel(ctx context.Context, repoID int64, name, color string) (Label, error) {
	name = strings.TrimSpace(name)
	if err := ValidLabelName(name); err != nil {
		return Label{}, err
	}
	color, err := NormalizeColor(color)
	if err != nil {
		return Label{}, err
	}
	if _, err := s.db.ExecContext(ctx, `INSERT INTO labels (repo_id, name, color) VALUES (?, ?, ?)
		ON CONFLICT(repo_id, name) DO UPDATE SET color = excluded.color`, repoID, name, color); err != nil {
		return Label{}, err
	}
	return s.LabelByName(ctx, repoID, name)
}

// LabelByName leest één label.
func (s *Store) LabelByName(ctx context.Context, repoID int64, name string) (Label, error) {
	var l Label
	err := s.db.QueryRowContext(ctx, `SELECT id, repo_id, name, color FROM labels WHERE repo_id = ? AND name = ?`, repoID, strings.TrimSpace(name)).
		Scan(&l.ID, &l.RepoID, &l.Name, &l.Color)
	if errors.Is(err, sql.ErrNoRows) {
		return Label{}, errNotFound
	}
	return l, err
}

// ListLabels geeft de labels op naam.
func (s *Store) ListLabels(ctx context.Context, repoID int64) ([]Label, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT id, repo_id, name, color FROM labels WHERE repo_id = ? ORDER BY name`, repoID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := []Label{}
	for rows.Next() {
		var l Label
		if err := rows.Scan(&l.ID, &l.RepoID, &l.Name, &l.Color); err != nil {
			return nil, err
		}
		out = append(out, l)
	}
	return out, rows.Err()
}

// UpsertMilestone maakt een mijlpaal of werkt de datum bij.
func (s *Store) UpsertMilestone(ctx context.Context, repoID int64, title, due string) (Milestone, error) {
	title = strings.TrimSpace(title)
	if err := ValidMilestoneTitle(title); err != nil {
		return Milestone{}, err
	}
	due = strings.TrimSpace(due)
	if err := ValidDue(due); err != nil {
		return Milestone{}, err
	}
	if _, err := s.db.ExecContext(ctx, `INSERT INTO milestones (repo_id, title, due_on, state) VALUES (?, ?, ?, 'open')
		ON CONFLICT(repo_id, title) DO UPDATE SET due_on = excluded.due_on`, repoID, title, due); err != nil {
		return Milestone{}, err
	}
	return s.MilestoneByTitle(ctx, repoID, title)
}

// MilestoneByTitle leest één mijlpaal.
func (s *Store) MilestoneByTitle(ctx context.Context, repoID int64, title string) (Milestone, error) {
	var m Milestone
	err := s.db.QueryRowContext(ctx, `SELECT id, repo_id, title, due_on, state FROM milestones WHERE repo_id = ? AND title = ?`, repoID, strings.TrimSpace(title)).
		Scan(&m.ID, &m.RepoID, &m.Title, &m.DueOn, &m.State)
	if errors.Is(err, sql.ErrNoRows) {
		return Milestone{}, errNotFound
	}
	return m, err
}

// ListMilestones geeft de mijlpalen op titel.
func (s *Store) ListMilestones(ctx context.Context, repoID int64) ([]Milestone, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT id, repo_id, title, due_on, state FROM milestones WHERE repo_id = ? ORDER BY title`, repoID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := []Milestone{}
	for rows.Next() {
		var m Milestone
		if err := rows.Scan(&m.ID, &m.RepoID, &m.Title, &m.DueOn, &m.State); err != nil {
			return nil, err
		}
		out = append(out, m)
	}
	return out, rows.Err()
}

const issueSelect = `SELECT i.id, i.repo_id, i.number, i.title, i.description, i.author_id, u.username,
i.status, i.confidential, i.milestone_id, COALESCE(m.title, ''), i.due_on, i.created_at, i.updated_at, i.closed_at,
COALESCE((SELECT group_concat(l.id || char(31) || l.name || char(31) || l.color, char(30)) FROM issue_labels il JOIN labels l ON l.id = il.label_id WHERE il.issue_id = i.id), ''),
COALESCE((SELECT group_concat(au.id || char(31) || au.username, char(30)) FROM issue_assignees ia JOIN users au ON au.id = ia.user_id WHERE ia.issue_id = i.id), '')
FROM issues i
JOIN users u ON u.id = i.author_id
LEFT JOIN milestones m ON m.id = i.milestone_id AND m.id <> 0`

// CreateIssue nummert per repository en zet labels en toewijzingen.
func (s *Store) CreateIssue(ctx context.Context, repoID, authorID int64, in NewIssue) (Issue, error) {
	title := trim(strings.TrimSpace(in.Title), 200)
	if title == "" {
		return Issue{}, i18n.E("err.issueTitle")
	}
	if err := limitIssueText(in.Description); err != nil {
		return Issue{}, err
	}
	in.DueOn = strings.TrimSpace(in.DueOn)
	if err := ValidDue(in.DueOn); err != nil {
		return Issue{}, err
	}
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return Issue{}, err
	}
	defer func() { _ = tx.Rollback() }()
	if err := ensureIssueRefs(ctx, tx, repoID, in.MilestoneID, in.LabelIDs, in.AssigneeIDs); err != nil {
		return Issue{}, err
	}
	var n int64
	if err := tx.QueryRowContext(ctx, `SELECT COALESCE(MAX(number), 0) FROM issues WHERE repo_id = ?`, repoID).Scan(&n); err != nil {
		return Issue{}, err
	}
	ts := now()
	conf := 0
	if in.Confidential {
		conf = 1
	}
	res, err := tx.ExecContext(ctx, `INSERT INTO issues (repo_id, number, title, description, author_id, status, confidential, milestone_id, due_on, created_at, updated_at)
		VALUES (?, ?, ?, ?, ?, 'open', ?, ?, ?, ?, ?)`, repoID, n+1, title, in.Description, authorID, conf, in.MilestoneID, in.DueOn, ts, ts)
	if err != nil {
		return Issue{}, err
	}
	id, _ := res.LastInsertId()
	if err := replaceIssueLinks(ctx, tx, id, in.LabelIDs, in.AssigneeIDs); err != nil {
		return Issue{}, err
	}
	if err := tx.Commit(); err != nil {
		return Issue{}, err
	}
	return s.IssueByNumber(ctx, repoID, n+1)
}

// IssueByNumber leest #nummer.
func (s *Store) IssueByNumber(ctx context.Context, repoID, number int64) (Issue, error) {
	row := s.db.QueryRowContext(ctx, issueSelect+` WHERE i.repo_id = ? AND i.number = ?`, repoID, number)
	return scanIssue(row)
}

// ListIssues geeft issues, nieuwste nummer eerst.
// Zonder triage blijven andermans vertrouwelijke issues weg.
func (s *Store) ListIssues(ctx context.Context, repoID int64, f IssueFilter) ([]Issue, error) {
	q := issueSelect + ` WHERE i.repo_id = ?`
	args := []any{repoID}
	if !f.Triage {
		q += ` AND (i.confidential = 0 OR i.author_id = ?)`
		args = append(args, f.ViewerID)
	}
	switch f.State {
	case "closed":
		q += ` AND i.status = 'closed'`
	case "all":
	default:
		q += ` AND i.status = 'open'`
	}
	if strings.TrimSpace(f.Query) != "" {
		q += ` AND (instr(lower(i.title), lower(?)) > 0 OR instr(lower(i.description), lower(?)) > 0)`
		args = append(args, strings.TrimSpace(f.Query), strings.TrimSpace(f.Query))
	}
	if strings.TrimSpace(f.Label) != "" {
		q += ` AND EXISTS (SELECT 1 FROM issue_labels il JOIN labels l ON l.id = il.label_id WHERE il.issue_id = i.id AND l.name = ?)`
		args = append(args, strings.TrimSpace(f.Label))
	}
	if strings.TrimSpace(f.Assignee) != "" {
		q += ` AND EXISTS (SELECT 1 FROM issue_assignees ia JOIN users au ON au.id = ia.user_id WHERE ia.issue_id = i.id AND au.username = ?)`
		args = append(args, strings.ToLower(strings.TrimSpace(f.Assignee)))
	}
	q += ` ORDER BY i.number DESC`
	rows, err := s.db.QueryContext(ctx, q, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := []Issue{}
	for rows.Next() {
		iss, err := scanIssue(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, iss)
	}
	return out, rows.Err()
}

// UpdateIssueText wijzigt titel en beschrijving.
func (s *Store) UpdateIssueText(ctx context.Context, id int64, title, description string) error {
	title = trim(strings.TrimSpace(title), 200)
	if title == "" {
		return i18n.E("err.issueTitle")
	}
	if err := limitIssueText(description); err != nil {
		return err
	}
	_, err := s.db.ExecContext(ctx, `UPDATE issues SET title = ?, description = ?, updated_at = ? WHERE id = ?`, title, description, now(), id)
	return err
}

// SetIssueMeta wijzigt vertrouwelijkheid, mijlpaal en vervaldatum.
func (s *Store) SetIssueMeta(ctx context.Context, repoID, id, milestoneID int64, confidential bool, due string) error {
	due = strings.TrimSpace(due)
	if err := ValidDue(due); err != nil {
		return err
	}
	if err := ensureIssueRefs(ctx, s.db, repoID, milestoneID, nil, nil); err != nil {
		return err
	}
	flag := 0
	if confidential {
		flag = 1
	}
	_, err := s.db.ExecContext(ctx, `UPDATE issues SET confidential = ?, milestone_id = ?, due_on = ?, updated_at = ? WHERE id = ?`, flag, milestoneID, due, now(), id)
	return err
}

// SetIssueLabels vervangt de labels van een issue.
func (s *Store) SetIssueLabels(ctx context.Context, repoID, id int64, labelIDs []int64) error {
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer func() { _ = tx.Rollback() }()
	if err := ensureIssueRefs(ctx, tx, repoID, 0, labelIDs, nil); err != nil {
		return err
	}
	if _, err := tx.ExecContext(ctx, `DELETE FROM issue_labels WHERE issue_id = ?`, id); err != nil {
		return err
	}
	for _, lid := range labelIDs {
		if _, err := tx.ExecContext(ctx, `INSERT INTO issue_labels (issue_id, label_id) VALUES (?, ?)`, id, lid); err != nil {
			return err
		}
	}
	if _, err := tx.ExecContext(ctx, `UPDATE issues SET updated_at = ? WHERE id = ?`, now(), id); err != nil {
		return err
	}
	return tx.Commit()
}

// SetIssueAssignees vervangt de toewijzingen.
func (s *Store) SetIssueAssignees(ctx context.Context, repoID, id int64, userIDs []int64) error {
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer func() { _ = tx.Rollback() }()
	if err := ensureIssueRefs(ctx, tx, repoID, 0, nil, userIDs); err != nil {
		return err
	}
	if _, err := tx.ExecContext(ctx, `DELETE FROM issue_assignees WHERE issue_id = ?`, id); err != nil {
		return err
	}
	for _, uid := range userIDs {
		if _, err := tx.ExecContext(ctx, `INSERT INTO issue_assignees (issue_id, user_id) VALUES (?, ?)`, id, uid); err != nil {
			return err
		}
	}
	if _, err := tx.ExecContext(ctx, `UPDATE issues SET updated_at = ? WHERE id = ?`, now(), id); err != nil {
		return err
	}
	return tx.Commit()
}

// SetIssueState zet open of closed. changed is onwaar als de status al zo was.
func (s *Store) SetIssueState(ctx context.Context, id int64, status string) (bool, error) {
	if status != "open" && status != "closed" {
		return false, i18n.E("err.issueState")
	}
	closed := ""
	if status == "closed" {
		closed = now()
	}
	res, err := s.db.ExecContext(ctx, `UPDATE issues SET status = ?, closed_at = ?, updated_at = ? WHERE id = ? AND status <> ?`, status, closed, now(), id, status)
	if err != nil {
		return false, err
	}
	n, _ := res.RowsAffected()
	return n > 0, nil
}

// DeleteIssue verwijdert een issue met reacties, labels en koppelingen.
func (s *Store) DeleteIssue(ctx context.Context, repoID, id int64) error {
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer func() { _ = tx.Rollback() }()
	for _, q := range []string{
		`DELETE FROM issue_notes WHERE issue_id = ?`,
		`DELETE FROM issue_labels WHERE issue_id = ?`,
		`DELETE FROM issue_assignees WHERE issue_id = ?`,
	} {
		if _, err := tx.ExecContext(ctx, q, id); err != nil {
			return err
		}
	}
	if _, err := tx.ExecContext(ctx, `DELETE FROM issue_links WHERE a = ? OR b = ?`, id, id); err != nil {
		return err
	}
	res, err := tx.ExecContext(ctx, `DELETE FROM issues WHERE id = ? AND repo_id = ?`, id, repoID)
	if err != nil {
		return err
	}
	n, _ := res.RowsAffected()
	if n == 0 {
		return errNotFound
	}
	return tx.Commit()
}

// AddIssueNote bewaart een reactie.
func (s *Store) AddIssueNote(ctx context.Context, repoID, issueID, authorID int64, body string) (IssueNote, error) {
	body = strings.TrimSpace(body)
	if body == "" {
		return IssueNote{}, i18n.E("err.issueNote")
	}
	if err := limitIssueText(body); err != nil {
		return IssueNote{}, err
	}
	ts := now()
	res, err := s.db.ExecContext(ctx, `INSERT INTO issue_notes (repo_id, issue_id, author_id, body, created_at) VALUES (?, ?, ?, ?, ?)`, repoID, issueID, authorID, body, ts)
	if err != nil {
		return IssueNote{}, err
	}
	_, _ = s.db.ExecContext(ctx, `UPDATE issues SET updated_at = ? WHERE id = ?`, ts, issueID)
	id, _ := res.LastInsertId()
	return s.issueNote(ctx, id)
}

// ListIssueNotes geeft reacties, oudste eerst.
func (s *Store) ListIssueNotes(ctx context.Context, issueID int64) ([]IssueNote, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT n.id, n.repo_id, n.issue_id, n.author_id, u.username, n.body, n.created_at
		FROM issue_notes n JOIN users u ON u.id = n.author_id WHERE n.issue_id = ? ORDER BY n.id`, issueID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := []IssueNote{}
	for rows.Next() {
		n, err := scanIssueNote(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, n)
	}
	return out, rows.Err()
}

// LinkIssues koppelt twee issues in beide richtingen.
func (s *Store) LinkIssues(ctx context.Context, repoID, a, b int64) error {
	if a == 0 || b == 0 || a == b {
		return nil
	}
	_, err := s.db.ExecContext(ctx, `INSERT OR IGNORE INTO issue_links (repo_id, a, b) VALUES (?, ?, ?), (?, ?, ?)`, repoID, a, b, repoID, b, a)
	return err
}

// RelatedIssues geeft de gekoppelde issues.
func (s *Store) RelatedIssues(ctx context.Context, repoID, issueID int64) ([]Issue, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT b FROM issue_links WHERE repo_id = ? AND a = ? ORDER BY b`, repoID, issueID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var ids []int64
	for rows.Next() {
		var id int64
		if err := rows.Scan(&id); err != nil {
			return nil, err
		}
		ids = append(ids, id)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	out := []Issue{}
	for _, id := range ids {
		iss, err := s.issueByID(ctx, repoID, id)
		if err != nil {
			continue
		}
		out = append(out, iss)
	}
	return out, nil
}

func (s *Store) issueByID(ctx context.Context, repoID, id int64) (Issue, error) {
	row := s.db.QueryRowContext(ctx, issueSelect+` WHERE i.repo_id = ? AND i.id = ?`, repoID, id)
	return scanIssue(row)
}

// IssueByID leest een issue op de interne id.
func (s *Store) IssueByID(ctx context.Context, repoID, id int64) (Issue, error) {
	return s.issueByID(ctx, repoID, id)
}

// IssueNoteByID leest een reactie in deze repository.
func (s *Store) IssueNoteByID(ctx context.Context, repoID, id int64) (IssueNote, error) {
	n, err := s.issueNote(ctx, id)
	if err != nil {
		return IssueNote{}, err
	}
	if n.RepoID != repoID {
		return IssueNote{}, errNotFound
	}
	return n, nil
}

// UpdateIssueNoteBody vervangt de tekst van een issue-reactie.
func (s *Store) UpdateIssueNoteBody(ctx context.Context, repoID, id int64, body string) error {
	if strings.TrimSpace(body) == "" {
		return i18n.E("err.issueNote")
	}
	if err := limitIssueText(body); err != nil {
		return err
	}
	res, err := s.db.ExecContext(ctx, `UPDATE issue_notes SET body = ? WHERE id = ? AND repo_id = ?`, body, id, repoID)
	if err != nil {
		return err
	}
	n, _ := res.RowsAffected()
	if n == 0 {
		return errNotFound
	}
	return nil
}

func (s *Store) issueNote(ctx context.Context, id int64) (IssueNote, error) {
	row := s.db.QueryRowContext(ctx, `SELECT n.id, n.repo_id, n.issue_id, n.author_id, u.username, n.body, n.created_at
		FROM issue_notes n JOIN users u ON u.id = n.author_id WHERE n.id = ?`, id)
	return scanIssueNote(row)
}

type rowScanner interface {
	Scan(dest ...any) error
}

func scanIssue(sc rowScanner) (Issue, error) {
	var iss Issue
	var created, updated, closed, packedLabels, packedUsers string
	var conf int
	if err := sc.Scan(&iss.ID, &iss.RepoID, &iss.Number, &iss.Title, &iss.Description, &iss.AuthorID, &iss.Author,
		&iss.Status, &conf, &iss.MilestoneID, &iss.Milestone, &iss.DueOn, &created, &updated, &closed, &packedLabels, &packedUsers); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return Issue{}, errNotFound
		}
		return Issue{}, err
	}
	iss.Confidential = conf == 1
	iss.CreatedAt = parseTime(created)
	iss.UpdatedAt = parseTime(updated)
	iss.ClosedAt = parseTime(closed)
	iss.Labels = parsePackedLabels(packedLabels)
	iss.AssigneeIDs, iss.Assignees = parsePackedUsers(packedUsers)
	return iss, nil
}

func scanIssueNote(sc rowScanner) (IssueNote, error) {
	var n IssueNote
	var created string
	if err := sc.Scan(&n.ID, &n.RepoID, &n.IssueID, &n.AuthorID, &n.Author, &n.Body, &created); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return IssueNote{}, errNotFound
		}
		return IssueNote{}, err
	}
	n.CreatedAt = parseTime(created)
	return n, nil
}

func parsePackedLabels(raw string) []Label {
	out := []Label{}
	if raw == "" {
		return out
	}
	for _, part := range strings.Split(raw, "\x1e") {
		bits := strings.Split(part, "\x1f")
		if len(bits) != 3 {
			continue
		}
		id, _ := strconv.ParseInt(bits[0], 10, 64)
		out = append(out, Label{ID: id, Name: bits[1], Color: bits[2]})
	}
	sort.Slice(out, func(i, j int) bool { return out[i].Name < out[j].Name })
	return out
}

func parsePackedUsers(raw string) ([]int64, []string) {
	type pair struct {
		id   int64
		name string
	}
	var pairs []pair
	if raw != "" {
		for _, part := range strings.Split(raw, "\x1e") {
			bits := strings.SplitN(part, "\x1f", 2)
			if len(bits) != 2 {
				continue
			}
			id, _ := strconv.ParseInt(bits[0], 10, 64)
			pairs = append(pairs, pair{id, bits[1]})
		}
	}
	sort.Slice(pairs, func(i, j int) bool { return pairs[i].name < pairs[j].name })
	ids := []int64{}
	names := []string{}
	for _, p := range pairs {
		ids = append(ids, p.id)
		names = append(names, p.name)
	}
	return ids, names
}

type execQuerier interface {
	ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
	QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
}

func ensureIssueRefs(ctx context.Context, q execQuerier, repoID, milestoneID int64, labelIDs, userIDs []int64) error {
	if milestoneID != 0 {
		var n int
		if err := q.QueryRowContext(ctx, `SELECT COUNT(*) FROM milestones WHERE id = ? AND repo_id = ?`, milestoneID, repoID).Scan(&n); err != nil {
			return err
		}
		if n != 1 {
			return i18n.E("err.milestone")
		}
	}
	for _, id := range labelIDs {
		var n int
		if err := q.QueryRowContext(ctx, `SELECT COUNT(*) FROM labels WHERE id = ? AND repo_id = ?`, id, repoID).Scan(&n); err != nil {
			return err
		}
		if n != 1 {
			return i18n.E("err.labelMissing", id)
		}
	}
	for _, id := range userIDs {
		var name string
		err := q.QueryRowContext(ctx, `SELECT username FROM users WHERE id = ?`, id).Scan(&name)
		if errors.Is(err, sql.ErrNoRows) {
			return i18n.E("err.assignee", id)
		}
		if err != nil {
			return err
		}
	}
	return nil
}

func replaceIssueLinks(ctx context.Context, tx *sql.Tx, issueID int64, labelIDs, userIDs []int64) error {
	for _, id := range labelIDs {
		if _, err := tx.ExecContext(ctx, `INSERT OR IGNORE INTO issue_labels (issue_id, label_id) VALUES (?, ?)`, issueID, id); err != nil {
			return err
		}
	}
	for _, id := range userIDs {
		if _, err := tx.ExecContext(ctx, `INSERT OR IGNORE INTO issue_assignees (issue_id, user_id) VALUES (?, ?)`, issueID, id); err != nil {
			return err
		}
	}
	return nil
}
