package store

import (
	"database/sql"
	"errors"
	"fmt"
	"strings"
	"time"
)

type User struct {
	ID       int64
	Username string
	IsAdmin  bool
	Pending  bool // self-registered, email not yet verified
	Disabled bool // administratively suspended, or scheduled for deletion
	// DeleteAfter is when a scheduled deletion purges the account, ""
	// when none is scheduled. A scheduled account is also Disabled.
	DeleteAfter string
	Ghost       bool // stands in as the author of deleted accounts' content
	// SignedInAt is when the browser session this user came from was
	// created by a login. Set by WebSessionUser only; zero elsewhere.
	SignedInAt time.Time
}

type SSHKey struct {
	ID          int64
	UserID      int64
	Fingerprint string
	Algo        string
	Blob        []byte
	Scope       string
	Label       string // "" when the key was added with no name
	CreatedAt   string
	LastUsedAt  string     // "" when the key has never authenticated
	CreatedBy   string     // name of the API token that added the key; "" for none. ListSSHKeys only.
	ExpiresAt   *time.Time // nil when the key never expires
}

// Expired reports whether the key has lapsed at now.
func (k SSHKey) Expired(now time.Time) bool {
	return k.ExpiresAt != nil && !k.ExpiresAt.After(now)
}

// ErrDuplicateKey carries the exact user-facing message from the spec. It
// deliberately does not name the owning account (enumeration oracle).
var ErrDuplicateKey = errors.New("that key is already registered to another account; remove it there first or use a different key")

var ErrNotFound = errors.New("not found")

func (s *Store) CreateUser(username string, isAdmin bool) (int64, error) {
	if taken, err := ownerNameTaken(s.DB, username); err != nil {
		return 0, err
	} else if taken {
		return 0, fmt.Errorf("username %q is taken", username)
	}
	res, err := s.DB.Exec("INSERT INTO users (username, is_admin) VALUES (?, ?)", username, boolInt(isAdmin))
	if err != nil {
		if isUniqueErr(err) {
			return 0, fmt.Errorf("username %q is taken", username)
		}
		return 0, err
	}
	return res.LastInsertId()
}

// DeleteUser removes an account whose removal orphans nothing: no owned
// repositories, no authored issues, MRs, comments, or reviews, and not the
// only admin of an org. Everything else (keys, emails, sessions, tokens,
// pins, memberships, activity) cascades. Blockers come back as an error
// naming what stands in the way, so the operator can transfer, delete, or
// disable instead.
func (s *Store) DeleteUser(id int64) error {
	var blockers []string
	var checkErr error
	count := func(q string, what string) {
		var n int
		if err := s.DB.QueryRow(q, id).Scan(&n); err != nil {
			if checkErr == nil {
				checkErr = fmt.Errorf("checking %s: %w", what, err)
			}
			return
		}
		if n > 0 {
			blockers = append(blockers, fmt.Sprintf("%d %s", n, what))
		}
	}
	count("SELECT COUNT(*) FROM repos WHERE owner_kind = 'user' AND owner_id = ?", "owned repositories")
	count("SELECT COUNT(*) FROM issues WHERE author_id = ?", "authored issues")
	count("SELECT COUNT(*) FROM merge_requests WHERE author_id = ?", "authored merge requests")
	count("SELECT COUNT(*) FROM issue_comments WHERE author_id = ?", "issue comments")
	count("SELECT COUNT(*) FROM mr_comments WHERE author_id = ?", "MR comments")
	count("SELECT COUNT(*) FROM mr_diff_comments WHERE author_id = ?", "diff comments")
	count("SELECT COUNT(*) FROM mr_reviews WHERE reviewer_id = ?", "reviews")
	count(`SELECT COUNT(*) FROM org_members m WHERE m.user_id = ? AND m.role = 'admin'
		AND NOT EXISTS (SELECT 1 FROM org_members o
			WHERE o.org_id = m.org_id AND o.role = 'admin' AND o.user_id != m.user_id)`,
		"organizations with no other admin")
	if checkErr != nil {
		return checkErr
	}
	if len(blockers) > 0 {
		return fmt.Errorf("account still anchors: %s — transfer or delete those first, or disable the account instead",
			strings.Join(blockers, ", "))
	}
	// Grants and a parked about text name the account by id with no
	// foreign key, so they go in the same transaction (#306).
	tx, err := s.DB.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()
	if _, err := tx.Exec("DELETE FROM repo_access WHERE subject_kind = 'user' AND subject_id = ?", id); err != nil {
		return err
	}
	if _, err := tx.Exec("DELETE FROM profile_about_backfill WHERE owner_kind = 'user' AND owner_id = ?", id); err != nil {
		return err
	}
	res, err := tx.Exec("DELETE FROM users WHERE id = ?", id)
	if err != nil {
		return err
	}
	if n, _ := res.RowsAffected(); n == 0 {
		return ErrNotFound
	}
	if err := tx.Commit(); err != nil {
		return err
	}
	s.announce(Revoked{UserID: id})
	return nil
}

// OwnerExists reports whether a user or org owns the name — the ACME host
// policy check for pages subdomains.
func (s *Store) OwnerExists(name string) bool {
	var n int
	s.DB.QueryRow(`SELECT (SELECT COUNT(*) FROM users WHERE username = ?1)
		+ (SELECT COUNT(*) FROM orgs WHERE name = ?1)`, name).Scan(&n)
	return n > 0
}

func (s *Store) UserByUsername(name string) (User, error) {
	var u User
	var admin, pending, disabled, ghost int
	var deleteAfter sql.NullString
	err := s.DB.QueryRow("SELECT id, username, is_admin, pending, disabled, delete_after, ghost FROM users WHERE username = ?", name).
		Scan(&u.ID, &u.Username, &admin, &pending, &disabled, &deleteAfter, &ghost)
	if errors.Is(err, sql.ErrNoRows) {
		return u, ErrNotFound
	}
	u.IsAdmin = admin != 0
	u.Pending = pending != 0
	u.Disabled = disabled != 0
	u.DeleteAfter = deleteAfter.String
	u.Ghost = ghost != 0
	return u, err
}

// UserEmailAddresses returns every address on the account, verified or not.
func (s *Store) UserEmailAddresses(userID int64) ([]string, error) {
	rows, err := s.DB.Query("SELECT address FROM emails WHERE user_id = ? ORDER BY is_primary DESC, address", userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []string
	for rows.Next() {
		var a string
		if err := rows.Scan(&a); err != nil {
			return nil, err
		}
		out = append(out, a)
	}
	return out, rows.Err()
}

// Email is one address on an account, with the state the signature rules
// and notification routing depend on.
type Email struct {
	Address    string
	Verified   bool
	VerifiedBy string // smtp | admin, empty when unverified
	Primary    bool
}

// ListEmails returns every address on the account with its state.
func (s *Store) ListEmails(userID int64) ([]Email, error) {
	rows, err := s.DB.Query(`SELECT address, verified_at IS NOT NULL,
		COALESCE(verified_by, ''), is_primary
		FROM emails WHERE user_id = ? ORDER BY is_primary DESC, address`, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Email
	for rows.Next() {
		var e Email
		if err := rows.Scan(&e.Address, &e.Verified, &e.VerifiedBy, &e.Primary); err != nil {
			return nil, err
		}
		out = append(out, e)
	}
	return out, rows.Err()
}

// SetUserDisabled suspends or restores an account. Disabling drops every
// credential that would grant a session on its own — web sessions, API
// tokens, unclaimed login links — and leaves the SSH keys registered but
// refused at every entry point until re-enabled; connections they opened
// are closed.
func (s *Store) SetUserDisabled(userID int64, disabled bool) error {
	v := 0
	if _, err := s.DB.Exec("DELETE FROM account_deletions WHERE user_id = ?", userID); err != nil {
		return err
	}
	if disabled {
		v = 1
	}
	// An admin's decision replaces a scheduled or requested deletion
	// either way: a disabled account stays disabled and is not purged,
	// an enabled one is back in use. A purge already under way finishes.
	res, err := s.DB.Exec("UPDATE users SET disabled = ?, delete_after = CASE WHEN delete_after = ? THEN delete_after END WHERE id = ?", v, Purging, userID)
	if err != nil {
		return err
	}
	if n, _ := res.RowsAffected(); n == 0 {
		return ErrNotFound
	}
	if disabled {
		// A pending login link is a session in waiting, so it goes with
		// the sessions and API tokens. Re-enabling means minting again.
		for _, table := range []string{"web_sessions", "api_tokens", "login_tokens"} {
			if _, err = s.DB.Exec("DELETE FROM "+table+" WHERE user_id = ?", userID); err != nil {
				return err
			}
		}
		s.announce(Revoked{UserID: userID})
	}
	return err
}

// MailEnabled reports whether activity notifications reach the account
// by mail as well as the inbox.
func (s *Store) MailEnabled(userID int64) (bool, error) {
	var on int
	err := s.DB.QueryRow("SELECT notify_mail FROM users WHERE id = ?", userID).Scan(&on)
	if errors.Is(err, sql.ErrNoRows) {
		return false, ErrNotFound
	}
	return on != 0, err
}

func (s *Store) SetMailEnabled(userID int64, on bool) error {
	v := 0
	if on {
		v = 1
	}
	_, err := s.DB.Exec("UPDATE users SET notify_mail = ? WHERE id = ?", v, userID)
	return err
}

// ReplyEnabled reports whether the account's issue and merge request
// mail carries a reply address (#295).
func (s *Store) ReplyEnabled(userID int64) (bool, error) {
	var on int
	err := s.DB.QueryRow("SELECT notify_reply FROM users WHERE id = ?", userID).Scan(&on)
	if errors.Is(err, sql.ErrNoRows) {
		return false, ErrNotFound
	}
	return on != 0, err
}

func (s *Store) SetReplyEnabled(userID int64, on bool) error {
	v := 0
	if on {
		v = 1
	}
	_, err := s.DB.Exec("UPDATE users SET notify_reply = ? WHERE id = ?", v, userID)
	return err
}

// WatchEnabled reports whether the account hears about every issue and
// merge request on the repositories it can write to, without a
// repo_watchers row on each (#194).
func (s *Store) WatchEnabled(userID int64) (bool, error) {
	var on int
	err := s.DB.QueryRow("SELECT notify_watch FROM users WHERE id = ?", userID).Scan(&on)
	if errors.Is(err, sql.ErrNoRows) {
		return false, ErrNotFound
	}
	return on != 0, err
}

func (s *Store) SetWatchEnabled(userID int64, on bool) error {
	v := 0
	if on {
		v = 1
	}
	_, err := s.DB.Exec("UPDATE users SET notify_watch = ? WHERE id = ?", v, userID)
	return err
}

// Theme is the web colour scheme the account chose: system, light or
// dark (#232).
func (s *Store) Theme(userID int64) (string, error) {
	var theme string
	err := s.DB.QueryRow("SELECT theme FROM users WHERE id = ?", userID).Scan(&theme)
	if errors.Is(err, sql.ErrNoRows) {
		return "", ErrNotFound
	}
	return theme, err
}

func (s *Store) SetTheme(userID int64, theme string) error {
	_, err := s.DB.Exec("UPDATE users SET theme = ? WHERE id = ?", theme, userID)
	return err
}

// DiffLayout is how the account wants diffs drawn on the web: unified or
// split (#290).
func (s *Store) DiffLayout(userID int64) (string, error) {
	var l string
	err := s.DB.QueryRow("SELECT diff_layout FROM users WHERE id = ?", userID).Scan(&l)
	if errors.Is(err, sql.ErrNoRows) {
		return "", ErrNotFound
	}
	return l, err
}

func (s *Store) SetDiffLayout(userID int64, layout string) error {
	_, err := s.DB.Exec("UPDATE users SET diff_layout = ? WHERE id = ?", layout, userID)
	return err
}

func (s *Store) UserByID(id int64) (User, error) {
	var u User
	var admin, pending, disabled, ghost int
	var deleteAfter sql.NullString
	err := s.DB.QueryRow("SELECT id, username, is_admin, pending, disabled, delete_after, ghost FROM users WHERE id = ?", id).
		Scan(&u.ID, &u.Username, &admin, &pending, &disabled, &deleteAfter, &ghost)
	if errors.Is(err, sql.ErrNoRows) {
		return u, ErrNotFound
	}
	u.IsAdmin = admin != 0
	u.Pending = pending != 0
	u.Disabled = disabled != 0
	u.DeleteAfter = deleteAfter.String
	u.Ghost = ghost != 0
	return u, err
}

// KeyOrigin is how a key came to be.
type KeyOrigin struct {
	CreatedByToken int64      // the API token that added it; 0 for none
	ExpiresAt      *time.Time // when it stops authenticating; nil for never
}

// AddSSHKey registers a key and bumps the key epoch in one transaction.
func (s *Store) AddSSHKey(userID int64, fingerprint, algo string, blob []byte, scope, label string) error {
	return s.AddSSHKeyFrom(userID, fingerprint, algo, blob, scope, label, KeyOrigin{})
}

// AddSSHKeyFrom is AddSSHKey recording where the key came from.
func (s *Store) AddSSHKeyFrom(userID int64, fingerprint, algo string, blob []byte, scope, label string, o KeyOrigin) error {
	tx, err := s.DB.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()
	var exp any
	if o.ExpiresAt != nil {
		exp = fmtTime(*o.ExpiresAt)
	}
	if _, err := tx.Exec(
		"INSERT INTO ssh_keys (user_id, fingerprint, algo, blob, scope, label, created_by_token, expires_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
		userID, fingerprint, algo, blob, scope, label, nullID(o.CreatedByToken), exp); err != nil {
		if isUniqueErr(err) {
			return ErrDuplicateKey
		}
		return err
	}
	if err := bumpKeyEpoch(tx); err != nil {
		return err
	}
	return tx.Commit()
}

// RemoveSSHKey removes a key owned by userID, bumps the key epoch, and
// announces the revocation.
func (s *Store) RemoveSSHKey(userID int64, fingerprint string) error {
	tx, err := s.DB.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()
	var id int64
	err = tx.QueryRow("DELETE FROM ssh_keys WHERE user_id = ? AND fingerprint = ? RETURNING id", userID, fingerprint).Scan(&id)
	if errors.Is(err, sql.ErrNoRows) {
		return ErrNotFound
	}
	if err != nil {
		return err
	}
	if err := bumpKeyEpoch(tx); err != nil {
		return err
	}
	if err := tx.Commit(); err != nil {
		return err
	}
	s.announce(Revoked{KeyIDs: []int64{id}})
	return nil
}

// SetSSHKeyLabel renames a key owned by userID. Labels do not touch the
// key epoch: nothing about authentication changes.
func (s *Store) SetSSHKeyLabel(userID int64, fingerprint, label string) error {
	res, err := s.DB.Exec("UPDATE ssh_keys SET label = ? WHERE user_id = ? AND fingerprint = ?", label, userID, fingerprint)
	if err != nil {
		return err
	}
	if n, _ := res.RowsAffected(); n == 0 {
		return ErrNotFound
	}
	return nil
}

func (s *Store) SSHKeyByFingerprint(fingerprint string) (SSHKey, error) {
	var k SSHKey
	var exp sql.NullString
	err := s.DB.QueryRow(
		"SELECT id, user_id, fingerprint, algo, blob, scope, label, expires_at FROM ssh_keys WHERE fingerprint = ?",
		fingerprint).Scan(&k.ID, &k.UserID, &k.Fingerprint, &k.Algo, &k.Blob, &k.Scope, &k.Label, &exp)
	if errors.Is(err, sql.ErrNoRows) {
		return k, ErrNotFound
	}
	k.ExpiresAt = parseTime(exp)
	return k, err
}

func (s *Store) ListSSHKeys(userID int64) ([]SSHKey, error) {
	rows, err := s.DB.Query(
		`SELECT k.id, k.user_id, k.fingerprint, k.algo, k.blob, k.scope, k.label, k.created_at,
		        COALESCE(k.last_used_at, ''), COALESCE(t.name, ''), k.expires_at
		 FROM ssh_keys k LEFT JOIN api_tokens t ON t.id = k.created_by_token
		 WHERE k.user_id = ? ORDER BY k.id`,
		userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var keys []SSHKey
	for rows.Next() {
		var k SSHKey
		var exp sql.NullString
		if err := rows.Scan(&k.ID, &k.UserID, &k.Fingerprint, &k.Algo, &k.Blob, &k.Scope, &k.Label, &k.CreatedAt, &k.LastUsedAt, &k.CreatedBy, &exp); err != nil {
			return nil, err
		}
		k.ExpiresAt = parseTime(exp)
		keys = append(keys, k)
	}
	return keys, rows.Err()
}

// TouchSSHKey records key use; best-effort, callers ignore the error.
func (s *Store) TouchSSHKey(id int64) error {
	_, err := s.DB.Exec(
		"UPDATE ssh_keys SET last_used_at = strftime('%Y-%m-%dT%H:%M:%fZ','now') WHERE id = ?", id)
	return err
}

// AddEmail adds an address; verifiedBy is "" (unverified), "smtp", or "admin".
// Adding an already-verified address bumps the key epoch: it is a trust input
// for signature states.
func (s *Store) AddEmail(userID int64, address, verifiedBy string, primary bool) error {
	tx, err := s.DB.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()
	var vAt, vBy any
	if verifiedBy != "" {
		vAt = "now"
		vBy = verifiedBy
	}
	_, err = tx.Exec(
		`INSERT INTO emails (user_id, address, verified_at, verified_by, is_primary)
		 VALUES (?, ?, CASE WHEN ? IS NULL THEN NULL ELSE strftime('%Y-%m-%dT%H:%M:%fZ','now') END, ?, ?)`,
		userID, address, vAt, vBy, boolInt(primary))
	if isUniqueErr(err) {
		return fmt.Errorf("address %q is already in use", address)
	}
	if err != nil {
		return err
	}
	if verifiedBy != "" {
		if err := bumpKeyEpoch(tx); err != nil {
			return err
		}
	}
	return tx.Commit()
}

var (
	ErrPrimaryEmail      = errors.New("that is the primary address; make another address primary first")
	ErrLastVerifiedEmail = errors.New("that is the only verified address on the account; verify another first")
	ErrUnverifiedEmail   = errors.New("that address is not verified")
)

// RemoveEmail drops an address from the account, and any verification
// code pending for it. The primary and the last verified address stay:
// activation, login links and commit identity all resolve through
// verified addresses. Removing a verified address bumps the key epoch,
// since the signature cache keys on verified addresses too.
func (s *Store) RemoveEmail(userID int64, address string) error {
	tx, err := s.DB.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()
	var primary, verified bool
	err = tx.QueryRow("SELECT is_primary, verified_at IS NOT NULL FROM emails WHERE user_id = ? AND address = ?",
		userID, address).Scan(&primary, &verified)
	if errors.Is(err, sql.ErrNoRows) {
		return ErrNotFound
	}
	if err != nil {
		return err
	}
	if primary {
		return ErrPrimaryEmail
	}
	if verified {
		var others int
		if err := tx.QueryRow("SELECT count(*) FROM emails WHERE user_id = ? AND verified_at IS NOT NULL AND address != ?",
			userID, address).Scan(&others); err != nil {
			return err
		}
		if others == 0 {
			return ErrLastVerifiedEmail
		}
	}
	if _, err := tx.Exec("DELETE FROM email_tokens WHERE user_id = ? AND address = ?", userID, address); err != nil {
		return err
	}
	if _, err := tx.Exec("DELETE FROM emails WHERE user_id = ? AND address = ?", userID, address); err != nil {
		return err
	}
	if verified {
		if err := bumpKeyEpoch(tx); err != nil {
			return err
		}
	}
	return tx.Commit()
}

// SetPrimaryEmail makes a verified address the account's primary. The
// verified set is unchanged, so the key epoch is not.
func (s *Store) SetPrimaryEmail(userID int64, address string) error {
	tx, err := s.DB.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()
	var verified bool
	err = tx.QueryRow("SELECT verified_at IS NOT NULL FROM emails WHERE user_id = ? AND address = ?",
		userID, address).Scan(&verified)
	if errors.Is(err, sql.ErrNoRows) {
		return ErrNotFound
	}
	if err != nil {
		return err
	}
	if !verified {
		return ErrUnverifiedEmail
	}
	if _, err := tx.Exec("UPDATE emails SET is_primary = 0 WHERE user_id = ?", userID); err != nil {
		return err
	}
	if _, err := tx.Exec("UPDATE emails SET is_primary = 1 WHERE user_id = ? AND address = ?", userID, address); err != nil {
		return err
	}
	return tx.Commit()
}

func (s *Store) KeyEpoch() (int64, error) {
	var v int64
	err := s.DB.QueryRow("SELECT value FROM settings WHERE key = 'key_epoch'").Scan(&v)
	return v, err
}

type execer interface {
	Exec(query string, args ...any) (sql.Result, error)
}

func bumpKeyEpoch(tx execer) error {
	_, err := tx.Exec("UPDATE settings SET value = value + 1 WHERE key = 'key_epoch'")
	return err
}

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

func isUniqueErr(err error) bool {
	return err != nil && strings.Contains(err.Error(), "UNIQUE constraint failed")
}

func (s *Store) SSHKeyByID(id int64) (SSHKey, error) {
	var k SSHKey
	var exp sql.NullString
	err := s.DB.QueryRow(
		"SELECT id, user_id, fingerprint, algo, blob, scope, label, expires_at FROM ssh_keys WHERE id = ?",
		id).Scan(&k.ID, &k.UserID, &k.Fingerprint, &k.Algo, &k.Blob, &k.Scope, &k.Label, &exp)
	if errors.Is(err, sql.ErrNoRows) {
		return k, ErrNotFound
	}
	k.ExpiresAt = parseTime(exp)
	return k, err
}

// ListDeployKeys returns the deploy keys bound to a repository.
func (s *Store) ListDeployKeys(repoID int64) ([]SSHKey, error) {
	rows, err := s.DB.Query(
		`SELECT id, user_id, fingerprint, algo, blob, scope, label, COALESCE(last_used_at, ''), expires_at
		 FROM ssh_keys WHERE scope LIKE 'deploy:' || ? || ':%' ORDER BY id`,
		repoID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var keys []SSHKey
	for rows.Next() {
		var k SSHKey
		var exp sql.NullString
		if err := rows.Scan(&k.ID, &k.UserID, &k.Fingerprint, &k.Algo, &k.Blob, &k.Scope, &k.Label, &k.LastUsedAt, &exp); err != nil {
			return nil, err
		}
		k.ExpiresAt = parseTime(exp)
		keys = append(keys, k)
	}
	return keys, rows.Err()
}

// RemoveDeployKey removes a deploy key from a repository by fingerprint;
// any repo admin may remove it regardless of who added it.
func (s *Store) RemoveDeployKey(repoID int64, fingerprint string) error {
	tx, err := s.DB.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()
	var id int64
	err = tx.QueryRow(
		"DELETE FROM ssh_keys WHERE fingerprint = ? AND scope LIKE 'deploy:' || ? || ':%' RETURNING id",
		fingerprint, repoID).Scan(&id)
	if errors.Is(err, sql.ErrNoRows) {
		return ErrNotFound
	}
	if err != nil {
		return err
	}
	if err := bumpKeyEpoch(tx); err != nil {
		return err
	}
	if err := tx.Commit(); err != nil {
		return err
	}
	s.announce(Revoked{KeyIDs: []int64{id}})
	return nil
}