package db

import (
	"context"
	"database/sql"
	"errors"
)

type Issue struct {
	ID        int64
	RepoID    int64
	AuthorID  *int64
	Number    int64
	Title     string
	Body      string
	Status    string
	CreatedAt string
	UpdatedAt string
	EditedAt  *string
	// Joined from users, null when the author was deleted.
	AuthorUsername      *string
	AuthorAvatarVersion *int64
}

// IssueRef is the small lookup result the mutating routes work with.
type IssueRef struct {
	ID       int64
	AuthorID *int64
	Status   string
}

type IssueComment struct {
	ID                  int64
	IssueID             int64
	AuthorID            *int64
	Body                string
	CreatedAt           string
	EditedAt            *string
	AuthorUsername      *string
	AuthorAvatarVersion *int64
}

// Reaction is one row of issue_reactions or patch_reactions.
type Reaction struct {
	ID        int64
	CommentID *int64
	UserID    int64
	Emoji     string
}

// CommentAuth carries the fields needed to authorise a comment edit.
type CommentAuth struct {
	AuthorID *int64
	// Status of the parent issue or patch.
	Status string
}

const issueColumns = `issues.id, issues.repo_id, issues.author_id, issues.number, issues.title,
	issues.body, issues.status, issues.created_at, issues.updated_at, issues.edited_at,
	users.username, users.avatar_version`

func scanIssue(s rowScanner) (*Issue, error) {
	var i Issue
	err := s.Scan(&i.ID, &i.RepoID, &i.AuthorID, &i.Number, &i.Title, &i.Body, &i.Status,
		&i.CreatedAt, &i.UpdatedAt, &i.EditedAt, &i.AuthorUsername, &i.AuthorAvatarVersion)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &i, nil
}

// labelFilter builds the EXISTS clause that keeps only rows carrying one of
// the given labels. linkTable, linkColumn and parentTable are
// "issue_labels", "issue_id", "issues" or the patch equivalents.
func labelFilter(linkTable, linkColumn, parentTable string, labelIDs []int64, args []any) (string, []any) {
	if len(labelIDs) == 0 {
		return "", args
	}
	clause := ` AND EXISTS (SELECT 1 FROM ` + linkTable + ` WHERE ` + linkTable + `.` + linkColumn +
		` = ` + parentTable + `.id AND ` + linkTable + `.label_id IN (` + placeholders(len(labelIDs)) + `))`
	return clause, append(args, int64Args(labelIDs)...)
}

// IssueCounts returns the number of issues per status, honouring the label filter.
func (d *DB) IssueCounts(ctx context.Context, repoID int64, labelIDs []int64) (map[string]int, error) {
	args := []any{repoID}
	filter, args := labelFilter("issue_labels", "issue_id", "issues", labelIDs, args)
	rows, err := d.QueryContext(ctx,
		`SELECT issues.status, COUNT(*) FROM issues WHERE issues.repo_id = ?`+filter+
			` GROUP BY issues.status`, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	counts := map[string]int{}
	for rows.Next() {
		var status string
		var n int
		if err := rows.Scan(&status, &n); err != nil {
			return nil, err
		}
		counts[status] = n
	}
	return counts, rows.Err()
}

// ListIssues returns one page of issues with the given status, newest number first.
func (d *DB) ListIssues(ctx context.Context, repoID int64, status string, labelIDs []int64,
	limit, offset int,
) ([]Issue, error) {
	args := []any{repoID, status}
	filter, args := labelFilter("issue_labels", "issue_id", "issues", labelIDs, args)
	args = append(args, limit, offset)
	rows, err := d.QueryContext(ctx,
		`SELECT `+issueColumns+` FROM issues
		 LEFT JOIN users ON users.id = issues.author_id
		 WHERE issues.repo_id = ? AND issues.status = ?`+filter+
			` ORDER BY issues.number DESC LIMIT ? OFFSET ?`, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Issue
	for rows.Next() {
		i, err := scanIssue(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, *i)
	}
	return out, rows.Err()
}

func (d *DB) IssueByNumber(ctx context.Context, repoID, number int64) (*Issue, error) {
	return scanIssue(d.QueryRowContext(ctx,
		`SELECT `+issueColumns+` FROM issues
		 LEFT JOIN users ON users.id = issues.author_id
		 WHERE issues.repo_id = ? AND issues.number = ?`, repoID, number))
}

func (d *DB) IssueRefByNumber(ctx context.Context, repoID, number int64) (*IssueRef, error) {
	var r IssueRef
	err := d.QueryRowContext(ctx,
		`SELECT id, author_id, status FROM issues WHERE repo_id = ? AND number = ?`,
		repoID, number).Scan(&r.ID, &r.AuthorID, &r.Status)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &r, nil
}

// CreateIssue allocates the next issue number and inserts the issue with its
// labels in one transaction. Only labels belonging to the repo are attached.
func (d *DB) CreateIssue(ctx context.Context, repoID int64, authorID *int64,
	title, body, now string, labelIDs []int64,
) (int64, error) {
	tx, err := d.BeginTx(ctx, nil)
	if err != nil {
		return 0, err
	}
	defer tx.Rollback()

	var number int64
	if err := tx.QueryRowContext(ctx,
		`UPDATE repositories SET issue_seq = issue_seq + 1 WHERE id = ? RETURNING issue_seq`,
		repoID).Scan(&number); err != nil {
		return 0, err
	}
	res, err := tx.ExecContext(ctx,
		`INSERT INTO issues (repo_id, author_id, number, title, body, status, created_at, updated_at)
		 VALUES (?, ?, ?, ?, ?, 'open', ?, ?)`,
		repoID, authorID, number, title, body, now, now)
	if err != nil {
		return 0, err
	}
	issueID, err := res.LastInsertId()
	if err != nil {
		return 0, err
	}
	if err := attachLabels(ctx, tx, "issue_labels", "issue_id", issueID, repoID, labelIDs); err != nil {
		return 0, err
	}
	return number, tx.Commit()
}

// attachLabels links the valid subset of labelIDs to an issue or patch.
func attachLabels(ctx context.Context, tx *sql.Tx, linkTable, linkColumn string,
	parentID, repoID int64, labelIDs []int64,
) error {
	if len(labelIDs) == 0 {
		return nil
	}
	args := append([]any{repoID}, int64Args(labelIDs)...)
	rows, err := tx.QueryContext(ctx,
		`SELECT id FROM labels WHERE repo_id = ? AND id IN (`+placeholders(len(labelIDs))+`)`, args...)
	if err != nil {
		return err
	}
	var valid []int64
	for rows.Next() {
		var id int64
		if err := rows.Scan(&id); err != nil {
			rows.Close()
			return err
		}
		valid = append(valid, id)
	}
	rows.Close()
	if err := rows.Err(); err != nil {
		return err
	}
	for _, id := range valid {
		if _, err := tx.ExecContext(ctx,
			`INSERT INTO `+linkTable+` (`+linkColumn+`, label_id) VALUES (?, ?)
			 ON CONFLICT DO NOTHING`, parentID, id); err != nil {
			return err
		}
	}
	return nil
}

func (d *DB) UpdateIssue(ctx context.Context, id int64, title, body, now string) error {
	_, err := d.ExecContext(ctx,
		`UPDATE issues SET title = ?, body = ?, edited_at = ?, updated_at = ? WHERE id = ?`,
		title, body, now, now, id)
	return err
}

func (d *DB) CompleteIssue(ctx context.Context, id int64, now string) error {
	_, err := d.ExecContext(ctx,
		`UPDATE issues SET status = 'completed', updated_at = ? WHERE id = ?`, now, id)
	return err
}

// ToggleIssueClosed flips an issue between open and closed.
func (d *DB) ToggleIssueClosed(ctx context.Context, id int64, now string) error {
	_, err := d.ExecContext(ctx,
		`UPDATE issues SET status = CASE WHEN status = 'open' THEN 'closed' ELSE 'open' END,
		 updated_at = ? WHERE id = ?`, now, id)
	return err
}

func (d *DB) DeleteIssue(ctx context.Context, id int64) error {
	_, err := d.ExecContext(ctx, `DELETE FROM issues WHERE id = ?`, id)
	return err
}

func (d *DB) ListIssueComments(ctx context.Context, issueID int64) ([]IssueComment, error) {
	rows, err := d.QueryContext(ctx,
		`SELECT issue_comments.id, issue_comments.issue_id, issue_comments.author_id,
		        issue_comments.body, issue_comments.created_at, issue_comments.edited_at,
		        users.username, users.avatar_version
		 FROM issue_comments
		 LEFT JOIN users ON users.id = issue_comments.author_id
		 WHERE issue_comments.issue_id = ?
		 ORDER BY issue_comments.created_at ASC`, issueID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []IssueComment
	for rows.Next() {
		var c IssueComment
		if err := rows.Scan(&c.ID, &c.IssueID, &c.AuthorID, &c.Body, &c.CreatedAt,
			&c.EditedAt, &c.AuthorUsername, &c.AuthorAvatarVersion); err != nil {
			return nil, err
		}
		out = append(out, c)
	}
	return out, rows.Err()
}

// AddIssueComment inserts a comment and bumps the issue's updated_at together.
func (d *DB) AddIssueComment(ctx context.Context, issueID int64, authorID *int64, body, now string) error {
	tx, err := d.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback()
	if _, err := tx.ExecContext(ctx,
		`INSERT INTO issue_comments (issue_id, author_id, body, created_at) VALUES (?, ?, ?, ?)`,
		issueID, authorID, body, now); err != nil {
		return err
	}
	if _, err := tx.ExecContext(ctx,
		`UPDATE issues SET updated_at = ? WHERE id = ?`, now, issueID); err != nil {
		return err
	}
	return tx.Commit()
}

func (d *DB) UpdateIssueComment(ctx context.Context, id int64, body, now string) error {
	_, err := d.ExecContext(ctx,
		`UPDATE issue_comments SET body = ?, edited_at = ? WHERE id = ?`, body, now, id)
	return err
}

// IssueCommentAuth loads the comment author and parent status in one query,
// scoped to the repo so a comment from another repo cannot be edited.
func (d *DB) IssueCommentAuth(ctx context.Context, commentID, repoID int64) (*CommentAuth, error) {
	var a CommentAuth
	err := d.QueryRowContext(ctx,
		`SELECT issue_comments.author_id, issues.status
		 FROM issue_comments
		 JOIN issues ON issues.id = issue_comments.issue_id
		 WHERE issue_comments.id = ? AND issues.repo_id = ?`,
		commentID, repoID).Scan(&a.AuthorID, &a.Status)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &a, nil
}

func (d *DB) ListIssueReactions(ctx context.Context, issueID int64) ([]Reaction, error) {
	rows, err := d.QueryContext(ctx,
		`SELECT id, comment_id, user_id, emoji FROM issue_reactions WHERE issue_id = ?`, issueID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanReactions(rows)
}

func scanReactions(rows *sql.Rows) ([]Reaction, error) {
	var out []Reaction
	for rows.Next() {
		var r Reaction
		if err := rows.Scan(&r.ID, &r.CommentID, &r.UserID, &r.Emoji); err != nil {
			return nil, err
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

// ToggleIssueReaction keeps one reaction per user per target: the same emoji
// removes it, a different emoji replaces it.
func (d *DB) ToggleIssueReaction(ctx context.Context, issueID int64, commentID *int64,
	userID int64, emoji string,
) error {
	return d.toggleReaction(ctx, "issue_reactions", "issue_comments", "issue_id", issueID, commentID, userID, emoji)
}

// ErrCommentNotFound reports a reaction on a comment that is not part of the
// target issue or patch.
var ErrCommentNotFound = errors.New("comment not found")

func (d *DB) toggleReaction(ctx context.Context, table, commentTable, parentColumn string, parentID int64,
	commentID *int64, userID int64, emoji string,
) error {
	tx, err := d.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback()

	if commentID != nil {
		var one int
		err := tx.QueryRowContext(ctx,
			`SELECT 1 FROM `+commentTable+` WHERE id = ? AND `+parentColumn+` = ?`,
			*commentID, parentID).Scan(&one)
		if errors.Is(err, sql.ErrNoRows) {
			return ErrCommentNotFound
		}
		if err != nil {
			return err
		}
	}

	commentCond := `comment_id IS NULL`
	args := []any{parentID}
	if commentID != nil {
		commentCond = `comment_id = ?`
		args = append(args, *commentID)
	}
	args = append(args, userID)

	var existingID int64
	var existingEmoji string
	err = tx.QueryRowContext(ctx,
		`SELECT id, emoji FROM `+table+` WHERE `+parentColumn+` = ? AND `+commentCond+` AND user_id = ?`,
		args...).Scan(&existingID, &existingEmoji)
	switch {
	case errors.Is(err, sql.ErrNoRows):
		_, err = tx.ExecContext(ctx,
			`INSERT INTO `+table+` (`+parentColumn+`, comment_id, user_id, emoji) VALUES (?, ?, ?, ?)`,
			parentID, commentID, userID, emoji)
	case err != nil:
		return err
	case existingEmoji == emoji:
		_, err = tx.ExecContext(ctx, `DELETE FROM `+table+` WHERE id = ?`, existingID)
	default:
		_, err = tx.ExecContext(ctx, `UPDATE `+table+` SET emoji = ? WHERE id = ?`, emoji, existingID)
	}
	if err != nil {
		return err
	}
	return tx.Commit()
}

// IssueLabels returns the labels attached to one issue.
func (d *DB) IssueLabels(ctx context.Context, issueID int64) ([]Label, error) {
	rows, err := d.QueryContext(ctx,
		`SELECT labels.id, labels.repo_id, labels.name, labels.color, labels.created_at
		 FROM issue_labels
		 JOIN labels ON labels.id = issue_labels.label_id
		 WHERE issue_labels.issue_id = ?`, issueID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanLabels(rows)
}

// IssueLabelsByIssue batch-loads labels for a list view.
func (d *DB) IssueLabelsByIssue(ctx context.Context, issueIDs []int64) (map[int64][]Label, error) {
	return d.labelsByParent(ctx, "issue_labels", "issue_id", issueIDs)
}

func (d *DB) labelsByParent(ctx context.Context, linkTable, linkColumn string,
	parentIDs []int64,
) (map[int64][]Label, error) {
	out := map[int64][]Label{}
	if len(parentIDs) == 0 {
		return out, nil
	}
	rows, err := d.QueryContext(ctx,
		`SELECT `+linkTable+`.`+linkColumn+`, labels.id, labels.repo_id, labels.name,
		        labels.color, labels.created_at
		 FROM `+linkTable+`
		 JOIN labels ON labels.id = `+linkTable+`.label_id
		 WHERE `+linkTable+`.`+linkColumn+` IN (`+placeholders(len(parentIDs))+`)`,
		int64Args(parentIDs)...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		var parentID int64
		var l Label
		if err := rows.Scan(&parentID, &l.ID, &l.RepoID, &l.Name, &l.Color, &l.CreatedAt); err != nil {
			return nil, err
		}
		out[parentID] = append(out[parentID], l)
	}
	return out, rows.Err()
}

func (d *DB) AddIssueLabel(ctx context.Context, issueID, labelID int64) error {
	_, err := d.ExecContext(ctx,
		`INSERT INTO issue_labels (issue_id, label_id) VALUES (?, ?) ON CONFLICT DO NOTHING`,
		issueID, labelID)
	return err
}

func (d *DB) RemoveIssueLabel(ctx context.Context, issueID, labelID int64) error {
	_, err := d.ExecContext(ctx,
		`DELETE FROM issue_labels WHERE issue_id = ? AND label_id = ?`, issueID, labelID)
	return err
}