package store

import (
	"context"
	"database/sql"
	"encoding/json"
	"errors"
	"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
}

// 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
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 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); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return MergeRequest{}, errNotFound
		}
		return MergeRequest{}, err
	}
	m.CreatedAt = parseTime(created)
	m.UpdatedAt = parseTime(updated)
	m.Draft = draft == 1
	return m, nil
}

// 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 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); err != nil {
			return nil, err
		}
		m.CreatedAt = parseTime(created)
		m.UpdatedAt = parseTime(updated)
		m.Draft = draft == 1
		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
}

// 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.
func (s *Store) RecordConfigFailure(ctx context.Context, repoID int64, sha, ref, message string) (int64, error) {
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return 0, err
	}
	defer func() { _ = tx.Rollback() }()
	ts := now()
	res, err := tx.ExecContext(ctx, `INSERT INTO pipelines (repo_id, commit_sha, ref, status, created_at, finished_at) VALUES (?, ?, ?, 'failed', ?, ?)`, repoID, sha, ref, ts, ts)
	if err != nil {
		return 0, err
	}
	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, err
	}
	if err := tx.Commit(); err != nil {
		return 0, err
	}
	return id, nil
}

// CreatePipeline maakt een pipeline met jobs in de wachtrij.
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()
	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 0, err
		}
	}
	if err := tx.Commit(); err != nil {
		return 0, err
	}
	return id, 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()
}

const pipelineCols = `SELECT id, repo_id, commit_sha, ref, status, created_at, finished_at, source, actor, commit_title, started_at, mr_id 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); 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
}

// CountRunningJobs telt jobs die Steve nu uitvoert.
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
}
