package postgres import ( "context" "encoding/json" "errors" "fmt" "strings" "time" "github.com/jackc/pgx/v5" "gitea.zheromiku.top/Weknora/dobotai-platform/internal/directory" ) func (s *Store) LookupDirectoryProfile(ctx context.Context, tenantKey, staffID string) (directory.Profile, bool, error) { tenantKey = strings.TrimSpace(tenantKey) staffID = strings.TrimSpace(staffID) if tenantKey == "" || staffID == "" { return directory.Profile{}, false, errors.New("tenant and staff id are required") } var profile directory.Profile var departmentIDs []byte err := s.pool.QueryRow(ctx, `SELECT staff_id,union_id,name,nickname,job_title,department_ids,department_name,main_department_name,region,employment_status,source,synced_at FROM directory_profiles WHERE tenant_key=$1 AND staff_id=$2`, tenantKey, staffID).Scan( &profile.StaffID, &profile.UnionID, &profile.Name, &profile.Nickname, &profile.JobTitle, &departmentIDs, &profile.DepartmentName, &profile.MainDepartmentName, &profile.Region, &profile.EmploymentStatus, &profile.Source, &profile.SyncedAt, ) if errors.Is(err, pgx.ErrNoRows) { return directory.Profile{}, false, nil } if err != nil { return directory.Profile{}, false, fmt.Errorf("lookup directory profile: %w", err) } if err := json.Unmarshal(departmentIDs, &profile.DepartmentIDs); err != nil { return directory.Profile{}, false, errors.New("decode directory department ids") } return profile, true, nil } func (s *Store) UpsertDirectoryProfiles(ctx context.Context, tenantKey string, profiles []directory.Profile) error { tenantKey = strings.TrimSpace(tenantKey) if tenantKey == "" || len(profiles) == 0 { return errors.New("tenant and non-empty directory profiles are required") } for _, profile := range profiles { if strings.TrimSpace(profile.StaffID) == "" || strings.TrimSpace(profile.Name) == "" { return errors.New("directory profile requires staff id and name") } } tx, err := s.pool.BeginTx(ctx, pgx.TxOptions{}) if err != nil { return err } defer func() { _ = tx.Rollback(ctx) }() for _, profile := range profiles { departmentIDs, marshalErr := json.Marshal(profile.DepartmentIDs) if marshalErr != nil { return errors.New("encode directory department ids") } syncedAt := profile.SyncedAt if syncedAt.IsZero() { syncedAt = time.Now().UTC() } _, err = tx.Exec(ctx, `INSERT INTO directory_profiles (tenant_key,staff_id,union_id,name,nickname,job_title,department_ids,department_name,main_department_name,region,employment_status,source,synced_at) VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13) ON CONFLICT (tenant_key,staff_id) DO UPDATE SET union_id=EXCLUDED.union_id,name=EXCLUDED.name,nickname=EXCLUDED.nickname,job_title=EXCLUDED.job_title, department_ids=EXCLUDED.department_ids,department_name=EXCLUDED.department_name, main_department_name=EXCLUDED.main_department_name,region=EXCLUDED.region, employment_status=EXCLUDED.employment_status,source=EXCLUDED.source,synced_at=EXCLUDED.synced_at,updated_at=NOW()`, tenantKey, profile.StaffID, profile.UnionID, profile.Name, profile.Nickname, profile.JobTitle, departmentIDs, profile.DepartmentName, profile.MainDepartmentName, profile.Region, profile.EmploymentStatus, profile.Source, syncedAt) if err != nil { return fmt.Errorf("upsert directory profile: %w", err) } } return tx.Commit(ctx) }