broad-announce/internal/authd/telegrambots.go

// Package authd — telegrambots.go: Telegram bot CRUD for M13b W3.
//
// Schema (post-migration 012):
//   bot_id            TEXT
//   company_id        TEXT FK -> public.companies(id)
//   name              TEXT (human label; e.g. "Acme Ops")
//   bot_token         TEXT  (plaintext; read by telegramd; M11
//                            security milestone will replace
//                            this with AES-256-GCM)
//   bot_token_hash    TEXT  (bcrypt; W3-added so the UI can
//                            render "configured" without
//                            exposing plaintext. NULL on
//                            pre-W3 rows until the operator
//                            rotates once.)
//   status            TEXT  (active | paused)
//   last_seen_at      TIMESTAMPTZ
//   created_at        TIMESTAMPTZ
//   welcome_message   TEXT  (W3; reply to /start)
//   default_source_id TEXT  (W3; soft FK to public.sources.id)
//   description       TEXT  (W3; free-text label)
//   last_rotated_at   TIMESTAMPTZ (W3; set on every token write)
//   updated_at        TIMESTAMPTZ (W3; trigger-maintained)
//
// Wire contract (UI):
//   The plaintext bot_token is NEVER returned. The response
//   shape includes `bot_token_set` (bool: bot_token IS NOT NULL
//   AND bot_token <> '') so the UI can render "Configured" /
//   "Not set" badges. The operator pastes a token on create
//   and on rotate; the server stores the plaintext (so
//   telegramd can use it) and bcrypt-hashes it for the hash
//   column. The plaintext leaves the server only via the
//   "rotate token" handshake, where the UI receives the new
//   token in the response body — once. After that, it cannot
//   be re-fetched.
//
// Threading: safe for concurrent use (pgx pool is goroutine-safe).
package authd

import (
	"context"
	"errors"
	"fmt"
	"strings"
	"time"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgconn"
)

// TelegramBot is the wire shape returned to handlers / JSON
// callers. Mirrors public.telegram_bots but excludes the
// plaintext bot_token; the UI sees only the `bot_token_set`
// boolean.
type TelegramBot struct {
	ID               string     `json:"id"`
	CompanyID        string     `json:"company_id"`
	Name             string     `json:"name"`
	WelcomeMessage   string     `json:"welcome_message,omitempty"`
	DefaultSourceID  string     `json:"default_source_id,omitempty"`
	Description      string     `json:"description,omitempty"`
	Status           string     `json:"status"`
	BotTokenSet      bool       `json:"bot_token_set"`
	LastSeenAt       *time.Time `json:"last_seen_at,omitempty"`
	LastRotatedAt    *time.Time `json:"last_rotated_at,omitempty"`
	CreatedAt        time.Time  `json:"created_at"`
	UpdatedAt        time.Time  `json:"updated_at"`
}

// ErrTelegramBotNotFound is returned when (company_id, id)
// doesn't exist.
var ErrTelegramBotNotFound = errors.New("authd: telegram bot not found")

// ErrTelegramBotIDTaken is returned when CreateTelegramBot sees
// a duplicate (company_id, id) for a tenant.
var ErrTelegramBotIDTaken = errors.New("authd: telegram bot id already in use")

// ErrTelegramBotInvalid is returned when input validation fails.
var ErrTelegramBotInvalid = errors.New("authd: telegram bot input invalid")

// validTelegramBotStatuses mirrors the schema default comment
// in 004. The M3 schema comment says active|paused, so we use
// that.
var validTelegramBotStatuses = map[string]struct{}{
	"active": {},
	"paused": {},
}

// TelegramBotFilter controls ListTelegramBots. Empty fields
// mean "no filter".
type TelegramBotFilter struct {
	Q      string // matches id OR name (ILIKE)
	Status string // exact match
	Limit  int
	Offset int
}

// CreateTelegramBotInput is the validated create payload. The
// bot_token is required on create (the operator got it from
// @BotFather and is pasting it in). WelcomeMessage and
// DefaultSourceID are optional. Description is optional.
type CreateTelegramBotInput struct {
	ID              string
	Name            string
	BotToken        string
	WelcomeMessage  string
	DefaultSourceID string
	Description     string
}

// UpdateTelegramBotInput is the PATCH payload. Pointer / non-nil
// fields mean "apply this." All fields optional; an empty patch
// is a no-op (returns the current row).
type UpdateTelegramBotInput struct {
	Name            *string
	WelcomeMessage  *string
	DefaultSourceID *string
	Description     *string
}

// Validate runs the constraints the DB enforces, but earlier
// and with friendlier error messages for the UI.
func (in *CreateTelegramBotInput) Validate() error {
	if !validTelegramBotID(in.ID) {
		return fmt.Errorf("%w: id must match ^[a-z0-9][a-z0-9-]{0,62}[a-z0-9]$", ErrTelegramBotInvalid)
	}
	if strings.TrimSpace(in.Name) == "" {
		return fmt.Errorf("%w: name is required", ErrTelegramBotInvalid)
	}
	if len(in.Name) > 200 {
		return fmt.Errorf("%w: name must be \u2264 200 characters", ErrTelegramBotInvalid)
	}
	if !validBotTokenFormat(in.BotToken) {
		return fmt.Errorf("%w: bot_token must match ^\\d+:[A-Za-z0-9_-]{35}$", ErrTelegramBotInvalid)
	}
	if len(in.WelcomeMessage) > 4096 {
		return fmt.Errorf("%w: welcome_message must be \u2264 4096 characters", ErrTelegramBotInvalid)
	}
	if in.DefaultSourceID != "" && !validTelegramBotID(in.DefaultSourceID) {
		return fmt.Errorf("%w: default_source_id must match ^[a-z0-9][a-z0-9-]{0,62}[a-z0-9]$", ErrTelegramBotInvalid)
	}
	if len(in.Description) > 500 {
		return fmt.Errorf("%w: description must be \u2264 500 characters", ErrTelegramBotInvalid)
	}
	return nil
}

// Validate is the same for Update. We don't enforce presence
// of fields (PATCH can be empty), just per-field constraints.
func (in *UpdateTelegramBotInput) Validate() error {
	if in.Name != nil {
		s := strings.TrimSpace(*in.Name)
		if s == "" {
			return fmt.Errorf("%w: name cannot be empty", ErrTelegramBotInvalid)
		}
		if len(s) > 200 {
			return fmt.Errorf("%w: name must be \u2264 200 characters", ErrTelegramBotInvalid)
		}
	}
	if in.WelcomeMessage != nil && len(*in.WelcomeMessage) > 4096 {
		return fmt.Errorf("%w: welcome_message must be \u2264 4096 characters", ErrTelegramBotInvalid)
	}
	if in.DefaultSourceID != nil && *in.DefaultSourceID != "" && !validTelegramBotID(*in.DefaultSourceID) {
		return fmt.Errorf("%w: default_source_id must match ^[a-z0-9][a-z0-9-]{0,62}[a-z0-9]$", ErrTelegramBotInvalid)
	}
	if in.Description != nil && len(*in.Description) > 500 {
		return fmt.Errorf("%w: description must be \u2264 500 characters", ErrTelegramBotInvalid)
	}
	return nil
}

// ListTelegramBots returns the bots visible to the caller under
// the given filter, plus the total count. W3 scopes by tenant:
// every caller sees only the bots of the tenant whose id is
// passed in the URL path. super_admin can list any tenant;
// tenant_admin can list their own (gate enforced in the
// HTTP handler, not here).
func (s *Store) ListTelegramBots(ctx context.Context, f TelegramBotFilter) ([]TelegramBot, int, error) {
	if s.pool == nil {
		return nil, 0, errors.New("authd: no DB pool (test mode)")
	}
	if f.Limit <= 0 {
		f.Limit = 100
	}
	if f.Limit > 500 {
		f.Limit = 500
	}
	args := []any{}
	conds := []string{}
	if strings.TrimSpace(f.Status) != "" {
		args = append(args, f.Status)
		conds = append(conds, fmt.Sprintf("status = $%d", len(args)))
	}
	if strings.TrimSpace(f.Q) != "" {
		args = append(args, "%"+strings.TrimSpace(f.Q)+"%")
		conds = append(conds, fmt.Sprintf("(bot_id ILIKE $%d OR name ILIKE $%d)", len(args), len(args)))
	}
	where := ""
	if len(conds) > 0 {
		where = "WHERE " + strings.Join(conds, " AND ")
	}
	var total int
	if err := s.pool.QueryRow(ctx, "SELECT COUNT(*) FROM public.telegram_bots "+where, args...).Scan(&total); err != nil {
		return nil, 0, fmt.Errorf("count telegram_bots: %w", err)
	}
	args = append(args, f.Limit, f.Offset)
	q := fmt.Sprintf(`
		SELECT bot_id, company_id, name,
		       COALESCE(welcome_message, ''),
		       COALESCE(default_source_id, ''),
		       COALESCE(description, ''),
		       status,
		       (bot_token IS NOT NULL AND bot_token <> ''),
		       last_seen_at, last_rotated_at, created_at, updated_at
		FROM public.telegram_bots
		%s
		ORDER BY created_at DESC
		LIMIT $%d OFFSET $%d
	`, where, len(args)-1, len(args))
	rows, err := s.pool.Query(ctx, q, args...)
	if err != nil {
		return nil, 0, fmt.Errorf("list telegram_bots: %w", err)
	}
	defer rows.Close()
	out := make([]TelegramBot, 0, f.Limit)
	for rows.Next() {
		var b TelegramBot
		if err := rows.Scan(
			&b.ID, &b.CompanyID, &b.Name,
			&b.WelcomeMessage, &b.DefaultSourceID, &b.Description,
			&b.Status, &b.BotTokenSet,
			&b.LastSeenAt, &b.LastRotatedAt, &b.CreatedAt, &b.UpdatedAt,
		); err != nil {
			return nil, 0, fmt.Errorf("scan telegram_bot: %w", err)
		}
		out = append(out, b)
	}
	if err := rows.Err(); err != nil {
		return nil, 0, fmt.Errorf("rows: %w", err)
	}
	return out, total, nil
}

// GetTelegramBot fetches a single bot by (company_id, id).
// Returns ErrTelegramBotNotFound if missing. The handler is
// responsible for the per-id scope check; this method is a
// straight DB lookup.
func (s *Store) GetTelegramBot(ctx context.Context, companyID, botID string) (*TelegramBot, error) {
	if s.pool == nil {
		return nil, errors.New("authd: no DB pool (test mode)")
	}
	const q = `
		SELECT bot_id, company_id, name,
		       COALESCE(welcome_message, ''),
		       COALESCE(default_source_id, ''),
		       COALESCE(description, ''),
		       status,
		       (bot_token IS NOT NULL AND bot_token <> ''),
		       last_seen_at, last_rotated_at, created_at, updated_at
		FROM public.telegram_bots
		WHERE company_id = $1 AND bot_id = $2
	`
	bot := &TelegramBot{}
	err := s.pool.QueryRow(ctx, q, companyID, botID).Scan(
		&bot.ID, &bot.CompanyID, &bot.Name,
		&bot.WelcomeMessage, &bot.DefaultSourceID, &bot.Description,
		&bot.Status, &bot.BotTokenSet,
		&bot.LastSeenAt, &bot.LastRotatedAt, &bot.CreatedAt, &bot.UpdatedAt,
	)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return nil, ErrTelegramBotNotFound
		}
		return nil, fmt.Errorf("get telegram_bot: %w", err)
	}
	return bot, nil
}

// CreateTelegramBot inserts a new bot and writes audit. The
// bot_token is stored in plaintext (telegramd reads it) AND
// bcrypt-hashed (so the UI can render "configured" without
// exposing the plaintext). Returns the wire-shape row, which
// includes bot_token_set=true. The plaintext is NOT returned
// in the response (the operator just typed it in; no need to
// echo it).
//
// Behavior:
//   - Duplicate (company_id, id) → ErrTelegramBotIDTaken (409).
//   - last_rotated_at is set to now() because the token was
//     just written. updated_at is set by the trigger.
func (s *Store) CreateTelegramBot(
	ctx context.Context,
	companyID, tenantDisplayName string,
	in CreateTelegramBotInput,
	actorUserID, actorIP, actorUA string,
) (*TelegramBot, error) {
	if s.pool == nil {
		return nil, errors.New("authd: no DB pool (test mode)")
	}
	if err := in.Validate(); err != nil {
		return nil, err
	}
	// Bridge: telegram_bots.company_id is a TEXT FK to
	// public.companies(id). M13a created auth.tenants; the
	// legacy public.companies row is what telegram_bots
	// references. ensurePublicCompanyRow (defined in
	// sources.go) is the same idempotent INSERT … ON CONFLICT
	// DO NOTHING we use for source create, so we don't 500
	// when a tenant has no companies row yet.
	if err := s.ensurePublicCompanyRow(ctx, companyID, tenantDisplayName); err != nil {
		return nil, err
	}
	hash, err := hashBotToken(in.BotToken)
	if err != nil {
		return nil, err
	}
	now := time.Now().UTC()
	const q = `
		INSERT INTO public.telegram_bots
		    (bot_id, company_id, name, bot_token, bot_token_hash,
		     welcome_message, default_source_id, description,
		     status, last_rotated_at)
		VALUES
		    ($1, $2::text, $3, $4, $5,
		     NULLIF($6, ''), NULLIF($7, ''), NULLIF($8, ''),
		     'active', $9)
		RETURNING bot_id, company_id, name,
		          COALESCE(welcome_message, ''),
		          COALESCE(default_source_id, ''),
		          COALESCE(description, ''),
		          status,
		          (bot_token IS NOT NULL AND bot_token <> ''),
		          last_seen_at, last_rotated_at, created_at, updated_at
	`
	bot := &TelegramBot{}
	err = s.pool.QueryRow(ctx, q,
		in.ID, companyID, strings.TrimSpace(in.Name),
		in.BotToken, hash,
		in.WelcomeMessage, in.DefaultSourceID, in.Description,
		now,
	).Scan(
		&bot.ID, &bot.CompanyID, &bot.Name,
		&bot.WelcomeMessage, &bot.DefaultSourceID, &bot.Description,
		&bot.Status, &bot.BotTokenSet,
		&bot.LastSeenAt, &bot.LastRotatedAt, &bot.CreatedAt, &bot.UpdatedAt,
	)
	if err != nil {
		var pgErr *pgconn.PgError
		if errors.As(err, &pgErr) && pgErr.Code == "23505" {
			return nil, ErrTelegramBotIDTaken
		}
		return nil, fmt.Errorf("create telegram_bot: %w", err)
	}
	// Audit. The plaintext token is NOT included.
	if err := s.WriteAudit(ctx, "telegram_bot.create", actorUserID, actorIP, actorUA, bot.CompanyID, bot.ID, map[string]any{
		"name":                bot.Name,
		"default_source_id":   bot.DefaultSourceID,
		"has_welcome_message": bot.WelcomeMessage != "",
		"bot_token_set":       bot.BotTokenSet,
	}); err != nil {
		_ = err
	}
	return bot, nil
}

// UpdateTelegramBot applies a partial update and writes audit.
// The bot_token is NOT updatable through this method (rotate is
// a separate action with its own audit trail and its own
// response shape).
func (s *Store) UpdateTelegramBot(
	ctx context.Context,
	companyID, botID string,
	in UpdateTelegramBotInput,
	actorUserID, actorIP, actorUA string,
) (*TelegramBot, error) {
	if s.pool == nil {
		return nil, errors.New("authd: no DB pool (test mode)")
	}
	if err := in.Validate(); err != nil {
		return nil, err
	}
	sets := []string{}
	args := []any{companyID, botID}
	if in.Name != nil {
		args = append(args, strings.TrimSpace(*in.Name))
		sets = append(sets, fmt.Sprintf("name = $%d", len(args)))
	}
	if in.WelcomeMessage != nil {
		args = append(args, *in.WelcomeMessage)
		sets = append(sets, fmt.Sprintf("welcome_message = NULLIF($%d, '')", len(args)))
	}
	if in.DefaultSourceID != nil {
		args = append(args, *in.DefaultSourceID)
		sets = append(sets, fmt.Sprintf("default_source_id = NULLIF($%d, '')", len(args)))
	}
	if in.Description != nil {
		args = append(args, *in.Description)
		sets = append(sets, fmt.Sprintf("description = NULLIF($%d, '')", len(args)))
	}
	if len(sets) == 0 {
		return s.GetTelegramBot(ctx, companyID, botID)
	}
	q := fmt.Sprintf("UPDATE public.telegram_bots SET %s WHERE company_id = $1 AND bot_id = $2", strings.Join(sets, ", "))
	tag, err := s.pool.Exec(ctx, q, args...)
	if err != nil {
		return nil, fmt.Errorf("update telegram_bot: %w", err)
	}
	if tag.RowsAffected() == 0 {
		return nil, ErrTelegramBotNotFound
	}
	payload := map[string]any{}
	if in.Name != nil {
		payload["name"] = *in.Name
	}
	if in.WelcomeMessage != nil {
		payload["welcome_message_set"] = true
	}
	if in.DefaultSourceID != nil {
		payload["default_source_id"] = *in.DefaultSourceID
	}
	if in.Description != nil {
		payload["description_set"] = true
	}
	if err := s.WriteAudit(ctx, "telegram_bot.update", actorUserID, actorIP, actorUA, companyID, botID, payload); err != nil {
		_ = err
	}
	return s.GetTelegramBot(ctx, companyID, botID)
}

// SetTelegramBotStatus flips status. Allowed transitions:
//   active  -> paused
//   paused  -> active
// No "archived" / "deleted" state for bots in v1 (operators
// leave them paused; archival is a v1.1 feature).
func (s *Store) SetTelegramBotStatus(
	ctx context.Context,
	companyID, botID, newStatus string,
	actorUserID, actorIP, actorUA string,
) (*TelegramBot, error) {
	if s.pool == nil {
		return nil, errors.New("authd: no DB pool (test mode)")
	}
	if _, ok := validTelegramBotStatuses[newStatus]; !ok {
		return nil, fmt.Errorf("%w: status must be active|paused", ErrTelegramBotInvalid)
	}
	cur, err := s.GetTelegramBot(ctx, companyID, botID)
	if err != nil {
		return nil, err
	}
	if cur.Status == newStatus {
		return cur, nil
	}
	if _, err := s.pool.Exec(ctx,
		"UPDATE public.telegram_bots SET status = $3 WHERE company_id = $1 AND bot_id = $2",
		companyID, botID, newStatus); err != nil {
		return nil, fmt.Errorf("set telegram_bot status: %w", err)
	}
	if err := s.WriteAudit(ctx, "telegram_bot.status", actorUserID, actorIP, actorUA, companyID, botID, map[string]any{
		"from": cur.Status,
		"to":   newStatus,
	}); err != nil {
		_ = err
	}
	return s.GetTelegramBot(ctx, companyID, botID)
}

// RotateTelegramBotToken sets a new bot_token, replacing the
// existing one. The new token is bcrypt-hashed and written to
// bot_token_hash; the plaintext replaces bot_token (telegramd
// will pick it up on the next reload — v1.1 adds a
// notification channel; W3 simply relies on the periodic poll
// restart). last_rotated_at is set to now().
//
// Returns the updated row. The plaintext is NOT echoed back —
// the operator just typed it, they already have it. If you
// want the server to generate a token, use the dedicated
// "create bot with BotFather" path (out of scope for v1).
func (s *Store) RotateTelegramBotToken(
	ctx context.Context,
	companyID, botID, newToken string,
	actorUserID, actorIP, actorUA string,
) (*TelegramBot, error) {
	if s.pool == nil {
		return nil, errors.New("authd: no DB pool (test mode)")
	}
	if !validBotTokenFormat(newToken) {
		return nil, fmt.Errorf("%w: bot_token must match ^\\d+:[A-Za-z0-9_-]{35}$", ErrTelegramBotInvalid)
	}
	// Confirm the bot exists first; surface 404 before doing
	// any work.
	if _, err := s.GetTelegramBot(ctx, companyID, botID); err != nil {
		return nil, err
	}
	hash, err := hashBotToken(newToken)
	if err != nil {
		return nil, err
	}
	now := time.Now().UTC()
	if _, err := s.pool.Exec(ctx,
		"UPDATE public.telegram_bots SET bot_token = $3, bot_token_hash = $4, last_rotated_at = $5 WHERE company_id = $1 AND bot_id = $2",
		companyID, botID, newToken, hash, now); err != nil {
		return nil, fmt.Errorf("rotate telegram_bot token: %w", err)
	}
	if err := s.WriteAudit(ctx, "telegram_bot.rotate_token", actorUserID, actorIP, actorUA, companyID, botID, map[string]any{
		"rotated": true,
	}); err != nil {
		_ = err
	}
	return s.GetTelegramBot(ctx, companyID, botID)
}

// -------------------------------------------------------------------
// helpers
// -------------------------------------------------------------------

// validTelegramBotID matches the same regex as auth.tenants.slug
// and source IDs: ^[a-z0-9][a-z0-9-]{0,62}[a-z0-9]$.
//
// Why a separate function? The bot id appears in a public
// Telegram URL (`t.me/<bot>`) and as part of the api.telegram.org
// path; keeping the same charset as the rest of the system
// avoids any URL-encoding gotchas.
func validTelegramBotID(s string) bool {
	if len(s) < 2 || len(s) > 64 {
		return false
	}
	if !isAlnumOrDash(s[0]) || s[0] == '-' {
		return false
	}
	if !isAlnumOrDash(s[len(s)-1]) || s[len(s)-1] == '-' {
		return false
	}
	for i := 1; i < len(s)-1; i++ {
		if !isAlnumOrDash(s[i]) {
			return false
		}
	}
	return true
}

// validBotTokenFormat — Telegram bot tokens look like
//   <bot_id>:<secret>
// where bot_id is a decimal integer (8-10 digits) and secret
// is 35 [A-Za-z0-9_-] chars. The full regex Telegram documents
// is `^\d+:[A-Za-z0-9_-]{35}$`; we accept the same shape.
// (Real BotFather tokens are exactly 46 chars including the
// colon; we use the more lenient regex from M13b_PLAN §2.3.)
func validBotTokenFormat(s string) bool {
	if len(s) < 37 || len(s) > 100 {
		// minimum 1+1+35 = 37; upper bound is generous
		return false
	}
	colon := -1
	for i, c := range s {
		if c == ':' {
			if colon >= 0 {
				return false // more than one colon
			}
			colon = i
		}
	}
	if colon < 1 || colon == len(s)-1 {
		return false
	}
	// bot id part: digits only
	for i := 0; i < colon; i++ {
		if s[i] < '0' || s[i] > '9' {
			return false
		}
	}
	// secret part: 35+ [A-Za-z0-9_-]
	if len(s)-colon-1 < 35 {
		return false
	}
	for i := colon + 1; i < len(s); i++ {
		c := s[i]
		if !((c >= 'a' && c <= 'z') || (c >= 'A' && c <= 'Z') ||
			(c >= '0' && c <= '9') || c == '_' || c == '-') {
			return false
		}
	}
	return true
}

// hashBotToken bcrypts the plaintext token at cost 10. The
// actual hash is never used to validate anything (Telegram
// validates by checking the plaintext itself); the hash
// column exists so the UI can render "configured" without
// the server having to expose the plaintext. Cost 10 mirrors
// the source hmac_secret path.
func hashBotToken(plain string) (string, error) {
	return hashSecret(plain, "bot_token")
}