package db

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

type Patch struct {
	ID           int64
	RepoID       int64
	AuthorID     *int64
	Number       int64
	Title        string
	Description  string
	PatchContent string
	Status       string
	AuthorName   string
	AuthorEmail  string
	CreatedAt    string
	UpdatedAt    string
	EditedAt     *string
	Version      string
	// Joined from users, null when the author was deleted.
	AuthorUsername      *string
	AuthorAvatarVersion *int64
}

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

type PatchComment struct {
	ID                  int64
	PatchID             int64
	AuthorID            *int64
	Body                string
	CreatedAt           string
	EditedAt            *string
	AuthorUsername      *string
	AuthorAvatarVersion *int64
}

const patchColumns = `patches.id, patches.repo_id, patches.author_id, patches.number, patches.title,
	patches.description, patches.patch_content, patches.status, patches.author_name,
	patches.author_email, patches.created_at, patches.updated_at, patches.edited_at,
	patches.version, users.username, users.avatar_version`

func scanPatch(s rowScanner) (*Patch, error) {
	var p Patch
	err := s.Scan(&p.ID, &p.RepoID, &p.AuthorID, &p.Number, &p.Title, &p.Description,
		&p.PatchContent, &p.Status, &p.AuthorName, &p.AuthorEmail, &p.CreatedAt,
		&p.UpdatedAt, &p.EditedAt, &p.Version, &p.AuthorUsername, &p.AuthorAvatarVersion)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &p, nil
}

func (d *DB) PatchCounts(ctx context.Context, repoID int64, labelIDs []int64) (map[string]int, error) {
	args := []any{repoID}
	filter, args := labelFilter("patch_labels", "patch_id", "patches", labelIDs, args)
	rows, err := d.QueryContext(ctx,
		`SELECT patches.status, COUNT(*) FROM patches WHERE patches.repo_id = ?`+filter+
			` GROUP BY patches.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()
}

func (d *DB) ListPatches(ctx context.Context, repoID int64, status string, labelIDs []int64,
	limit, offset int,
) ([]Patch, error) {
	args := []any{repoID, status}
	filter, args := labelFilter("patch_labels", "patch_id", "patches", labelIDs, args)
	args = append(args, limit, offset)
	rows, err := d.QueryContext(ctx,
		`SELECT `+patchColumns+` FROM patches
		 LEFT JOIN users ON users.id = patches.author_id
		 WHERE patches.repo_id = ? AND patches.status = ?`+filter+
			` ORDER BY patches.number DESC LIMIT ? OFFSET ?`, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Patch
	for rows.Next() {
		p, err := scanPatch(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, *p)
	}
	return out, rows.Err()
}

func (d *DB) PatchByNumber(ctx context.Context, repoID, number int64) (*Patch, error) {
	return scanPatch(d.QueryRowContext(ctx,
		`SELECT `+patchColumns+` FROM patches
		 LEFT JOIN users ON users.id = patches.author_id
		 WHERE patches.repo_id = ? AND patches.number = ?`, repoID, number))
}

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

// CreatePatch allocates the next patch number and inserts the patch with its
// labels in one transaction. It returns the patch number and row id.
func (d *DB) CreatePatch(ctx context.Context, repoID int64, authorID *int64,
	title, description, patchContent, authorName, authorEmail, version, now string,
	labelIDs []int64,
) (number, id int64, err error) {
	tx, err := d.BeginTx(ctx, nil)
	if err != nil {
		return 0, 0, err
	}
	defer tx.Rollback()

	if err = tx.QueryRowContext(ctx,
		`UPDATE repositories SET patch_seq = patch_seq + 1 WHERE id = ? RETURNING patch_seq`,
		repoID).Scan(&number); err != nil {
		return 0, 0, err
	}
	res, err := tx.ExecContext(ctx,
		`INSERT INTO patches (repo_id, author_id, number, title, description, patch_content,
		 status, author_name, author_email, created_at, updated_at, version)
		 VALUES (?, ?, ?, ?, ?, ?, 'open', ?, ?, ?, ?, ?)`,
		repoID, authorID, number, title, description, patchContent,
		authorName, authorEmail, now, now, version)
	if err != nil {
		return 0, 0, err
	}
	if id, err = res.LastInsertId(); err != nil {
		return 0, 0, err
	}
	if err = attachLabels(ctx, tx, "patch_labels", "patch_id", id, repoID, labelIDs); err != nil {
		return 0, 0, err
	}
	return number, id, tx.Commit()
}

// ClaimPatchMerge marks an open patch merged, but only while its content is
// still at the version the admin reviewed. It reports whether the claim won.
func (d *DB) ClaimPatchMerge(ctx context.Context, id int64, version, now string) (bool, error) {
	res, err := d.ExecContext(ctx,
		`UPDATE patches SET status = 'merged', updated_at = ?
		 WHERE id = ? AND status = 'open' AND version = ?`, now, id, version)
	if err != nil {
		return false, err
	}
	n, err := res.RowsAffected()
	return n > 0, err
}

// ReopenPatch rolls a failed merge back to open.
func (d *DB) ReopenPatch(ctx context.Context, id int64, now string) error {
	_, err := d.ExecContext(ctx,
		`UPDATE patches SET status = 'open', updated_at = ? WHERE id = ?`, now, id)
	return err
}

// TogglePatchClosed flips open and closed. Merged patches are excluded, so a
// false result means the patch is merged or gone.
func (d *DB) TogglePatchClosed(ctx context.Context, id int64, now string) (bool, error) {
	res, err := d.ExecContext(ctx,
		`UPDATE patches SET status = CASE WHEN status = 'open' THEN 'closed' ELSE 'open' END,
		 updated_at = ? WHERE id = ? AND status != 'merged'`, now, id)
	if err != nil {
		return false, err
	}
	n, err := res.RowsAffected()
	return n > 0, err
}

// ReplacePatchContent stores a re-uploaded patch file under a new version.
// It reports false when the patch is no longer open, so a merge landing
// between the caller's check and this write cannot rewrite merged content.
func (d *DB) ReplacePatchContent(ctx context.Context, id int64,
	patchContent, authorName, authorEmail, version, now string,
) (bool, error) {
	res, err := d.ExecContext(ctx,
		`UPDATE patches SET patch_content = ?, author_name = ?, author_email = ?,
		 version = ?, updated_at = ? WHERE id = ? AND status = 'open'`,
		patchContent, authorName, authorEmail, version, now, id)
	if err != nil {
		return false, err
	}
	n, err := res.RowsAffected()
	return n > 0, err
}

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

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

func (d *DB) ListPatchComments(ctx context.Context, patchID int64) ([]PatchComment, error) {
	rows, err := d.QueryContext(ctx,
		`SELECT patch_comments.id, patch_comments.patch_id, patch_comments.author_id,
		        patch_comments.body, patch_comments.created_at, patch_comments.edited_at,
		        users.username, users.avatar_version
		 FROM patch_comments
		 LEFT JOIN users ON users.id = patch_comments.author_id
		 WHERE patch_comments.patch_id = ?
		 ORDER BY patch_comments.created_at ASC`, patchID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []PatchComment
	for rows.Next() {
		var c PatchComment
		if err := rows.Scan(&c.ID, &c.PatchID, &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()
}

// AddPatchComment inserts a comment and bumps the patch's updated_at together.
func (d *DB) AddPatchComment(ctx context.Context, patchID 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 patch_comments (patch_id, author_id, body, created_at) VALUES (?, ?, ?, ?)`,
		patchID, authorID, body, now); err != nil {
		return err
	}
	if _, err := tx.ExecContext(ctx,
		`UPDATE patches SET updated_at = ? WHERE id = ?`, now, patchID); err != nil {
		return err
	}
	return tx.Commit()
}

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

// PatchCommentAuth 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) PatchCommentAuth(ctx context.Context, commentID, repoID int64) (*CommentAuth, error) {
	var a CommentAuth
	err := d.QueryRowContext(ctx,
		`SELECT patch_comments.author_id, patches.status
		 FROM patch_comments
		 JOIN patches ON patches.id = patch_comments.patch_id
		 WHERE patch_comments.id = ? AND patches.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) ListPatchReactions(ctx context.Context, patchID int64) ([]Reaction, error) {
	rows, err := d.QueryContext(ctx,
		`SELECT id, comment_id, user_id, emoji FROM patch_reactions WHERE patch_id = ?`, patchID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanReactions(rows)
}

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

func (d *DB) PatchLabels(ctx context.Context, patchID int64) ([]Label, error) {
	rows, err := d.QueryContext(ctx,
		`SELECT labels.id, labels.repo_id, labels.name, labels.color, labels.created_at
		 FROM patch_labels
		 JOIN labels ON labels.id = patch_labels.label_id
		 WHERE patch_labels.patch_id = ?`, patchID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanLabels(rows)
}

// PatchLabelsByPatch batch-loads labels for a list view.
func (d *DB) PatchLabelsByPatch(ctx context.Context, patchIDs []int64) (map[int64][]Label, error) {
	return d.labelsByParent(ctx, "patch_labels", "patch_id", patchIDs)
}

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

func (d *DB) RemovePatchLabel(ctx context.Context, patchID, labelID int64) error {
	_, err := d.ExecContext(ctx,
		`DELETE FROM patch_labels WHERE patch_id = ? AND label_id = ?`, patchID, labelID)
	return err
}