package store

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"sort"
	"strings"
	"time"

	"gitlab.com/Timfa/bluegit/internal/access"
	"gitlab.com/Timfa/bluegit/internal/i18n"
	"gitlab.com/Timfa/bluegit/internal/names"
)

// Group is een verzameling repositories en leden.
type Group struct {
	ID          int64
	Name        string
	Path        string
	Description string
	Visibility  string
	DefaultRole access.Role
	LogoMIME    string
	CreatedBy   int64
	OwnerID     int64
	CreatedAt   time.Time
}

// HasLogo meldt of er een geüpload logo is.
func (g Group) HasLogo() bool { return g.LogoMIME != "" }

// LogoURL is het pad van het groepslogo, of leeg.
func (g Group) LogoURL() string {
	if !g.HasLogo() {
		return ""
	}
	return "/groups/" + g.Path + "/logo"
}

// Repo is een git-repository van een gebruiker of een groep.
type Repo struct {
	ID            int64
	OwnerKind     string
	OwnerID       int64
	OwnerPath     string
	Name          string
	Description   string
	DefaultBranch string
	Visibility    string
	LogoMIME      string
	CreatedAt     time.Time
	// ReleaseVisibility is "inherit" (zelfde als de repo), "private" of
	// "public". Zie ReleasesPublic.
	ReleaseVisibility string
	// PushedAt is het moment van de laatste push. Nul: nog nooit gepusht.
	PushedAt time.Time
	// CIPaused: geen nieuwe pipelines in de wachtrij (push, MR, handmatig).
	CIPaused bool
}

// ReleasesPublic meldt of releases (pagina, notities, assets) voor
// niet-leden zichtbaar zijn. Broncode valt hier nooit onder.
func (r Repo) ReleasesPublic() bool {
	switch r.ReleaseVisibility {
	case "public":
		return true
	case "private":
		return false
	default:
		return r.Visibility == "public"
	}
}

// ValidReleaseVisibility kent inherit, private en public.
func ValidReleaseVisibility(v string) bool {
	return v == "inherit" || v == "private" || v == "public"
}

// SetReleaseVisibility zet de zichtbaarheid van releases.
func (s *Store) SetReleaseVisibility(ctx context.Context, id int64, v string) error {
	if !ValidReleaseVisibility(v) {
		return i18n.E("err.releaseVisibility")
	}
	_, err := s.db.ExecContext(ctx, `UPDATE repos SET release_visibility = ? WHERE id = ?`, v, id)
	return err
}

// ListReleaseOnlyRepoIDs geeft privé repositories met publieke releases
// waar deze gebruiker geen lid van is (0 = anoniem). Alleen naam, korte
// beschrijving en de Releases-pagina zijn voor hen zichtbaar.
func (s *Store) ListReleaseOnlyRepoIDs(ctx context.Context, userID int64) ([]int64, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT id FROM repos WHERE visibility = 'private' AND release_visibility = 'public' ORDER BY id DESC`)
	if err != nil {
		return nil, err
	}
	var ids []int64
	for rows.Next() {
		var id int64
		if err := rows.Scan(&id); err != nil {
			rows.Close()
			return nil, err
		}
		ids = append(ids, id)
	}
	rows.Close()
	if err := rows.Err(); err != nil {
		return nil, err
	}
	var out []int64
	for _, id := range ids {
		role, err := s.EffectiveRole(ctx, userID, id)
		if err != nil {
			return nil, err
		}
		if !role.CanRead() {
			out = append(out, id)
		}
	}
	return out, nil
}

// HasLogo meldt of er een geüpload logo is.
func (r Repo) HasLogo() bool { return r.LogoMIME != "" }

// LogoURL is het pad van het repologo, of leeg.
func (r Repo) LogoURL() string {
	if !r.HasLogo() {
		return ""
	}
	return "/" + r.FullPath() + "/-/logo"
}

// FullPath is owner/naam.
func (r Repo) FullPath() string { return r.OwnerPath + "/" + r.Name }

// CreateGroup maakt een groep. De maker wordt beheerder.
// defaultRole is de rol die leden krijgen op een repository die daarna ontstaat.
func (s *Store) CreateGroup(ctx context.Context, creator int64, name, path, description string, defaultRole access.Role) (Group, error) {
	name = strings.TrimSpace(name)
	path = strings.ToLower(strings.TrimSpace(path))
	if name == "" {
		return Group{}, i18n.E("err.groupNeedsName")
	}
	if defaultRole == "" {
		defaultRole = access.RoleDeveloper
	}
	if _, err := access.ParseRole(string(defaultRole)); err != nil {
		return Group{}, err
	}
	if err := names.ValidGroupPath(path); err != nil {
		return Group{}, err
	}
	if err := s.ensurePathFree(ctx, path); err != nil {
		return Group{}, err
	}
	res, err := s.db.ExecContext(ctx, `INSERT INTO groups (name, path, description, visibility, default_role, created_by, owner_id, created_at) VALUES (?, ?, ?, 'private', ?, ?, ?, ?)`,
		name, path, strings.TrimSpace(description), string(defaultRole), creator, creator, now())
	if err != nil {
		return Group{}, err
	}
	id, _ := res.LastInsertId()
	if _, err := s.db.ExecContext(ctx, `INSERT INTO group_members (group_id, user_id, role) VALUES (?, ?, ?)`, id, creator, string(access.RoleMaintainer)); err != nil {
		return Group{}, err
	}
	return s.GroupByID(ctx, id)
}

func (s *Store) ensurePathFree(ctx context.Context, path string) error {
	var n int
	if err := s.db.QueryRowContext(ctx, `SELECT COUNT(*) FROM users WHERE username = ?`, path).Scan(&n); err != nil {
		return err
	}
	if n > 0 {
		return i18n.E("err.pathUser", path)
	}
	if err := s.db.QueryRowContext(ctx, `SELECT COUNT(*) FROM groups WHERE path = ?`, path).Scan(&n); err != nil {
		return err
	}
	if n > 0 {
		return i18n.E("err.pathGroup", path)
	}
	return nil
}

// GroupByPath leest een groep.
func (s *Store) GroupByPath(ctx context.Context, path string) (Group, error) {
	row := s.db.QueryRowContext(ctx, `SELECT `+groupCols+` FROM groups WHERE path = ?`, path)
	return scanGroup(row)
}

// GroupByID leest een groep.
func (s *Store) GroupByID(ctx context.Context, id int64) (Group, error) {
	row := s.db.QueryRowContext(ctx, `SELECT `+groupCols+` FROM groups WHERE id = ?`, id)
	return scanGroup(row)
}

const groupCols = `id, name, path, description, created_by, created_at, visibility, default_role, logo_mime, owner_id`

func scanGroup(row scanner) (Group, error) {
	var g Group
	var created, role string
	if err := row.Scan(&g.ID, &g.Name, &g.Path, &g.Description, &g.CreatedBy, &created, &g.Visibility, &role, &g.LogoMIME, &g.OwnerID); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return Group{}, errNotFound
		}
		return Group{}, err
	}
	g.DefaultRole = access.Role(role)
	g.CreatedAt = parseTime(created)
	if g.OwnerID == 0 {
		g.OwnerID = g.CreatedBy
	}
	return g, nil
}

// GroupRole is de rol van een gebruiker in een groep.
// Installatiebeheer geeft geen lidmaatschap.
func (s *Store) GroupRole(ctx context.Context, groupID, userID int64) (access.Role, error) {
	if userID == 0 {
		return access.RoleNone, nil
	}
	g, err := s.GroupByID(ctx, groupID)
	if err != nil {
		return access.RoleNone, err
	}
	if userID == g.OwnerID {
		return access.RoleMaintainer, nil
	}
	var role string
	err = s.db.QueryRowContext(ctx, `SELECT role FROM group_members WHERE group_id = ? AND user_id = ?`, groupID, userID).Scan(&role)
	if errors.Is(err, sql.ErrNoRows) {
		return access.RoleNone, nil
	}
	if err != nil {
		return access.RoleNone, err
	}
	return access.Role(role), nil
}

// ListGroups geeft de groepen waarvan deze gebruiker lid is.
func (s *Store) ListGroups(ctx context.Context, userID int64) ([]Group, error) {
	q := `SELECT ` + groupCols + ` FROM groups WHERE id IN (SELECT group_id FROM group_members WHERE user_id = ?) ORDER BY path`
	args := []any{userID}
	rows, err := s.db.QueryContext(ctx, q, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Group
	for rows.Next() {
		g, err := scanGroup(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, g)
	}
	return out, rows.Err()
}

// CreateRepo maakt de metadata. De schijfmap maakt de aanroeper.
func (s *Store) CreateRepo(ctx context.Context, ownerKind string, ownerID int64, name, description, defaultBranch, visibility string) (Repo, error) {
	if ownerKind != "user" && ownerKind != "group" {
		return Repo{}, i18n.E("err.ownerKind")
	}
	if err := names.ValidRepoName(name); err != nil {
		return Repo{}, err
	}
	if visibility != "public" && visibility != "private" {
		return Repo{}, i18n.E("err.visibility")
	}
	if defaultBranch == "" {
		defaultBranch = "master"
	}
	if ownerKind == "user" {
		u, err := s.UserByID(ctx, ownerID)
		if err != nil {
			return Repo{}, err
		}
		var n int
		if err := s.db.QueryRowContext(ctx, `SELECT COUNT(*) FROM groups WHERE path = ?`, u.Username).Scan(&n); err != nil {
			return Repo{}, err
		}
		if n > 0 {
			return Repo{}, i18n.E("err.userGroupClash", u.Username)
		}
	}
	res, err := s.db.ExecContext(ctx, `INSERT INTO repos (owner_kind, owner_id, name, description, default_branch, visibility, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)`,
		ownerKind, ownerID, name, strings.TrimSpace(description), defaultBranch, visibility, now())
	if err != nil {
		if strings.Contains(err.Error(), "UNIQUE") {
			return Repo{}, i18n.E("err.repoExists", name)
		}
		return Repo{}, err
	}
	id, _ := res.LastInsertId()
	if err := s.AddProtected(ctx, id, defaultBranch); err != nil {
		return Repo{}, err
	}
	if ownerKind == "group" {
		if err := s.GrantDefaultRoles(ctx, ownerID, id); err != nil {
			return Repo{}, err
		}
	}
	return s.RepoByID(ctx, id)
}

// RepoByID leest een repository.
func (s *Store) RepoByID(ctx context.Context, id int64) (Repo, error) {
	row := s.db.QueryRowContext(ctx, repoSelect+` WHERE r.id = ?`, id)
	return scanRepo(row)
}

// RepoByPath leest owner/naam.
func (s *Store) RepoByPath(ctx context.Context, owner, name string) (Repo, error) {
	row := s.db.QueryRowContext(ctx, repoSelect+`
		WHERE r.name = ? AND (
			(r.owner_kind = 'user' AND r.owner_id = (SELECT id FROM users WHERE username = ?))
			OR (r.owner_kind = 'group' AND r.owner_id = (SELECT id FROM groups WHERE path = ?))
		)`, name, owner, owner)
	return scanRepo(row)
}

const repoSelect = `
SELECT r.id, r.owner_kind, r.owner_id,
  CASE r.owner_kind WHEN 'user' THEN (SELECT username FROM users WHERE id = r.owner_id) ELSE (SELECT path FROM groups WHERE id = r.owner_id) END,
  r.name, r.description, r.default_branch, r.visibility, r.created_at, r.logo_mime, r.release_visibility, r.pushed_at, r.ci_paused
FROM repos r`

func scanRepo(row *sql.Row) (Repo, error) {
	var r Repo
	var created, pushed string
	var owner sql.NullString
	var paused int
	if err := row.Scan(&r.ID, &r.OwnerKind, &r.OwnerID, &owner, &r.Name, &r.Description, &r.DefaultBranch, &r.Visibility, &created, &r.LogoMIME, &r.ReleaseVisibility, &pushed, &paused); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return Repo{}, errNotFound
		}
		return Repo{}, err
	}
	r.OwnerPath = owner.String
	r.CreatedAt = parseTime(created)
	if pushed != "" {
		r.PushedAt = parseTime(pushed)
	}
	r.CIPaused = paused == 1
	return r, nil
}

// UpdateRepo wijzigt beschrijving en zichtbaarheid.
func (s *Store) UpdateRepo(ctx context.Context, id int64, description, visibility string) error {
	if visibility != "public" && visibility != "private" {
		return i18n.E("err.visibility")
	}
	_, err := s.db.ExecContext(ctx, `UPDATE repos SET description = ?, visibility = ? WHERE id = ?`, strings.TrimSpace(description), visibility, id)
	return err
}

// SetDefaultBranch zet de standaardbranch in de metadata. De aanroeper
// zet HEAD in de bare repository.
func (s *Store) SetDefaultBranch(ctx context.Context, id int64, branch string) error {
	branch = strings.TrimSpace(branch)
	if branch == "" {
		return i18n.E("err.branchMissing")
	}
	_, err := s.db.ExecContext(ctx, `UPDATE repos SET default_branch = ? WHERE id = ?`, branch, id)
	return err
}

// SetRepoCIPaused zet of er nieuwe pipelines mogen worden ingepland.
func (s *Store) SetRepoCIPaused(ctx context.Context, id int64, paused bool) error {
	_, err := s.db.ExecContext(ctx, `UPDATE repos SET ci_paused = ? WHERE id = ?`, boolInt(paused), id)
	return err
}

// DeleteRepo verwijdert de metadata. Bestanden verwijdert de aanroeper.
func (s *Store) DeleteRepo(ctx context.Context, id int64) error {
	// Forks blijven bestaan als zelfstandige repositories.
	if _, err := s.db.ExecContext(ctx, `UPDATE repos SET fork_of = 0 WHERE fork_of = ?`, id); err != nil {
		return err
	}
	_, err := s.db.ExecContext(ctx, `DELETE FROM repos WHERE id = ?`, id)
	return err
}

// ListRepoIDs geeft repositories die deze gebruiker mag zien:
// openbare repositories, eigen repositories, repositories van een groep
// waarvan hij lid is, en repositories met een rechtstreeks lidmaatschap.
// Installatiebeheer voegt daar niets aan toe.
func (s *Store) ListRepoIDs(ctx context.Context, userID int64) ([]int64, error) {
	q := `SELECT id FROM repos WHERE visibility = 'public'`
	args := []any{}
	if userID != 0 {
		q = `SELECT DISTINCT id FROM repos WHERE visibility = 'public'
			OR (owner_kind = 'user' AND owner_id = ?)
			OR (owner_kind = 'group' AND owner_id IN (SELECT group_id FROM group_members WHERE user_id = ?))
			OR id IN (SELECT repo_id FROM repo_members WHERE user_id = ?)`
		args = []any{userID, userID, userID}
	}
	q += ` ORDER BY id DESC`
	rows, err := s.db.QueryContext(ctx, q, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var ids []int64
	for rows.Next() {
		var id int64
		if err := rows.Scan(&id); err != nil {
			return nil, err
		}
		ids = append(ids, id)
	}
	return ids, rows.Err()
}

// pushOrder: laatste push bovenaan, repos zonder push onderaan, daarna op naam.
const pushOrder = `(r.pushed_at = ''), r.pushed_at DESC, r.name COLLATE NOCASE, r.id`

// ReposByOwner geeft de repositories van een gebruiker, laatste push eerst.
func (s *Store) ReposByOwner(ctx context.Context, userID int64) ([]Repo, error) {
	rows, err := s.db.QueryContext(ctx, repoSelect+` WHERE r.owner_kind = 'user' AND r.owner_id = ? ORDER BY `+pushOrder, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanRepos(rows)
}

// CountRepos telt repositories van een eigenaar. publicOnly slaat privé over.
func (s *Store) CountRepos(ctx context.Context, ownerKind string, ownerID int64, publicOnly bool) (int, error) {
	q := `SELECT COUNT(*) FROM repos WHERE owner_kind = ? AND owner_id = ?`
	if publicOnly {
		q += ` AND visibility = 'public'`
	}
	var n int
	err := s.db.QueryRowContext(ctx, q, ownerKind, ownerID).Scan(&n)
	return n, err
}

// ReposByGroup geeft de repositories van een groep.
func (s *Store) ReposByGroup(ctx context.Context, groupID int64) ([]Repo, error) {
	rows, err := s.db.QueryContext(ctx, repoSelect+` WHERE r.owner_kind = 'group' AND r.owner_id = ? ORDER BY `+pushOrder, groupID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanRepos(rows)
}

// ReposByIDs laadt een lijst.
func (s *Store) ReposByIDs(ctx context.Context, ids []int64) ([]Repo, error) {
	var out []Repo
	for _, id := range ids {
		r, err := s.RepoByID(ctx, id)
		if err != nil {
			return nil, err
		}
		out = append(out, r)
	}
	return out, nil
}

func scanRepos(rows *sql.Rows) ([]Repo, error) {
	var out []Repo
	for rows.Next() {
		var r Repo
		var created, pushed string
		var owner sql.NullString
		var paused int
		if err := rows.Scan(&r.ID, &r.OwnerKind, &r.OwnerID, &owner, &r.Name, &r.Description, &r.DefaultBranch, &r.Visibility, &created, &r.LogoMIME, &r.ReleaseVisibility, &pushed, &paused); err != nil {
			return nil, err
		}
		r.OwnerPath = owner.String
		r.CreatedAt = parseTime(created)
		if pushed != "" {
			r.PushedAt = parseTime(pushed)
		}
		r.CIPaused = paused == 1
		out = append(out, r)
	}
	return out, rows.Err()
}

// AllRepos is voor het verversen van hooks bij het starten.
func (s *Store) AllRepos(ctx context.Context) ([]Repo, error) {
	rows, err := s.db.QueryContext(ctx, repoSelect+` ORDER BY r.id`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanRepos(rows)
}

// EffectiveRole past de rolregels toe op een repository.
func (s *Store) EffectiveRole(ctx context.Context, userID, repoID int64) (access.Role, error) {
	if userID == 0 {
		return access.RoleNone, nil
	}
	if _, err := s.UserByID(ctx, userID); err != nil {
		return access.RoleNone, err
	}
	repo, err := s.RepoByID(ctx, repoID)
	if err != nil {
		return access.RoleNone, err
	}
	in := access.Input{}
	if repo.OwnerKind == "user" && repo.OwnerID == userID {
		in.UserOwner = true
	}
	if repo.OwnerKind == "group" {
		role, err := s.GroupRole(ctx, repo.OwnerID, userID)
		if err != nil {
			return access.RoleNone, err
		}
		in.GroupRole = role
	}
	var role string
	err = s.db.QueryRowContext(ctx, `SELECT role FROM repo_members WHERE repo_id = ? AND user_id = ?`, repoID, userID).Scan(&role)
	if err != nil && !errors.Is(err, sql.ErrNoRows) {
		return access.RoleNone, err
	}
	if err == nil {
		in.RepoRole = access.Role(role)
	}
	return access.Resolve(in), nil
}

// Member is een lid of een uitnodiging op gebruikersnaam.
type Member struct {
	UserID    int64
	Username  string
	AvatarURL string
	Role      access.Role
	Pending   bool
}

// SetMember zet een lidmaatschap. Een onbekende naam blijft wachten tot de eerste login.
func (s *Store) SetMember(ctx context.Context, scopeKind string, scopeID int64, username string, role access.Role) error {
	if scopeKind != "repo" && scopeKind != "group" {
		return i18n.E("err.scope")
	}
	if _, err := access.ParseRole(string(role)); err != nil {
		return err
	}
	username = strings.ToLower(strings.TrimSpace(username))
	if err := names.ValidUsername(username); err != nil {
		return err
	}
	if scopeKind == "group" && role != access.RoleMaintainer {
		g, err := s.GroupByID(ctx, scopeID)
		if err != nil {
			return err
		}
		owner, err := s.UserByID(ctx, g.OwnerID)
		if err == nil && owner.Username == username {
			return i18n.E("err.ownerRole")
		}
	}
	u, err := s.UserByUsername(ctx, username)
	if IsNotFound(err) {
		_, err = s.db.ExecContext(ctx, `INSERT INTO pending_members (scope_kind, scope_id, username, role) VALUES (?, ?, ?, ?)
			ON CONFLICT(scope_kind, scope_id, username) DO UPDATE SET role = excluded.role`, scopeKind, scopeID, username, string(role))
		return err
	}
	if err != nil {
		return err
	}
	table, col := "repo_members", "repo_id"
	if scopeKind == "group" {
		table, col = "group_members", "group_id"
	}
	_, err = s.db.ExecContext(ctx, `INSERT INTO `+table+` (`+col+`, user_id, role) VALUES (?, ?, ?)
		ON CONFLICT(`+col+`, user_id) DO UPDATE SET role = excluded.role`, scopeID, u.ID, string(role))
	return err
}

// RemoveMember haalt een lid of een uitnodiging weg.
func (s *Store) RemoveMember(ctx context.Context, scopeKind string, scopeID int64, username string) error {
	username = strings.ToLower(strings.TrimSpace(username))
	_, err := s.db.ExecContext(ctx, `DELETE FROM pending_members WHERE scope_kind = ? AND scope_id = ? AND username = ?`, scopeKind, scopeID, username)
	if err != nil {
		return err
	}
	u, err := s.UserByUsername(ctx, username)
	if IsNotFound(err) {
		return nil
	}
	if err != nil {
		return err
	}
	if scopeKind == "group" {
		g, err := s.GroupByID(ctx, scopeID)
		if err != nil {
			return err
		}
		if u.ID == g.OwnerID {
			return i18n.E("err.ownerRole")
		}
		_, err = s.db.ExecContext(ctx, `DELETE FROM group_members WHERE group_id = ? AND user_id = ?`, scopeID, u.ID)
		return err
	}
	_, err = s.db.ExecContext(ctx, `DELETE FROM repo_members WHERE repo_id = ? AND user_id = ?`, scopeID, u.ID)
	return err
}

// ListMembers geeft leden van een repository of groep, plus open uitnodigingen.
func (s *Store) ListMembers(ctx context.Context, scopeKind string, scopeID int64) ([]Member, error) {
	var q string
	if scopeKind == "group" {
		q = `SELECT u.id, u.username, u.avatar_url, m.role FROM group_members m JOIN users u ON u.id = m.user_id WHERE m.group_id = ? ORDER BY u.username`
	} else {
		q = `SELECT u.id, u.username, u.avatar_url, m.role FROM repo_members m JOIN users u ON u.id = m.user_id WHERE m.repo_id = ? ORDER BY u.username`
	}
	rows, err := s.db.QueryContext(ctx, q, scopeID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Member
	for rows.Next() {
		var m Member
		var role string
		if err := rows.Scan(&m.UserID, &m.Username, &m.AvatarURL, &role); err != nil {
			return nil, err
		}
		m.Role = access.Role(role)
		out = append(out, m)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	prows, err := s.db.QueryContext(ctx, `SELECT username, role FROM pending_members WHERE scope_kind = ? AND scope_id = ? ORDER BY username`, scopeKind, scopeID)
	if err != nil {
		return nil, err
	}
	defer prows.Close()
	for prows.Next() {
		var m Member
		var role string
		if err := prows.Scan(&m.Username, &role); err != nil {
			return nil, err
		}
		m.Role = access.Role(role)
		m.Pending = true
		out = append(out, m)
	}
	return out, prows.Err()
}

// ProtectedPatterns geeft de beschermde branchpatronen.
func (s *Store) ProtectedPatterns(ctx context.Context, repoID int64) ([]string, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT pattern FROM protected_branches WHERE repo_id = ? ORDER BY pattern`, repoID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []string
	for rows.Next() {
		var p string
		if err := rows.Scan(&p); err != nil {
			return nil, err
		}
		out = append(out, p)
	}
	return out, rows.Err()
}

// AddProtected voegt een patroon toe.
func (s *Store) AddProtected(ctx context.Context, repoID int64, pattern string) error {
	pattern = strings.TrimSpace(pattern)
	if pattern == "" || strings.Contains(pattern, " ") || strings.Contains(pattern, "..") {
		return i18n.E("err.pattern", pattern)
	}
	_, err := s.db.ExecContext(ctx, `INSERT INTO protected_branches (repo_id, pattern) VALUES (?, ?)`, repoID, pattern)
	if err != nil && strings.Contains(err.Error(), "UNIQUE") {
		return nil
	}
	return err
}

// RemoveProtected haalt een patroon weg.
func (s *Store) RemoveProtected(ctx context.Context, repoID int64, pattern string) error {
	_, err := s.db.ExecContext(ctx, `DELETE FROM protected_branches WHERE repo_id = ? AND pattern = ?`, repoID, pattern)
	return err
}

// ProtectedTagPatterns geeft de beschermde tagpatronen.
func (s *Store) ProtectedTagPatterns(ctx context.Context, repoID int64) ([]string, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT pattern FROM protected_tags WHERE repo_id = ? ORDER BY pattern`, repoID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []string
	for rows.Next() {
		var p string
		if err := rows.Scan(&p); err != nil {
			return nil, err
		}
		out = append(out, p)
	}
	return out, rows.Err()
}

// AddProtectedTag voegt een tagpatroon toe.
func (s *Store) AddProtectedTag(ctx context.Context, repoID int64, pattern string) error {
	pattern = strings.TrimSpace(pattern)
	if pattern == "" || strings.Contains(pattern, " ") || strings.Contains(pattern, "..") {
		return i18n.E("err.pattern", pattern)
	}
	_, err := s.db.ExecContext(ctx, `INSERT INTO protected_tags (repo_id, pattern) VALUES (?, ?)`, repoID, pattern)
	if err != nil && strings.Contains(err.Error(), "UNIQUE") {
		return nil
	}
	return err
}

// RemoveProtectedTag haalt een tagpatroon weg.
func (s *Store) RemoveProtectedTag(ctx context.Context, repoID int64, pattern string) error {
	_, err := s.db.ExecContext(ctx, `DELETE FROM protected_tags WHERE repo_id = ? AND pattern = ?`, repoID, pattern)
	return err
}

// SecretMeta is een geheim zonder de waarde.
type SecretMeta struct {
	Name      string
	Protected bool
	Invisible bool
	// Expand: $NAAM en ${NAAM} in de waarde worden in de runner vervangen.
	Expand    bool
	CreatedAt time.Time
}

// PutSecret bewaart een versleutelde waarde. De naam is een omgevingsvariabele.
// Bij een bestaande naam blijven waarde en protected bijgewerkt; invisible wordt nooit gewist.
func (s *Store) PutSecret(ctx context.Context, scopeKind string, scopeID int64, name, value string, protected bool) error {
	if scopeKind != "repo" && scopeKind != "group" {
		return i18n.E("err.scope")
	}
	if err := access.ValidSecretName(name); err != nil {
		return err
	}
	if strings.TrimSpace(value) == "" {
		return i18n.E("err.emptyValue")
	}
	nonce, ct, err := s.encrypt(value)
	if err != nil {
		return err
	}
	_, err = s.db.ExecContext(ctx, `INSERT INTO secrets (scope_kind, scope_id, name, ciphertext, nonce, protected, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)
		ON CONFLICT(scope_kind, scope_id, name) DO UPDATE SET ciphertext = excluded.ciphertext, nonce = excluded.nonce, protected = excluded.protected`,
		scopeKind, scopeID, name, ct, nonce, boolInt(protected), now())
	return err
}

// DeleteSecret verwijdert een geheim.
func (s *Store) DeleteSecret(ctx context.Context, scopeKind string, scopeID int64, name string) error {
	res, err := s.db.ExecContext(ctx, `DELETE FROM secrets WHERE scope_kind = ? AND scope_id = ? AND name = ?`, scopeKind, scopeID, name)
	if err != nil {
		return err
	}
	n, _ := res.RowsAffected()
	if n == 0 {
		return errNotFound
	}
	return nil
}

// CreateSecret voegt een geheim toe. Een bestaande naam wordt niet overschreven.
func (s *Store) CreateSecret(ctx context.Context, scopeKind string, scopeID int64, name, value string, protected, invisible bool) error {
	nonce, ct, err := s.secretCipher(scopeKind, name, value)
	if err != nil {
		return err
	}
	_, err = s.db.ExecContext(ctx, `INSERT INTO secrets (scope_kind, scope_id, name, ciphertext, nonce, protected, invisible, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
		scopeKind, scopeID, name, ct, nonce, boolInt(protected), boolInt(invisible), now())
	if err != nil && strings.Contains(err.Error(), "UNIQUE") {
		return i18n.E("err.secretExists", name)
	}
	return err
}

// UpdateSecret wijzigt waarde en protected van een zichtbaar geheim.
func (s *Store) UpdateSecret(ctx context.Context, scopeKind string, scopeID int64, name, value string, protected bool) error {
	return s.writeSecret(ctx, scopeKind, scopeID, name, value, protected, false)
}

// ReplaceSecret zet een nieuwe waarde op een onzichtbaar geheim. De vlag blijft aan.
func (s *Store) ReplaceSecret(ctx context.Context, scopeKind string, scopeID int64, name, value string, protected bool) error {
	return s.writeSecret(ctx, scopeKind, scopeID, name, value, protected, true)
}

func (s *Store) writeSecret(ctx context.Context, scopeKind string, scopeID int64, name, value string, protected, invisible bool) error {
	nonce, ct, err := s.secretCipher(scopeKind, name, value)
	if err != nil {
		return err
	}
	res, err := s.db.ExecContext(ctx, `UPDATE secrets SET ciphertext = ?, nonce = ?, protected = ? WHERE scope_kind = ? AND scope_id = ? AND name = ? AND invisible = ?`,
		ct, nonce, boolInt(protected), scopeKind, scopeID, name, boolInt(invisible))
	if err != nil {
		return err
	}
	n, _ := res.RowsAffected()
	if n == 1 {
		return nil
	}
	return s.secretMiss(ctx, scopeKind, scopeID, name, invisible)
}

func (s *Store) secretCipher(scopeKind, name, value string) (nonce, ct []byte, err error) {
	if scopeKind != "repo" && scopeKind != "group" {
		return nil, nil, i18n.E("err.scope")
	}
	if err := access.ValidSecretName(name); err != nil {
		return nil, nil, err
	}
	if strings.TrimSpace(value) == "" {
		return nil, nil, i18n.E("err.emptyValue")
	}
	return s.encrypt(value)
}

func (s *Store) secretMiss(ctx context.Context, scopeKind string, scopeID int64, name string, wantInvisible bool) error {
	var inv int
	err := s.db.QueryRowContext(ctx, `SELECT invisible FROM secrets WHERE scope_kind = ? AND scope_id = ? AND name = ?`, scopeKind, scopeID, name).Scan(&inv)
	if errors.Is(err, sql.ErrNoRows) {
		return errNotFound
	}
	if err != nil {
		return err
	}
	if wantInvisible && inv == 0 {
		return i18n.E("err.secretVisible", name)
	}
	if !wantInvisible && inv == 1 {
		return i18n.E("err.secretHidden", name)
	}
	return errNotFound
}

// RevealSecret geeft de waarde van een zichtbaar geheim.
func (s *Store) RevealSecret(ctx context.Context, scopeKind string, scopeID int64, name string) (string, error) {
	if scopeKind != "repo" && scopeKind != "group" {
		return "", i18n.E("err.scope")
	}
	var ct, nonce []byte
	var inv int
	err := s.db.QueryRowContext(ctx, `SELECT ciphertext, nonce, invisible FROM secrets WHERE scope_kind = ? AND scope_id = ? AND name = ?`, scopeKind, scopeID, name).Scan(&ct, &nonce, &inv)
	if errors.Is(err, sql.ErrNoRows) {
		return "", errNotFound
	}
	if err != nil {
		return "", err
	}
	if inv == 1 {
		return "", i18n.E("err.secretHidden", name)
	}
	return s.decrypt(nonce, ct)
}

// ListSecrets geeft namen, niet de waarden.
func (s *Store) ListSecrets(ctx context.Context, scopeKind string, scopeID int64) ([]SecretMeta, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT name, protected, invisible, expand, created_at FROM secrets WHERE scope_kind = ? AND scope_id = ? ORDER BY name`, scopeKind, scopeID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []SecretMeta
	for rows.Next() {
		var m SecretMeta
		var p, inv, exp int
		var created string
		if err := rows.Scan(&m.Name, &p, &inv, &exp, &created); err != nil {
			return nil, err
		}
		m.Protected = p == 1
		m.Invisible = inv == 1
		m.Expand = exp == 1
		m.CreatedAt = parseTime(created)
		out = append(out, m)
	}
	return out, rows.Err()
}

// SecretValues ontcijfert geheimen voor een job of webhook.
// refProtected bepaalt of beschermde geheimen meegaan.
func (s *Store) SecretValues(ctx context.Context, scopeKind string, scopeID int64, refProtected bool) (map[string]string, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT name, ciphertext, nonce, protected FROM secrets WHERE scope_kind = ? AND scope_id = ?`, scopeKind, scopeID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := map[string]string{}
	for rows.Next() {
		var name string
		var ct, nonce []byte
		var p int
		if err := rows.Scan(&name, &ct, &nonce, &p); err != nil {
			return nil, err
		}
		if !access.InjectSecret(p == 1, refProtected) {
			continue
		}
		val, err := s.decrypt(nonce, ct)
		if err != nil {
			return nil, err
		}
		out[name] = val
	}
	return out, rows.Err()
}

// TouchPushed zet het moment van de laatste push.
func (s *Store) TouchPushed(ctx context.Context, repoID int64, at time.Time) error {
	_, err := s.db.ExecContext(ctx, `UPDATE repos SET pushed_at = ? WHERE id = ? AND (pushed_at = '' OR pushed_at < ?)`,
		at.UTC().Format(time.RFC3339), repoID, at.UTC().Format(time.RFC3339))
	return err
}

// SortReposByPush sorteert zoals pushOrder: laatste push eerst, zonder push
// onderaan, daarna op naam.
func SortReposByPush(list []Repo) {
	sort.SliceStable(list, func(i, j int) bool {
		a, b := list[i], list[j]
		if a.PushedAt.IsZero() != b.PushedAt.IsZero() {
			return !a.PushedAt.IsZero()
		}
		if !a.PushedAt.Equal(b.PushedAt) {
			return a.PushedAt.After(b.PushedAt)
		}
		if x, y := strings.ToLower(a.FullPath()), strings.ToLower(b.FullPath()); x != y {
			return x < y
		}
		return a.ID < b.ID
	})
}

// GroupPushTimes geeft per groep de laatste push in een van haar repos.
// Groepen zonder push ontbreken in de map.
func (s *Store) GroupPushTimes(ctx context.Context) (map[int64]time.Time, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT owner_id, MAX(pushed_at) FROM repos WHERE owner_kind = 'group' AND pushed_at <> '' GROUP BY owner_id`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := map[int64]time.Time{}
	for rows.Next() {
		var id int64
		var at string
		if err := rows.Scan(&id, &at); err != nil {
			return nil, err
		}
		out[id] = parseTime(at)
	}
	return out, rows.Err()
}

// SortGroupsByPush: laatste push eerst, groepen zonder push onderaan, dan op naam.
func SortGroupsByPush(list []Group, at map[int64]time.Time) {
	sort.SliceStable(list, func(i, j int) bool {
		a, b := at[list[i].ID], at[list[j].ID]
		if a.IsZero() != b.IsZero() {
			return !a.IsZero()
		}
		if !a.Equal(b) {
			return a.After(b)
		}
		return strings.ToLower(list[i].Name) < strings.ToLower(list[j].Name)
	})
}

// SetSecretExpand zet of de waarde variabelen uitbreidt.
func (s *Store) SetSecretExpand(ctx context.Context, scopeKind string, scopeID int64, name string, on bool) error {
	_, err := s.db.ExecContext(ctx, `UPDATE secrets SET expand = ? WHERE scope_kind = ? AND scope_id = ? AND name = ?`, boolInt(on), scopeKind, scopeID, name)
	return err
}

// SecretExpandFlags geeft per geheimnaam of expand aan staat.
func (s *Store) SecretExpandFlags(ctx context.Context, scopeKind string, scopeID int64) (map[string]bool, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT name, expand FROM secrets WHERE scope_kind = ? AND scope_id = ?`, scopeKind, scopeID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := map[string]bool{}
	for rows.Next() {
		var name string
		var e int
		if err := rows.Scan(&name, &e); err != nil {
			return nil, err
		}
		out[name] = e == 1
	}
	return out, rows.Err()
}

// ErrGroupNotEmpty: een groep met repositories kan niet weg.
type ErrGroupNotEmpty struct{ Repos int }

func (e ErrGroupNotEmpty) Error() string {
	return fmt.Sprintf("group still has %d repositories", e.Repos)
}

// DeleteGroup verwijdert een lege groep met leden, uitnodigingen, geheimen,
// webhooks, goedkeuringsregels en groepsrunners. Een groep met repositories
// geeft ErrGroupNotEmpty; er is geen force.
func (s *Store) DeleteGroup(ctx context.Context, id int64) error {
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback()
	var n int
	if err := tx.QueryRowContext(ctx, `SELECT COUNT(*) FROM repos WHERE owner_kind = 'group' AND owner_id = ?`, id).Scan(&n); err != nil {
		return err
	}
	if n > 0 {
		return ErrGroupNotEmpty{Repos: n}
	}
	for _, q := range []string{
		`DELETE FROM group_members WHERE group_id = ?`,
		`DELETE FROM group_runner_optin WHERE group_id = ?`,
		`DELETE FROM pending_members WHERE scope_kind = 'group' AND scope_id = ?`,
		`DELETE FROM secrets WHERE scope_kind = 'group' AND scope_id = ?`,
		`DELETE FROM webhooks WHERE scope_kind = 'group' AND scope_id = ?`,
		`DELETE FROM approval_rules WHERE scope_kind = 'group' AND scope_id = ?`,
		`DELETE FROM runner_reg_tokens WHERE owner_kind = 'group' AND owner_id = ?`,
		`UPDATE runners SET revoked = 1 WHERE owner_kind = 'group' AND owner_id = ?`,
		`DELETE FROM groups WHERE id = ?`,
	} {
		if _, err := tx.ExecContext(ctx, q, id); err != nil {
			return err
		}
	}
	return tx.Commit()
}
