package db

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

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

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

const issueColumns = `SELECT id, repo_id, number, title, body, state, pending,
	author_id, author_name, author_email, created_at, closed_at FROM issues WHERE `

// CreateIssue inserts an issue with a per-repo number of MAX(number)+1,
// computed atomically inside the INSERT. authorID is 0 for a guest.
func CreateIssue(database *sql.DB, repoID int64, title, body string, authorID int64, authorName, authorEmail string, pending bool) (Issue, error) {
	const insert = `INSERT INTO issues (repo_id, number, title, body, pending, author_id, author_name, author_email)
		 VALUES (?, (SELECT COALESCE(MAX(number), 0) + 1 FROM issues WHERE repo_id = ?), ?, ?, ?, ?, ?, ?)`
	args := []any{repoID, repoID, title, body, 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 Issue{}, fmt.Errorf("create issue: %w", err)
	}
	return GetIssueByID(database, id)
}

// GetIssueByID looks up an issue by primary key.
func GetIssueByID(database *sql.DB, id int64) (Issue, error) {
	return getIssue(database, issueColumns+`id = ?`, id)
}

// GetIssueByNumber looks up an issue by its per-repo number.
func GetIssueByNumber(database *sql.DB, repoID, number int64) (Issue, error) {
	return getIssue(database, issueColumns+`repo_id = ? AND number = ?`, repoID, number)
}

// scanIssue reads one issues row (see issueColumns) into issue.
func scanIssue(s rowScanner, issue *Issue) error {
	var pending int
	if err := s.Scan(
		&issue.ID, &issue.RepoID, &issue.Number, &issue.Title, &issue.Body,
		&issue.State, &pending, &issue.AuthorID, &issue.AuthorName,
		&issue.AuthorEmail, &issue.CreatedAt, &issue.ClosedAt); err != nil {
		return err
	}
	issue.Pending = pending != 0
	return nil
}

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

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

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

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

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

// DeleteIssue removes an issue row; its comments cascade.
func DeleteIssue(database *sql.DB, repoID, number int64) error {
	return execScoped(database, "delete issue",
		`DELETE FROM issues WHERE repo_id = ? AND number = ?`, repoID, number)
}

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

const issueCommentColumns = `SELECT id, issue_id, body, pending, author_id,
	author_name, author_email, created_at FROM issue_comments WHERE `

// CreateComment inserts a comment. authorID is 0 for a guest.
func CreateComment(database *sql.DB, issueID int64, body string, authorID int64, authorName, authorEmail string, pending bool) (IssueComment, error) {
	id, err := execLastID(database,
		`INSERT INTO issue_comments (issue_id, body, pending, author_id, author_name, author_email)
		 VALUES (?, ?, ?, ?, ?, ?)`,
		issueID, body, boolToInt(pending), nullableID(authorID), authorName, authorEmail)
	if err != nil {
		return IssueComment{}, fmt.Errorf("create comment: %w", err)
	}
	return getComment(database, issueCommentColumns+`id = ?`, id)
}

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

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

// ListComments returns an issue's comments oldest first. includePending must
// be true only for the owner.
func ListComments(database *sql.DB, issueID int64, includePending bool) ([]IssueComment, error) {
	query := issueCommentColumns + `issue_id = ?`
	if !includePending {
		query += ` AND pending = 0`
	}
	query += ` ORDER BY created_at, id`

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

// ApproveComment publishes a pending comment, scoped to its issue so a
// comment id cannot be approved through a different issue.
func ApproveComment(database *sql.DB, issueID, id int64) error {
	return execScoped(database, "approve comment",
		`UPDATE issue_comments SET pending = 0 WHERE id = ? AND issue_id = ?`, id, issueID)
}

// DeleteComment removes a comment, scoped to its issue.
func DeleteComment(database *sql.DB, issueID, id int64) error {
	return execScoped(database, "delete comment",
		`DELETE FROM issue_comments WHERE id = ? AND issue_id = ?`, id, issueID)
}

// CountPending returns how many issues, comments, pull requests, and PR
// comments in a repo await owner moderation.
func CountPending(database *sql.DB, repoID int64) (int, error) {
	var n int
	err := database.QueryRow(
		`SELECT (SELECT COUNT(*) FROM issues WHERE repo_id = ? AND pending = 1)
		      + (SELECT COUNT(*) FROM issue_comments c JOIN issues i ON i.id = c.issue_id
		         WHERE i.repo_id = ? AND c.pending = 1)
		      + (SELECT COUNT(*) FROM pulls WHERE repo_id = ? AND pending = 1)
		      + (SELECT COUNT(*) FROM pull_comments c JOIN pulls p ON p.id = c.pull_id
		         WHERE p.repo_id = ? AND c.pending = 1)`,
		repoID, repoID, repoID, repoID).Scan(&n)
	if err != nil {
		return 0, fmt.Errorf("count pending: %w", err)
	}
	return n, nil
}

func boolToInt(b bool) int {
	if b {
		return 1
	}
	return 0
}

func nullableID(id int64) any {
	if id == 0 {
		return nil
	}
	return id
}