package store

import (
	"context"
	"database/sql"
	"encoding/json"
	"errors"
	"strings"
	"time"

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

// Webhook is een uitgaande melding.
type Webhook struct {
	ID           int64
	ScopeKind    string
	ScopeID      int64
	Name         string
	Preset       string
	URLTemplate  string
	Headers      map[string]string
	BodyTemplate string
	Events       []string
	BranchFilter string
	Active       bool
	Method       string
	Fields       string
}

// CreateWebhook bewaart een webhook.
func (s *Store) CreateWebhook(ctx context.Context, w Webhook) (int64, error) {
	if w.Headers == nil {
		w.Headers = map[string]string{}
	}
	if w.Preset == "" {
		w.Preset = "generic"
	}
	if w.Method == "" {
		w.Method = "POST"
	}
	res, err := s.db.ExecContext(ctx, `INSERT INTO webhooks (scope_kind, scope_id, name, preset, url_template, headers_json, body_template, events_json, branch_filter, active, method, fields_json, created_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 1, ?, ?, ?)`,
		w.ScopeKind, w.ScopeID, w.Name, w.Preset, w.URLTemplate, mustJSON(w.Headers), w.BodyTemplate, mustJSON(w.Events), w.BranchFilter, w.Method, w.Fields, now())
	if err != nil {
		return 0, err
	}
	return res.LastInsertId()
}

// ListWebhooks geeft de webhooks van een repository of groep.
func (s *Store) ListWebhooks(ctx context.Context, scopeKind string, scopeID int64) ([]Webhook, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT `+webhookCols+` FROM webhooks WHERE scope_kind = ? AND scope_id = ? ORDER BY id`, scopeKind, scopeID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Webhook
	for rows.Next() {
		w, err := scanWebhook(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, w)
	}
	return out, rows.Err()
}

const webhookCols = `id, scope_kind, scope_id, name, preset, url_template, headers_json, body_template, events_json, branch_filter, active, method, fields_json`

// Webhook leest één webhook in dit scope.
func (s *Store) Webhook(ctx context.Context, scopeKind string, scopeID, id int64) (Webhook, error) {
	w, err := scanWebhook(s.db.QueryRowContext(ctx, `SELECT `+webhookCols+` FROM webhooks WHERE id = ? AND scope_kind = ? AND scope_id = ?`, id, scopeKind, scopeID))
	if errors.Is(err, sql.ErrNoRows) {
		return Webhook{}, errNotFound
	}
	return w, err
}

// UpdateWebhook werkt een webhook in dit scope bij.
func (s *Store) UpdateWebhook(ctx context.Context, w Webhook) error {
	if w.Headers == nil {
		w.Headers = map[string]string{}
	}
	if w.Preset == "" {
		w.Preset = "generic"
	}
	if w.Method == "" {
		w.Method = "POST"
	}
	res, err := s.db.ExecContext(ctx, `UPDATE webhooks SET name = ?, preset = ?, url_template = ?, headers_json = ?, body_template = ?, events_json = ?, branch_filter = ?, method = ?, fields_json = ? WHERE id = ? AND scope_kind = ? AND scope_id = ?`,
		w.Name, w.Preset, w.URLTemplate, mustJSON(w.Headers), w.BodyTemplate, mustJSON(w.Events), w.BranchFilter, w.Method, w.Fields, w.ID, w.ScopeKind, w.ScopeID)
	if err != nil {
		return err
	}
	n, _ := res.RowsAffected()
	if n == 0 {
		return errNotFound
	}
	return nil
}

func scanWebhook(row interface{ Scan(...any) error }) (Webhook, error) {
	var w Webhook
	var headers, events string
	var active int
	if err := row.Scan(&w.ID, &w.ScopeKind, &w.ScopeID, &w.Name, &w.Preset, &w.URLTemplate, &headers, &w.BodyTemplate, &events, &w.BranchFilter, &active, &w.Method, &w.Fields); err != nil {
		return Webhook{}, err
	}
	w.Active = active == 1
	_ = json.Unmarshal([]byte(headers), &w.Headers)
	_ = json.Unmarshal([]byte(events), &w.Events)
	if w.Headers == nil {
		w.Headers = map[string]string{}
	}
	return w, nil
}

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

// AddDelivery bewaart het resultaat van een poging.
func (s *Store) AddDelivery(ctx context.Context, webhookID int64, eventType string, status int, errText string) error {
	_, err := s.db.ExecContext(ctx, `INSERT INTO webhook_deliveries (webhook_id, event_type, status_code, error, created_at) VALUES (?, ?, ?, ?, ?)`,
		webhookID, eventType, status, trim(errText, 500), now())
	return err
}

// Delivery is één poging.
type Delivery struct {
	EventType  string
	StatusCode int
	Error      string
	CreatedAt  time.Time
}

// RecentDeliveries geeft de laatste pogingen.
func (s *Store) RecentDeliveries(ctx context.Context, webhookID int64) ([]Delivery, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT event_type, status_code, error, created_at FROM webhook_deliveries WHERE webhook_id = ? ORDER BY id DESC LIMIT 8`, webhookID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Delivery
	for rows.Next() {
		var d Delivery
		var created string
		if err := rows.Scan(&d.EventType, &d.StatusCode, &d.Error, &created); err != nil {
			return nil, err
		}
		d.CreatedAt = parseTime(created)
		out = append(out, d)
	}
	return out, rows.Err()
}

// MergeRequest is een basisverzoek om een branch samen te voegen.
type MergeRequest struct {
	ID                 int64
	RepoID             int64
	Number             int64
	Title              string
	Description        string
	SourceBranch       string
	TargetBranch       string
	AuthorID           int64
	Author             string
	Status             string
	CreatedAt          time.Time
	UpdatedAt          time.Time
	Draft              bool
	Labels             string
	RemoveSourceBranch bool
	Squash             bool
	SquashMessage      string
}

// CreateMergeRequest nummert per repository.
func (s *Store) CreateMergeRequest(ctx context.Context, repoID, authorID int64, title, description, source, target string) (MergeRequest, error) {
	title = trim(title, 200)
	if title == "" {
		return MergeRequest{}, i18n.E("err.mrTitle")
	}
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return MergeRequest{}, err
	}
	defer func() { _ = tx.Rollback() }()
	var n int64
	if err := tx.QueryRowContext(ctx, `SELECT COALESCE(MAX(number), 0) FROM merge_requests WHERE repo_id = ?`, repoID).Scan(&n); err != nil {
		return MergeRequest{}, err
	}
	ts := now()
	res, err := tx.ExecContext(ctx, `INSERT INTO merge_requests (repo_id, number, title, description, source_branch, target_branch, author_id, status, created_at, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, 'open', ?, ?)`, repoID, n+1, title, description, source, target, authorID, ts, ts)
	if err != nil {
		return MergeRequest{}, err
	}
	id, _ := res.LastInsertId()
	if err := tx.Commit(); err != nil {
		return MergeRequest{}, err
	}
	return s.MergeRequest(ctx, repoID, id)
}

// MergeRequest leest op interne id.
func (s *Store) MergeRequest(ctx context.Context, repoID, id int64) (MergeRequest, error) {
	row := s.db.QueryRowContext(ctx, mrSelect+` WHERE m.repo_id = ? AND m.id = ?`, repoID, id)
	return scanMR(row)
}

// MergeRequestByNumber leest !nummer.
func (s *Store) MergeRequestByNumber(ctx context.Context, repoID, number int64) (MergeRequest, error) {
	row := s.db.QueryRowContext(ctx, mrSelect+` WHERE m.repo_id = ? AND m.number = ?`, repoID, number)
	return scanMR(row)
}

const mrSelect = `SELECT m.id, m.repo_id, m.number, m.title, m.description, m.source_branch, m.target_branch, m.author_id, u.username, m.status, m.created_at, m.updated_at, m.draft, m.labels, m.remove_source_branch, m.squash, m.squash_message
FROM merge_requests m JOIN users u ON u.id = m.author_id`

func scanMR(row *sql.Row) (MergeRequest, error) {
	var m MergeRequest
	var created, updated string
	var draft, remove, squash int
	if err := row.Scan(&m.ID, &m.RepoID, &m.Number, &m.Title, &m.Description, &m.SourceBranch, &m.TargetBranch, &m.AuthorID, &m.Author, &m.Status, &created, &updated, &draft, &m.Labels, &remove, &squash, &m.SquashMessage); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return MergeRequest{}, errNotFound
		}
		return MergeRequest{}, err
	}
	applyMR(&m, created, updated, draft, remove, squash)
	return m, nil
}

func applyMR(m *MergeRequest, created, updated string, draft, remove, squash int) {
	m.CreatedAt = parseTime(created)
	m.UpdatedAt = parseTime(updated)
	m.Draft = draft == 1
	m.RemoveSourceBranch = remove == 1
	m.Squash = squash == 1
}

// ListMergeRequests geeft verzoeken, open eerst.
func (s *Store) ListMergeRequests(ctx context.Context, repoID int64) ([]MergeRequest, error) {
	rows, err := s.db.QueryContext(ctx, mrSelect+` WHERE m.repo_id = ? ORDER BY CASE m.status WHEN 'open' THEN 0 ELSE 1 END, m.number DESC`, repoID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanMRList(rows)
}

// SetMergeRequestStatus zet open, merged of closed.
func (s *Store) SetMergeRequestStatus(ctx context.Context, id int64, status string) error {
	_, err := s.db.ExecContext(ctx, `UPDATE merge_requests SET status = ?, updated_at = ? WHERE id = ?`, status, now(), id)
	return err
}

// OpenMergeRequestsBySource geeft open verzoeken waarvan de bronbranch deze is.
func (s *Store) OpenMergeRequestsBySource(ctx context.Context, repoID int64, branch string) ([]MergeRequest, error) {
	return s.openMergeRequests(ctx, repoID, "source_branch", branch)
}

// OpenMergeRequestsByTarget geeft open verzoeken waarvan de doelbranch deze is.
func (s *Store) OpenMergeRequestsByTarget(ctx context.Context, repoID int64, branch string) ([]MergeRequest, error) {
	return s.openMergeRequests(ctx, repoID, "target_branch", branch)
}

func (s *Store) openMergeRequests(ctx context.Context, repoID int64, column, branch string) ([]MergeRequest, error) {
	if column != "source_branch" && column != "target_branch" {
		return nil, errNotFound
	}
	rows, err := s.db.QueryContext(ctx, mrSelect+` WHERE m.repo_id = ? AND m.`+column+` = ? AND m.status = 'open' ORDER BY m.number`, repoID, branch)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanMRList(rows)
}

func scanMRList(rows *sql.Rows) ([]MergeRequest, error) {
	var out []MergeRequest
	for rows.Next() {
		var m MergeRequest
		var created, updated string
		var draft, remove, squash int
		if err := rows.Scan(&m.ID, &m.RepoID, &m.Number, &m.Title, &m.Description, &m.SourceBranch, &m.TargetBranch, &m.AuthorID, &m.Author, &m.Status, &created, &updated, &draft, &m.Labels, &remove, &squash, &m.SquashMessage); err != nil {
			return nil, err
		}
		applyMR(&m, created, updated, draft, remove, squash)
		out = append(out, m)
	}
	return out, rows.Err()
}

// AttachPipelineMR koppelt een pipeline aan een merge request.
func (s *Store) AttachPipelineMR(ctx context.Context, pipelineID, mrID int64) error {
	_, err := s.db.ExecContext(ctx, `UPDATE pipelines SET mr_id = ? WHERE id = ?`, mrID, pipelineID)
	return err
}

// LatestMergePipeline is de nieuwste pipeline van deze merge request.
func (s *Store) LatestMergePipeline(ctx context.Context, repoID, mrID int64) (Pipeline, error) {
	p, err := scanPipelineRow(s.db.QueryRowContext(ctx, pipelineCols+` WHERE repo_id = ? AND mr_id = ? ORDER BY id DESC LIMIT 1`, repoID, mrID))
	if errors.Is(err, sql.ErrNoRows) {
		return Pipeline{}, errNotFound
	}
	return p, err
}

// PipelineBlocksMerge zegt of deze jobs een merge tegenhouden.
// Een lege tekst betekent dat elke job klaar is en geen harde fout heeft.
// allow_failure mag mislukt zijn, maar moet wel af zijn.
func PipelineBlocksMerge(jobs []Job) string {
	if len(jobs) == 0 {
		return "waiting"
	}
	hard, open := false, false
	for _, j := range jobs {
		switch j.Status {
		case "success", "skipped":
		case "failed", "canceled":
			if !j.AllowFailure {
				hard = true
			}
		default:
			open = true
		}
	}
	if open {
		return "running"
	}
	if hard {
		return "failed"
	}
	return ""
}

// NewJob is een job die nog de wachtrij in moet.
type NewJob struct {
	Name           string
	Stage          string
	Position       int
	Image          string
	Before         string
	Script         string
	After          string
	Needs          []string
	Optional       []string
	NeedsSet       bool
	Variables      map[string]string
	ArtifactPaths  []string
	ArtifactExpire time.Duration
	AllowFailure   bool
	When           string
	Timeout        time.Duration
	Tags           []string
	CachePaths     []string
	CacheKey       string
	ResourceGroup  string
	RetryMax       int
	RetryWhen      []string
	Services       []Service
	Environment    string
	EnvironmentURL string
	Interruptible  bool
}

// Service is een sidecar die de runner naast de job start.
type Service struct {
	Name       string            `json:"name"`
	Alias      string            `json:"alias"`
	Command    []string          `json:"command,omitempty"`
	Entrypoint []string          `json:"entrypoint,omitempty"`
	Variables  map[string]string `json:"variables,omitempty"`
}

// Pipeline is een draai van blue-ci-cd.yml.
type Pipeline struct {
	ID          int64
	RepoID      int64
	CommitSHA   string
	Ref         string
	Status      string
	Source      string
	Actor       string
	CommitTitle string
	CreatedAt   time.Time
	StartedAt   time.Time
	FinishedAt  time.Time
	MRID        int64
	// TargetSHA is de doelcommit van een merge-request-pipeline.
	// Leeg zolang de rij nog niet aan een merge request hangt.
	// Een nieuwe doelcommit start een nieuwe pipeline.
	TargetSHA string
}

// Job is één container in een pipeline.
type Job struct {
	ID             int64
	PipelineID     int64
	RepoID         int64
	Name           string
	Stage          string
	Position       int
	Status         string
	Image          string
	Before         string
	Script         string
	After          string
	Needs          []string
	Optional       []string
	NeedsSet       bool
	Variables      map[string]string
	ArtifactPaths  []string
	ArtifactExpire time.Duration
	AllowFailure   bool
	When           string
	Timeout        time.Duration
	Tags           []string
	CachePaths     []string
	CacheKey       string
	ResourceGroup  string
	RetryMax       int
	RetryWhen      []string
	Attempt        int
	Services       []Service
	Environment    string
	EnvironmentURL string
	Interruptible  bool
	Log            string
	CommitSHA      string
	Ref            string
	StartedAt      time.Time
	FinishedAt     time.Time
}

// RecordConfigFailure bewaart een pipeline die niet kon starten.
// Dezelfde repo, ref, sha, bron, merge request en doel-sha levert de
// bestaande pipeline terug. Een kapotte push die al bestaat wordt aan
// de merge request gekoppeld in plaats van een tweede mislukte rij.
func (s *Store) RecordConfigFailure(ctx context.Context, repoID int64, sha, ref, source, message string, mrID int64, targetSHA string) (int64, bool, error) {
	if source == "" {
		source = "push"
	}
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return 0, false, err
	}
	defer func() { _ = tx.Rollback() }()
	if id, ok, err := existingPipeline(ctx, tx, repoID, sha, ref, source, mrID, targetSHA); err != nil {
		return 0, false, err
	} else if ok {
		return finishExisting(ctx, tx, id)
	}
	if source == "push" && mrID == 0 {
		if id, ok, err := newestPush(ctx, tx, repoID, sha, ref); err != nil {
			return 0, false, err
		} else if ok {
			return finishExisting(ctx, tx, id)
		}
	}
	if mrID != 0 {
		if id, ok, err := configFailurePush(ctx, tx, repoID, sha, ref, mrID, targetSHA); err != nil {
			return 0, false, err
		} else if ok {
			if err := bindPipelineMR(ctx, tx, id, mrID, targetSHA); err != nil {
				return 0, false, err
			}
			return finishExisting(ctx, tx, id)
		}
	}
	ts := now()
	res, err := tx.ExecContext(ctx, `INSERT INTO pipelines (repo_id, commit_sha, ref, status, source, created_at, finished_at, mr_id, target_sha)
		SELECT ?, ?, ?, 'failed', ?, ?, ?, ?, ?
		WHERE NOT EXISTS (
			SELECT 1 FROM pipelines WHERE repo_id = ? AND commit_sha = ? AND ref = ? AND source = ? AND mr_id = ? AND target_sha = ?
		)`, repoID, sha, ref, source, ts, ts, mrID, targetSHA, repoID, sha, ref, source, mrID, targetSHA)
	if err != nil {
		return 0, false, err
	}
	if n, _ := res.RowsAffected(); n == 0 {
		id, err := pipelineKeyID(ctx, tx, repoID, sha, ref, source, mrID, targetSHA)
		if err != nil {
			return 0, false, err
		}
		return finishExisting(ctx, tx, id)
	}
	id, _ := res.LastInsertId()
	_, err = tx.ExecContext(ctx, `INSERT INTO jobs (pipeline_id, repo_id, name, stage, position, status, image, script, needs_json, needs_set, variables_json, artifact_paths, artifact_expire_seconds, allow_failure, log, finished_at)
		VALUES (?, ?, 'blue-ci-cd', 'test', 0, 'failed', 'alpine:3.20', 'exit 1', '[]', 0, '{}', '[]', 0, 0, ?, ?)`, id, repoID, message, ts)
	if err != nil {
		return 0, false, err
	}
	if err := tx.Commit(); err != nil {
		return 0, false, err
	}
	return id, true, nil
}

// CreatePipeline maakt een pipeline met jobs in de wachtrij.
// Elke aanroep voegt een rij toe. Starten via een push of merge request
// gaat via OpenPipeline, zodat een dubbele hook dezelfde run teruggeeft.
func (s *Store) CreatePipeline(ctx context.Context, repoID int64, sha, ref, source, actor, title string, jobs []NewJob) (int64, error) {
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return 0, err
	}
	defer func() { _ = tx.Rollback() }()
	res, err := tx.ExecContext(ctx, `INSERT INTO pipelines (repo_id, commit_sha, ref, status, source, actor, commit_title, created_at) VALUES (?, ?, ?, 'queued', ?, ?, ?, ?)`, repoID, sha, ref, source, actor, title, now())
	if err != nil {
		return 0, err
	}
	id, _ := res.LastInsertId()
	if err := insertPipelineJobs(ctx, tx, id, repoID, jobs); err != nil {
		return 0, err
	}
	if err := tx.Commit(); err != nil {
		return 0, err
	}
	return id, nil
}

// OpenPipeline maakt een pipeline, of geeft de bestaande terug.
// De sleutel is repo, ref, sha, bron, merge request en doel-sha.
// Een push voor dezelfde sha blijft één rij, ook nadat een merge request
// hem heeft gekoppeld. Kiezen push en merge request dezelfde jobs, dan
// koppelt deze aanroep de push-rij en maakt hij geen tweede pipeline.
// Een andere jobset of een nieuwe doel-sha start wel een eigen rij.
// created is false wanneer een eerdere trigger de run al had.
func (s *Store) OpenPipeline(ctx context.Context, repoID int64, sha, ref, source, actor, title, targetSHA string, mrID int64, jobs []NewJob) (int64, bool, error) {
	if source == "" {
		source = "push"
	}
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return 0, false, err
	}
	defer func() { _ = tx.Rollback() }()
	if id, ok, err := existingPipeline(ctx, tx, repoID, sha, ref, source, mrID, targetSHA); err != nil {
		return 0, false, err
	} else if ok {
		return finishExisting(ctx, tx, id)
	}
	if source == "push" && mrID == 0 {
		if id, ok, err := newestPush(ctx, tx, repoID, sha, ref); err != nil {
			return 0, false, err
		} else if ok {
			return finishExisting(ctx, tx, id)
		}
	}
	if mrID != 0 {
		if id, ok, err := claimMatchingPush(ctx, tx, repoID, sha, ref, mrID, targetSHA, jobs); err != nil {
			return 0, false, err
		} else if ok {
			return finishExisting(ctx, tx, id)
		}
	}
	notExists := `SELECT 1 FROM pipelines WHERE repo_id = ? AND commit_sha = ? AND ref = ? AND source = ? AND mr_id = ? AND target_sha = ?`
	args := []any{repoID, sha, ref, source, actor, title, now(), mrID, targetSHA, repoID, sha, ref, source, mrID, targetSHA}
	if source == "push" && mrID == 0 {
		notExists = `SELECT 1 FROM pipelines WHERE repo_id = ? AND commit_sha = ? AND ref = ? AND source = 'push'`
		args = []any{repoID, sha, ref, source, actor, title, now(), mrID, targetSHA, repoID, sha, ref}
	}
	res, err := tx.ExecContext(ctx, `INSERT INTO pipelines (repo_id, commit_sha, ref, status, source, actor, commit_title, created_at, mr_id, target_sha)
		SELECT ?, ?, ?, 'queued', ?, ?, ?, ?, ?, ?
		WHERE NOT EXISTS (`+notExists+`)`, args...)
	if err != nil {
		return 0, false, err
	}
	if n, _ := res.RowsAffected(); n == 0 {
		id, ok, err := existingPipeline(ctx, tx, repoID, sha, ref, source, mrID, targetSHA)
		if err != nil {
			return 0, false, err
		}
		if !ok && source == "push" && mrID == 0 {
			id, ok, err = newestPush(ctx, tx, repoID, sha, ref)
			if err != nil {
				return 0, false, err
			}
		}
		if !ok {
			return 0, false, errors.New("pipeline ontbreekt na een lege insert")
		}
		return finishExisting(ctx, tx, id)
	}
	id, _ := res.LastInsertId()
	if err := insertPipelineJobs(ctx, tx, id, repoID, jobs); err != nil {
		return 0, false, err
	}
	if err := tx.Commit(); err != nil {
		return 0, false, err
	}
	return id, true, nil
}

func finishExisting(ctx context.Context, tx *sql.Tx, id int64) (int64, bool, error) {
	if err := tx.Commit(); err != nil {
		return 0, false, err
	}
	return id, false, nil
}

func existingPipeline(ctx context.Context, tx *sql.Tx, repoID int64, sha, ref, source string, mrID int64, targetSHA string) (int64, bool, error) {
	id, err := pipelineKeyID(ctx, tx, repoID, sha, ref, source, mrID, targetSHA)
	if errors.Is(err, sql.ErrNoRows) {
		return 0, false, nil
	}
	if err != nil {
		return 0, false, err
	}
	return id, true, nil
}

func pipelineKeyID(ctx context.Context, tx *sql.Tx, repoID int64, sha, ref, source string, mrID int64, targetSHA string) (int64, error) {
	var id int64
	err := tx.QueryRowContext(ctx, `SELECT id FROM pipelines WHERE repo_id = ? AND commit_sha = ? AND ref = ? AND source = ? AND mr_id = ? AND target_sha = ? ORDER BY id LIMIT 1`,
		repoID, sha, ref, source, mrID, targetSHA).Scan(&id)
	return id, err
}

func newestPush(ctx context.Context, tx *sql.Tx, repoID int64, sha, ref string) (int64, bool, error) {
	var id int64
	err := tx.QueryRowContext(ctx, `SELECT id FROM pipelines WHERE repo_id = ? AND commit_sha = ? AND ref = ? AND source = 'push' ORDER BY id DESC LIMIT 1`,
		repoID, sha, ref).Scan(&id)
	if errors.Is(err, sql.ErrNoRows) {
		return 0, false, nil
	}
	if err != nil {
		return 0, false, err
	}
	return id, true, nil
}

// configFailurePush zoekt een mislukte push die alleen de kapotte yaml bewaart.
func configFailurePush(ctx context.Context, tx *sql.Tx, repoID int64, sha, ref string, mrID int64, targetSHA string) (int64, bool, error) {
	var id int64
	err := tx.QueryRowContext(ctx, `SELECT p.id FROM pipelines p
		WHERE p.repo_id = ? AND p.commit_sha = ? AND p.ref = ? AND p.source = 'push' AND p.status = 'failed'
		AND (p.mr_id = 0 OR p.mr_id = ?) AND (p.target_sha = '' OR p.target_sha = ?)
		AND EXISTS (SELECT 1 FROM jobs j WHERE j.pipeline_id = p.id AND j.name = 'blue-ci-cd')
		ORDER BY p.id DESC LIMIT 1`, repoID, sha, ref, mrID, targetSHA).Scan(&id)
	if errors.Is(err, sql.ErrNoRows) {
		return 0, false, nil
	}
	if err != nil {
		return 0, false, err
	}
	return id, true, nil
}

type pushCandidate struct {
	id     int64
	mrID   int64
	target string
}

// claimMatchingPush koppelt een push-pipeline met dezelfde jobs aan de merge request.
func claimMatchingPush(ctx context.Context, tx *sql.Tx, repoID int64, sha, ref string, mrID int64, targetSHA string, jobs []NewJob) (int64, bool, error) {
	rows, err := tx.QueryContext(ctx, `SELECT id, mr_id, target_sha FROM pipelines
		WHERE repo_id = ? AND commit_sha = ? AND ref = ? AND source = 'push'
		AND (mr_id = 0 OR mr_id = ?) AND (target_sha = '' OR target_sha = ?)
		ORDER BY id DESC`, repoID, sha, ref, mrID, targetSHA)
	if err != nil {
		return 0, false, err
	}
	var cands []pushCandidate
	for rows.Next() {
		var c pushCandidate
		if err := rows.Scan(&c.id, &c.mrID, &c.target); err != nil {
			rows.Close()
			return 0, false, err
		}
		cands = append(cands, c)
	}
	if err := rows.Err(); err != nil {
		rows.Close()
		return 0, false, err
	}
	rows.Close()
	for _, c := range cands {
		if c.mrID == mrID && c.target == targetSHA {
			return c.id, true, nil
		}
		got, err := loadJobSigs(ctx, tx, c.id)
		if err != nil {
			return 0, false, err
		}
		if !sameJobSigs(got, jobs) {
			continue
		}
		if err := bindPipelineMR(ctx, tx, c.id, mrID, targetSHA); err != nil {
			return 0, false, err
		}
		return c.id, true, nil
	}
	return 0, false, nil
}

func bindPipelineMR(ctx context.Context, tx *sql.Tx, id, mrID int64, targetSHA string) error {
	_, err := tx.ExecContext(ctx, `UPDATE pipelines SET mr_id = ?, target_sha = ? WHERE id = ?`, mrID, targetSHA, id)
	return err
}

type jobSig struct {
	name, stage, image, script, when string
	allow                            bool
}

func loadJobSigs(ctx context.Context, tx *sql.Tx, pipelineID int64) ([]jobSig, error) {
	rows, err := tx.QueryContext(ctx, `SELECT name, stage, image, script, when_mode, allow_failure FROM jobs WHERE pipeline_id = ? ORDER BY position, id`, pipelineID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []jobSig
	for rows.Next() {
		var s jobSig
		var allow int
		if err := rows.Scan(&s.name, &s.stage, &s.image, &s.script, &s.when, &allow); err != nil {
			return nil, err
		}
		s.allow = allow == 1
		out = append(out, s)
	}
	return out, rows.Err()
}

func sameJobSigs(got []jobSig, jobs []NewJob) bool {
	if len(got) != len(jobs) {
		return false
	}
	for i, j := range jobs {
		when := j.When
		if when == "" {
			when = "on_success"
		}
		g := got[i]
		if g.name != j.Name || g.stage != j.Stage || g.image != j.Image || g.script != j.Script || g.when != when || g.allow != j.AllowFailure {
			return false
		}
	}
	return true
}

func insertPipelineJobs(ctx context.Context, tx *sql.Tx, id, repoID int64, jobs []NewJob) error {
	for _, j := range jobs {
		if j.Variables == nil {
			j.Variables = map[string]string{}
		}
		if j.Needs == nil {
			j.Needs = []string{}
		}
		if j.ArtifactPaths == nil {
			j.ArtifactPaths = []string{}
		}
		if j.Optional == nil {
			j.Optional = []string{}
		}
		if j.Tags == nil {
			j.Tags = []string{}
		}
		if j.CachePaths == nil {
			j.CachePaths = []string{}
		}
		if j.When == "" {
			j.When = "on_success"
		}
		if j.RetryWhen == nil {
			j.RetryWhen = []string{}
		}
		if j.Services == nil {
			j.Services = []Service{}
		}
		status := "queued"
		if j.When == "manual" {
			status = "manual"
		}
		_, err := tx.ExecContext(ctx, `INSERT INTO jobs (pipeline_id, repo_id, name, stage, position, status, image, script, needs_json, needs_set, variables_json, artifact_paths, artifact_expire_seconds, allow_failure, log, before_script, after_script, when_mode, timeout_seconds, tags_json, cache_paths, cache_key, resource_group, optional_json, retry_max, retry_when, services_json, environment_name, environment_url, interruptible)
			VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, '', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
			id, repoID, j.Name, j.Stage, j.Position, status, j.Image, j.Script, mustJSON(j.Needs), boolInt(j.NeedsSet), mustJSON(j.Variables), mustJSON(j.ArtifactPaths), int(j.ArtifactExpire/time.Second), boolInt(j.AllowFailure),
			j.Before, j.After, j.When, int(j.Timeout/time.Second), mustJSON(j.Tags), mustJSON(j.CachePaths), j.CacheKey, j.ResourceGroup, mustJSON(j.Optional),
			j.RetryMax, mustJSON(j.RetryWhen), mustJSON(j.Services), j.Environment, j.EnvironmentURL, boolInt(j.Interruptible))
		if err != nil {
			return err
		}
	}
	return nil
}

// ListPipelines geeft de nieuwste pipelines.
func (s *Store) ListPipelines(ctx context.Context, repoID int64) ([]Pipeline, error) {
	rows, err := s.db.QueryContext(ctx, pipelineCols+` WHERE repo_id = ? ORDER BY id DESC LIMIT 50`, repoID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Pipeline
	for rows.Next() {
		p, err := scanPipeline(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, p)
	}
	return out, rows.Err()
}

// PipelineFilter beperkt FindPipelines. Lege velden filteren niet.
// SHA mag een prefix van minstens 7 tekens zijn. Refs is een lijst van
// volledige refs; MRID neemt ook pipelines van de MR zelf mee.
type PipelineFilter struct {
	SHA  string
	Refs []string
	MRID int64
}

// FindPipelines geeft de nieuwste pipelines die bij f passen, hooguit 50.
func (s *Store) FindPipelines(ctx context.Context, repoID int64, f PipelineFilter) ([]Pipeline, error) {
	q := pipelineCols + ` WHERE repo_id = ?`
	args := []any{repoID}
	if f.SHA != "" {
		q += ` AND commit_sha LIKE ?`
		args = append(args, strings.ToLower(f.SHA)+"%")
	}
	var alt []string
	if f.MRID != 0 {
		alt = append(alt, `mr_id = ?`)
		args = append(args, f.MRID)
	}
	for _, ref := range f.Refs {
		alt = append(alt, `ref = ?`)
		args = append(args, ref)
	}
	if len(alt) > 0 {
		q += ` AND (` + strings.Join(alt, ` OR `) + `)`
	}
	q += ` ORDER BY id DESC LIMIT 50`
	rows, err := s.db.QueryContext(ctx, q, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Pipeline
	for rows.Next() {
		p, err := scanPipeline(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, p)
	}
	return out, rows.Err()
}

const pipelineCols = `SELECT id, repo_id, commit_sha, ref, status, created_at, finished_at, source, actor, commit_title, started_at, mr_id, target_sha FROM pipelines`

func scanPipeline(rows *sql.Rows) (Pipeline, error) {
	return scanPipelineRow(rows)
}

func scanPipelineRow(sc scanner) (Pipeline, error) {
	var p Pipeline
	var created, finished, started sql.NullString
	if err := sc.Scan(&p.ID, &p.RepoID, &p.CommitSHA, &p.Ref, &p.Status, &created, &finished, &p.Source, &p.Actor, &p.CommitTitle, &started, &p.MRID, &p.TargetSHA); err != nil {
		return Pipeline{}, err
	}
	p.CreatedAt = parseTime(created.String)
	p.FinishedAt = parseTime(finished.String)
	p.StartedAt = parseTime(started.String)
	return p, nil
}

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

// LatestPipelineByCommit geeft de nieuwste pipeline van deze commit.
func (s *Store) LatestPipelineByCommit(ctx context.Context, repoID int64, sha string) (Pipeline, error) {
	p, err := scanPipelineRow(s.db.QueryRowContext(ctx, pipelineCols+` WHERE repo_id = ? AND commit_sha = ? ORDER BY id DESC LIMIT 1`, repoID, sha))
	if errors.Is(err, sql.ErrNoRows) {
		return Pipeline{}, errNotFound
	}
	return p, err
}

// PipelineByID leest één pipeline.
func (s *Store) PipelineByID(ctx context.Context, repoID, id int64) (Pipeline, error) {
	p, err := scanPipelineRow(s.db.QueryRowContext(ctx, pipelineCols+` WHERE repo_id = ? AND id = ?`, repoID, id))
	if errors.Is(err, sql.ErrNoRows) {
		return Pipeline{}, errNotFound
	}
	return p, err
}

// JobsByPipeline geeft de jobs in bestandsvolgorde.
func (s *Store) JobsByPipeline(ctx context.Context, pipelineID int64) ([]Job, error) {
	rows, err := s.db.QueryContext(ctx, jobSelect+` WHERE j.pipeline_id = ? ORDER BY j.position, j.id`, pipelineID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Job
	for rows.Next() {
		j, err := scanJob(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, j)
	}
	return out, rows.Err()
}

const jobSelect = `SELECT j.id, j.pipeline_id, j.repo_id, j.name, j.stage, j.position, j.status, j.image, j.script, j.needs_json, j.needs_set, j.variables_json, j.artifact_paths, j.artifact_expire_seconds, j.allow_failure, j.log, p.commit_sha, p.ref, j.before_script, j.after_script, j.when_mode, j.timeout_seconds, j.tags_json, j.cache_paths, j.cache_key, j.resource_group, j.optional_json, j.started_at, j.finished_at, j.retry_max, j.retry_when, j.attempt, j.services_json, j.environment_name, j.environment_url, j.interruptible
FROM jobs j JOIN pipelines p ON p.id = j.pipeline_id`

func scanJob(rows *sql.Rows) (Job, error) {
	var j Job
	var needs, vars, paths, tags, cache, optional, retryWhen, services string
	var needsSet, allow, expire, timeout, interruptible int
	var started, finished sql.NullString
	if err := rows.Scan(&j.ID, &j.PipelineID, &j.RepoID, &j.Name, &j.Stage, &j.Position, &j.Status, &j.Image, &j.Script, &needs, &needsSet, &vars, &paths, &expire, &allow, &j.Log, &j.CommitSHA, &j.Ref, &j.Before, &j.After, &j.When, &timeout, &tags, &cache, &j.CacheKey, &j.ResourceGroup, &optional, &started, &finished, &j.RetryMax, &retryWhen, &j.Attempt, &services, &j.Environment, &j.EnvironmentURL, &interruptible); err != nil {
		return Job{}, err
	}
	j.NeedsSet = needsSet == 1
	j.AllowFailure = allow == 1
	j.Interruptible = interruptible == 1
	j.ArtifactExpire = time.Duration(expire) * time.Second
	j.Timeout = time.Duration(timeout) * time.Second
	j.StartedAt = parseTime(started.String)
	j.FinishedAt = parseTime(finished.String)
	_ = json.Unmarshal([]byte(needs), &j.Needs)
	_ = json.Unmarshal([]byte(vars), &j.Variables)
	_ = json.Unmarshal([]byte(paths), &j.ArtifactPaths)
	_ = json.Unmarshal([]byte(tags), &j.Tags)
	_ = json.Unmarshal([]byte(cache), &j.CachePaths)
	_ = json.Unmarshal([]byte(optional), &j.Optional)
	_ = json.Unmarshal([]byte(retryWhen), &j.RetryWhen)
	_ = json.Unmarshal([]byte(services), &j.Services)
	return j, nil
}

// ActivePipelines zijn nog niet af.
func (s *Store) ActivePipelines(ctx context.Context) ([]Pipeline, error) {
	rows, err := s.db.QueryContext(ctx, pipelineCols+` WHERE status IN ('queued', 'running', 'manual') OR id IN (SELECT pipeline_id FROM jobs WHERE status IN ('queued', 'running')) ORDER BY id`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Pipeline
	for rows.Next() {
		p, err := scanPipeline(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, p)
	}
	return out, rows.Err()
}

// MarkJobRunning zet een wachtende job op running als hij dat nog is.
func (s *Store) MarkJobRunning(ctx context.Context, id int64) (bool, error) {
	res, err := s.db.ExecContext(ctx, `UPDATE jobs SET status = 'running', started_at = ? WHERE id = ? AND status = 'queued'`, now(), id)
	if err != nil {
		return false, err
	}
	n, _ := res.RowsAffected()
	if n == 1 {
		_, _ = s.db.ExecContext(ctx, `UPDATE pipelines SET status = 'running', started_at = COALESCE(started_at, ?) WHERE id = (SELECT pipeline_id FROM jobs WHERE id = ?)`, now(), id)
	}
	return n == 1, nil
}

// AbandonedJobNote komt in het log van een job die de herstart niet haalde.
// Steve zit in dit proces. Een job die nog running is, heeft hier geen container.
const AbandonedJobNote = "BlueGit herstartte terwijl deze job liep; het proces is weg.\n"

// FailAbandonedJobs zet elke job met status running op failed.
// Aanroep dit bij het starten van de service, vóór de runner jobs oppakt.
// Een tweede keer doet niets. De pipelinestatus wordt daarna opnieuw berekend,
// zodat retry met mode failed deze jobs en hun wachtende vervolg oppakt.
func (s *Store) FailAbandonedJobs(ctx context.Context) (int, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT id, pipeline_id FROM jobs WHERE status = 'running' ORDER BY id`)
	if err != nil {
		return 0, err
	}
	defer rows.Close()
	type item struct{ id, pipe int64 }
	var found []item
	for rows.Next() {
		var it item
		if err := rows.Scan(&it.id, &it.pipe); err != nil {
			return 0, err
		}
		found = append(found, it)
	}
	if err := rows.Err(); err != nil {
		return 0, err
	}
	n := 0
	seen := map[int64]struct{}{}
	var pipes []int64
	for _, it := range found {
		res, err := s.db.ExecContext(ctx, `UPDATE jobs SET status = 'failed', log = log || ?, finished_at = ? WHERE id = ? AND status = 'running'`, AbandonedJobNote, now(), it.id)
		if err != nil {
			return n, err
		}
		affected, _ := res.RowsAffected()
		if affected == 0 {
			continue
		}
		n++
		if _, ok := seen[it.pipe]; !ok {
			seen[it.pipe] = struct{}{}
			pipes = append(pipes, it.pipe)
		}
	}
	for _, id := range pipes {
		if _, err := s.RefreshPipelineStatus(ctx, id); err != nil {
			return n, err
		}
	}
	return n, nil
}

// CountRunningJobs telt jobs die Steve nu uitvoert.
// publish_aur eindigt vóór de POST en self_update eindigt meteen daarna,
// dus er is geen job die tijdens de installatie op de updater wacht.
func (s *Store) CountRunningJobs(ctx context.Context) (int, error) {
	var n int
	err := s.db.QueryRowContext(ctx, `SELECT COUNT(*) FROM jobs WHERE status = 'running'`).Scan(&n)
	return n, err
}

// FinishJob schrijft het eindresultaat en het log.
func (s *Store) FinishJob(ctx context.Context, id int64, status, extra string) error {
	_, err := s.db.ExecContext(ctx, `UPDATE jobs SET status = ?, log = log || ?, finished_at = ? WHERE id = ?`, status, extra, now(), id)
	return err
}

// AppendJobLog voegt tekst toe terwijl de job loopt.
func (s *Store) AppendJobLog(ctx context.Context, id int64, chunk string) error {
	_, err := s.db.ExecContext(ctx, `UPDATE jobs SET log = log || ? WHERE id = ?`, chunk, id)
	return err
}

// RequeueJob zet een mislukte job terug in de wachtrij voor een nieuwe poging.
func (s *Store) RequeueJob(ctx context.Context, id int64, attempt int, note string) error {
	_, err := s.db.ExecContext(ctx, `UPDATE jobs SET status = 'queued', attempt = ?, log = log || ?, finished_at = NULL, started_at = NULL WHERE id = ?`, attempt, note, id)
	return err
}

// CancelInterruptible annuleert onderbreekbare jobs van oudere pipelines op dezelfde ref.
// Lopende jobs komen terug zodat de runner hun context kan stoppen.
func (s *Store) CancelInterruptible(ctx context.Context, repoID int64, ref string, keep int64) (running []int64, pipes []int64, err error) {
	rows, err := s.db.QueryContext(ctx, `SELECT j.id, j.pipeline_id, j.status FROM jobs j
		JOIN pipelines p ON p.id = j.pipeline_id
		WHERE p.repo_id = ? AND p.ref = ? AND p.id != ? AND j.interruptible = 1 AND j.status IN ('queued', 'running', 'manual')`, repoID, ref, keep)
	if err != nil {
		return nil, nil, err
	}
	defer rows.Close()
	type row struct {
		id, pipe int64
		status   string
	}
	var found []row
	seenPipe := map[int64]bool{}
	for rows.Next() {
		var item row
		if err := rows.Scan(&item.id, &item.pipe, &item.status); err != nil {
			return nil, nil, err
		}
		found = append(found, item)
		if !seenPipe[item.pipe] {
			seenPipe[item.pipe] = true
			pipes = append(pipes, item.pipe)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, nil, err
	}
	note := time.Now().UTC().Format(time.RFC3339) + "\tcanceled because a newer pipeline started for the same ref\n"
	for _, item := range found {
		if item.status == "running" {
			running = append(running, item.id)
			continue
		}
		if _, err := s.db.ExecContext(ctx, `UPDATE jobs SET status = 'canceled', log = log || ?, finished_at = ? WHERE id = ? AND status IN ('queued', 'manual')`, note, now(), item.id); err != nil {
			return running, pipes, err
		}
	}
	return running, pipes, nil
}

// RefreshPipelineStatus berekent de pipelinestatus uit de jobs.
func (s *Store) RefreshPipelineStatus(ctx context.Context, pipelineID int64) (string, error) {
	jobs, err := s.JobsByPipeline(ctx, pipelineID)
	if err != nil {
		return "", err
	}
	status := "success"
	open := false
	manual := false
	canceled := false
	for _, j := range jobs {
		switch j.Status {
		case "failed":
			if !j.AllowFailure {
				status = "failed"
			}
		case "canceled":
			if !j.AllowFailure {
				canceled = true
			}
		case "queued", "running":
			open = true
		case "manual":
			manual = true
		}
	}
	switch {
	case open:
		// Een harde mislukking wint pas als er niets meer in de wachtrij of
		// aan het werk is. Anders blijft de runner latere jobs overslaan.
		status = "running"
		_, err = s.db.ExecContext(ctx, `UPDATE pipelines SET status = 'running', finished_at = NULL WHERE id = ?`, pipelineID)
	case status == "failed":
		_, err = s.db.ExecContext(ctx, `UPDATE pipelines SET status = 'failed', finished_at = ? WHERE id = ?`, now(), pipelineID)
	case canceled:
		status = "canceled"
		_, err = s.db.ExecContext(ctx, `UPDATE pipelines SET status = 'canceled', finished_at = ? WHERE id = ?`, now(), pipelineID)
	case manual:
		status = "manual"
		_, err = s.db.ExecContext(ctx, `UPDATE pipelines SET status = 'manual', finished_at = NULL WHERE id = ?`, pipelineID)
	default:
		_, err = s.db.ExecContext(ctx, `UPDATE pipelines SET status = 'success', finished_at = ? WHERE id = ?`, now(), pipelineID)
	}
	return status, err
}

// ResourceBusy meldt of een andere job in deze repository die groep bezet.
func (s *Store) ResourceBusy(ctx context.Context, repoID int64, group string, except int64) (bool, error) {
	if group == "" {
		return false, nil
	}
	var n int
	err := s.db.QueryRowContext(ctx, `SELECT COUNT(*) FROM jobs WHERE repo_id = ? AND resource_group = ? AND status = 'running' AND id != ?`, repoID, group, except).Scan(&n)
	return n > 0, err
}

// RetryJob zet een mislukte of geannuleerde job terug in de wachtrij.
func (s *Store) RetryJob(ctx context.Context, id int64) (bool, error) {
	res, err := s.db.ExecContext(ctx, `UPDATE jobs SET status = 'queued', finished_at = NULL WHERE id = ? AND status IN ('failed', 'canceled')`, id)
	if err != nil {
		return false, err
	}
	n, _ := res.RowsAffected()
	if n == 1 {
		_, _ = s.db.ExecContext(ctx, `UPDATE pipelines SET status = 'running', finished_at = NULL WHERE id = (SELECT pipeline_id FROM jobs WHERE id = ?)`, id)
	}
	return n == 1, nil
}

// CancelJob stopt een job die nog niet klaar is.
func (s *Store) CancelJob(ctx context.Context, id int64) (bool, error) {
	res, err := s.db.ExecContext(ctx, `UPDATE jobs SET status = 'canceled', finished_at = ? WHERE id = ? AND status IN ('created', 'pending', 'queued', 'manual', 'running')`, now(), id)
	if err != nil {
		return false, err
	}
	n, _ := res.RowsAffected()
	return n == 1, nil
}

// QueueJob zet een handmatige job in de wachtrij.
func (s *Store) QueueJob(ctx context.Context, id int64) (bool, error) {
	res, err := s.db.ExecContext(ctx, `UPDATE jobs SET status = 'queued' WHERE id = ? AND status = 'manual'`, id)
	if err != nil {
		return false, err
	}
	n, _ := res.RowsAffected()
	if n == 1 {
		_, _ = s.db.ExecContext(ctx, `UPDATE pipelines SET status = 'running', finished_at = NULL WHERE id = (SELECT pipeline_id FROM jobs WHERE id = ?)`, id)
	}
	return n == 1, nil
}

// SkipJob markeert een job die niet meer hoeft.
func (s *Store) SkipJob(ctx context.Context, id int64, reason string) error {
	_, err := s.db.ExecContext(ctx, `UPDATE jobs SET status = 'skipped', log = ?, finished_at = ? WHERE id = ? AND status IN ('queued', 'manual')`, reason, now(), id)
	return err
}

// CreateJobToken maakt het token waarmee een lopende job de API aanroept.
func (s *Store) CreateJobToken(ctx context.Context, jobID int64) (string, error) {
	plain, err := newToken("bgj_")
	if err != nil {
		return "", err
	}
	_, err = s.db.ExecContext(ctx, `INSERT INTO job_tokens (job_id, token_hash, created_at) VALUES (?, ?, ?)
		ON CONFLICT(job_id) DO UPDATE SET token_hash = excluded.token_hash, created_at = excluded.created_at`,
		jobID, hashToken(plain), now())
	if err != nil {
		return "", err
	}
	return plain, nil
}

// JobByToken zoekt een lopende job bij een job-token.
func (s *Store) JobByToken(ctx context.Context, token string) (Job, error) {
	if len(token) < 4 || token[:4] != "bgj_" {
		return Job{}, errNotFound
	}
	var jobID int64
	err := s.db.QueryRowContext(ctx, `SELECT job_id FROM job_tokens WHERE token_hash = ?`, hashToken(token)).Scan(&jobID)
	if errors.Is(err, sql.ErrNoRows) {
		return Job{}, errNotFound
	}
	if err != nil {
		return Job{}, err
	}
	rows, err := s.db.QueryContext(ctx, jobSelect+` WHERE j.id = ?`, jobID)
	if err != nil {
		return Job{}, err
	}
	defer rows.Close()
	if !rows.Next() {
		return Job{}, errNotFound
	}
	return scanJob(rows)
}

// JobByID leest een job van deze repository.
func (s *Store) JobByID(ctx context.Context, repoID, id int64) (Job, error) {
	rows, err := s.db.QueryContext(ctx, jobSelect+` WHERE j.repo_id = ? AND j.id = ?`, repoID, id)
	if err != nil {
		return Job{}, err
	}
	defer rows.Close()
	if !rows.Next() {
		return Job{}, errNotFound
	}
	return scanJob(rows)
}

// Artifact verwijst naar een bestand uit een job.
type Artifact struct {
	ID          int64
	JobID       int64
	Path        string
	StoragePath string
	Size        int64
}

// AddArtifact registreert een bewaard bestand.
func (s *Store) AddArtifact(ctx context.Context, jobID int64, relPath, storagePath string, size int64, expires *time.Time) error {
	var exp any
	if expires != nil {
		exp = expires.UTC().Format(time.RFC3339)
	}
	_, err := s.db.ExecContext(ctx, `INSERT INTO artifacts (job_id, path, storage_path, size, expires_at) VALUES (?, ?, ?, ?, ?)`,
		jobID, relPath, storagePath, size, exp)
	return err
}

// ListArtifacts toont de bestanden van één job.
func (s *Store) ListArtifacts(ctx context.Context, jobID int64) ([]Artifact, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT id, job_id, path, storage_path, size FROM artifacts WHERE job_id = ? ORDER BY path`, jobID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Artifact
	for rows.Next() {
		var a Artifact
		if err := rows.Scan(&a.ID, &a.JobID, &a.Path, &a.StoragePath, &a.Size); err != nil {
			return nil, err
		}
		out = append(out, a)
	}
	return out, rows.Err()
}

// ArtifactPath zoekt een bestand van een job in dezelfde pipeline.
func (s *Store) ArtifactPath(ctx context.Context, pipelineID int64, jobName, relPath string) (string, error) {
	var storage string
	err := s.db.QueryRowContext(ctx, `SELECT a.storage_path FROM artifacts a JOIN jobs j ON j.id = a.job_id
		WHERE j.pipeline_id = ? AND j.name = ? AND a.path = ? AND j.status = 'success'`, pipelineID, jobName, relPath).Scan(&storage)
	if errors.Is(err, sql.ErrNoRows) {
		return "", errNotFound
	}
	return storage, err
}

// Release is een publicatie op een commit.
type Release struct {
	ID        int64
	RepoID    int64
	Tag       string
	CommitSHA string
	Name      string
	Body      string
	CreatedAt time.Time
	Assets    []ReleaseAsset
}

// ReleaseAsset is een bestand bij een release.
type ReleaseAsset struct {
	Name        string
	StoragePath string
	Size        int64
}

// CreateRelease bewaart de release. De bestanden staan al op storagePath.
func (s *Store) CreateRelease(ctx context.Context, repoID int64, tag, sha, name, body string, authorID, jobID int64, assets []ReleaseAsset) (int64, error) {
	if err := names.ValidTag(tag); err != nil {
		return 0, err
	}
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return 0, err
	}
	defer func() { _ = tx.Rollback() }()
	var author, job any
	if authorID != 0 {
		author = authorID
	}
	if jobID != 0 {
		job = jobID
	}
	res, err := tx.ExecContext(ctx, `INSERT INTO releases (repo_id, tag, commit_sha, name, body, author_id, job_id, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
		repoID, tag, sha, name, body, author, job, now())
	if err != nil {
		if containsUnique(err) {
			return 0, i18n.E("err.tagExists", tag)
		}
		return 0, err
	}
	id, _ := res.LastInsertId()
	for _, a := range assets {
		if _, err := tx.ExecContext(ctx, `INSERT INTO release_assets (release_id, name, storage_path, size) VALUES (?, ?, ?, ?)`, id, a.Name, a.StoragePath, a.Size); err != nil {
			return 0, err
		}
	}
	if err := tx.Commit(); err != nil {
		return 0, err
	}
	return id, nil
}

// ListReleases geeft publicaties, nieuwste eerst.
func (s *Store) ListReleases(ctx context.Context, repoID int64) ([]Release, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT id, repo_id, tag, commit_sha, name, body, created_at FROM releases WHERE repo_id = ? ORDER BY id DESC`, repoID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Release
	for rows.Next() {
		var r Release
		var created string
		if err := rows.Scan(&r.ID, &r.RepoID, &r.Tag, &r.CommitSHA, &r.Name, &r.Body, &created); err != nil {
			return nil, err
		}
		r.CreatedAt = parseTime(created)
		out = append(out, r)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	for i := range out {
		assets, err := s.releaseAssets(ctx, out[i].ID)
		if err != nil {
			return nil, err
		}
		out[i].Assets = assets
	}
	return out, nil
}

// ReleaseByTag leest één release.
func (s *Store) ReleaseByTag(ctx context.Context, repoID int64, tag string) (Release, error) {
	var r Release
	var created string
	err := s.db.QueryRowContext(ctx, `SELECT id, repo_id, tag, commit_sha, name, body, created_at FROM releases WHERE repo_id = ? AND tag = ?`, repoID, tag).
		Scan(&r.ID, &r.RepoID, &r.Tag, &r.CommitSHA, &r.Name, &r.Body, &created)
	if errors.Is(err, sql.ErrNoRows) {
		return Release{}, errNotFound
	}
	if err != nil {
		return Release{}, err
	}
	r.CreatedAt = parseTime(created)
	r.Assets, err = s.releaseAssets(ctx, r.ID)
	return r, err
}

func (s *Store) releaseAssets(ctx context.Context, id int64) ([]ReleaseAsset, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT name, storage_path, size FROM release_assets WHERE release_id = ? ORDER BY name`, id)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []ReleaseAsset
	for rows.Next() {
		var a ReleaseAsset
		if err := rows.Scan(&a.Name, &a.StoragePath, &a.Size); err != nil {
			return nil, err
		}
		out = append(out, a)
	}
	return out, rows.Err()
}

func trim(s string, n int) string {
	s = string([]rune(s))
	if len([]rune(s)) > n {
		return string([]rune(s)[:n])
	}
	return s
}

func containsUnique(err error) bool {
	return err != nil && (errors.Is(err, err) && (len(err.Error()) > 0 && (contains(err.Error(), "UNIQUE") || contains(err.Error(), "unique"))))
}

func contains(s, sub string) bool {
	return len(s) >= len(sub) && (s == sub || len(sub) == 0 || (len(s) > 0 && (indexOf(s, sub) >= 0)))
}

func indexOf(s, sub string) int {
	for i := 0; i+len(sub) <= len(s); i++ {
		if s[i:i+len(sub)] == sub {
			return i
		}
	}
	return -1
}
