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
}

// 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, 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, 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 id, scope_kind, scope_id, name, preset, url_template, headers_json, body_template, events_json, branch_filter, active, method 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()
}

func scanWebhook(rows *sql.Rows) (Webhook, error) {
	var w Webhook
	var headers, events string
	var active int
	if err := rows.Scan(&w.ID, &w.ScopeKind, &w.ScopeID, &w.Name, &w.Preset, &w.URLTemplate, &headers, &w.BodyTemplate, &events, &w.BranchFilter, &active, &w.Method); 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
}

// 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
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
	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); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return MergeRequest{}, errNotFound
		}
		return MergeRequest{}, err
	}
	m.CreatedAt = parseTime(created)
	m.UpdatedAt = parseTime(updated)
	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()
	var out []MergeRequest
	for rows.Next() {
		var m MergeRequest
		var created, updated string
		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); err != nil {
			return nil, err
		}
		m.CreatedAt = parseTime(created)
		m.UpdatedAt = parseTime(updated)
		out = append(out, m)
	}
	return out, rows.Err()
}

// 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
}

// 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
}

// 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
}

// 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
	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) error {
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return 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 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 err
	}
	return tx.Commit()
}

// 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"
		}
		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)
			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))
		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 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); 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
}

// 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
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 string
	var needsSet, allow, expire, timeout 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); err != nil {
		return Job{}, err
	}
	j.NeedsSet = needsSet == 1
	j.AllowFailure = allow == 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)
	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
}

// 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
}

// 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
	for _, j := range jobs {
		switch j.Status {
		case "failed":
			if !j.AllowFailure {
				status = "failed"
			}
		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 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
}

// 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)
}

// 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
}

// 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
}
