package db

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

type Repo struct {
	ID              int64
	Name            string
	Description     *string
	IsPrivate       bool
	IsPinned        bool
	DefaultBranch   string
	CreatedAt       string
	IssueSeq        int64
	PatchSeq        int64
	IssueTemplate   *string
	PatchTemplate   *string
	AllowUserLabels bool
}

const repoColumns = `id, name, description, is_private, is_pinned, default_branch, created_at,
	issue_seq, patch_seq, issue_template, patch_template, allow_user_labels`

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

func scanRepo(s rowScanner) (*Repo, error) {
	var r Repo
	err := s.Scan(&r.ID, &r.Name, &r.Description, &r.IsPrivate, &r.IsPinned, &r.DefaultBranch,
		&r.CreatedAt, &r.IssueSeq, &r.PatchSeq, &r.IssueTemplate, &r.PatchTemplate, &r.AllowUserLabels)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &r, nil
}

func (d *DB) RepoByName(ctx context.Context, name string) (*Repo, error) {
	return scanRepo(d.QueryRowContext(ctx, `SELECT `+repoColumns+` FROM repositories WHERE name = ?`, name))
}

// GetRepo looks a repo up by name and hides private repos from non-admins.
func (d *DB) GetRepo(ctx context.Context, name string, isAdmin bool) (*Repo, error) {
	repo, err := d.RepoByName(ctx, name)
	if err != nil || repo == nil {
		return nil, err
	}
	if repo.IsPrivate && !isAdmin {
		return nil, nil
	}
	return repo, nil
}

// searchClause builds the optional name/description LIKE filter. The pattern
// is already escaped by the caller.
func searchClause(pattern string, args []any) (string, []any) {
	if pattern == "" {
		return "", args
	}
	return ` AND (name LIKE ? ESCAPE '\' OR description LIKE ? ESCAPE '\')`, append(args, pattern, pattern)
}

// EscapeLike turns a raw search term into a LIKE pattern.
func EscapeLike(term string) string {
	r := strings.NewReplacer(`\`, `\\`, `%`, `\%`, `_`, `\_`)
	return "%" + r.Replace(term) + "%"
}

// visibilityClause hides private repos from non-admins. An admin sees
// everything, so the clause becomes a no-op.
func visibilityClause(isAdmin bool) string {
	if isAdmin {
		return `1 = 1`
	}
	return `is_private = 0`
}

func (d *DB) CountRepos(ctx context.Context, isAdmin bool, searchPattern string) (int, error) {
	where, args := searchClause(searchPattern, nil)
	var n int
	err := d.QueryRowContext(ctx,
		`SELECT COUNT(*) FROM repositories WHERE `+visibilityClause(isAdmin)+where, args...).Scan(&n)
	return n, err
}

// ListRepos returns one page of repos. Pinned repos always come first; sort is
// "name" or "created".
func (d *DB) ListRepos(ctx context.Context, isAdmin bool, searchPattern, sort string, limit, offset int) ([]Repo, error) {
	where, args := searchClause(searchPattern, nil)
	order := `is_pinned DESC, created_at DESC`
	if sort == "name" {
		order = `is_pinned DESC, name ASC`
	}
	args = append(args, limit, offset)
	rows, err := d.QueryContext(ctx,
		`SELECT `+repoColumns+` FROM repositories WHERE `+visibilityClause(isAdmin)+where+
			` ORDER BY `+order+` LIMIT ? OFFSET ?`, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Repo
	for rows.Next() {
		r, err := scanRepo(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, *r)
	}
	return out, rows.Err()
}

// CreateRepo inserts a repo and returns the stored row.
func (d *DB) CreateRepo(ctx context.Context, name string, description *string, isPrivate bool,
	defaultBranch, createdAt string,
) (*Repo, error) {
	res, err := d.ExecContext(ctx,
		`INSERT INTO repositories (name, description, is_private, default_branch, created_at)
		 VALUES (?, ?, ?, ?, ?)`,
		name, description, isPrivate, defaultBranch, createdAt)
	if err != nil {
		return nil, err
	}
	id, err := res.LastInsertId()
	if err != nil {
		return nil, err
	}
	return scanRepo(d.QueryRowContext(ctx, `SELECT `+repoColumns+` FROM repositories WHERE id = ?`, id))
}

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

func (d *DB) DeleteRepoByName(ctx context.Context, name string) error {
	_, err := d.ExecContext(ctx, `DELETE FROM repositories WHERE name = ?`, name)
	return err
}

// UpdateRepoSettings writes the repo settings form in one statement.
func (d *DB) UpdateRepoSettings(ctx context.Context, id int64, description *string,
	isPrivate, isPinned, allowUserLabels bool, defaultBranch string,
	issueTemplate, patchTemplate *string,
) error {
	_, err := d.ExecContext(ctx,
		`UPDATE repositories SET description = ?, is_private = ?, is_pinned = ?,
		 allow_user_labels = ?, default_branch = ?, issue_template = ?, patch_template = ?
		 WHERE id = ?`,
		description, isPrivate, isPinned, allowUserLabels, defaultBranch,
		issueTemplate, patchTemplate, id)
	return err
}

func (d *DB) RenameRepo(ctx context.Context, id int64, newName string) error {
	_, err := d.ExecContext(ctx, `UPDATE repositories SET name = ? WHERE id = ?`, newName, id)
	return err
}

func (d *DB) SetDefaultBranch(ctx context.Context, id int64, branch string) error {
	_, err := d.ExecContext(ctx, `UPDATE repositories SET default_branch = ? WHERE id = ?`, branch, id)
	return err
}

// RepoRef is the id/name pair used by the disk sync job.
type RepoRef struct {
	ID   int64
	Name string
}

func (d *DB) AllRepoRefs(ctx context.Context) ([]RepoRef, error) {
	rows, err := d.QueryContext(ctx, `SELECT id, name FROM repositories`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []RepoRef
	for rows.Next() {
		var r RepoRef
		if err := rows.Scan(&r.ID, &r.Name); err != nil {
			return nil, err
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

// RepoCounts is a repo row with its issue and patch counts. A delete also
// cascades to releases and CI runs, which are not counted here.
type RepoCounts struct {
	ID      int64
	Name    string
	Issues  int
	Patches int
}

// AllRepoCounts lists every repo with its issue and patch counts.
func (d *DB) AllRepoCounts(ctx context.Context) ([]RepoCounts, error) {
	rows, err := d.QueryContext(ctx, `
		SELECT r.id, r.name,
		  (SELECT COUNT(*) FROM issues WHERE repo_id = r.id),
		  (SELECT COUNT(*) FROM patches WHERE repo_id = r.id)
		FROM repositories r ORDER BY r.name`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []RepoCounts
	for rows.Next() {
		var r RepoCounts
		if err := rows.Scan(&r.ID, &r.Name, &r.Issues, &r.Patches); err != nil {
			return nil, err
		}
		out = append(out, r)
	}
	return out, rows.Err()
}