package db

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

// Pull is a row in the pulls table. AuthorID is NULL for guest-authored
// rows; Pending marks guest content awaiting owner moderation.
type Pull struct {
	ID          int64
	RepoID      int64
	Number      int64
	Title       string
	Body        string
	Base        string
	Head        string
	State       string
	Pending     bool
	AuthorID    sql.NullInt64
	AuthorName  string
	AuthorEmail string
	CreatedAt   int64
	ClosedAt    sql.NullInt64
	MergedAt    sql.NullInt64
	MergeCommit string
}

// PullComment is a row in the pull_comments table.
type PullComment struct {
	ID          int64
	PullID      int64
	Body        string
	Pending     bool
	AuthorID    sql.NullInt64
	AuthorName  string
	AuthorEmail string
	CreatedAt   int64
}

const pullColumns = `SELECT id, repo_id, number, title, body, base, head, state,
	pending, author_id, author_name, author_email, created_at, closed_at,
	merged_at, merge_commit FROM pulls WHERE `

// CreatePull inserts a pull request with a per-repo number of MAX(number)+1,
// computed atomically inside the INSERT. authorID is 0 for a guest.
func CreatePull(database *sql.DB, repoID int64, title, body, base, head string, authorID int64, authorName, authorEmail string, pending bool) (Pull, error) {
	const insert = `INSERT INTO pulls (repo_id, number, title, body, base, head, pending, author_id, author_name, author_email)
		 VALUES (?, (SELECT COALESCE(MAX(number), 0) + 1 FROM pulls WHERE repo_id = ?), ?, ?, ?, ?, ?, ?, ?, ?)`
	args := []any{repoID, repoID, title, body, base, head, boolToInt(pending), nullableID(authorID), authorName, authorEmail}
	// A concurrent insert can claim the computed number; retry the insert once
	// (the subselect then recomputes MAX+1).
	id, err := execLastID(database, insert, args...)
	if err != nil && isUniqueViolation(err) {
		id, err = execLastID(database, insert, args...)
	}
	if err != nil {
		return Pull{}, fmt.Errorf("create pull: %w", err)
	}
	return GetPullByID(database, id)
}

// GetPullByID looks up a pull request by primary key.
func GetPullByID(database *sql.DB, id int64) (Pull, error) {
	return getPull(database, pullColumns+`id = ?`, id)
}

// GetPullByNumber looks up a pull request by its per-repo number.
func GetPullByNumber(database *sql.DB, repoID, number int64) (Pull, error) {
	return getPull(database, pullColumns+`repo_id = ? AND number = ?`, repoID, number)
}

// scanPull reads one pulls row (see pullColumns) into p.
func scanPull(s rowScanner, p *Pull) error {
	var pending int
	if err := s.Scan(
		&p.ID, &p.RepoID, &p.Number, &p.Title, &p.Body, &p.Base, &p.Head,
		&p.State, &pending, &p.AuthorID, &p.AuthorName, &p.AuthorEmail,
		&p.CreatedAt, &p.ClosedAt, &p.MergedAt, &p.MergeCommit); err != nil {
		return err
	}
	p.Pending = pending != 0
	return nil
}

func getPull(database *sql.DB, query string, args ...any) (Pull, error) {
	var p Pull
	err := scanPull(database.QueryRow(query, args...), &p)
	if errors.Is(err, sql.ErrNoRows) {
		return Pull{}, fmt.Errorf("pull: %w", ErrNotFound)
	}
	if err != nil {
		return Pull{}, fmt.Errorf("get pull: %w", err)
	}
	return p, nil
}

// ListPulls returns a repo's pull requests. includePending must be true only
// for the owner; state, when non-empty, filters to 'open', 'closed', or
// 'merged'. Pending rows sort first so the owner sees what needs review.
func ListPulls(database *sql.DB, repoID int64, includePending bool, state string) ([]Pull, error) {
	query := pullColumns + `repo_id = ?`
	args := []any{repoID}
	if !includePending {
		query += ` AND pending = 0`
	}
	switch state {
	case "open", "closed", "merged":
		query += ` AND state = ?`
		args = append(args, state)
	}
	query += ` ORDER BY pending DESC, number DESC`

	pulls, err := listQuery(database, query, args, scanPull)
	if err != nil {
		return nil, fmt.Errorf("list pulls: %w", err)
	}
	return pulls, nil
}

// SetPullState opens or closes a pull request (closed_at tracks the close
// time). Scoped to the repo so a number cannot cross repos.
func SetPullState(database *sql.DB, repoID, number int64, state string) error {
	if state != "open" && state != "closed" {
		return fmt.Errorf("set pull state %q: invalid state", state)
	}
	query := `UPDATE pulls SET state = ?, closed_at = NULL WHERE repo_id = ? AND number = ?`
	args := []any{state, repoID, number}
	if state == "closed" {
		query = `UPDATE pulls SET state = 'closed', closed_at = unixepoch() WHERE repo_id = ? AND number = ?`
		args = []any{repoID, number}
	}
	return execScoped(database, "set pull state", query, args...)
}

// SetPullMerged records a completed merge.
func SetPullMerged(database *sql.DB, repoID, number int64, mergeCommit string) error {
	return execScoped(database, "set pull merged",
		`UPDATE pulls SET state = 'merged', merged_at = unixepoch(), merge_commit = ? WHERE repo_id = ? AND number = ?`,
		mergeCommit, repoID, number)
}

// ApprovePull publishes a pending pull request.
func ApprovePull(database *sql.DB, repoID, number int64) error {
	return execScoped(database, "approve pull",
		`UPDATE pulls SET pending = 0 WHERE repo_id = ? AND number = ?`, repoID, number)
}

// DeletePull removes a pull request; its comments cascade.
func DeletePull(database *sql.DB, repoID, number int64) error {
	return execScoped(database, "delete pull",
		`DELETE FROM pulls WHERE repo_id = ? AND number = ?`, repoID, number)
}

// HasDuplicatePull reports whether a row with the same title, body, branch
// pair, and claimed author already exists in the repo.
func HasDuplicatePull(database *sql.DB, repoID int64, title, body, base, head, authorName, authorEmail string) (bool, error) {
	var n int
	err := database.QueryRow(
		`SELECT COUNT(*) FROM pulls WHERE repo_id = ? AND title = ? AND body = ?
		 AND base = ? AND head = ? AND author_name = ? AND author_email = ?`,
		repoID, title, body, base, head, authorName, authorEmail).Scan(&n)
	if err != nil {
		return false, fmt.Errorf("duplicate pull check: %w", err)
	}
	return n > 0, nil
}

const pullCommentColumns = `SELECT id, pull_id, body, pending, author_id,
	author_name, author_email, created_at FROM pull_comments WHERE `

// CreatePullComment inserts a PR-level comment. authorID is 0 for a guest.
func CreatePullComment(database *sql.DB, pullID int64, body string, authorID int64, authorName, authorEmail string, pending bool) (PullComment, error) {
	id, err := execLastID(database,
		`INSERT INTO pull_comments (pull_id, body, pending, author_id, author_name, author_email)
		 VALUES (?, ?, ?, ?, ?, ?)`,
		pullID, body, boolToInt(pending), nullableID(authorID), authorName, authorEmail)
	if err != nil {
		return PullComment{}, fmt.Errorf("create pull comment: %w", err)
	}
	return getPullComment(database, pullCommentColumns+`id = ?`, id)
}

// scanPullComment reads one pull_comments row into c.
func scanPullComment(s rowScanner, c *PullComment) error {
	var pending int
	if err := s.Scan(
		&c.ID, &c.PullID, &c.Body, &pending, &c.AuthorID,
		&c.AuthorName, &c.AuthorEmail, &c.CreatedAt); err != nil {
		return err
	}
	c.Pending = pending != 0
	return nil
}

func getPullComment(database *sql.DB, query string, args ...any) (PullComment, error) {
	var c PullComment
	err := scanPullComment(database.QueryRow(query, args...), &c)
	if errors.Is(err, sql.ErrNoRows) {
		return PullComment{}, fmt.Errorf("pull comment: %w", ErrNotFound)
	}
	if err != nil {
		return PullComment{}, fmt.Errorf("get pull comment: %w", err)
	}
	return c, nil
}

// ListPullComments returns a pull request's comments oldest first.
// includePending must be true only for the owner.
func ListPullComments(database *sql.DB, pullID int64, includePending bool) ([]PullComment, error) {
	query := pullCommentColumns + `pull_id = ?`
	if !includePending {
		query += ` AND pending = 0`
	}
	query += ` ORDER BY created_at, id`

	comments, err := listQuery(database, query, []any{pullID}, scanPullComment)
	if err != nil {
		return nil, fmt.Errorf("list pull comments: %w", err)
	}
	return comments, nil
}

// ApprovePullComment publishes a pending comment, scoped to its pull.
func ApprovePullComment(database *sql.DB, pullID, id int64) error {
	return execScoped(database, "approve pull comment",
		`UPDATE pull_comments SET pending = 0 WHERE id = ? AND pull_id = ?`, id, pullID)
}

// DeletePullComment removes a comment, scoped to its pull.
func DeletePullComment(database *sql.DB, pullID, id int64) error {
	return execScoped(database, "delete pull comment",
		`DELETE FROM pull_comments WHERE id = ? AND pull_id = ?`, id, pullID)
}