// Package store bewaart BlueGit in één SQLite-bestand op de datadisk.
package store

import (
	"context"
	"crypto/rand"
	"crypto/sha256"
	"database/sql"
	"encoding/hex"
	"encoding/json"
	"errors"
	"fmt"
	"strings"
	"time"

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

	_ "modernc.org/sqlite"
)

// Store is de metadata van deze installatie.
type Store struct {
	db  *sql.DB
	key []byte
}

// Open opent of maakt het databasebestand. key is 32 bytes voor geheimen.
func Open(path string, key []byte) (*Store, error) {
	if len(key) != 32 {
		return nil, fmt.Errorf("geheime sleutel moet 32 bytes zijn")
	}
	dsn := "file:" + path + "?_pragma=busy_timeout(5000)&_pragma=foreign_keys(1)"
	db, err := sql.Open("sqlite", dsn)
	if err != nil {
		return nil, err
	}
	db.SetMaxOpenConns(8)
	s := &Store{db: db, key: key}
	if _, err := db.Exec(`PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON;`); err != nil {
		_ = db.Close()
		return nil, err
	}
	if err := s.migrate(); err != nil {
		_ = db.Close()
		return nil, err
	}
	return s, nil
}

// Close sluit de database.
func (s *Store) Close() error { return s.db.Close() }

func (s *Store) migrate() error {
	if _, err := s.db.Exec(schema); err != nil {
		return err
	}
	alters := []string{
		`ALTER TABLE users ADD COLUMN language TEXT NOT NULL DEFAULT 'en'`,
		`ALTER TABLE groups ADD COLUMN visibility TEXT NOT NULL DEFAULT 'private'`,
		`ALTER TABLE groups ADD COLUMN default_role TEXT NOT NULL DEFAULT 'developer'`,
		`ALTER TABLE groups ADD COLUMN logo_mime TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE repos ADD COLUMN logo_mime TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE webhooks ADD COLUMN method TEXT NOT NULL DEFAULT 'POST'`,
		`ALTER TABLE notes ADD COLUMN file_path TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE notes ADD COLUMN line_start INTEGER NOT NULL DEFAULT 0`,
		`ALTER TABLE notes ADD COLUMN line_end INTEGER NOT NULL DEFAULT 0`,
		`ALTER TABLE notes ADD COLUMN suggestion TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE notes ADD COLUMN base_text TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE pipelines ADD COLUMN source TEXT NOT NULL DEFAULT 'push'`,
		`ALTER TABLE pipelines ADD COLUMN actor TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE pipelines ADD COLUMN commit_title TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE pipelines ADD COLUMN started_at TEXT`,
		`ALTER TABLE jobs ADD COLUMN before_script TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE jobs ADD COLUMN after_script TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE jobs ADD COLUMN when_mode TEXT NOT NULL DEFAULT 'on_success'`,
		`ALTER TABLE jobs ADD COLUMN timeout_seconds INTEGER NOT NULL DEFAULT 0`,
		`ALTER TABLE jobs ADD COLUMN tags_json TEXT NOT NULL DEFAULT '[]'`,
		`ALTER TABLE jobs ADD COLUMN cache_paths TEXT NOT NULL DEFAULT '[]'`,
		`ALTER TABLE jobs ADD COLUMN cache_key TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE jobs ADD COLUMN resource_group TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE jobs ADD COLUMN optional_json TEXT NOT NULL DEFAULT '[]'`,
		`ALTER TABLE jobs ADD COLUMN retry_max INTEGER NOT NULL DEFAULT 0`,
		`ALTER TABLE jobs ADD COLUMN retry_when TEXT NOT NULL DEFAULT '[]'`,
		`ALTER TABLE jobs ADD COLUMN attempt INTEGER NOT NULL DEFAULT 0`,
		`ALTER TABLE jobs ADD COLUMN services_json TEXT NOT NULL DEFAULT '[]'`,
		`ALTER TABLE jobs ADD COLUMN environment_name TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE jobs ADD COLUMN environment_url TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE jobs ADD COLUMN interruptible INTEGER NOT NULL DEFAULT 0`,
		`ALTER TABLE pipelines ADD COLUMN mr_id INTEGER NOT NULL DEFAULT 0`,
		`ALTER TABLE groups ADD COLUMN owner_id INTEGER NOT NULL DEFAULT 0`,
		`ALTER TABLE merge_requests ADD COLUMN draft INTEGER NOT NULL DEFAULT 0`,
		`ALTER TABLE merge_requests ADD COLUMN labels TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE notes ADD COLUMN parent_id INTEGER NOT NULL DEFAULT 0`,
		`ALTER TABLE secrets ADD COLUMN invisible INTEGER NOT NULL DEFAULT 0`,
		`ALTER TABLE webhooks ADD COLUMN fields_json TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE access_tokens ADD COLUMN scopes TEXT NOT NULL DEFAULT 'api'`,
	}
	for _, q := range alters {
		if _, err := s.db.Exec(q); err != nil && !strings.Contains(strings.ToLower(err.Error()), "duplicate column") {
			return err
		}
	}
	if _, err := s.db.Exec(`UPDATE groups SET owner_id = created_by WHERE owner_id = 0 AND created_by != 0`); err != nil {
		return err
	}
	return nil
}

const schema = `
CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY,
  auth_id INTEGER NOT NULL UNIQUE,
  username TEXT NOT NULL UNIQUE,
  avatar_url TEXT NOT NULL DEFAULT '',
  auth_admin INTEGER NOT NULL DEFAULT 0,
  instance_admin INTEGER NOT NULL DEFAULT 0,
  language TEXT NOT NULL DEFAULT 'en',
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS sessions (
  id TEXT PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  access_token TEXT NOT NULL,
  expires_at TEXT NOT NULL,
  csrf TEXT NOT NULL,
  created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS access_tokens (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  token_hash TEXT NOT NULL UNIQUE,
  prefix TEXT NOT NULL,
  created_at TEXT NOT NULL,
  last_used_at TEXT,
  expires_at TEXT
);
CREATE TABLE IF NOT EXISTS ssh_keys (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  public_key TEXT NOT NULL,
  fingerprint TEXT NOT NULL UNIQUE,
  created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS groups (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  path TEXT NOT NULL UNIQUE,
  description TEXT NOT NULL DEFAULT '',
  visibility TEXT NOT NULL DEFAULT 'private',
  default_role TEXT NOT NULL DEFAULT 'developer',
  logo_mime TEXT NOT NULL DEFAULT '',
  created_by INTEGER NOT NULL,
  created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS group_members (
  group_id INTEGER NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role TEXT NOT NULL,
  PRIMARY KEY (group_id, user_id)
);
CREATE TABLE IF NOT EXISTS repos (
  id INTEGER PRIMARY KEY,
  owner_kind TEXT NOT NULL,
  owner_id INTEGER NOT NULL,
  name TEXT NOT NULL,
  description TEXT NOT NULL DEFAULT '',
  default_branch TEXT NOT NULL,
  visibility TEXT NOT NULL DEFAULT 'private',
  logo_mime TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL,
  UNIQUE(owner_kind, owner_id, name)
);
CREATE TABLE IF NOT EXISTS repo_members (
  repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role TEXT NOT NULL,
  PRIMARY KEY (repo_id, user_id)
);
CREATE TABLE IF NOT EXISTS pending_members (
  id INTEGER PRIMARY KEY,
  scope_kind TEXT NOT NULL,
  scope_id INTEGER NOT NULL,
  username TEXT NOT NULL,
  role TEXT NOT NULL,
  UNIQUE(scope_kind, scope_id, username)
);
CREATE TABLE IF NOT EXISTS protected_branches (
  id INTEGER PRIMARY KEY,
  repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  pattern TEXT NOT NULL,
  UNIQUE(repo_id, pattern)
);
CREATE TABLE IF NOT EXISTS protected_tags (
  id INTEGER PRIMARY KEY,
  repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  pattern TEXT NOT NULL,
  UNIQUE(repo_id, pattern)
);
CREATE TABLE IF NOT EXISTS secrets (
  id INTEGER PRIMARY KEY,
  scope_kind TEXT NOT NULL,
  scope_id INTEGER NOT NULL,
  name TEXT NOT NULL,
  ciphertext BLOB NOT NULL,
  nonce BLOB NOT NULL,
  protected INTEGER NOT NULL DEFAULT 0,
  invisible INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL,
  UNIQUE(scope_kind, scope_id, name)
);
CREATE TABLE IF NOT EXISTS webhooks (
  id INTEGER PRIMARY KEY,
  scope_kind TEXT NOT NULL,
  scope_id INTEGER NOT NULL,
  name TEXT NOT NULL,
  preset TEXT NOT NULL,
  url_template TEXT NOT NULL,
  headers_json TEXT NOT NULL,
  body_template TEXT NOT NULL,
  events_json TEXT NOT NULL,
  branch_filter TEXT NOT NULL DEFAULT '',
  active INTEGER NOT NULL DEFAULT 1,
  method TEXT NOT NULL DEFAULT 'POST',
  fields_json TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS webhook_deliveries (
  id INTEGER PRIMARY KEY,
  webhook_id INTEGER NOT NULL REFERENCES webhooks(id) ON DELETE CASCADE,
  event_type TEXT NOT NULL,
  status_code INTEGER NOT NULL DEFAULT 0,
  error TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS merge_requests (
  id INTEGER PRIMARY KEY,
  repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  number INTEGER NOT NULL,
  title TEXT NOT NULL,
  description TEXT NOT NULL DEFAULT '',
  source_branch TEXT NOT NULL,
  target_branch TEXT NOT NULL,
  author_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  draft INTEGER NOT NULL DEFAULT 0,
  labels TEXT NOT NULL DEFAULT '',
  UNIQUE(repo_id, number)
);
CREATE TABLE IF NOT EXISTS pipelines (
  id INTEGER PRIMARY KEY,
  repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  commit_sha TEXT NOT NULL,
  ref TEXT NOT NULL,
  status TEXT NOT NULL,
  created_at TEXT NOT NULL,
  finished_at TEXT,
  source TEXT NOT NULL DEFAULT 'push',
  actor TEXT NOT NULL DEFAULT '',
  commit_title TEXT NOT NULL DEFAULT '',
  started_at TEXT
);
CREATE TABLE IF NOT EXISTS jobs (
  id INTEGER PRIMARY KEY,
  pipeline_id INTEGER NOT NULL REFERENCES pipelines(id) ON DELETE CASCADE,
  repo_id INTEGER NOT NULL,
  name TEXT NOT NULL,
  stage TEXT NOT NULL,
  position INTEGER NOT NULL,
  status TEXT NOT NULL,
  image TEXT NOT NULL,
  script TEXT NOT NULL,
  needs_json TEXT NOT NULL,
  needs_set INTEGER NOT NULL,
  variables_json TEXT NOT NULL,
  artifact_paths TEXT NOT NULL,
  artifact_expire_seconds INTEGER NOT NULL DEFAULT 0,
  allow_failure INTEGER NOT NULL DEFAULT 0,
  log TEXT NOT NULL DEFAULT '',
  started_at TEXT,
  finished_at TEXT,
  before_script TEXT NOT NULL DEFAULT '',
  after_script TEXT NOT NULL DEFAULT '',
  when_mode TEXT NOT NULL DEFAULT 'on_success',
  timeout_seconds INTEGER NOT NULL DEFAULT 0,
  tags_json TEXT NOT NULL DEFAULT '[]',
  cache_paths TEXT NOT NULL DEFAULT '[]',
  cache_key TEXT NOT NULL DEFAULT '',
  resource_group TEXT NOT NULL DEFAULT '',
  optional_json TEXT NOT NULL DEFAULT '[]',
  retry_max INTEGER NOT NULL DEFAULT 0,
  retry_when TEXT NOT NULL DEFAULT '[]',
  attempt INTEGER NOT NULL DEFAULT 0,
  services_json TEXT NOT NULL DEFAULT '[]',
  environment_name TEXT NOT NULL DEFAULT '',
  environment_url TEXT NOT NULL DEFAULT '',
  interruptible INTEGER NOT NULL DEFAULT 0,
  UNIQUE(pipeline_id, name)
);
CREATE TABLE IF NOT EXISTS artifacts (
  id INTEGER PRIMARY KEY,
  job_id INTEGER NOT NULL REFERENCES jobs(id) ON DELETE CASCADE,
  path TEXT NOT NULL,
  storage_path TEXT NOT NULL,
  size INTEGER NOT NULL,
  expires_at TEXT
);
CREATE TABLE IF NOT EXISTS job_tokens (
  job_id INTEGER PRIMARY KEY REFERENCES jobs(id) ON DELETE CASCADE,
  token_hash TEXT NOT NULL UNIQUE,
  created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS releases (
  id INTEGER PRIMARY KEY,
  repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  tag TEXT NOT NULL,
  commit_sha TEXT NOT NULL,
  name TEXT NOT NULL,
  body TEXT NOT NULL DEFAULT '',
  author_id INTEGER,
  job_id INTEGER,
  created_at TEXT NOT NULL,
  UNIQUE(repo_id, tag)
);
CREATE TABLE IF NOT EXISTS release_assets (
  id INTEGER PRIMARY KEY,
  release_id INTEGER NOT NULL REFERENCES releases(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  storage_path TEXT NOT NULL,
  size INTEGER NOT NULL,
  UNIQUE(release_id, name)
);
CREATE TABLE IF NOT EXISTS contributions (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  kind TEXT NOT NULL,
  ref TEXT NOT NULL,
  occurred_at TEXT NOT NULL,
  UNIQUE(repo_id, kind, ref)
);
CREATE INDEX IF NOT EXISTS contributions_repo_time ON contributions(repo_id, occurred_at);
CREATE INDEX IF NOT EXISTS contributions_user_time ON contributions(user_id, occurred_at);
CREATE TABLE IF NOT EXISTS contribution_sync (
  repo_id INTEGER PRIMARY KEY REFERENCES repos(id) ON DELETE CASCADE,
  synced_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS stars (
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  created_at TEXT NOT NULL,
  PRIMARY KEY (user_id, repo_id)
);
CREATE TABLE IF NOT EXISTS notes (
  id INTEGER PRIMARY KEY,
  repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  mr_id INTEGER NOT NULL REFERENCES merge_requests(id) ON DELETE CASCADE,
  author_id INTEGER NOT NULL REFERENCES users(id),
  body TEXT NOT NULL,
  created_at TEXT NOT NULL,
  file_path TEXT NOT NULL DEFAULT '',
  line_start INTEGER NOT NULL DEFAULT 0,
  line_end INTEGER NOT NULL DEFAULT 0,
  suggestion TEXT NOT NULL DEFAULT '',
  base_text TEXT NOT NULL DEFAULT '',
  parent_id INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS mr_events (
  id INTEGER PRIMARY KEY,
  repo_id INTEGER NOT NULL,
  mr_id INTEGER NOT NULL REFERENCES merge_requests(id) ON DELETE CASCADE,
  actor_id INTEGER NOT NULL DEFAULT 0,
  kind TEXT NOT NULL,
  body TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS mr_events_mr ON mr_events(mr_id, id);
CREATE TABLE IF NOT EXISTS commit_pushers (
  repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  sha TEXT NOT NULL,
  user_id INTEGER NOT NULL REFERENCES users(id),
  author_name TEXT NOT NULL,
  author_email TEXT NOT NULL,
  created_at TEXT NOT NULL,
  PRIMARY KEY (repo_id, sha)
);
CREATE TABLE IF NOT EXISTS approval_rules (
  scope_kind TEXT NOT NULL,
  scope_id INTEGER NOT NULL,
  configured INTEGER NOT NULL DEFAULT 0,
  maintainer_required INTEGER NOT NULL DEFAULT 0,
  required_count INTEGER NOT NULL DEFAULT 1,
  developers_approve INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (scope_kind, scope_id)
);
CREATE TABLE IF NOT EXISTS reviews (
  id INTEGER PRIMARY KEY,
  repo_id INTEGER NOT NULL,
  mr_id INTEGER NOT NULL REFERENCES merge_requests(id) ON DELETE CASCADE,
  user_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  UNIQUE(mr_id, user_id)
);
CREATE TABLE IF NOT EXISTS labels (
  id INTEGER PRIMARY KEY,
  repo_id INTEGER NOT NULL,
  name TEXT NOT NULL,
  color TEXT NOT NULL,
  UNIQUE(repo_id, name)
);
CREATE TABLE IF NOT EXISTS milestones (
  id INTEGER PRIMARY KEY,
  repo_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  due_on TEXT NOT NULL DEFAULT '',
  state TEXT NOT NULL DEFAULT 'open',
  UNIQUE(repo_id, title)
);
CREATE TABLE IF NOT EXISTS issues (
  id INTEGER PRIMARY KEY,
  repo_id INTEGER NOT NULL,
  number INTEGER NOT NULL,
  title TEXT NOT NULL,
  description TEXT NOT NULL DEFAULT '',
  author_id INTEGER NOT NULL,
  status TEXT NOT NULL DEFAULT 'open',
  confidential INTEGER NOT NULL DEFAULT 0,
  milestone_id INTEGER NOT NULL DEFAULT 0,
  due_on TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  closed_at TEXT NOT NULL DEFAULT '',
  UNIQUE(repo_id, number)
);
CREATE TABLE IF NOT EXISTS issue_labels (
  issue_id INTEGER NOT NULL,
  label_id INTEGER NOT NULL,
  PRIMARY KEY(issue_id, label_id)
);
CREATE TABLE IF NOT EXISTS issue_assignees (
  issue_id INTEGER NOT NULL,
  user_id INTEGER NOT NULL,
  PRIMARY KEY(issue_id, user_id)
);
CREATE TABLE IF NOT EXISTS issue_notes (
  id INTEGER PRIMARY KEY,
  repo_id INTEGER NOT NULL,
  issue_id INTEGER NOT NULL,
  author_id INTEGER NOT NULL,
  body TEXT NOT NULL,
  created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS issue_links (
  repo_id INTEGER NOT NULL,
  a INTEGER NOT NULL,
  b INTEGER NOT NULL,
  PRIMARY KEY (a, b)
);
CREATE INDEX IF NOT EXISTS issues_repo ON issues(repo_id, number);
`

func now() string { return time.Now().UTC().Format(time.RFC3339) }

func parseTime(s string) time.Time {
	t, err := time.Parse(time.RFC3339, s)
	if err != nil {
		return time.Time{}
	}
	return t
}

// User is een ingelogd account van de auth-server.
type User struct {
	ID            int64
	AuthID        int64
	Username      string
	AvatarURL     string
	AuthAdmin     bool
	InstanceAdmin bool
	Language      string
	CreatedAt     time.Time
}

// UpsertUser maakt of ververst een account. De eerste gebruiker wordt
// installatiebeheerder. Wachtende lidmaatschappen op deze naam worden gekoppeld.
func (s *Store) UpsertUser(ctx context.Context, authID int64, username, avatar string, authAdmin bool) (User, error) {
	username = strings.ToLower(strings.TrimSpace(username))
	if err := names.ValidUsername(username); err != nil {
		return User{}, err
	}
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return User{}, err
	}
	defer func() { _ = tx.Rollback() }()

	var count int
	if err := tx.QueryRowContext(ctx, `SELECT COUNT(*) FROM users`).Scan(&count); err != nil {
		return User{}, err
	}
	ts := now()
	var existingID int64
	err = tx.QueryRowContext(ctx, `SELECT id FROM users WHERE auth_id = ?`, authID).Scan(&existingID)
	if errors.Is(err, sql.ErrNoRows) {
		var nameTaken int
		if err := tx.QueryRowContext(ctx, `SELECT COUNT(*) FROM users WHERE username = ?`, username).Scan(&nameTaken); err != nil {
			return User{}, err
		}
		if nameTaken > 0 {
			return User{}, i18n.E("err.userTaken", username)
		}
		admin := 0
		if count == 0 {
			admin = 1
		}
		res, err := tx.ExecContext(ctx, `INSERT INTO users (auth_id, username, avatar_url, auth_admin, instance_admin, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?)`,
			authID, username, avatar, boolInt(authAdmin), admin, ts, ts)
		if err != nil {
			return User{}, err
		}
		existingID, _ = res.LastInsertId()
	} else if err != nil {
		return User{}, err
	} else {
		if _, err := tx.ExecContext(ctx, `UPDATE users SET username = ?, avatar_url = ?, auth_admin = ?, updated_at = ? WHERE id = ? AND username = ?`,
			username, avatar, boolInt(authAdmin), ts, existingID, username); err != nil {
			return User{}, err
		}
		// Naamwijziging alleen als de nieuwe naam vrij is en gelijk blijft als hij botst.
		var current string
		if err := tx.QueryRowContext(ctx, `SELECT username FROM users WHERE id = ?`, existingID).Scan(&current); err != nil {
			return User{}, err
		}
		if current != username {
			var taken int
			if err := tx.QueryRowContext(ctx, `SELECT COUNT(*) FROM users WHERE username = ? AND id <> ?`, username, existingID).Scan(&taken); err != nil {
				return User{}, err
			}
			var groupTaken int
			if err := tx.QueryRowContext(ctx, `SELECT COUNT(*) FROM groups WHERE path = ?`, username).Scan(&groupTaken); err != nil {
				return User{}, err
			}
			if taken == 0 && groupTaken == 0 {
				if _, err := tx.ExecContext(ctx, `UPDATE users SET username = ?, avatar_url = ?, auth_admin = ?, updated_at = ? WHERE id = ?`,
					username, avatar, boolInt(authAdmin), ts, existingID); err != nil {
					return User{}, err
				}
			} else if _, err := tx.ExecContext(ctx, `UPDATE users SET avatar_url = ?, auth_admin = ?, updated_at = ? WHERE id = ?`,
				avatar, boolInt(authAdmin), ts, existingID); err != nil {
				return User{}, err
			}
		}
	}
	if err := claimPending(ctx, tx, existingID, username); err != nil {
		return User{}, err
	}
	if err := tx.Commit(); err != nil {
		return User{}, err
	}
	return s.UserByID(ctx, existingID)
}

func claimPending(ctx context.Context, tx *sql.Tx, userID int64, username string) error {
	rows, err := tx.QueryContext(ctx, `SELECT id, scope_kind, scope_id, role FROM pending_members WHERE username = ?`, username)
	if err != nil {
		return err
	}
	defer rows.Close()
	type pend struct {
		id, scopeID int64
		kind, role  string
	}
	var all []pend
	for rows.Next() {
		var p pend
		if err := rows.Scan(&p.id, &p.kind, &p.scopeID, &p.role); err != nil {
			return err
		}
		all = append(all, p)
	}
	if err := rows.Err(); err != nil {
		return err
	}
	for _, p := range all {
		switch p.kind {
		case "group":
			if _, err := tx.ExecContext(ctx, `INSERT OR IGNORE INTO group_members (group_id, user_id, role) VALUES (?, ?, ?)`, p.scopeID, userID, p.role); err != nil {
				return err
			}
		case "repo":
			if _, err := tx.ExecContext(ctx, `INSERT OR IGNORE INTO repo_members (repo_id, user_id, role) VALUES (?, ?, ?)`, p.scopeID, userID, p.role); err != nil {
				return err
			}
		}
		if _, err := tx.ExecContext(ctx, `DELETE FROM pending_members WHERE id = ?`, p.id); err != nil {
			return err
		}
	}
	return nil
}

// UserByID leest één account.
func (s *Store) UserByID(ctx context.Context, id int64) (User, error) {
	row := s.db.QueryRowContext(ctx, `SELECT id, auth_id, username, avatar_url, auth_admin, instance_admin, language, created_at FROM users WHERE id = ?`, id)
	return scanUser(row)
}

// UserByUsername leest op naam.
func (s *Store) UserByUsername(ctx context.Context, username string) (User, error) {
	row := s.db.QueryRowContext(ctx, `SELECT id, auth_id, username, avatar_url, auth_admin, instance_admin, language, created_at FROM users WHERE username = ?`, strings.ToLower(username))
	return scanUser(row)
}

func scanUser(row *sql.Row) (User, error) {
	var u User
	var admin, inst int
	var created string
	if err := row.Scan(&u.ID, &u.AuthID, &u.Username, &u.AvatarURL, &admin, &inst, &u.Language, &created); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return User{}, errNotFound
		}
		return User{}, err
	}
	u.AuthAdmin = admin == 1
	u.InstanceAdmin = inst == 1
	if u.Language == "" {
		u.Language = "en"
	}
	u.CreatedAt = parseTime(created)
	return u, nil
}

// Session is een browsersessie.
type Session struct {
	ID          string
	UserID      int64
	AccessToken string
	ExpiresAt   time.Time
	CSRF        string
	User        User
}

// CreateSession bewaart het bearer-token van de auth-server.
func (s *Store) CreateSession(ctx context.Context, userID int64, accessToken string, expiresAt time.Time) (Session, error) {
	id, err := randomID()
	if err != nil {
		return Session{}, err
	}
	csrf, err := randomID()
	if err != nil {
		return Session{}, err
	}
	_, err = s.db.ExecContext(ctx, `INSERT INTO sessions (id, user_id, access_token, expires_at, csrf, created_at) VALUES (?, ?, ?, ?, ?, ?)`,
		id, userID, accessToken, expiresAt.UTC().Format(time.RFC3339), csrf, now())
	if err != nil {
		return Session{}, err
	}
	return s.Session(ctx, id)
}

// Session leest een sessie met de gebruiker.
func (s *Store) Session(ctx context.Context, id string) (Session, error) {
	var sess Session
	var exp string
	err := s.db.QueryRowContext(ctx, `SELECT id, user_id, access_token, expires_at, csrf FROM sessions WHERE id = ?`, id).
		Scan(&sess.ID, &sess.UserID, &sess.AccessToken, &exp, &sess.CSRF)
	if errors.Is(err, sql.ErrNoRows) {
		return Session{}, errNotFound
	}
	if err != nil {
		return Session{}, err
	}
	sess.ExpiresAt = parseTime(exp)
	u, err := s.UserByID(ctx, sess.UserID)
	if err != nil {
		return Session{}, err
	}
	sess.User = u
	return sess, nil
}

// UpdateSessionExpiry schuift de lokale kopie van de auth-sessie op.
func (s *Store) UpdateSessionExpiry(ctx context.Context, id string, expiresAt time.Time) error {
	_, err := s.db.ExecContext(ctx, `UPDATE sessions SET expires_at = ? WHERE id = ?`, expiresAt.UTC().Format(time.RFC3339), id)
	return err
}

// DeleteSession verwijdert de lokale sessie.
func (s *Store) DeleteSession(ctx context.Context, id string) error {
	_, err := s.db.ExecContext(ctx, `DELETE FROM sessions WHERE id = ?`, id)
	return err
}

// TokenInfo is een persoonlijk toegangstoken zonder het geheim.
type TokenInfo struct {
	ID         int64
	Name       string
	Prefix     string
	Scopes     string
	CreatedAt  time.Time
	LastUsedAt time.Time
	ExpiresAt  time.Time
}

// TokenAuth is een geldig token plus de eigenaar.
type TokenAuth struct {
	Token TokenInfo
	User  User
}

// TokenRequest beschrijft een nieuw token. Lege scopes worden api.
type TokenRequest struct {
	Name      string
	Scopes    string
	ExpiresAt time.Time
}

// CreateToken maakt een bg_-token met scope api. De tekst wordt één keer teruggegeven.
func (s *Store) CreateToken(ctx context.Context, userID int64, name string) (string, error) {
	plain, _, err := s.IssueToken(ctx, userID, TokenRequest{Name: name, Scopes: "api"})
	return plain, err
}

// IssueToken maakt een token met scopes en een optionele vervaldatum.
func (s *Store) IssueToken(ctx context.Context, userID int64, req TokenRequest) (string, TokenInfo, error) {
	name := strings.TrimSpace(req.Name)
	if name == "" || len(name) > 80 {
		return "", TokenInfo{}, i18n.E("err.tokenNeedsName")
	}
	scopes := strings.TrimSpace(req.Scopes)
	if scopes == "" {
		scopes = "api"
	}
	if !req.ExpiresAt.IsZero() && !req.ExpiresAt.After(time.Now()) {
		return "", TokenInfo{}, i18n.E("err.tokenExpired")
	}
	plain, err := newToken("bg_")
	if err != nil {
		return "", TokenInfo{}, err
	}
	var exp any
	if !req.ExpiresAt.IsZero() {
		exp = req.ExpiresAt.UTC().Format(time.RFC3339)
	}
	res, err := s.db.ExecContext(ctx, `INSERT INTO access_tokens (user_id, name, token_hash, prefix, scopes, created_at, expires_at) VALUES (?, ?, ?, ?, ?, ?, ?)`,
		userID, name, hashToken(plain), plain[:10], scopes, now(), exp)
	if err != nil {
		return "", TokenInfo{}, err
	}
	id, _ := res.LastInsertId()
	info := TokenInfo{ID: id, Name: name, Prefix: plain[:10], Scopes: scopes, CreatedAt: time.Now().UTC(), ExpiresAt: req.ExpiresAt}
	return plain, info, nil
}

// ListTokens toont de tokens van een gebruiker.
func (s *Store) ListTokens(ctx context.Context, userID int64) ([]TokenInfo, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT id, name, prefix, COALESCE(scopes, 'api'), created_at, COALESCE(last_used_at, ''), COALESCE(expires_at, '') FROM access_tokens WHERE user_id = ? ORDER BY id DESC`, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []TokenInfo
	for rows.Next() {
		var t TokenInfo
		var created, used, expires string
		if err := rows.Scan(&t.ID, &t.Name, &t.Prefix, &t.Scopes, &created, &used, &expires); err != nil {
			return nil, err
		}
		t.CreatedAt = parseTime(created)
		t.LastUsedAt = parseTime(used)
		t.ExpiresAt = parseTime(expires)
		out = append(out, t)
	}
	return out, rows.Err()
}

// DeleteToken verwijdert een token van deze gebruiker.
func (s *Store) DeleteToken(ctx context.Context, userID, id int64) error {
	res, err := s.db.ExecContext(ctx, `DELETE FROM access_tokens WHERE id = ? AND user_id = ?`, id, userID)
	if err != nil {
		return err
	}
	n, _ := res.RowsAffected()
	if n == 0 {
		return errNotFound
	}
	return nil
}

// UserByToken zoekt de eigenaar van een persoonlijk token.
func (s *Store) UserByToken(ctx context.Context, token string) (User, error) {
	auth, err := s.AuthenticateToken(ctx, token)
	if err != nil {
		return User{}, err
	}
	return auth.User, nil
}

// AuthenticateToken controleert het geheim, de vervaldatum en geeft de scopes terug.
func (s *Store) AuthenticateToken(ctx context.Context, token string) (TokenAuth, error) {
	if !strings.HasPrefix(token, "bg_") {
		return TokenAuth{}, errNotFound
	}
	var info TokenInfo
	var userID int64
	var created, expires, scopes string
	err := s.db.QueryRowContext(ctx, `SELECT id, user_id, name, prefix, COALESCE(scopes, 'api'), created_at, COALESCE(expires_at, '') FROM access_tokens WHERE token_hash = ?`, hashToken(token)).
		Scan(&info.ID, &userID, &info.Name, &info.Prefix, &scopes, &created, &expires)
	if errors.Is(err, sql.ErrNoRows) {
		return TokenAuth{}, errNotFound
	}
	if err != nil {
		return TokenAuth{}, err
	}
	info.Scopes = scopes
	info.CreatedAt = parseTime(created)
	info.ExpiresAt = parseTime(expires)
	if !info.ExpiresAt.IsZero() && !info.ExpiresAt.After(time.Now()) {
		return TokenAuth{}, i18n.E("err.tokenExpired")
	}
	_, _ = s.db.ExecContext(ctx, `UPDATE access_tokens SET last_used_at = ? WHERE id = ?`, now(), info.ID)
	user, err := s.UserByID(ctx, userID)
	if err != nil {
		return TokenAuth{}, err
	}
	return TokenAuth{Token: info, User: user}, nil
}

// SSHKey is een publieke sleutel voor git-over-SSH.
type SSHKey struct {
	ID          int64
	UserID      int64
	Name        string
	PublicKey   string
	Fingerprint string
	CreatedAt   time.Time
}

// AddSSHKey bewaart één OpenSSH-regel.
func (s *Store) AddSSHKey(ctx context.Context, userID int64, name, line, fingerprint string) error {
	name = strings.TrimSpace(name)
	if name == "" {
		name = "sleutel"
	}
	_, err := s.db.ExecContext(ctx, `INSERT INTO ssh_keys (user_id, name, public_key, fingerprint, created_at) VALUES (?, ?, ?, ?, ?)`,
		userID, name, strings.TrimSpace(line), fingerprint, now())
	if err != nil && strings.Contains(err.Error(), "UNIQUE") {
		return i18n.E("err.sshDup")
	}
	return err
}

// ListSSHKeys toont de sleutels van een gebruiker.
func (s *Store) ListSSHKeys(ctx context.Context, userID int64) ([]SSHKey, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT id, user_id, name, public_key, fingerprint, created_at FROM ssh_keys WHERE user_id = ? ORDER BY id`, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanKeys(rows)
}

// SSHKeyByFingerprint zoekt een sleutel voor de SSH-server.
func (s *Store) SSHKeyByFingerprint(ctx context.Context, fingerprint string) (SSHKey, error) {
	row := s.db.QueryRowContext(ctx, `SELECT id, user_id, name, public_key, fingerprint, created_at FROM ssh_keys WHERE fingerprint = ?`, fingerprint)
	var k SSHKey
	var created string
	if err := row.Scan(&k.ID, &k.UserID, &k.Name, &k.PublicKey, &k.Fingerprint, &created); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return SSHKey{}, errNotFound
		}
		return SSHKey{}, err
	}
	k.CreatedAt = parseTime(created)
	return k, nil
}

// DeleteSSHKey verwijdert een sleutel van deze gebruiker.
func (s *Store) DeleteSSHKey(ctx context.Context, userID, id int64) error {
	res, err := s.db.ExecContext(ctx, `DELETE FROM ssh_keys WHERE id = ? AND user_id = ?`, id, userID)
	if err != nil {
		return err
	}
	n, _ := res.RowsAffected()
	if n == 0 {
		return errNotFound
	}
	return nil
}

func scanKeys(rows *sql.Rows) ([]SSHKey, error) {
	var out []SSHKey
	for rows.Next() {
		var k SSHKey
		var created string
		if err := rows.Scan(&k.ID, &k.UserID, &k.Name, &k.PublicKey, &k.Fingerprint, &created); err != nil {
			return nil, err
		}
		k.CreatedAt = parseTime(created)
		out = append(out, k)
	}
	return out, rows.Err()
}

func randomID() (string, error) {
	b := make([]byte, 32)
	if _, err := rand.Read(b); err != nil {
		return "", err
	}
	return hex.EncodeToString(b), nil
}

func newToken(prefix string) (string, error) {
	b := make([]byte, 32)
	if _, err := rand.Read(b); err != nil {
		return "", err
	}
	return prefix + hex.EncodeToString(b), nil
}

func hashToken(token string) string {
	sum := sha256.Sum256([]byte(token))
	return hex.EncodeToString(sum[:])
}

func boolInt(b bool) int {
	if b {
		return 1
	}
	return 0
}

var errNotFound = fmt.Errorf("niet gevonden")

// Missing meldt dat de gevraagde rij er niet is.
func Missing(err error) bool { return errors.Is(err, errNotFound) }

// NotFound is de fout voor een ontbrekende rij.
func NotFound() error { return errNotFound }

// ErrNotFound is het resultaat als een rij ontbreekt.
func ErrNotFound() error { return errNotFound }

// IsNotFound meldt of err een ontbrekende rij is.
func IsNotFound(err error) bool { return errors.Is(err, errNotFound) }

func mustJSON(v any) string {
	b, _ := json.Marshal(v)
	if b == nil {
		return "null"
	}
	return string(b)
}

// EncryptSecret versleutelt een geheim met de installatiesleutel.
func (s *Store) encrypt(plain string) (nonce, ct []byte, err error) {
	return secretbox.Encrypt(s.key, plain)
}

func (s *Store) decrypt(nonce, ct []byte) (string, error) {
	return secretbox.Decrypt(s.key, nonce, ct)
}
