package db

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

type CiRun struct {
	ID                int64
	RepoID            int64
	TriggeredBy       *int64
	TriggerSource     string
	CommitSHA         *string
	CommitBranch      *string
	CommitTag         *string
	Status            string
	VariableOverrides *string
	StartedAt         *string
	FinishedAt        *string
	CreatedAt         string
	RepoRunID         *int64
	// Joined from users, null when the run was not triggered by a person.
	TriggeredByUsername *string
}

type CiStep struct {
	ID         int64
	RunID      int64
	Name       string
	Status     string
	ExitCode   *int64
	StartedAt  *string
	FinishedAt *string
	Log        string
}

type CiArtifact struct {
	ID        int64
	RunID     int64
	Filename  string
	Size      int64
	CreatedAt string
}

// CiSecret omits the value; only the pipeline runner reads values.
type CiSecret struct {
	ID          int64
	Name        string
	Description *string
	CreatedAt   string
}

const ciRunColumns = `ci_runs.id, ci_runs.repo_id, ci_runs.triggered_by, ci_runs.trigger_source,
	ci_runs.commit_sha, ci_runs.commit_branch, ci_runs.commit_tag, ci_runs.status,
	ci_runs.variable_overrides, ci_runs.started_at, ci_runs.finished_at, ci_runs.created_at,
	ci_runs.repo_run_id, users.username`

func scanCiRun(s rowScanner) (*CiRun, error) {
	var r CiRun
	err := s.Scan(&r.ID, &r.RepoID, &r.TriggeredBy, &r.TriggerSource, &r.CommitSHA,
		&r.CommitBranch, &r.CommitTag, &r.Status, &r.VariableOverrides, &r.StartedAt,
		&r.FinishedAt, &r.CreatedAt, &r.RepoRunID, &r.TriggeredByUsername)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &r, nil
}

// LatestCiRunStatus returns the status of the newest run, or "" when the repo
// has never run CI.
func (d *DB) LatestCiRunStatus(ctx context.Context, repoID int64) (string, error) {
	var status string
	err := d.QueryRowContext(ctx,
		`SELECT status FROM ci_runs WHERE repo_id = ? ORDER BY id DESC LIMIT 1`, repoID).Scan(&status)
	if errors.Is(err, sql.ErrNoRows) {
		return "", nil
	}
	return status, err
}

func (d *DB) CountCiRuns(ctx context.Context, repoID int64) (int, error) {
	var n int
	err := d.QueryRowContext(ctx, `SELECT COUNT(*) FROM ci_runs WHERE repo_id = ?`, repoID).Scan(&n)
	return n, err
}

func (d *DB) ListCiRuns(ctx context.Context, repoID int64, limit, offset int) ([]CiRun, error) {
	rows, err := d.QueryContext(ctx,
		`SELECT `+ciRunColumns+` FROM ci_runs
		 LEFT JOIN users ON users.id = ci_runs.triggered_by
		 WHERE ci_runs.repo_id = ? ORDER BY ci_runs.id DESC LIMIT ? OFFSET ?`,
		repoID, limit, offset)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []CiRun
	for rows.Next() {
		r, err := scanCiRun(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, *r)
	}
	return out, rows.Err()
}

func (d *DB) CiRunInRepo(ctx context.Context, runID, repoID int64) (*CiRun, error) {
	return scanCiRun(d.QueryRowContext(ctx,
		`SELECT `+ciRunColumns+` FROM ci_runs
		 LEFT JOIN users ON users.id = ci_runs.triggered_by
		 WHERE ci_runs.id = ? AND ci_runs.repo_id = ?`, runID, repoID))
}

// ── steps ────────────────────────────────────────────────────────────────

func (d *DB) ListCiSteps(ctx context.Context, runID int64) ([]CiStep, error) {
	rows, err := d.QueryContext(ctx,
		`SELECT id, run_id, name, status, exit_code, started_at, finished_at, log
		 FROM ci_steps WHERE run_id = ? ORDER BY id ASC`, runID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []CiStep
	for rows.Next() {
		var s CiStep
		if err := rows.Scan(&s.ID, &s.RunID, &s.Name, &s.Status, &s.ExitCode,
			&s.StartedAt, &s.FinishedAt, &s.Log); err != nil {
			return nil, err
		}
		out = append(out, s)
	}
	return out, rows.Err()
}

// ── artifacts ────────────────────────────────────────────────────────────

func (d *DB) ListCiArtifacts(ctx context.Context, runID int64) ([]CiArtifact, error) {
	rows, err := d.QueryContext(ctx,
		`SELECT id, run_id, filename, size, created_at FROM ci_artifacts
		 WHERE run_id = ? ORDER BY id ASC`, runID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []CiArtifact
	for rows.Next() {
		var a CiArtifact
		if err := rows.Scan(&a.ID, &a.RunID, &a.Filename, &a.Size, &a.CreatedAt); err != nil {
			return nil, err
		}
		out = append(out, a)
	}
	return out, rows.Err()
}

// CiArtifactCounts returns the number of artifacts per run for a list view.
func (d *DB) CiArtifactCounts(ctx context.Context, runIDs []int64) (map[int64]int, error) {
	out := map[int64]int{}
	if len(runIDs) == 0 {
		return out, nil
	}
	rows, err := d.QueryContext(ctx,
		`SELECT run_id, COUNT(*) FROM ci_artifacts WHERE run_id IN (`+placeholders(len(runIDs))+`)
		 GROUP BY run_id`, int64Args(runIDs)...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		var id int64
		var n int
		if err := rows.Scan(&id, &n); err != nil {
			return nil, err
		}
		out[id] = n
	}
	return out, rows.Err()
}

// CiArtifactInRun resolves a download request, checking run and repo in the
// same query so an artifact from another repo cannot be fetched.
func (d *DB) CiArtifactInRun(ctx context.Context, artifactID, runID, repoID int64) (*CiArtifact, error) {
	var a CiArtifact
	err := d.QueryRowContext(ctx,
		`SELECT ci_artifacts.id, ci_artifacts.run_id, ci_artifacts.filename, ci_artifacts.size,
		        ci_artifacts.created_at
		 FROM ci_artifacts
		 JOIN ci_runs ON ci_runs.id = ci_artifacts.run_id
		 WHERE ci_artifacts.id = ? AND ci_runs.id = ? AND ci_runs.repo_id = ?`,
		artifactID, runID, repoID).Scan(&a.ID, &a.RunID, &a.Filename, &a.Size, &a.CreatedAt)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &a, nil
}

// ── secrets ──────────────────────────────────────────────────────────────

// ListCiSecrets returns secret metadata for the settings page, without values.
func (d *DB) ListCiSecrets(ctx context.Context, repoID int64) ([]CiSecret, error) {
	rows, err := d.QueryContext(ctx,
		`SELECT id, name, description, created_at FROM ci_secrets WHERE repo_id = ? ORDER BY name ASC`,
		repoID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []CiSecret
	for rows.Next() {
		var s CiSecret
		if err := rows.Scan(&s.ID, &s.Name, &s.Description, &s.CreatedAt); err != nil {
			return nil, err
		}
		out = append(out, s)
	}
	return out, rows.Err()
}

func (d *DB) UpsertCiSecret(ctx context.Context, repoID int64, name, value string, description *string) error {
	_, err := d.ExecContext(ctx,
		`INSERT INTO ci_secrets (repo_id, name, value, description) VALUES (?, ?, ?, ?)
		 ON CONFLICT(repo_id, name) DO UPDATE SET value = excluded.value,
		 description = excluded.description`,
		repoID, name, value, description)
	return err
}

func (d *DB) DeleteCiSecret(ctx context.Context, id, repoID int64) error {
	_, err := d.ExecContext(ctx, `DELETE FROM ci_secrets WHERE id = ? AND repo_id = ?`, id, repoID)
	return err
}