package postgres import ( "context" "crypto/rand" "crypto/sha256" "crypto/subtle" "encoding/hex" "encoding/json" "errors" "fmt" "strings" "time" "gitea.zheromiku.top/Weknora/dobotai-platform/internal/reasoningmode" "gitea.zheromiku.top/Weknora/dobotai-platform/internal/settings" "github.com/jackc/pgx/v5" ) type SettingsRepository struct{ store *Store } func NewSettingsRepository(s *Store) (*SettingsRepository, error) { if s == nil || s.pool == nil { return nil, errors.New("settings repository: store is required") } return &SettingsRepository{store: s}, nil } func (r *SettingsRepository) IsAdministrator(ctx context.Context, tenant, staff string) (bool, error) { var ok bool err := r.store.pool.QueryRow(ctx, `SELECT EXISTS(SELECT 1 FROM administrators WHERE tenant_key=$1 AND staff_id=$2 AND status='active')`, strings.TrimSpace(tenant), strings.TrimSpace(staff)).Scan(&ok) return ok, err } func (r *SettingsRepository) SetPreference(ctx context.Context, tenant, channel, staff, locale string) error { mode := "fixed" if locale == "auto" { mode = "auto" } _, err := r.store.pool.Exec(ctx, `INSERT INTO user_preferences(tenant_key,channel,external_actor_id,language_mode,locale) VALUES($1,$2,$3,$4,$5) ON CONFLICT(tenant_key,channel,external_actor_id) DO UPDATE SET language_mode=EXCLUDED.language_mode,locale=EXCLUDED.locale,updated_at=NOW()`, tenant, channel, staff, mode, locale) return err } func (r *SettingsRepository) SetReasoningMaxRounds(ctx context.Context, tenant, channel, staff string, rounds int, now time.Time) error { _, err := r.store.SetMaxRounds(ctx, reasoningmode.Scope{TenantKey: tenant, Channel: channel, ExternalActorID: staff}, rounds, now) return err } func (r *SettingsRepository) SetReasoningHintEnabled(ctx context.Context, tenant, channel, staff string, enabled bool, now time.Time) error { _, err := r.store.SetHintEnabled(ctx, reasoningmode.Scope{TenantKey: tenant, Channel: channel, ExternalActorID: staff}, enabled, now) return err } func (r *SettingsRepository) GetPreference(ctx context.Context, tenant, channel, staff string) (string, error) { var locale string err := r.store.pool.QueryRow(ctx, `SELECT locale FROM user_preferences WHERE tenant_key=$1 AND channel=$2 AND external_actor_id=$3`, tenant, channel, staff).Scan(&locale) if errors.Is(err, pgx.ErrNoRows) { return "auto", nil } return locale, err } func (r *SettingsRepository) GetSettingSession(ctx context.Context, tenant, staff string) (settings.SettingSession, bool, error) { var session settings.SettingSession err := r.store.pool.QueryRow(ctx, `SELECT tenant_key,staff_id,conversation_id,conversation_type,pending_action,expires_at FROM setting_sessions WHERE tenant_key=$1 AND staff_id=$2 AND expires_at>NOW()`, tenant, staff).Scan(&session.TenantKey, &session.StaffID, &session.ConversationID, &session.ConversationType, &session.PendingAction, &session.ExpiresAt) if errors.Is(err, pgx.ErrNoRows) { return settings.SettingSession{}, false, nil } return session, err == nil, err } func (r *SettingsRepository) SaveSettingSession(ctx context.Context, session settings.SettingSession) error { _, err := r.store.pool.Exec(ctx, `INSERT INTO setting_sessions(tenant_key,staff_id,conversation_id,conversation_type,current_step,pending_action,draft_config,expires_at) VALUES($1,$2,$3,$4,'waiting_input',$5,'{}'::JSONB,$6) ON CONFLICT(tenant_key,staff_id) DO UPDATE SET conversation_id=EXCLUDED.conversation_id,conversation_type=EXCLUDED.conversation_type,current_step='waiting_input',pending_action=EXCLUDED.pending_action,draft_config='{}'::JSONB,expires_at=EXCLUDED.expires_at,updated_at=NOW()`, session.TenantKey, session.StaffID, session.ConversationID, session.ConversationType, session.PendingAction, session.ExpiresAt) return err } func (r *SettingsRepository) DeleteSettingSession(ctx context.Context, tenant, staff string) error { _, err := r.store.pool.Exec(ctx, `DELETE FROM setting_sessions WHERE tenant_key=$1 AND staff_id=$2`, tenant, staff) return err } func (r *SettingsRepository) SaveBinding(ctx context.Context, b settings.ResourceBinding) error { payload, err := json.Marshal(map[string]interface{}{"conversation_id": b.ConversationID, "conversation_title": b.ConversationTitle, "base_id": b.BaseID, "sheet_id": b.SheetID, "view_id": b.ViewID, "operator_id": b.OperatorID, "field_mapping": b.FieldMapping}) if err != nil { return err } tx, err := r.store.pool.Begin(ctx) if err != nil { return err } defer tx.Rollback(ctx) var rev int if err = tx.QueryRow(ctx, `SELECT COALESCE(MAX(revision),0)+1 FROM system_resource_bindings WHERE tenant_key=$1 AND kind=$2`, b.TenantKey, b.Kind).Scan(&rev); err != nil { return err } state := "validated" var validatedAt, activatedAt interface{} if b.Activate { state = "active" validatedAt, activatedAt = time.Now(), time.Now() if _, err = tx.Exec(ctx, `UPDATE system_resource_bindings SET state='retired' WHERE tenant_key=$1 AND kind=$2 AND state='active'`, b.TenantKey, b.Kind); err != nil { return err } } validationCode := strings.TrimSpace(b.ValidationCode) if validationCode == "" { validationCode = "pending_external_probe" } _, err = tx.Exec(ctx, `INSERT INTO system_resource_bindings(id,tenant_key,kind,revision,state,public_config,validation_code,validated_at,activated_at,created_by_staff_id) VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10)`, newSettingsID(), b.TenantKey, b.Kind, rev, state, payload, validationCode, validatedAt, activatedAt, b.CreatedByStaffID) if err != nil { return err } return tx.Commit(ctx) } func (r *SettingsRepository) DetectBootstrapCandidate(ctx context.Context, c settings.BootstrapCandidate) error { hash := sha256.Sum256([]byte(strings.TrimSpace(c.Code))) var id, stored string var attempts, max int err := r.store.pool.QueryRow(ctx, `SELECT id,code_hash,attempts,max_attempts FROM admin_bootstrap_challenges WHERE tenant_key=$1 AND status='pending' AND expires_at>NOW() ORDER BY created_at DESC LIMIT 1`, c.TenantKey).Scan(&id, &stored, &attempts, &max) if err != nil { return err } if attempts >= max { return errors.New("bootstrap challenge locked") } provided := hex.EncodeToString(hash[:]) if len(provided) != len(stored) || subtle.ConstantTimeCompare([]byte(provided), []byte(stored)) != 1 { _, _ = r.store.pool.Exec(ctx, `UPDATE admin_bootstrap_challenges SET attempts=attempts+1,status=CASE WHEN attempts+1>=max_attempts THEN 'locked' ELSE status END,updated_at=NOW() WHERE id=$1`, id) return errors.New("invalid bootstrap code") } _, err = r.store.pool.Exec(ctx, `UPDATE admin_bootstrap_challenges SET candidate_staff_id=$2,candidate_union_id=$3,candidate_display_name=$4,candidate_department=$5,status='candidate_detected',updated_at=NOW() WHERE id=$1`, id, c.StaffID, c.UnionID, c.DisplayName, c.Department) return err } func (r *SettingsRepository) CreateBootstrapChallenge(ctx context.Context, tenant, code string, expires time.Time) error { sum := sha256.Sum256([]byte(code)) tx, err := r.store.pool.Begin(ctx) if err != nil { return err } defer tx.Rollback(ctx) if _, err = tx.Exec(ctx, `UPDATE admin_bootstrap_challenges SET status='expired',updated_at=NOW() WHERE tenant_key=$1 AND status IN ('pending','candidate_detected')`, tenant); err != nil { return err } _, err = tx.Exec(ctx, `INSERT INTO admin_bootstrap_challenges(id,tenant_key,code_hash,expires_at,status) VALUES($1,$2,$3,$4,'pending')`, newSettingsID(), tenant, hex.EncodeToString(sum[:]), expires) if err != nil { return err } return tx.Commit(ctx) } func (r *SettingsRepository) ConfirmBootstrap(ctx context.Context, tenant string) error { tx, err := r.store.pool.Begin(ctx) if err != nil { return err } defer tx.Rollback(ctx) var challengeID, staff, union, name, dept string err = tx.QueryRow(ctx, `SELECT id,candidate_staff_id,candidate_union_id,candidate_display_name,candidate_department FROM admin_bootstrap_challenges WHERE tenant_key=$1 AND status='candidate_detected' AND expires_at>NOW() FOR UPDATE`, tenant).Scan(&challengeID, &staff, &union, &name, &dept) if err != nil { if errors.Is(err, pgx.ErrNoRows) { return settings.ErrBootstrapCandidatePending } return err } if strings.TrimSpace(staff) == "" { return errors.New("bootstrap candidate missing") } _, err = tx.Exec(ctx, `INSERT INTO administrators(id,tenant_key,staff_id,union_id,display_name,department,role,status) VALUES($1,$2,$3,$4,$5,$6,'super_admin','active') ON CONFLICT(tenant_key,staff_id) DO UPDATE SET status='active',role='super_admin',updated_at=NOW()`, newSettingsID(), tenant, staff, union, name, dept) if err != nil { return err } if _, err = tx.Exec(ctx, `UPDATE admin_bootstrap_challenges SET status='confirmed',updated_at=NOW() WHERE id=$1`, challengeID); err != nil { return err } return tx.Commit(ctx) } func (r *SettingsRepository) AdministratorCount(ctx context.Context, tenant string) (int, error) { var n int err := r.store.pool.QueryRow(ctx, `SELECT COUNT(*) FROM administrators WHERE tenant_key=$1 AND status='active'`, tenant).Scan(&n) return n, err } func (r *SettingsRepository) ListAdministrators(ctx context.Context, tenant string) ([]settings.Administrator, error) { rows, err := r.store.pool.Query(ctx, `SELECT staff_id,display_name,department,role,status FROM administrators WHERE tenant_key=$1 ORDER BY CASE status WHEN 'active' THEN 0 ELSE 1 END,created_at`, strings.TrimSpace(tenant)) if err != nil { return nil, err } defer rows.Close() administrators := []settings.Administrator{} for rows.Next() { var administrator settings.Administrator if err = rows.Scan(&administrator.StaffID, &administrator.DisplayName, &administrator.Department, &administrator.Role, &administrator.Status); err != nil { return nil, err } administrators = append(administrators, administrator) } return administrators, rows.Err() } func (r *SettingsRepository) DisableAdministrator(ctx context.Context, tenant, staffID string) error { tenant, staffID = strings.TrimSpace(tenant), strings.TrimSpace(staffID) if tenant == "" || staffID == "" { return settings.ErrAdministratorNotFound } tx, err := r.store.pool.Begin(ctx) if err != nil { return err } defer tx.Rollback(ctx) rows, err := tx.Query(ctx, `SELECT staff_id,status FROM administrators WHERE tenant_key=$1 FOR UPDATE`, tenant) if err != nil { return err } activeCount, targetStatus, found := 0, "", false for rows.Next() { var currentStaffID, status string if err = rows.Scan(¤tStaffID, &status); err != nil { rows.Close() return err } if status == "active" { activeCount++ } if currentStaffID == staffID { targetStatus, found = status, true } } rows.Close() if err = rows.Err(); err != nil { return err } if !found { return settings.ErrAdministratorNotFound } if err = canDisableAdministrator(targetStatus, activeCount); err != nil { return err } result, err := tx.Exec(ctx, `UPDATE administrators SET status='disabled',updated_at=NOW() WHERE tenant_key=$1 AND staff_id=$2 AND status='active'`, tenant, staffID) if err != nil { return err } if result.RowsAffected() != 1 { return settings.ErrAdministratorNotFound } if _, err = tx.Exec(ctx, `INSERT INTO config_audit_events(id,tenant_key,actor_staff_id,action,target_kind,target_revision,result_code,metadata) VALUES($1,$2,'','administrator.disable','administrator',0,'administrator_disabled',jsonb_build_object('target_staff_id',$3,'source','admin_web'))`, newSettingsID(), tenant, staffID); err != nil { return err } return tx.Commit(ctx) } func canDisableAdministrator(status string, activeCount int) error { if status != "active" { return settings.ErrAdministratorAlreadyDisabled } if activeCount <= 1 { return settings.ErrLastAdministrator } return nil } func (r *SettingsRepository) SaveDingTalkConfig(ctx context.Context, tenant, clientID, secretRef string, verified bool) error { payload, _ := json.Marshal(map[string]string{"client_id": clientID}) state := "validated" validation := "ok" if verified { state = "active" } tx, err := r.store.pool.Begin(ctx) if err != nil { return err } defer tx.Rollback(ctx) var rev int if err = tx.QueryRow(ctx, `SELECT COALESCE(MAX(revision),0)+1 FROM system_config_revisions WHERE tenant_key=$1 AND kind='dingtalk_app'`, tenant).Scan(&rev); err != nil { return err } if state == "active" { if _, err = tx.Exec(ctx, `UPDATE system_config_revisions SET state='retired' WHERE tenant_key=$1 AND kind='dingtalk_app' AND state='active'`, tenant); err != nil { return err } } _, err = tx.Exec(ctx, `INSERT INTO system_config_revisions(id,tenant_key,kind,revision,state,public_config,secret_ref,validation_status,validation_code,validated_at,activated_at) VALUES($1,$2,'dingtalk_app',$3,$4,$5,$6,$7,$7,CASE WHEN $7='ok' THEN NOW() END,CASE WHEN $7='ok' THEN NOW() END)`, newSettingsID(), tenant, rev, state, payload, secretRef, validation) if err != nil { return err } return tx.Commit(ctx) } func (r *SettingsRepository) CandidateDingTalkConfig(ctx context.Context, tenant string) (clientID, secretRef string, err error) { var payload []byte err = r.store.pool.QueryRow(ctx, `SELECT public_config,secret_ref FROM system_config_revisions WHERE tenant_key=$1 AND kind='dingtalk_app' AND state='validated' AND validation_status='ok' ORDER BY revision DESC LIMIT 1`, tenant).Scan(&payload, &secretRef) if err != nil { return } var value map[string]string if json.Unmarshal(payload, &value) != nil { return "", "", errors.New("decode dingtalk candidate") } return value["client_id"], secretRef, nil } func (r *SettingsRepository) ActivateDingTalkCandidate(ctx context.Context, tenant, secretRef string) error { tx, err := r.store.pool.Begin(ctx) if err != nil { return err } defer tx.Rollback(ctx) var candidateID string err = tx.QueryRow(ctx, `SELECT id FROM system_config_revisions WHERE tenant_key=$1 AND kind='dingtalk_app' AND state='validated' AND secret_ref=$2 AND validation_status='ok' FOR UPDATE`, tenant, secretRef).Scan(&candidateID) if err != nil { return err } if _, err = tx.Exec(ctx, `UPDATE system_config_revisions SET state='retired' WHERE tenant_key=$1 AND kind='dingtalk_app' AND state='active'`, tenant); err != nil { return err } if _, err = tx.Exec(ctx, `UPDATE system_config_revisions SET state='active',validation_code='pending_runtime',activated_at=NOW() WHERE id=$1`, candidateID); err != nil { return err } return tx.Commit(ctx) } func (r *SettingsRepository) PrepareDingTalkRuntimeStart(ctx context.Context, tenant string) (bool, error) { tx, err := r.store.pool.Begin(ctx) if err != nil { return false, err } defer tx.Rollback(ctx) var activeID, activeRef, code string err = tx.QueryRow(ctx, `SELECT id,secret_ref,validation_code FROM system_config_revisions WHERE tenant_key=$1 AND kind='dingtalk_app' AND state='active' FOR UPDATE`, tenant).Scan(&activeID, &activeRef, &code) if errors.Is(err, pgx.ErrNoRows) { return false, tx.Commit(ctx) } if err != nil { return false, err } switch code { case "pending_runtime": if _, err = tx.Exec(ctx, `UPDATE system_config_revisions SET validation_code='runtime_starting' WHERE id=$1`, activeID); err != nil { return false, err } return true, tx.Commit(ctx) case "runtime_starting": var oldID string err = tx.QueryRow(ctx, `SELECT id FROM system_config_revisions WHERE tenant_key=$1 AND kind='dingtalk_app' AND state='retired' AND validation_status='ok' ORDER BY revision DESC LIMIT 1 FOR UPDATE`, tenant).Scan(&oldID) if err != nil && !errors.Is(err, pgx.ErrNoRows) { return false, err } if _, err = tx.Exec(ctx, `UPDATE system_config_revisions SET state='failed',validation_status='failed',validation_code='runtime_start_failed' WHERE id=$1`, activeID); err != nil { return false, err } if oldID != "" { if _, err = tx.Exec(ctx, `UPDATE system_config_revisions SET state='active',validation_code='rollback_restored',activated_at=NOW() WHERE id=$1`, oldID); err != nil { return false, err } } return false, tx.Commit(ctx) default: _ = activeRef return false, tx.Commit(ctx) } } func (r *SettingsRepository) MarkDingTalkRuntimeHealthy(ctx context.Context, tenant, secretRef string) error { _, err := r.store.pool.Exec(ctx, `UPDATE system_config_revisions SET validation_code='runtime_ok' WHERE tenant_key=$1 AND kind='dingtalk_app' AND state='active' AND secret_ref=$2`, tenant, secretRef) return err } func (r *SettingsRepository) RollbackPendingDingTalkActivation(ctx context.Context, tenant, failedRef string) error { tx, err := r.store.pool.Begin(ctx) if err != nil { return err } defer tx.Rollback(ctx) var currentID string if err = tx.QueryRow(ctx, `SELECT id FROM system_config_revisions WHERE tenant_key=$1 AND kind='dingtalk_app' AND state='active' AND secret_ref=$2 AND validation_code='pending_runtime' FOR UPDATE`, tenant, failedRef).Scan(¤tID); err != nil { return err } var oldID string if err = tx.QueryRow(ctx, `SELECT id FROM system_config_revisions WHERE tenant_key=$1 AND kind='dingtalk_app' AND state='retired' AND validation_status='ok' ORDER BY revision DESC LIMIT 1 FOR UPDATE`, tenant).Scan(&oldID); err != nil { return err } if _, err = tx.Exec(ctx, `UPDATE system_config_revisions SET state='failed',validation_status='failed',validation_code='runtime_start_failed' WHERE id=$1`, currentID); err != nil { return err } if _, err = tx.Exec(ctx, `UPDATE system_config_revisions SET state='active',validation_code='rollback_restored',activated_at=NOW() WHERE id=$1`, oldID); err != nil { return err } return tx.Commit(ctx) } func (r *SettingsRepository) MarkDingTalkCandidateFailed(ctx context.Context, tenant, secretRef, code string) error { _, err := r.store.pool.Exec(ctx, `UPDATE system_config_revisions SET state='failed',validation_status='failed',validation_code=$3 WHERE tenant_key=$1 AND kind='dingtalk_app' AND state='validated' AND secret_ref=$2`, tenant, secretRef, code) return err } func (r *SettingsRepository) ActiveDingTalkConfig(ctx context.Context, tenant string) (clientID, secretRef string, verified bool, err error) { var payload []byte var status string err = r.store.pool.QueryRow(ctx, `SELECT public_config,secret_ref,validation_status FROM system_config_revisions WHERE tenant_key=$1 AND kind='dingtalk_app' AND state='active' ORDER BY revision DESC LIMIT 1`, tenant).Scan(&payload, &secretRef, &status) if errors.Is(err, pgx.ErrNoRows) { err = nil return } if err != nil { return } var v map[string]string if json.Unmarshal(payload, &v) != nil { err = fmt.Errorf("decode dingtalk config") return } clientID = v["client_id"] verified = status == "ok" return } func (r *SettingsRepository) ActiveBindings(ctx context.Context, tenant string) (map[string]settings.ResourceBinding, error) { rows, err := r.store.pool.Query(ctx, `SELECT kind,public_config FROM system_resource_bindings WHERE tenant_key=$1 AND state='active'`, tenant) if err != nil { return nil, err } defer rows.Close() out := map[string]settings.ResourceBinding{} for rows.Next() { var kind string var raw []byte if err = rows.Scan(&kind, &raw); err != nil { return nil, err } var v struct { ConversationID string `json:"conversation_id"` ConversationTitle string `json:"conversation_title"` BaseID string `json:"base_id"` SheetID string `json:"sheet_id"` ViewID string `json:"view_id"` OperatorID string `json:"operator_id"` FieldMapping map[string]string `json:"field_mapping"` } if json.Unmarshal(raw, &v) != nil { continue } out[kind] = settings.ResourceBinding{Kind: kind, TenantKey: tenant, ConversationID: v.ConversationID, ConversationTitle: v.ConversationTitle, BaseID: v.BaseID, SheetID: v.SheetID, ViewID: v.ViewID, OperatorID: v.OperatorID, FieldMapping: v.FieldMapping, Activate: true} } return out, rows.Err() } func newSettingsID() string { b := make([]byte, 16); _, _ = rand.Read(b); return hex.EncodeToString(b) }